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).
