Oracle Top 20 Parallel SQL

SELECT * FROM
(SELECT substr(sql_text,1,100) sql,
ELAPSED_TIME, CPU_TIME,
disk_reads, executions,
disk_reads/executions "Reads/Exec",
hash_value,address
FROM V$SQLAREA
WHERE ( hash_value, address ) in (
SELECT distinct HASH_VALUE, address
FROM v$sql_plan
WHERE DISTRIBUTION is not null )
and disk_reads > 1000
ORDER BY ELAPSED_TIME DESC)
WHERE rownum <= 20;

Warning: Product user profile information not loaded!

SQL> connect scott/XXXX
Error accessing PRODUCT_USER_PROFILE
Warning: Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
Connected.
SQL> connect system/XXXX
Connected.


SQL> @$ORACLE_HOME/sqlplus/admin/pupbld.sql

Difference between v$sql v$sqltext v$sqltext_with_newlines

v$sql : if you have multiple copies of the query:

select * from x

if user A and B are executing (in shared pool ) above query then we will have 2 rows in v$sql

v$sqlarea:- aggregate of v$sql. It selects out DISTINCT sql.select * from x
will appear there.

v$sqltext:- for full text

v$sqltext_with_newlines: - without the white space replacement and tab.

Database Point-in-Time Recovery RECOVER DATABASE UNTIL TIME

oracle only accept this command at the time of time base recovery (following format, delimited by single quotation marks 'YYYY-MM-DD:HH24:MI:SS' )

Performs a incomplete recovery (=Point in time recovery).

The format of the time is 'YYYY-MM-DD:HH24:MI:SS'


in sql prompt


SQL>recover automatic database until time '2010-05-09:10:45:00';

SQL>recover database UNTIL TIME '2010-05-08:08:42:00';





Pl don't try or waste your time in following date format at the time of Point in Time Recovery

to_date," " etc ...


SQL> alter session set nls_date_format='YYYY-MM-DD:HH24:MI:SS' ;

Session altered.

SQL> RECOVER DATABASE UNTIL TIME to_date('2010-05-08:08:42:00','YYYY-MM-DD:HH24:MI:SS');
ORA-00285: TIME not given as a string constant


SQL> RECOVER DATABASE UNTIL TIME "to_date('2010-05-08:08:42:00','YYYY-MM-DD:HH24:MI:SS')";
ORA-00972: identifier is too long


SQL> RECOVER DATABASE UNTIL TIME to_date('2010-05-08:08:42:00','YYYY-MM-DD:HI24:MI:SS');

ORA-00285: TIME not given as a string constant


SQL> RECOVER DATABASE UNTIL TIME "to_date('2010-05-08:08:42:00','YYYY-MM-DD:HI24:MI:SS')";

ORA-00972: identifier is too long


SQL> RECOVER DATABASE UNTIL TIME to_date('2010-05-08:08:42:00','YYYY-MM-DD:HI24:MI:SS') ;

ORA-00285: TIME not given as a string constant


SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS';

Session altered.

SQL> recover database UNTIL TIME "TO_DATE('08/05/2010 08:42:00','DD/MM/YYYY HH24:MI:SS')";

ORA-00972: identifier is too long


SQL> recover database UNTIL TIME TO_DATE('08/05/2010 08:42:00','DD/MM/YYYY HH24:MI:SS');
ORA-00285: TIME not given as a string constant

RECOVER DATABASE UNTIL TIME 'to_date('2006-12-11:10:35:00','YYYY-MM-DD:HH24:MI:SS')'

oracle 10g current SQL Monitor Query

from Web



