(转)有用的sql

实施dba用到有水平的41条sql语句

转自 http://zz563143188.iteye.com/blog/2241707

大牛dba用到的34条SQL语句

1.检查无效的数据文件

 Select * from v$data_file;

2.执行失败或中断的Jobs

 

select job, to_char(last_date,'yyyy-mm-dd hh24:mi:ss') "Last Date", to_char(this_date,'yyyy-mm-dd hh24:mi:ss')

"This Date", broken,failures, schema_user, what                                 

       from dba_jobs where broken='Y' or failures>0;

  

3.无效对象检查方法

Select OWNER, OBJECT_NAME, OBJECT_TYPE

      from dba_objects                      

       where status = 'INVALID'

       and owner=''              

       ORDER BY 1,2,3;

  

4.表空间空间不够将导致不能扩展的Objects

Select a.tablespace_name, a.owner, decode(a.partition_name, null, a.segment_name,               

              a.segment_name || '.' || a.partition_name) "Segment Name", a.extents, round(next_extent/1024) next_extent_kb,           

              round(b.free / 1024) ts_free_kb,                             

              round(c.morebytes / 1024 / 1024) ts_growth_mb                

       from dba_segments a,                                              

             (Select df.tablespace_name, nvl(max(fs.bytes), 0) free        

              from dba_data_files df,                                  

                       dba_free_space fs                                   

              where df.file_id = fs.file_id (+)                         

              group by df.tablespace_name) b,                              

            (Select tablespace_name, max(maxbytes - bytes) morebytes, sum(decode(AUTOEXTENSIBLE, 'YES', 1, 0)) autoextensible from dba_data_files                                      

             group by tablespace_name) c                                   

       where a.tablespace_name = b.tablespace_name and a.owner='替换为真实的owner'                        

             and a.tablespace_name = c.tablespace_name                   

             and ((c.autoextensible = 0) or ((c.autoextensible > 0)      

             and (a.next_extent > c.morebytes)))                           

             and a.next_extent > b.free                                  

       order by 1;

  

5.被Disabled的约束

Select owner, table_name, constraint_name, CONSTRAINT_TYPE from dba_constraints                                      

       where status = 'DISABLED' and owner='替换为真实的owner'                                        

       ORDER BY 1,2,3;

  

  

  

6.没有索引的外键

SELECT acc.owner,acc.table_name,acc.constraint_name,acc.column_name FROM all_cons_columns acc, all_constraints ac                           

       WHERE ac.constraint_name = acc.constraint_name                                       

             AND ac.constraint_type = 'R'                                      

             and acc.owner='替换为真实的owner'                             

             AND (acc.owner, acc.table_name, acc.column_name, acc.position) IN                                                             

                (SELECT acc.owner, acc.table_name, acc.column_name, acc.position

                FROM all_cons_columns acc, all_constraints ac                  

                WHERE ac.constraint_name = acc.constraint_name                 

                AND ac.constraint_type = 'R'                                   

                 MINUS                                                         

                SELECT table_owner, table_name, column_name, column_position FROM all_ind_columns)                                          

       ORDER BY acc.owner,acc.table_name, acc.constraint_name,acc.column_name;

  

7.被Disabled的触发器

Select owner, nvl(table_name, '<system trigger>') table_name, trigger_name from dba_triggers                                                         

       where status = 'DISABLED' and owner='替换为真实的owner'                                                

       ORDER BY 1,2,3; 

  

8.死锁检测

select SID,TYPE,LMODE,REQUEST,BLOCK from v$lock where type = 'TX';

9.Library Cache Reload Ratio

select SUM(RELOADS)/(SUM(PINS)+SUM(RELOADS))*100 from v$librarycache;

10.Data Dictionary Miss Ratio

Select Round((((sum(GetMisses)) / sum(Gets)) * 100),4) "DC_Miss_Ratio%" From V$rowcache;

 

11.数据缓冲区的命中率

select (1 - (sum(decode(name, 'physical reads', value, 0)) / (sum(decode(name, 'db block gets', value, 0)) + sum(decode(name, 'consistent gets', value, 0))))) * 100 "Hit Ratio" from v$sysstat;

