Oracle数据库-重点信息查询方法

一、数据库信息及查询方法

1.1是否为RAC

结果为true 则为RAC
col RAC for a20
col value for a30

select name RAC,value from v$parameter where name='cluster_database';

1.2 数据库存储容量大小

数据库分配的空间大小

select sum(bytes)/1024/1024/1024 datafile_size_GB from dba_data_files;

1.3 在线会话数

各实例的在线会话数

with tmp_sess
as
(select SNAP_ID,INSTANCE_NUMBER,BEGINTIME,ENDTIME,METRIC_NAME,MAXVALfrom (select SNAP_ID,INSTANCE_NUMBER,TO_CHAR(BEGIN_TIME,'YYYY-MM-DD HH24:MI') BEGINTIME,
TO CHAR(END_TIME,'YYYY-MM-DD HH24:MI') ENDTIME,
METRIC_NAME,METRIC_UNIT,MAXVAL
from dba_hist_SYSMETRIC_SUMMARY
where METRIC_NAME in ('Session Count')
order by MAXVAL desc)
where rownum<=1)
select SNAP_ID,INSTANCE_NUMBER,BEGINTIME,ENDTIME,METRIC_NAME,MAXVAL from tmp_sess union all
select SNAP_ID,INSTANCE_NUMBER,BEGINTIME,ENDTIME,METRIC_NAME,MAXVAL from(select h.SNAP,h.INSTANCE_NUMBER,TO_CHAR(BEGIN_TIME,'YYYY-MM-DD HH24:MI')BEGINTIME,
from dba_hist_SYSMETRIC_SUMMARY h,tmp_sess t where h.SNAP_ID=t.SNAP_ID and h.instance_number<>t.instance_number
and h.METRIC_NAME in('Session Count')
order by MAXVAL desc
)
where rownum<=1;

在同一个快照采样期间，数据库在线会话数

select SNAP_ID,BEGINTIME,ENDTIME,METRIC_NAME,sum(maxval)
from(
with tmp_sess
as
(select SNAP_ID,INSTACE_NUMBER,BEGINTIME,ENDTIME,METRIC_NAME,MAXVALfrom(select SNAP_ID,INSTANCE_NUMBER,TO_CHAR(BEGIN_TIME,'YYYY-MM-DD HH24:MI')BEGINTIME,
TO_CHAR(END_TIME,'YYYY-MM-DD HH24:MI')ENDTIME,
METRIC_NAME,METRIC_UNIT,MAXVAL
from dba_hist_SYSMETRIC_SUMMARY
where METRIC_NAME in('Session Count')
order by MAXVAL desc)where rownum<=1)
select SNAP_ID,INSTACE_NUMBER,BEGINTIME,ENDTIME,METRIC_NAME,MAXVAL from tmp_sess union all
select SNAP_ID,INSTACE_NUMBER,BEGINTIME,ENDTIME,METRIC_NAME,MAXVAL from(
select h.SNAP_ID,h.INSTACE_NUMBER,TO_CHAR(BEGIN_TIME,'YYYY-MM-DD HH24:MI') BEGINTIME, TO_CHAR(ENDTIME,'YYYY-MM-DD HH24:MI') ENDTIME,h.METRIC_NAME,h.MAXVAL from dba_hist_SYSMETRIC_SUMMARY h,tmp_sess t where h.SNAP_ID=t.SNAP_ID and h.instance_number<>t.instance_number
and h.METRIC_NAME in('Session Count')
order by MAXVAL desc
)
where rownum<=1
)group by SNAP_ID,BEGINTIME,ENDTIME,METRIC_NAME;

1.4 最大分区数

用户表中分区数最多的分区

