oracle常用sql整理

1、holder

with holder_tree  as 
(select distinct ff.INST_ID,
       level level_num,
       SYS_CONNECT_BY_PATH(ff.sid,'-->') path,
       ff.SID,
       ff.SERIAL#,
       ff.username,
       ff.BLOCKING_INSTANCE,
       ff.EVENT,
       ff.SQL_ID,
       ff.ROW_WAIT_OBJ#
  from gv$session ff
 START WITH ff.BLOCKING_SESSION is not null
CONNECT BY  nocycle PRIOR ff.BLOCKING_SESSION =  ff.sid) 
select distinct max(level_num) level_num,
       ee.path,
       ee.SID,
       ee.SERIAL#,
       ee.EVENT,
       ee.SQL_ID,
       ee.ROW_WAIT_OBJ#,
       ee.username,
       ee.BLOCKING_INSTANCE
 from holder_tree ee
       group by ee.path,
       ee.SID,
       ee.SERIAL#,
       ee.username,
       ee.BLOCKING_INSTANCE,
       ee.EVENT,
       ee.SQL_ID,
       ee.ROW_WAIT_OBJ# 
       order by level_num desc;

2、event

 sum(decode(wait_Time,0,0,1)) "Prev", 
 sum(decode(wait_Time,0,1,0)) "Curr",
 count(*) "Tot" 
 from gv$session_Wait 
 group by event,INST_ID 
 order by 1,4 desc;

3、FRA区使用率

归档日志量：

       count(*),   
       round(sum(blocks * block_size) / 1024 / 1024) mbsize   
  from (select trunc(first_time, 'hh') as logtime, a.BLOCKS, a.BLOCK_SIZE   
          from v$archived_log a   
         where a.DEST_ID = 1   
           and a.FIRST_TIME > trunc(sysdate-1))   
 group by logtime   
 order by logtime desc;  

4、内存OPS

   select * from v$memory_resize_ops;

5、sql相关

v$sql
v$sql_cursor---bind值
dba_hist_sqlbind
v$sql_shared_cursor----解析
dba_hist_sqltext

spid to sql

select se.username,
       se.sid,
       se.serial#,
       se.osuser,
       se.machine,
       se.program,
       se.logon_time,
       sa.sql_text,
       sa.sql_id
  from v$session se, v$sqlarea sa, v$process pr
 where se.SQL_ADDRESS = sa.ADDRESS
   and se.SQL_HASH_VALUE = sa.HASH_VALUE
   and se.PADDR=pr.ADDR
   and pr.spid = '&SPID';  

6、temp

  from (select t.sample_time,
               s.PARSING_SCHEMA_NAME,
               t.sql_id,
               t.sql_child_number as sql_child,
               round(t.temp_space_allocated / 1024 / 1024 / 1024, 2) || ' G' as temp_used,
               round(t.temp_space_allocated /
                     (select sum(decode(d.autoextensible, 'YES', d.maxbytes, d.bytes))
                        from dba_temp_files d),
                     2) * 100 || ' %' as temp_pct,
               t.program,
               t.module,
               s.SQL_TEXT
          from v$active_session_history t, v$sql s
         where t.sample_time > to_date('&begin_time', 'yyyy-mm-dd hh24:mi:ss')
           and t.sample_time < to_date('&end_time', 'yyyy-mm-dd hh24:mi:ss')
           and t.temp_space_allocated is not null
           and t.sql_id = s.SQL_ID
         order by t.temp_space_allocated desc)
 where rownum < 50
 order by temp_used desc;

7、ash awr

select s.snap_id,
       s.dbid,
       s.instance_number,
       to_char(s.end_interval_time, 'yyyy-mm-dd hh24:mi:ss') cdate
  from dba_hist_snapshot s
 order by snap_id desc;
 
select dbms_workload_repository.awr_report_html(1594936874,
                                                1,
                                                153940,
                                                153941)
  from dual;
  
select dbms_workload_repository.ash_report_html(1594936874,
                                                1,
                                                to_date('2015-07-17 10:00:00',
                                                        'yyyy-mm-dd hh24:mi:ss'),
                                                to_date('2015-07-17 10:05:00',
                                                        'yyyy-mm-dd hh24:mi:ss'))

8、查询sql历史执行信息（次数，时间,HASH_VALUES变化等）

  s.instance_number,
  to_char(sn.end_interval_time, 'YYYYMMDD HH24:MI:SS'),
  s.plan_hash_value,
  s.executions_delta,
  round(s.elapsed_time_delta / s.executions_delta)/1000 elapsed_time_ms ,
  round(s.BUFFER_GETS_delta / s.executions_delta),
  round(s.CPU_TIME_delta / s.executions_delta)/1000 CPU_TIME_ms
  from dba_hist_snapshot sn, sys.WRH$_SQLSTAT s
where s.snap_id = sn.snap_id
   and s.sql_id = '5t21ty9qhhpw2'
   and s.instance_number = sn.instance_number
   and s.executions_delta > 0
order by sn.end_interval_time desc;