| (probably) basic CBO question [message #479042] |
Thu, 14 October 2010 06:49  |
chris32680
Messages: 92
Registered: January 2006
Location: Charlotte
|
Member |
|
|
Hey guys,
We're working on upgrading our 9i db to 11g, so we built a separate, comparable machine to do our testing on.
We imported our full database and we've been running mostly our larger batch jobs to see what kind of performance change there is.
One large update in particular has me perplexed, and i was hoping maybe you could push me in the right direction for some info.
The first time i ran the update, it took hours. It took so long in fact that i ended up just killing the session so that i could come back to it and check it out.
Well, the next time i ran the update, it only took 10 minutes. And each subsequent time i ran it, it only took ~10 minutes.
I do know that a work table that was populated for the update did get statistics calculated on it AFTER the initial test run...but i ran the delete_table_stats proc against that table to clear the stats to try and reproduce the really bad performance.
I can supply the tracing and explain plan info if neeeded, but my main question is, is there a feature of the cbo that would explain why the first run was so dreadfully slow, but each one after that would be very efficient?
I tried searching through metalink but wasn't able to find anything that answered my question.
Thanks all!
edit: and for clarity, we were/are still running the rules based optimzer on our 9i production box.
[Updated on: Thu, 14 October 2010 07:09] Report message to a moderator |
|
|
|
| Re: (probably) basic CBO question [message #479089 is a reply to message #479042] |
Thu, 14 October 2010 09:37  |
Yasir Hashmi
Messages: 304
Registered: April 2006
|
Senior Member |
|
|
Any DML occuring in the database happens to be done in buffer cache.So the first time you did update,all the blocks are fetched from disk into the buffer cache.This required physical I/O which is expensive than logical I/O.The next time you ran the update,the buffers were found in the cache hence doing logical I/O which is very faster than disk access.
So what you have seen is a normal behavior IMO. As for the update statement,post the explain plan and check for statistics and indexes for columns mentioned in where clause.
|
|
|
|
| Re: (probably) basic CBO question [message #479091 is a reply to message #479089] |
Thu, 14 October 2010 09:40 |
chris32680
Messages: 92
Registered: January 2006
Location: Charlotte
|
Member |
|
|
Thanks Yasir.
That's actually what i was assuming, i just couldn't seem to find any validation of that.
I'll look into it some more and see what i can find out...and post more info if i'm unable to figure it out.
|
|
|
|
| Re: (probably) basic CBO question [message #479148 is a reply to message #479091] |
Thu, 14 October 2010 14:20 |
chris32680
Messages: 92
Registered: January 2006
Location: Charlotte
|
Member |
|
|
could someone possibly explain to me the "time" column in the autotrace info? The time on the update statement says 27+ minutes, but the actual update only took ~9 minutes.
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1040K| 20M| 8336K (13)| 27:47:17 |
| 1 | UPDATE | INVENTORY | | | | |
|* 2 | TABLE ACCESS FULL | INVENTORY | 1040K| 20M| 10271 (4)| 00:02:04 |
| 3 | SORT AGGREGATE | | 1 | 32 | | |
| 4 | NESTED LOOPS | | | | | |
| 5 | NESTED LOOPS | | 1 | 32 | 7 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| EOM_INVENTORY_WORK | 1 | 12 | 4 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | IEOM_INV_WORK | 1 | | 3 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | UI_ITEM_DEMAND | 1 | | 2 (0)| 00:00:01 |
|* 9 | TABLE ACCESS BY INDEX ROWID | ITEM_DEMAND | 1 | 20 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Thanks again.
EDIT: wait, am i looking at that wrong? does that say 27 hours???
[Updated on: Thu, 14 October 2010 14:50] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
