|
-
As I said earlier I used KEEP pool very cautiously. One incident happened few years ago I still remember even today. I had to rebuild 85 indexes one time. All those 85 indexes were created on one table - S_ORG_EXT, a Siebel table. The size of the table was 40GB that had 40 Millions rows.
I had a powerful IBM UNIX servers at that time, and well striped disks on EMC DMX array. The index rebuild scripts (there are 3) was written by me and I used all the available features such as parallel, nologging etc. Each script had 30 indexes to be created. The SGA was increased to 9GB -only for this purpose. When I executed all the 3 scripts concurrently, they ran for 15 hours. I was not satisfied with the run time. Even though the SGA was set to 9GB, all are allocated to DEFAULT pool. What was happening was oracle flushed the buffer cache to accommodate index buffers during final phase of index built. The caused already table block buffers read by the other sessions were flushed, more IO was happening on the disks.
The only way I could keep recently read table blocks into the buffer cache was to give some other area in the SGA for the index buffers.
So I set KEEP pool in my index creation script, and allocated 500MB in the SGA.
And this time, the total run time for the 3 scripts was around 5 hours.
Once index was done, I reset the pool to DEFAULT.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|