Daily Scripts – Part 2

-- Datafile Tuning
 select vtablespace.name tablespace_name,
 vdatafile.name datafile_name,
 vfilestat.phyrds physical_reads,
 vfilestat.phywrts physical_writes,
 vfilestat.avgiotim average_time_for_io
 from v$datafile vdatafile,
 v$filestat vfilestat,
 v$tablespace vtablespace
 where vdatafile.file# = vfilestat.file#
 and vdatafile.ts# = vtablespace.ts#
 order by 3 desc ,4 desc;
-- Tuning Queries, to identify SQL commands to improve
 * DISK:
 prompt 'Gathering information by Disk ...';
 set lin 300
 set pages 10000
select exec, buff, disk, num_rows, sql_text, ROUND(buff/exec,2) "Buffer_Exec", ROUND(num_rows / exec,2) "rows_Exec",
 round( round(buff/exec,2)/decode(round(num_rows / exec,2),0,1,round(num_rows / exec,2)),2) "Blocks per Row",
 round(disk/exec,0) Disk_Exec,address, hash
 from (
 select sum(executions) exec, sum(buffer_gets) buff, sum(disk_reads) disk, sum(rows_processed) num_rows,
 min(sql_text) sql_text, min(address) address, min(hash_value) hash
 from gv$sql
 WHERE COMMAND_TYPE <> 47
 group by address
 order by 3 desc)
 where rownum<20;
* BUFFER GET
 prompt 'Gathering information per Buffer gets ...';
select * from
 (
 select exec, buff, disk, num_rows, sql_text, round(buff/exec,2) "Buffer_Exec", round(num_rows / exec,2) "num_rows_Exec",
 round( round(buff/exec,2)/decode(round(num_rows / exec,2),0,1,round(num_rows / exec,2)),2) "Block per Row",
 address, hash_value
 from (
 select
 sum(executions) exec,
 sum(buffer_gets) buff,
 sum(disk_reads) disk,
 sum(rows_processed) num_rows,
 min(sql_text) sql_text,
 min(address) address,
 min(hash_value) hash_value
 from gv$sql
 where COMMAND_TYPE <> 47
 group by address, hash_value
 order by 2 desc)
 where rownum<20)
 order by 8 desc;
* CPU
 prompt 'Gathering information per CPU ...';
select exec, cpu, round(cpu / exec,2) cpu_exec,buff, disk, num_rows, sql_text, round(buff/exec,2) "Buffer_Exec", round(num_rows / exec,2) "num_rows_Exec",
 round( round(buff/exec,2)/decode(round(num_rows / exec,2),0,1,round(num_rows / exec,2)),2) "Blocks per row",
 address, hash_value
 from (
 select
 sum(executions) exec,
 sum(buffer_gets) buff,
 sum(disk_reads) disk,
 sum(cpu_time) cpu,
 sum(rows_processed) num_rows,
 min(sql_text) sql_text,
 min(address) address,
 min(hash_value) hash_value
 from gv$sql
 where COMMAND_TYPE <> 47
 group by address
 order by 4 desc)
 where rownum<20
 order by 3 desc;
-- Library Cache PIN
 set lin 100
 set timing off
 COL event FORMAT a20 TRUNC;
 tti "Users Waiting for Library Cache Pins";
SELECT sid, event, p1raw, seconds_in_wait, wait_time
 FROM sys.v_$session_wait
 WHERE
 event = 'library cache pin'
 AND
 state = 'WAITING';
tti "Object blocker ..."
 COL owner format a8
 COL object format a70
 SELECT kglnaown AS owner, kglnaobj as Object
 FROM sys.x$kglob
 WHERE kglhdadr='&P1RAW';
tti "Waiting users ..."
 col SID_SERIAL format a12
 SELECT s.sid||','||s.serial# SID_SERIAL,s.username, kglpnmod "Mode Held", kglpnreq "Request"
 FROM sys.x$kglpn p, sys.v_$session s
 WHERE p.kglpnuse = s.saddr
 AND kglpnhdl   = '&P1RAW';