SELECT /* SQL Monitor Query for >= 10g */
'(' || s.SID || ',' || s.serial# || ')' "sid/serial",
s.program "pgrm", s.terminal "term", s.username "db user",
DECODE (ROUND (s.last_call_et / 60),'0', '< 1',ROUND (s.last_call_et / 60)) "rtime (mins)",
DECODE (ps."px oper cnt", '', 'N/A', ps."px oper cnt") "px opers",
DECODE (ps."px count", '', 'N/A', ps."px count") "px slaves",
si.block_changes "sess bchgs", si.physical_reads "sess preads",
pss_pr."px preads" "child px preads",
si.consistent_gets "sess cgets", pss_cg."px cgets" "child px cgets",
ss_cpu."sess cpu" "sess cpu", pss_cpu."px cpu" "child px cpu",
sa.optimizer_cost "curr sql cost", su."blocks" "temp blocks",
t."used_ublk" "undo blocks", s.event "wait",
s.seconds_in_wait "wait secs", s.state "wait state",
s.sql_id "current sql id", sa.sql_fulltext "sql text",
DBMS_XPLAN.display_cursor (s.sql_id, s.sql_child_number) "sql xplan"
FROM v$session s,
v$sess_io si,
v$sql sa,
(SELECT qcsid, COUNT (DISTINCT server_set) "px oper cnt", COUNT (*) "px count"
FROM v$px_session
WHERE NOT server_set IS NULL
GROUP BY qcsid, DEGREE) ps,
(SELECT qcsid, SUM (VALUE) "px preads"
FROM v$px_sesstat
WHERE statistic# = 54 AND SID != qcsid
GROUP BY qcsid) pss_pr,
(SELECT qcsid, SUM (VALUE) "px cgets"
FROM v$px_sesstat pss
WHERE statistic# = 50 AND SID != qcsid
GROUP BY qcsid) pss_cg,
(SELECT qcsid, SUM (VALUE) "px cpu"
FROM v$px_sesstat pss
WHERE statistic# = 12 AND SID != qcsid
GROUP BY qcsid) pss_cpu,
(SELECT ss.SID, SUM (ss.VALUE) "sess cpu"
FROM v$sesstat ss
WHERE statistic# = 12
GROUP BY ss.SID) ss_cpu,
(SELECT t.ses_addr, SUM (t.used_ublk) "used_ublk"
FROM v$transaction t
GROUP BY t.ses_addr) t,
(SELECT su.session_addr, SUM (su.blocks) "blocks"
FROM v$sort_usage su
GROUP BY su.session_addr) su
WHERE s.sql_address = sa.address
AND s.sql_hash_value = sa.hash_value
AND s.saddr = su.session_addr(+)
AND s.SID = ps.qcsid(+)
AND s.SID = si.SID(+)
AND s.saddr = t.ses_addr(+)
AND s.SID = pss_pr.qcsid(+)
AND s.SID = pss_cg.qcsid(+)
AND s.SID = pss_cpu.qcsid(+)
AND s.SID = ss_cpu.SID(+)
AND s.TYPE != 'BACKGROUND'
AND s.status = 'ACTIVE'
AND program NOT LIKE ('%(C%') --Eliminate Streams Capture
AND program NOT LIKE ('%(A%') --Eliminate Streams Apply
AND program NOT LIKE ('%(P%') --Eliminate Parallel Slaves
ORDER BY sa.optimizer_cost DESC;

oracle 10g find large full scans SQL and objects

SELECT sql_id,sql_text,
total_large_scans,
executions,
executions * total_large_scans sum_large_scans
FROM
(SELECT c.sql_id sql_id, sql_text,
count(*) total_large_scans,
executions
FROM sys.v_$sql_plan a,
sys.dba_segments b,
sys.v_$sql c
WHERE a.object_owner (+) = b.owner
AND a.object_name (+) = b.segment_name
AND b.segment_type IN ('TABLE', 'TABLE PARTITION')
AND a.operation LIKE '%TABLE%'
AND a.options = 'FULL'
AND c.hash_value = a.hash_value
AND b.bytes / 1024 > 1024
and PARSING_SCHEMA_NAME not in ('SYS','SYSTEM')
group by c.sql_id, sql_text, executions)
order by 4 desc
/





col TABLE_OWNER format a12

select sql_id,
table_owner,
table_name,
table_type,
size_kb,
statement_count,
reference_count,
executions,
executions * reference_count total_scans
from
(select c.sql_id sql_id,
a.object_owner table_owner,
a.object_name table_name,
b.segment_type table_type,
b.bytes / 1024 size_kb,
sum(c.executions ) executions,
count( distinct a.hash_value ) statement_count,
count( * ) reference_count
from
sys.v_$sql_plan a,
sys.dba_segments b,
sys.v_$sql c
where a.object_owner (+) = b.owner
and a.object_name (+) = b.segment_name
and b.segment_type in ('TABLE', 'TABLE PARTITION')
and a.operation like '%TABLE%'
and a.options = 'FULL'
and a.hash_value = c.hash_value
and b.bytes / 1024 > 1024
and PARSING_SCHEMA_NAME not in ('SYS','SYSTEM')
-- and a.object_owner not in ('SYS','SYSTEM')
and a.object_owner not in ('SYS','SYSTEM','PERFSTAT','SYSMAN','WKSYS')
group by c.sql_id,a.object_owner, a.object_name, a.operation, b.bytes / 1024, b.segment_type
order by 4 desc, 1, 2 );

oracle tablespace cron job

-bash-3.00$ cat /export/home/oracle/scripts/space.sh
#!/bin/bash
export ORACLE_BASE=/opt/oracle
export ORACLE_HOME=/opt/oracle/product/10.2
export PATH=$ORACLE_HOME/bin:.:$PATH
export ORACLE_SID=cccdb

