Wednesday, February 19, 2020

Performance Queries



BLOCKING SESSIONS 

 select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' )  is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;

select  * from v$lock where block>0;

select  sid,blocking_session from v$session where blocking_session is not null;

select decode(request,0,'Holder: ','Waiter: ')||sid sess,
id1, id2, lmode, request, type
from v$lock
where (id1, id2, type) in
(select id1, id2, type from v$lock where request>0)
order by id1, request;  

select  blocking_session, sid,  wait_class,
seconds_in_wait
from     v$session
where blocking_session is not NULL
order by blocking_session;


IDENTIFYING RECENTLY LOCKED SESSIONS


select to_char(h.sample_time, 'HH24:MI:SS') TIME,ash.session_id,
decode(ash.session_state, 'WAITING' ,ash.event, ash.session_state) STATE,    
ash.sql_id,
ash.blocking_session BLOCKER
from v$active_session_history ash, dba_users du
where du.user_id = ash.user_id;




IDENTIFYING A LOCKED OBJECT

select lpad(' ',decode(l.xidusn,0,3,0)) || l.oracle_username "User",
o.owner, o.object_name, o.object_type
from v$locked_object l, dba_objects o
where l.object_id = o.object_id
order by o.object_id, 1 desc;



MAPPING A RESOURCE-INTENSIVE PROCESS TO A DATABASE PROCESS
This command displays the top ten CPU consuming resources on the server.

 ps -e -o pcpu,pid,user,tty,args | sort -n -k 1 -r | head

This command  displays the top ten MEMORY  consuming resources on the server.

 ps -e -o pmem,pid,user,tty,args | sort -n -k 1 -r | head
SELECT
  'USERNAME    : ' || s.username     || CHR(10) ||
  'SCHEMA      : ' || s.schemaname   || CHR(10) ||
  'OSUSER      : ' || s.osuser       || CHR(10) ||
  'MODUEL      : ' || s.program      || CHR(10) ||
  'ACTION      : ' || s.schemaname   || CHR(10) ||
  'CLIENT_INFO : ' || s.osuser       || CHR(10) ||
  'PROGRAM     : ' || s.program      || CHR(10) ||
  'SPID        : ' || p.spid         || CHR(10) ||
  'SID         : ' || s.sid          || CHR(10) ||
  'SERIAL#     : ' || s.serial#      || CHR(10) ||
  'KILL STRING : ' || '''' || s.sid || ',' || s.serial# || ''''  || CHR(10) ||
  'MACHINE     : ' || s.machine      || CHR(10) ||
  'TYPE        : ' || s.type         || CHR(10) ||
  'TERMINAL    : ' || s.terminal     || CHR(10) ||
  'SQL ID      : ' || q.sql_id       || CHR(10) ||
  'CHILD_NUM   : ' || q.child_number || CHR(10) ||
  'SQL TEXT    : ' || q.sql_text
FROM v$session s
    ,v$process p
    ,v$sql     q
WHERE s.paddr  = p.addr
AND   p.spid   = '&PID_FROM_OS'
AND   s.sql_id = q.sql_id(+)
AND   s.status = 'ACTIVE';

