| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: How to benchmark SQL and PL/SQL scripts??
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:
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
 |
 |