Re: HELP on performance

Ian,

There shouldn't be any non-zero values in the "Rows" column for the "Execute" phase for a query of that type. Only DML statements (i.e. INSERT, UPDATE, and DELETE) and SELECT ... FOR UPDATE should tally up row counts during the "Execute" phase. That cell should show a zero value...

I suspect ratty data, in other words something unusual in the ".trc" file. Can you look up the cursor number of the query in question, by finding the text of the SQL statement in the ".trc" file and then referencing the cursor number on the line just above, in the phrase "PARSING IN CURSOR #"? Please also not the number of the line in the file where this takes place?

Then, come out of the editor to use the UNIX "grep" utility as follows:

    $ grep -n "^EXEC #nnn" <trace-file-name> | more

where "nnn" is the cursor number from the SQL statement. In each line displayed, the number of rows should be represented in the field "r=nnn" somewhere in the middle of the line. It should say "r=0", but if it says something else, that would interesting.

Another possibility is that TKPROF is being fooled. Another check to run is the following:

    $ grep -n "^PARSING IN CURSOR #nnn" <trace-file-name>

This should show only the single line you found when you edited the file to search for the SQL statement. If more than one such line shows, then it appears that the cursor number is being used more than once. Normally, this should not "confuse" TKPROF, but I also couldn't help noticing that your "Parse" statistics show no parsing having taken place, which is odd to say the least...

Here is what I think happened:

• you had started SQL tracing this process in mid-stream, utilizing ORADEBUG utility, the DBMS_SYSTEM package's SET_EV or SET_SQL_TRACE_IN_SESSION procedures
• since SQL trace was not started from the beginning, another SQL statement (a DML statement or a SELECT ... FOR UPDATE) had started off using the same cursor number in the trace file as the SQL statement you are looking at
• TKPROF is summarizing those statistics in with this SQL statement
• if this is true, the "grep -n" command should show "EXEC #nnn" lines preceding the "PARSING IN CURSOR #nnn" line you looked at

Please let us know what you find?

Hope this helps...

-Tim

on 5/12/03 4:56 AM, Biddell, Ian at ian.biddell_at_hp.com wrote:

> Hi Stephane,
>
> Thanks for replying to my query.
> I was wondering what exactly you meant by a reprogrammed nested loop?
> Why would the calling loop affect the "Rows" count in this particular
> queries trace output?
>
> Thanks Ian
>
> SELECT FT.RATE_ID,TO_CHAR(FT.PERIOD_START_DATE,'YYYY-MM-DD'),
> TO_CHAR(FT.PERIOD_END_DATE,'YYYY-MM-DD'),RS.REVENUE_TYPE_TCODE
> INTO:b1,:b2,
> :b3,:b4
> FROM
> FINANCIAL_TRANSACTION_B FT,RATE_SCHEDULE_B RS WHERE FT.COMPANY_CODE=:b5
> AND
> FT.ACCT_NUM=:b6 AND FT.FIN_TRANS_ID=:b7 AND
> RS.COMPANY_CODE=FT.COMPANY_CODE
> AND RS.RATE_ID=FT.RATE_ID
>
>
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 0 0.00 0.00 0 0 0
> 0
> Execute 613070 177.44 175.13 0 0 0
> 3766855855
> Fetch 613070 418.84 878.44 82220 4904560 0
> 613070
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 1226140 596.28 1053.57 82220 4904560 0
> 3767468925
>
> Misses in library cache during parse: 0
> Optimizer goal: CHOOSE
> Parsing user id: 19 (SVCAUGRANGEAP)
>
> Rows Execution Plan
> ------- ---------------------------------------------------
> 0 SELECT STATEMENT GOAL: CHOOSE
> 0 NESTED LOOPS
> 0 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF
> 'FINANCIAL_TRANSACTION_B'
> 0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
> 'FINANCIAL_TRANSACTION_PK' (UNIQUE)
> 0 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'RATE_SCHEDULE_B'
> 0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'RATE_SCHEDULE_PK'
> (UNIQUE)

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tim Gorman
  INET: tim_at_sagelogix.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).