合 Oracle DBA日常维护的SQL脚本(常用SQL)

发布日期 2022年10月27日 · 已更新 2022年12月3日

0 858 3

👉 本文共约6908个字，系统预计阅读时间或需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

--- 查看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

--- 查看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

根据文件号和块号查询数据库对象

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';

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';

元数据获取(表空间、用户、权限)

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;

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;

查询表的历史统计信息

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;

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;

查询索引的历史统计信息

 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;

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;

表上列的使用情况

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#
;

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#

;

查询字符集

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;

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

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;

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部分

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
;

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,

to_number(null),

(sqt.elap / sqt.exec / 1000000)),

2) "Elapsed Time per Exec (s)",

round(decode(sqt.exec,

to_number(null),

(sqt.cput / sqt.exec / 1000000)),

2) "CPU per Exec (s)",

round(decode(sqt.exec,

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信息


---------  信息
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;

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主机信息


 ----主机信息
 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;

----主机信息

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

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

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

查询索引碎片的比例

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;

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高的表

集群因子越接近块数越好，接近行数则说明索引列的列值相等的行分布极度散列，可能不走索引扫描而走全表扫描

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

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

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

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语句

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;

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语句

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;

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来查，如下

select a.sql_id,a.machine,a.* from V$ACTIVE_SESSION_HISTORY a where a.session_id=5057 and a.SESSION_SERIAL#=12807

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是多少

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'

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即可

[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

[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

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'

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对应的实例上去执行

或如下也可以

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;

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

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';

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对象

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

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

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

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


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

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

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

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

查询一个会话session、process平均消耗多少内存，查看下面avg_used_M值


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;

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 执行次数排序


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;

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 物理读排序

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;

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 逻辑读排序


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;

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排序


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;

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）

查询等待事件


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;

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语句

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

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也不变。）

第一种


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

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次以上

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)

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)

查看数据文件可用百分比

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;

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;

查看数据文件可用百分比

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;

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;

查看表空间可用百分比


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";

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";

查看临时表空间使用率

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

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表空间使用情况

select tablespace_name,status,sum(bytes)/1024/1024 M from dba_undo_extents group by tablespace_name,status

1	select tablespace_name,status,sum(bytes)/1024/1024 M from dba_undo_extents group by tablespace_name,status

查看ASM磁盘组使用率


select name,round(total_mb/1024) "总容量",round(free_mb/1024) "空闲空间",round((free_mb/total_mb)*100) "可用空间比例" 
from gv$asm_diskgroup

select name,round(total_mb/1024) "总容量",round(free_mb/1024) "空闲空间",round((free_mb/total_mb)*100) "可用空间比例"

from gv$asm_diskgroup

统计每个用户使用表空间率

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 "用户","表空间名"

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 "用户","表空间名"

查看闪回区\快速恢复区空间使用率


select sum(percent_space_used)||'%' "已使用空间比例" from V$RECOVERY_AREA_USAGE

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中

会话不在的


select * from v$process where addr not in (select paddr from v$session) and pid not in (1,17,18)

1 2	select * from v$process where addr not in (select paddr from v$session) and pid not in (1,17,18)

会话还在的，但是会话标记为killed


select * from v$process where addr in (select paddr from v$session where status='KILLED')

1 2	select * from v$process where addr in (select paddr from v$session where status='KILLED')

再根据上述结果中的SPID通过如下命令可以查看到process的启动时间


ps auxw|head -1;ps auxw|grep SPID

1 2	ps auxw\|head -1;ps auxw\|grep SPID

查看行迁移或行链接的表

select * From dba_tables where nvl(chain_cnt,0)<>0

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.

数据缓冲区命中率

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';

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';

共享池命中率

以下两者应该都可以，看个人怎么理解

select sum(pinhits)/sum(pins)*100 from v$librarycache;

select sum(pinhits-reloads)/sum(pins)*100 from v$librarycache;

select sum(pinhits)/sum(pins)*100 from v$librarycache;

select sum(pinhits-reloads)/sum(pins)*100 from v$librarycache;

查询归档日志切换频率


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;

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需要多少秒


select event,state,seq#,seconds_in_wait,program from v$session where program like '%LGWR%' and state='WAITING'

1 2	select event,state,seq#,seconds_in_wait,program from v$session where program like '%LGWR%' and state='WAITING'

查询没有索引的表


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)

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

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

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)表示访问次数越高，热点快竞争问题就存在

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;

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语句

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));

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的执行计划

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'));

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，谢谢！

后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！

获取验证码：请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。

打赏赞(3)

标签： Oracle DBA脚本

小麦苗

学习或考证，均可联系麦老师，请加微信db_bao或QQ646634621

发表回复取消回复

要发表评论，您必须先登录。

合 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 10g和11g查询表空间大小（数据库大小）

Oracle 12c的DG自动同步密码文件--ASM 新特性：共享密码文件

Oracle收集内部对象和数据字典的统计信息

发表回复 取消回复

网站公告

网站寄语

本站其它工具

搜索本网站

标签云☁

网站日历

网站归档

网站分类

发表回复取消回复