Re: TKPROF

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/03/01
Message-ID: <34fe797d.24863291_at_192.86.155.100>#1/1

[Quoted] A copy of this was sent to arthurk_at_ibm.net (if that email address didn't require changing) On 28 Feb 98 23:39:12 GMT, you wrote:

>Using Sql*Plus on Windows NT, I connect to an Oracle 8 instance on a Unix box.
>I need to tune some statements, preferably using TKPROF, but I don't have a
>login on the Unix machine (nor do I have NFS available to read the remote machine
>disks :-(.
>
>Any ideas how I can run TKPROF to tune my statements? I don't think I'll
>get a login on the UNIX box in the near future.
>
>TIA,
>
>Kurt Arthur
>arthurk_at_ibm.net

Can you ask the Oracle DBA to make it so you can use the sqlplus autotrace feature? Its similar to TKPROF but runs in 'real' time in sqlplus.

You need to:

• run plustrce.sql as SYS. this is found in $ORACLE_HOME/sqlplus/admin.
• grant plustrace (a role) to people who want to be able to use this feature.
• run utlxplan.sql found in $ORACLE_HOME/rdbms/admin in the schemas of people who want to use this feature.

Then, in sqlplus you'll be able to:

SQL> set autotrace on

SQL> select * from emp, dept
  2 where emp.deptno = dept.deptno
  3 and dept.deptno = 10;

[data snipped]

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=97)    1 0 NESTED LOOPS (Cost=2 Card=1 Bytes=97)

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

Statistics

---

         39  recursive calls
          3  db block gets
         10  consistent gets
          6  physical reads
          0  redo size
       1461  bytes sent via SQL*Net to client
        703  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          4  rows processed



 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

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

---

[Quoted] 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 Sun Mar 01 1998 - 00:00:00 CET