select * from table(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id',&child_num));





MONITORING LONG RUNNING SQL STATEMENTS
SELECT username, target, sofar blocks_read, totalwork total_blocks,
round(time_remaining/60) minutes
FROM v$session_longops
WHERE sofar <> totalwork




IF YOU WANT TO DISPLAY CURRENT DATABASE SESSIONS THAT ARE WAITING FOR I/O RESOURCES ,YOU CAN QUERY THE DATA DICTIONARY AS FOLLOWS


col OBJECT_NAME format a27
SELECT a.username,a.sql_id,b.object_name,b.object_type,a.event
FROM v$session a
     ,dba_objects b
     ,v$event_name c
WHERE b.object_id=a.row_wait_obj#
AND   a.event=c.name
AND   c.wait_class='User I/O' ;




RUNNING A DATABASE HEALTH CHECK

 You can get a list of all the health checks you can run by querying the following query.

 select name from v$hm_check where internal='N';

 Once you decide on the type of check ,specify the name of the check in the DBMS_HM package' s RUN_CHECK

 begin
dbms_hm.run_check('Dictionary Integrity Check','testrun1');
end;
/

 You can view a health check's findings using the DBMS_HM Package.

 set long 10000000
set longchunksize 1000
set pagesize 1000
set linesize 512
select dbms_hm.get_run_report('testrun1') from dual;


IDENTIFYING RESOURCE-CONSUMING SQL STATEMENTS THAT ARE CURRENTLY EXECUTING


SELECT sql_text,disk_reads FROM
(SELECT sql_text,buffer_gets,disk_reads,sorts,cpu_time/10000000 cpu,rows_processed ,elapsed_time
FROM gv$sqlstats
ORDER BY disk_reads DESC)
WHERE rownum <=10;


SELECT sql_text,elapased_time FROM
(SELECT sql_text,buffer_gets,disk_reads,sorts,cpu_time/10000000 cpu,rows_processed ,elapsed_time
FROM gv$sqlstats
ORDER BY elapsed_time DESC)
WHERE rownum <=5;

SELECT sql_text,cpu  FROM
(SELECT sql_text,buffer_gets,disk_reads,sorts,cpu_time/10000000 cpu,rows_processed ,elapsed_time
FROM v$sqlstats
ORDER BY cpu_time DESC)
WHERE rownum <=5;

SELECT sql_text,buffer_gets FROM
(SELECT sql_text,buffer_gets,disk_reads,sorts,cpu_time/10000000 cpu,rows_processed ,elapsed_time
FROM v$sqlstats
ORDER BY buffer_gets DESC)
WHERE rownum <=5;


SEEING EXECUTION STATISTICS FOR CURRENTLY RUNNING SQL
SELECT sql_text from v$sql_monitor WHERE status='EXECUTING';

SELECT sid,buffer_gets,disk_reads,round(cpu_time/1000000,1) cpu_seconds
FROM v$sql_monitor
WHERE SID='&sessionsid'
AND status='EXECUTING';



THE V$SQL_MONITOR VIEWS CONTAINS CURRENTLY RUNNING SQL STATMENTS , AS WELL AS RECENTLY RUN SQL STATEMENTS.
SELECT * FROM (
SELECT sid,SESSION_SERIAL#,buffer_gets,disk_reads,round(cpu_time/1000000,1) cpu_seconds
FROM v$sql_monitor
ORDER BY cpu_time desc)
WHERE rownum<=5;
IF YOU WANTED TO SEE ALL EXECUTIONS FOR A GIVEN QUERY (BASED ON SQL_ID ) ,WE CAN GET THAT INFORMATION BY QUERYING ON THE THREE NECESSARY COLUMNS TO DRILL TO A GIVEN
SELECT * FROM (
SELECT sql_id,to_char(sql_exec_start,'yyyy-mm-dd:hh24:mi:ss') sql_exec_start ,
sql_exec_id,sum(buffer_gets) buffer_gets,sum(disk_reads) disk_reads,round(cpu_time/1000000,1) cpu_seconds
FROM v$sql_monitor
WHERE sql_id ='&sql_id'
GROUP BY sql_id,sql_exec_start,sql_exex_id
ORDER BY 6 desc)
WHERE rownum<=5;


FOR PARALLEL SESSIONS CURRENTLY RUNNING
SELECT inst_id,SID,sql_id,to_char(sql_exec_start,'yyyy-mm-dd:hh24:mi:ss') sql_exec_start ,
sql_exec_id,px_server# px#,buffer_gets,disk_reads,round(cpu_time/1000000,1) cpu_seconds,round(elapsed_time/1000000,1) elapsed_seconds
FROM gv$sql_monitor
WHERE status='EXECUTING'
ORDER BY  elapsed_seconds;

SELECT SID,sql_id,to_char(sql_exec_start,'yyyy-mm-dd:hh24:mi:ss') sql_exec_start ,
sql_exec_id,px_server# px#,buffer_gets,disk_reads,round(cpu_time/1000000,1) cpu_seconds,round(elapsed_time/1000000,1) elapsed_seconds
FROM gv$sql_monitor
WHERE status='EXECUTING'
ORDER BY  sql_id;



MONITORING PROGRESS OF A SQL EXECUTION PLAN


set pages 9999
set long 100000
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_id=>'<sql_id>',type=>'html') FROM DUAL;
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_id=>'<sql_id>',type=>'text') FROM DUAL;


COMPARING SQL PERFORMANCE AFTER A SYSTEM CHANGE 

variable g_task varchar2(100)

EXEC :g_task :=DBMS_SQLPA.CREATE_ANALYSIS_TASK(sql_text=>'sql text paste here');

alter session set optimizer_features_enable='11.2.0.3';
EXEC  DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name=>:g_task ,execution_type=>'test execute',execution_name=>'before_change');

alter session set optimizer_features_enable='12.1.0.1.1';
EXEC  DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name=>:g_task ,execution_type=>'test execute',execution_name=>'after_change');

EXEC  DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name=>:g_task ,execution_type=>'COMPARE PERFORMANCE',execution_name=>'compare change',execution_params=>dbms_advisor.arglist('comparision_metric','buffer_gets'));

set long 1000000 longchunksize 100000 linesize 1000
spool compare_report.txt
SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK:g_task,'TEXT','CHANGED_PLANS','ALL') from  dual;



VIEWING RESOURCE INTENSIVE SQL IN THE AWR
select snap_id,instance_number,end_interval_time from dba_hist_snapshot order by snap_id;

SELECT sql_id,substr(sql_text,1,50) sql_text ,disk_reads,cpu_time,elapsed_time from
table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(begin_snap_id,End_snap_id,null,null,'disk_reads',null,null,null,10))
order by disk_reads DESC;


SELECT sql_id,substr(sql_text,1,50) sql_text ,disk_reads,cpu_time,elapsed_time,parsing_schema_name from
table(
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
begin_snap=>,
end_snap=>,
basic_filter=>'parsing_schema_name<>"SYS"',
ranking_measure1=>'buffer_gets',
result_limit=>10))
order by disk_reads DESC;



IDENTIFYING RESOURCE - CONSUMING SQL STATEMENTS THAT HAVE EXECUTED IN THE PAST 
SELECT * FROM (
  SELECT sql_id, sum(disk_reads_delta) disk_reads_delta,
                 sum(disk_reads_total) disk_reads_total,
                 sum(executions_delta) execs_delta,
                 sum(executions_total) execs_total
  FROM dba_hist_sqlstat
  GROUP BY sql_id
  ORDER BY 2 desc)