sqlplus -s /nolog <connect / as sysdba
set pagesize 100 lines 120
set feed off heading off
break on REPORT;
compute sum label 'Total' of SIZE_MB on REPORT;
compute sum label 'Total Free MB' of FREE_MB on REPORT;
compute sum label 'Total Used MB' of USED_MB on REPORT;
COLUMN dummy NOPRINT
COLUMN pct_used FORMAT 999.9 HEADING "%|Used"
COLUMN name FORMAT a21 HEADING "Tablespace Name"
COLUMN Kbytes FORMAT 999,999,999 HEADING "MBytes"
COLUMN used FORMAT 999,999,999 HEADING "Used"
COLUMN free FORMAT 999,999,999 HEADING "Free"
COLUMN largest FORMAT 999,999,999 HEADING "Largest"
COLUMN maxsize FORMAT 999,999,999 HEADING "MAXSIZE"
BREAK ON report
COMPUTE sum OF kbytes ON REPORT
COMPUTE sum OF free ON REPORT
COMPUTE sum OF used ON REPORT

spool tablespace1.alert
select '********************************************' from dual;
select 'TABLESPACE REPORT1 PRODUCTION DATABASE ' from dual;
select to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS') as "Report Time" from dual;
select '********************************************' from dual;
set feed on heading on
SELECT
NVL(b.tablespace_name,nvl(a.tablespace_name,'UNKOWN')) name
, kbytes_alloc/1024 kbytes
, nvl(kbytes_alloc/1024,0) - NVL(kbytes_free/1024,0) used
, NVL(kbytes_free,0) free
, ((kbytes_alloc-NVL(kbytes_free,0))/kbytes_alloc)*100 pct_used
, NVL(largest,0) largest
,MAXBYTES/1024/1024 MAXSIZE
,AUTOEXTENSIBLE
FROM ( SELECT SUM(bytes)/1024 Kbytes_free
, MAX(bytes)/1024 largest
, tablespace_name
FROM sys.dba_free_space
GROUP BY tablespace_name
) a
, ( SELECT SUM(bytes)/1024 Kbytes_alloc
, tablespace_name,AUTOEXTENSIBLE,MAXBYTES
FROM sys.dba_data_files
GROUP BY tablespace_name,AUTOEXTENSIBLE,MAXBYTES
) b
WHERE a.tablespace_name (+) = b.tablespace_name
order by 1;

spool off
exit
EOF
if [ `cat tablespace1.alert|wc -l` -gt 0 ]
then
# echo "test"
cat tablespace1.alert > tablespace1.tmp
# mailx -s "TABLESPACE ALERT for Production Database" -r oracle@joshua anuj.@tus.co.uk < tablespace1.tmp
#/usr/lib/sendmail -s "TABLESPACE ALERT1 for Joshua" anuj.singh@pus.co.uk < tablespace1.tmp

#cat tablespace.tmp | mailx -s "mail test" anuj.s@ptus.co.uk
fi

cron job for top oracle sql waits

-bash-3.00$ cat /export/home/oracle/scripts/topsqlwait.sh

#! /bin/ksh

export ORACLE_BASE=/opt/oracle
export ORACLE_HOME=/opt/oracle/product/10.2
export PATH=$ORACLE_HOME/bin:.:$PATH
export ORACLE_SID=cccdb

sqlplus -s /nolog <<-EOF set pagesize 0 connect / as sysdba -- set pagesize 100 lines 120 set lines 120 set feed off set heading on set long 500000 spool topsql4.alert select '**************************************************************' from dual; select '........ Oracle Wait Report On C Database...................' from dual; select '**************************************************************' from dual; select to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS') as "Report Time" from dual; set linesize 90 -- set pagesize 4000 prompt SQL to identify heavy SQL (Get the SQL with heavy BUFFER_GETS) set heading on prompt ================================================================== select sql_text ,executions ,disk_reads ,buffer_gets from v\$sqlarea where decode(executions,0,buffer_gets,buffer_gets/executions) >
(select avg(decode(executions,0,buffer_gets,buffer_gets/executions))
+ stddev(decode(executions,0,buffer_gets,buffer_gets/executions)) from v\$sqlarea)
and PARSING_SCHEMA_NAME='PROD1'
/
prompt ==================================================================

prompt SQL to identify heavy SQL (Get the SQL with heavy DISK_READS)

select sql_text ,executions ,disk_reads ,buffer_gets from v\$sqlarea
where decode(executions ,0,disk_reads,disk_reads/executions) > (select
avg(decode(executions,0,disk_reads,disk_reads/executions)) +
stddev(decode(executions,0,disk_reads,disk_reads/executions)) from v\$sqlarea)
and PARSING_SCHEMA_NAME='PROD1'
/
prompt ==================================================================


Prompt 20+++++++ SQL FULL TABLE SCAN


