Gather stats on the table

 col TABLE_NAME for a30

col PARTITION_NAME for a20
col SUBPARTITION_NAME for a20
select OWNER,TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,NUM_ROWS,LAST_ANALYZED from DBA_TAB_STATISTICS where STALE_STATS='YES';

OR

select OWNER,TABLE_NAME,LAST_ANALYZED,STALE_STATS from DBA_TAB_STATISTICS where OWNER='&OWNER' AND STALE_STATS='YES';
set linesize 200 trimspool on
set pagesize 100
set pause off echo off verify off
set head on term on 


col begin_interval_time format a30
col min_sess_per_day format 99,999 head 'MIN|SESS|PER|DAY'
col max_sess_per_day format 99,999 head 'MAX|SESS|PER|DAY'
col avg_sess_per_day format 99,999 head 'AVG|SESS|PER|DAY'
col min_proc_per_day format 99,999 head 'MIN|PROC|PER|DAY'
col max_proc_per_day format 99,999 head 'MAX|PROC|PER|DAY'
col avg_proc_per_day format 99,999 head 'AVG|PROC|PER|DAY'


with processes as (
	select snap_id, CURRENT_UTILIZATION process_count
	from dba_hist_RESOURCE_LIMIT
	where resource_name = 'processes'
),
sessions as (
	select snap_id, CURRENT_UTILIZATION session_count
	from dba_hist_RESOURCE_LIMIT
	where resource_name = 'sessions'
)
select distinct
	sn.begin_interval_time
	, min(s.session_count) over ( partition by trunc(sn.begin_interval_time)) min_sess_per_day
	, max(s.session_count) over ( partition by trunc(sn.begin_interval_time)) max_sess_per_day
	, round(avg(s.session_count) over ( partition by trunc(sn.begin_interval_time)),1) avg_sess_per_day
	, min(p.process_count) over ( partition by trunc(sn.begin_interval_time)) min_proc_per_day
	, max(p.process_count) over ( partition by trunc(sn.begin_interval_time)) max_proc_per_day
	, round(avg(p.process_count) over ( partition by trunc(sn.begin_interval_time)),1) avg_proc_per_day
from dba_hist_snapshot sn
right outer join sessions s on s.snap_id = sn.snap_id
right outer join processes p on p.snap_id = sn.snap_id
order by sn.begin_interval_time
/
SELECT OWNER,TABLE_NAME,LAST_ANALYZED,STALE_STATS from DBA_TAB_STATISTICS WHERE TABLE_NAME='&TNAME';

-- awr-top-sqlid-events.sql
-- get top 5 events per AWR snapshot, per sql_id
-- Jared Still still@pythian.com jkstill@gmail.com
--
-- 2016-11-02 jkstill - added enqueue decode

-- requires https://github.com/jkstill/oracle-script-lib/blob/master/get_date_range.sql

-- prompt for date range
@get_date_range

-- or just specify it here
--@get_date_range '2018-07-01 00:00:00' '2018-07-12 08:00:00'

@clears 

set linesize 200 trimspool on
set pagesize 60

col event format a30
col p1text format a20
col p1 format a25
col p2text format a20

spool aws-top-sqlid-event.log

break on begin_interval skip 1

