Sort blocks.
Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic, Dec 9-11 Honolulu - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas - Jonathan Lewis' Optimising Oracle, Nov 19-21 Dallas -----Original Message----- Rich Sent: Tuesday, November 12, 2002 2:19 PM To: Multiple recipients of list ORACLE-L Hey all, As I'm doing some perf tuning on a procedure using a 10046 trace with tkprof (8.1.7 on HP/UX 11.0). One of the queries from the tkprof has the following output: ----- SELECT QPM.PRODUCTLINE PL,MIN(PLN.PLANNERNO) PNO FROM VISIB.QT_PRODUCTLINE_MEMBERS QPM,VISIB.PLANNERS PLN WHERE UPPER(RTRIM(QPM.USERID)) = UPPER(RTRIM(PLN.PLANNER)) AND QPM.PRODUCTLINE = :b1 GROUP BY PRODUCTLINE call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 66491 19.21 20.59 0 0 0 0 Fetch 132982 83.54 90.78 88 332455 531928 66491 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 199474 102.75 111.37 88 332455 531928 66491 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 256 (QT_PRODSCHED) (recursive depth: 1) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 0 SORT (GROUP BY NOSORT) 0 HASH JOIN 0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'QT_PRODUCTLINE_MEMBERS' 0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'PLANNERS' ----- Yes, this is obviously bad by design. What I don't understand is the high "current" count. From the docs, it says that this is normal for DML, but says nothing about what this means for queries. The SELECT statement is defined as a cursor, and there is no "FOR UPDATE OF" clause in the cursor. I've searched through Metalink about this, but haven't had any luck. Does anyone have an explanation? TIA, Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cary Millsap INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).