合 Oracle DBA日常维护的SQL脚本(常用SQL)
👉 本文共约6834个字，系统预计阅读时间或需26分钟。
目录
- 查看PSU
- 根据文件号和块号查询数据库对象
- 元数据获取(表空间、用户、权限)
- 查询表的历史统计信息
- 查询索引的历史统计信息
- 表上列的使用情况
- 查询字符集
- 生成AWR
- AWR的SQL部分
- AWR信息
- AWR主机信息
- 查询碎片程度高的表
- 查询索引碎片的比例
- 集群因子clustering_factor高的表
- 根据sid查spid或根据spid查sid
- 根据sid查看具体的sql语句
- 根据spid查询具体的sql语句
- 查看历史session_id的SQL来自哪个IP
- 查询DML死锁会话sid，及引起死锁的堵塞者会话blocking_session
- 查询DDL锁的sql
- 查询锁住的DDL对象
- 查询当前正在执行的sql
- 查询正在执行的SCHEDULER_JOB
- 查询正在执行的dbms_job
- 查询一个会话session、process平均消耗多少内存，查看下面avg_used_M值
- TOP 10 执行次数排序
- TOP 10 物理读排序
- TOP 10 逻辑读排序
- TOP 10 CPU排序
- 查询等待事件
- 查询当前正在消耗temp空间的sql语句
- 查询需要使用绑定变量的sql,10G以后推荐第二种
- 查看数据文件可用百分比
- 查看数据文件可用百分比
- 查看表空间可用百分比
- 查看临时表空间使用率
- 查询undo表空间使用情况
- 查看ASM磁盘组使用率
- 统计每个用户使用表空间率
- 查看闪回区\快速恢复区空间使用率
- 查看僵死进程，分两种
- 查看行迁移或行链接的表
- 数据缓冲区命中率
- 共享池命中率
- 查询归档日志切换频率
- 查询lgwr进程写日志时每执行一次lgwr需要多少秒
- 查询没有索引的表
- 查询7天的db time
- 查询产生热块较多的对象
- 导出AWR报告的SQL语句
- 查询某个SQL的执行计划
- 在 Oracle 中生成随机数值
- 检查表中是否含有任何的数据
- 把数值转换成文字
- 在包的源代码中查询字符串
- 把用逗号分隔的数据插入的表中
- 查询表中的最后一个记录
- 在 Oracle 中做行数据乘法
- 获取当前月份的第一天
- 获取当前月份的最后一天
- 获取当前年份的第一天
- 获取当前年份的最后一天
- 获取当前月份的天数
- 获取当前月份剩下的天数
- 获取两个日期之间的天数
- 显示当前年份截止到上个月每个月份开始和结束的日期
- 获取直到目前为止今天过去的秒数（从 00：00 开始算）
- 获取今天剩下的秒数（直到 23：59：59 结束）
- 检查在当前数据库模式下是否存在指定的表
- 检查在当前表中是否存在指定的列
- 显示表结构
- 获取当前模式
- 修改当前模式
- 数据库版本信息
- 数据库默认信息
- 数据库字符设置信息
- 获取 Oracle 版本
- 存储区分大小写的数据，但是索引不区分大小写
- 调整没有添加数据文件的表空间
- 检查表空间的自动扩展开关
- 在表空间添加数据文件
- 增加数据文件的大小
- 查询数据库的实际大小
- 查询数据库中数据占用的大小或者是数据库使用细节
- 查询模式或者用户的大小
- 查询数据库中每个用户最后使用的 SQL 查询
- 查询用户 CPU 的使用率
- 查询数据库长查询进展情况
- 获取当前会话 ID，进程 ID，客户端 ID 等
- 查询特定的模式或者表中执行的最后一个 SQL 语句
- 查询每个执行读取的前十个 SQL
- 在视图中查询并显示实际的 Oracle 连接
- 查询并显示通过打开连接程序打开连接的组
- 查询并显示连接 Oracle 的用户和用户的会话数量
- 获取拥有者的对象数量
- 表空间历史增长量
- 清理killed的会话
- 删除主键及主键索引
- 数据文件自动扩展
- 注册监听
- 用户PROFILE
查看PSU
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | --- 查看psu /nfs/software/db/install/chk/chkora.sh opatch lsinventory -bugs_fixed | grep 'PSU' opatch lsinv [ZFCASSDB1:grid]:/home/grid>opatch lspatches 13343438;Database Patch Set Update : 11.2.0.3.1 (13343438) 13348650;Grid Infrastructure Patch Set Update : 11.2.0.3.1 (13348650) [ZFCASSDB1:grid]: /home/grid col action_time for a30 col action for a10 col namespace for a10 col version for a10 col bundle_series for a10 col comments for a30 SELECT to_char(action_time, 'YYYY-MM-DD HH24:MI:SS') action_time, action, namespace, version, id, bundle_series, comments FROM dba_registry_history D; select action,comments from registry$history; --- grid和oracle分别回滚 $ORACLE_HOME/OPatch/opatch rollback -local -id 13348650 -oh /oracle/app/oracle/product/11.2.0/db |
根据文件号和块号查询数据库对象
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 | SELECT tablespace_name, segment_type, owner, segment_name, partition_name FROM dba_extents WHERE file_id = &file_id AND &block_id BETWEEN block_id AND block_id + blocks - 1 ; SELECT ENAME,SAL,ROWID,DBMS_ROWID.ROWID_CREATE(1,125628,147,4,7),DBMS_ROWID.ROWID_OBJECT(ROWID) OBJECT_ID,DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK#,DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) FILE#,DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) RN FROM SCOTT.EMP WHERE EMPNO=7839; SELECT ENAME, SAL, ROWID, DBMS_ROWID.ROWID_CREATE(1, 125628, 147, 4, 7), DBMS_ROWID.ROWID_OBJECT(ROWID) OBJECT_ID, DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK#, DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) FILE#, DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) RN FROM SCOTT.EMP WHERE EMPNO = 7839; SELECT ROWID, dbms_rowid.rowid_object(ROWID) object_id, dbms_rowid.rowid_relative_fno(ROWID) file_id, dbms_rowid.rowid_block_number(ROWID) block_id, d.* FROM scott.SALGRADE d WHERE dbms_rowid.rowid_block_number(ROWID) = 163 AND dbms_rowid.rowid_relative_fno(ROWID) = 4; SELECT DBMS_ROWID.ROWID_CREATE(1, (SELECT DATA_OBJECT_ID FROM DBA_OBJECTS WHERE OBJECT_ID = ROW_WAIT_OBJ#), ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW#), A.ROW_WAIT_OBJ#, A.ROW_WAIT_FILE#, A.ROW_WAIT_BLOCK#, A.ROW_WAIT_ROW#, (SELECT D.OWNER || '.' || D.OBJECT_NAME FROM DBA_OBJECTS D WHERE OBJECT_ID = ROW_WAIT_OBJ#) OBJECT_NAME FROM V$SESSION A WHERE A.ROW_WAIT_OBJ# <> -1; SELECT * FROM SYS.COM$ A WHERE A.ROWID='AAAACJAABAAAARGAAA'; |
元数据获取(表空间、用户、权限)
1 2 3 4 5 6 7 8 9 10 11 12 | SELECT to_char(DBMS_METADATA.GET_DDL('TABLESPACE', a.tablespace_name)) FROM DBA_TABLESPACES a where a.TABLESPACE_NAME = 'TS_LHR'; SELECT TO_CHAR(DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')) DDL_SQL FROM DUAL; SELECT ((DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'LHRSYS'))) FROM DUAL UNION ALL SELECT ((DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'LHRSYS'))) FROM DUAL UNION ALL SELECT ((DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', 'LHRSYS'))) FROM DUAL; |
查询表的历史统计信息
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | SELECT D.OWNER, D.TABLE_NAME, TO_CHAR(D.STATS_UPDATE_TIME, 'YYYY-MM-DD HH24:MI:SS') STATS_UPDATE_TIME FROM DBA_TAB_STATS_HISTORY D WHERE D.TABLE_NAME IN ('TPCCBOKBAL', 'TPCCBOKBAL_TMP', 'TPCCBOKBALJN') ORDER BY D.owner,D.table_name, D.stats_update_time; SELECT B.OWNER, B.OBJECT_NAME TABLE_NAME, TO_CHAR(D.ANALYZETIME, 'YYYY-MM-DD HH24:MI:SS') LAST_ANALYZETIME, TO_CHAR(D.SAVTIME, 'YYYY-MM-DD HH24:MI:SS') CURR_ANALYZETIME, D.ROWCNT FROM SYS.WRI$_OPTSTAT_TAB_HISTORY D, DBA_OBJECTS B WHERE D.OBJ# = B.OBJECT_ID AND B.OBJECT_NAME IN ('TEST_STAT', 'TPCCBOKBAL_TMP', 'TPCCBOKBALJN', 'PK_TPCCBOKBAL') ORDER BY D.OBJ#, D.SAVTIME; |
查询索引的历史统计信息
1 2 3 4 5 6 7 8 9 10 11 12 13 | SELECT B.OWNER, B.OBJECT_NAME INDEX_NAME, TO_CHAR(D.ANALYZETIME, 'YYYY-MM-DD HH24:MI:SS') LAST_ANALYZETIME, TO_CHAR(D.SAVTIME, 'YYYY-MM-DD HH24:MI:SS') CURR_ANALYZETIME, D.ROWCNT, D.BLEVEL, D.LEAFCNT, D.DISTKEY, D.CLUFAC FROM SYS.WRI$_OPTSTAT_IND_HISTORY D, DBA_OBJECTS B WHERE D.OBJ# = B.OBJECT_ID AND B.OBJECT_NAME IN ('IND_TEST') ORDER BY D.OBJ#, D.SAVTIME; |
表上列的使用情况
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | CREATE OR REPLACE VIEW VW_COLUMN_USAGE_LHR AS SELECT oo.name owner, o.name table_name, c.name column_name, u.equality_preds, u.equijoin_preds, u.nonequijoin_preds, u.range_preds, u.like_preds, u.null_preds, u.timestamp FROM sys.col_usage$ u, sys.obj$ o, sys.user$ oo, sys.col$ c WHERE o.obj# = u.obj# AND oo.user# = o.owner# AND c.obj# = u.obj# AND c.col# = u.intcol# ; |
查询字符集
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | 0001 US7ASCII 0369 AL32UTF8 0354 ZHS16GBK SELECT NLS_CHARSET_NAME(TO_NUMBER('0001', 'XXXX')) US7ASCII, NLS_CHARSET_NAME(TO_NUMBER('0354', 'XXXX')) ZHS16GBK, NLS_CHARSET_NAME(TO_NUMBER('0369', 'XXXX')) AL32UTF8, TO_CHAR(NLS_CHARSET_ID('US7ASCII'), 'XXXX') US7ASCII_ID, TO_CHAR(NLS_CHARSET_ID('ZHS16GBK'), 'XXXX') ZHS16GBK_ID, TO_CHAR(NLS_CHARSET_ID('AL32UTF8'), 'XXXX') AL32UTF8_ID FROM DUAL; select userenv('language') from dual; select SYS_CONTEXT('USERENV', 'LANGUAGE') from dual; select * from v$nls_parameters; |
生成AWR
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | select * from table(dbms_workload_repository.awr_report_html(3424884828,1,1161,1165)); @$ORACLE_HOME/rdbms/admin/awrrpt.sql; select * from DBA_HIST_WR_CONTROL; select * from DBA_HIST_SNAPSHOT; select * from DBA_HIST_ACTIVE_SESS_HISTORY; select * from DBA_HIST_ASH_SNAPSHOT; select * from DBA_HIST_SEG_STAT; select * from DBA_HIST_SQLBIND; select * from DBA_HIST_SQLSTAT; select * from DBA_HIST_SQLTEXT; select * from DBA_HIST_SQL_BIND_METADATA; select * from DBA_HIST_SQL_PLAN; |
AWR的SQL部分
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 | select &begin_snap || '~' || &end_snap snap_id_range, (SELECT round(sum(db_time) / 1000000 / 60, 2) db_time_m FROM (select lead(a.value, 1, null) over(partition by b.startup_time order by b.end_interval_time) - a.value db_time from dba_hist_sys_time_model a, dba_hist_snapshot b where a.snap_id = b.snap_id and a.dbid = b.dbid and a.instance_number = b.instance_number and a.stat_name = 'DB time' and a.snap_id between &begin_snap and &end_snap) where db_time IS NOT NULL) "db_time(m)", round(nvl((sqt.elap / 1000000), to_number(null)), 2) "Elapsed Time (s)", round(nvl((sqt.cput / 1000000), to_number(null)), 2) "CPU Time (s)", round(nvl((sqt.iowait_delta / 1000000), to_number(null)), 2) "User I/O Time (s)", round(nvl((sqt.buffer_gets_delta), to_number(null)), 2) "Buffer Gets", round(nvl((sqt.disk_reads_delta), to_number(null)), 2) "Physical Reads", round(nvl((sqt.rows_processed_delta), to_number(null)), 2) "Rows Processed", round(nvl((sqt.parse_calls_delta), to_number(null)), 2) "Parse Calls", sqt.exec executions, round(decode(sqt.exec, 0, to_number(null), (sqt.elap / sqt.exec / 1000000)), 2) "Elapsed Time per Exec (s)", round(decode(sqt.exec, 0, to_number(null), (sqt.cput / sqt.exec / 1000000)), 2) "CPU per Exec (s)", round(decode(sqt.exec, 0, to_number(null), (sqt.iowait_delta / sqt.exec / 1000000)), 2) "UIO per Exec (s)", round(sqt.cput * 100 / sqt.elap, 2) "%CPU", round(sqt.iowait_delta * 100 / sqt.elap, 2) "%IO", round(sqt.elap * 100 / (SELECT sum(db_time) FROM (select lead(a.value, 1, null) over(partition by b.startup_time order by b.end_interval_time) - a.value db_time from dba_hist_sys_time_model a, dba_hist_snapshot b where a.snap_id = b.snap_id and a.dbid = b.dbid and a.instance_number = b.instance_number and a.stat_name = 'DB time' and a.snap_id between &begin_snap and &end_snap) where db_time IS NOT NULL), 2) "elapsed/dbtime", sqt.sql_id, parsing_schema_name, (decode(sqt.module, null, null, sqt.module)) module, nvl((select dbms_lob.substr(st.sql_text, 2000, 1) from dba_hist_sqltext st WHERE st.sql_id = sqt.sql_id and st.dbid = sqt.dbid), (' ** SQL Text Not Available ** ')) sql_text from (select sql_id, a.dbid, a.parsing_schema_name, max(module || '--' || a.action) module, sum(elapsed_time_delta) elap, sum(cpu_time_delta) cput, sum(executions_delta) exec, SUM(a.iowait_delta) iowait_delta, sum(a.buffer_gets_delta) buffer_gets_delta, sum(a.disk_reads_delta) disk_reads_delta, sum(a.rows_processed_delta) rows_processed_delta, sum(a.parse_calls_delta) parse_calls_delta from dba_hist_sqlstat a where &begin_snap < snap_id and snap_id <= &end_snap group by sql_id, parsing_schema_name, a.dbid) sqt order by nvl(sqt.elap, -1) desc, sqt.sql_id ; |
AWR信息
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 | --------- 信息 select s.snap_date, snap_time_range, t.snap_id + 1 snap_id, decode(s.redosize, null, '--shutdown or end--', s.currtime) "TIME", startup_time, to_char(round(s.seconds / 60, 2)) "elapse(min)", round(t.db_time / 1000000 / 60, 2) "DB time(min)", s.redosize redo, round(s.redosize / s.seconds, 2) "redo/s", round(s.redosize / s.transactions, 2) "redo/t", s.logicalreads logical, round(s.logicalreads / s.seconds, 2) "logical/s", round(s.logicalreads / s.transactions, 2) "logical/t", physicalreads physical, round(s.physicalreads / s.seconds, 2) "phy/s", round(s.physicalreads / s.transactions, 2) "phy/t", s.executes execs, round(s.executes / s.seconds, 2) "execs/s", round(s.executes / s.transactions, 2) "execs/t", s.parse, round(s.parse / s.seconds, 2) "parse/s", round(s.parse / s.transactions, 2) "parse/t", s.hardparse, round(s.hardparse / s.seconds, 2) "hardparse/s", round(s.hardparse / s.transactions, 2) "hardparse/t", s.transactions trans, round(s.transactions / s.seconds, 2) "trans/s" from (select curr_redo - last_redo redosize, curr_logicalreads - last_logicalreads logicalreads, curr_physicalreads - last_physicalreads physicalreads, curr_executes - last_executes executes, curr_parse - last_parse parse, curr_hardparse - last_hardparse hardparse, curr_transactions - last_transactions transactions, round(((currtime + 0) - (lasttime + 0)) * 3600 * 24, 0) seconds, to_char(currtime, 'yyyy-mm-dd') snap_date, to_char(currtime, 'hh24:mi') currtime, to_char(lasttime, 'YYYY-MM-DD HH24:MI') || '~' || to_char(currtime, 'YYYY-MM-DD HH24:MI') snap_time_range, currsnap_id endsnap_id, to_char(startup_time, 'yyyy-mm-dd hh24:mi:ss') startup_time from (select a.redo last_redo, a.logicalreads last_logicalreads, a.physicalreads last_physicalreads, a.executes last_executes, a.parse last_parse, a.hardparse last_hardparse, a.transactions last_transactions, lead(a.redo, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_redo, lead(a.logicalreads, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_logicalreads, lead(a.physicalreads, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_physicalreads, lead(a.executes, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_executes, lead(a.parse, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_parse, lead(a.hardparse, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_hardparse, lead(a.transactions, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_transactions, b.end_interval_time lasttime, lead(b.end_interval_time, 1, null) over(partition by b.startup_time order by b.end_interval_time) currtime, lead(b.snap_id, 1, null) over(partition by b.startup_time order by b.end_interval_time) currsnap_id, b.startup_time from (select snap_id, dbid, instance_number, sum(decode(stat_name, 'redo size', value, 0)) redo, sum(decode(stat_name, 'session logical reads', value, 0)) logicalreads, sum(decode(stat_name, 'physical reads', value, 0)) physicalreads, sum(decode(stat_name, 'execute count', value, 0)) executes, sum(decode(stat_name, 'parse count (total)', value, 0)) parse, sum(decode(stat_name, 'parse count (hard)', value, 0)) hardparse, sum(decode(stat_name, 'user rollbacks', value, 'user commits', value, 0)) transactions from dba_hist_sysstat where stat_name in ('redo size', 'session logical reads', 'physical reads', 'execute count', 'user rollbacks', 'user commits', 'parse count (hard)', 'parse count (total)') group by snap_id, dbid, instance_number) a, dba_hist_snapshot b where a.snap_id = b.snap_id and a.dbid = b.dbid and a.instance_number = b.instance_number order by end_interval_time)) s, (select lead(a.value, 1, null) over(partition by b.startup_time order by b.end_interval_time) - a.value db_time, lead(b.snap_id, 1, null) over(partition by b.startup_time order by b.end_interval_time) endsnap_id, b.snap_id from dba_hist_sys_time_model a, dba_hist_snapshot b where a.snap_id = b.snap_id and a.dbid = b.dbid and a.instance_number = b.instance_number and a.stat_name = 'DB time') t where s.endsnap_id = t.endsnap_id order by s.snap_date desc, snap_id desc, time asc; |
AWR主机信息
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 | ----主机信息 SELECT s.snap_id, DB_NAME, s.dbid, INSTANCE_NAME, s.instance_number, s.startup_time, Version Release, PARALLEL RAC, HOST_NAME, di.platform_name, v.cpus CPUS, v.cores, v.sockets, v.Memory "Memory (GB)" FROM DBA_HIST_DATABASE_INSTANCE di, DBA_HIST_SNAPSHOT s, (SELECT snap_id, dbid, instance_number, SUM(CPUs) CPUs, SUM(Cores) Cores, SUM(Sockets) Sockets, SUM(Memory) Memory FROM (SELECT o.snap_id, o.dbid, o.instance_number, decode(o.stat_name, 'NUM_CPUS', o.value) CPUs, decode(o.stat_name, 'NUM_CPU_CORES', o.value) Cores, decode(o.stat_name, 'NUM_CPU_SOCKETS', o.value) Sockets, decode(o.stat_name, 'PHYSICAL_MEMORY_BYTES', trunc(o.value / 1024 / 1024 / 1024, 2)) Memory FROM dba_hist_osstat o WHERE o.stat_name IN ('NUM_CPUS', 'NUM_CPU_CORES', 'NUM_CPU_SOCKETS', 'PHYSICAL_MEMORY_BYTES')) GROUP BY snap_id, dbid, instance_number) v WHERE s.instance_number = di.instance_number AND s.startup_time = di.startup_time AND s.dbid = di.dbid AND s.snap_id = v.snap_id AND s.dbid = s.dbid AND s.instance_number = v.instance_number; |
查询碎片程度高的表
条件为什么block>100，因为一些很小的表，只有几行数据实际大小很小，但是block一次性分配就是5个（11g开始默认一次性分配1M的block大小了，见create table storged的NEXT参数），5个block相对于几行小表数据来说就相差太大了。
算法中/0.9是因为块的pfree一般为10%，所以一个块最多只用了90%，而且一行数据大于8KB时容易产生行链接，把一行分片存储，一样的一个块连90%都用不满、
AVG_ROW_LEN还是比较准的，比如个人实验情况一表6个字段，一个number，其他5个都是char(100)但是实际数据都是’1111111’7位，AVG_ROW_LEN显示依然为513
1 2 3 4 5 6 | SELECT TABLE_NAME,(BLOCKS*8192/1024/1024)"理论大小M", (NUM_ROWS*AVG_ROW_LEN/1024/1024/0.9)"实际大小M", round((NUM_ROWS*AVG_ROW_LEN/1024/1024/0.9)/(BLOCKS*8192/1024/1024),3)*100||'%' "实际使用率%" FROM USER_TABLES where blocks>100 and (NUM_ROWS*AVG_ROW_LEN/1024/1024/0.9)/(BLOCKS*8192/1024/1024)<0.3 order by (NUM_ROWS*AVG_ROW_LEN/1024/1024/0.9)/(BLOCKS*8192/1024/1024) desc |
查询索引碎片的比例
1 2 | select name,del_lf_rows,lf_rows, round(del_lf_rows/decode(lf_rows,0,1,lf_rows)*100,0)||'%' frag_pct from index_stats where round(del_lf_rows/decode(lf_rows,0,1,lf_rows)*100,0)>30; |
集群因子clustering_factor高的表
集群因子越接近块数越好，接近行数则说明索引列的列值相等的行分布极度散列，可能不走索引扫描而走全表扫描
1 2 3 4 5 | select tab.table_name,tab.blocks,tab.num_rows,ind.index_name,ind.clustering_factor, round(nvl(ind.clustering_factor,1)/decode(tab.num_rows,0,1,tab.num_rows),3)*100||'%' "集群因子接近行数" from user_tables tab, user_indexes ind where tab.table_name=ind.table_name and tab.blocks>100 and nvl(ind.clustering_factor,1)/decode(tab.num_rows,0,1,tab.num_rows) between 0.35 and 3 |
根据sid查spid或根据spid查sid
1 2 | select s.sid,s.serial#,s.LOGON_TIME,s.machine,p.spid,p.terminal from v$session s,v$process p where s.paddr=p.addr and s.sid=XX or p.spid=YY |
根据sid查看具体的sql语句
1 2 | select username,sql_text,machine,osuser from v$session a,v$sqltext_with_newlines b where DECODE(a.sql_hash_value, 0, prev_hash_value, sql_hash_value)=b.hash_value and a.sid=&sid order by piece; |
根据spid查询具体的sql语句
1 2 3 4 5 6 | select ss.SID, pr.SPID, ss.action, sa.SQL_FULLTEXT, ss.TERMINAL, ss.PROGRAM, ss.SERIAL#, ss.USERNAME, ss.STATUS, ss.OSUSER, ss.last_call_et from v$process pr, v$session ss, v$sqlarea sa where ss.status='ACTIVE' and ss.username is not null and pr.ADDR = ss.PADDR and ss.SQL_ADDRESS = sa.ADDRESS and ss.SQL_HASH_VALUE = sa.HASH_VALUE and pr.spid = XX; |
查看历史session_id的SQL来自哪个IP
(当然这是个误解，都是历史的了，怎么可能还查到spid，其实查看trace文件名就可以知道spid,trace文件里面有sid和具体sql，如果trace存在incident，那trace就看不到具体sql，但是可以在incident文件中看到具体的sql，如DW_ora_17751.trc中17751就是spid，里面有这样的内容Incident 115 created, dump file: /XX/incident/incdir_115/DW_ora_17751_i115.trc，那么在DW_ora_17751_i115.trc就可以看到具体的sql语句)
DB_ora_29349.trc中出现如下
*** SESSION ID:(5057.12807) 2016-10-26 14:45:52.726
通过表V$ACTIVE_SESSION_HISTORY来查，如下
1 | select a.sql_id,a.machine,a.* from V$ACTIVE_SESSION_HISTORY a where a.session_id=5057 and a.SESSION_SERIAL#=12807 |
查询上面的machine的IP是多少
1 | select s.sid,s.serial#,s.LOGON_TIME,s.machine,p.spid,p.terminal from v$session s,v$process p where s.paddr=p.addr and s.machine='localhost' |
通过上面的spid在oracle服务器上执行netstat -anp |grep spid即可
1 2 3 | [oracle@dwdb trace]$ netstat -anp |grep 17630 tcp 210 0 192.168.64.228:11095 192.168.21.16:1521 ESTABLISHED 17630/oracleDB tcp 0 0 ::ffff:192.168.64.228:1521 ::ffff:192.168.64.220:59848 ESTABLISHED 17630/oracleDB |
出现两个，说明来自220，连接了228数据库服务器，但是又通过228服务器的dblink去连接了16服务器
查询DML死锁会话sid，及引起死锁的堵塞者会话blocking_session
1 2 | select sid, blocking_session, LOGON_TIME,sql_id,status,event,seconds_in_wait,state, BLOCKING_SESSION_STATUS from v$session where event like 'enq%' and state='WAITING' and BLOCKING_SESSION_STATUS='VALID' |
BLOCKING_SESSION:Session identifier of the blocking session. This column is valid only if BLOCKING_SESSION_STATUS has the value VALID.
可以在v$session.LOGON_TIME上看到引起死锁的堵塞者会话比等待者要早
如果遇到RAC环境，一定要用gv$来查，并且执行alter system kill session 'sid,serial#'要到RAC对应的实例上去执行
或如下也可以
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | select (select username from v$session where sid=a.sid) blocker, a.sid, a.id1, a.id2, ' is blocking ' "IS BLOCKING", (select username from v$session where sid=b.sid) blockee, b.sid from v$lock a, v$lock b where a.block = 1 and b.request > 0 and a.id1 = b.id1 and a.id2 = b.id2; |
查询DDL锁的sql
1 2 3 4 5 6 7 8 9 10 | SELECT sid, event, p1raw, seconds_in_wait, wait_time FROM sys.v_$session_wait WHERE event like 'library cache %' p1raw结果为'0000000453992440' SELECT s.sid, kglpnmod "Mode", kglpnreq "Req", s.LOGON_TIME FROM x$kglpn p, v$session s WHERE p.kglpnuse=s.saddr AND kglpnhdl='0000000453992440'; |
结果为671 0 3 2011-11-1 12:00:00
525 2 0 2011-11-4 12:00:00
查询锁住的DDL对象
1 | select d.session_id,s.SERIAL#,d.name from dba_ddl_locks d,v$session s where d.owner='MKLMIGEM' and d.SESSION_ID=s.sid |
查询当前正在执行的sql
1 2 3 | SELECT s.sid,s.serial#,s.username,spid,v$sql.sql_id,machine,s.terminal,s.program,sql_text FROM v$process,v$session s,v$sql WHERE addr=paddr and s.sql_id=v$sql.sql_id AND sql_hash_value=hash_value |
查询正在执行的SCHEDULER_JOB
1 2 3 4 5 | select owner,job_name,sid,b.SERIAL#,b.username,spid from ALL_SCHEDULER_RUNNING_JOBS,v$session b,v$process where session_id=sid and paddr=addr |
查询正在执行的dbms_job
1 2 3 | select job,b.sid,b.SERIAL#,b.username,spid from DBA_JOBS_RUNNING a ,v$session b,v$process where a.sid=b.sid and paddr=addr |
1 2 3 4 5 | select round(sum(pga_used_mem)/1024/1024,0) total_used_M, round(sum(pga_used_mem)/count(1)/1024/1024,0) avg_used_M, round(sum(pga_alloc_mem)/1024/1024,0) total_alloc_M, round(sum(pga_alloc_mem)/count(1)/1024/1024,0) avg_alloc_M from v$process; |
TOP 10 执行次数排序
1 2 3 4 5 6 | select * from (select executions,username,PARSING_USER_ID,sql_id,sql_text from v$sql,dba_users where user_id=PARSING_USER_ID order by executions desc) where rownum <=5; |
TOP 10 物理读排序
1 2 3 4 | select * from (select DISK_READS,username,PARSING_USER_ID,sql_id,ELAPSED_TIME/1000000,sql_text from v$sql,dba_users where user_id=PARSING_USER_ID order by DISK_READS desc) where rownum <=5; |
（不要使用DISK_READS/ EXECUTIONS来排序，因为任何一条语句不管执行几次都会耗逻辑读和cpu，可能不会耗物理读（遇到LRU还会耗物理读，LRU规则是执行最不频繁的且最后一次执行时间距离现在最久远的就会被交互出buffer cache），是因为buffer cache存放的是数据块，去数据块里找行一定会消耗cpu和逻辑读的。Shared pool执行存放sql的解析结果，sql执行的时候只是去share pool中找hash value，如果有匹配的就是软解析。所以物理读逻辑读是在buffer cache中，软解析硬解析是在shared pool）
TOP 10 逻辑读排序
1 2 3 4 5 6 | select * from (select BUFFER_GETS,username,PARSING_USER_ID,sql_id,ELAPSED_TIME/1000000,sql_text from v$sql,dba_users where user_id=PARSING_USER_ID order by BUFFER_GETS desc) where rownum <=5; |
（不要使用BUFFER_GETS/ EXECUTIONS来排序，原因同16）
TOP 10 CPU排序
1 2 3 4 5 | select * from (select CPU_TIME/1000000,username,PARSING_USER_ID,sql_id,ELAPSED_TIME/1000000,sql_text from v$sql,dba_users where user_id=PARSING_USER_ID order by CPU_TIME/1000000 desc) where rownum <=5; |
（不要使用CPU_TIME/ EXECUTIONS来排序，原因同16）
查询等待事件
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | select a.inst_id, a.EVENT, count(*) counts from gv$session a where a.status = 'ACTIVE' group by a.inst_id,a.EVENT having count(*) > 1 order by a.inst_id,counts desc; select a.EVENT, count(*) counts from v$session a where a.status = 'ACTIVE' group by a.EVENT having count(*) > 1 order by counts desc; select event,sum(decode(wait_time,0,0,1)) "之前等待次数", sum(decode(wait_time,0,1,0)) "正在等待次数",count(*) from v$session_wait group by event order by 4 desc; |
查询当前正在消耗temp空间的sql语句
1 2 3 4 5 6 7 8 9 10 | Select distinct se.username, se.sid, su.blocks * to_number(rtrim(p.value))/1024/1024 as space_G, su.tablespace, sql_text from V$TEMPSEG_USAGE su, v$parameter p, v$session se, v$sql s where p.name = 'db_block_size' and su.session_addr=se.saddr and su.sqlhash=s.hash_value and su.sqladdr=s.address |
查询需要使用绑定变量的sql,10G以后推荐第二种
（任何一条执行过的语句不管执行了几次在V$SQL中都只有一条记录，V$SQL中会记录执行了几次。两条一模一样的语句但是在不同的schema下执行的两种结果，如select from t1.test在sye、system下执行则V$SQL只有一条记录(谁先执行则PARSING_SCHEMA_NAME显示谁)。如在sys和system都执行select from test则V$SQL中有两条记录，两条记录的CHILD_NUMBER和PARSING_SCHEMA_NAME不一样。同一个用户下执行一样的语句如果大小写不一样或加了hint的话则会出现多个V$SQL记录，说明V$SQL对应的sql语句必须一模一样，如果alter system flush shared_pool（主站慎用）后再执行一样的语句，发现语句在V$SQL中的SQL_ID和HASH_VALUE与之前的一样，说明SQL_ID和HASH_VALUE应该是oracle自己的一套算法来的，只是根据sql语句内容来进行转换，sql语句不变则SQL_ID和HASH_VALUE也不变。）
第一种
1 2 3 4 5 | select * from ( select count(*),sql_id, substr(sql_text,1,40) from v$sql group by sql_id, substr(sql_text,1,40) having count(*) > 10 order by count(*) desc) where rownum<10 |
第二种
count(1)>10表示类语句运行了10次以上
1 2 3 4 5 6 7 8 9 10 | select sql_id, FORCE_MATCHING_SIGNATURE, sql_text from v$SQL where FORCE_MATCHING_SIGNATURE in (select /*+ unnest */ FORCE_MATCHING_SIGNATURE from v$sql where FORCE_MATCHING_SIGNATURE > 0 and FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATURE group by FORCE_MATCHING_SIGNATURE having count(1) > 10) |
查看数据文件可用百分比
1 2 3 4 5 6 7 8 9 | select b.file_id,b.tablespace_name,b.file_name,b.AUTOEXTENSIBLE, ROUND(b.bytes/1024/1024/1024,2) ||'G' "文件总容量", ROUND((b.bytes-sum(nvl(a.bytes,0)))/1024/1024/1024,2)||'G' "文件已用容量", ROUND(sum(nvl(a.bytes,0))/1024/1024/1024,2)||'G' "文件可用容量", ROUND(sum(nvl(a.bytes,0))/(b.bytes),2)*100||'%' "文件可用百分比" from dba_free_space a,dba_data_files b where a.file_id=b.file_id group by b.tablespace_name,b.file_name,b.file_id,b.bytes,b.AUTOEXTENSIBLE order by b.tablespace_name; |
查看数据文件可用百分比
1 2 3 4 5 6 7 8 | select b.file_id,b.tablespace_name,b.file_name,b.AUTOEXTENSIBLE, ROUND(b.MAXBYTES/1024/1024/1024,2) ||'G' "文件最大可用总容量", ROUND((b.bytes-sum(nvl(a.bytes,0)))/1024/1024/1024,2)||'G' "文件已用容量", ROUND(((b.MAXBYTES/1024/1024/1024)-((b.bytes-sum(nvl(a.bytes,0)))/1024/1024/1024))/(b.MAXBYTES/1024/1024/1024),2)*100||'%' "文件可用百分比" from dba_free_space a,dba_data_files b where a.file_id=b.file_id and b.file_id>4 group by b.tablespace_name,b.file_name,b.file_id,b.bytes,b.AUTOEXTENSIBLE,b.MAXBYTES order by b.tablespace_name; |
查看表空间可用百分比
1 2 3 4 5 6 | select b.tablespace_name,a.total,b.free,round((b.free/a.total)*100) "% Free" from (select tablespace_name, sum(bytes/(1024*1024)) total from dba_data_files group by tablespace_name) a, (select tablespace_name, round(sum(bytes/(1024*1024))) free from dba_free_space group by tablespace_name) b WHERE a.tablespace_name = b.tablespace_name order by "% Free"; |
查看临时表空间使用率
1 2 3 4 5 6 7 8 9 10 | SELECT temp_used.tablespace_name,total,used, total - used as "Free", round(nvl(total-used, 0) * 100/total,3) "Free percent" FROM (SELECT tablespace_name, SUM(bytes_used)/1024/1024 used FROM GV_$TEMP_SPACE_HEADER GROUP BY tablespace_name) temp_used, (SELECT tablespace_name, SUM(bytes)/1024/1024 total FROM dba_temp_files GROUP BY tablespace_name) temp_total WHERE temp_used.tablespace_name = temp_total.tablespace_name |
查询undo表空间使用情况
1 | select tablespace_name,status,sum(bytes)/1024/1024 M from dba_undo_extents group by tablespace_name,status |
查看ASM磁盘组使用率
1 2 3 4 | select name,round(total_mb/1024) "总容量",round(free_mb/1024) "空闲空间",round((free_mb/total_mb)*100) "可用空间比例" from gv$asm_diskgroup |
统计每个用户使用表空间率
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | SELECT c.owner "用户", a.tablespace_name "表空间名", total/1024/1024 "表空间大小M", free/1024/1024 "表空间剩余大小M", ( total - free )/1024/1024 "表空间使用大小M", Round(( total - free ) / total, 4) * 100 "表空间总计使用率 %", c.schemas_use/1024/1024 "用户使用表空间大小M", round((schemas_use)/total,4)*100 "用户使用表空间率 %" FROM (SELECT tablespace_name, Sum(bytes) free FROM DBA_FREE_SPACE GROUP BY tablespace_name) a, (SELECT tablespace_name, Sum(bytes) total FROM DBA_DATA_FILES GROUP BY tablespace_name) b, (Select owner ,Tablespace_Name, Sum(bytes) schemas_use From Dba_Segments Group By owner,Tablespace_Name) c WHERE a.tablespace_name = b.tablespace_name and a.tablespace_name =c.Tablespace_Name order by "用户","表空间名" |
查看闪回区\快速恢复区空间使用率
1 2 | select sum(percent_space_used)||'%' "已使用空间比例" from V$RECOVERY_AREA_USAGE |
查看僵死进程，分两种
alter system kill session一执行则session即标记为KILLED，但是如果会话产生的数据量大则这个kill可能会比较久，在这个过程中session标记为KILLED但是这个会话还在V$session中，则V$session.paddr还在，所以可以匹配到V$process.addr，所以process进程还在；当kill过程执行完毕，则这个会话即不在V$session中
会话不在的
1 2 | select * from v$process where addr not in (select paddr from v$session) and pid not in (1,17,18) |
会话还在的，但是会话标记为killed
1 2 | select * from v$process where addr in (select paddr from v$session where status='KILLED') |
再根据上述结果中的SPID通过如下命令可以查看到process的启动时间
1 2 | ps auxw|head -1;ps auxw|grep SPID |
查看行迁移或行链接的表
1 | select * From dba_tables where nvl(chain_cnt,0)<>0 |
chain_cnt ：Number of rows in the table that are chained from one data block to another or that have migrated to a new block, requiring a link to preserve the old rowid. This column is updated only after you analyze the table.
数据缓冲区命中率
1 2 3 4 5 6 7 8 9 10 | SELECT a.VALUE+b.VALUE logical_reads, c.VALUE phys_reads, round(100*(1-c.value/(a.value+b.value)),2)||'%' hit_ratio FROM v$sysstat a,v$sysstat b,v$sysstat c WHERE a.NAME='db block gets' AND b.NAME='consistent gets' AND c.NAME='physical reads'; 或 SELECT DB_BLOCK_GETS+CONSISTENT_GETS Logical_reads,PHYSICAL_READS phys_reads, round(100*(1-(PHYSICAL_READS/(DB_BLOCK_GETS+CONSISTENT_GETS))),2)||'%' "Hit Ratio" FROM V$BUFFER_POOL_STATISTICS WHERE NAME='DEFAULT'; |
共享池命中率
以下两者应该都可以，看个人怎么理解
1 2 3 | select sum(pinhits)/sum(pins)*100 from v$librarycache; select sum(pinhits-reloads)/sum(pins)*100 from v$librarycache; |
查询归档日志切换频率
1 2 3 4 5 6 7 8 9 | select sequence#,to_char(first_time,'yyyymmdd_hh24:mi:ss') firsttime,round((first_time-lag(first_time) over(order by first_time))*24*60,2) minutes from v$log_history where first_time > sysdate - 3 order by first_time,minutes; 或 select sequence#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss') First_time,First_change#,switch_change# from v$loghist where first_time>sysdate-3 order by 1; |
查询lgwr进程写日志时每执行一次lgwr需要多少秒
在state是waiting的情况下，某个等待编号seq#下，seconds_in_wait达多少秒，就是lgwr进程写一次IO需要多少秒
1 2 | select event,state,seq#,seconds_in_wait,program from v$session where program like '%LGWR%' and state='WAITING' |
查询没有索引的表
1 2 3 4 | Select table_name from user_tables where table_name not in (select table_name from user_indexes) Select table_name from user_tables where table_name not in (select table_name from user_ind_columns) |
查询7天的db time
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | TH sysstat AS (select sn.begin_interval_time begin_interval_time, sn.end_interval_time end_interval_time, ss.stat_name stat_name, ss.value e_value, lag(ss.value, 1) over(order by ss.snap_id) b_value from dba_hist_sysstat ss, dba_hist_snapshot sn where trunc(sn.begin_interval_time) >= sysdate - 7 and ss.snap_id = sn.snap_id and ss.dbid = sn.dbid and ss.instance_number = sn.instance_number and ss.dbid = (select dbid from v$database) and ss.instance_number = (select instance_number from v$instance) and ss.stat_name = 'DB time') select to_char(BEGIN_INTERVAL_TIME, 'mm-dd hh24:mi') || to_char(END_INTERVAL_TIME, ' hh24:mi') date_time, stat_name, round((e_value - nvl(b_value, 0)) / (extract(day from(end_interval_time - begin_interval_time)) * 24 * 60 * 60 + extract(hour from(end_interval_time - begin_interval_time)) * 60 * 60 + extract(minute from(end_interval_time - begin_interval_time)) * 60 + extract(second from(end_interval_time - begin_interval_time))), \0) per_sec from sysstat where (e_value - nvl(b_value, 0)) > 0 and nvl(b_value, 0) > 0 |
查询产生热块较多的对象
x$bh .tch(Touch)表示访问次数越高，热点快竞争问题就存在
1 2 3 4 5 6 7 8 9 10 | SELECT e.owner, e.segment_name, e.segment_type FROM dba_extents e, (SELECT * FROM (SELECT addr,ts#,file#,dbarfil,dbablk,tch FROM x$bh ORDER BY tch DESC) WHERE ROWNUM < 11) b WHERE e.relative_fno = b.dbarfil AND e.block_id <= b.dbablk AND e.block_id + e.blocks > b.dbablk; |
导出AWR报告的SQL语句
1 2 3 4 5 | select * from dba_hist_snapshot select * from table(dbms_workload_repository.awr_report_html(DBID, INSTANCE_NUMBER, startsnapid,endsnapid)) select * from TABLE(DBMS_WORKLOAD_REPOSITORY.awr_diff_report_html(DBID, INSTANCE_NUMBER, startsnapid,endsnapid, DBID, INSTANCE_NUMBER, startsnapid,endsnapid)); |
查询某个SQL的执行计划
1 2 3 | select a.hash_value,a.* from v$sql a where sql_id='0n4qfzbqfsjm3' select * from table(dbms_xplan.display_cursor(v$sql.hash_value,0,'advanced')); |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