12.磁盘排序

select a.value "Sort(Disk)", b.value "Sort(Memory)",

               round(100*(a.value/decode((a.value+b.value), 0,1, (a.value+b.value))),2) "Disk_Sort_Ratio％" from v$sysstat a, v$sysstat b where a.name = 'sorts (disk)' and b.name = 'sorts (memory)';

  

13. Log Buffer latch Contention

SELECT name "Redo Name", gets, misses, immediate_gets, immediate_misses, Decode(gets,0,0,round(misses/gets*100,3)) "Miss_Ratio％", Decode(immediate_gets+immediate_misses,0,0, round( immediate_misses/(immediate_gets+immediate_misses)*100,3)) "Immediate Misses Ratio%" FROM v$latch WHERE name IN ('redo allocation', 'redo copy');

14. 含有50个以上的Extent且30%以上碎片的表空间

 

通过以下SQL语句查询：

查看所有表空间的碎片程度（值在30以下表示碎片很多）--

 select tablespace_name,sum(bytes),sum(free),sum(free)*100/sum(bytes) from (select

  b.file_id file_ID,

  b.tablespace_name tablespace_name,

  b.bytes Bytes,

  (b.bytes-sum(nvl(a.bytes,0))) used,

  sum(nvl(a.bytes,0)) free,

  sum(nvl(a.bytes,0))/(b.bytes)*100 Percent

  from dba_free_space a,dba_data_files b

  where a.file_id=b.file_id

  group by b.tablespace_name,b.file_id,b.bytes

  order by b.file_id) group by tablespace_name order by sum(free)*100/sum(bytes);

 

15.表空间上的I/O分布

SELECT t.name ts_name, f.name file_name, s.phyrds phy_reads, s.phyblkrd phy_blockreads, s.phywrts phy_writes, s.phyblkwrt phy_blockwrites FROM gv$tablespace t, gv$datafile f, gv$filestat s WHERE t.ts# = f.ts# and f.file# = s.file# ORDER BY s.phyrds desc, s.phywrts desc;

 

16.数据文件上的I/O分布

Select ts.NAME "Table Space", D.NAME "File Name", FS.PHYRDS "Phys Rds", decode(fstot.sum_ph_rds, 0, 0, round(100 * FS.PHYRDS / fstot.sum_ph_rds, 2)) "% Phys Rds", FS.PHYWRTS "Phys Wrts",

          decode(fstot.sum_ph_wrts, 0, 0, round(100 * FS.PHYWRTS / fstot.sum_ph_wrts, 2)) "% Phys Wrts" FROM V$FILESTAT FS, V$DATAFILE d, V$tablespace ts, (select sum(phyrds) sum_ph_rds, sum(phywrts) sum_ph_wrts, sum(phyblkrd) sum_bl_rds, sum(phyblkwrt) sum_bl_wrts from V$filestat) fstot WHERE D.FILE# = FS.FILE# AND D.TS# = TS.TS#;

17.已经分配超过100 Extents的Segments

通过以下SQL语句查询：

Select segment_type, owner, segment_name, extents, partition_name                     

       from dba_segments                                                                       

       where segment_type not in ('ROLLBACK', 'TEMPORARY', 'CACHE', 'TYPE2 UNDO')                                                                    

       and owner='替换为真实的owner'                

       and extents > 100;

  

18.因表空间空间不够将导致不能扩展的Objects

