Home » RDBMS Server » Performance Tuning » Does "alter system flush shared_pool" statement impact to performance? (Oracle 10g, crossover OS! )
Does "alter system flush shared_pool" statement impact to performance? [message #413639] Thu, 16 July 2009 22:00 Go to next message
trantuananh24hg
Messages: 664
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

Re: Does "alter system flush shared_pool" statement impact to performance? [message #413654 is a reply to message #413639] Thu, 16 July 2009 23:16 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1.

Quote:
When using flush shared_pool, the statistics was gather before will be obsoleted?

No.
The rest is correct, all must be reloaded (including dictionary information, so mis=1) and all statements or PL/SQL must be reparsed.

2.

No.

Regards
Michel
Re: Does "alter system flush shared_pool" statement impact to performance? [message #413656 is a reply to message #413639] Thu, 16 July 2009 23:23 Go to previous messageGo to next message
trantuananh24hg
Messages: 664
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Thank you sir, Michel!

You help me much! Thank you, again!
Re: Does "alter system flush shared_pool" statement impact to performance? [message #424447 is a reply to message #413639] Fri, 02 October 2009 07:51 Go to previous messageGo to next message
mohd_dba
Messages: 19
Registered: October 2009
Junior Member
while using this command,alter system flush shared pool.

It will clear all the sql statements that was available in the shared pool.This option you can check both in Oracle 9i and Oracle 10g.

Please correct me if I am wrong.

Thanks,
Afroze.
Re: Does "alter system flush shared_pool" statement impact to performance? [message #424449 is a reply to message #424447] Fri, 02 October 2009 07:53 Go to previous message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please stop reactivating old topics with useless posts.

Regards
Michel
Previous Topic: Slow Insert APPEND into Temporary Table
Next Topic: flush shared pool
Goto Forum:
  


Current Time: Sat Sep 24 16:21:54 CDT 2016

Total time taken to generate the page: 0.11369 seconds