Understanding TKPROF output

From: Sam Jordan <sjo_at_spin.ch>
Date: Wed, 06 Oct 1999 08:37:30 +0000
Message-ID: <37FB0A4A.E03ECC54@spin.ch>

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:

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?
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?
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?
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?
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 Received on Wed Oct 06 1999 - 03:37:30 CDT