Strange behaviour of Cramer query

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
.

Relevant Pages

  • Update statement in trigger gets bad execution plan
    ... -- Columns 3 through 10 snipped from Usenet post ... I had about 7,800 rows in GenRgl ... bad choice of execution plan for this statement. ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL Server Query Analyzer faster than ADO.Net SQL Data Provider
    ... Consider a stored proc defined as follows: ... In 6.5, at compile time SQL ... when compiling a plan. ... execution plans, but a key requirement for everything to work as expected ...
    (microsoft.public.sqlserver.programming)
  • Re: Query Plan
    ... get the query plan in cache for a given stored procedure. ... execution plan for a different stored procedure. ... Links for SQL Server Books Online: ...
    (microsoft.public.sqlserver.server)
  • stored procedure occassionally running 10x slower???
    ... But there are some "anomalies" happening when this stored procedure is ... Using SQL Profiler, ... anomalies and normal execution). ... I tried to "capture" the execution plan for that particular query, ...
    (microsoft.public.sqlserver.programming)
  • Stored Procedure occasionally running 10x slower???
    ... But there are some "anomalies" happening when this stored procedure is ... Using SQL Profiler, ... anomalies and normal execution). ... I tried to "capture" the execution plan for that particular query, ...
    (microsoft.public.sqlserver.programming)