Select a.tablespace_name, a.owner,                                                                                                          

              decode(a.partition_name, null, a.segment_name,               

              a.segment_name || '.' || a.partition_name) "Segment Name", a.extents, round(next_extent/1024) next_extent_kb,           

              round(b.free / 1024) ts_free_kb,                             

              round(c.morebytes / 1024 / 1024) ts_growth_mb                

       from dba_segments a,                                              

             (Select df.tablespace_name, nvl(max(fs.bytes), 0) free from dba_data_files df,                                  

                       dba_free_space fs                                   

              where df.file_id = fs.file_id (+)                         

              group by df.tablespace_name) b,                              

            (Select tablespace_name, max(maxbytes - bytes) morebytes, sum(decode(AUTOEXTENSIBLE, 'YES', 1, 0)) autoextensible from dba_data_files                                      

             group by tablespace_name) c                                   

       where a.tablespace_name = b.tablespace_name and a.owner='替换为真实的owner'                        

             and a.tablespace_name = c.tablespace_name                   

             and ((c.autoextensible = 0) or ((c.autoextensible > 0) and (a.next_extent > c.morebytes)))                           

             and a.next_extent > b.free                                  

       order by 1; 

19.查看存储过程是否失效

Select * from user_objects;

8.27.2. 优化建议

 

20.SQL ordered by Elapsed Time

SELECT *                                 

        FROM (SELECT parsing_user_id executions, sorts,                    

                     command_type,             

                     disk_reads,               

                     sql_text                  

              FROM v$sqlarea                 

              ORDER BY disk_reads DESC)       

        WHERE rownum < 10;

21.表空间可用性检查

select tablespace_name,status from dba_tablespaces;

 

 

22.检查alert_SID.Log,并找出最近的10份trace文件

 

Select se.username,

         se.sid,

         su.extents,

         su.blocks * to_number(rtrim(p.value)) as Space,

         tablespace,

         segtype,

         sql_text

    from v$sort_usage su, v$parameter p, v$session se, v$sql s

   where p.name = 'db_block_size'

     and su.session_addr = se.saddr

     and s.hash_value = su.sqlhash

     and s.address = su.sqladdr

   order by se.username, se.sid

  

23.undo表空间管理模式

(1) 检查方法（或操作命令）

SELECT A.NAME,'实例' || A.INST_ID || ': ' || A.DISPLAY_VALUE VALUE FROM GV$PARAMETER A WHERE A.NAME = 'undo_management'

24.undo表空间大小

(1) 检查方法（或操作命令）

SELECT 'UNDO表空间大小', TO_CHAR(WM_CONCAT(A.TABLESPACE_NAME || '表空间大小为' || SUM(BYTES) / 1024 / 1024 || 'M')) VALUE FROM DBA_TABLESPACES A, DBA_DATA_FILES B WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME AND A.TABLESPACE_NAME LIKE 'UNDO%' GROUP BY A.TABLESPACE_NAME

25.临时表空间使用情况

(1) 检查方法（或操作命令）

SELECT TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024 || 'M' VALUE FROM DBA_TEMP_FILES GROUP BY TABLESPACE_NAME

26.数据库参数db_block_size

(1) 检查方法（或操作命令）

SELECT A.NAME,'实例' || A.INST_ID || ': ' || A.DISPLAY_VALUE VALUE FROM GV$PARAMETER A WHERE A.NAME = 'db_block_size'

27.数据库文件的表空间使用情况