col owner for a30
col table_name for a30
select * from
（select OWNER,TABLE_NAME,PARTITIONING_TYPE,PARTITIN_COUNT from dba_part_tables where owner not in('SYS','SYSTEM','XS$NULL','OJVMSYS','LBACSYC','OUTLN','SYS$UMF','DBSNMP','APPQOSSYS','DBSFWUSER','GGSYS','ANONYMOUS','EXFSYS','SYSMAN','CTXSYS','DVF','DVSYS','GSMADMIN_INTERNAL','MDSYS','OLAPSYS','XDB','WMSYS','GSMCATUSER','MDDATA','REMOTE_SCHERDULER_AGENT','SQLTXPLAIN','SYSBACKUP','GSMUSER','GSMROOTUSER','SYSRAC','SI_INFORMIN_SCHEMA','AUDSYS','DIP','ORDPLUGINS','ORDDATA','SYSKM','ORACLE_OCM','ORDSYS','SYSDG','APEX_220200','ORDS_METADATA')
order by PARTITION_COUNT desc）where rownum=1;

1.5 最大存储过程行数

col owner for a30
col name for a30
select owner,sum(max_lines) from (select * from(select owner,name,type,max(line) max_lines from dba_source where owner not in ('SYS','SYSTEM','XS$NULL','OJVMSYS','LBACSYC','OUTLN','SYS$UMF','DBSNMP','APPQOSSYS','DBSFWUSER','GGSYS','ANONYMOUS','EXFSYS','SYSMAN','CTXSYS','DVF','DVSYS','GSMADMIN_INTERNAL','MDSYS','OLAPSYS','XDB','WMSYS','GSMCATUSER','MDDATA','REMOTE_SCHERDULER_AGENT','SQLTXPLAIN','SYSBACKUP','GSMUSER','GSMROOTUSER','SYSRAC','SI_INFORMIN_SCHEMA','AUDSYS','DIP','ORDPLUGINS','ORDDATA','SYSKM','ORACLE_OCM','ORDSYS','SYSDG','APEX_220200','ORDS_METADATA')
group by owner,name,type
order by max_lines desc
))group by owner;

或者指定schema

select owner,sum(max_lines)
from (select * from (select owner,name,type,max(line)maxlines
from dba_source where owner in 'CC' group by owner,name,type
order by max_lines desc))group by owner;

1.6 单表最大行数

select * from(select owner,table_name,NUM_ROWS，to_char(LAST_ANALYZED,'yyyy-mm-dd hh24:mi:ss') LAST_ANALYZED
from dba_tables
where LAST_ANALYZED is not null
and owner not in('SYS','SYSTEM','XS$NULL','OJVMSYS','LBACSYC','OUTLN','SYS$UMF','DBSNMP','APPQOSSYS','DBSFWUSER','GGSYS','ANONYMOUS','EXFSYS','SYSMAN','CTXSYS','DVF','DVSYS','GSMADMIN_INTERNAL','MDSYS','OLAPSYS','XDB','WMSYS','GSMCATUSER','MDDATA','REMOTE_SCHERDULER_AGENT','SQLTXPLAIN','SYSBACKUP','GSMUSER','GSMROOTUSER','SYSRAC','SI_INFORMIN_SCHEMA','AUDSYS','DIP','ORDPLUGINS','ORDDATA','SYSKM','ORACLE_OCM','ORDSYS','SYSDG','APEX_220200','ORDS_METADATA')
order by NUM_ROWS desc
)where rownum<=1;

1.7 最大单表大小

col SEGEMENT_NAME for a35
select * from(
select owner,segement_name,sum(bytes)/1024/1024/1024 size_gb
from dba_segements
where segement_type='TABLE'
and owner not in ('SYS','SYSTEM','XS$NULL','OJVMSYS','LBACSYC','OUTLN','SYS$UMF','DBSNMP','APPQOSSYS','DBSFWUSER','GGSYS','ANONYMOUS','EXFSYS','SYSMAN','CTXSYS','DVF','DVSYS','GSMADMIN_INTERNAL','MDSYS','OLAPSYS','XDB','WMSYS','GSMCATUSER','MDDATA','REMOTE_SCHERDULER_AGENT','SQLTXPLAIN','SYSBACKUP','GSMUSER','GSMROOTUSER','SYSRAC','SI_INFORMIN_SCHEMA','AUDSYS','DIP','ORDPLUGINS','ORDDATA','SYSKM','ORACLE_OCM','ORDSYS','SYSDG','APEX_220200','ORDS_METADATA')
group by owner,segement_name
order by size_gb desc
)where rownum<=1;

1.8 表总数量

