Re: Getting detailed informations of a sql-statement

On Thu, 15 Oct 1998 10:47:42 GMT, agrudzinski_at_cs-controlling.de wrote:

>Hi,
>I do need to check, if my program's access to
>oracle-tables should be optimized. For this I would
>like to know, if the sql-statement is using an
>index or anything optimized routines (e.g.: if I do
>select the data with a combined key, one field
>exists as index, others not; does the select use
>this index or does it prefers a full table scan?).
>
>Which possibilities do I have to analyze
>bad performance?

You can from sql*plus

set autotrace on

setup:

install the file $ORACLE_HOME/sqlplus/admin/plustrce.sql before you do this.
( review that file for installation steps and usage )

NOTE: You must install the plan_table for this method to work. You can use the file $ORACLE_HOME/rdbms/admin/utlxplan.sql to create that table.

eg.

SQL> set autotrace on

SQL> select ename, dname
  2 from emp, dept
  3 where emp.deptno = dept.deptno;

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

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

14 rows selected.

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 MERGE JOIN

   2    1     SORT (JOIN)
   3    2       TABLE ACCESS (FULL) OF 'DEPT'
   4    1     SORT (JOIN)
   5    4       TABLE ACCESS (FULL) OF 'EMP'

Statistics

         28  recursive calls
          8  db block gets
          4  consistent gets
          1  physical reads
          0  redo size
       1291  bytes sent via SQL*Net to client
        701  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
         14  rows processed

SQL> Also look into using alter session set sql_trace=true in conjunction with tkprof.

Hope this helps.

chris.

>
>Thanks in advance,
>
>Astrid Grudzinski
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Thu Oct 15 1998 - 07:49:20 CDT