DBA常用命令之东北大乱炖

一:执行计划篇
二:统计信息篇
三:AWR报告篇
四:格式篇
五:hint篇
六:会话篇
七:日志挖掘LOGMNR篇
八:备份与恢复
九:坏块
十:表空间
十一:日志文件
十二:RAC
十三:12c
十四:需要关闭的特性
十五:归档日志
十六:dbms_metadata.get_ddl
十七:Oracle游标溢出
十八:BBED
十九:大表删除
二十:OEM
二十一:创建测试数据
二十二:EXP-00056
二十三:ORA-01102
二十四:登录触发器
二十五:代码里SQL增加随机数
二十六:PLSQL乱码
二十七:SCN
二十八:数据初始化
二十九:DB2
三十:sqlserver
三十一：mysql
三十二:AIX
三十三:Linux

如何获取执行计划：

参考：How to Obtain a Formatted Explain Plan - Recommended Methods (Doc ID 235530.1)
版本10.2及更高版本,最后执行的SQL：
在10.2及更高版本中，如果已经执行了SQL，则可以从库缓存中提取执行计划（除了早期版本中的标准解释计划选项之外）。
要获取上次执行的SQL问题的计划，请执行以下操作：

set linesize 150
set pagesize 2000
select * from table(dbms_xplan.display_cursor(null,null, 'ALL'));

一个SQL_ID可以有多个具有不同特征的child number。
您可以通过从V$SQL中选择来标识child number，如下所示：

SELECT sql_id, hash_value, child_number, SUBSTR(sql_text,1,40) Text
FROM v$sql
WHERE sql_text LIKE '%&Some_Identifiable_String%'
/set linesize 150

set pagesize 2000
select * from TABLE(dbms_xplan.display_cursor('&SQL_ID'));
或
select * from TABLE(dbms_xplan.display_cursor('&SQL_ID', &CHILD));

select /*+ gather_plan_statistics */ col1, col2 etc.....
set linesize 150
set pagesize 2000
select * from TABLE(dbms_xplan.display_cursor('&SQL_ID', &CHILD,'ALL IOSTATS LAST'))

For SQL ID :
select * from table(dbms_xplan.display_cursor('&sql_id'));
select * from table(dbms_xplan.display_cursor('&sql_id', NULL, 'ALL'));

For SQL ID, Child Cursor :
select * from table(dbms_xplan.display_cursor('&sql_id', &child_number, 'ALL'));

For SQL Text :
select t.*
from v$sql s, table(dbms_xplan.display_cursor(s.sql_id, s.child_number, 'ALL')) t
where s.sql_text like '%&querystring%';

awrgrpt.sql
AWR SQL report是从特定时间段获取执行计划的一种简单方法。
使用以下命令启动报告：
$ORACLE_HOME/rdbms/admin/awrsqrpt.sql
输入sql_id

For SQL ID :
select * from table(dbms_xplan.display_awr('&sql_id')) ;
select * from table(dbms_xplan.display_awr('&sql_id', NULL, NULL, 'ALL')) ;

For SQL ID, Plan Hash Value in the current database :
select * from table(dbms_xplan.display_awr('&sql_id', '&plan_hash_value', NULL, 'ALL')) ;

For SQL ID, Plan Hash Value in a different database ID :
select * from table(dbms_xplan.display_awr('&sql_id', '&plan_hash_value', &dbid, 'ALL')) ;

For SQL ID that was executed during a certain period :
select t.*
from (select distinct sql_id, plan_hash_value, dbid
from dba_hist_sqlstat
where sql_id = '&sql_id'
and snap_id between &begin_snap and &end_snap) s,
table(dbms_xplan.display_awr(s.sql_id, s.plan_hash_value, s.dbid, 'ALL')) t;

For SQL Text :
select t.*
from (select distinct r.sql_id sql_id, r.plan_hash_value plan_hash_value, r.dbid dbid
from dba_hist_sqltext q, dba_hist_sqlstat r
where q.sql_id = r.sql_id
and q.sql_text like '%&querystring%') s,
table(dbms_xplan.display_awr(s.sql_id, s.plan_hash_value, s.dbid, 'ALL')) t;

Note : STS owner is the current user by default.
For SQL ID in a STS :
select * from table (dbms_xplan.display_sqlset( '&sts_name','&sql_id'));

For All Statements in a STS :
select t.*
from dba_sqlset_statements s, table(dbms_xplan.display_sqlset(s.sqlset_name, s.sql_id, s.plan_hash_value, 'ALL', s.sqlset_owner)) t
where s.sqlset_name = '&sts_name';

For SQL ID, Plan Hash Value in a STS :
select * from table (dbms_xplan.display_sqlset( '&sts_name','&sql_id', '&plan_hash_value', 'ALL'));

For SQL ID, Plan Hash Value, STS Owner :
select * from table (dbms_xplan.display_sqlset( '&sts_name','&sql_id', '&plan_hash_value', 'ALL', '&sts_owner'));

For SQL Text in a STS :
select t.*
from dba_sqlset_statements s, table(dbms_xplan.display_sqlset(s.sqlset_name, s.sql_id, s.plan_hash_value, 'ALL', s.sqlset_owner)) t
where s.sqlset_name = '&sts_name'
and s.sql_text like '%&querystring%';

For SQL Handle :
select * from table(dbms_xplan.display_sql_plan_baseline('&sql_handle'));

For SQL Handle, Plan Name :
select * from table(dbms_xplan.display_sql_plan_baseline('&sql_handle', '&plan_name', 'ALL'));

For SQL Text :
select t.*
from (select distinct sql_handle, plan_name
from dba_sql_plan_baselines
where sql_text like '%&querystring%') s,
table(dbms_xplan.display_sql_plan_baseline(s.sql_handle, s.plan_name, 'ALL')) t;

With Oracle 9.2, Oracle supplies a utility called dbms_xplan. It is created by dbmsutil.sql which is called by catproc.sql. As such it should already be installed on most 9.2 databases.

SQL> set lines 130
SQL> set head off
SQL> spool
SQL> alter session set cursor_sharing=EXACT;
SQL> select plan_table_output from table(dbms_xplan.display('PLAN_TABLE',null,'ALL'));
SQL> spool off

Versions 8.1.7 and 9.0.1

From version 8.1.5, Oracle has supplied 2 scripts to extract formatted explain plans from plan_tables. 
One is for serial plans and the other is for parallel plans. 
Scripts can be found under $ORACLE_HOME/rdbms/admin.
Examples of their usage are below.

Serial Plans
To obtain a formatted execution plan for serial plans:

SQL> set lines 130
SQL> set head off
SQL> spool
SQL> @@?/rdbms/admin/utlxpls
SQL> spool off

Autotrace can also be used. 
It shows more information about distributed queries but in earlier versions, the output is often badly formatted. If using autotrace, there is no need to 'explain' the query

SQL> set lines 130
SQL> set long 2000
SQL> set head off
SQL> spool >>spool file <<
SQL> set autotrace trace explain
SQL> @@ >> your query <<
SQL> spool off

If required, the initial steps to create a plan table and make the optimizer populate the plan table with the execution plan are common to all supported versions of Oracle.

1. Use the utlxplan.sql script to create the plan table as instructed below.
SQL> @@?/rdbms/admin/utlxplan
2. On 10g and above there is a new script - catplan.sql - to create the plan table that  
    creates a public plan table as a global temporary table accessible from any schema
SQL> @@?/rdbms/admin/catplan

sql_id:
The SQL_ID specifies the sql_id value for a specific SQL statement, and can be found in:

V$SQL.SQL_ID, 
V$SESSION.SQL_ID, 
V$SESSION.PREV_SQL_ID,
V$SQL.SQL_ID, 
V$SESSION.SQL_ID,  
V$SESSION.PREV_SQL_ID.
If no sql_id is specified, the last executed statement of the current session is shown.

cursor_child_no:
The child cursor number specifies the child number for a specific sql cursor, and can be found in:
V$SQL.CHILD_NUMBER
V$SESSION.SQL_CHILD_NUMBER
V$SESSION.PREV_CHILD_NUMBER.

PL/SQL Developer中通过快捷键F5可以查看目标SQL的执行计划。
实际后台调用的就是explain plan命令，相当于封装了该命令。
explain plan使用方法：
(1) 执行explain plan for + SQL
(2) 执行select * from table(dbms_xplan.display);

SELECT sql_id, child_number FROM v$sql WHERE sql_text LIKE '%XXX%';
(1) select * from table(dbms_xplan.display);
(2) select * from table(dbms_xplan.display_cursor(null, null, 'advanced'));
(3) select * from table(dbms_xplan.display_cursor(null, null, 'all'));
其中：
'advanced'记录的信息要比'all'多，主要就是多一个Outline Data。
Outline Data主要是执行SQL时用于固定执行计划的内部HINT组合，可以将这部分内容摘出来加到目标SQL中以固定其执行计划。
(4) select * from table(dbms_xplan.display_cursor('sql_id/hash_value', child_cursor_number, 'advanced'));
(5) select * from table(dbms_xplan.display_awr('sql_id'));
select plan_table_output from table (dbms_xplan.display_awr('4bctzmucz7fxg',null,null,'ADVANCED +PEEKED_BINDS'));

(2)从awr性能视图里获取
SELECT * FROM TABLE(dbms_xplan.display_awr('SQL_ID'));
优点：
(1)知道sql_id就可以得到执行计划，和explain plan for一样无须执行；
(2)可以得到真实执行计划；
缺点:
(1)没有输出运行时的相关统计信息(例如:逻辑读，递归调用次数，物理读等)
(2)无法判断处理多少上；
(3)无法判断表访问多少次；

