Re: Slow access with Pro*C/C++

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 19 May 1999 12:03:31 GMT
Message-ID: <3743a70e.3864456_at_newshost.us.oracle.com>

A copy of this was sent to mguillem_at_my-dejanews.com (if that email address didn't require changing) On Wed, 19 May 1999 09:37:13 GMT, you wrote:

>I want to select from a C code a small number of lines (lets say 10)
>from a join on 2 large tables with an "order by" clause.
>I get good results when I seek elements at the end of the order by but
>very bad results at the beginning. It looks as if Oracle first loads
>all the results before giving me my 10 lines.
>
>When I execute the same SQL order from SQL Plus for instance, it comes
>immediately, so it's not a problem of index or of database.
>
>Any suggestion?
>

lets see the query as you have it in pro*c and sqlplus. I bet they are different in small ways (eg: no using bind variables in sqlplus, using them in pro*c and so one) so the query plans are different.

To see what the differences are, use explain plan to get the query plans. Perhaps the easiest way to do this would be:

• in sqlplus, enable AUTOTRACE. That way you can:

SQL> set autotrace on explain
SQL> select ename, dname from emp, dept where emp.deptno = dept.deptno;

ENAME DNAME
---------- --------------

SMITH      RESEARCH
ALLEN      SALES
WARD       SALES
JONES      RESEARCH
MARTIN     SALES
BLAKE      SALES
CLARK      ACCOUNTING
SCOTT      RESEARCH
KING       ACCOUNTING
TURNER     SALES
ADAMS      RESEARCH
JAMES      SALES
FORD       RESEARCH
MILLER     ACCOUNTING

14 rows selected.

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 NESTED LOOPS

   2    1     TABLE ACCESS (FULL) OF 'EMP'
   3    1     TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
   4    3       INDEX (UNIQUE SCAN) OF 'SYS_C0038800' (UNIQUE)

to enable autotrace -- you can:

cd $oracle_home/rdbms/admin
log into sqlplus as system

run SQL> _at_utlxplan 
run SQL> create public public synonym plan_table for plan_table 
run SQL> grant all on plan_table to public 

exit sqlplus and cd $oracle_home/sqlplus/admin log into sqlplus as sys
run SQL> _at_plustrce
run SQL> grant plustrace to public

that will enable everyone in your database to use autotrace. replace public with users/roles if you want.

• use tkprof in your pro*c program. Add the line:

  exec sql alter session set sql_trace=true;

before your statement in your code and run your program. That will generate a trace file into the user_dump_dest (init.ora parameter) directory on the server. get that trace file and run:

$ tkprof tracefilename reportfilename explain=userid/password

that will generate a report that will have something like:

select ename, dname
from
 emp, dept where emp.deptno = dept.deptno

call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0         18          3          14
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0         18          3          14

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 4932 (SCOTT)

Rows Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      0   NESTED LOOPS
      0    TABLE ACCESS (FULL) OF 'EMP'
      0    TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
      0     INDEX (UNIQUE SCAN) OF 'DEPT_PK' (UNIQUE)

compare the 2 plans and see what is different.

>
>--== Sent via Deja.com http://www.deja.com/ ==--
>---Share what you know. Learn what you don't.---

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

-- 
Opinions are mine and do not necessarily reflect those of Oracle Corporation

Received on Wed May 19 1999 - 14:03:31 CEST