HASH UNIQUE "producing" millions of cr blocks out of nowhere

From: Stefan Knecht <knecht.stefan_at_gmail.com>
Date: Thu, 13 Nov 2008 13:46:46 +0100
Message-ID: <486b2b610811130446w19b4b421i2e07000bcb5c7f54@mail.gmail.com>


Hello list

I'm sort of stuck looking at this:

PARSING IN CURSOR #11 len=40 dep=0 uid=0 oct=3 lid=0 tim=28320555905074 hv=1189208669 ad='106ff490'
select count(*) from es.ooc_cat_hardware END OF STMT
PARSE #11:c=0,e=607,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=28320555904982 BINDS #11:
EXEC #11:c=0,e=978,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=28320555906355 WAIT #11: nam='SQL*Net message to client' ela= 8 driver id=1650815232
#bytes=1 p3=0 obj#=8707 tim=28320555906513
*** 2008-11-13 12:12:35.734
FETCH
#11:c=183220000,e=180210773,p=0,cr=2224255,cu=0,mis=0,r=1,dep=0,og=4,tim=28320736117374
WAIT #11: nam='SQL*Net message from client' ela= 656 driver id=1650815232
#bytes=1 p3=0 obj#=8707 tim=28320736123055
FETCH #11:c=0,e=8,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=28320736123369 WAIT #11: nam='SQL*Net message to client' ela= 3 driver id=1650815232
#bytes=1 p3=0 obj#=8707 tim=28320736123449
*** 2008-11-13 12:13:22.489
WAIT #11: nam='SQL*Net message from client' ela= 45654614 driver id=1650815232 #bytes=1 p3=0 obj#=8707 tim=28320781778129 XCTEND rlbk=0, rd_only=0
WAIT #0: nam='log file sync' ela= 3695 buffer#=3319 p2=0 p3=0 obj#=8707 tim=28320781782931
STAT #11 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=2224255 pr=0 pw=0 time=180210957 us)'

STAT #11 id=2 cnt=4480 pid=1 pos=1 obj=436056 op='VIEW  OOC_CAT_HARDWARE
(cr=2224255 pr=0 pw=0 time=180212109 us)'
STAT #11 id=3 cnt=4480 pid=2 pos=1 obj=0 op='HASH UNIQUE (cr=2224255 pr=0
pw=0 time=180207620 us)'
STAT #11 id=4 cnt=8945 pid=3 pos=1 obj=0 op='HASH JOIN (cr=92 pr=0 pw=0 time=160024 us)'
STAT #11 id=5 cnt=92 pid=4 pos=1 obj=584822 op='INDEX FULL SCAN CAT_HARDWARE_IDX$$_41590014 (cr=1 pr=0 pw=0 time=363 us)' STAT #11 id=6 cnt=8994 pid=4 pos=2 obj=0 op='HASH JOIN (cr=91 pr=0 pw=0 time=95009 us)'
STAT #11 id=7 cnt=41 pid=6 pos=1 obj=584831 op='TABLE ACCESS FULL CAT_RATEPLAN (cr=15 pr=0 pw=0 time=369 us)' STAT #11 id=8 cnt=9097 pid=6 pos=2 obj=584828 op='TABLE ACCESS FULL CAT_PRICE (cr=76 pr=0 pw=0 time=18284 us)'

How can a HASH UNIQUE of approx 150 blocks in total, produce over 2 million consistent reads ? Any idea on how to further diagnose this ?

Thanks in advance

Cheers

Stefan


Stefan P Knecht
Senior Consultant
Systems Engineering

OPITZ CONSULTING Schweiz GmbH
Seestrasse 97
CH-8800 Thalwil

Mobile +41-79-571 36 27
stefan.knecht_at_opitz-consulting.ch
http://www.opitz-consulting.ch

OCP 9i/10g SCSA SCNA


--
http://www.freelists.org/webpage/oracle-l
Received on Thu Nov 13 2008 - 06:46:46 CST

Original text of this message