Strange behaviour of Cramer query
Date: Wed, 24 Jun 2009 05:41:18 -0700 (PDT)
Message-ID: <9083b7ff-8e4c-4051-8619-ef1881320ba3_at_v2g2000vbb.googlegroups.com>
OS: Solaris 5.10
Oracle: 10.2.0.2
Application: Cramer see http://en.wikipedia.org/wiki/Cramer_Systems
I was ask to look at a performance issue on a database used for Cramer.
A stored procedure is executed which executes a couple of queries and returns some data.
Like :
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.33
After a couple of tries the Elapsed time goes up to 20 minutes. So no
caching issues. Because
After cancelling the next execution is quick again.
I did traced both cases.
The strange thing is that execution plan is in both cases the same
except for the numer of rows returned by the steps.
The explain plan is 228 lines long so I will not copy in the complete
plan but just the last lines.
Fast execution:
0 BUFFER SORT (cr=0 pr=0 pw=0 time=0 us)
0 INDEX FAST FULL SCAN CC_UK (cr=0 pr=0 pw=0 time=0 us)(object id
191358)
0 TABLE ACCESS BY INDEX ROWID CIRCUIT (cr=0 pr=0 pw=0 time=0 us)
0 INDEX UNIQUE SCAN CCT_PK (cr=0 pr=0 pw=0 time=0 us)(object id
191354)
3 TABLE ACCESS BY INDEX ROWID SERVICEOBJECT (cr=12 pr=0 pw=0 time=116
us)
3 INDEX RANGE SCAN SEROBJ_OBJ_I (cr=9 pr=0 pw=0 time=69 us)(object id
192668)
3 TABLE ACCESS BY INDEX ROWID SERVICE (cr=9 pr=0 pw=0 time=60 us)
3 INDEX UNIQUE SCAN SERV_PK (cr=6 pr=0 pw=0 time=38 us)(object id
192658
Slow execution:
9112536 BUFFER SORT (cr=149 pr=18090 pw=90 time=1151263 us)
45336 INDEX FAST FULL SCAN CC_UK (cr=149 pr=0 pw=0 time=70 us)(object
id 191358)
9112536 TABLE ACCESS BY INDEX ROWID CIRCUIT (cr=18225074 pr=0 pw=0
time=68090494 us)
9112536 INDEX UNIQUE SCAN CCT_PK (cr=9112538 pr=0 pw=0 time=31488868
us)(object id 191354)
3 TABLE ACCESS BY INDEX ROWID SERVICEOBJECT (cr=12 pr=0 pw=0 time=269
us)
3 INDEX RANGE SCAN SEROBJ_OBJ_I (cr=9 pr=0 pw=0 time=110 us)(object id
192668)
3 TABLE ACCESS BY INDEX ROWID SERVICE (cr=9 pr=0 pw=0 time=87 us)
3 INDEX UNIQUE SCAN SERV_PK (cr=6 pr=0 pw=0 time=56 us)(object id
192658)
v$sql_plan shows only 1 plan.
Does anyone have clue?
Statistics cannot be the issue because the remain the same between
executions.
regards HansP Received on Wed Jun 24 2009 - 07:41:18 CDT