select count(*) num_tables from dba_tables
where owner not in('SYS','SYSTEM','XS$NULL','OJVMSYS','LBACSYC','OUTLN','SYS$UMF','DBSNMP','APPQOSSYS','DBSFWUSER','GGSYS','ANONYMOUS','EXFSYS','SYSMAN','CTXSYS','DVF','DVSYS','GSMADMIN_INTERNAL','MDSYS','OLAPSYS','XDB','WMSYS','GSMCATUSER','MDDATA','REMOTE_SCHERDULER_AGENT','SQLTXPLAIN','SYSBACKUP','GSMUSER','GSMROOTUSER','SYSRAC','SI_INFORMIN_SCHEMA','AUDSYS','DIP','ORDPLUGINS','ORDDATA','SYSKM','ORACLE_OCM','ORDSYS','SYSDG','APEX_220200','ORDS_METADATA');

1.9 无主键表的数量

select count(*) table_count from(
select owner,table_name from dba_tables t
where owner not in('SYS','SYSTEM','XS$NULL','OJVMSYS','LBACSYC','OUTLN','SYS$UMF','DBSNMP','APPQOSSYS','DBSFWUSER','GGSYS','ANONYMOUS','EXFSYS','SYSMAN','CTXSYS','DVF','DVSYS','GSMADMIN_INTERNAL','MDSYS','OLAPSYS','XDB','WMSYS','GSMCATUSER','MDDATA','REMOTE_SCHERDULER_AGENT','SQLTXPLAIN','SYSBACKUP','GSMUSER','GSMROOTUSER','SYSRAC','SI_INFORMIN_SCHEMA','AUDSYS','DIP','ORDPLUGINS','ORDDATA','SYSKM','ORACLE_OCM','ORDSYS','SYSDG','APEX_220200','ORDS_METADATA')
and NOT EXISTS
(select table_name from dba_constraints c
where constraint_type='P'
and owner not in ('SYS','SYSTEM','XS$NULL','OJVMSYS','LBACSYC','OUTLN','SYS$UMF','DBSNMP','APPQOSSYS','DBSFWUSER','GGSYS','ANONYMOUS','EXFSYS','SYSMAN','CTXSYS','DVF','DVSYS','GSMADMIN_INTERNAL','MDSYS','OLAPSYS','XDB','WMSYS','GSMCATUSER','MDDATA','REMOTE_SCHERDULER_AGENT','SQLTXPLAIN','SYSBACKUP','GSMUSER','GSMROOTUSER','SYSRAC','SI_INFORMIN_SCHEMA','AUDSYS','DIP','ORDPLUGINS','ORDDATA','SYSKM','ORACLE_OCM','ORDSYS','SYSDG','APEX_220200','ORDS_METADATA')
and t.table_name=c.table_name and t.owner=c.owner) );

1.10 字段数超过200的宽表

select owner,table_name,count(COLUMN_NAME) column_count
frm dba_tab_columns
where owner not in ('SYS','SYSTEM','XS$NULL','OJVMSYS','LBACSYC','OUTLN','SYS$UMF','DBSNMP','APPQOSSYS','DBSFWUSER','GGSYS','ANONYMOUS','EXFSYS','SYSMAN','CTXSYS','DVF','DVSYS','GSMADMIN_INTERNAL','MDSYS','OLAPSYS','XDB','WMSYS','GSMCATUSER','MDDATA','REMOTE_SCHERDULER_AGENT','SQLTXPLAIN','SYSBACKUP','GSMUSER','GSMROOTUSER','SYSRAC','SI_INFORMIN_SCHEMA','AUDSYS','DIP','ORDPLUGINS','ORDDATA','SYSKM','ORACLE_OCM','ORDSYS','SYSDG','APEX_220200','ORDS_METADATA')
and TABLE_NAME NOT LIKE 'BIN$%'
group by owner,table_name
having count(COLUMN_NAME)>200;

1.11 关注CPU耗时高的SQL

总体思路
1、一个快照周期内平均CPU耗时高，一般说明此类SQL单次消耗CPU多。比如一个小时内平均CPU耗时超过0.5秒。
2、一个快照周期内总CPU耗时高，平均CPU耗时不高，可能存在执行次数多，总CPU消耗大。按照CPU耗时排名SQL