SELECT /*+ NO_MERGE(D) NO_MERGE(A) NO_MERGE(F) NO_MERGE(U) NO_MERGE(O) */ D.TABLESPACE_NAME "表空间名", D.BLOCK_SIZE/1024 "块大小(KB)",D.INITIAL_EXTENT/1024 "初始分配大小(KB)",

       ROUND(NVL(A.BYTES /1024 /1024,0) ,2) "大小(MB)",

       ROUND(DECODE(D.CONTENTS, 'UNDO', NVL(U.BYTES, 0) / 1024 / 1024,NVL(A.BYTES - NVL(F.BYTES, 0), 0) / 1024 / 1024) ,2) "占用量(MB)",

       TO_CHAR(ROUND(DECODE(D.CONTENTS, 'UNDO', NVL(U.BYTES / A.BYTES * 100, 0),NVL((A.BYTES - NVL(F.BYTES, 0)) / A.BYTES * 100, 0)) ,2),'999.99')||'%' "占用率(MB)",

       ROUND(DECODE(D.CONTENTS, 'UNDO', NVL(A.BYTES - NVL(U.BYTES, 0), 0) / 1024 / 1024,NVL(F.BYTES, 0) / 1024 / 1024) ,2) "空闲空间(MB)",

       D.STATUS "状态",A.AUTOEXTENSIBLE "是否自动扩展",D.LOGGING "是否记录日志", A.COUNT "数据文件", D.CONTENTS "类型", D.EXTENT_MANAGEMENT "区管理", D.SEGMENT_SPACE_MANAGEMENT "段管理" FROM SYS.DBA_TABLESPACES D, (SELECT TABLESPACE_NAME, SUM(BYTES) BYTES, COUNT(FILE_ID) COUNT, CASE WHEN SUM(DECODE(AUTOEXTENSIBLE,'YES',10000,'NO',1,AUTOEXTENSIBLE))>=10000 THEN 'YES' ELSE 'NO' END||CASE WHEN MOD(SUM(DECODE(AUTOEXTENSIBLE,'YES',10000,'NO',1,AUTOEXTENSIBLE)),10000)>0 THEN CHR(13)||CHR(38)||CHR(13)||'NO' ELSE '' END AS AUTOEXTENSIBLE FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) A, (SELECT TABLESPACE_NAME, SUM(BYTES) BYTES FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F,

       (SELECT TABLESPACE_NAME, SUM(BYTES) BYTES

         FROM DBA_UNDO_EXTENTS

         WHERE STATUS IN ('UNEXPIRED', 'EXPIRED')

         GROUP BY TABLESPACE_NAME) U

  WHERE D.TABLESPACE_NAME = A.TABLESPACE_NAME(+) AND D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)

    AND D.TABLESPACE_NAME = U.TABLESPACE_NAME(+) AND NOT (D.EXTENT_MANAGEMENT = 'LOCAL' AND D.CONTENTS = 'TEMPORARY')

    --AND D.TABLESPACE_NAME LIKE '%COMP%'

UNION ALL

SELECT /*+ NO_MERGE(D) NO_MERGE(A) NO_MERGE(T) */ D.TABLESPACE_NAME "表空间名", D.BLOCK_SIZE/1024 "块大小(KB)",D.INITIAL_EXTENT/1024 "初始分配大小(KB)",

       ROUND(NVL(A.BYTES /1024 /1024,0) ,2) "大小(MB)",

       ROUND(NVL(T.BYTES, 0) / 1024 / 1024 ,2) "占用量(MB)", TO_CHAR(ROUND(NVL(T.BYTES / A.BYTES * 100, 0) ,2),'999.99')||'%' "占用率(MB)", ROUND((NVL(A.BYTES, 0) / 1024 / 1024 - NVL(T.BYTES, 0) / 1024 / 1024) ,2) "空闲空间(MB)", D.STATUS "状态",A.AUTOEXTENSIBLE "是否自动扩展",D.LOGGING "是否记录日志", A.COUNT "数据文件", D.CONTENTS "类型", D.EXTENT_MANAGEMENT "区管理", D.SEGMENT_SPACE_MANAGEMENT "段管理" FROM SYS.DBA_TABLESPACES D,

       (SELECT TABLESPACE_NAME, SUM(BYTES) BYTES, COUNT(FILE_ID) COUNT, CASE WHEN SUM(DECODE(AUTOEXTENSIBLE,'YES',10000,'NO',1,AUTOEXTENSIBLE))>=10000 THEN 'YES' ELSE 'NO' END||CASE WHEN MOD(SUM(DECODE(AUTOEXTENSIBLE,'YES',10000,'NO',1,AUTOEXTENSIBLE)),10000)>0 THEN CHR(13)||CHR(38)||CHR(13)||'NO' ELSE '' END AS AUTOEXTENSIBLE FROM DBA_TEMP_FILES GROUP BY TABLESPACE_NAME) A, (SELECT SS.TABLESPACE_NAME, SUM((SS.USED_BLOCKS * TS.BLOCKSIZE)) BYTES

         FROM GV$SORT_SEGMENT SS, SYS.TS$ TS

         WHERE SS.TABLESPACE_NAME = TS.NAME

         GROUP BY SS.TABLESPACE_NAME) T

  WHERE D.TABLESPACE_NAME = A.TABLESPACE_NAME(+) AND D.TABLESPACE_NAME = T.TABLESPACE_NAME(+)

    AND D.EXTENT_MANAGEMENT = 'LOCAL'

    AND D.CONTENTS = 'TEMPORARY'

  --AND D.TABLESPACE_NAME LIKE :2

