Translate

Search This Blog

tracking down resource intensive queries - made easy 10g

from 10g u dont need continuously query v$sesion_wait to track wait events and log in the data to table to aggregate them: follows ASH


top wait events and total time waited :

select active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history
where active_session_history.sample_time between sysdate - 60/2880 and sysdate
group by active_session_history.event
order by 2

top sessions id,username and total time waited :

select sesion.sid,
sesion.username,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history,
v$session sesion
where active_session_history.sample_time between sysdate - 60/2880 and sysdate
and active_session_history.session_id = sesion.sid
group by sesion.sid, sesion.username
order by 3



top user id/name,sql_text and total time waited:

SELECT active_session_history.user_id,
dba_users.username,
sqlarea.sql_text,
SUM(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
FROM v$active_session_history active_session_history,
v$sqlarea sqlarea,
dba_users
WHERE active_session_history.sample_time BETWEEN SYSDATE - 1 AND SYSDATE
AND active_session_history.sql_id = sqlarea.sql_id
AND active_session_history.user_id = dba_users.user_id
GROUP BY active_session_history.user_id,sqlarea.sql_text, dba_users.username
ORDER BY 4 DESC

top segments name/type ,events and total wait on them:


SELECT dba_objects.object_name,
dba_objects.object_type,
active_session_history.event,
SUM(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
FROM v$active_session_history active_session_history,
dba_objects
WHERE active_session_history.sample_time BETWEEN SYSDATE - 1 AND SYSDATE
AND active_session_history.current_obj# = dba_objects.object_id
GROUP BY dba_objects.object_name, dba_objects.object_type, active_session_history.event
ORDER BY 4 DESC

top 10 completed queries:

SELECT *
FROM
(SELECT sql_text,
sql_id,
elapsed_time,
cpu_time,
user_io_wait_time,
elapsed_Time/executions,executions
FROM sys.v_$sqlarea
WHERE executions>0
ORDER BY 6 DESC)
WHERE ROWNUM < 11


executions=0 means query are currently in progress

extracting execution plan for library cache:


SELECT LPAD(' ', 2*(LEVEL-1))||operation||' '||
DECODE(ID, 0, 'Cost = '||position) "OPERATION",
options, object_name
FROM v$sql_plan
START WITH (sql_id='&sql_idof_query'
AND child_number = 0
AND ID=0 )
CONNECT BY PRIOR ID = parent_id
AND PRIOR address = address
AND PRIOR hash_value = hash_value
AND PRIOR child_number = child_number
ORDER BY ID, position

query taken: merge join

SELECT st.sql_id,operation,options,object_name,
FROM v$sqltext st , v$sql_plan sp
WHERE st.sql_id=sp.sql_id AND
operation LIKE '%MERGE JOIN%' AND options IS NULL
ORDER BY st.sql_id,piece


query to find sqls which have text 'INDX_IND1':

SELECT /*+ hola */ * FROM v$sqlarea WHERE sql_text LIKE '%INDX_IND1%' AND sql_text NOT LIKE '%hola%'


Parallel Query in Oracle !! Beware!!

Run query in parallel only if you have idle CPUs and Table is distributed across disk but again be careful to choose degree of parallelism. Over Parallelization can kill performance

just imagine of 8 CPUs and parallel_threads_per_cpu parameter default value 2

you fired a query with degree 4 but its taking cpu_counts*parallel_threads_per_cpu =16! You can hit few moere such a query and see PX idle wait evens and helplessly watch terrific slow down!!

query session wait details for parallel query(PX wait events)-

SELECT * FROM v$Session_wait
WHERE sid IN ( SELECT sid FROM v$session
WHERE
username='TEST_USER' AND
status='ACTIVE'
)
AND wait_time=0
AND sid IN ( SELECT sid FROM v$px_session)
ORDER BY 1


351 1699 direct path read file number 5 0000000000000005 first dba 2588307 0000000000277E93 block cnt 126 000000000000007E 1740759767 8 User I/O 0 0 WAITING
416 1498 direct path read file number 5 0000000000000005 first dba 2573843 0000000000274613 block cnt 126 000000000000007E 1740759767 8 User I/O 0 0 WAITING
419 26 PX Deq: Execution Msg sleeptime/senderid 268566527 000000001001FFFF passes 26 000000000000001A 0 00 2723168908 6 Idle 0 3 WAITING
445 26 PX Deq: Execution Msg sleeptime/senderid 268566527 000000001001FFFF passes 26 000000000000001A 0 00 2723168908 6 Idle 0 3 WAITING
575 26 PX Deq: Execution Msg sleeptime/senderid 268566527 000000001001FFFF passes 26 000000000000001A 0 00 2723168908 6 Idle 0 3 WAITING
618 996 PX Deq: Execute Reply sleeptime/senderid 200 00000000000000C8 passes 1 0000000000000001 0 00 2723168908 6 Idle 0 21 WAITING
684 26 PX Deq: Execution Msg sleeptime/senderid 268566527 000000001001FFFF passes 26 000000000000001A 0 00 2723168908 6 Idle 0 3 WAITING
693 1616 direct path read file number 5 0000000000000005 first dba 2566803 0000000000272A93 block cnt 126 000000000000007E 1740759767 8 User I/O 0 0 WAITING
702 1329 direct path read file number 5 0000000000000005 first dba 2599955 000000000027AC13 block cnt 126 000000000000007E 1740759767 8 User I/O 0 0 WAITING

------------

345 194 PX Deq Credit: send blkd sleeptime/senderid 268566527 000000001001FFFF passes 175 00000000000000AF qref 0 00 1893977003 0 Other 0 0 WAITING
356 1248 PX Deq: Execution Msg sleeptime/senderid 268566527 000000001001FFFF passes 1244 00000000000004DC 0 00 2723168908 6 Idle 0 0 WAITING

So better you diable parallelization of tables by statement alter table noparallel