set lin 1000
 set timing on
-- Long OPS
 select distinct a.inst_id, a.start_time, a.last_update_time,
 round((a.time_remaining /60 ),0) minutos_restante, round((a.elapsed_seconds /60 ),0) minutos_percorridos,
 round((a.time_remaining / (a.elapsed_seconds + a.time_remaining)) * 100,2) "% Faltante",
 decode(round((a.time_remaining /60 ),0),0,'Finalizado','Executando ...') Status,
 b.sid, b.serial#, b.username, c.address, b.program, b.module, b.action, c.address, c.sql_text, a.message, a.units
 from GV$SESSION_LONGOPS a, Gv$session b, Gv$sql c
 where a.sid=b.sid and
 a.serial#=b.serial# and
 b.sql_address=c.address(+) and
 a.time_remaining> 0;

--- Oradebug
oradebug setospid 774373
oradebug event 10046 trace name context forever, level 12;
oradebug tracefile_name
<Processes>
oradebug event 10046 trace name context off

-- Get SQL text
select SQL_TEXT
from gv$sqltext where address = '&ADDR'
ORDER BY PIECE;

-- Information by SID
select distinct
a.sid, a.serial#,a.server, c.spid,a.inst_id,a.username, d.event, a.machine, a.program, a.module, a.osuser, a.action,to_char(a.logon_time,'dd-mon-rr hh24:mi:ss') logon_time, a.status,b.address,b.sql_text, round(a.last_call_et/60,1) Minutos, a.CLIENT_INFO
from gv$session a, gv$sql b, gv$process c, gv$session_wait d
where
a.SQL_ADDRESS=b.ADDRESS(+) and
a.inst_id=b.inst_id(+) and
a.paddr=c.addr and
a.inst_id=c.inst_id and
a.sid=&sid and
a.sid=d.sid and
a.inst_id=d.inst_id
order by 1,2;


-- Info Index

set heading off

select 1,’INDEX_NAME: ‘ || A.OWNER || ‘.’ || A.INDEX_NAME || ‘ – TIPO: ‘ || A.INDEX_TYPE || ‘ TABLE: ‘ || A.TABLE_NAME || ‘ – TABLESPACE:’ || A.TABLESPACE_NAME || ‘ – CRIACAO: ‘ || TO_CHAR(B.CREATED,’DD-MON-RR HH24:MI:SS’) “Informações”
FROM DBA_INDEXES a, dba_objects b
WHERE INDEX_NAME = UPPER(‘&INDEX’)
AND A.OWNER=B.OWNER
AND A.INDEX_NAME=B.OBJECT_NAME
AND B.OBJECT_TYPE=’INDEX’
UNION
SELECT 2,’COLUMNS:’
FROM DUAL
UNION
SELECT 3, ‘——————————————————‘ FROM DUAL
UNION
SELECT 4, ‘POSICAO: ‘ || TO_CHAR(COLUMN_POSITION) || ‘ – ‘ || COLUMN_NAME
FROM
(SELECT COLUMN_NAME, COLUMN_POSITION
FROM DBA_IND_COLUMNS
WHERE INDEX_NAME = UPPER(‘&INDEX’)
ORDER BY 2 ASC)
UNION
SELECT 5, ‘——————————————————‘ FROM DUAL
UNION
SELECT 6,’Dados:-> BLevel: ‘ || TRIM(TO_CHAR(BLEVEL)) || ‘ – FATOR DE SELETIVIDADE ( >= 90%): ‘ || TO_CHAR(ROUND((DISTINCT_KEYS / NUM_ROWS) * 100,2),’999.99’) || ‘% – PCT_FREE: ‘ || TO_CHAR(PCT_FREE) || ‘ Analyze: ‘ || to_char(last_analyzed,’dd-mon-rr hh24:mi’)
FROM DBA_INDEXES
WHERE INDEX_NAME = UPPER(‘&INDEX’)
ORDER BY 1;

set heading on

 


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s