| Does "alter system flush shared_pool" statement impact to performance? [message #413639] |
Thu, 16 July 2009 22:00  |
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
Dear all!
I've wondered about the statement
SQL> alter system flush shared_pool;
The statement will flush shared_pool, clear all data from SGA, release wait_latch, memory...
Two questions about it:
1. The Optimizer will be impacted after flush shared pool, reload data, but it will re-parse all objects by hard-parse/soft-parse/executions/fetch? And it will impact to the statistic which have been gathered before?
Answer piece: Yes, it will re parse. Eg:
Step 1: Hard parse
SQL> Select object_name, object_id
2 from big_objs
3 where object_id = 199;
...............................
Step 2: Soft parse
SQL> /
..................
Step 3: Flush shared pool:
SQL> alter system flush shared_pool;
.............................
Step 4: Hard parse
SQL> select object_name, object_id
2 from big_objs
3 where object_id=199;
....................................
The mis=1 will appear in dump file (using sql_trace) in step 1,4. And it is "library miss".
And how about the statistics? When using flush shared_pool, the statistics was gather before will be obsoleted?
2. Does it impact to SYSTEM tablespace, and the backup set/piece before?
May you clarify me?
Thank you very much!
[Updated on: Thu, 16 July 2009 22:24] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
