Feeds:
Posts
Comments

Archive for October, 2013

Friends as I have told in my last post ” oracle-monitoring-scripts ” that soon I will be back with some performance related queries. So, it’s been a little late but here they are. 

  • High water mark status for a given table

SET SERVEROUTPUT ON
SET VERIFY OFF

DECLARE
CURSOR cu_tables IS
SELECT a.owner,
a.table_name
FROM all_tables a
WHERE a.table_name = Decode(Upper(‘&&1′),’ALL’,a.table_name,Upper(‘&&1’))
and a.owner = Upper(‘&&2’);

op1 NUMBER;
op2 NUMBER;
op3 NUMBER;
op4 NUMBER;
op5 NUMBER;
op6 NUMBER;
op7 NUMBER;
BEGIN

Dbms_Output.Disable;
Dbms_Output.Enable(1000000);
Dbms_Output.Put_Line(‘TABLE UNUSED BLOCKS TOTAL BLOCKS HIGH WATER MARK’);
Dbms_Output.Put_Line(‘—————————— ————— ————— —————‘);
FOR cur_rec IN cu_tables LOOP
Dbms_Space.Unused_Space(cur_rec.owner,cur_rec.table_name,’TABLE’,op1,op2,op3,op4,op5,op6,op7);
Dbms_Output.Put_Line(RPad(cur_rec.table_name,30,’ ‘) ||
LPad(op3,15,’ ‘) ||
LPad(op1,15,’ ‘) ||
LPad(Trunc(op1-op3-1),15,’ ‘));
END LOOP;

END;
/

SET VERIFY ON;

  • To see the server metrics on OS level (Best for RAC)

SELECT ‘Time now is ‘ METRIC_NAME,
To_char(sysdate, ‘dd/mm/yyyy hh24:mi:ss’) VALUE
FROM DUAL
UNION
SELECT to_char(inst_id) || ‘_’ || METRIC_NAME METRIC_NAME,
to_char(VALUE) value
FROM gV$SYSMETRIC
WHERE METRIC_NAME IN (‘Database CPU Time Ratio’,’Database Wait Time Ratio’)
AND INTSIZE_CSEC = (SELECT MAX(INTSIZE_CSEC) FROM gV$SYSMETRIC where inst_id=1)
and inst_id=1
UNION
SELECT to_char(inst_id) || ‘_’ || METRIC_NAME METRIC_NAME,
to_char(VALUE) value
FROM gV$SYSMETRIC
WHERE METRIC_NAME IN (‘Database CPU Time Ratio’,’Database Wait Time Ratio’)
AND INTSIZE_CSEC = (SELECT MAX(INTSIZE_CSEC) FROM gV$SYSMETRIC where inst_id=2)
and inst_id=2 ;

  • To see the long running sqls

SELECT s.SQL_TEXT,
vw.inst_id,
vw.username,
vw.osuser
–vw.machine ,
–vw.terminal ,vw.sid,vw.serial# , vw.spid,
–‘alter system kill session’ ||””||vw.sid||’,’||vw.serial#||”’;’ dbsession,
— ‘!kill -9 ‘||vw.spid osprocess
FROM
(
SELECT s.inst_id,
s.sid,
s.serial#,
s.username,
sl.sql_hash_value,
sl.sql_address,
s.program,
s.osuser,
s.machine,
s.terminal,
p.spid,
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 gv$session s,
gv$session_longops sl ,
gv$process p
WHERE s.sid = sl.sid
AND s.inst_id = sl.inst_id
AND s.serial# = sl.serial#
AND ROUND(sl.elapsed_seconds/60) > 1 AND ROUND(sl.sofar/sl.totalwork*100, 2) < 100
and p.addr = s.paddr
and s.inst_id = p.inst_id
)VW LEFT OUTER JOIN gv$sql s ON
s.hash_value = VW.sql_hash_value
AND s.address = VW.sql_address
and vw.inst_id = s.inst_id
–and trim(vw.program) = ‘SQL Developer’
AND s.child_number = 0

  • Database metrics for everything

