Re: How to benchmark SQL and PL/SQL scripts??

Thomas,

Does this work when executing a query from a stored procedure/package? It do not work when I use SET AUTOTRACE ON in SQL*Plus for all my procedure executions.

Is there a way to do the same thing for procedures?

-Phil Tsao
ptsao_at_sequeltech.com

Thomas J. Kyte wrote:
>
> You would be looking for sql_trace and timed_statistics.
>
> If you set timed_statistics = true in you init$ORACLE_SID.ora file and bounce
> the instance, you can in sql*plus (or any 3gl/4gl/etc) issue something like:
>
>
> SQL> alter session set sql_trace=true;
>
> Session altered.
>
> SQL> select count(*) from scott.emp, scott.dept
> 2 where emp.deptno = dept.deptno and dept.loc like 'C%';
>
> COUNT(*)
> ----------
> 6
>
> SQL> exit
>
> Then, run tkprof on the resulting trace file to get a listing like:
> ------------------------------------------------------------------------
> select count(*) from scott.emp, scott.dept
> where emp.deptno = dept.deptno and dept.loc like 'C%'
>
> call count cpu elapsed disk query current rows
> ------- ------ -------- ---------- ---------- ---------- ---------- ----------
> Parse 1 0.15 0.15 2 0 2 0
> Execute 1 0.00 0.00 0 0 0 0
> Fetch 1 0.00 0.01 3 34 2 1
> ------- ------ -------- ---------- ---------- ---------- ---------- ----------
> total 3 0.15 0.16 5 34 4 1
>
> Misses in library cache during parse: 1
> Optimizer goal: CHOOSE
> Parsing user id: 449 (TKYTE)
>
> Rows Execution Plan
> ------- ---------------------------------------------------
> 0 SELECT STATEMENT GOAL: CHOOSE
> 0 SORT (AGGREGATE)
> 6 NESTED LOOPS
> 11 TABLE ACCESS (FULL) OF 'EMP'
> 11 TABLE ACCESS (BY ROWID) OF 'DEPT'
> 11 INDEX (UNIQUE SCAN) OF 'SYS_C0023259' (UNIQUE)
> --------------------------------------------------------------------------
>
> This will work for pl/sql and sql. Read up on sql_trace and tkprof in the
> applications developer guide (i think thats where tkprof is documented).
>
> You want timed_statistics on to get the cpu and elapsed times in the report.
> you could set sql_trace on at the instance level but I prefer to do it session
> by session and selectively trace....
>
> On Wed, 01 Jan 1997 04:01:50 GMT, zlm101_at_psu.edu (Z. Martinez) wrote:
>
> >Does anybody know how do benchmarking on SQL and PL/SQL scripts?
> >
> >I want to know how much load the scripts put on the system.
> >I'm mainly concerned about CPU and DISK I/O scores.
> >
> >I'm currently running on HP-UX with Oracle 7.1.
> >
> >I appreciate any information on this matter.
> >
> >Thanks in advance.
> >
> >Please send your response to zlm101_at_psu.edu
>
> Thomas Kyte
> Oracle Government
> tkyte_at_us.oracle.com
>
> http://govt.us.oracle.com
>
> ---- Check out Oracle Governments web site! -----
> Follow the link to "Tech Center"
> and then downloadable Utilities for some free software...
>
> -------------------
> statements and opinions are mine and do not necessarily
> reflect the opinions of Oracle Corporation
  Received on Wed Jan 08 1997 - 00:00:00 CST