RE: Select statement runs slow until flush of shared pool

From: Saibabu Devabhaktuni <saibabu_d_at_yahoo.com>
Date: Wed, 28 Nov 2012 00:37:38 -0800 (PST)
Message-ID: <1354091858.16591.YahooMailNeo_at_web161302.mail.bf1.yahoo.com>



Finn,
Since you are using automatic SGA, there are two possibilities:
  1. Shared pool may be growing and hence it'd steal more memory from the buffer cache. There can be brief performance impact while the resize is happening. Shared pool flush essentially put a stop to this, at least for a while.
  2. Not sure if the shared pool flush trigger buffer cache getting some of it's memory back. Check this.

Even though the execution plan may remain same but the bind values being different can cause more records to be accessed and filtered, and this is typically the case for higher buffer gets as outlier for some of the sql executions when execution plan remained same.

Can you run the below query:

select sql_exec_id, sql_exec_start, event, sum(time_waited), count(*), max(sql_plan_hash_value) from dba_hist_active_sess_history where sql_id='&sqlid' and snap_id between &begin_snap_id and &end_snap_Id group by sql_exec_id, sql_exec_start, event order by 1, 4 desc;

Result set from above query will be more reliable and useful when executed against in memory ASH if session samples from the problem period still available in ASH.

Thanks,
 Sai
http://sai-oracle.blogspot.com

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Nov 28 2012 - 09:37:38 CET

Original text of this message