select 1 INST_ID,
CASE METRIC_NAME
WHEN ‘SQL Service Response Time’ then ‘SQL Service Response Time (secs)’
WHEN ‘Response Time Per Txn’ then ‘Response Time Per Txn (secs)’
ELSE METRIC_NAME
END METRIC_NAME,
CASE METRIC_NAME
WHEN ‘SQL Service Response Time’ then ROUND((MINVAL / 100),2)
WHEN ‘Response Time Per Txn’ then ROUND((MINVAL / 100),2)
ELSE MINVAL
END MININUM,
CASE METRIC_NAME
WHEN ‘SQL Service Response Time’ then ROUND((MAXVAL / 100),2)
WHEN ‘Response Time Per Txn’ then ROUND((MAXVAL / 100),2)
ELSE MAXVAL
END MAXIMUM,
CASE METRIC_NAME
WHEN ‘SQL Service Response Time’ then ROUND((AVERAGE / 100),2)
WHEN ‘Response Time Per Txn’ then ROUND((AVERAGE / 100),2)
ELSE AVERAGE
END AVERAGE
FROM GV$SYSMETRIC_SUMMARY
where METRIC_NAME in (‘CPU Usage Per Sec’,
‘CPU Usage Per Txn’,
‘Database CPU Time Ratio’,
‘Database Wait Time Ratio’,
‘Executions Per Sec’,
‘Executions Per Txn’,
‘Response Time Per Txn’,
‘SQL Service Response Time’,
‘User Transaction Per Sec’)
AND INTSIZE_CSEC = (SELECT MAX(INTSIZE_CSEC) FROM GV$SYSMETRIC_SUMMARY WHERE INST_ID=1)
AND INST_ID=1
UNION
select 2 INST_ID,
CASE METRIC_NAME
WHEN ‘SQL Service Response Time’ then ‘SQL Service Response Time (secs)’
WHEN ‘Response Time Per Txn’ then ‘Response Time Per Txn (secs)’
ELSE METRIC_NAME
END METRIC_NAME,
CASE METRIC_NAME
WHEN ‘SQL Service Response Time’ then ROUND((MINVAL / 100),2)
WHEN ‘Response Time Per Txn’ then ROUND((MINVAL / 100),2)
ELSE MINVAL
END MININUM,
CASE METRIC_NAME
WHEN ‘SQL Service Response Time’ then ROUND((MAXVAL / 100),2)
WHEN ‘Response Time Per Txn’ then ROUND((MAXVAL / 100),2)
ELSE MAXVAL
END MAXIMUM,
CASE METRIC_NAME
WHEN ‘SQL Service Response Time’ then ROUND((AVERAGE / 100),2)
WHEN ‘Response Time Per Txn’ then ROUND((AVERAGE / 100),2)
ELSE AVERAGE
END AVERAGE
FROM GV$SYSMETRIC_SUMMARY
where METRIC_NAME in (‘CPU Usage Per Sec’,
‘CPU Usage Per Txn’,
‘Database CPU Time Ratio’,
‘Database Wait Time Ratio’,
‘Executions Per Sec’,
‘Executions Per Txn’,
‘Response Time Per Txn’,
‘SQL Service Response Time’,
‘User Transaction Per Sec’)
AND INTSIZE_CSEC = (SELECT MAX(INTSIZE_CSEC) FROM GV$SYSMETRIC_SUMMARY WHERE INST_ID=2)
AND INST_ID=2
ORDER BY 1

  • Displays segment advice for the specified segment.

SET SERVEROUTPUT ON SIZE 1000000
SET LINESIZE 200
SET VERIFY OFF

DECLARE
l_object_id NUMBER;
l_task_name VARCHAR2(32767) := ‘SEGMENT_ADVISOR’;
l_object_type VARCHAR2(32767) := UPPER(‘&object_type’);
l_attr1 VARCHAR2(32767) := UPPER(‘&object_owner’);
l_attr2 VARCHAR2(32767) := UPPER(‘&object_name’);
BEGIN
IF l_attr2 = ‘NULL’ THEN
l_attr2 := NULL;
END IF;

