oracle 常用 script

9. 查看top 10 sql
---TOP DISK Reads(totally)


select *
  from (select a.disk_reads "Physical Reads",
               a.executions "Executions",
               a.disk_reads / decode(a.executions, 0, 1, a.executions) "Reads per Exec",
               a.cpu_time / 1000000 "CPU Time (s)",
               a.elapsed_time / 100000 "Elapsed Time (s)",
               a.sql_id,
               a.PARSING_SCHEMA_NAME,
               a.sql_text sql
          from v$sqlarea a
         order by 1 desc)
 where rownum < 11;


---TOP DISK Reads Exec 


select *
  from (select a.disk_reads "Physical Reads",
               a.executions "Executions",
               a.disk_reads / decode(a.executions, 0, 1, a.executions) "Reads per Exec",
               a.cpu_time / 1000000 "CPU Time(s)",
               a.elapsed_time / 100000 "Elapsed Time (s)",
               a.sql_id,
               a.PARSING_SCHEMA_NAME,
               a.sql_text sql
          from v$sqlarea a
         order by 3 desc)
 where rownum < 11;


---TOP Elapsed Time(totally) 单次执行时间最长的sql


select *
  from (select a.elapsed_time / 100000 "Elapsed Time (s)",
               a.cpu_time / 1000000 "CPU Time(s)",
               a.executions "Executions",
               a.elapsed_time / decode(a.executions, 0, 1, a.executions) /
               1000000 "Elap per Exec (s)",
               a.cpu_time / decode(a.executions, 0, 1, a.executions) /
               1000000 "CPU Time per Exec (s)",
               a.sql_id,
               a.PARSING_SCHEMA_NAME,
               a.sql_text sql
          from v$sqlarea a
         order by 1 desc)
 where rownum < 11;
 
---TOP Elapsed Time Exec


select *
  from (select a.elapsed_time / 100000 "Elapsed Time (s)",
               a.cpu_time / 1000000 "CPU Time(s)",
               a.executions "Executions",
               a.elapsed_time / decode(a.executions, 0, 1, a.executions)/1000000 "Elap per Exec (s)",
               a.cpu_time / decode(a.executions, 0, 1, a.executions)/1000000 "CPU Time per Exec (s)",
               a.sql_id,
               a.PARSING_SCHEMA_NAME,
               a.sql_text sql
          from v$sqlarea a
         order by 4 desc)
 where rownum < 11;


---TOP Buffer Gets(totally)


select *
  from (select a.buffer_gets "Buffer Gets",
               a.executions "Executions",
               a.buffer_gets / decode(a.executions, 0, 1, a.executions) "Gets per Exec",
               a.cpu_time / 1000000 "CPU Time (s)",
               a.elapsed_time / 100000 "Elapsed Time (s)",
               a.sql_id,
               a.PARSING_SCHEMA_NAME,
               a.sql_text sql
          from v$sqlarea a
         order by 1 desc)
 where rownum < 11;


---TOP Buffer Gets Exec 


select *
  from (select a.buffer_gets "Buffer Gets",
               a.executions "Executions",
               a.buffer_gets / decode(a.executions, 0, 1, a.executions) "Gets per Exec",
               a.cpu_time / 1000000 "CPU Time(s)",
               a.elapsed_time / 100000 "Elapsed Time (s)",
               a.sql_id,
               a.PARSING_SCHEMA_NAME,
               a.sql_text sql
          from v$sqlarea a
         order by 3 desc)
 where rownum < 11;




10. 查看走了FULL TABLE SCAN 的表 以及INDEX FAST FULL SCAN
如果发现AWR里面 db file scattered read比较靠前，可以自己定制脚本，查询出走 FULL TABLE SCAN 的表 以及INDEX FAST FULL SCAN 的索引






select distinct a.executions,a.sql_id,a.sql_text, b.object_owner, b.object_name,b.object_type
  from v$sql a, v$sql_plan_statistics_all b
 where a.sql_id = b.sql_id
   and operation = 'TABLE ACCESS'
   and options = 'FULL'
   and object_type = 'TABLE'
   and object_owner not like '%SYS%' order by 1 desc ;




select distinct a.executions,a.sql_id,a.sql_text, b.object_owner, b.object_name,b.object_type
  from v$sql a, v$sql_plan_statistics_all b
 where a.sql_id = b.sql_id
   and options = 'FAST FULL SCAN'
   and object_type like 'INDEX%'
   and object_owner not like '%SYS%' order by 1 desc ;






下面脚本 查询7天以来最繁忙的时间段，按照 DBTIME_MINUTES 降序排序


select b.instance_number,
       b.snap_id as begin_snap,
       lead(b.snap_Id,1,null) over (order by b.snap_Id) as end_snap,
       to_char(b.end_interval_time,'YYYY-MM-DD hh24:MI:SS') as begin_time ,
       to_char(lead(b.end_interval_time,1,null) over (order by b.snap_id),'YYYY-MM-DD hh24:MI:SS') as end_time,   
       extract(hour from  lead(b.end_interval_time,1,null) over (order by b.snap_id) - b.end_interval_time ) * 60 
       + extract(minute from  lead(b.end_interval_time,1,null) over (order by b.snap_id) - b.end_interval_time )  as Elapsed_minutes,       
       a.value as db_time_us ,
       round((lead(a.value,1,null) over (order by b.snap_id) -  a.value)/1000000/60,2) as DbTime_minutes
  from sys.WRH$_SYS_TIME_MODEL A, dba_hist_snapshot B, sys.wrh$_stat_name C
 where a.dbid = b.dbid
   and a.instance_number=b.instance_number
   and a.snap_id = b.snap_id
   and a.stat_id = c.stat_id
   and b.instance_number=1
   and c.stat_name = 'DB time'
   and b.end_interval_time>systimestamp - 7 
   ORDER BY 8 DESC