How to Determine the SQL_ID for a SQL Statement (Doc ID 1627387.1)
For Example:
SELECT /* TARGET SQL */ * FROM dual;
SELECT sql_id, plan_hash_value, substr(sql_text,1,40) sql_text  
FROM  v$sql 
WHERE sql_text like 'SELECT /* TARGET SQL */%'
SQL_ID        PLAN_HASH_VALUE SQL_TEXT
------------- --------------- ----------------------------------------
0xzhrtn5gkpjs       272002086 SELECT /* TARGET SQL */ * FROM dual

SELECT sql_id, plan_hash_value, SUBSTR(sql_text,1,40) Text
FROM v$sql
WHERE sql_text LIKE '%&An_Identifiable_String%';

SELECT
    s.sql_id,
    s.plan_hash_value,
    t.sql_text,
    s.snap_id
FROM
    dba_hist_sqlstat   s,
    dba_hist_sqltext   t
WHERE s.dbid = t.dbid
AND   s.sql_id = t.sql_id
AND   sql_text LIKE 'SELECT * TARGET SQL */%'
ORDER BY
    s.sql_id;

SQL> conn scott/tiger
SQL> set autotrace on
SP2-0618: Cannot find the Session Identifier.  Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

---SYS@PROD1>@?/sqlplus/admin/plustrce.sql
SQL> @$ORACLE_HOME/sqlplus/admin/plustrce.sql
SQL> grant plustrace to scott;

显示结果集以及执行计划
SQL> set autotrace on 
不显示结果集，显示执行计划
SQL> set autotrace traceonly
优点：
(1)可以输出运行时的相关统计信息(产生多少逻辑读，多少次递归调用，多少次物理读)
(2)traceonly可以控制返回结果不输出；
缺点：
(1)必须等到语句真正执行完毕后，才可以出结果；
(2)无法看到表被访问多少次；

1 此为默认值，即关闭Autotrace
SET AUTOTRACE OFF
2 产生结果集和解释计划并列出统计
SET AUTOTRACE ON
3 显示结果集和解释计划不显示统计
SET AUTOTRACE ON EXPLAIN
4 显示解释计划和统计，尽管执行该语句但您将看不到结果集
SET AUTOTRACE TRACEONLY
5 只显示统计
SET AUTOTRACE TRACEONLY STATISTICS

(1)alter session set statistics_level=all; 
(2)执行SQL; 
(3)select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
示例：
select /*+ gather_plan_statistics */  * from t1 where exists (select 1 from scott.emp a where t1.empno=a.empno);
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
另注：
1. /*+ gather_plan_statistics */可以省略第一步骤；
2. 关键字解读 
(1)starts为该SQL执行的次数；
(2)E-Rows为执行计划预计的行数；
(3)A-Rows为实际返回的行数。A-Rows和E-Rows做比较，就可以确定哪一步执行计划出了问题。
(4)A-Time为每一步实际执行的时间(HH:MM:SS.FF),根据这一行可以知道该SQL耗时在哪个地方；
(5)buffers为每一步实际执行的逻辑读或一致性读；
(6)Reads为物理读；
优点:
(1)可以清晰的从STARTS得出表被访问多少次；
(2)可以清晰的从E-ROWS和A-ROWS中得到预测的行数和真实的行数，从而可以准确判断Oracle评估是否准确；
(3)BUFFER显示真实的逻辑读数量；
缺点:
(1)必须要等到语句真正执行完毕后，才能出结果；
(2)无法控制记录不打屏输出，没有类似autotrace的traceonly的功能；
(3)看不出递归调用的次数，看不出物理读；

设置追踪标识符
alter session set tracefile_identifier='10046';
开启跟踪
alter session set events '10046 trace name context forever, level 12';
执行语句
select count(*) from all_objects;
关闭10046追踪
alter session set events '10046 trace name context off';
当退出当前会话的时候，Oracle就会将追踪的结果写入到trace文件目录
使用Oracle提供的tkprof来格式化打开追踪文件，进行分析，如下：
tkprof ora_2229_10046.trc 888.trc
vim 888.trc
---开启会话跟踪：alter system set events ‘10046 trace name context forever, level 12’;
---关闭会话跟踪：alter system set events ‘10046 trace name context off’;
---收集特定session的10046
SPID 是操作系统的进程标识符（os pid）
PID 是Oracle的进程标识符(ora pid)
假设需要被跟踪的OSPID是9834，以sysdba的身份登录到SQL*Plus并执行下面的命令：
oradebug setospid 9834
oradebug unlimit
oradebug event 10046 trace name context forever,level 12
   
注：也可以通过oradebug使用 'setorapid'命令连接到一个session。
下面的例中， 使用PID（Oracle进程标识符）(而不是SPID), oradebug命令将被改为：

connect  as sysdba
oradebug setorapid 9834
oradebug unlimit
oradebug event 10046 trace name context forever,level 12
跟踪过程完成以后，关闭oradebug跟踪：
oradebug event 10046 trace name context off

SELECT d.VALUE || '/' || LOWER(RTRIM(i.INSTANCE, CHR(0))) || '_ora_' ||
       p.spid || '.trc' AS "trace_file_name"
  FROM (SELECT p.spid
          FROM v$mystat m, v$session s, v$process p
         WHERE m.statistic# = 1
           AND s.SID = m.SID
           AND p.addr = s.paddr) p,
       (SELECT t.INSTANCE
          FROM v$thread t, v$parameter v
         WHERE v.NAME = 'thread'
           AND (v.VALUE = 0 OR t.thread# = TO_NUMBER(v.VALUE))) i,
       (SELECT VALUE FROM v$parameter WHERE NAME = 'user_dump_dest') d;
 
优点:
(1)可以看出SQL语句对应的等待事件；
(2)如果SQL语句中有函数调用，函数中又有SQL，将会被列出；
(3)可以方便的看出处理的行数，产生的物理读，逻辑读；
(4)可以方便的看出解析时间和执行计划；
(5)可以跟踪整个程序包；
缺点：
(1)步骤繁琐；
(2)无法判断表被访问多少次；
(3)执行计划中谓词部分不能清晰展现出来；

awrsqrpt.sql 
(1)@?/rdbms/admin/awrsqrpt.sql
(2)begin snap，end snap 
(3)输入sql_id

就是从第ID=0的向下看，一路向下，如果缩进被挡住了，就是最先执行的。。。如果同级，是在上面的先执行
除了标量子查询啥的，不符合这种规则。。

使用AUTOTRACE或者EXPLAIN PLAN FOR获取的执行计划来自于PLAN_TABLE。
PLAN_TABLE是一个会话级的临时表，里面的执行计划并不是SQL真实的执行计划，它只是优化器估算出来的。
真实的执行计划不应该是估算的，应该是真正执行过的。
SQL执行过的执行计划存在于共享池中，具体存在于数据字典V$SQL_PLAN中，带有A-Time的执行计划来自于V$SQL_PLAN，是真实的执行计划，
而通过AUTOTRACE、通过EXPLAIN PLAN FOR获取的执行计划只是优化器估算获得的执行计划。

select sql_text, sql_fulltext, sql_id, first_load_time, LAST_ACTIVE_TIME
  from v$sqlarea t
 where t.PARSING_SCHEMA_NAME in ('K2_20181205')
 order by t.LAST_ACTIVE_TIME desc;

alter session set tracefile_identifier='10046';
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
...
ALTER SESSION SET EVENTS '10046 trace name context off';
tkprof 原文件.trc 目标文件

connect / as sysdba
oradebug setorapid 9834
oradebug unlimit
oradebug event 10046 trace name context forever,level 12
跟踪过程完成以后，关闭oradebug跟踪：
oradebug event 10046 trace name context off

SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump systemstate 266
Statement processed.
SQL>--等上30秒到1分钟
SQL> oradebug dump systemstate 266
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/nctst/nctst/trace/nctst_ora_27983.trc

alter session set tracefile_identifier='10053';
ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';
......
ALTER SESSION SET EVENTS '10053 trace name context off';
select value from v$diag_info where name='Default Trace File';

--SQL 10046
alter session set tracefile_identifier='enmo10046';
alter session set events '10046 trace name context forever, level 12';
run your sql;
alter session set events '10046 trace name context off';
--如果会话已经运行了，可以用oradebug
conn  as sysdba
oradebug setospid 16835
oradebug unlimit
oradebug event 10046 trace name context forever,level 12
oradebug event 10046 trace name context off
--systemstate dump
sqlplus -prelim  as sysdba
oradebug setmypid
oradebug unlimit;
oradebug dump systemstate 266;
--wait for 1 min
oradebug dump systemstate 266;
--wait for 1 min
oradebug dump systemstate 266;
oradebug tracefile_name;
--hanganalyze
oradebug setmypid
oradebug unlimit;
oradebug dump hanganalyze 3
--wait for 1 min
oradebug dump hanganalyze 3
--wait for 1 min
oradebug dump hanganalyze 3
oradebug tracefile_name;

---DBA_HIST_SQL_PLAN，DBA_HIST_SQLSTAT，DBA_HIST_SNAPSHOT
select distinct SQL_ID,
                PLAN_HASH_VALUE,
                to_char(TIMESTAMP, 'yyyymmdd hh24:mi:ss') TIMESTAMP
  from dba_hist_sql_plan
 where SQL_ID = '2fjy1vnu83bwj'
 order by TIMESTAMP desc;
---
select plan_hash_value,
       id,
       operation,
       options,
       object_name,
       depth,
       cost,
       to_char(TIMESTAMP, 'yyyymmdd hh24:mi:ss')
  from DBA_HIST_SQL_PLAN
 where sql_id = '2fjy1vnu83bwj'
   and plan_hash_value in (4030773303,801583493)
 order by ID, TIMESTAMP;

select distinct SQL_ID,
                PLAN_HASH_VALUE,
                to_char(TIMESTAMP, 'yyyymmdd hh24:mi:ss') TIMESTAMP
  from dba_hist_sql_plan
 where SQL_ID = 'afqfknn3nwwpw'
 order by TIMESTAMP;
 
select plan_hash_value,
       id,
       operation,
       options,
       object_name,
       depth,
       cost,
       to_char(TIMESTAMP, 'yyyymmdd hh24:mi:ss')
  from DBA_HIST_SQL_PLAN
 where sql_id = 'afqfknn3nwwpw'
   and plan_hash_value in (1542630049, 2754593971, 2620382595)
 order by ID, TIMESTAMP;

oracle 10G 以后可以通过下面的三个视图查询到sql执行计划的历史信息：
DBA_HIST_SQL_PLAN
DBA_HIST_SQLSTAT
DBA_HIST_SNAPSHOT

select distinct SQL_ID,PLAN_HASH_VALUE,to_char(TIMESTAMP,'yyyymmdd hh24:mi:ss')  TIMESTAMP
from dba_hist_sql_plan 
where SQL_ID='68wnxdjxwwn2h' order by TIMESTAMP;

col options for a15
col operation for a20
col object_name for a20
select plan_hash_value,id,operation,options,object_name,depth,cost,to_char(TIMESTAMP,'yyyymmdd hh24:mi:ss')
    from DBA_HIST_SQL_PLAN  
    where sql_id ='68wnxdjxwwn2h' 
    and plan_hash_value in (1542630049,2754593971,2620382595)
    order by ID,TIMESTAMP;

1.hint
2.存储大纲stored outline
3.sql_profile
4.spm

SQL计划管理（SPM）是一种预防性机制，它记录和评估SQL语句的执行计划。
SPM构建SQL计划基线，由一组已知有效的现有计划组成。
然后使用SQL计划基线来保持相应SQL语句的性能，而不管系统中发生什么变化，从而提供“计划稳定性”。

数据库升级
安装新优化器版本的数据库升级通常会导致一小部分SQL语句的计划更改，而大多数计划更改不会导致性能更改或改进。
但是，某些计划变更可能会导致绩效下降。
SQL计划基线的使用大大减少了数据库升级导致的潜在性能下降。

系统/数据更改
正在进行的系统和数据更改可能会影响某些SQL语句的计划，可能会导致性能下降。SQL计划基线的使用将有助于最小化性能退化并稳定SQL性能。

应用程序升级
部署新的应用程序模块意味着在系统中引入新的SQL语句。应用软件可以使用在标准测试配置下为新SQL语句开发的适当SQL执行计划。
如果您的系统配置与测试配置明显不同，那么SQL计划基线可以随着时间的推移而变化，以产生更好的性能。
如果您在游标缓存中有一个好的计划，那么您可以将这些计划加载到SPM中，以便可以使用此基线来保持性能。以下示例命令说明了这一点：

set serveroutput on
var n number
begin
:n:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'&sql_id', plan_hash_value=>&plan_hash_value, fixed =>'NO', enabled=>'YES');
end;
/

EXEC dbms_output.put_line('Number of plans loaded: ' || :n);

启用SPM后，对于那些具有SQL计划管理基线的SQL ID，数据库将不会收集新的访问计划（即使在禁用SPM后-假设基线处于活动状态）

本文档旨在展示一个SQL计划管理示例。

下面是一个脚本，您可以运行该脚本来演示SPM如何操作，然后将基本原则应用到实际代码中。
脚本包含注释，用于解释运行时发生的情况。
该脚本最初是在SH模式的11.1.0.6.0上创建和测试的。其他版本的输出可能略有不同。
SCRIPT spm.sql

绑定变量
SELECT snap_id, NAME, position , value_string, last_captured,WAS_CAPTURED
FROM dba_hist_sqlbind;
还有一个dba_hist_sqlbind也是记录了awr中绑定变量的内容
不过这两个视图中记录的绑定变量只对where条件后面的绑定进行捕获，这点需要使用的时候注意。

绑定变量
10g 数据倾斜严重时，使用绑定变量，导致所有同类SQL都走同一个执行计划，部分SQL效率低；
11g 游标自适应，可以自动从多个执行计划中找到最优的执行计划，避免这个问题；

EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => 'cjc',tabname => 't1',estimate_percent=>100,method_opt=> 'FOR ALL INDEXED COLUMNS',CASCADE=> TRUE,no_invalidate=> FALSE,degree=>);
EXEC DBMS_STATS.GATHER_TABLE_STATS('k2_20181211','t_BD_AccountView',estimate_percent=>100,method_opt=> 'FOR ALL INDEXED COLUMNS',CASCADE=> TRUE);
EXEC DBMS_STATS.GATHER_TABLE_STATS('OWNER','TABLE',estimate_percent=>30,CASCADE=> TRUE);
dbms_stats.gather_table_stats(User,TableName,degree => 4,cascade => true);
EXEC DBMS_STATS.GATHER_TABLE_STATS('OWNER','TABLE',estimate_percent=>30,CASCADE=> TRUE);