DBMS_ADVISOR.create_task (
advisor_name => ‘Segment Advisor’,
task_name => l_task_name);

DBMS_ADVISOR.create_object (
task_name => l_task_name,
object_type => l_object_type,
attr1 => l_attr1,
attr2 => l_attr2,
attr3 => NULL,
attr4 => ‘null’,
attr5 => NULL,
object_id => l_object_id);

DBMS_ADVISOR.set_task_parameter (
task_name => l_task_name,
parameter => ‘RECOMMEND_ALL’,
value => ‘TRUE’);

DBMS_ADVISOR.execute_task(task_name => l_task_name);

FOR cur_rec IN (SELECT f.impact,
o.type,
o.attr1,
o.attr2,
f.message,
f.more_info
FROM dba_advisor_findings f
JOIN dba_advisor_objects o ON f.object_id = o.object_id AND f.task_name = o.task_name
WHERE f.task_name = l_task_name
ORDER BY f.impact DESC)
LOOP
DBMS_OUTPUT.put_line(‘..’);
DBMS_OUTPUT.put_line(‘Type : ‘ || cur_rec.type);
DBMS_OUTPUT.put_line(‘Attr1 : ‘ || cur_rec.attr1);
DBMS_OUTPUT.put_line(‘Attr2 : ‘ || cur_rec.attr2);
DBMS_OUTPUT.put_line(‘Message : ‘ || cur_rec.message);
DBMS_OUTPUT.put_line(‘More info : ‘ || cur_rec.more_info);
END LOOP;