ORDER BY 1 Asc,6 DESC;

28.检查数据库的JOB

select * from dba_jobs

29.用户默认表空间

select username,account_status,lock_date,expiry_date,default_tablespace,temporary_tablespace from dba_users where default_tablespace in ('USERS','SYSTEM','SYSAUX');

30.检查无效的存储过程

(1) 检查方法（或操作命令）

select * from dba_objects where object_type in ('PROCEDURE','PACKAGE','PACKAGE BODY') AND STATUS<>'VALID';

31.检查无效的索引

SELECT INDEX_NAME, TABLE_NAME, TABLESPACE_NAME, STATUS

  FROM DBA_INDEXES

WHERE OWNER LIKE 'FMIS%' AND STATUS <> 'VALID';

32.检查无效的触发器

SELECT owner, trigger_name, table_name, status

FROM dba_triggers

WHERE status = 'DISABLED';

33.检查分区表

SELECT "用户","名称","类型",SUM("大小（M）") FROM (

SELECT OWNER "用户",SEGMENT_NAME "名称",SEGMENT_TYPE "类型",BYTES/1024/1024 "大小（M）" FROM DBA_SEGMENTS WHERE BYTES>=1024*1024*200 AND SEGMENT_TYPE IN ('INDEX','TABLE')

UNION ALL

SELECT A.OWNER "用户",B.TABLE_NAME "名称",A.SEGMENT_TYPE "类型",A.BYTES/1024/1024 "大小（M）" FROM DBA_SEGMENTS A,DBA_LOBS B WHERE A.SEGMENT_NAME=B.SEGMENT_NAME AND A.BYTES>=1024*1024*200 AND A.SEGMENT_TYPE='LOBSEGMENT'

) GROUP BY "用户","名称","类型"

ORDER BY 4 DESC

34.表空间大小使用情况

 

(1) 检查方法（或操作命令）

SELECT B.TABLESPACE_NAME 表空间名称, ROUND((B.BYTES/1024)/1024,2) 总空间大小, NVL2(A.BYTES,ROUND((B.BYTES-NVL(A.BYTES,0))/1024/1024,2),B.BYTES) 已使用大小MB, NVL2(A.BYTES,ROUND(NVL(A.BYTES,0)/1024/1024,2),0) 未使用大小MB, NVL2(A.BYTES,TO_CHAR(ROUND(((B.BYTES-NVL(A.BYTES,0))/B.BYTES)*100,2),'990.0'),'100')||'%' 已使用率

  FROM (SELECT TABLESPACE_NAME,SUM(BYTES) BYTES FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME)A,

  (SELECT TABLESPACE_NAME,SUM(BYTES) BYTES FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) B

  WHERE B.TABLESPACE_NAME=A.TABLESPACE_NAME(+);

 

35.查找时间戳不匹配的对象

若有数据返回，应立即反馈，防止出现ORA-04068

SELECT du.name duname,

       do.name dname,

       pu.name puname,

       po.name pname,

       p_timestamp,

       po.stime p_stime

  FROM sys.obj$        do,

       sys.dependency$ d,

       sys.obj$        po,

       sys.user$       du,

       sys.user$       pu

 WHERE p_obj# = po.obj#(+)

   AND d_obj# = do.obj#

   AND do.owner# = du.user#

   AND po.owner# = pu.user#

   AND do.status = 1 /*dependent is valid*/

   AND po.status = 1 /*parent is valid*/

   AND po.stime != p_timestamp /*parent timestamp does not match*/

   AND do.type# not in (13, 28, 29, 30) /*dependent type is not a type or java*/

   AND po.type# not in (13, 28, 29, 30) /*parent type is not a type or java*/

