Oracle-L: RE: High current reads from fetch in query.

From: Cary Millsap <cary.millsap_at_hotsos.com>
Date: Tue, 12 Nov 2002 13:30:18 -0800
Message-ID: <F001.005016C6.20021112133018@fatcity.com>

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
Rich.Jesse_at_qtiworld.com              Quad/Tech International, Sussex, WI

USA

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jesse, Rich
  INET: Rich.Jesse_at_qtiworld.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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Cary Millsap
  INET: cary.millsap_at_hotsos.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).

Received on Tue Nov 12 2002 - 15:30:18 CST