Re: Tkprof73 question

From: <ddf_dba_at_my-deja.com>
Date: 2000/06/15
Message-ID: <8ian2q$qhm$1@nnrp1.deja.com>#1/1

In article <8i9s8o$7bo$1_at_nnrp1.deja.com>,   pmartin_at_mitsa.ch wrote:
> Hello
>
> I'm using tkprof73 to find the bottleneck queries, however I don't
> understand the query or the current values.
>
> Could somebody please explain what a consistent read is
> and what the current mode is and how inportant are the
> query and current values to interpreting the results
>
> Many thanks
>
> Paul
>

Paul,

Don't feel discouraged -- tkprof output is not the easiest to read and interpret. Let's get to your questions and hopefully shed some light on the matter.

We'll take the 'current mode' questions first. The 'current' statistic is the number of buffers retrieved in current mode, which is the mode utilized when INSERT, UPDATE and DELETE calls are issued. Current mode ensures that the data is current throughout the life of the transaction, especially for updates, i.e., the data is acquired for update and locked so that no other transaction or process may alter the data during the update. Inserts and deletes are a bit different but still require current mode; the buffers acquired are locked so that the inserted or deleted data is current throughout.

The 'query' statistic is the number of blocks retrieved in consistent mode, utilizing the SCN (System Change Number) to ensure that the data has not been altered since the query began.

Perhaps it will be helpful to you to examine sample tkprof output and interpret the results.

Given the following tkprof output let's discover how this session is running:

   SELECT * FROM emp, dept WHERE emp.deptno = dept.deptno;

   call count cpu elapsed disk query current rows

• ------- ------- --------- -------- -------- ------- ------ Parse 11 0.08 0.18 0 0 0 0 Execute 11 0.23 0.66 0 3 6 2 Fetch 35 6.70 6.83 100 12326 2 824

  ---

  total 57 7.01 7.67 100 12329 8 826

   Misses in library cache during parse: 0

   10 user SQL statements in session.
    0 internal SQL statements in session.    10 SQL statements in session.

There are several problems evident from this output. First there were 10 SQL statements in the session, but 11 parse calls. This 1 extra call was probably unnecessary.

The 'execute' statistics indicate that 2 rows were inserted, updated or deleted in this session and the 'fetch' statistics indicate that 824 rows were returned from a SELECT statement. Since 824 rows were returned, and only 35 fetches were performed array fetch operations were executed.

The 'disk' statistics show that 100 disk blocks were read from the datafiles for the session. The fact that 12329 query buffers were used probably indicates that the same data blocks were continually revisited throughout the session, not the best situation.

Remember that the SQL statement listed is not the only statement executed during this session, just the only one shown in this excerpt.

Knowing that the session was resource intensive the next step would be to discover which of the 10 SQL statements contributed to the problem. Usually output like this is the result of one or two SQL statements that are inefficient. EXPLAIN PLAN is a good tool to use to discern resource-intensive SQL. In addition statements can be explained through tkprof using the 'explain=user/password' command-line parameter -- tkprof myfile.trc myfile.out explain=scott/tiger. As long as PLAN_TABLE exists each SQL statement in the tkprof output will be explained. To illustrate:

   SELECT * FROM emp, dept WHERE emp.deptno = dept.deptno;

   call count cpu elapsed disk query current rows

• ------- ------- --------- -------- -------- ------- ------ Parse 1 0.16 0.29 3 13 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.03 0.26 2 2 4 14

   Misses in library cache during parse: 1    Parsing user id: (8) SCOTT

   Rows Execution Plan

• --------------------------------------------------- 14 MERGE JOIN 4 SORT JOIN 4 TABLE ACCESS (FULL) OF 'DEPT' 14 SORT JOIN 14 TABLE ACCESS (FULL) OF 'EMP'

Note that this is the same SQL statement listed in the problematic tkprof statistics and that these statistics are quite reasonable. You can also see the query plan for the listed SQL; apparently both tables are short so full table scans are acceptable -- in fact, the optimizer may choose a full table scan over an index scan if it results in less 'work' for Oracle.

I hope this helps with your statement tuning.

David Fitzjarrell
Oracle Certified DBA

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Jun 15 2000 - 00:00:00 CDT