WHERE rownum <= 5;

 SELECT sql_text FROM dba_hist_sqltext
WHERE sql_id = '7wjb00vsk8btp ';

 SELECT id, operation || ' ' || options operation, object_name, cost, bytes
FROM dba_hist_sql_plan
WHERE sql_id = '7wjb00vsk8btp '
ORDER BY 1;




YOU CAN EXECUTE THE FOLLOWING QUERY TO FIGURE OUT HOW MANY BLOCKS FOR EACH SEGMENT ARE CURRENTLY IN THE BUFFER CACHE :



select o.object_name, count(*) number_of_blocks
from dba_objects o, v$bh v
where o.data_object_id = v.objd
      and o.owner !='SYS'
group by o.object_name
order by count(*);
MINIMIZING SYSTEM CONTENTION
response time = processing time+ wait time

 Issue the following query to find out the relative percentage of wait times and actual CPU processing in the database :

 select metric_name, value
from v$sysmetric
where metric_name in ('Database CPU Time Ratio',
'Database Wait Time Ratio') and
intsize_csec =
(select max(INTSIZE_CSEC) from V$SYSMETRIC);



IDENTIFYING SQL STATEMENTS WITH THE MOST WAITS




Issue the following query to  identify the SQL Statements that are experiencing the most waits in your database :


select ash.user_id,
u.username,
s.sql_text,
sum(ash.wait_time +
ash.time_waited) ttl_wait_time
from v$active_session_history ash,
v$sqlarea s,
dba_users u
where ash.sample_time between sysdate - 60/2880 and sysdate
and ash.sql_id = s.sql_id
and ash.user_id = u.user_id
group by ash.user_id,s.sql_text, u.username
order by ttl_wait_time


ANALYZING WAIT EVENTS
Examining Session Waits:

 select event,count(*) from v$session_wait group by event;

 select event,state,seconds_in_wait siw from v$session_wait where sid= <sid number> ;
EXAMINING WAIT EVENTS BY CLASS 
The following query shows the different types of wait classes and the wait events associated with each wait class :

 select  wait_class, name
from v$event_name
where name LIKE 'enq%'
and wait_class <> 'Other'
order by wait_class

To view the current waits grouped into various wait classes , Issue the following query :

select wait_class, sum(time_waited), sum(time_waited)/sum(total_waits)
sum_waits
from v$system_wait_class
group by wait_class
order by 3 desc;

To view the current waits grouped into various wait classes by average waits , Issue the following query :

 select sea.event, sea.total_waits, sea.time_waited, sea.average_wait
   from v$system_event sea, v$event_name enb, v$system_wait_class swc
   where sea.event_id=enb.event_id
   and enb.wait_class#=swc.wait_class#
   and swc.wait_class in ('Application','Concurrency')
   order by average_wait desc
  
To get at the session whose performance is being affected by the contention for the row lock , drill down to the session level using the following query :

select se.sid, se.event, se.total_waits, se.time_waited, se.average_wait
     from v$session_event se, v$session ss
     where time_waited > 0
     and se.sid=ss.sid
     and ss.username is not NULL
     and se.event='enq: TX - row lock contention';


BUFFER BUSY WAITS
Your database is experiencing a high number of buffer busy waits ,  Issue the following query :

 select row_wait_obj#
from v$session
where event = 'buffer busy waits';

 select owner, object_name, subobject_name, object_type
from dba_objects
where object_id = &row_wait_obj;

