Hi,
Firstly I'm not sure if this should be in "Database - General" or "SQL and PL/SQL". Since it's more of a performance tuning question than specifically about the PL/SQL, I'm going to put it here in "Database - General". I hope that doesn't offend anyone.
I've just started looking at a reported performance problem in our app. One of the developers set me up a procedure that replicates the issue, I ran it while tracing the session and then fed the trace file to tkprof. The results at the bottom of my tkprof output file look like this:
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.01 0.07 10 60 0 0
Execute 3 0.01 0.01 0 3 0 3
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.03 0.08 10 63 0 3
Misses in library cache during parse: 1
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 5 0.00 0.00
SQL*Net message from client 4 1.68 1.70
db file sequential read 18 0.01 0.10
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 416 0.00 0.01 0 0 2 0
Execute 1456 0.71 0.75 26 1739 425 590
Fetch 2932 0.12 2.21 337 6338 0 3061
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4804 0.84 2.98 363 8077 427 3651
Misses in library cache during parse: 25
Misses in library cache during execute: 24
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 343 0.08 2.09
db file scattered read 1 0.00 0.00
47 user SQL statements in session.
888 internal SQL statements in session.
935 SQL statements in session.
31 statements EXPLAINed in this session.
I'm looking in particular at that relatively high activity for RECURSIVE STATEMENTS, because to me, the NON-RECURSIVE (ie the actual submitted statements that form our code) looks pretty harmless. This is my first experience of trying to tune some complex looking PL/SQL and I've no idea what could be considered more "normal", but from what I think I know, and google searches, the results look quite odd to me.
Is this high activity for RECURSIVE STATEMENTS a problem, and if so, what should I start looking at to reduce that activity?
Regards,
Ados