-
Hello;
When i tune SQL's i want to see their initial Performance not there cached performance.
For example a query lasts 20 seconds
and then by repeating 0,2 Seconds.
If i change the SQL for tuning many Datablocks are already wating for and smiling at me in the buffer cache.
I do not want to restart tje database.
Refreshing SHARED-POOL will not help too;
Orca
-
Use the following command to flush the shared pool and then try running the query again.
Alter system flush shared pool;
Regards
S.Prabhakar
-
As original poster allready said, using "ALTER SYSTEM FLUSH SHARED POOL" has no impact on database buffer cache whatsoever. There is no such command to flush buffers from buffer cache.
I can't verify the following right now, but I seem to remember to read somewhere that if you take a tablespace offline, all the buffers that belongs to segments from that tablespace are freed from buffer cache. So if you can afford it, take all the tablespaces that are involved in your query offline, put them back online and benchmark your query. However verify first that the above trick realy works, as I never tested it.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Thank you all;
The Db needs to be online, but you could be right this could work.
Ok; i wait for Oracle 10 ii with
ALTER SYSTEM FLUSH BUFFER_POOL [ IMMEDIATE ];
;-))
Orca
-
Look into recycle pool (BUFFER_POOL RECYCLE).Allocate enough blocks so your query runs same time as in a regular pool. Assign your tables to recycle pool. Run your query. Assign object that is not in a query to the recycle pool as well. Run another query against the object which is not in your original query. I assume it will overwrite cached blocks in a pool and if you run your original query it will take same time.
One, who thinks that the other one who thinks that know and does not know, does not know either!
-
Why dont you consider a test database , which is a replica of production, and you can bounce it , when ever you wanted .
Madhu Reddy
xdollor@yahoo.com
-
By your post, you are obviousely testing your queries.
I dont know if this works ..
1. Reduce the the Buffer Size
2. Create a table large enuf with junk data that can fill up your Data Buffer.
3. Run your Query
4. Run a Query on the large table, buffers are replaced
5. Run you Query
Sridhar R Patnam
-
Originally posted by patnams
By your post, you are obviousely testing your queries.
I dont know if this works ..
1. Reduce the the Buffer Size
2. Create a table large enuf with junk data that can fill up your Data Buffer.
3. Run your Query
4. Run a Query on the large table, buffers are replaced
5. Run you Query
You want HIM to do it on a production box ?????
Madhu Reddy
xdollor@yahoo.com
-
I think recycle buffer pool will be a good choice
it will not degrade performance of other table.
Oracle Certified Master - September, 2003, the Second OCM in China
*** LOOKING for PART TIME JOB***
Data Warehouse & Business Intelligence Expert
MCSE, CCNA, SCJP, SCSA from 1998
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
|