Oracle dba常用sql

1.--根据hash_value查询单条语句执行情况
SELECT tim.start_time AS "daytime",
       ROUND(SUM(stf.active_time), 2) AS "in_oracle_time",
       ROUND(SUM(cpu_used), 2) AS "using_cpu_time",
       ROUND(SUM(wait_io), 2) AS "io_wait",
       ROUND(SUM(wait_buffer), 2) AS "buffer_wait",
       ROUND(SUM(wait_latch), 2) AS "latch_wait",
       SUM(executions_started) AS "exec_no",
       decode(SUM(executions_started),
              0,
              SUM(stf.active_time),
              ROUND(SUM(stf.active_time) / SUM(executions_started), 2)) AS "avg_exec_time"
  FROM fglpa4.quest_sc_sql_stat_fact    stf,
       fglpa4.quest_time_dim            tim,
       fglpa4.quest_ctrl_pyramid_levels pyr,
       fglpa4.quest_sc_sql_syntax_dim   sql
 WHERE stf.instance_key in
       (select instance_key
          from fglpa4.quest_instance_dim
         where upper(instance_name) = upper('invdeal'))
   AND stf.time_key = tim.time_key
   AND tim.pyramid_level = pyr.level_id
   AND tim.start_time >= trunc(SYSDATE - 30, 'DD')
   AND tim.end_time <= trunc(SYSDATE, 'DD')
   AND pyr.resolution_type = 5
   AND stf.syntax_key = sql.syntax_key
   AND sql.oracle_hash_value = 3172821298
 GROUP BY tim.start_time
 ORDER BY tim.start_time
2.查询执行计划

select id,
      lpad(' ', depth * 4) || operation operation,
      options,
      object_owner,
      object_name,
      optimizer,
      cost,
      cardinality,
      bytes,
      cpu_cost,
      io_cost,
      temp_space,
      access_predicates
 from v$sql_plan
 where hash_value = 3290770933;
 
 
 
 set pagesize 999
select  '| Operation                         | PHV/Object Name               |  Rows | Bytes|   Cost |'
as "Optimizer Plan:" from dual
union all
select
    rpad('| '||substr(lpad(' ',1*(depth-1))||operation||
     decode(options, null,'',' '||options), 1, 35), 36, ' ')||'|'||
  rpad(decode(id, 0, '------------- '
    , substr(decode(substr(object_name, 1, 7), 'SYS_LE_', null, object_name)
       ||' ',1, 30)), 31, ' ')||'|'||
   lpad(decode(cardinality,null,'  ',
      decode(sign(cardinality-1000), -1, cardinality||' ',
      decode(sign(cardinality-1000000), -1, trunc(cardinality/1000)||'K',
      decode(sign(cardinality-1000000000), -1, trunc(cardinality/1000000)||'M',
      trunc(cardinality/1000000000)||'G')))), 7, ' ') || '|' ||
  lpad(decode(bytes,null,' ',
    decode(sign(bytes-1024), -1, bytes||' ',
    decode(sign(bytes-1048576), -1, trunc(bytes/1024)||'K',
       decode(sign(bytes-1073741824), -1, trunc(bytes/1048576)||'M',
         trunc(bytes/1073741824)||'G')))), 6, ' ') || '|' ||
    lpad(decode(cost,null,' ', decode(sign(cost-10000000), -1, cost||' ',
                decode(sign(cost-1000000000), -1, trunc(cost/1000000)||'M',
                       trunc(cost/1000000000)||'G'))), 8, ' ') || '|' as "Explain plan"
from v$sql_plan sp
where sp.hash_value=&1;

 

3.查看session并kill

SELECT p.spid, a.serial#, c.object_name, b.session_id, b.oracle_username,
       b.os_user_name
  FROM v$process p, v$session a, v$locked_object b, all_objects c
 WHERE p.addr = a.paddr AND a.process = b.process
       AND c.object_id = b.object_id;
       SELECT sid, serial#, username, osuser FROM v$session  where osuser = 'TUSHAN348';
       alter system kill session '216,23111';
       alter system kill session '281,44571';

 

4.查看表空间大小

SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
       　　D.TOT_GROOTTE_MB "表空间大小(M)",
       　　D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
       　　TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) /
                         D.TOT_GROOTTE_MB * 100,
                         　　2),
                   　　 '990.99') "使用比",
       　　F.TOTAL_BYTES "空闲空间(M)",
       　　F.MAX_BYTES "最大块(M)" 　　FROM (SELECT TABLESPACE_NAME,
                                                    　　ROUND(SUM(BYTES) /
                                                              (1024 * 1024),
                                                              2) TOTAL_BYTES,
                                                    　　ROUND(MAX(BYTES) /
                                                              (1024 * 1024),
                                                              2) MAX_BYTES 　　FROM SYS.DBA_FREE_SPACE 　　GROUP BY TABLESPACE_NAME) F,
       　　 (SELECT DD.TABLESPACE_NAME,
                    　　ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB 　　FROM SYS.DBA_DATA_FILES DD 　　GROUP BY DD.TABLESPACE_NAME) D 　　WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME 　　ORDER BY 4 DESC