Re: Oracle/SQL Question

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 14 Apr 1998 14:05:58 GMT
Message-ID: <35336c9b.3452183@192.86.155.100>

A copy of this was sent to lorrie_at_macconnect.com (Lorrie) (if that email address didn't require changing) On Mon, 13 Apr 1998 19:48:29 -0400, you wrote:

>I am an Oracle developer. When I am developing SQL statements and tuning
>them, I need to run them over and over again, to see the performance effect
>of changes to the statement, and optimization mode. I also need to see how
>long it takes each statement to run. Also, sometimes when I drastically
>change
>the statement, and it uses the wrong index, the statement goes on for ever.
>Then I have to kill the application, and then ask my DBA to kill the orphan
>client session. Are there any tools out there that could help me with any
>of these problems/requirements ?

You don't mention the database version but assuming 7.3 or up, you can use autotrace pretty easily to do this in sqlplus. Your DBA will run the plustrce.sql in $ORACLE_HOME/sqlplus/admin to configure this and you will create a plan table using the template table create in utlxplan.sql found in $ORACLE_HOME/rdbms/admin. Once you do this, you can do things like:

SQL> set autotrace on
SQL> select * from emp, dept where emp.deptno = dept.deptno;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
DEPTNO     DEPTNO DNAME          LOC
---------- ---------- --------- ---------- --------- ---------- ----------
---------- ---------- -------------- -------------
      7369 SMITH      CLERK           7902 17-DEC-80        800
20         20 RESEARCH       DALLAS

..... [snip]

14 rows selected.

And sqlplus will generate:

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_C0013980' (UNIQUE)

Statistics

        389  recursive calls
          5  db block gets
         96  consistent gets
         12  physical reads
          0  redo size
       2469  bytes sent via SQL*Net to client
        682  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          7  sorts (memory)
          0  sorts (disk)
         14  rows processed

Also, you can just Ctl-C a long running query to kill it.

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

http://govt.us.oracle.com/ -- downloadable utilities

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

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Tue Apr 14 1998 - 09:05:58 CDT