Re: Understanding TKPROF output

In looking at the explain plan below of your statement and the table of information produced by the tkprof, the statement needs to have a hint added to it. Something like:
  SELECT /*+INDEX(AINST 'THE NAME OF THE PRIMARY KEY or index')*/

         ATTR.KEY
  FROM
   ATTRIBUTE ATTR,ATTR_INST AINST,SERV_INST SI,PROD_USR PU   WHERE
   ATTR.UNIQUE_KEY = :b1 AND AINST.ATTRIBUTE = ATTR.KEY AND AINST.VALUE
 =
   :b2 AND AINST.KEY != :b3 AND SI.KEY = AINST.SERVINST AND PU.KEY =    SI.PRODINST AND PU.STATE != 'I' AND ROWNUM = 1 If you look at the explain plan, in particular the following line:

     126 TABLE ACCESS (FULL) OF 'ATTR_INST' you are doing a full table scan on the ATTR_INST table. Then looking at the other info produced by tkprof, look at the columns disk and query, the statement is performing a large number of logical reads for only retrieving a small number of rows. If you add a hint to your statement, run it through an explain plan to check that it is not doing a full table scan, the performance of the statement should be enhanced.

Eric Peterson
Programmer/Analyst DBA
Maurices Inc.
eric_peterson_at_maurices.inrg.com

In article <37FB0A4A.E03ECC54_at_spin.ch>,   sjo_at_spin.ch wrote:
> Hello
>
> I'm working with Oracle 8.0.5 on Linux/x86/SUSE6. Right now
> I'm experiencing performance problems, but which most likely go
> down to inefficient statements and unsufficient database tuning
> using indexes and other methods. Therefore I would like to use
> TKPROF to see, where I can do something useful. Unfortunately
> it's not easy to understand its output, even not after reading
> the manuals. Therefore I hope somebody can give me some hints.
> I would like to paste the statistics for one statement here.
> It's a statement located in a trigger, used to do some checks
> for uniqueness, which can't be done using simple constraints.
> The 'rownum = 1' condition is there to allow to use the 'into'
> clause, even if more rows satisfy all other conditions. If a
> row is returned, than an exception is raised, if no data is
> found, then the trigger goes on (NO_DATA_FOUND is caught).
>
> --------
>
> SELECT ATTR.KEY
> FROM
> ATTRIBUTE ATTR,ATTR_INST AINST,SERV_INST SI,PROD_USR PU WHERE
> ATTR.UNIQUE_KEY = :b1 AND AINST.ATTRIBUTE = ATTR.KEY AND
AINST.VALUE
> =
> :b2 AND AINST.KEY != :b3 AND SI.KEY = AINST.SERVINST AND PU.KEY =
> SI.PRODINST AND PU.STATE != 'I' AND ROWNUM = 1
>
> call count cpu elapsed disk query
> current rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 0.00 0.00 0 0
> 0 0
> Execute 7 0.00 0.00 0 0
> 0 7
> Fetch 7 0.10 0.10 2267 2283
> 21 0
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 15 0.10 0.10 2267 2283
> 21 7
>
> Misses in library cache during parse: 1
> Optimizer goal: CHOOSE
> Parsing user id: 27 (SPIN2) (recursive depth: 2)
>
> Rows Execution Plan
> ------- ---------------------------------------------------
> 0 SELECT STATEMENT GOAL: CHOOSE
> 0 COUNT (STOPKEY)
> 0 NESTED LOOPS
> 1 NESTED LOOPS
> 1 NESTED LOOPS
> 126 TABLE ACCESS (FULL) OF 'ATTR_INST'
> 1 TABLE ACCESS (BY INDEX ROWID) OF 'SERV_INST'
> 1 INDEX (UNIQUE SCAN) OF 'SYS_C009771' (UNIQUE)
> 1 TABLE ACCESS (BY INDEX ROWID) OF 'PROD_USR'
> 1 INDEX (UNIQUE SCAN) OF 'SYS_C009745' (UNIQUE)
> 1 TABLE ACCESS (BY INDEX ROWID) OF 'ATTRIBUTE'
> 1 INDEX (UNIQUE SCAN) OF 'SYS_C009782' (UNIQUE)
>
> ---------
>
> SQL> desc attr_inst
> Name Null? Type
> ------------------------------- -------- ----
> KEY NOT NULL NUMBER(38)
> SERVINST NOT NULL NUMBER(38)
> ATTRIBUTE NOT NULL NUMBER(38)
> VALUE VARCHAR2(2048)
> CONFIRMED CHAR(1)
> NOTRIGGER CHAR(1)
> CONS_VALUE VARCHAR2(2048)
>
> ----------
>
> There are currently no indexes on attr_inst other than those
> implicitely generated.
>
> Here come my questions:
>
> a) At the time this statement was executed, there were no more
> than 18 rows in the table ATTR_INST. Nevertheless the execution
> plan says that 126 rows were processed in the inner loop. I see
> that 126 = 7*18, and 7 is also displayed in the count column.
> What does all that mean? Why are 126 rows processed, if only
> 18 are present?
>
> b) I have a problem understanding some of the fields of the output,
> especially the 'count' column in conjunction with 'Execute'/'Fetch'.
> The doc says 'Number of times a statement was
parsed/executed/fetched'.
> A statement is fetched 7 times? What does that mean? Or what does it
> mean, that the statement was executed 7 times? Why is it executed
> 7 times?
>
> c) At the time this statement was executed, there should be
> no more than about 200-300 bytes totally in ATTR_INST. So
> what could be the reason for the enormous amount of blocks
> fetched, even from disk (block size is 4K)? Sure, the table
> has large varchar2 fields, but I thought that these values
> only consume as much space as they actually need?
>
> d) I read somewhere, that the order of the joins can have a major
> impact on performance. I would really like to reorder the joins,
> but I even don't understand, how the order of the joins in the
> statement fits to the execution plan, where the order is something
> quite different. What are good rules concerning join ordering in
> SQL statements?
>
> e) I have quite some difficulties to 'read' the execution plan
> above. In the manuals I found the description of the keywords, but
> again not easy to understand for me. Particularly I have a problem
> in seeing the order in which the operations are done, especially
> concerning those 'loops'. It would be fine, if someone
> could translate this plan into a bit of english text or some sort
> of pseudo code.
>
> Thanks in advance
>
> bye
> --
> Sam Jordan
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Oct 06 1999 - 07:47:50 CDT