select * from (select t.SQL_FULLTEXT,t.HASH_VALUE,t.executions from v\$sqlarea t, v\$sql_plan p
where t.hash_value=p.hash_value and p.operation='TABLE ACCESS'
and p.options='FULL'
and p.object_owner in ('PROD1')
order by EXECUTIONS desc )
where rownum <=20 / prompt ================================================================== prompt Top 10 by Buffer Gets: SELECT * FROM (SELECT sql_text, buffer_gets, executions, buffer_gets/executions "Gets/Exec", hash_value,address FROM V\$SQLAREA WHERE buffer_gets > 10000
ORDER BY buffer_gets DESC)
WHERE rownum <= 10 / prompt ================================================================== prompt Top 10 by Physical Reads SELECT * FROM (SELECT sql_text, disk_reads, executions, disk_reads/executions "Reads/Exec", hash_value,address FROM V\$SQLAREA WHERE disk_reads > 1000
and PARSING_SCHEMA_NAME='PROD1'
ORDER BY disk_reads DESC)
WHERE rownum <=10 / prompt ================================================================== prompt Top 10 by Executions SELECT * FROM (SELECT sql_text,executions, rows_processed, rows_processed/executions "Rows/Exec", hash_value,address FROM V\$SQLAREA WHERE executions > 100
and PARSING_SCHEMA_NAME='PROD1'
ORDER BY executions DESC)
WHERE rownum <10 / prompt ================================================================== prompt Top 10 by Parse Calls: SELECT * FROM (SELECT sql_text, parse_calls, executions, hash_value,address FROM V\$SQLAREA WHERE parse_calls > 1000
and PARSING_SCHEMA_NAME='PROD1'
ORDER BY parse_calls DESC)
WHERE rownum <=10 / prompt ================================================================== prompt Top 10 by Sharable Memory: SELECT * FROM (SELECT sql_text, sharable_mem, executions, hash_value,address FROM V\$SQLAREA WHERE sharable_mem > 1048576
ORDER BY sharable_mem DESC)
WHERE rownum <= 10 / prompt ================================================================== prompt Top 10 by Version Count: SELECT * FROM (SELECT sql_text, version_count, executions, hash_value,address FROM V\$SQLAREA WHERE version_count > 20
and PARSING_SCHEMA_NAME='PROD1'
ORDER BY version_count DESC)
WHERE rownum <= 10 / prompt ================================================================== set heading on spool off exit EOF cat topsql4.alert >anuj.txt

EMAIL=`grep -v '^[ ]*$' topsql4.alert`

if [ "$EMAIL" ]
then
# echo "test"
#echo "$EMAIL" |/usr/bin/mailx -s "Oracle WAIT ALERT for C DATABASE " -r oracle@josa anuj08@gmail.co.uk
echo "$EMAIL">topsql4.tmp
fi

oracle Find Active SQL Statements

