| Real time speed improvements & autotrace results. [message #493004] |
Fri, 04 February 2011 04:50  |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
Hey all,
I've had a google for this but not got much useful to me - it may be I'm not using the right phrases however.
I've got a query running a select count (*) over a table. The default plan takes in the order of 15 minutes to return, a hinted plan to use a different index takes 3 minutes to return.
Unfortunately I cant get at the index stats and a few other areas which I suspect may be key here.
When running autotrace against the two queries I see fairly different values as one would expect.
Query
select count (*) from fulfilmentitem bfi where created >= sysdate-30 AND bfi.status = 'FA' AND bfi.fulfilmentmethod = 'D'
Slow run
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15 | 33119 (1)|
| 1 | SORT AGGREGATE | | 1 | 15 | |
|* 2 | TABLE ACCESS BY INDEX ROWID| FULFILMENTITEM | 12525 | 183K| 33119 (1)|
|* 3 | INDEX RANGE SCAN | IDX_FULFIL_METHODSTATUS | 250K| | 1786 (1)|
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("BFI"."CREATED">=SYSDATE@!-30)
3 - access("BFI"."FULFILMENTMETHOD"='D' AND "BFI"."STATUS"='FA')
15 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
176301 consistent gets
176035 physical reads
196 redo size
214 bytes sent via SQL*Net to client
244 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Fast run
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15 | 107K (1)|
| 1 | SORT AGGREGATE | | 1 | 15 | |
|* 2 | TABLE ACCESS BY INDEX ROWID| FULFILMENTITEM | 12525 | 183K| 107K (1)|
|* 3 | INDEX RANGE SCAN | IDX_BFI_CREATED | 2678K| | 13526 (1)|
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("BFI"."STATUS"='FA' AND "BFI"."FULFILMENTMETHOD"='D')
3 - access("BFI"."CREATED">=SYSDATE@!-30)
15 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
250924 consistent gets
142429 physical reads
19476 redo size
215 bytes sent via SQL*Net to client
244 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
IDX_FULFIL_METHODSTATUS is across FULFILMENTMETHOD & STATUS in that order.
IDX_BFI_CREATED is on CREATED and is approx 70% of the size of the other index
The row counts estimated in the explain plan are out, the count(*) comes in at 32.8k rows.
As you will have seen, the fast run shows a pretty significant consistent get increase compared to the slow run and a decent though not dramatic physical read drop.
My uncertainty is around if these changes in consistent get/phys read values would typically be enough to suggest the real time improvements I'm observing or if other (albeit perhaps temporary) factors are involved. It is a prod OLTP environment so the data will be rapidly changing and that may be a factor - again I cant say for sure, which is why I'm asking.
I know it can never be an exact science without intimately knowing the hardware/current loads etc but I also know that there's enough experience on these boards to have a loose handle on if the time shifts between queries are likely (or not) to be reflective of the stat changes or if those differences alone shouldn't (or typically wouldn't account) for it.
In short - I'm thinking about instructing the query to ignore its original plan but am hesitant to do so without being a little more confident that it's not just a timing thing or something other than the change of index approach which may be causing the improvement. Its a pretty good improvement time, however, based exclusively the autotrace stat changes observed I couldn't put my hand on heart say "yup - that change is good, ignore the default index all the time for this job".
I appreciate I don't have all the data which is necessary to even being to expect a firm answer - a steer will do me fine for the time being, I'm not expecting much more than that given the lack of data I'm able to provide.
As always, any assistance gratefully received.
|
|
|
|
| Re: Real time speed improvements & autotrace results. [message #493044 is a reply to message #493004] |
Fri, 04 February 2011 06:27  |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
One rock solid recommendation: raise the value of the optimizer_dynamic_sampling parameter, if that hasn't been done already. The 9i default is 1, I would always raise to at least 2 (the 10g/11g default), usually to 4. You would need 4, because your predicate has three columns. I have seen astronomical improvements from this, specially as you think that your statistics may be wrong. I would do it at the session level (or as a hint) then if it works put in the change request to do it for the system.
|
|
|
|
|
|
| Re: Real time speed improvements & autotrace results. [message #493046 is a reply to message #493045] |
Fri, 04 February 2011 06:57 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
Plan has remained unchanged as has performance. Which I guess makes sense - its original plan does use the more selective index as one would hope.
Perhaps I'm hearing hooves and thought zebras, perhaps it may be as "simple" as its quicker to scan a less selective 11gb index as it is to scan a more selective 16gb index...
|
|
|
|
|
|
| Re: Real time speed improvements & autotrace results. [message #493051 is a reply to message #493049] |
Fri, 04 February 2011 07:21 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
A couple, though none will hold true for long I hope.
*I'm new here, I don't know the data models/other interactions well. I'm wary of inadvertently breaking other things at the expense of fixing this - that would be a VeryBadThing™. Which leads me to my next challenge....
*I lack a dev area to test it properly on, hoping to resolve that one very soon, but that means proving its a good idea is...difficult.
|
|
|
|
| Re: Real time speed improvements & autotrace results. [message #493223 is a reply to message #493004] |
Sun, 06 February 2011 21:45 |
hkchital
Messages: 128
Registered: September 2008
Location: Singapore
|
Senior Member |
|
|
Note that the expected cost of using the index on CREATED is much higher -- Oracle expects to have to read very many more entries from this index before filtering on FULFILMENTMETHOD and STATUS.
The real "solution" would be to have an index on all three columns together.
However, for the present you could run the execution with Plan Statistics :
select /*+ gather_plan_statistics index (BFI IDX_FULFIL_METHODSTATUS) */
from fulfilmentitem bfi where created >= sysdate-30 AND bfi.status = 'FA' AND bfi.fulfilmentmethod = 'D'
/
select * from table(dbms_xplan.display_cursor('','','ALLSTATS LAST'));
select /*+ gather_plan_statistics index (BFI IDX_BFI_CREATED) */
from fulfilmentitem bfi where created >= sysdate-30 AND bfi.status = 'FA' AND bfi.fulfilmentmethod = 'D'
/
select * from table(dbms_xplan.display_cursor('','','ALLSTATS LAST'));
and review the results.
Hemant K Chitale
|
|
|
|
| Re: Real time speed improvements & autotrace results. [message #493297 is a reply to message #493223] |
Mon, 07 February 2011 04:48 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
Oracle is right, at least in the selectivity stakes, CREATED is by far the less selective index. But its also the physically smaller index.
I couldn't get that query to run, came back with ORA-00904: "DBMS_XPLAN"."DISPLAY_CURSOR": invalid identifier |
|
|
|
|
|
|
|
|
|
| Re: Real time speed improvements & autotrace results. [message #493373 is a reply to message #493367] |
Mon, 07 February 2011 09:43 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
Thanks anyhow 
For what its worth, I'm running tests again today utilising both indexes are permutations and it is still consistently quicker to go via the index on created.
I'll post back if there's anything else noteworthy I run across for future reference or if I ever get stats back on the indexes - the best conjecture I can come up with at the moment with the limited data available is the size differences are enough that the difference in selectivity cant make the time gap up.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