select sql_id from v$session
where sid in (SELECT sid FROM v$session_wait WHERE event = 'buffer
              busy waits');
SELECT sql_text FROM v$sqlarea WHERE sql_id = <sql_id>;


READ BY OTHER SESSION WAIT EVENTS
select p1 "file#",p2 "block#",p3 "class#" from v$session_wait where event='read by other session';

select relative_fno, owner, segment_name, segment_type
from dba_extents
where file_id = &file
and &block between block_id
and block_id + blocks - 1;

select row_wait_obj#
from v$session
where event = 'buffer busy waits';

select owner, object_name, subobject_name, object_type
from dba_objects
where data_object_id = &row_wait_obj;
DIRECT PATH READ WAIT EVENTS 
select p1 "file#",p2 "block#",p3 "class#" from v$session_wait where event='direct path read temp';



select relative_fno, owner, segment_name, segment_type
from dba_extents
where file_id = &file
and &block between block_id
and block_id + blocks - 1;
enq : TM Lock Contention 



You can find all unindexed foreign key constraints in a specific schema by issuing the following query to get all such constraints 


select * from (
select ct.table_name, co.column_name, co.position column_position
from   user_constraints ct, user_cons_columns co
where  ct.constraint_name = co.constraint_name
and   ct.constraint_type = 'R'
minus
select ui.table_name, uic.column_name, uic.column_position
from   user_indexes ui, user_ind_columns uic
where  ui.index_name = uic.index_name
)
order by table_name, column_position;



ANALYZING RECENT WAIT EVENTS IN A DATABASE


To find the most important wait events in the last 15 minutes , issue the following query :

select event,
sum(wait_time +
time_waited) total_wait_time
from v$active_session_history
where sample_time between
sysdate –15/1440 and sysdate
group by event
order by total_wait_time desc;

To find out which of your users experienced the most  wait events in the last 15 minutes , issue the following query :

select s.sid, s.username,
sum(a.wait_time +
a.time_waited) total_wait_time
from v$active_session_history a,
v$session s
where a.sample_time between sysdate – 15/1440 and sysdate
and a.session_id=s.sid
group by s.sid, s.username
order by total_wait_time desc;

You can identify the SQL statements that have been waiting the most during the last 15 minutes with this query:

select a.user_id,u.username,s.sql_text,
sum(a.wait_time + a.time_waited) total_wait_time
from v$active_session_history a,
v$sqlarea s,
dba_users u
where a.sample_time between sysdate – 15/1440 and sysdate
and a.sql_id = s.sql_id
and a.user_id = u.user_id
group by a.user_id,s.sql_text, u.username
order by 4;


You want to identify the total time spent waiting by sessions


select wait_class, event, time_waited / 100 time_secs
from v$system_event e
where e.wait_class <> 'Idle' AND time_waited > 0
union
select 'Time Model', stat_name NAME,
round ((value / 1000000), 2) time_secs
from v$sys_time_model
where stat_name NOT IN ('background elapsed time', 'background cpu time')
order by 3 desc;


Minimizing Latch Contention

select event,sum(p3),sum(seconds_in_wait) seconds_in_wait from v$session_wait where event like 'latch%' group by event;



STATISTICS OF OBJECTS OF A SPECIFIC SQL ID



SET LINES 300 SET PAGES 300
COL TABLE_NAME FOR A40
COL OWNER FOR A30

SELECT DISTINCT OWNER, TABLE_NAME, STALE_STATS, LAST_ANALYZED, STATTYPE_LOCKED
  FROM DBA_TAB_STATISTICS
  WHERE (OWNER, TABLE_NAME) IN
  (SELECT DISTINCT OWNER, TABLE_NAME
          FROM DBA_TABLES
          WHERE ( TABLE_NAME)
          IN ( SELECT OBJECT_NAME
                  FROM GV$SQL_PLAN
                  WHERE UPPER(SQL_ID) = UPPER('&SQL_ID') AND OBJECT_NAME IS NOT NULL))
  --AND STALE_STATS='YES'
/

DISPLAYS LAST ANALYZED DETAILS FOR A GIVEN SCHEMA. (ALL SCHEMA OWNERS IF 'ALL' SPECIFIED):



SET PAUSE ON
SET PAUSE 'PRESS RETURN TO CONTINUE'
SET PAGESIZE 60
SET LINESIZE 300

SELECT T.OWNER,
       T.TABLE_NAME AS "TABLE NAME", 
       T.NUM_ROWS AS "ROWS", 
       T.AVG_ROW_LEN AS "AVG ROW LEN", 
       TRUNC((T.BLOCKS * P.VALUE)/1024) AS "SIZE KB", 
       TO_CHAR(T.LAST_ANALYZED,'DD/MM/YYYY HH24:MM:SS') AS "LAST ANALYZED"
FROM   DBA_TABLES T,
       V$PARAMETER P
WHERE T.OWNER = DECODE(UPPER('&&TABLE_OWNER'), 'ALL', T.OWNER, UPPER('&&TABLE_OWNER'))
AND   P.NAME = 'DB_BLOCK_SIZE'
ORDER BY T.OWNER,T.LAST_ANALYZED,T.TABLE_NAME
/

SET VER OFF
SET LINESIZE 60
COL TABLE_NAME FORMAT A15
COL LAST_ANALYZED FORMAT A40

SELECT TABLE_NAME "TABLE NAME",TO_CHAR(LAST_ANALYZED,'DD-MON-YY HH24:MI:SS') "DATE AND TIME" FROM DBA_TABLES WHERE LOWER(TABLE_NAME)='&TNAME';


THIS SCRIPT IS INTENDED FOR DAILY USE TO GET TABLES WHERE PERCENTAGE OF CHANGED RECORDS IS ABOVE 10%:




SELECT DT.OWNER,
       DT.TABLE_NAME,
       ROUND ( (DELETES + UPDATES + INSERTS) / NUM_ROWS * 100) PERCENTAGE
FROM   DBA_TABLES DT, ALL_TAB_MODIFICATIONS ATM
WHERE      DT.OWNER = ATM.TABLE_OWNER
       AND DT.TABLE_NAME = ATM.TABLE_NAME
       AND NUM_ROWS > 0
       AND ROUND ( (DELETES + UPDATES + INSERTS) / NUM_ROWS * 100) >= 10
ORDER BY 3 DESC;




STATISTICS OF OBJECTS OF A SPECIFIC SQL ID:



SET LINES 300 SET PAGES 300
COL TABLE_NAME FOR A40
COL OWNER FOR A30

SELECT DISTINCT OWNER, TABLE_NAME, STALE_STATS, LAST_ANALYZED, STATTYPE_LOCKED
  FROM DBA_TAB_STATISTICS
  WHERE (OWNER, TABLE_NAME) IN
  (SELECT DISTINCT OWNER, TABLE_NAME
          FROM DBA_TABLES
          WHERE ( TABLE_NAME)
          IN ( SELECT OBJECT_NAME
                  FROM GV$SQL_PLAN
                  WHERE UPPER(SQL_ID) = UPPER('&SQL_ID') AND OBJECT_NAME IS NOT NULL))
  --AND STALE_STATS='YES'
/




TO START TRACE RUN PROCEDURE WITH A MODULE NAME AND DATABASE NAME:




BEGIN
DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE( SERVICE_NAME => '<DB_NAME>'
, MODULE_NAME => '<MODULE_NAME>' 
, ACTION_NAME => DBMS_MONITOR.ALL_ACTIONS
,WAITS => TRUE
,BINDS => TRUE
,INSTANCE_NAME => NULL
,PLAN_STAT => NULL 
);
END;
/

TO STOP TRACE RUN PROCEDURE:

EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE('<DB_NAME>','<MODULE_NAME>');


DETAIL REPORT USER SESSION:



COL orauser HEA "   Oracle User   " FOR a17 TRUNC
COL osuser HEA " O/S User " FOR a10 TRUNC
COL ssid HEA "Sid" FOR a4
COL sserial HEA "Serial#" FOR a7
COL ospid HEA "O/S Pid" FOR a7
COL slogon HEA "  Logon Time  " FOR a14
COL sstat HEA "Status" FOR a6
COL auth HEA "Auth" FOR a4
COL conn HEA "Con" FOR a3
 
SELECT
   ' '||NVL( s.username, '    ????    ' ) orauser, 
   ' '||s.osuser osuser, 
   LPAD( s.sid, 4 ) ssid, LPAD( s.serial#, 6 ) sserial,
   LPAD( p.spid, 6 ) ospid, 
   INITCAP( LOWER( TO_CHAR( logon_time, 'MONDD HH24:MI:SS' ) ) ) slogon,
   DECODE( s.status, 'ACTIVE', ' Busy ', 'INACTIVE', ' Idle ', 'KILLED', ' Kill ', '  ??  ' ) sstat, 
   DECODE( sc.authentication_type, 'DATABASE', ' DB ', 'OS', ' OS ', ' ?? ' ) auth,
   DECODE( s.server, 'DEDICATED', 'Dir', 'NONE', 'Mts', 'SHARED', 'Mts', '???' ) conn
FROM
   v$session s, v$process p, 
   (
   SELECT
      DISTINCT sid, authentication_type
   FROM
      v$session_connect_info
   ) sc
WHERE
   s.paddr = p.addr AND s.sid = sc.sid
ORDER BY
   s.status,s.sid;


DATABASE UPTIME :



COL D_NAME HEADING 'DATABASE' FORMAT A8
COL V_LOGON_TIME HEADING 'STARTUP'
COL DH_UPTIME HEADING 'UPTIME' FORMAT A30

SELECT UPPER(SYS_CONTEXT('USERENV','DB_NAME')) D_NAME,
       TO_CHAR(LOGON_TIME,'DD-MON-YYYY HH24:MI:SS') V_LOGON_TIME,
       TO_CHAR(TRUNC(SYSDATE-LOGON_TIME,0))||' DAYS, '||TRUNC(((SYSDATE-LOGON_TIME)-FLOOR(SYSDATE-LOGON_TIME))*24)||' HOURS' DH_UPTIME
  FROM SYS.V_$SESSION
 WHERE SID=1 /* PMON SESSION */
/




TO INVESTIGATE RECENT BLOCKING LOCKS :





SET PAGESIZE 50
SET LINESIZE 120
COL SQL_ID FORMAT A15
COL INST_ID FORMAT '9'
COL SQL_TEXT FORMAT A50
COL MODULE FORMAT A10
COL BLOCKER_SES FORMAT '999999'
COL BLOCKER_SER FORMAT '999999'

SELECT DISTINCT
       A.SQL_ID ,
       A.INST_ID,
       A.BLOCKING_SESSION BLOCKER_SES,
       A.BLOCKING_SESSION_SERIAL# BLOCKER_SER,
       A.USER_ID,
       S.SQL_TEXT,
       A.MODULE
FROM  GV$ACTIVE_SESSION_HISTORY A,
      GV$SQL S
WHERE A.SQL_ID=S.SQL_ID
  AND BLOCKING_SESSION IS NOT NULL
  AND A.USER_ID <> 0 --  EXCLUDE SYS USER
  AND A.SAMPLE_TIME > SYSDATE - 1;


DISPLAYS INFORMATION ON ALL LONG OPERATIONS



COLUMN SID FORMAT 999
COLUMN SERIAL# FORMAT 9999999
COLUMN MACHINE FORMAT A30
COLUMN PROGRESS_PCT FORMAT 99999999.00
COLUMN ELAPSED FORMAT A10
COLUMN REMAINING FORMAT A10

SELECT S.SID,
       S.SERIAL#,
       S.MACHINE,
       ROUND(SL.ELAPSED_SECONDS/60) || ':' || MOD(SL.ELAPSED_SECONDS,60) ELAPSED,
       ROUND(SL.TIME_REMAINING/60) || ':' || MOD(SL.TIME_REMAINING,60) REMAINING,
       ROUND(SL.SOFAR/SL.TOTALWORK*100, 2) PROGRESS_PCT
FROM   V$SESSION S,
       V$SESSION_LONGOPS SL
WHERE  S.SID     = SL.SID
AND    S.SERIAL# = SL.SERIAL#;





DISPLAYS THE SQL STATEMENTS FOR CURRENTLY RUNNING PROCESSES:


SET LINESIZE 500
SET PAGESIZE 1000
SET FEEDBACK OFF

SELECT S.SID,
       S.STATUS "STATUS",
       P.SPID "PROCESS",
       S.SCHEMANAME "SCHEMA NAME",
       S.OSUSER "OS USER",
       SUBSTR(A.SQL_TEXT,1,120) "SQL TEXT",
       S.PROGRAM "PROGRAM"
FROM   V$SESSION S,
       V$SQLAREA A,
       V$PROCESS P
WHERE  S.SQL_HASH_VALUE = A.HASH_VALUE (+)
AND    S.SQL_ADDRESS    = A.ADDRESS (+)
AND    S.PADDR          = P.ADDR;




DISPLAYS INFORMATION ON ALL DATABASE SESSIONS ORDERED BY EXECUTIONS:



SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF
COLUMN USERNAME FORMAT A15
COLUMN MACHINE FORMAT A25
COLUMN LOGON_TIME FORMAT A20

SELECT NVL(A.USERNAME, '(ORACLE)') AS USERNAME,
       A.OSUSER,
       A.SID,
       A.SERIAL#,
       C.VALUE AS &1,
       A.LOCKWAIT,
       A.STATUS,
       A.MODULE,
       A.MACHINE,
       A.PROGRAM,
       TO_CHAR(A.LOGON_TIME,'DD-MON-YYYY HH24:MI:SS') AS LOGON_TIME
FROM   V$SESSION A,
       V$SESSTAT C,
       V$STATNAME D
WHERE  A.SID        = C.SID
AND    C.STATISTIC# = D.STATISTIC#
AND    D.NAME       = DECODE(UPPER('&1'), 'READS', 'session logical reads','EXECS','execute count','CPU','CPU used by this session')
ORDER BY C.VALUE DESC;


UNCOMMITED TRANSACTIONS:

SET LINES 250
COLUMN START_TIME FORMAT A20
COLUMN SID FORMAT 999
COLUMN SERIAL# FORMAT 999999
COLUMN USERNAME FORMAT A10
COLUMN STATUS FORMAT A10
COLUMN SCHEMANAME FORMAT A10
COLUMN OSUSER FORMAT A10
COLUMN PROCESS FORMAT A10
COLUMN MACHINE FORMAT A15
COLUMN TERMINAL FORMAT A10
COLUMN PROGRAM FORMAT A25
COLUMN MODULE FORMAT A10
COLUMN LOGON FORMAT A20
PROMPT ####################################################
PROMPT # CURRENT TRANSACTIONS:
PROMPT ####################################################
SELECT T.START_TIME,S.SID,S.SERIAL#,S.USERNAME,S.STATUS,S.SCHEMANAME,
S.OSUSER,S.PROCESS,S.MACHINE,S.TERMINAL,S.PROGRAM,S.MODULE,TO_CHAR(S.LOGON_TIME,'DD/MON/YY HH24:MI:SS') LOGON_TIME
FROM V$TRANSACTION T, V$SESSION S
WHERE S.SADDR = T.SES_ADDR
ORDER BY START_TIME;

DBMS_PROFILER :  How to analyze pl/sql performance


 Analyze the PL/SQL code and identifying performance issues using DBMS_PROFILER, 

  • we need to first start the profiler using DBMS_PROFILER.START_PROFILER, 
  • then we can execute the our pl/sql procedure we want monitored 
  • and at last we need to simply call DBMS_PROFILER.STOP_PROFILER to stop the profiler.
To analyze PL/SQL and identify bottlenecks, we can break the use of DBMS_PROFILER in following steps:

1.      Collect Profiler data for PL/SQL Block
2.      Identify RUNID using PLSQL_PROFILER_RUNS
3.      Identify UNIT_NUMBER using PLSQL_PROFILER_UNITS
4.      Identify PL/SQL Line Number which may have performance issue by PLSQL_PROFILER_DATA
5.      Get the Line of Code by USER_SOURCE
 
We do not need to call DBMS_PROFILER.FLUSH_DATA explicitly as DBMS_PROFILER.STOP_PROFILE flush profiler data automatically.

Make sure the user that needs to run the profiler has the correct privileges on the DBMS_PROFILER package.

GRANT EXECUTE ON dbms_profiler TO test;

Next we connect to the test user and create a dummy procedure to profile.

CONN test/test@service

CREATE OR REPLACE PROCEDURE do_something (p_times  IN  NUMBER) AS
  l_dummy  NUMBER;
BEGIN
  FOR i IN 1 .. p_times LOOP
    SELECT l_dummy + 1
    INTO   l_dummy
    FROM   dual;
  END LOOP;
END;
/

Next we start the profiler, run our procedure and stop the profiler.

DECLARE
  l_result  BINARY_INTEGER;
BEGIN
  l_result := DBMS_PROFILER.start_profiler(run_comment => 'do_something: ' || SYSDATE);
  do_something(p_times => 100);
  l_result := DBMS_PROFILER.stop_profiler;
END;
/

With the profile complete we can analyze the data to see which bits of the process took the most time, with all times presented in nanoseconds. First we check out which runs we have.

SET LINESIZE 200
SET TRIMOUT ON

COLUMN runid FORMAT 99999
COLUMN run_comment FORMAT A50
SELECT runid,
       run_date,
       run_comment,
       run_total_time
FROM   plsql_profiler_runs
ORDER BY runid;

RUNID RUN_DATE  RUN_COMMENT                        RUN_TOTAL_TIME
----- --------- ---------------------------------- --------------
    1 21-AUG-03 do_something: 21-AUG-2003 14:51:54      131072000

We then use the appropriate RUNID value in the following query.

COLUMN runid FORMAT 99999
COLUMN unit_number FORMAT 99999
COLUMN unit_type FORMAT A20
COLUMN unit_owner FORMAT A20

SELECT u.runid,
       u.unit_number,
       u.unit_type,
       u.unit_owner,
       u.unit_name,
       d.line#,
       d.total_occur,
       d.total_time,
       d.min_time,
       d.max_time
FROM   plsql_profiler_units u
       JOIN plsql_profiler_data d ON u.runid = d.runid AND u.unit_number = d.unit_number
WHERE  u.runid = 1
ORDER BY u.unit_number, d.line#;

RUNID UNIT_NU UNIT_TYPE       UNIT_OWNER  UNIT_NAME    LINE# TOTAL_OCCUR TOTAL_TIME MIN_TIME MAX_TIME
----- ------- --------------- ----------- ------------ ----- ----------- ---------- -------- --------
    1       1 ANONYMOUS BLOCK <anonymous> <anonymous>      4           1          0        0        0
    1       1 ANONYMOUS BLOCK <anonymous> <anonymous>      5           1          0        0        0
    1       1 ANONYMOUS BLOCK <anonymous> <anonymous>      6           1          0        0        0
    1       2 PROCEDURE       MY_SCHEMA   DO_SOMETHING     4         101          0        0        0
    1       2 PROCEDURE       MY_SCHEMA   DO_SOMETHING     5         100   17408000        0  2048000

5 rows selected.

The results of this query show that line 4 of the DO_SOMETHING procedure ran 101 times but took very little time, while line 5 ran 100 times and took proportionately more time. We can check the line numbers of the source using the following query.

SELECT line || ' : ' || text
FROM   all_source
WHERE  owner = 'MY_SCHEMA'
AND    type  = 'PROCEDURE'
AND    name  = 'DO_SOMETHING';

LINE||':'||TEXT
---------------------------------------------------
1 : PROCEDURE do_something (p_times  IN  NUMBER) AS
2 :   l_dummy  NUMBER;
3 : BEGIN
4 :   FOR i IN 1 .. p_times LOOP
5 :     SELECT l_dummy + 1
6 :     INTO   l_dummy
7 :     FROM   dual;
8 :   END LOOP;
9 : END;

As expected, the query took proportionately more time than the procedural loop. Assuming this were a real procedure we could use the DBMS_TRACE or the SQL trace facilities to investigate the problem area further.

For further information see:


Description of the DBMS_MONITOR package

DBMS_MONITOR come for replacement of DBMS_SUPPORT with new functionnalities and easier management.
DBMS_MONITOR can be used to trace sessions or make traces with larger scope.
First let’s have a look to the available procedures of DBMS_MONITOR. Each procedure exists twice, one to enable the trace and one to disable it.

The CLIENT_ID_TRACE procedure

Description

This procedure is used to enable tracing using a Client Identifier.
This means that all sessions using  the same Client Identifier will be traced at the same time. This type of tracing is useful when you have to make a trace for an application using an application pool to connect to the database.
The trace will persist after a database restart and you’ll have to explicitly disable it.

Syntaxe

DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE(
 client_id    IN  VARCHAR2,
 waits        IN  BOOLEAN DEFAULT TRUE,
 binds        IN  BOOLEAN DEFAULT FALSE,
 plan_stat    IN  VARCHAR2 DEFAULT NULL);

Exemple

set the identifier in the session you want to trace:

SQL> exec DBMS_SESSION.SET_IDENTIFIER('this is a test');

PL/SQL procedure successfully completed.

Then enable the trace for the client identifier, execute the following command in another session:

SQL>  exec DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE('this is a test',true,true);

PL/SQL procedure successfully completed.

To see if the trace is enabled, you can query the dba_enabled_trace view :

SQL> select TRACE_TYPE,PRIMARY_ID,WAITS,BINDS,PLAN_STATS from dba_enabled_traces;

TRACE_TYPE            PRIMARY_ID                                                       WAITS BINDS PLAN_STATS
--------------------- ---------------------------------------------------------------- ----- ----- ----------
CLIENT_ID             this is a test                                                   TRUE  TRUE  FIRST_EXEC

You can choose to get the waits, binds ans plan_stats using the parameters.

Don’t forget to disable the trace when done :

SQL> exec DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE('this is a test');

PL/SQL procedure successfully completed.

The DATABASE_TRACE procedure

Description

This procedure can be used to enable the trace for the whole database or for a specific instance in case RAC is used.

Syntaxe

DBMS_MONITOR.DATABASE_TRACE_ENABLE(
   waits          IN BOOLEAN DEFAULT TRUE,
   binds          IN BOOLEAN DEFAULT FALSE,
   instance_name  IN VARCHAR2 DEFAULT NULL,
   plan_stat      IN VARCHAR2 DEFAULT NULL);

Exemple

To activate the trace:

exec dbms_monitor.database_trace_enable(waits=>true,binds=>TRUE);

PL/SQL procedure successfully completed.

To see if the trace is activated :

SQL>  select TRACE_TYPE,PRIMARY_ID,WAITS,BINDS,PLAN_STATS from dba_enabled_traces;

TRACE_TYPE            PRIMARY_ID                                                       WAITS BINDS PLAN_STATS
--------------------- ---------------------------------------------------------------- ----- ----- ----------
DATABASE                                                                               TRUE  TRUE  FIRST_EXEC

Then disable the trace when done :

SQL> exec dbms_monitor.database_trace_disable;

PL/SQL procedure successfully completed.

The SERV_MOD_ACT_TRACE procedure

Description

This procedure can be used to trace a particular application. It is possible to filter more finely application specifying the type of action you want to trace.

Syntaxe

dbms_monitor.serv_mod_act_trace_enable(
service_name  IN VARCHAR2,
module_name   IN VARCHAR2 DEFAULT ANY_MODULE,
action_name   IN VARCHAR2 DEFAULT ANY_ACTION,
waits         IN BOOLEAN  DEFAULT TRUE,
binds         IN BOOLEAN  DEFAULT FALSE,
instance_name IN VARCHAR2 DEFAULT NULL,
plan_stat     IN VARCHAR2 DEFAULT NULL);

Exemple

Consider you want to trace every user using SQL*PLUS, you can use this command :

exec dbms_monitor.serv_mod_act_trace_enable('SYS$USERS', 'SQL*Plus', dbms_monitor.all_actions, TRUE, TRUE);

to see if trace is enabled :

SQL> select TRACE_TYPE,PRIMARY_ID,QUALIFIER_ID1,QUALIFIER_ID2,WAITS,BINDS from dba_enabled_traces;

TRACE_TYPE            PRIMARY_ID                                                       QUALIFIER_ID1                                    QUALIFIER_ID2                    WAITS BINDS
--------------------- ---------------------------------------------------------------- ------------------------------------------------ -------------------------------- ----- -----
SERVICE_MODULE        SYS$USERS                                                        SQL*Plus                                                                          TRUE  TRUE

to disable it :

SQL>  exec dbms_monitor.serv_mod_act_trace_disable('SYS$USERS', 'SQL*Plus', dbms_monitor.all_actions);

PL/SQL procedure successfully completed.

The SESSION_TRACE Procedure

Description

The SESSION_TRACE procedure is used to trace a specific session using the SID and SERIAL# columns of the v$session view. Like the other procedures you can use parameters to collect binds and waits.

Syntaxe

DBMS_MONITOR.SESSION_TRACE_ENABLE(
    session_id   IN  BINARY_INTEGER DEFAULT NULL,
    serial_num   IN  BINARY_INTEGER DEFAULT NULL,
    waits        IN  BOOLEAN DEFAULT TRUE,
    binds        IN  BOOLEAN DEFAULT FALSE,
    plan_stat    IN  VARCHAR2 DEFAULT NULL);

Example

Activate the trace :

SQL> exec DBMS_MONITOR.SESSION_TRACE_ENABLE(5,14,true,true);

PL/SQL procedure successfully completed.

to disable the trace :

SQL> exec DBMS_MONITOR.SESSION_TRACE_DISABLE(5,14);

PL/SQL procedure successfully completed.

This trace will not be visible in the database_enabled_trace view and will not persist after a database restart.

Using TRCSESS to aggregate your trace files

When you use a client_id trace, or procedure which can generate more than one trace file you will have to aggregate the files in order to analyze them all at the same time. This is the purpose of the TRCSESS utility.

Syntaxe

trcsess  [output=output_file_name]
         [session=session_id]
         [clientid=client_id]
         [service=service_name]
         [action=action_name]
         [module=module_name]
         [trace_files]

Example

If I need to aggregate the traces I made for the client_id ‘this is a test’, I’ll use the following syntaxe

trcsess output=mytracefile.trc clientid='this is a test' *.trc

One my file mytracefile.trc has been generated I can use TKPROF utility to analyze it :

tkprof mytracefile.trc mytraceoutput.log

You can refer to this well explained oracle documentation to learn more about application tracing.


DBMS_SQL_MONITOR : 
The DBMS_SQL_MONITOR package provides information about Real-time SQL Monitoring and Real-time Database Operation Monitoring.
Overview

The DBMS_SQL_MONITOR package provides information about Real-time SQL Monitoring and Real-time Database Operation Monitoring. These features provide automatic monitoring of SQL statements, PL/SQL blocks, or composite database operations that are considered expensive. A simple database operation is a single SQL statement or PL/SQL procedure or function. A composite database operation is activity between two defined points in time in a database session. The monitored data is collected in V$SQL_MONITOR and V$SQL_PLAN_MONITOR.

The following subprograms begin and end monitoring of a composite database operation:

The following subprograms report on monitoring data collected in V$SQL_MONITOR and V$SQL_PLAN_MONITOR:

DBMS_SQLTUNE

The DBMS_SQLTUNE package is the interface for tuning SQL on demand. The related package DBMS_AUTO_SQLTUNE package provides the interface for SQL Tuning Advisor run as an automated task.



Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)