当前位置： 首页 > news >正文

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

news 2025/6/30 5:31:52

文章目录

  • 一、数据库信息及查询方法
    • 1.1是否为RAC
    • 1.2 数据库存储容量大小
    • 1.3 在线会话数
    • 1.4 最大分区数
    • 1.5 最大存储过程行数
    • 1.6 单表最大行数
    • 1.7 最大单表大小
    • 1.8 表总数量
    • 1.9 无主键表的数量
    • 1.10 字段数超过200的宽表
    • 1.11 关注CPU耗时高的SQL

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

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

http://www.lryc.cn/news/370244.html

相关文章：

  • 【全开源】多平台租房系统源码（Fastadmin+ThinkPHP+Uniapp）
  • Pythond 的 corr函数
  • Fiddler 中文版 （强大的网络响应HTPP协议抓包工具）
  • 初出茅庐的小李博客之JSON格式介绍
  • Vue3相关语法内容，组件传值,事件监听，具名插槽。
  • Linux用户，用户组，所有者权限分配，sftp用户权限分配
  • iFlyCode：AI智能编程助手引领未来软件开发新趋势
  • 高低温测试发现文件被篡改
  • 高考真的不再重要了吗？
  • spring常用注解（八）@Async
  • B站画质补完计划（3）：智能修复让宝藏视频重焕新生
  • Spring Cloud Stream整合RocketMQ
  • Web前端浪漫源码：编织梦想与爱的交织乐章
  • 【云岚到家】-day02-4-我的账户-实名认证
  • MySQL复习题（期末考试）
  • 利用DVWA演示文件上传漏洞获取网站shell权限（二）
  • Java---BigInteger和BigDecimal和枚举
  • mybatis数据批量更新
  • 自动驾驶#芯片-1
  • 【保姆级讲解下QT6.3】
  • windows安装conda
  • ubuntu设置GPU功率
  • [发布]嵌入式系统远程测控软件-基于Qt
  • 【数据结构】查找（顺序查找、二分查找、索引顺序查找、二叉排序树、平衡排序树、B树、B+树、哈希表）
  • 远程连接路由器：方法大全与优缺点解析
  • NI USB-6009 DAQ采集卡拆解
  • 详细分析Mysql临时变量的基本知识（附Demo）
  • JS的五种事件函数，各自应用场景又分别是什么
  • 电脑想加个WIFI功能，怎么选！
  • 机器学习——决策树