I've two questions First, it's substantial the difference between execute the ALTER SYSTEM FLUSH SHARED_POOL command in the server and in one client? In my company they teach me that I've to execute directly in the server that command, but I think is just a command that goes for the network and just a message that is flushed I think that shouldn't be substantial differente how it happens with lot of data, I'm talking of a system that take about 5 minutes approximately to flush
The second, how can I flush a instance from another instance?
ALTER SYSTEM FLUSH SHARED_POOL; can be run from either the client or the server, it doesn't matter.
Many DBAs will run the command from the server, for two reasons. First, many DBAs run all commands from the server, usually because they never learned the importance of an IDE. Second, the command ALTER SYSTEM FLUSH SHARED_POOL; only affects one instance in a clustered database. Connecting directly to the server is usually an easy way of ensuring you connect to each database instance of a cluster.
But you can easily flush the shared pool from all instances without directly connecting to each instance, using the below code. (Thanks to berxblog for this idea.)
--Assumes you have elevated privileges, like DBA role or ALTER SYSTEM privilege.
create or replace function flush_shared_pool return varchar2 authid current_user as
begin
execute immediate 'alter system flush shared_pool';
return 'Done';
end;
/
select *
from table(gv$(cursor(
select instance_number, flush_shared_pool from v$instance
)));
INSTANCE_NUMBER FLUSH_SHARED_POOL
--------------- -----------------
1 Done
3 Done
2 Done
I partially disagree with @sstan - flushing the shared pool should be rare in production, but it may be relatively common in development. Flushing the shared pool and buffer cache can help imitate running queries "cold".