with snaps as (
select 
min(snap_id) min_snap_id
, max(snap_id) max_snap_id
from dba_hist_snapshot
where begin_interval_time >= to_date(:v_begin_date,'&d_date_format')
and end_interval_time <= to_date(:v_end_date,'&d_date_format')
),
data as (
select 
h.snap_id
, h.dbid
, h.instance_number
, case h.session_state
when 'ON CPU' then 'CPU'
else h.event
end event
, h.p1text
, CASE
WHEN event like 'enq%' THEN
'0x'||trim(to_char(h.p1, 'XXXXXXXXXXXXXXXX'))||': '||
chr(bitand(h.p1, 4278190080)/16777216)||
chr(bitand(h.p1,16711680)/65536)||
' mode '||bitand(h.p1, power(2,14)-1)
ELSE NULL 
END AS p1
, h.sql_id
from dba_hist_active_sess_history h
where h.sql_id is not null
),
agg_data as (
select  distinct
to_char(hs.begin_interval_time,'&d_date_format') begin_interval
, d.instance_number
, d.sql_id
, d.event
, d.p1text
, d.p1
, count(d.event) over (partition by d.snap_id, d.sql_id, d.event, d.instance_number) event_count
from data d
join dba_hist_snapshot hs on hs.snap_id = d.snap_id
and hs.instance_number = d.instance_number
and hs.dbid = d.dbid
and hs.snap_id between (select min_snap_id from snaps)
and (select max_snap_id from snaps)
--where rownum <= 200
order by begin_interval, event_count desc, instance_number
), 
rank_output as (
select
begin_interval
, instance_number
, sql_id
, event
, p1text
, p1
, event_count
, row_number() over (partition by begin_interval, instance_number order by event_count desc) event_rank
from agg_data
)
select 
begin_interval
, instance_number
, sql_id
, event
, p1text
, p1
, event_count
, event_rank
from rank_output
where event_rank <= 5
order by begin_interval, event_count desc
/

spool off

ed aws-top-sqlid-event.log

Comments

Post a Comment

Popular posts from this blog

QUERY DATAGUARD AND STANDBY STATUS

-- Find which logs were applied in the last day  SELECT SEQUENCE#, to_char(FIRST_TIME,'hh24:mi:ss dd/mm/yyyy'), to_char(NEXT_TIME,'hh24:mi:ss dd/mm/yyyy'),APPLIED FROM V$ARCHIVED_LOG where next_time>sysdate-1 ORDER BY SEQUENCE# ; -- Find last applied log   select to_char(max(FIRST_TIME),'hh24:mi:ss dd/mm/yyyy') FROM V$ARCHIVED_LOG where applied='YES';   -- What are the managed standby processes doing?   SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;   -- Are we on production or standby?  SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE;   -- Check for errors  SELECT MESSAGE FROM V$DATAGUARD_STATUS;   -- Check that the DB was openned correctly   SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS; -- important lag statistics  select * from v$dataguard_stats;  -- co...
Read more

Blocking session on RAC database

Blocking session on RAC database Blocking session of the databases Generate the report for Blocking session The SQL will provide Blocking sessions with SID, node information SELECT DISTINCT S1.USERNAME || '@' || S1.MACHINE || ' ( INST=' || S1.INST_ID || ' SID=' || S1.SID || ' ) IS BLOCKING ' || S2.USERNAME || '@' || S2.MACHINE || ' ( INST=' || S1.INST_ID || ' SID=' || S2.SID || ' ) ' AS BLOCKING_STATUS FROM GV$LOCK L1, GV$SESSION S1, GV$LOCK L2, GV$SESSION S2 WHERE S1.SID=L1.SID AND S2.SID=L2.SID AND S1.INST_ID=L1.INST_ID AND S2.INST_ID=L2.INST_ID AND L1.BLOCK > 0 AND L2.REQUEST > 0 AND L1.ID1 = L2.ID1 AND L1.ID2 = L2.ID2; Blockers information select s1.username || '@' || s1.machine  || ' ( SID=' || s1.sid || ' ) is blocking '  || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status from gv$lock l1, gv$session s...
Read more

Finding object size in Oracle database

Ref: http://www.runningoracle.com/product_info.php?cPath=2_44&products_id=153 Finding object size in Oracle database is very important and common. Is it very useful to know the exact size occupied by the object at the tablespace. The object size in the following scripts is in Mbytes. The scripts have been formatted to work very easily with TOAD SQL Editor. For example you can filter with tablespace_name, or owner, or size (for example more than 1GByte)  SELECT owner, segment_name, segment_type, partition_name, ROUND(bytes/(1024*1024),2) SIZE_MB, tablespace_name  FROM DBA_SEGMENTS  WHERE SEGMENT_TYPE IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION',  'INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION', 'TEMPORARY')  --AND TABLESPACE_NAME LIKE 'COSTE%'  --AND SEGMENT_NAME LIKE 'P2010201%'  --AND partition_name LIKE 'P20100201%' --AND segment_type = 'TABLE' --AND OWNER = 'TARG...
Read more