set serverout on size 999999
declare
begin
dbms_output.put_line(' ');
dbms_output.put_line('************* Start report for WAITING sessions with current SQL ***************');
for x in (select vs.sid || ',' || vs.serial# sidser, vs.sql_address, vs.sql_hash_value,
vs.last_call_et, vsw.seconds_in_wait, vsw.event, vsw.state
from v$session_wait vsw, v$session vs
where vsw.sid = vs.sid
and vs.type <> 'BACKGROUND'
and vsw.event NOT IN ('rdbms ipc message'
,'smon timer'
,'pmon timer'
,'SQL*Net message from client'
,'lock manager wait for remote message'
,'ges remote message'
,'gcs remote message'
,'gcs for action'
,'client message'
,'pipe get'
,'Null event'
,'PX Idle Wait'
,'single-task message'
,'PX Deq: Execution Msg'
,'KXFQ: kxfqdeq - normal deqeue'
,'listen endpoint status'
,'slave wait'
,'wakeup time manager'))
loop
begin
dbms_output.put_line('Event WaitState InstID SidSerial LastCallEt SecondsInWait');
dbms_output.put_line('************************* ******************** ****** *********** ********** *************');
dbms_output.put_line(rpad(x.event,25) ||' '|| rpad(x.state,20) ||' '|| lpad(x.sidser,11) ||'
'|| lpad(x.last_call_et,10) ||' '|| lpad(x.seconds_in_wait,13));
dbms_output.put_line(' SQLText ');
dbms_output.put_line('****************************************************************');
for y in (select sql_text
from v$sqltext
where address = x.sql_address
and hash_value = x.sql_hash_value
-- and inst_id = x.inst_id
order by piece)
loop
dbms_output.put_line(y.sql_text);
end loop;
end;
end loop;
dbms_output.put_line('************** End re! port for sessions waiting with current SQL ****************');
dbms_output.put_line(' ');
end;
/

oracle WARNING: AQ_TM_PROCESSES is set to 0

in alert log <<<<
replication_dependency_tracking turned off (no async multimaster replication found)
WARNING: AQ_TM_PROCESSES is set to 0. System operation might be adversely affected.
Completed: ALTER DATABASE OPEN



as per metalink 428441.1




SQL> declare
2 mycheck number;
3 begin
4 select 1 into mycheck from v$parameter where name = 'aq_tm_processes' and value = '0'
5 and (ismodified <> 'FALSE' OR isdefault='FALSE');
6 if mycheck = 1 then
7 dbms_output.put_line('The parameter ''aq_tm_processes'' is explicitly set to 0!');
8 end if;
9 exception when no_data_found then
10 dbms_output.put_line('The parameter ''aq_tm_processes'' is not explicitly set to 0.');
11 end;
12 /

PL/SQL procedure successfully completed.

SQL> set serveroutput on
SQL> /
The parameter 'aq_tm_processes' is explicitly set to 0!

PL/SQL procedure successfully completed.


conn / as sysdba


SQL> alter system set aq_tm_processes = 1 scope=spfile ;

System altered.



re start database then check the value


SQL> col TYPE format a15
set linesize 200


SQL> show parameter aq_tm_processes

NAME TYPE VALUE
------------------------------------ --------------- ------------------------------
aq_tm_processes integer 1

oracle 10g Collecting Real Time Wait Events

Oracle wait info



drop table real_time_session_waits
/
create table real_time_session_waits
( snap_tm date not null,
seq# number not null,
sid number not null,
event varchar2(64) not null,
p1 number,
p2 number,
p3 number,
wait_time number,
seconds_in_wait number,
object_id number )

/


SQL> @w
SQL> Enter time is seconds to run: 30



w.sql

truncate table real_time_session_waits -- comment out to keep history
/



accept tm prompt 'Enter time in seconds to run: '
variable tm_t number;

truncate table real_time_session_waits -- comment out to keep history
/




declare

iteration number;
start_time date;

begin
:tm_t := '&&tm';
select sysdate into start_time from dual;

iteration := 24*60*60/:tm_t;

while sysdate < start_time+1/iteration loop

insert into real_time_session_waits (SNAP_TM,
SEQ#,
SID,
EVENT,
P1,
P2,
P3,
WAIT_TIME,
SECONDS_IN_WAIT,
OBJECT_ID )
select sysdate,
a.seq#,
a.sid,
a.event,
a.p1,
a.p2,
a.p3,
a.wait_time,
a.seconds_in_wait,
b.row_wait_obj#
from v$session_wait a,
v$session b
where a.sid = b.sid
-- and a.sid = 'session id' -- only insert a single session
and a.event not in
('pmon timer',
'rdbms ipc message',
'smon timer',
'dispatcher timer',
'Null event',
'parallel query dequeue wait',
'parallel query idle wait - Slaves',
'pipe get',
'PL/SQL lock timer',
'slave wait',
'virtual circuit status',
'lock element cleanup',
'WMON goes to sleep'
);
dbms_lock.sleep(1); -- sleep for one second before checking and inserting next record.
-- Can take this out for more detail or change value for longer
-- runs.
end loop;
commit;
end;
/




set linesize 150
col sid format 99999 heading "Session ID"
col event format a45 heading "Wait Event"
col cnt heading "Wait Count"
col wait heading "Wait time(cs)"
col pct format 999.99 heading "% of Time"
select sid,event, count(*) cnt, sum(wait_time) wait
from real_time_session_waits
where wait_time > 0 -- this will limit wait times from sessions that have completed event and are > 1 sc.
-- and sid = XX -- change to limit by sid
-- filter out idle events
and event not in ('SQL*Net break/reset to client',
'SQL*Net message to client',
'SQL*Net message from client',
'SQL*Net more data to client')
group by sid,event
order by wait
/




set linesize 120
col event format a30

select * from ( select event, total_waits, total_timeouts, time_waited
from v$system_event
where event not like 'SQL*Net%'
and event not in ('pmon timer','rdbms ipc message','dispatcher timer','smon timer')
order by time_waited desc )
where rownum < 10;



SQL> select * from ( select event, total_waits, total_timeouts, time_waited
from v$system_event
where event not like 'SQL*Net%'
and event not in ('pmon timer','rdbms ipc message','dispatcher timer','smon timer')
order by time_waited desc )
where rownum < 10;
2 3 4 5 6
EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED
---------------------------------------------------------------- ----------- -------------- -----------
DIAG idle wait 4014401 4014401 401792698
shared server idle wait 66964 66963 200954924
Streams AQ: qmn coordinator idle wait 143494 71747 200953644
Streams AQ: qmn slave idle wait 73415 0 200952381
Streams AQ: waiting for time management or cleanup tasks 7134 4745 200891813
Space Manager: slave idle wait 405099 401211 200885816
jobq slave wait 1360408 1354900 68009146
VKRM Idle 23 0 67679254
control file parallel write 711619 0 1682008



SQL> SELECT count(*), event
FROM v$session_wait
WHERE wait_time = 0
AND event NOT IN ('smon timer','pipe get','wakeup time manager','pmon timer','rdbms ipc message', 'SQL*Net message from client')
GROUP BY event
ORDER BY 1 DESC;

COUNT(*) EVENT
---------- ----------------------------------------------------------------
2 DIAG idle wait
1 Space Manager: slave idle wait
1 Streams AQ: qmn coordinator idle wait
1 Streams AQ: waiting for time management or cleanup tasks
1 Streams AQ: qmn slave idle wait
1 VKTM Logical Idle Wait

6 rows selected.



SELECT count(*), event FROM v$session_wait
WHERE wait_time = 0
-- AND event NOT IN ('smon timer','pipe get','wakeup time manager','pmon timer','rdbms ipc message', 'SQL*Net message from client')
GROUP BY event
ORDER BY 1 DESC; 2 3 4 5

COUNT(*) EVENT
---------- ----------------------------------------------------------------
16 rdbms ipc message
2 DIAG idle wait
1 smon timer
1 Space Manager: slave idle wait
1 VKTM Logical Idle Wait
1 Streams AQ: qmn coordinator idle wait
1 Streams AQ: waiting for time management or cleanup tasks
1 pmon timer
1 SQL*Net message from client
1 Streams AQ: qmn slave idle wait

10 rows selected.






clear breaks
break on event# on event on total_ms_waited
column event# format 999 heading "Event#"
column event format a30 heading "Event"
column wait_time_ms format 999,999 heading "Maximum|Wait Time (ms)"
column wait_count format 999,999,999,999 heading "Wait|Count"
column percent_wait_count format 990.00 heading "Percent|Wait Count"
column percent_wait_time format 990.00 heading "Percent|Wait Time"
column total_waited_s format 999,999,999,999 heading "Total|Waited (s)"
SELECT event#
, event
, SUM(wait_time_milli * wait_count / 1000) OVER(PARTITION BY event#) total_waited_s
, wait_time_milli wait_time_ms
, wait_count
, ROUND(100 * RATIO_TO_REPORT(wait_count) OVER(PARTITION BY event#),2) percent_wait_count
, ROUND(100 * RATIO_TO_REPORT(wait_time_milli * wait_count) OVER(PARTITION BY event#),2) percent_wait_time
FROM v$event_histogram
ORDER BY event, wait_time_ms



---

WHERE event IN (
'xblock change tracking buffer space'
,'buffer busy waits'
,'db file parallel read'
,'db file parallel write'
,'db file scattered read'
,'db file sequential read'
,'db file single write'
,'direct path read'
,'direct path read temp'
,'direct path write'
,'direct path write temp'
,'local write wait'
,'Log archive I/O'
,'log file parallel write'
,'log file sequential read'
,'log file single write'
)
ORDER BY event, wait_time_ms
----

oracle active sql for last 5min

oracle active sql for last 5min
Oracle active session info



SELECT topsql.sql_id ,txt.sql_text
,topsql.active_hits ,topsql.pctload*100 pctload
FROM (SELECT sql_id ,COUNT(*) active_hits
,ROUND(COUNT(*)/SUM(COUNT(*)) OVER (), 2) pctload
FROM v$active_session_history
WHERE session_type!='BACKGROUND'
AND sample_time > sysdate -5/1440
GROUP BY sql_id
ORDER BY COUNT(*) DESC
) topsql
,v$sqltext txt
WHERE topsql.sql_id = txt.sql_id
AND txt.piece = 0
AND ROWNUM <= 30
;


active sql for last 1 min

select sql_id,count(*), count(*)*100/sum(count(*)) over () pctload
from v$active_session_history
where sample_time > sysdate - 1/24/60
and session_type <> 'BACKGROUND'
group by sql_id
order by count(*) desc ;



IO

select sql_id,count(*) from v$active_session_history
where sample_time > sysdate - 1/24/60
and wait_class='User I/O'
group by sql_id
order by count(*) desc ;

Wait

select sum(a.time_waited) total_time
from v$active_session_history a , v$event_name b
where a.event# = b.event# and
sample_time > '02-JUL-11 12:00:00 AM' and
sample_time <'02-JUL-11 05:00:00 AM' and
b.wait_class='User I/O'

oracle top sql given time

Top SQLs Elaps time and CPU time in a given time range..

SELECT SQL_TEXT,X.CPU_TIME
FROM DBA_HIST_SQLTEXT DHST,
(SELECT DHSS.SQL_ID SQL_ID,SUM(DHSS.CPU_TIME_DELTA) CPU_TIME
FROM DBA_HIST_SQLSTAT DHSS
WHERE DHSS.SNAP_ID IN(SELECT SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE BEGIN_INTERVAL_TIME>=TO_DATE('01-may-2010 08:00', 'dd-mon-yyyy hh24:mi')
AND END_INTERVAL_TIME<=TO_DATE('01-may-2010 16:00', 'dd-mon-yyyy hh24:mi')) GROUP BY DHSS.SQL_ID) X
WHERE X.SQL_ID=DHST.SQL_ID
ORDER BY X.CPU_TIME DESC;

More.. --X.ELAPSED_TIME/1000000 => From Micro second to second

--X.ELAPSED_TIME/1000000/X.EXECUTIONS_DELTA => How many times the sql ran

SELECT SQL_TEXT
,ROUND(X.ELAPSED_TIME/1000000/X.EXECUTIONS_DELTA,3) ELAPSED_TIME_SEC
,ROUND(X.CPU_TIME /1000000/X.EXECUTIONS_DELTA,3) CPU_TIME_SEC
, EXECUTIONS_DELTA
,X.ELAPSED_TIME
,X.CPU_TIME
,X.EXECUTIONS_DELTA
FROM DBA_HIST_SQLTEXT DHST,
(SELECT DHSS.SQL_ID SQL_ID,SUM(DHSS.CPU_TIME_DELTA) CPU_TIME,
SUM (DHSS.ELAPSED_TIME_DELTA) ELAPSED_TIME
, SUM(DHSS.EXECUTIONS_DELTA) EXECUTIONS_DELTA
FROM DBA_HIST_SQLSTAT DHSS
WHERE DHSS.SNAP_ID IN (SELECT SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE BEGIN_INTERVAL_TIME >= TO_DATE('01-may-2010 08:00', 'dd-mon-yyyy hh24:mi')
AND END_INTERVAL_TIME <= TO_DATE('01-may-2010 16:00', 'dd-mon-yyyy hh24:mi'))
GROUP BY DHSS.SQL_ID) X
WHERE X.SQL_ID=DHST.SQL_ID
ORDER BY ELAPSED_TIME_SEC DESC;

********

For specific owners sql history for given time ..

SELECT SQL_TEXT
,ROUND(X.ELAPSED_TIME/1000000,3) ELAPSED_TIME_SEC
,ROUND(X.CPU_TIME /1000000,3) CPU_TIME_SEC
FROM DBA_HIST_SQLTEXT DHST,
(SELECT DHSS.SQL_ID SQL_ID, SUM(DHSS.CPU_TIME_DELTA) CPU_TIME
, SUM (DHSS.ELAPSED_TIME_DELTA) ELAPSED_TIME
, SUM(DHSS.EXECUTIONS_DELTA) EXECUTIONS_DELTA
FROM DBA_HIST_SQLSTAT DHSS
WHERE DHSS.SNAP_ID IN
(SELECT SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE
BEGIN_INTERVAL_TIME >= TO_DATE('01-may-2010 08:00', 'dd-mon-yyyy hh24:mi')
AND END_INTERVAL_TIME <= TO_DATE('01-may-2010 14:00', 'dd-mon-yyyy hh24:mi'))
AND DHSS.parsing_schema_name='SCOTT'
GROUP BY DHSS.SQL_ID ) X
WHERE X.SQL_ID = DHST.SQL_ID
ORDER BY ELAPSED_TIME_SEC DESC;

or

with sql id

SELECT X.SQL_ID,SQL_TEXT
,ROUND(X.ELAPSED_TIME/1000000,3) ELAPSED_TIME_SEC
,ROUND(X.CPU_TIME /1000000,3) CPU_TIME_SEC
FROM DBA_HIST_SQLTEXT DHST,
(SELECT DHSS.SQL_ID SQL_ID, SUM(DHSS.CPU_TIME_DELTA) CPU_TIME
, SUM (DHSS.ELAPSED_TIME_DELTA) ELAPSED_TIME
, SUM(DHSS.EXECUTIONS_DELTA) EXECUTIONS_DELTA
FROM DBA_HIST_SQLSTAT DHSS
WHERE DHSS.SNAP_ID IN (SELECT SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE
BEGIN_INTERVAL_TIME >= TO_DATE('08-feb-2011 08:00', 'dd-mon-yyyy hh24:mi')
AND END_INTERVAL_TIME <= TO_DATE('08-feb-2011 14:00', 'dd-mon-yyyy hh24:mi'))
-- AND DHSS.parsing_schema_name='SCOTT'
GROUP BY DHSS.SQL_ID ) X
WHERE X.SQL_ID = DHST.SQL_ID
ORDER BY ELAPSED_TIME_SEC DESC;

*********


How many Times a query executed?

Per Hour sql execution growth:
Begin_interval_time means snapshot interval time.
As we fixed this 60 min so here you can see the execution growth (per hour).
The execution delta will show you the no of queries execution (per hour).

select s.begin_interval_time, sql.sql_id as sql_id, sql.executions_delta as exe_delta, sql.EXECUTIONS_TOTAL
from dba_hist_sqlstat sql, dba_hist_snapshot s
where sql_id='&sqlid'
and s.snap_id = SQL.snap_id
and s.begin_interval_time> TO_date('01-may-2010 14:00', 'dd-mon-yyyy hh24:mi')
and s.begin_interval_time< TO_date('01-may-2010 18:30', 'dd-mon-yyyy hh24:mi')
order by s.begin_interval_time;

Oracle active waits info  .. 

ash oracle waiting info 

set pagesize 200

select
ash.inst_id,
ash.sql_id ,
sum(decode(ash.session_state,'ON CPU',1,0)) "CPU",
sum(decode(ash.session_state,'WAITING',1,0)) - sum(decode(ash.session_state,'WAITING', decode(en.wait_class, 'User I/O',1,0),0)) "WAIT" ,
sum(decode(ash.session_state,'WAITING', decode(en.wait_class, 'User I/O',1,0),0)) "IO" ,
sum(decode(ash.session_state,'ON CPU',1,1)) "TOTAL"
from gv$active_session_history ash,v$event_name en
where SQL_ID is not NULL 
and en.event#=ash.event#
and USER_ID not in ( select USER_ID from dba_users where USERNAME in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' ) ) 
group by ash.inst_id,ash.sql_id
having sum(decode(ash.session_state,'WAITING',1,0)) - sum(decode(ash.session_state,'WAITING', decode(en.wait_class, 'User I/O',1,0),0)) !=0
order by 6 desc ;



select * from (
 select inst_id,
 sql_id ,
 sum(decode(session_state,'ON CPU',1,0)) as CPU,
 sum(decode(session_state,'WAITING',1,0)) - sum(decode(session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0)) as WAIT,
 sum(decode(session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0)) as IO,
 sum(decode(session_state,'ON CPU',1,1)) as TOTAL
 from gv$active_session_history
 where 1=1 
 and SQL_ID is not NULL
 and USER_ID not in ( select USER_ID from dba_users where USERNAME in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' ) ) 
 and SESSION_TYPE!='BACKGROUND'
 group by inst_id,sql_id
 order by sum(decode(session_state,'ON CPU',1,1)) desc
 )
where rownum <21 ;

 INST_ID SQL_ID CPU WAIT IO TOTAL
---------- ------------- ---------- ---------- ---------- ----------
 1 8gnjy50gxbwnc 0 3 0 3
 1 fj7ft4r6nathc 1 0 0 1
 1 6jxvh6g4t6xfw 0 1 0 1
 1 0fuvy5t4x00xd 0 1 0 1
 1 f58bxv021xat3 0 1 0 1
 1 axwq64tsktbnx 1 0 0 1

6 rows selected.



set long 10000 pagesize 1000
select sql_fulltext from gv$sql where sql_id='&sql_id';


set pages 300 lines 200
-- Shared Pool
select * from table(dbms_xplan.display_cursor('&SQL_ID',null,'ALL'));
--select * from table(dbms_xplan.display_cursor('&SQL_ID'));
--select * from table(dbms_xplan.display_cursor('&SQL_ID',0,'ALLSTATS LAST'));
--select * from table(dbms_xplan.display_cursor('&SQL_ID',0,'TYPICAL OUTLINE'));
--select * from table(dbms_xplan.display_cursor('&SQL_ID',null,'ADVANCED OUTLINE ALLSTATS LAST +PEEKED_BINDS'));

-- AWR
select * from table(dbms_xplan.display_awr('&SQL_ID',null,null,'ALL'));
select * from table(dbms_xplan.display_awr('&SQL_ID',null,DBID,'ALL'));
-- select * from table(dbms_xplan.display_awr('&1',null,null,'ADVANCED OUTLINE ALLSTATS LAST +PEEKED_BINDS'));




set pages 300 lines 200
col PLAN_TABLE_OUTPUT for a200
select plan_table_output
from gv$sql s, table(dbms_xplan.display_cursor(s.sql_id, s.child_number,'ADVANCED OUTLINE ALLSTATS LAST +PEEKED_BINDS')) t
where sql_id='&SQL_ID';

oracle 10g waits in last 30 min

--- last 30 minutes ( sysdate . 60/2880 )
--- WHERE (date) > sysdate - 7/1440; Past 7 minutes

spool wait_last_30min.txt
set pagesize 200
select to_char( sysdate, 'DD/MM/YY HH24:MI:SS' ) "Right Now" from dual;

prompt What resource is currently in high demand last 30 minutes?

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
/

-- Which user is waiting the most last 30 minutes?

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
/

prompt What SQL is currently using the most resources last 30 minutes ?

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 - 60/2880 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
/


col oBJECT_NAME format a30
col OBJECT_TYPE format a15
col EVENT format a30

prompt What object is currently causing the highest resource waits last 30 minutes?

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 - 60/2880 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
/