select object_name as 对象名称,s.sid,s.serial#,p.spid as 系统进程号
  from v$locked_object l , 
       dba_objects o , 
       v$session s , 
       v$process p
 where l.object_id=o.object_id 
   and l.session_id=s.sid 
   and s.paddr=p.addr;




Page 366: 


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


insert into trx_log
 select level, sysdate + level/24/60, round(dbms_random.value*100) 
   from dual 
connect by level <= 20;             








with a as
(
select 'abcdeft' as a1 from dual
union all
select 'aaabbb' as a1 from dual
)
select regexp_replace(a.a1,'(...)(.+$)','\1123\2') from a;
 
REGEXP_REPLACE(A.A1,'(...)(.+$
--------------------------------------------------------------------------------
abc123deft
aaa123bbb






with a as
(
select 'abcdeft' as a1 from dual
union all
select 'aaabbb' as a1 from dual
union all
select 'a' as a1 from dual
)
select regexp_replace(a.a1,'(...)(.+$)','\1123\2') from a;
 
REGEXP_REPLACE(A.A1,'(...)(.+$
--------------------------------------------------------------------------------
abc123deft
aaa123bbb
a
 


查看归档切换频率:


select thread#,
       trunc(first_time) "Date",
       to_char(first_time, 'Dy') "Day",
       substr(count(1), 1, 5) as "Total",
       substr(SUM(decode(to_char(first_time, 'hh24'), '00', 1, 0)), 1, 3) as "h00",
       substr(SUM(decode(to_char(first_time, 'hh24'), '01', 1, 0)), 1, 3) as "h01",
       substr(SUM(decode(to_char(first_time, 'hh24'), '02', 1, 0)), 1, 3) as "h02",
       substr(SUM(decode(to_char(first_time, 'hh24'), '03', 1, 0)), 1, 3) as "h03",
       substr(SUM(decode(to_char(first_time, 'hh24'), '04', 1, 0)), 1, 3) as "h04",
       substr(SUM(decode(to_char(first_time, 'hh24'), '05', 1, 0)), 1, 3) as "h05",
       substr(SUM(decode(to_char(first_time, 'hh24'), '06', 1, 0)), 1, 3) as "h06",
       substr(SUM(decode(to_char(first_time, 'hh24'), '07', 1, 0)), 1, 3) as "h07",
       substr(SUM(decode(to_char(first_time, 'hh24'), '08', 1, 0)), 1, 3) as "h08",
       substr(SUM(decode(to_char(first_time, 'hh24'), '09', 1, 0)), 1, 3) as "h09",
       substr(SUM(decode(to_char(first_time, 'hh24'), '10', 1, 0)), 1, 3) as "h10",
       substr(SUM(decode(to_char(first_time, 'hh24'), '11', 1, 0)), 1, 3) as "h11",
       substr(SUM(decode(to_char(first_time, 'hh24'), '12', 1, 0)), 1, 3) as "h12",
       substr(SUM(decode(to_char(first_time, 'hh24'), '13', 1, 0)), 1, 3) as "h13",
       substr(SUM(decode(to_char(first_time, 'hh24'), '14', 1, 0)), 1, 3) as "h14",
       substr(SUM(decode(to_char(first_time, 'hh24'), '15', 1, 0)), 1, 3) as "h15",
       substr(SUM(decode(to_char(first_time, 'hh24'), '16', 1, 0)), 1, 3) as "h16",
       substr(SUM(decode(to_char(first_time, 'hh24'), '17', 1, 0)), 1, 3) as "h17",
       substr(SUM(decode(to_char(first_time, 'hh24'), '18', 1, 0)), 1, 3) as "h18",
       substr(SUM(decode(to_char(first_time, 'hh24'), '19', 1, 0)), 1, 3) as "h19",
       substr(SUM(decode(to_char(first_time, 'hh24'), '20', 1, 0)), 1, 3) as "h20",
       substr(SUM(decode(to_char(first_time, 'hh24'), '21', 1, 0)), 1, 3) as "h21",
       substr(SUM(decode(to_char(first_time, 'hh24'), '22', 1, 0)), 1, 3) as "h22",
       substr(SUM(decode(to_char(first_time, 'hh24'), '23', 1, 0)), 1, 3) as "h23"
  from V$log_history
 group by trunc(first_time), to_char(first_time, 'Dy'), thread#
 order by 1, 2;




查看归档切换频率:


alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
set pages 9999


select sequence#, 
       first_time Log_started, 
       lead(first_time,1,NULL) over(order by first_time) Log_ended
  from (select distinct sequence#, first_time 
          from dba_hist_log
         where archived='YES' 
           and sequence#! = 0 
         order by first_time 
       ) 
 order by sequence#;