Oracle Profiling

[Nicolas Dumeige]

Hello,

Do you any tool to do SQL profiling, i.e. get sequence and cost of an SQL statement execution for Oracle. something like that :
...
fetch 1581 blocks from table EMPLOYEE 19 s
parellel sort on 1581 blocks from table EMPLOYEE 7 s
group by on
...

I'm aware you can get explanation plan, that's not exactly what i'm looking for. I want to see where the time is lost in order to work on the real case and not prevsion on the estimated cost of each operation. I mean, when you read FULL SCAN EMPLOYEE, you can apply some I/O throughoutput estimation X nomber of block to read and get an estimated time. But that a lot better to know by observation that this specific access costed 27 seconds.

Any idea is welcomed

Thank in advance

Nicolas

[James A. Donovan]

Oracle itself has a couple of very good profilers called: SQL-trace and tkprof.

To get timings, you need to set timed_statistics=TRUE in your init.ora file.

http://www.lc.leidenuniv.nl/awcourse/oracle/server.920/a96533/sqltrace.htm

[Nicolas Dumeige]

Jim,

Thanks for your answer, but again, that's not exactly what I'm looking for.
How do you make the link between Parse/Execute/Fetch and the explain plan if the rq is a lot more complicated and ugly than the SELECT statement used as exemple ?

As for me, a more immediate output would look like a process trace with the time delta between each call with extra info on the block manipulated, (something like truss -D on Solaris).

Cheers

Nicolas

[Navin Bhat_2]

You could try using tusc but then you will not be looking at parse/execute/fetch, but the system calls that could affect the same fuction for e.g a read system call could be eqv. of the fetch. You can use the -ccc -T options for timing info. But again I am not sure this is a good way of profiling. There are other tools like gprof etc... to profile the entire application in HP. Oracle support might be able to give you SQL specific tools if tkprof is not what you are looking for.

[Nicolas Dumeige]

Navin,

Maybe I did not express myself with clarity. What I meant was that to me it lacks a tool that display info on the Oracle calls (cost/time). I wouldn't dream of using a trace utility on an Oracle process to get this information.

My question is about SQL profiling / tuning.

Cheers

Nicolas

[James A. Donovan · Accepted solution]

From what you are saying I would think tkprof would be what you want, but there is one other Oracle tool I could suggest downloading. The TRCANLZR suite of scripts from Metalink does some more powerful analysis of SQL trace files than tkprof can by itself.

http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=224270.1

Purpose

Given a Raw SQL Trace with Binds and/or Waits, generated by EVENT 10046 Levels 1, 4, 8 or 12, the Trace Analyzer generates a comprehensive report that can be used to troubleshoot common SQL performance issues, or functional issues requiring to find the values of bind variables.

The Trace Analyzer report includes, for any given Raw SQL Trace:

1. Total User Elapsed Time (actual); Accounted Elapsed and CPU Times (same as TKPROF); WAIT Times (IDLE and NON-IDLE).
2. Top 5 expensive SQL in terms of CPU, Elapsed, non-idle and idle wait times.
3. Gaps of no tracing activity; Oracle Errors; End of Transactions (COMMIT or ROLLBACK).
4. Summary of CALLs (PARSE, EXECUTE, FETCH) by USER/INTERNAL and RECURSIVE/NON-RECURSIVE (similar to TRCSUMMARY and last page of TKPROF).
5. Summary of CALLs per Command Type (SELECT, INSERT, etc.) by USER/INTERNAL and RECURSIVE/NON-RECURSIVE.
6. Summary of WAITs (IDLE and NON-IDLE) by USER/INTERNAL and RECURSIVE/NON-RECURSIVE (similar to TRCSUMMARY).
7. Hottest 5 blocks (most times waited for)
8. Summary of CPU, Elapsed Time and WAITs per SQL Statement (CURSOR).
9. Summary of Physical Reads, Logical Reads, Number of Rows and Library Cache Misses per SQL Statement (CURSOR).
10. For each SQL Statement (CURSOR).
1. CURSOR_ID, Length, Address, Hash Value, Optimizer Goal, USER, Parsing Errors.
2. SQL Statement Text.
3. CALLs Summary, including CALLs Count, CPU Time, Elapsed Time, Physical Reads (disk), Logical Reads (query and current), Rows, and Library Cache Misses.
4. Row Source Plan.
5. Explain Plan (if SQL Trace is processed on same Instance where it was generated). Includes execution order.
6. List of tables being accessed by SQL according to Explain Plan, and indexes for these tables. Includes CBO Stats: number of rows, blocks, sample size and last analyzed date.
7. For listed indexes, their indexed columns and if the index is unique or not.
8. WAIT EVENTs and corresponding details (ex: Table/Index names and count of accessed blocks).
9. Bind Variables Values used for each Execution.
10. Execution Performance of each set of Bind Variables Values.

[Nicolas Dumeige]

Many thanks Jim for this sharp infos.