My First Blog Post

I decide to create my own Oracle related blog, so that I can share some of Oracle tips with friends.

1 To identify the total amount and type of parsing going on in the system execute the following SQL statement:

SQL> select name, value from v$sysstat where name like 'parse count%';

2 To associate SQL statements with their bind variable names and values:

SQL> select s.address, s.sql_text, m.bind_name, d.value

 from v$sql s, v$sql_bind_metadata m, v$sql_bind_data d, v$sql_cursor c, x$kglcursor k

 where s.address=c.parent_handle 

         and s.address=k.kglhdpar

         and m.address=k.kglhdadr 

         and c.curno=d.cursor_num;

3 Diagnostic LIBRARY CACHE hit ratio:

 SQL> select namespace, gets, gethitratio GET_HR, pins, pinhitratio PIN_HR, reloads, invalidations INV from v$librarycache;

4 Monitoring SGA usage:

select * from v$sgastat;

Use V$SQLAREA to identify SQL with high parse calls and high resource usage (DISK_READS, BUFFER_GETS). These SQL statements and the application should be

tuned and optimized to minimize this activity. 

 

5 Locate similar SQL not using bind variables

select substr(sql_text,1,40) SQL, count(*) from v$sqlarea group by substr(sql_text,1,40) having count(*) > 5;

6 Locate cursors with different object definitions

select substr(sql_text,1,50), version_count from v$sqlarea where version_count > 5;

7 Estimate memory usage

select sum(sharable_mem) from v$sqlarea;

8 Locate anonymous blocks in order to convert them to package, so that to share SQL.

Avoid sizing the shared pool too large if SQL is not shared; iteratively arrive at the optimal value. Find anonymous blocks and convert to packages if possible.

SQL> select sql_text from v$sqlarea where command_type = 47;

Note: 47 is the command type for an anonymous PL/SQL block.

9 To identify the statements that are receiving a lot of parse calls execute the following:

SQL> select sql_text, parse_calls, executions from v$sqlarea where parse_calls > 100 and executions < 2*parse_calls;

10 Latch usage info:  "Normally excessive library cache contention will show up in waits for latch free event in V$SYSTEM_EVENT and V$SESSION_WAIT. Then the next step would be to determine which of the latches are contributing excessively to the waits for the latch free event. If it is the library cache or the shared pool latches then probably the library cache and shared pool tuning is required. V$SESSION_WAIT will display high number of waits for event latch free. V$LATCH will show that the contention occurs on the library cache latch."

select latch#, substr(name,1,25) Latch, sleeps from v$latch where sleeps != 0 order by sleeps desc;

11 Real time indication of library cache latch contention

select a.sid, a.username, a.program, a.status, b.name, c.wait_time, c.state

from v$session a, v$latch b, v$session_wait c

where a.sid = c.sid and c.p2 = b.latch# and c.event = 'latch free' and b.name like 'library%';

12 Determine the distribution of the load among the library cache child latches.

select gets, misses, sleeps, spin_gets, sleep1, sleep2, sleep3

from v$latch_children

where latch# = 99

order by sleeps desc;

13 Find sessions with the Highest CPU Consumption

set lines 300

col module format a50

SELECT s.sid, s.serial#, p.spid as "OS PID",s.username, s.module, st.value/100 as "CPU sec"

FROM v$sesstat st, v$statname sn, v$session s, v$process p

WHERE sn.name = 'CPU used by this session' -- CPU

AND st.statistic# = sn.statistic#

AND st.sid = s.sid

AND s.paddr = p.addr

AND s.last_call_et < 1800 -- active within last 1/2 hour

AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours

ORDER BY st.value;

14 Find Sessions with Highest Waits of a Certain Type

-- sessions with the highest time for a certain wait

SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, se.time_waited

FROM v$session_event se, v$session s, v$process p

WHERE se.event = '&event_name'

AND s.last_call_et < 1800 -- active within last 1/2 hour

AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours

AND se.sid = s.sid

AND s.paddr = p.addr

ORDER BY se.time_waited;

15 Find Sessions with Waits

set lines 300

col module format a50

col event format a40

col username format a15

SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, se.event, s.module, se.time_waited

FROM v$session_event se, v$session s, v$process p

where s.last_call_et < 1800 -- active within last 1/2 hour

AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours

AND se.sid = s.sid

AND s.paddr = p.addr

ORDER BY se.time_waited desc

16 10g or higher: Find Sessions with the Highest DB Time

-- sessions with highest DB Time usage

SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, st.value/100 as "DB Time

(sec)", stcpu.value/100 as "CPU Time (sec)", round(stcpu.value / st.value * 100,2) as "% CPU"

FROM v$sesstat st, v$statname sn, v$session s, v$sesstat stcpu, v$statname sncpu, v$process p

WHERE sn.name = 'DB time' -- CPU

AND st.statistic# = sn.statistic#

AND st.sid = s.sid

AND sncpu.name = 'CPU used by this session' -- CPU

AND stcpu.statistic# = sncpu.statistic#

AND stcpu.sid = st.sid

AND s.paddr = p.addr

AND s.last_call_et < 1800 -- active within last 1/2 hour

AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours

AND st.value > 0;

17 Find blocking and blocked sessions and corresponding SQL statement

--Find blocked sessions

select sid,serial#, SQL_ID,status,ACTION, BLOCKING_SESSION,BLOCKING_SESSION_STATUS, EVENT from v$session where blocking_session is not null;

--Find blocked sessions' sql_id and sql_text

select sql_id,sql_text from v$sql where sql_id in (select sql_id from v$session where blocking_session is not null);

18 Look for SQL stmts with more than one parent cursor
-- (more than 1024 child cursors).
-- We hope that it returns no rows.

select sql_id, count(*) n
from v$sql
where child_number = 0
group by sql_id
having count(*) > 1 ; 

-- If any rows are returned from the previous query,
-- then please run the following
-- statement to see why the cursors are not sharing.

select *
from v$sql_shared_cursor
where sql_id in
(
select sql_id from v$sql where child_number = 0
group by sql_id having count(*) > 1
) ;

==========================================

Note: Most of the above scripts are found from Oracle guide, metalink, and some performance tuning books.