合 Oracle DBA日常维护的SQL脚本(常用SQL)
- 查看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; |