DBMS_ADVISOR.delete_task(task_name => l_task_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line(‘Error : ‘ || DBMS_UTILITY.format_error_backtrace);
DBMS_ADVISOR.delete_task(task_name => l_task_name);
END;
/

  • Getting current PGA status

SELECT 1 instnum,
pga_aggr /(1024 * 1024 * 1024),
tot_pag_alloc /(1024 * 1024 * 1024),
(tot_pag_used)/ (1024* 1024* 1024)
FROM
(select value tot_pag_used
from gv$pgastat where name = ‘total PGA inuse’ and inst_id = 1),
(select value tot_pag_alloc
from gv$pgastat where name = ‘total PGA allocated’ and inst_id = 1 ),
(select value pga_aggr from gv$parameter where UPPER(NAME)=’PGA_AGGREGATE_TARGET’ and inst_id = 1)
UNION
SELECT 2 instnum,
pga_aggr /(1024 * 1024 * 1024),
tot_pag_alloc /(1024 * 1024 * 1024),
(tot_pag_used)/ (1024* 1024* 1024)
FROM
(select value tot_pag_used
from gv$pgastat where name = ‘total PGA inuse’ and inst_id = 2),
(select value tot_pag_alloc
from gv$pgastat where name = ‘total PGA allocated’ and inst_id = 2 ),
(select value pga_aggr from gv$parameter where UPPER(NAME)=’PGA_AGGREGATE_TARGET’ and inst_id = 2);

  • Getting current SGA info

select inst_id,  ROUND(SUM(BYTES)/1024/1024/1024,2) from gv$sgainfo WHERE RESIZEABLE=’Yes’ group by inst_id;

  • To see sessions per node

select count(sid) Total_session,
count(case status when ‘ACTIVE’ THEN 1 ELSE NULL END) Active_session,
count(case status when ‘INACTIVE’ THEN 1 ELSE NULL END ) Inactive_Session,
inst_id
from gv$session
WHERE USERNAME IN ( ‘SEFPLLIVE’) –> Use this clause for particular schemas
— AND LOGON_TIME >= SYSDATE – INTERVAL ‘5’ MINUTE –> use this clause for more current information
group by inst_id order by 4;

  • Dictionary hit ratio

SELECT inst_id,
(1 – (Sum(getmisses)/(Sum(gets) + Sum(getmisses)))) * 100 Dict_Hit_Ratio
FROM gv$rowcache
group by inst_id;

  • db_buffer cache hit ratio

SELECT 1 inst_id,
(1 – (phys.value / (db.value + cons.value))) * 100 Buff_Cache_Hit_Ratio
FROM gv$sysstat phys,
gv$sysstat db,
gv$sysstat cons
WHERE phys.name = ‘physical reads’
AND db.name = ‘db block gets’
AND cons.name = ‘consistent gets’
and phys.inst_id = 1
and db.inst_id = 1
and cons.inst_id = 1
UNION
SELECT 2 inst_id,
(1 – (phys.value / (db.value + cons.value))) * 100 Buff_Cache_Hit_Ratio
FROM gv$sysstat phys,
gv$sysstat db,
gv$sysstat cons
WHERE phys.name = ‘physical reads’
AND db.name = ‘db block gets’
AND cons.name = ‘consistent gets’
and phys.inst_id = 1
and db.inst_id = 1
and cons.inst_id = 1

/

  • Latch hit ratio

SELECT inst_id,
(1 – (Sum(misses) / Sum(gets))) * 100 Latch_Hit_Ratio
FROM gv$latch
group by inst_id;

  • Displays a list of SQL statements that are using the most resources.

SELECT *
FROM (SELECT Substr(a.sql_text,1,100) sql_text,
NVL(p.username, ‘(PROD)’) AS username,
p.osuser,
p.sid,
p.serial#,
Trunc(a.disk_reads/Decode(a.executions,0,1,a.executions)) reads_per_execution,
a.buffer_gets,
a.disk_reads,
a.executions,
a.sorts,
a.address
FROM v$sqlarea a,
v$session p
ORDER BY 2 DESC)
WHERE rownum <= 5;

  • DB CACHE ADVICE

select inst_id,
SIZE_FOR_ESTIMATE,
BUFFERS_FOR_ESTIMATE,
ESTD_PHYSICAL_READ_FACTOR,
ESTD_PHYSICAL_READS
FROM GV$db_cache_advice
WHERE NAME = ‘DEFAULT’
AND BLOCK_SIZE = (SELECT value from V$PARAMETER WHERE name = ‘db_block_size’)
AND ADVICE_STATUS = ‘ON

  • SGA TARGET ADVICE

select inst_id, sga_size, sga_size_factor, estd_db_time, estd_db_time_factor, estd_physical_reads from gv$sga_target_advice
order by inst_id, sga_size_factor;

  • TABLESPACE Fragmentation details

SELECT
TABLESPACE_NAME,COUNT(*) FREE_CHUNKS,decode( round((max(BYTES) / 1024000),2),
null,0, round((max(BYTES) / 1024000),2)) largest_chunk,
nvl(round(sqrt(max(BLOCKS)/sum(BLOCKS))*(100/sqrt(sqrt(count(BLOCKS)) )),2), 0) FRAG_INDEX
FROM
SYS.DBA_FREE_SPACE
GROUP BY
TABLESPACE_NAME
ORDER BY 2 DESC, 1;

Thats it. These scripts are few which I use to monitor for one of my project which was on RAC (Real Application Clusters).

You can also follow Tim Hall blog for more.

Soon I will be back with some shell scripts which You can you for monitoring.

Thanks & Regards

Nimai Karmakar

Read Full Post »

Johribazaar's Blog

Just another WordPress.com weblog

Ayurveda and Yoga

Site for Ayurveda and Yoga articles

SanOraLife

Few adventures in my slow running life...

pavankumaroracledba

4 out of 5 dentists recommend this WordPress.com site

ORACLE WRITES

Technical Articles by Kanchana Selvakumar

SAP Basis Cafe

SAP Basis Cafe - Exploring SAP R/3 Basis World

Life Is A Mystery

This Is Wihemdra's Blog...I Share What I Have

ursvenkat's Blog

Oracle Applications DBA Blog

The Ivica Arsov Blog

Database Management & Performance

gumpx

DBA's online diary