ORDER BY 4, 2

36.归档日志检查

检查1天内每个实例每小时产生归档，若某个时段产生归档较大，需分析出现该情况是否正常。（比如电费转换时会生成较多的redo）

SELECT TO_CHAR(COMPLETION_TIME,'YYYY-MM-DD HH24') "COMPLETION TIME",THREAD#,

       ROUND(SUM(BLOCKS * BLOCK_SIZE) / 1024 / 1024) AS "SIZE(M)"

  FROM V$ARCHIVED_LOG WHERE (SYSDATE-COMPLETION_TIME)<1

 GROUP BY TO_CHAR(COMPLETION_TIME,'YYYY-MM-DD HH24'),THREAD#

 ORDER by 1 DESC,2

 

 

36.如何生成SQL报告

AWR分析

生成当天每个实例9:00-18:00的AWR报告，以每小时为快照间隔，进行分析。同时应将AWR中耗时超过1秒的SQL进行分析。每月1日AWR报告应保存1年时间。

--执行awrsqrpt.sql脚本，按要求输入begin_snap，end_snap，sql_id即可。

SQL> @?/rdbms/admin/awrsqrpt.sql

37.出现oracle进程耗费CPU资源过高情况，可参考下面命令找到相关SQL

--topas出来的最耗cpu资源的pid，根据pid找到最耗资源的sql：

select /*+ ordered */

 sql_text

  from v$sqltext a

 where (a.hash_value, a.address) in

       (select decode(sql_hash_value, 0, prev_hash_value, sql_hash_value),

               decode(sql_hash_value, 0, prev_sql_addr, sql_address)

          from v$session b

         where b.paddr =

               (select addr from v$process c where c.spid = '&pid'))

 order by piece asc

 

38.如何使用SQL Tuning Advisor分析SQL

以下所有涉及sql_id=0r42sp7k3p54n需根据实际替换

--创建优化任务

declare

  l_task_id varchar2(20);

begin

  l_task_id := dbms_sqltune.create_tuning_task(sql_id=>'0r42sp7k3p54n',

                                               scope      => 'COMPREHENSIVE',

                                               time_limit => 120,

                                               task_name  => 'STA_0r42sp7k3p54n'

                                               );

  dbms_sqltune.execute_tuning_task('STA_0r42sp7k3p54n');

end;

39.临时表数量检查

检查临时表数量，防止因临时表异常增加导致表空间过度使用，浪费空间，同时，过多的临时表会导致数据字典膨胀，影响性能。若临时表数据过多（超过10万），需检查后台JOB是否出现问题。

SELECT COUNT(*) FROM DBA_OBJECTS

  WHERE OBJECT_TYPE='TABLE' AND OWNER LIKE 'FMIS%'

        AND (SUBSTR(OBJECT_NAME,1,4)='TEMP' OR SUBSTR(OBJECT_NAME,1,3)='TMP'

        OR (SUBSTR(OBJECT_NAME,1,5)='BBHBG' AND LENGTH(OBJECT_NAME)>5)

        OR (SUBSTR(OBJECT_NAME,1,10)='ZWCX_PZTMP' AND LENGTH(OBJECT_NAME)>10)

        OR (SUBSTR(OBJECT_NAME,1,7)='PZCX_20' AND LENGTH(OBJECT_NAME)>7)

        OR (SUBSTR(OBJECT_NAME,1,9)='LJZTABLE_' AND LENGTH(OBJECT_NAME)>9))

40.Jobs运行检查

检查JOB运行状况，避免出现运行失败情况导致汇总或临时表删除等出现问题。

select * From dba_jobs where broken='Y' or failures>0 order by job;

统计HZ_JOB表中各类别汇总任务数据，若剩余汇总任务过多，需分析汇总是否出现问题。

select job_lb, count(*) from hz_job group by rollup(job_lb);

41.Alert日志检查

每天对alert日志进行检查，及时排除日志中的报错等异常问题。

Alert.log的位置：$ORACLE_BASE/admin/<db_name>/bdump/alert_$ORACLE_SID.log