exec dbms_stats.gather_schema_stats('HR'); 

exec DBMS_STATS.GATHER_DATABASE_STATS (estimate_percent=>10,Degree=>8, Cascade=>TRUE, Granularity=>'ALL');

How To Run DBMS_STATS.GATHER_TABLE_STATS Using Dynamic Query (Doc ID 1072911.1)

create or replace procedure getstattest IS

str1 varchar2(1000);
cnt NUMBER;
CURSOR c_C1 IS select TABLE_NAME, OWNER from dba_tables where OWNER IN ('SCOTT') and TABLE_NAME IN ('EMP', 'DEPT','BONUS','SALGRADE');
begin
FOR tab_rec IN c_C1
LOOP
cnt:=0;
dbms_output.put_line('Checking OWNER: '||tab_rec.OWNER);
dbms_output.put_line('Checking TAB: '||tab_rec.TABLE_NAME);
execute immediate 'SELECT COUNT (*) FROM '|| tab_rec.TABLE_NAME INTO cnt;
if cnt > 2 then
str1:='BEGIN dbms_stats.gather_table_stats(ownname =>'''||tab_rec.OWNER||''',tabname =>'''||tab_rec.TABLE_NAME||''',cascade => true,degree=>
0); END;';
dbms_output.put_line(str1);
execute immediate str1;
end if;
cnt:=0;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
       DBMS_OUTPUT.put_line('An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM||' '||str1);
END;
/

SQL> set serverout on;
SQL> exec  getstattest

10g
gather_stats_job Scheduler调度

select program_name, schedule_name, schedule_type, enabled, state
  from dba_scheduler_jobs
 where owner = 'SYS'
   and job_name = 'GATHER_STATS_JOB';

select program_action, number_of_arguments, enabled
  from dba_scheduler_programs
 where owner = 'SYS'
   and program_name = 'GATHER_STATS_PROG';

select w.window_name, w.repeat_interval, w.duration, w.enabled
  from dba_scheduler_wingroup_members m, dba_scheduler_windows w
 where m.window_name = w.window_name
   and m.window_group_name = 'MAINTENANCE_WINDOW_GROUP';

11g 自动维护任务
select task_name, status
  from dba_autotask_task
 where client_name = 'auto optimizer stats collection';

select program_action, number_of_arguments, enable
  from dba_scheduler_programs
 where owner = 'SYS'
   and program_name = 'GATHER_STATS_PROG';

select w.window_name, w.repeat_interval, w.duration, w.enabled
  from dba_autotask_window_clients c, dba_scheduler_windows w
 where c.window_name = w.window_name
   and c.optimizer_stats = 'ENABLED';

启用
BEGIN
  DBMS_AUTO_TASK_ADMIN.ENABLE(client_name => 'auto optimizer stats collection',
                              operation   => NULL,
                              window_name => NULL);
END;
/
禁用
BEGIN
  DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto optimizer stats collection',
                               operation   => NULL,
                               window_name => NULL);
END;
/

analyze table gl_detail compute statistics for columns prepareddatev size 254;
EXEC DBMS_STATS.GATHER_TABLE_STATS('OWNER','TABLE',estimate_percent=>30,method_opt => 'FOR ALL INDEXED COLUMNS',CASCADE=> TRUE);
exec dbms_stats.gather_table_stats('chen','test1',method_opt=>'for columns (ACCYEAR,ACCMONTH) size AUTO');
或
begin
  dbms_stats.gather_table_stats('chen',
                                'test1',
                                method_opt => 'for columns status size AUTO');
end;
例如：
select count(distinct id) from t1; ---num_distinct=5001>num_buckets(default=75)
select count(distinct id) from t2; ---num_distinct=3<num_buckets(default=75) ---frequence histograms

select table_name,column_name,density,num_distinct,num_buckets,histogram from user_tab_col_statistics;
频率直方图(frequence histograms)
频率直方图列中的不同值被划到相同数量的桶中.每一个桶中存储的都是相同的值,也就是说频率直方图的桶数等于列的不同值的个数.buckets=ndv

高度平衡直方图(height-balanced histograms)
在频率直方图中oracle给每一个不同值分配一个桶,然而桶的最大个数是254,因此如果表中的列有大量的不同值(超过254),将会创建一个高度平衡的直方图.
在高度平衡直方图中,因为我们的不同值超过了桶的个数,因此oracle首先分对列数据进行排序然后将数据集按桶数进行分类且除了最后一桶可能包含的数据比其它的桶少以外,
所有其它的桶包含相同数量的值(这就是为什么叫高度平等直方图的原因).

列的相关性：就是where子句中同时存在2个列以上条件时，CBO不做列与列之间相关性分析，这会导致CBO计算执行计划出现一种偏差
select count(*) from leo6 where object_type='TABLE' and owner='LEO1';
execute dbms_stats.gather_table_stats('LEO1','LEO6',method_opt=>'for columns (object_type,owner) size skewonly');

收集统计信息(完全计算法)： 
analyze table abc compute statistics;
---删除统计信息(级联删除列直方图)
begin
  dbms_stats.delete_table_stats(ownname => 'CHEN', tabname => 'TEST1');
end;
或
ANALYZE TABLE TEST1 DELETE STATISTICS;

###收集统计信息[抽样估算法(抽样20%)]： 
analyze table abc estimate statistics sample 20 percent;
###数据库级统计信息
rq="start time":`date +"%Y-%m-%d %H:%M:%S"`
echo $rq>>/oracle/scripts/stats.log
sqlplus nolog <<EOF
connect  as sysdba;
exec DBMS_STATS.GATHER_DATABASE_STATS (estimate_percent=>10,Degree=>8, Cascade=>TRUE, Granularity=>'ALL');
EOF
rq="stop time":`date +"%Y-%m-%d %H:%M:%S"`echo $rq>>/oracle/scripts/stats.log

收集一下字典表的统计信息，递归SQL执行计划有问题
exec dbms_stats.gather_fixed_objects_stats;
exec dbms_stats.gather_dictionary_stats;
execute dbms_stats.gather_schema_stats(‘SYS’);

(1) 首先创建一个分析表，该表是用来保存之前的分析值：
begin
  dbms_stats.create_stat_table(ownname => 'CHEN', stattab => 'STAT_TABLE',cascade => true);
end;

(2) 导出表分析信息到stat_table中
begin
  dbms_stats.export_table_stats(ownname => 'CHEN',
                                tabname => 'T1',
                                stattab => 'STAT_TABLE');
end;

(3)开始更新T1表统计信息
begin
  dbms_stats.gather_table_stats(ownname => 'CHEN', tabname => 'T1');
end;
/*
begin
  dbms_stats.gather_table_stats(ownname => 'CHEN',
                                tabname => 'T1',
                                cascade => true);
end;
*/
(4)删除分析信息
begin
  dbms_stats.delete_table_stats(ownname => 'CHEN', tabname => 'T1');
end;

(5)导入分析信息
begin
  dbms_stats.import_table_stats(ownname => 'CHEN',
                                tabname => 'T1',
                                stattab => 'STAT_TABLE');
end;

---这一步非常重要，需要显式地赋予用户建表权限
grant create any table to chen ;
---创建收集统计信息的存储过程
CREATE OR REPLACE PROCEDURE ANALYZE_TB AS
  OWNER_NAME  VARCHAR2(100);
  V_LOG       INTEGER;
  V_SQL1      VARCHAR2(800);
  V_TABLENAME VARCHAR2(50);
  CURSOR CUR_LOG IS
    SELECT COUNT(*) FROM USER_TABLES WHERE TABLE_NAME = 'ANALYZE_LOG';
  --1
BEGIN
  --DBMS_OUTPUT.ENABLE (buffer_size=>100000);
  --1.1
  BEGIN
    OPEN CUR_LOG;
    FETCH CUR_LOG
      INTO V_LOG;
    IF V_LOG = 0 THEN
      EXECUTE IMMEDIATE 'CREATE TABLE ANALYZE_LOG (USER_NAME VARCHAR(20),OP_TIME CHAR(19) DEFAULT to_char(sysdate,''yyyy-mm-dd hh24:mi:ss''),ERROR_TEXT VARCHAR(200),TABLE_NAME VARCHAR(40))';
    END IF;
  END;

  SELECT USER INTO OWNER_NAME FROM DUAL;
  V_SQL1 := 'INSERT INTO ANALYZE_LOG (USER_NAME,ERROR_TEXT,TABLE_NAME) VALUES (''' ||
            OWNER_NAME || ''',''ANALYZE BEGIN'',''ALL'')';
  EXECUTE IMMEDIATE V_SQL1;
  sys.dbms_stats.gather_schema_stats(ownname          => UPPER(OWNER_NAME),
                                     estimate_percent => 100,
                                     method_opt       => 'FOR ALL INDEXED COLUMNS',
                                     cascade          => TRUE);
  V_SQL1 := 'INSERT INTO ANALYZE_LOG (USER_NAME,ERROR_TEXT,TABLE_NAME) VALUES (''' ||
            OWNER_NAME || ''',''ANALYZE END'',''ALL'')';
  EXECUTE IMMEDIATE V_SQL1;
  commit;

  --1.2 delete tmptb statitics and lock statistics
  BEGIN
    for x in (select a.table_name, a.last_analyzed, b.stattype_locked
                from user_tables a, user_tab_statistics b
               where a.temporary = 'Y'
                 and a.table_name = b.table_name
                 and (b.STATTYPE_LOCKED is null or
                     a.last_analyzed is not null)) LOOP
      IF x.last_analyzed IS NOT NULL THEN
        --delete stats
        dbms_stats.delete_table_stats(ownname => user,
                                      tabname => x.table_name,
                                      force   => TRUE);
      END IF;
    
      IF x.stattype_locked IS NULL THEN
        --lock stats
        dbms_stats.lock_table_stats(ownname => user,
                                    tabname => x.table_name);
      END IF;
    END LOOP;
  end;
EXCEPTION
  WHEN OTHERS THEN
    IF CUR_LOG%ISOPEN THEN
    
      CLOSE CUR_LOG;
    END IF;
    commit;
end;

SQL> VARIABLE JOBNO NUMBER;
SQL> VARIABLE INSTNO NUMBER;
SQL> 
SQL> BEGIN
  2    SELECT INSTANCE_NUMBER INTO :INSTNO FROM V$INSTANCE;
  3    DBMS_JOB.SUBMIT(:JOBNO,
  4                    'ANALYZE_TB; ',
  5                    TRUNC(SYSDATE) + 1 + 2  24,
  6                    'TRUNC(SYSDATE)+2+2/24',
  7                    TRUE,
  8                    :INSTNO);
  9    COMMIT;
 10  END;
 11  /
 
PL/SQL procedure successfully completed
INSTNO
---------
1
JOBNO
---------
5

---执行存储过程
SQL> exec ANALYZE_TB;
PL/SQL procedure successfully completed

---查看表信息
select table_name,tablespace_name,logging,last_analyzed,num_rows,temporary from user_tables;

select index_name,index_type,table_owner,table_name,table_type,last_analyzed,num_rows from user_indexes;

---Oracle10g： 
exec dbms_scheduler.disable('SYS.GATHER_STATS_JOB');
exec dbms_scheduler.enable('SYS.GATHER_STATS_JOB');
select job_name,
       schedule_name,
       enabled,
       last_start_date,
       last_run_duration,
       next_run_date
  from dba_scheduler_jobs a
 where job_name = 'GATHER_STATS_JOB';
 
SQL>Exec DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
    exec dbms_scheduler.disable('gather_stats_job'); 


---Oracle11g： 
BEGIN
  DBMS_AUTO_TASK_ADMIN.disable(client_name => 'auto optimizer stats collection',
                               operation   => NULL,
                               window_name => NULL);
END;


---SQL> select client_name,status from dba_autotask_client;
禁用自动收集统计信息的任务可以使用DBMS_AUTO_TASK_ADMIN包完成：
SQL> exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);
启用自动收集统计信息的任务
SQL> exec DBMS_AUTO_TASK_ADMIN.ENABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);

ANALYZE TABLE wa_cacu_data DELETE STATISTICS;
SQL> execute dbms_stats.lock_table_stats(ownname=>'CHEN',tabname=>'WA_CACU_DATA');
SQL> execute dbms_stats.unlock_table_stats(ownname=>'CHEN',tabname=>'WA_CACU_DATA');

========
移除JOB
========
exec dbms_job.remove(1233)
========
停止JOB
========
select sid from dba_jobs_running;
select a.spid from v$process a ,v$session b where a.addr=b.paddr  and b.sid in (select sid from dba_jobs_running);
使用DBMS_JOB包来标识你的JOB为BROKEN。
SQL> EXEC DBMS_JOB.BROKEN(job#,TRUE);
注意：当执行完该命令你选择的这个JOB还是在运行着的。 
Kill 对应的Oracle Session
ALTER SYSTEM KILL SESSION 'sid,serial#';
或者直接KILL对应的操作系统的SESSION，如果使用ALTER SYSTEM KILL SESSION执行很长时间，其实可以使用OS的命令来快速KILL掉SESSION.
For Windows, at the DOS Prompt: orakill sid spid
For UNIX at the command line> kill –9 spid

---定制job
SQL> variable job1 number;
SQL> begin
  2  dbms_job.submit(:job1,'p_test;',sysdate,'sysdate+1/1440');
  3  end;
  4  
 
PL/SQL procedure successfully completed
job1
---------
23
---创建job
SQL> begin
  2  dbms_job.run(23);
  3  end;
  4  
 
PL/SQL procedure successfully completed

---删除JOB
SQL> begin
  2  dbms_job.remove(23);
  3  end;
  4  
 
PL/SQL procedure successfully completed

===============
停止JOB 
===============
ALTER SYSTEM KILL SESSION 'sid,serial#';
SQL> select name,value from v$parameter where name ='job_queue_processes';     
SQL> ALTER SYSTEM SET job_queue_processes = 0;
恢复job_queue_processes的原始值
SQL> ALTER SYSTEM SET job_queue_processes = original_value;

##查询jobs
set lin 200
col interval for a30
col last_sec for a10
col this_sec for a10
col next_sec for a10
col schema_user for a20
col log_user for a20
col what for a80
select job,schema_user,this_date,this_sec,next_date,next_sec,interval,what from dba_jobs where  schema_user not in ('APEX_030200');

--scheduler
set pages 200 lin 200
col owner for a10
col job_name for a30
col START_DATE for a40
select owner,job_name,START_DATE from dba_scheduler_jobs where owner not in ('EXFSYS','SYS','ORACLE_OCM');

SQL> Exec dbms_workload_repository.create_snapshot; 
SQL> SHOW PARAMETER STATISTICS_LEVEL 
如果STATISTICS_LEVEL的值为TYPICAL或者 ALL，表示启用AWR；如果STATISTICS_LEVEL的值为BASIC，表示禁用AWR。

--产生整个数据库的AWR报告，运行脚本awrrpt.sql。
@$ORACLE_HOME/rdbms/admin/awrrpt.sql 
--产生某个实例的AWR报告，运行脚本awrrpti.sql。
@$ORACLE_HOME/rdbms/admin/awrrpti.sql 
--产生某条SQL语句的AWR报告，运行脚本awrsqrpt.sql。
 @$ORACLE_HOME/rdbms/admin/awrsqrpt.sql 

@?/rdbms/admin/awrgrpt
1 awr
@?/rdbms/admin/awrrpt.sql
2 ash
@?/rdbms/admin/ashrpt.sql
3 addm
@?/rdbms/admin/addmrpt.sql
4 awrsqrpt
@?/rdbms/admin/awrsqrpt.sql
5 awrdd
@?/rdbms/admin/awrddrpt.sql

--数据库繁忙程度----
set line 200 pages 2000
col DB_TIME_US for 9999999999999.9999
select b.instance_number,
       b.snap_id as begin_snap,
       lead(b.snap_Id,1,null) over (order by b.snap_Id) as end_snap,
       to_char(b.end_interval_time,'YYYY-MM-DD hh24:MI:SS') as begin_time ,
       to_char(lead(b.end_interval_time,1,null) over (order by b.snap_id),'YYYY-MM-DD hh24:MI:SS') as end_time,   
       extract(hour from  lead(b.end_interval_time,1,null) over (order by b.snap_id) - b.end_interval_time ) * 60 
       + extract(minute from  lead(b.end_interval_time,1,null) over (order by b.snap_id) - b.end_interval_time )  as Elapsed_minutes,       
       a.value as db_time_us ,
       round((lead(a.value,1,null) over (order by b.snap_id) -  a.value)/1000000/60,2) as DbTime_minutes
  from sys.WRH$_SYS_TIME_MODEL A, dba_hist_snapshot B, sys.wrh$_stat_name C
 where a.dbid = b.dbid
   and a.instance_number=b.instance_number
   and a.snap_id = b.snap_id
   and a.stat_id = c.stat_id
   and b.instance_number=1
   and c.stat_name = 'DB time'
   and b.end_interval_time>systimestamp -7
   ORDER BY 8 DESC;

--查看latch那个数据字典争用严重
set lin 200 pages 200
col cache# head "Cache|no" form 999
col parameter head "Parameter" form a25
col type head "Type" form a12
col subordinate# head "Sub|ordi|nate" form 9999
col rcgets head "Cache|Gets" form 999999999999
col rcmisses head "Cache|Misses" form 999999999999
col rcmodifications head "Cache|Modifica|tions" form 999999999999
col rcflushes head "Cache|Flushes" form 999999999999
col kqrstcln head "Child#" form 999
col lagets head "Latch|Gets" form 999999999999
col lamisses head "Latch|Misses" form 999999999999
col laimge head "Latch|Immediate|gets" form 999999999999
select dc.kqrstcid CACHE#, dc.kqrsttxt PARAMETER, decode(dc.kqrsttyp, 1,'PARENT','SUBORDINATE') type,
decode(dc.kqrsttyp, 2, kqrstsno, null) subordinate#,
dc.kqrstgrq rcgets, dc.kqrstgmi rcmisses, dc.kqrstmrq rcmodifications, dc.kqrstmfl rcflushes, dc.kqrstcln, 
la.gets lagets, la.misses lamisses, la.immediate_gets laimge from x$kqrst dc,v$latch_children la 
where dc.inst_id = userenv('instance') 
and la.child# = dc.kqrstcln 
and la.name = 'row cache objects' 
order by rcgets desc
/

--查看历史数据增长量，通过数据文件判断
SELECT
    TO_CHAR(creation_time, 'RRRR-MM')  month
  , SUM(bytes)/1024/1024                         growth_MB
FROM     sys.v_$datafile
GROUP BY TO_CHAR(creation_time, 'RRRR-MM')
ORDER BY TO_CHAR(creation_time, 'RRRR-MM');

SELECT
    TO_CHAR(creation_time, 'RRRR-MM-DD')  month
  , SUM(bytes)/1024/1024                         growth_MB
FROM     sys.v_$datafile
GROUP BY TO_CHAR(creation_time, 'RRRR-MM-DD')
ORDER BY TO_CHAR(creation_time, 'RRRR-MM-DD');
###索引监控
set pagesize 0;
set feedback off;
spool monitor_index.sql; 
select 'alter index CJC.' || index_name || ' monitoring usage;' from dba_indexes where owner = 'CJC';
spool off;
rem start monitor_index.sql

SQL> alter index CJC.I_T2_ID monitoring usage;
取消监控
SQL> alter index <索引名> nomonitoring usage;
SQL> select * from v$object_usage
   INDEX_NAMETABLE_NAMEMONITORINGUSEDSTART_MONITORINGEND_MONITORING
1I_T2_IDT2NOYES06/17/2020 10:14:1606/17/2020 10:16:25
###空间碎片
以下查询是碎片最严重的前100个表的脚本及结果
col frag format 999999.99
col owner format a30;
col table_name format a30;
select *
  from (select a.owner "用户名",
               a.table_name "表名",
               a.num_rows "记录数",
               a.avg_row_len * a.num_rows "需要空间",
               sum(b.bytes) "实际空间",
               (a.avg_row_len * a.num_rows)  sum(b.bytes) "碎片率"
          from dba_tables a, dba_segments b
         where a.table_name = b.segment_name
           and a.owner = b.owner
           and a.num_rows <> 0
           ---and a.table_name = 'XXX'
           and a.owner not in ('SYS',
                               'SYSTEM',
                               'OUTLN',
                               'DMSYS',
                               'TSMSYS',
                               'DBSNMP',
                               'WMSYS',
                               'EXFSYS',
                               'CTXSYS',
                               'XDB',
                               'OLAPSYS',
                               'ORDSYS',
                               'MDSYS',
                               'SYSMAN')
         group by a.owner, a.table_name, a.avg_row_len, a.num_rows
        having a.avg_row_len * a.num_rows  sum(b.bytes) < 0.7
         order by sum(b.bytes) desc)
 where rownum <= 100;
###监控，统计表DML次数
user_tab_modifications
dba_tab_modifications

select * from user_tab_modifications order by updates desc;

select table_name, last_analyzed, num_rows, modify_rows, modify_rate
  from (select t.table_name,
               t.last_analyzed,
               t.num_rows,
               m.modify_rows,
               round(m.modify_rows  t.num_rows * 100, 2) as modify_rate
          from user_tables t
         inner join (select table_name,
                           inserts + updates + deletes as modify_rows
                      from user_tab_modifications
                     where inserts + updates + deletes > 0) M
            on t.table_name = m.table_name
           and t.num_rows > 0)
 where modify_rate > 10
 order by modify_rate;
 
###top sql
---查看逻辑读前10的SQL：
SELECT *
  FROM (SELECT sql_fulltext AS sql,
               SQL_ID,
               buffer_gets  executions AS "Gets/Exec",
               buffer_gets,
               executions
          FROM V$SQLAREA
         WHERE buffer_gets > 10000
         ORDER BY "Gets/Exec" DESC)
 WHERE rownum <= 10;

---查看物理读前10的SQL：
SELECT *
  FROM (SELECT sql_fulltext AS sql,
               SQL_ID,
               disk_reads  executions AS "Reads/Exec",
               disk_reads,
               executions
          FROM V$SQLAREA
         WHERE disk_reads > 1000
         ORDER BY "Reads/Exec" DESC)
 WHERE rownum <= 10;

---elapsed_time
select a.*, elapsed_seconds  executions elapsed_per
  from (select sql_text,
               round(elapsed_time  1000000, 2) elapsed_seconds,
               executions,
               sql_id,
               buffer_gets,
               disk_reads
          from (select * from v$sql order by elapsed_time desc)
         where rownum <= 100) a
 order by elapsed_per desc;
 
--查看pga占用最多的进程
select p.spid,p.pid,s.sid,s.serial#,s.status,p.pga_alloc_mem,s.username,s.osuser,s.program from v$process p,
v$session s where s.paddr(+)=p.addr order by p.pga_alloc_mem desc;
--查看登录时间最长的会话
select * from (select t.sid,t2.spid,t.program,t.status,t.sql_id,t.prev_sql_id,t.event,to_char(t.logon_time,'yyyymmdd hh24:mi:ss') logon,
trunc(sysdate-logon_time) from v$session t,v$process t2 where t.paddr=t2.addr and t.type<>'BACKGROUND' order by logon_time) where rownum<=20;
--查看逻辑读最多的SQL
select * from (select sql_id,sql_text,s.executions,s.last_load_time,s.first_load_time,s.disk_reads,s.buffer_gets from v$sql s  where 
buffer_gets >300 order by buffer_gets desc) where rownuM<20;
--物理读最多的SQL
select * from (select sql_id,sql_text,s.executions,s.last_load_time,s.first_load_time,s.disk_reads,s.buffer_gets,PARSE_CALLS from v$sql s  where 
disk_reads >300 order by disk_reads desc) where rownuM<20;
--执行最多的SQL
select * from (select sql_id,sql_text,s.executions,s.last_load_time,s.first_load_time,s.disk_reads,s.buffer_gets,PARSE_CALLS from v$sql s   X 
order by executions desc) where rownuM<20;
--查看解析次数最多的SQL
select * from (select sql_id,sql_text,s.executions,s.last_load_time,s.first_load_time,s.disk_reads,s.buffer_gets,PARSE_CALLS from v$sql s    
order by PARSE_CALLS desc) where rownuM<20;
--查看disk sort 严重的SQL
SELECT SESS.USERNAME,SQL.SQL_TEXT,sql.address,sort1.blocks from v$session sess,
v$sqlarea sql,v$sort_usage sort1 where sess.serial#=sort1.session_num and sort1.sqladdr=sql.address
and sort1.sqlhash=sql.hash_value and sort1.blocks>200 order by sort1.blocks desc;

--查看等待产生次数及相关会话,过分提交的SQL
select t1.sid,t1.value,t2.name from v$sesstat t1,v$statname t2 where 
t2.name like '%user commits%' and t1.statistic#=t2.statistic# and value >=10000;

select t.sid,t.program,t.machine,to_char(t.logon_time,'yyyymmdd hh24:mi:ss') logon,t.wait_class,t.wait_time,
t.seconds_in_wait,t.event,t.sql_id,t.prev_sql_id from v$session t where sid in (xxx,xx);

--查看共享内存使用大小
select count(*),round(sum(sharable_mem)/1024/1024,2) MB from v$db_object_cache a;
--可以获取系统中应该使用绑定变量的SQL

--检查是否有显著未释放的高水位表,一个块放5条记录，降低高水位通过：shrink table 、move table、rename table
select table_name,blocks,num_rows from dba_tables where blocks/num_rows>=0.2 and
num_rows is not null and num_rows<>0 and blocks>=1000;

/*+leading(t1) use_nl(t2)*/ 
  sql语句中from后第一个表为驱动表，执行计划中排序下面首个表为驱动表
  强制先访问t1表，即作为驱动表
  use_nl强制使用嵌套循环，小表做驱动表，不需要排序
  use_hash强制使用hash循环，小表做驱动表，需要排序
  use_merge强制使用排序合并，需要排序
  
--占用dbtime最高的SQL
SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, st.value/100 as "DB Time 
(sec)"
, stcpu.value/100 as "CPU Time (sec)", round(stcpu.value  st.value * 100,2) as "% CPU"
FROM v$sesstat st, v$statname sn, v$session s, v$sesstat stcpu, v$statname sncpu, v$process p
WHERE sn.name = 'DB time' -- CPU
AND st.statistic# = sn.statistic#
AND st.sid = s.sid
AND  sncpu.name = 'CPU used by this session' -- CPU
AND stcpu.statistic# = sncpu.statistic#
AND stcpu.sid = st.sid
AND s.paddr = p.addr
AND s.last_call_et < 1800 -- active within last 1/2 hour
AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours
AND st.value > 0;
--等待最长的SQL
SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, se.time_waited
FROM v$session_event se, v$session s, v$process p
WHERE se.event = '&event_name' 
AND s.last_call_et < 1800 -- active within last 1/2 hour
AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours
AND se.sid = s.sid
AND s.paddr = p.addr
ORDER BY se.time_waited;
--占用CPU
SELECT s.sid, s.serial#, p.spid as "OS PID",s.username, s.module, st.value/100 as "CPU sec"
FROM v$sesstat st, v$statname sn, v$session s, v$process p
WHERE sn.name = 'CPU used by this session' -- CPU
AND st.statistic# = sn.statistic#
AND st.sid = s.sid
AND s.paddr = p.addr
AND s.last_call_et < 1800 -- active within last 1/2 hour
AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours
ORDER BY st.value;

--查看会话占用内存
set lin 200 pages 199
col name for a20
col username for a20
SELECT   server, s.username, osuser, NAME, VALUE  1024  1024 MB, s.SID, s.serial#, spid
    FROM v$session s, v$sesstat st, v$statname sn, v$process p
   WHERE st.SID = s.SID
     AND st.statistic# = sn.statistic#
     AND sn.NAME LIKE 'session pga memory'
     AND p.addr = s.paddr
   ORDER BY VALUE DESC;

--监控回滚信息
 set linesize 100 
  alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'; 
  select usn, state, undoblockstotal "Total", undoblocksdone "Done", undoblockstotal-undoblocksdone "ToDo",
         decode(cputime,0,'unknown',sysdate+(((undoblockstotal-undoblocksdone)  (undoblocksdone  cputime))  86400)) "Estimated time to complete" 
  from v$fast_start_transactions; 
  
###数据库性能
select s.snap_date,
       decode(s.redosize, null, '--shutdown or end--', s.currtime) "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",
       s.logicalreads logical,
       round(s.logicalreads  s.seconds, 2) "logical/s",
       physicalreads physical,
       round(s.physicalreads  s.seconds, 2) "phy/s",
       s.executes execs,
       round(s.executes  s.seconds, 2) "execs/s",
       s.parse,
       round(s.parse  s.seconds, 2) "parse/s",
       s.hardparse,
       round(s.hardparse  s.seconds, 2) "hardparse/s",
       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, 'yy/mm/dd') snap_date,
               to_char(currtime, 'hh24:mi') currtime,
               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
          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, time desc;

spool常用的设置 
set colsep' ';　　　 //域输出分隔符 
set echo off;　　　　//显示start启动的脚本中的每个sql命令，缺省为on 
set feedback off;　　//回显本次sql命令处理的记录条数，缺省为on 
set heading off;　　 //输出域标题，缺省为on 
set pagesize 0;　　　//输出每页行数，缺省为24,为了避免分页，可设定为0。 
set termout off;　　 //显示脚本中的命令的执行结果，缺省为on 
set trimout on;　　　//去除标准输出每行的拖尾空格，缺省为off 
set trimspool on;　　//去除重定向（spool）输出每行的拖尾空格，缺省为off 

SQL> set timing on;     //设置显示“已用时间：XXXX”
SQL> set autotrace on-;   //设置允许对执行的sql进行分析
SQL> set trimout on; //去除标准输出每行的拖尾空格，缺省为off
SQL> set trimspool on; //去除重定向（spool）输出每行的拖尾空格，缺省为off
SQL> set echo on       //设置运行命令是是否显示语句
SQL> set echo off; //显示start启动的脚本中的每个sql命令，缺省为on
SQL> set feedback on;   //设置显示“已选择XX行”
SQL> set feedback off;   //回显本次sql命令处理的记录条数，缺省为on
SQL> set colsep''; //输出分隔符
SQL> set heading off;   //输出域标题，缺省为on
SQL> set pagesize 0;   //输出每页行数，缺省为24,为了避免分页，可设定为0。
SQL> set linesize 80;   //输出一行字符个数，缺省为80
SQL> set numwidth 12;   //输出number类型域长度，缺省为10
SQL> set termout off;   //显示脚本中的命令的执行结果，缺省为on
SQL> set serveroutput on;  //设置允许显示输出类似dbms_output
SQL> set verify off         //可以关闭和打开提示确认信息old 1和new 1的显示.

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

###bashrc
[oracle@uf2010813 ~]$ cat .bashrc 
alias sql='sqlplus  as sysdba'
[oracle@uf2010813 ~]$ source .bashrc 

###glogin.sql
[oracle@uf2010813 ~]$ cd /opt/oracle/app/oracle/product/11.2.0/dbhome_1/sqlplus/admin/
[oracle@uf2010813 admin]$ cat glogin.sql 
set sqlprompt "_user'@'_connect_identifier> "
[oracle@uf2010813 admin]$ sqlplus  as sysdba
SYS@orcl> 

###number显示科学计数的解决方法
SYS@orcl>  select current_scn from v$database;
CURRENT_SCN
-----------
 1.5249E+13
 
SYS@orcl> show numwidth
numwidth 10
SYS@orcl> set numwidth 20
SYS@orcl> select current_scn from v$database;
         CURRENT_SCN
--------------------
      15248666996607

============
Oracle_HINT
============
select  /*+ full(t1) */ * from t1;--全表扫描
select  /*+ index(t1 idx_name) */ * from t1 where object_id>2;使用指定索引
select  /*+ no_index(t1 idx_name) */ * from t1 where object_id>2; --不使用指定索引
select  /*+ index_desc(t1 idx_name) */ * from t1 where object_id=2; --按索引降序顺序访问数据
select /*+ index_combine(t1 idx_name) */ * from t1;--选择位图索引
select /*+ index_ffs(t1  idx_name) */ from t1 where object_id <100; --索引快速全表扫描（把索引当作一个表看待）
select /*+ index_join(t1 idx_name1 idx_name2) */ * from t1 where object_id=5 and status='VALID'; --同时使用条件列上的相关索引
select /*+ index_ss(t1 index_name) */ * from t1 where object_id=99;  --跳跃式扫描 
select /*+ leading(t1,t) */ t.* from t,t1 where t1.object_id=t.object_id; --指定t1为驱动作，优化器先访问此表
select /*+ ordered */ t.* from t,t1 where t1.id=t.id;   --指定按from 后面表的顺序选择驱表，t作为驱动表
select /*+ use_nl(t1,t) */ t.* from t1,t where t1.object_id=t.object_id;--使用NEST LOOP表连接，适合含有小表数据关联，如一大一小(有别名，必须用别名)
select /*+ use_hash(t1,t) */ t.* from t1,t where t1.object_id=t.object_id;--使用HASH表连接，适合两个大表关联
select /*+ use_merge(t1,t) */t.* from t1,t where t1.object_id=t.object_id;--使用合并排序表连接
select /*+ no_use_nl(t1,t) */ t.* from t1,t where t1.object_id=t.object_id; --不使用NEST LOOP表连接
select /*+ no_use_hash(t1,t) */ t.* from t1,t where t1.object_id=t.object_id;--不使用HASH表连接
select /*+ no_use_merge(t1,t) */t.* from t1,t where t1.object_id=t.object_id; --不使用合并排序表连接
/*+OPTIMIZER_FEATURES_ENABLE('10.2.0.4')*/
SELECT /*+ PUSH_PRED (v1) */ t1.c1,v1.c1
/*+no_push_pred()*/
/*+ no_unnest */ 
/*+ unnest */ 
8.并行相关hint
select /*+ parallel(t 4) */ count(*) from t1; --开启表的4个并行度
select /*+ no_parallel(t) */ count(*) from t1; --不使用并行
表默认的degree(user_tables)如果不为0，默认开启并行针对select;
9.其它hint
select /*+ dynamic_sampling(t 4) */ * from t where id>134;--4为采样级别
select /*+ full(t1) cache(t1) */ object_id from t1;--将表t1放在LRU端最活跃处，相当于表属性的cache(keep);
--以上大部份资料来源于谭怀远《让ORACLE跑得更快》学习

/*+optimizer_features_enable('11.2.0.1')*/
alter system set "_optimizer_mjc_enabled" = false;
---基数反馈
SQL> alter system set “_optimizer_use_feedback”=false;
/*+ opt_param('_optimizer_use_feedback' 'false') cardinality(test,1) */
/*+ opt_param('_optimizer_use_feedback' 'false') */
还可以使用cardinality HINT来强制使用Cardinality Feedback 。
select /*+ cardinality(test,  1) */ count(*) from test;

ORA-08104: this index object 114615 is being online built or rebuilt

---查看正在执行的SQL
select * from v$sql where address in (select sql_address from v$session);

---通过sql_id查看sql的历史执行计划
select * from table(dbms_xplan.display_cursor('sql_sid'));

---查看正在执行SQL和执行时间
select v.last_call_et,
       v.username,
       v.machine,
       v.program,
       v.module,
       v.sid,
       sql.sql_text,
       sql.sql_fulltext,
       sql.sql_id,
       sql.disk_reads,
       v.event
  from v$session v, v$sql sql
 where v.sql_address = sql.address
   and v.last_call_et > 0
   and v.status = 'ACTIVE'
   and v.username is not null;

---SQL> Exec dbms_workload_repository.create_snapshot; 
 
---SQLServer
select text, cpu,*
  from sys.sysprocesses a
 cross apply sys.dm_exec_sql_text(a.sql_handle) st
 where status <> 'sleeping'
 order by a.cpu desc;
 
---通过sql_id查看sql的历史执行计划
select * from table(dbms_xplan.display_cursor('sql_sid'));  
select sid,username,machine,program,module from v$session where module='ORACLE.EXE'

---查看绑定变量值
select * from V$SQL_BIND_CAPTURE where sql_id='';  

select name, position, last_captured, value_string
  from dba_hist_sqlbind
 where sql_id = 'd2rumw0mt86yh'
 order by last_captured desc, name

###查看阻塞信息
方法一：
---通过dba_blockers查看产生阻塞的会话
select * from dba_blockers;  ---391
 
---dba_waiters查看产生阻塞和被阻塞的会话
select waiting_session,holding_session from dba_waiters;
 
从dba_blockers视图中,可以看到,SID=391的session阻塞了别的session,而从dba_waiters可以看到,HOLDING_SESSION为391,等待的WAITING_SESSION为10。

方法二：
---通过v$session中的blocking_session字段查找
select sid,blocking_session from v$session where blocking_session is not null;
 
其中blocking_session=391是产生阻塞的会话，sid=10是被阻塞的会话

---如果可以确定产生阻塞的会话没用，可以手动杀掉
select sid,serial# from v$session where sid=391;
alter system kill session '391,34';
集群
alter system kill session '391,34,@1';
如果：v$session.status = 'KILLED'

查询spid:
SELECT s.sid, s.serial#, p.spid
FROM v$session s,v$process p
WHERE s.paddr = p.addr
/

ps -ef|grep <spid> would give something like:
(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

kill -9 <spid>
orakill orcl 12345 Ps：这里要注意的是kill OS进程是在服务端操作，而不是你程序所在客户机。

###死锁
---1 查找产生阻塞会话的sid和serial#
select sid, serial#
  from v$session
 where sid in (select holdsid
                 from (select a.sid   holdsid,
                              b.sid   waitsid,
                              a.type,
                              a.id1,
                              a.id2,
                              a.ctime
                         from v$lock a, v$lock b
                        where a.id1 = b.id1
                          and a.id2 = b.id2
                          and a.block = 1and b.block = 0));

---2 kill会话(写入上面查到的sid和serial#)
alter system kill session 'SID,SERIAL#'; 
---3 
select a.spid, b.sid, b.serial#, b.username
  from v$process a, v$session b
 where a.addr = b.paddr
   and b.status = 'KILLED';

---WINDOWS 
orakill sid spid 
---Linux 
ps -ef|grep pid 
kill -9 xxx 

###查询会话信息
select inst_id,
       sid,
       serial#,
       username,
       status,
       machine,
       program,
       last_call_et
  from gv$session
 where machine in ('xxx')
   and status = 'INACTIVE'
   and type = 'USER';

###生成手动终止指定会话的SQL，根据实际情况之下kill_session列语句
select inst_id,
       sid,
       serial#,
       username,
       status,
       machine,
       program,
       last_call_et,
       'alter system kill session ' || '''' || sid || ',' || serial# || ',' || '@' ||
       inst_id || '''' || ' immediate;' as kill_session
  from gv$session
 where machine in ('xxx-db01', 'xxx-db02')
   and status = 'INACTIVE'
   and type = 'USER';
 
例如执行：
alter system kill session '1052,19,@1' immediate;
alter system kill session '1726,9,@1' immediate;
......

SELECT s.sid, s.serial#, p.spid
FROM v$session s,v$process p
WHERE s.paddr = p.addr
/

set line 300
set pagesize 100
col RESOURCE_NAME for a35
col INITIAL_ALLOCATION for a20
col LIMIT_VALUE for a20
select * from v$resource_limit;

xxx操作系统下查看当前来自xxxIP的连接
netstat -ant|grep 1521|grep 10.100.1.80
netstat -ant|grep 1521|grep 10.100.1.81
netstat -ant|grep 1521|grep 10.100.1.82
netstat -ant|grep 1521|grep 10.100.1.83
netstat -ant|grep 1521|grep 10.100.1.84

xx操作系统下查看历史来自分行大前置IP的连接时间和连接次数
cd oracle/grid/diag/tnslsnr/xxx-db02/listener/trace
grep "HOST=.*establish.*\* 0" listener.log | awk -F'*' '{match($3,/[0-9]+\.[0-9]+\.[0-9]+\.[0-9]+/); ip = substr($3,RSTART,RLENGTH);cnt[ip]+=1;last[ip]=$1;}END {for (i in cnt) printf "%-16s %9s %19s\n",i,cnt[i],last[i];}' | sort -k 1|grep 10.100.1.8
例如：
10.100.1.80              2 28-JAN-2021 19:08:45

查看监听连接明细
tail -300000 listener.log|grep 10.100.1.8

###session 
10G 
sessions=1.1*processes+5
11gR2
Default value
Derived: (1.5 * PROCESSES) + 22

set linesize 180
col username for a15
col owner for a15
col OBJECT_NAME for a30
col SPID for a10
--查询某个会话的锁
select /*+rule*/SESSION_ID,OBJECT_ID,ORACLE_USERNAME,OS_USER_NAME,PROCESS,LOCKED_MODE from gv$locked_object where session_id=&sid;
--查询TMTX锁
select /*+rule*/* from v$lock where ctime >100 and type in ('TX','TM') order by 3,9;
--查询数据库中的锁
select /*+rule*/s.sid,p.spid,l.type,round(max(l.ctime)/60,0) lock_min,s.sql_id,s.USERNAME,b.owner,b.object_type,b.object_name from v$session s, v$process p,v$lock l,v$locked_object o,dba_objects b where  o.SESSION_ID=s.sid and s.sid=l.sid and o.OBJECT_ID=b.OBJECT_ID and s.paddr = p.addr and l.ctime >100 and l.type in ('TX','TM','FB') group by s.sid,p.spid,l.type,s.sql_id,s.USERNAME,b.owner,b.object_type,b.object_name order by 9,1,3,4;

--查看会话突增情况：
set lin 200 pages 200
select to_char(LOGON_TIME,'yyyymmdd hh24:mi') LOGON,inst_id,username,count(*) from gv$session where LOGON_TIME>sysdate-0.1 group by to_char(LOGON_TIME,'yyyymmdd hh24:mi'),inst_id,username order by to_char(LOGON_TIME,'yyyymmdd hh24:mi') desc;

set lin 200 pages 200
select inst_id,username,machine,sql_id,count(*) from gv$session where LOGON_TIME>sysdate-0.1 group by to_char(LOGON_TIME,'yyyymmdd hh24:mi'),inst_id,username order by to_char(LOGON_TIME,'yyyymmdd hh24:mi') desc;

set lin 200 pages 200
select inst_id,username,machine,sql_id,count(*) from gv$session  group by inst_id,username,machine,sql_id order by count(*) desc;

select inst_id,username,machine,prev_sql_id,count(*) from gv$session  group by inst_id,username,machine,prev_sql_id order by count(*) desc;

--节点会话分布：
 select inst_id,count(*) from gv$session group by inst_id;

--查看实例，会话分布
col machine for a20
col program for a40
set lin 200 pages 200

select username,machine,program,count(*) from v$session group by username,machine,program order by 4;

select username,machine,program,count(*) from v$session where program like 'oracle@gdytj-db1 (P0%' group by username,machine,program order by 4;

LOGMNR
1.需要添加补充日志
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
2.明确需要挖掘的归档
SELECT NAME FROM V$ARCHIVED_LOG
WHERE FIRST_TIME = (SELECT MAX(FIRST_TIME) FROM V$ARCHIVED_LOG);
+DATA/orcl/archivelog/2018_09_20/thread_1_seq_6.313.987329093
3.加入挖掘列表
EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
LOGFILENAME => '+DATA/orcl/archivelog/2018_09_20/thread_1_seq_7.314.987330717', -
OPTIONS => DBMS_LOGMNR.NEW);
4.开始挖掘
EXECUTE DBMS_LOGMNR.START_LOGMNR( -
 OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
5.查询挖掘结果
desc V$LOGMNR_CONTENTS
select count(*) from 
SELECT username AS USR, (XIDUSN || '.' || XIDSLT || '.' ||  XIDSQN) AS XID, 
SQL_REDO, SQL_UNDO FROM V$LOGMNR_CONTENTS WHERE username IN ('TEST');
6.关闭挖掘
EXECUTE DBMS_LOGMNR.END_LOGMNR();
7.其他挖掘参数命令
只显示提交的数据
EXECUTE DBMS_LOGMNR.START_LOGMNR( -
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
DBMS_LOGMNR.COMMITTED_DATA_ONLY); 
格式化显示语句，使语句更可读
EXECUTE DBMS_LOGMNR.START_LOGMNR(-
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
    DBMS_LOGMNR.COMMITTED_DATA_ONLY + -
    DBMS_LOGMNR.PRINT_PRETTY_SQL);
8.构建dic字典
输出到文本
alter system set utl_file_dir='/home/oracle/scripts' scope=spfile;
exec dbms_logmnr_d.build(dictionary_filename=>'orcl.dic',dictionary_location=>'/home/oracle/scripts',options=>dbms_logmnr_d.STORE_IN_FLAT_FILE);
输出到归档
exec DBMS_LOGMNR_D.BUILD(dictionary_filename=>NULL,dictionary_location=>NULL,options=>dbms_logmnr_d.STORE_IN_REDO_LOGS);
查询包含LOGMNR字典的日志文件：
v$archived_log的dictionary_begin和dictionary_end列

###无数据字典logminer
配置异机恢复库
启动测试数据库1用于恢复归档，恢复控制文件，将数据库启动到mount
rman 
restore controlfile from '<controlfile file dir>';
alter database mount;

根据时间段确定需要挖的归档日志
select RECID,THREAD#,SEQUENCE#,
to_char(first_time,'yyyymmdd hh24:mi:ss'),
to_char(NEXT_TIME,'yyyymmdd hh24:mi:ss'),
to_char(COMPLETION_TIME,'yyyymmdd hh24:mi:ss')
from v$archived_log 
where first_time between to_date('20181228','yyyymmdd') and to_date('20181229','yyyymmdd') 
and COMPLETION_TIME between to_date('20181228','yyyymmdd') and to_date('20181229','yyyymmdd') 
order by 3,4;

根据thread和seq# 来restore归档日志
run{
allocate channel ch01 type 'sbt_tape';
send 'xxxxxx';
set archivelog destination to '/oracle11/lylog/20190115_logminer';
restore archivelog sequence 4252 thread 2;
restore archivelog sequence 4333 thread 1;
}

启动测试数据库2
用于挖掘日志
检查数据库补充日志
select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI from v$database;
开启数据库补充日志
alter database add supplemental log data;

添加归档文件
exec dbms_logmnr.add_logfile('/oracle11/lylog/20190115_logminer/1_4333_981803066.dbf',dbms_logmnr.new);
exec dbms_logmnr.add_logfile('/oracle11/lylog/20190115_logminer/2_4252_981803066.dbf',dbms_logmnr.addfile);
检查归档日志是否添加
set line 200
col filename for a80
select log_id,filename,status from v$logmnr_logs;

挖掘日志
exec DBMS_LOGMNR.START_LOGMNR(DictFileName=>'',Options=>0);

查询挖掘结果
set line 150 pages 20000
col username for a20
col sql_redo for a80
spool oracle11/lylog/20190115_logminer/logminer_1.log
select username,to_char(start_timestamp,'yyyymmdd hh24:mi:ss'),SQL_REDO from v$logmnr_contents where upper(sql_redo) like '%TRUNCATE %';
select username,to_char(start_timestamp,'yyyymmdd hh24:mi:ss'),SQL_REDO from v$logmnr_contents where upper(sql_redo) like '%GRANT %';

本次logminer需求是要挖掘到日志中的grant和truncate语句，所以条件如上述，实际使用需根据实际需要修改。

附注：
根据下方MOS
How to Setup LogMiner (文档 ID 111886.1)
LogMiner Dictionary Build Procedure Fails With ORA-01308 in 12.2 (文档 ID 2277747.1) 

12.2数据库推荐使用directory替换原来的utl_file_dir参数。
create directory logminer as '/oracle/soft/arch';
execute dbms_logmnr_d.build(dictionary_filename =>'dict.ora',dictionary_location =>'LOGMINER');

--11g
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LogFileName=>'/u01/app/oracle/archive/1_5673_863636484.dbf',Options=>dbms_logmnr.new);
SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
SQL> create table test1 as select * from v$logmnr_contents;
SQL> exec dbms_logmnr.end_logmnr;
---查看归档大小及目录名
select block_size * blocks  1024  1024 "size(M)",
name,
first_time,
next_time,
creator
from v$archived_log a
order by first_time desc;
---查看归档中执行次数多的SQL
select count(*),substr(sql_redo,1,100) from test.test1 group by substr(sql_redo,1,100) order by 1 desc;
--10g
SQL> exec dbms_logmnr.add_logfile('/opt/oracle/oradata/mmstest/redo02.log',dbms_logmnr.new);
SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
SQL> exec dbms_logmnr.add_logfile('/u01/app/oracle/oradata/ogg1/redo02.log',dbms_logmnr.new);
/
要生成数据字典文件，首先要修改一个utl_file_dir参数，修改为*或者想存放数据字典文件位置的目录：
SQL> show parameter utl_file_dir
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir                         string

SQL> alter system set utl_file_dir='*' scope=spfile;
SQL> shutdown immediate
SQL> startup

---
alter system set utl_file_dir='*' scope=spfile ;

3、生成数据字典文件
SQL> show parameter utl_file_dir
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir                         string      *

生成数据字典文件dict20150822.chen到/home/oracle/chen目录下
SQL> EXECUTE dbms_logmnr_d.build(dictionary_filename => 'dict20150822.chen',dictionary_location => '/home/oracle/chen');
PL/SQL procedure successfully completed.

4、可以先设置使用的表空间
SQL> EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE('test')  
PL/SQL procedure successfully completed.

5、填加要分析的日志文件
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LogFileName=>'/orabak/testarch/1_89802_640266118.dbf',Options=>dbms_logmnr.new);
PL/SQL procedure successfully completed.

6、可以继续填加，用dbms_logmnr.removefile可以删除
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LogFileName=>'/orabak/testarch/1_89807_640266118.dbf',Options=>dbms_logmnr.addfile);
PL/SQL procedure successfully completed.

7、开始分析日志
SQL> execute dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/chen/dict20150822.chen');
PL/SQL procedure successfully completed.

提取特定时间的日志：
dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/dict20150822.chen',
starttime=>to_date('2009-06-24 09:30:00','YYYY-MM-DD HH24:MI:SS'),
endtime=>to_date('2009-06-24 12:00:59','YYYY-MM-DD HH24:MI:SS'))
/*
使用在线数据字典（数据库版本低于10g或者logmner库和源库是同一个库时可以使用在线数据字典）：
SQL> EXEC DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
*/
8、查看结果
select timestamp,username,session#,sql_redo,operation from v$logmnr_contents;

9、结束分析，释放PGA内存资源
SQL> exec dbms_logmnr.end_logmnr;
注意，v$logmnr_contents内容保存了日志的内容，只在当前会话有效，如果想长期保存分析，可以在当前会话用create table tablename as select * from v$logmnr_contents语句来持久保存。
/

select name,first_time,next_time from v$archived_log where deleted='NO' and to_char(first_time,'yyyymmdd')= '20170518';

select name,first_time,next_time from v$archived_log where deleted='NO' and to_char(first_time,'yyyymmdd')= '20170518';
as of timestamp (systimestamp - interval '12' day);
(systimestamp - interval '2' minute)
(systimestamp - interval '120' second);
(systimestamp - interval '12' hour);

---离线分析---
begin
  sys.dbms_logmnr.add_logfile(LogFileName => '/picclife1/arch/log1_233013_789829078.arc',
                              Options     => sys.dbms_logmnr.new);
  sys.dbms_logmnr.add_logfile(LogFileName => '/picclife1/arch/log1_233014_789829078.arc',
                              Options     => sys.dbms_logmnr.addfile);
end;

begin
  sys.dbms_logmnr.start_logmnr(options=>SYS.DBMS_LOGMNR.SKIP_CORRUPTION, startscn=>15805310267810);
end;

  select scn,xid, operation,sql_redo, data_obj#,SEG_NAME,SEG_OWNER,data_objv#,status, row_id, rs_id,ssn, csf,rollback,
     rbasqn,RBABLK,rbabyte,pxid,OPERATION_code, thread#,table_name, seg_type,info from v$logmnr_contents WHERE SEG_NAME='OBJ# 243664'
  
-------------- 
create table test0627 as select level as id from dual connect by level<=1000;
select * from test0627;
select * from v$log; 
select * from v$logfile; 
alter system switch logfile;
alter system checkpoint;
select * from v$log; ---CURRENT  group 2
select * from v$logfile; ---D:\APP\ADMINISTRATOR\ORADATA\EASTEST\REDO02.LOG

truncate table test0627; ---2018-06-27 11:39

---添加分析的日志文件
begin
   DBMS_LOGMNR.ADD_LOGFILE(LogFileName=>'D:\APP\ADMINISTRATOR\ORADATA\EASTEST\REDO02.LOG',Options=>dbms_logmnr.new);
   end;
---开始分析日志
begin
  dbms_logmnr.start_logmnr(options => dbms_logmnr.dict_from_online_catalog);
end;

create table test0627_log as select * from v$logmnr_contents;

begin
  dbms_logmnr.end_logmnr;
end;

select * from test0627_log where lower(sql_redo) like '%test0627%';
--------------------