Re: Spotting the real cause of a Production slowdown (10g)

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Thu, 20 Apr 2006 15:20:39 +0100
Message-ID: <7765c8970604200720j61c965cs845ad852955489ee@mail.gmail.com>

On 4/20/06, Schultz, Charles <sac_at_uillinois.edu> wrote:
>
>
> Thanks. Yes, I agree with what you are saying. One of the fallouts from our case with Oracle Support is that we are bumping up against a bug whereby the 10g SGA auto-sizer will inflate the shared pool indiscriminately, even if you are flooding it with literal statements (counter-intuitive, as you pointed out below).
>
> The problem with finding many copies of sql statements that look similar is how granular do you go? The common method is to substring sql_text from v$sql. How many characters do you use? To be empirical, one would have to loop through all reasonable numbers (ie, between 7 and 150). Unless you know something I don't, which is entirely possible. *grin*
>
> Good point about which processes are consuming CPU - I did not even think about that. How does one use the Historical view of EM to determine that information? All I can find is "CPU used" under performance, and if I try to break it down, I get user processes. But I will keep looking.
>

If you are licensed for it you could look in V$ACTIVE_SESSION_HISTORY, something like this sqlscript perhaps

col event for a64
col elapsed_time for 9,999,999.99
set lines 120
accept period prompt "Enter period to report on in minutes [15]:" default 15

select sess.sid

, sess.username
, ash.event
, sum(ash.wait_time + ash.time_waited) elapsed_time

from
v$active_session_history ash
, v$session sess
where
ash.sample_time between sysdate - &period/2880 and sysdate and ash.session_id = sess.sid
and ash.session_serial# = sess.serial#
group by sess.sid, sess.username, ash.event order by username,event
/

--
Niall Litchfield
Oracle DBA
http://www.orawin.info
--
http://www.freelists.org/webpage/oracle-l

Received on Thu Apr 20 2006 - 09:20:39 CDT