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;
Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s