优化常用脚本

top
iostat -xm s 1
nmon
ps-T -p **
perf top 
perf list hw|sw -- hard ware soft ware
perf top -e cpu-clock --cpu 高时查看
perf top -e block:block_rq_issue--io 高时查看
strace pid 或者命令 --查看进程与内核交互信息
ldd --查看依赖库
临时关闭swap区 
swapoff -a
永久关闭swap区
vi /etc/sysctl.conf
# 修改 vm.swappiness 的修改为 0
vm.swappiness=0
sysctl -p                                            # 使配置生效

--堆栈分析

--常用系统表（V$IFUN，V$IFUN_arg,V$HINT_INI_INFO）：
select * from v$dynamic_tables WHERE regexp_like(name,'PROC|SESS') =1;
--动态性能视图v$sysstat（查看读写分离是否生效，查看各服务器select的个数）
select * from v$sysstat  where name like '%statement%' --select statements
--收缩临时表空间到size  单位MB
SP_TRUNC_TS_FILE(3,0,SIZE)  
--监控会话数、内存（在数据库安装目录bin下）
while true
do
a=`./disql SYSDBA/SYSDBA:5236 -e "select count(*),state from v\\$sessions group by state order  by state" | tail -n 2`
b=`free -m`
c=`date +%F%H%M%S`
echo $c" "$b" | "$a >>`date +%F`.sess.log
sleep 1
done
--### 监控dmserver的内存\cpu变化情况（2s收集一次）
for rs in {1..1000};do a=`date;top -bc -n 1|grep dmserver|grep -v grep`;echo $a;sleep 2;done | tee -a vm.`date +%F`.log
### 监控服务器的io情况(其中sdb对应替换成数据磁盘)
iostat -t -k -x 2 sdb | tee -a stat.`date +%F`.log
--###检查swap区使用进程情况
 for file in /proc/*/status ; do awk '/VmSwap|Name/{printf $2 " " $3}END{ print " "}' $file;done|sort -k 2 -n -r |h

--收集索引列上没有统计信息的的列
with a as (
SELECT SCH.NAME OWNER,TAB.NAME TABLE_NAME,col.name COLUMN_NAME FROM SYSSTATS S,SYSOBJECTS SCH,SYSOBJECTS TAB,syscolumns col
WHERE SCH.ID=TAB.SCHID AND S.ID=TAB.ID and col.id=tab.id and s.colid=col.colid
--SELECT SCH.NAME,TAB.NAME,S.* FROM SYSSTATS S,SYSOBJECTS SCH,SYSOBJECTS TAB
--WHERE SCH.ID=TAB.SCHID AND S.ID=TAB.ID 
),B AS(
select INDEX_OWNER OWNER,TABLE_NAME,COLUMN_NAME from -- dba_tab_columns
ALL_IND_COLUMNS
 WHERE INDEX_OWNER NOT IN ('CTISYS',
'SYS',
'SYSAUDITOR',
'SYSDBA',
'SYSJOB'
) -- AND DATA_LENGTH<500
),C AS(
select * from b MINUS SELECT * FROM a)
SELECt 'STAT 100 ON '||OWNER||'.'||TABLE_NAME||' ("'||COLUMN_NAME||'");' FROM C WHERE TABLE_NAME not LIKE '%_DELL'  

--查统计信息
select c.name,b.name,(select name from SYS.SYSCOLUMNS where colid=a.colid and id=a.id) as col_name,a.T_FLAG,blevel,n_leaf_pages,n_leaf_used_pages,a.N_DISTINCT,a.N_NULL,a.LAST_GATHERED,
'call dbms_stats.gather_table_stats('''||c.name||''','''||b.name||''',null,100,TRUE,''FOR ALL COLUMNS SIZE AUTO'');' as tb_gather,
'call DBMS_STATS.GATHER_INDEX_STATS('''||c.name||''','''||b.name||''');'as ix_gather
from sysstats a  left join sysobjects b on a.id=b.id
left join sysobjects c on b.schid=c.id
where  c.name='NSZSJ' --schema
and b.name in('T1','IX_T1_1')--tablename or index

--批量生成统计信息脚本
select 'DBMS_STATS.GATHER_SCHEMA_STATS('''||NAME||''',100,TRUE,''FOR ALL COLUMNS SIZE AUTO'');' from SYS.SYSOBJECTS where TYPE$='SCH';
--如何正确的第一次更新统计信息
sp_set_SESSION_para_value(1,'HAGR_HASH_SIZE',10000000);
sp_set_SESSION_para_value(1,'HAGR_BUF_SIZE',15000);
--上面两个语句，如果执行报错的话，可以不执行，跳过这两个sp_set的设置即可。
select 'DBMS_STATS.GATHER_SCHEMA_STATS('''||username||''',100,TRUE,''FOR ALL COLUMNS SIZE AUTO'');' 
from all_users;
--这里我们一般选择需要收集的用户对应的行，进行执行即可。
或者
begin
for rs in (select 'sf_set_SESSION_para_value(''HAGR_HASH_SIZE'',(select cast(
case when max(table_rowcount(owner,table_name))<=(select max_value from v$dm_ini 
where para_Name=''HAGR_HASH_SIZE'') and max(table_rowcount(owner,table_name))>=(
select min_value from v$dm_ini where para_Name=''HAGR_HASH_SIZE'')  then 
max(table_rowcount(owner,table_name)) when max(table_rowcount(owner,table_name))<(
select min_value from v$dm_ini where para_Name=''HAGR_HASH_SIZE'') then
 (select min_value from v$dm_ini where para_Name=''HAGR_HASH_SIZE'') else 
 (select max_value from v$dm_ini where para_Name=''HAGR_HASH_SIZE'') end as bigint) 
 from dba_tables where owner='''||NAME||'''));'
sql1,'
DBMS_STATS.GATHER_SCHEMA_STATS('''||NAME||''',100,TRUE,''FOR ALL COLUMNS SIZE AUTO'');' 
sql2
 from SYS.SYSOBJECTS where TYPE$='SCH' ) loop

execute  immediate rs.sql1;
execute  immediate rs.sql2;

end loop;
end;

--通过管理包查看表上列、表、索引统计信息
CALL DBMS_STATS.COLUMN_STATS_SHOW('PERSON','ADDRESS','ADDRESSID');
CALL DBMS_STATS.TABLE_STATS_SHOW('PERSON','ADDRESS');
CALL DBMS_STATS.INDEX_STATS_SHOW('SYSDBA',UPPER('IND1'));
--收集指定用户下所有索引的统计信息：
call DBMS_STATS.GATHER_SCHEMA_STATS('usename',100,TRUE,'FOR ALL INDEXED SIZE AUTO');
--收集指定用户下某表统计信息：
call DBMS_STATS.GATHER_TABLE_STATS('username','table_name',null,100,TRUE,'FOR ALL COLUMNS SIZE AUTO');
--收集单个索引统计信息：
call DBMS_STATS.GATHER_INDEX_STATS('username','IDX_T2_X');
--收集某表某列的统计信息：
STAT 100 ON table_name(column_name);
--初始化查询sql中涉及到的表统计信息\表\列\整库\索引
call SP_SQL_STAT_INIT('select * from PERSON.ADDRESS');
call SP_tab_STAT_INIT('SCHNAME','TABNAME'); 
call SP_COL_STAT_INIT('SCHNAME','TABNAME','COLNAME'); 
call SP_DB_STAT_INIT(); 
call SP_index_STAT_INIT('PERSON','INDEX33555454',100);

--添加自动收集统计信息的任务（每天3点收集全库统计信息）
call SP_CREATE_JOB('statistics',1,0,'',0,0,'',0,'');
call SP_JOB_CONFIG_START('statistics');
call SP_ADD_JOB_STEP('statistics', 'statistics1', 3, '', 0, 0, 0, 0, NULL, 0);
call SP_ADD_JOB_SCHEDULE('statistics', 'statistics1', 1, 1, 1, 0, 0, '03:00:00', NULL, '2021-06-09 22:54:37', NULL, '');
call SP_JOB_CONFIG_COMMIT('statistics');

--例如：监控sql执行在1秒以上的SQL语句使用的内存（统计最近1w条V$SQL_STAT_HISTORY ）
SELECT "SESSID", MAX_MEM_USED||'KB',SQL_TXT FROM V$SQL_STAT order by MAX_MEM_USED DESC;
--系统启动起来执行时间最长的前20条sql、(最近执行时间较长保留1000条V$LONG_EXEC_SQLS)
select *
from V$SYSTEM_LONG_EXEC_SQLS
order by  EXEC_TIME desc
--开启会话级别监控
CALL SF_SET_SESSION_PARA_VALUE('MONITOR_SQL_EXEC’,1)；
CALL SF_SET_SESSION_PARA_VALUE(‘ENABLE_MONITOR’,1);
--开启服务级别监控
SP_SET_PARA_VALUE(1,'ENABLE_MONITOR',1);
SP_SET_PARA_VALUE(1,'MONITOR_SQL_EXEC',1);
--事件捕捉-
开启：
alter session 0 set events '10003 trace name context forever,level 1'--表示抓CSCN全表扫描的sql执行计划，level=3=1+2表示（CSCN+HASH）
level 支持1到15 含义如下:
LEVEL 1 CSCN
LEVEL 2 HASH JOIN
LEVEL 4 HAGR
LEVR 8 NEST LOOP
--
关闭
alter session 0 set events '10003 trace name context off'
--------------------------------查看缓存中的执行计划 - 存放在数据文件目录的trace文件夹-------------------------------------
select cache_item,sqlstr from v$cachepln where sqlstr like ‘%语句片段%’;
确认CACHE_ITEM号
通过TRACE
Alter session set events 'immediate trace name plndump,level ******';
--------------------------------------------------------------------
--建索引
create index "ix_1" on "RISENET"."OFFICE_WorkflowInstance"("WorkflowInstance_GUID","REGION_ID");
-- 清理某个表涉及到的所有执行计划缓存
方法1：select cache_item,sqlstr,'call sp_clear_plan_cache('||cache_item||');' from v$cachepln where sqlstr like '%**%';
方法2：
begin
for rs in
(
select * from v$cachepln where sqlstr like '%MES_PRC_LAY_ITEM%'
)
loop
execute immediate 'sp_clear_plan_cache(' ||rs.cache_item ||');';
end loop;
end;
--查看某个列有没有统计信息（结果为空表示无统计信息）
--create table sysdba.test1(v1 int);
with a as (select tab.id||'@'||col.colid FF,sch.name sch,tab.NAME tab,col.NAME col from
sysobjects sch,
sysobjects tab,
syscolumns col
WHERE
sch.id=tab.schid
and col.id=tab.id
),b (FF) as (
select st.id||'@'||st.colid from
sysstats st where st.colid != -1
)
SELECT A.*,B.* FROM A,B where A.FF=B.FF AND A.sch='SYSDBA' and A.tab='TEST1' and A.col='V1';

--STAT 100 ON sysdba.test1(V1);
--执行下面这两条语句，完成统计信息收集（第二条语句查询出来的结果，都是需要执行的sql；请在同一个窗口中执行，不要更换窗口tab页）
sf_set_SESSION_para_value('HAGR_HASH_SIZE',(select max(table_rowcount(owner,table_name)) from dba_tables));
-- DBMS_STATS.GATHER_SCHEMA_STATS('SCD_NBSEI',100,TRUE,'FOR ALL COLUMNS SIZE AUTO');
select 'DBMS_STATS.GATHER_SCHEMA_STATS('''||username||''',100,TRUE,''FOR ALL COLUMNS SIZE AUTO'');' from all_users where username not in ('SYS','SYSAUDITOR','SYSSSO');
/*
select 'DBMS_STATS.GATHER_SCHEMA_STATS('''||username||''',100,TRUE,''FOR ALL COLUMNS SIZE AUTO'');' wait_exec_sqls from all_users
where username not in ('SYS','SYSAUDITOR','SYSSSO');
--select * from v$dm_ini where regexp_like(PARA_name,'BUF|MEM|SIZE')
--select * from v$dm_ini where regexp_like(PARA_name,'HAGR')
*/
set autotrace trace 可快速定位统计信息是否失真
前提 ENABLE_MONITOR=1、MONITOR_SQL_EXEC=1开启
SQL> 
SP_SET_PARA_VALUE(1,'ENABLE_MONITOR',1);
SP_SET_PARA_VALUE(1,'MONITOR_SQL_EXEC',1);
SQL> set autotrace trace   
SQL> select * from "TEST"."TEST1" where id= 100;
call et(执行号)
SELECT N.NAME, TIME_USED, N_ENTER 
FROM V$SQL_NODE_NAME N, V$SQL_NODE_HISTORY H 
WHERE N.TYPE$ = H.TYPE$ AND EXEC_ID = 534 --执行号
order by 2 desc;

--实例中查询活动会话
--查询实例中活动会话使用 V$sessions 视图，V$sessions 会显示会话的具体信息，如执行的 SQL 语句、主库名、当前会话状态、用户名等等。
SELECT count(*) FROM v$sessions WHERE state='ACTIVE';
--获取完整sql
SELECT SYSDATE,
       SF_GET_SESSION_SQL (SESS_ID),
       sess_id,
       sess_seq,
       sql_text,
       state,
       seq_no,
       user_name,
       trx_id,
       create_time,
       clnt_ip
  FROM v$sessions
 WHERE state = 'ACTIVE';
 
--实例中锁查询
--锁机制用于管理对共享资源的并发访问。数据库中使用锁是为了支持对共享资源进行并发访问，与此同时还能提供数据完整和一致性。每个数据库都会存在锁，不过实现机制各不相同。
--死锁：当两个或者多个事务都在请求对方资源，等待其他事务释放锁从而产生等待环，则会产生死锁。
--从定义上我们不难看出锁是数据库中合理的存在，它保证了共享资源的并发访问和数据一致性而正常存在的。但是死锁确实不合理的存在，它会影响客户正常的体验。下面是实例中锁查询语句：
--kill 会话：sp_close_session(sess_id)
SELECT o.name, l.*
  FROM v$lock l, sysobjects o
 WHERE l.table_id = o.id AND blocked = 1;

WITH locks
     AS (SELECT o.name,
                l.*,
                s.sess_id,
                s.sql_text,
                s.clnt_ip,
                s.last_send_time
           FROM v$lock l, sysobjects o, v$sessions s
          WHERE l.table_id = o.id AND l.trx_id = s.trx_id),
     lock_tr
     AS (SELECT trx_id wt_trxid, tid blk_trxid
           FROM locks
          WHERE blocked = 1),
     res
     AS (SELECT SYSDATE stattime,
                t1.name,
                t1.sess_id wt_sessid,
                s.wt_trxid,
                t2.sess_id blk_sessid,
                s.blk_trxid,
                t2.clnt_ip,
                SF_GET_SESSION_SQL (t1.sess_id) fulsql,
                datediff (ss, t1.last_send_time, SYSDATE) ss,
                t1.sql_text wt_sql
           FROM lock_tr s, locks t1, locks t2
          WHERE     t1.ltype = 'OBJECT'
                AND t1.table_id <> 0
                AND t2.ltype = 'OBJECT'
                AND t2.table_id <> 0
                AND s.wt_trxid = t1.trx_id
                AND s.blk_trxid = t2.trx_id)
--select distinct clnt_ip from res;
SELECT DISTINCT wt_sql, clnt_ip, ss
  FROM res;
 
--实例中已执行未提交的 SQL 查询
SELECT t1.sql_text, t1.state, t1.trx_id,t1.sess_id,*
  FROM v$sessions t1, v$trx t2
 WHERE t1.trx_id = t2.id AND t1.state = 'IDLE' AND t2.status = 'ACTIVE';
 
--有事务未提交的表查询
SELECT b.object_name, c.sess_id, a.*
  FROM v$lock a, dba_objects b, v$sessions c
 WHERE a.table_id = b.object_id AND ltype = 'OBJECT' AND a.trx_id = c.trx_id;

--锁超时的表对应的会话
select a.*,b.NAME,c.SESS_ID 
from v$lock a
left join sysobjects b on b.ID=a.TABLE_ID
left join v$sessions c on a.TRX_ID=c.TRX_ID
where name='表名'
 
--长时间的 SQL 查询
SELECT t1.sql_text, t1.state, t1.trx_id,msg_status, t1.last_send_time,t1.last_recv_time,
datediff (ss, case when msg_status='SEND' then t1.last_send_time
    when  msg_status='RECIEVE' then t1.last_recv_time end 
, SYSDATE)
FROM v$sessions t1, v$trx t2
WHERE t1.trx_id = t2.id AND t1.state = 'IDLE' AND t2.status = 'ACTIVE'
order  by   datediff (ss, case when msg_status='SEND' then t1.last_send_time
    when  msg_status='RECIEVE' then t1.last_recv_time end 
, SYSDATE) desc;
--如果存在长时间没有返回结果得语句，首先通过 V$SESSIONS 确认语句处于活动状态，语句如下：
SELECT TRX_ID,* FROM v$sessions WHERE state='ACTIVE'
AND dbms_lob.substr(sf_get_session_sql(sess_id)) LIKE '%语句片段%'
 
--明确事务等待导致语句没有正常执行结束，语句如下：
SELECT * FROM v$trxwait WHERE id = 上述语句得到结果得 TRX_ID

--
--阻塞、等待
SELECT b.wait_time as wait_time_ms,a.sql_text as watingsql,c.sql_text as block_sql,c.clnt_ip,b.*
FROM v$sessions a join v$trxwait b on a.trx_id=b.id
 join v$sessions c on c.trx_id=b.wait_for_id
WHERE a.state='ACTIVE' 
order by b.wait_time desc 

-- 
/* 查询当前数据库中执行时间超过1秒的SQL */
SELECT * FROM (
SELECT SESS_ID,SQL_TEXT,DATEDIFF(SS,LAST_SEND_TIME,SYSDATE) SS,
       SF_GET_SESSION_SQL(SESS_ID) FULLSQL,clnt_ip
 FROM V$SESSIONS WHERE STATE='ACTIVE')
 WHERE SS>=1;

--开启慢日志
--开启慢日志捕捉
SP_SET_PARA_VALUE(1,'SVR_LOG',1);
--设置慢日志捕捉规则
ASYNC_FLUSH =1
MIN_EXEC_TIME=1000
SQL_TRACE_MASK  = 2:3:9:22:25:28

--配置生效
SP_REFRESH_SVR_LOG_CONFIG();
---
[SLOG_ALL]
    FILE_PATH    = ../log
    PART_STOR    = 0
    SWITCH_MODE  = 2 #根据文件大小切换 1-记录数量；2-文件大小；3-时间间隔
    SWITCH_LIMIT   = 128 #结合switch_mode 表达不同意思，switch_mode=2对应的是文件大小单位M
    ASYNC_FLUSH   = 1  #生产环境 异步
    FILE_NUM = 5
    ITEMS    = 0
    SQL_TRACE_MASK  = 2:3:9:22:25:28
    MIN_EXEC_TIME = 1000  #ms
    USER_MODE   = 0
    USERS =
--设置 SQL 过滤规则，只记录必要的 SQL，生产环境不要设成 1
-- 2 只记录 DML 语句    3 只记录 DDL 语句   22 记录绑定参数的语句
-- 25 记录 SQL 语句和它的执行时间    28 记录 SQL 语句绑定的参数信息
--9 ROLLBACK 类型语句（回滚）23 存在错误的语句（语法错误，语义分析错误等）
--24 是否需要记录执行语句

--hint 注入
开启
sp_set_para_value(1,'ENABLE_INJECT_HINT ',1);
Ø SQL 只能是语法正确的增删改查语句；
Ø SQL 会经过系统格式化，格式化之后的 SQL 和指定的规则名称必须全局唯一；
Ø HINT 一指定，则全局生效；
Ø系统检查 SQL 匹配时，必须是整条语句完全匹配，不能是语句中子查询匹配；
Ø可通过 SYSINJECTHINT 视图查看已指定的 SQL 语句和对应的 HINT。
--对指定SQL增加HINT
SF_INJECT_HINT('sql语句', '参数名(参数值)', '规则名', null,TRUE,TRUE);
--对指定 SQL 撤回已增加的 HINT
SF_DEINJECT_HINT('规则名');
--修改已指定 HINT 的规则属性
SF_ALTER_HINT('('规则名', 'STATUS', 'DISABLED');
/****************查询重写rewrite_equivalence******************/
--开启重写规则适用于某用户，该用户下执行的sql才会进行sql重写
call SP_USER_SET_AUTO_REWRITE_FLAG('用户名',1);
--声明重写规则（使用sql需要重写生效的用户执行）
call DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE(
'TEST_REWRITE',--规则名，确保唯一
'SELECT COUNT(*) FROM X1 where c2=?', --原始查询语句
'SELECT COUNT(*) FROM X2', --目标语句
FALSE,
'TEXT_MATCH' --文本匹配重写
);
--删除重写规则
--移除查询重写规则(清相关sql的查询缓存，否则还是执行之前的重写sql)
call  dbms_advanced_rewrite.DROP_REWRITE_EQUIVALENCE('TEST_REWRITE'); --规则名
--禁用、启用重写规则（如果不想删除重写规则，可临时禁用，想开启时再开启）
--禁用重写规则
call  dbms_advanced_rewrite.ALTER_REWRITE_EQUIVALENCE('TEST_REWRITE','DISABLED');
--启用重写规则
call  dbms_advanced_rewrite.ALTER_REWRITE_EQUIVALENCE('TEST_REWRITE','TEXT_MATCH');
--想知道现有的重写sql 可通过以下系统表查询到
select * from SYS_REWRITE_EQUIVALENCES;
--通过重写规则定位到缓存计划，清相关sql缓存计划
select 'SP_CLEAR_PLAN_CACHE('||cache_item||');',sqlstr 
from v$cachepln a
where exists(
select 1 from SYS_REWRITE_EQUIVALENCES where upper(source_stmt)=upper(a.sqlstr)
);
--统计信息导入导出
--1 创建一个专用表保存各表的统计信息：TEST模式下STAT_TEST表：
call dbms_stats.create_stat_table('TEST','STAT_TEST');

--2 导出某表的统计信息，将TEST.T1表的统计信息导入到TEST.STAT_TEST表中；
call dbms_stats.export_table_stats('TEST','T1',null,'STAT_TEST','',TRUE,'TEST');
--以上1、2参数是：导出统计信息的表所属模式，表 ；参数4和7分别表示导出的统计信息存放的表和所属模式，即，步骤1创建的表
--3.导入某表的统计信息：
call dbms_stats.import_table_stats('TEST','T1',null,'STAT_TEST','',TRUE,'TEST');  
索引创建原则
在区分度较高、调用频繁的列创建索引
组合索引，等值列在前，非等值列在后的原则
表连接过程中，使用频繁的列
可以消除排序的列上创建索引

call SF_INJECT_HINT('select distinct a.workflowinstance_guid, a.star_level
           from office_workflowinstanceactors a
          where a.actors_classify = 1 and a.Person_Guid=', 'USE_CVT_VAR', 'use_cvt_var_02', null,TRUE,TRUE);
常用hint提示
VIEW_PULLUP_FLAG
USE_INDEX_SKIP_SCAN
ADAPTIVE_NPLN_FLAG和OPT_MAX_ADAPTIVE_EXEC_TIMES
OPTIMIZER_OR_NBEXP
USE_CVT_VAR--驱动表记录较少，而另一侧计划较复杂的场景 exists（....复制查询）
--范例（V_OFFICE_FILE 表由6个表union后和1表关联的视图）：
select /*+USE_CVT_VAR*/count(1) from(
select distinct a.workflowinstance_guid, a.star_level
           from office_workflowinstanceactors a
          where a.actors_classify = 1 and a.Person_Guid='{AC1063FA-0000-0000-192D-143600000001}'
 ) c
        where exists
        (select w.workflowinstance_guid
           from office_workflowinstance w, V_OFFICE_FILE v
          where w.workflowinstance_guid = v.workflowinstance_guid
            and w.instancedeleted = 0
            and w.appguid in ('5', '6')
            and v.biaoti is not null
            and (w.InstanceStatus = 2 OR w.InstanceStatus = 3)
            and w.workflowinstance_guid = c.workflowinstance_guid        and v.jinjichengdu<>'平件');
---反例，如果使用变量替代导致二次回表查询太高，则用以下提示试试
/*+no_use_cvt_var ADAPTIVE_NPLN_FLAG(0)*/

--放大hash桶数
HAGR_DISTINCT_HASH_TABLE_SIZE，JOIN_HASH_SIZE、HAGR_HASH_SIZE
--临时表删除
    增加系统过程sp_tmp_table_clear(‘模式名’,’表名’);  删除临时表数据的同时清理掉数据页上的数据，类似于truncate，但不提交
优化器提示【减少中间结果集（内连接优于外连接），放大内存，复杂表达式改写成case函数判断，nest loop （左表集合小）、hash（连接表大） 】：
1、多表hash join +top语法较慢时,可用
TOP_DIS_HASH_FLAG(1)  OPTIMIZER_MODE(1)
2、语句含有top  + order by ，排序列上建索引（目的剔除sort），可用TOP_ORDER_OPT_FLAG(1)减少估算代价，达到改变执行计划目的
/*********    去除重复值优化**********/
1、多列更新慢update t1 set(c1,c2.....)=(select a,b,.....) ,提示：/*+MULTI_UPD_OPT_FLAG(1)*/ 不用plsql方式更新，采用spl2方式临时表方式，
【spl2和ntts2不同之处，不需向父节点传递数据，只记录了key和编号】
2、/*+PARTIAL_JOIN_EVALUATION_FLAG(1) OPTIMIZER_MODE(1)*/
例子：distinct   + 聚合函数  +连接的输出项来自同一个孩子（select 后面的字段来着同一孩子）--》将inner join 转半连接 减少结果集
/************子查询优化************/
1、/*+CNNTB_OPT_FLAG(0)*/ 0-不优化 2-prior 列上有索引 走嵌套循环连接，若无和0一样效果；1-走hash join
子集比较多且无法建立索引或者过滤不佳的情况下，可以考虑将CNNTB_OPT_FLAG
HINT 成1，通过HASH 的方式进行层次查询操作
/************表达式优化************/
1、or 表达式：
1.1   OR_NBEXP_CVT_CASE_WHEN_FLAG 将or表达式转为case when 函数
1.2  NBEXP_OPT_FLAG 将not语法+or语法转为case when
1.3  and +or  ：对or的处理 当可以拆分出的公共部分比较复杂时
我们可以考虑将COL1,COL2条件过滤后的结果作为一个临时表，避免各个分支上需要重复计算
这里涉及到两个HINT
OR_CVT_HTAB_FLAG 0表示公共部分不转换为临时表，1表示转换
USE_HTAB_FLAG  表示是否开启临时表功能，为0表示强制不使用
1.4 每一列上都存在索引，默认INI 的情况下，不会把查询拆成40个分支执行
这里需要HINT MAX_OPT_N_OR_BEXPS ，这个参数默认是7，表示最多处理7个分支
参数最大64 ,hint /*+max_opt_n_or_bexps(64)*/， 即可将该查询分成40个分支处理
真实业务中，更多情况下各个分支都可以通过同一条或者有限的几条索引进行过滤，在分支很多且
需要拆分的情况下，可以考虑该HINT
/************分区表优化************/
1、减少单次排序数据规模 /*+ ENABLE_PARTITION_WISE_OPT(1) */ 

/********子查询优化************/
 /*+ ENABLE_DIST_IN_SUBQUERY_OPT(1) */
一般可语句改下 子查询的,仅在IN子查询的连接列与外层列属于同一张表，且列具有UNIQUE属性时，才能进行去掉子查询的优化
/*************是否使用SPL2方式对相关子查询去除相关性******************/
ENABLE_RQ_TO_NONREF_SPL参数提供了一种行级处理方式以实现去除相关子查询的相关性，这种处理方式将相关列转为变量VAR，简化了执行计划。
与普通的子查询平坦化机制不同，它不需要生成临时结果集
ENABLE_RQ_TO_NONREF_SPL=1时，对查询项中出现的相关子查询表达式进行优化处理；
ENABLE_RQ_TO_NONREF_SPL=2时，对查询项和WHERE表达式中出现的相关子查询表达式进行优化处理。
范例：
select  col1,(select col1 from aa where aa.col1=a.col1) as co1_aa
from a join b on a.col2=b.col2
where EXISTS in(
(select 1 from bb where bb.col1=b.col1) 
)
/*************操作符ACTRL 备用计划******************/
看到计划中出现该操作符，看参数是否优化到位，ADAPTIVE_NPLN_FLAG默认是3， 生产环境设为0，不启动自适应计划
/*************视图、子查询条件下放VIEW_FILTER_MERGING(1)******************/
注意：如果视图中有distinct ，过滤条件是复杂的表达式计算，可以不考虑视图下放
/***************ENABLE_INDEX_FILTER  先通过索引过滤后再二次返表查数据（减小返表查的结果集）*********************/
select *
  from (select  
 smsdetail0_.sms_Guid              as sms_Guid1_1_,
               smsdetail0_.approve_Guid          as approve_2_1_,
               smsdetail0_.bureau_Guid           as bureau_G3_1_,
               smsdetail0_.bureau_Name           as bureau_N4_1_,
               smsdetail0_.creator_Id            as creator_5_1_,
               smsdetail0_.deleteByReceived      as deleteBy6_1_,
               smsdetail0_.deleteBySender        as deleteBy7_1_,
               smsdetail0_.employee_Guid         as employee8_1_,
               smsdetail0_.mobile                as mobile9_1_,
               smsdetail0_.module_Id             as module_10_1_,
               smsdetail0_.msgid                 as msgid11_1_,
               smsdetail0_.ori_Tel               as ori_Tel12_1_,
               smsdetail0_.receiver              as receive13_1_,
               smsdetail0_.sendTime              as sendTim14_1_,
               smsdetail0_.sms                   as sms15_1_,
               smsdetail0_.sms_Split_Num         as sms_Spl16_1_,
               smsdetail0_.status                as status17_1_,
               smsdetail0_.workflow_InstanceGuid as workflo18_1_
          from Sms_Detail smsdetail0_
         where smsdetail0_.employee_Guid = '{08A8D6CF-76DD-B343-A5D5-5B2B6082425E}'
           and (smsdetail0_.mobile like '%18926031284%')
           and smsdetail0_.deleteBySender = '0'
order by smsdetail0_.sendTime desc
         )
 where num <= 10
/*************索引******************/
/*+INDEX(T1, IDX_T1_ID) */
/************in条件转成exist**********/
MULTI_IN_CVT_EXISTS(1)
/*************索引******************/
/*+no_INDEX(T1, IDX_T1_ID) */

/*************改变连接方式******************/
/*+PHC_MODE_ENFORCE(2) */

/*+ENABLE_IGNORE_PURGE_REC(0)*/ （报错代码-7120 回滚版本太旧 找不到记录 忽略跳过该报错）
--查看sql是否hash刷盘，考虑是否放大单个查询的hj_buf_size
select   * from   v$mtab_used_history;
----清理动态视图记录
SP_DYNAMIC_VIEW_DATA_CLEAR('V$MTAB_USED_HISTORY');

--批量改参数
select 'call sp_set_para_value(2,'''||para_name||''','||para_value||');',* from v$dm_ini where para_name in(
'PARALLEL_PURGE_FLAG',
'FAST_POOL_PAGES',
'FAST_ROLL_PAGES',
'ENABLE_FREQROOTS')

--查表记录数、大小
select table_rowcount(owner,table_name) rowsNum,
table_used_space(owner,table_name)/1024.0/1024*page sizeMb,* 
from dba_tables 
where owner='EXOA' order by rowsNum desc;
--查函数索引
select
        cast(dbms_metadata.get_ddl('INDEX', OBJ.NAME, sch.name) AS VARCHAR) DDL,
        DM_BIT_TEST(XTYPE, 2)                                                  ,
        sch.name                                                               ,
        obj.name                                                               ,
        *
from
        SYSINDEXES idx,
        SYSOBJECTS sch,
        SYSOBJECTS obj
where
        sch.id               =obj.schid
    and obj.id               =idx.id
    and DM_BIT_TEST(XTYPE, 2)=1;
--获取建表脚本
select
        cast(dbms_metadata.get_ddl('TABLE', table_NAME, owner) AS VARCHAR) DDL,
        *
from
        dba_tables
 where table_name like '%FLOW_APPLY%'
--根据文件号、页号 定位表
select * from dba_objects where object_id =
(select obj_id from V$SEGMENT_INFOS 
where seg_id=(select SF_PAGE_GET_SEGID(5, 0, 1740764)));
/******************归档日志挖掘分析********************/
前提：开启归档+附加日志
select * from v$dm_ini where para_name in('ARCH_INI','RLOG_APPEND_LOGIC');
alter database mount;
alter system set 'ARCH_INI'=1 both;
alter database open;
alter system set 'RLOG_APPEND_LOGIC'=1 both;
--查看归档日志配置及文件
select * from v$dm_arch_ini;
select top 100 first_time,name from v$archived_log where first_time between '2021-11-17' and '2021-11-30' order by first_time desc;
--将需要挖掘的归档文件添加到包中
call dbms_logmnr.add_logfile ('/dmdata/DAMENG/arch/ARCHIVE_LOCAL1_0x3AB743C0[0]_2021-11-17_11-35-31.log');
--指定开始和结束时间进行分析
call dbms_logmnr.start_logmnr(options=>2130, starttime=>'2021-11-17 15:35:00' ,endtime=>'2021-11-17 15:55'); 
call dbms_logmnr.start_logmnr(options=>2130); 
--查询分析结果，我们需要的数据存放在SQL_REDO列
SELECT sql_redo,sql_undo,TABLE_NAME,* FROM V$LOGMNR_CONTENTS WHERE TABLE_NAME = upper('office_oa_business') and sql_redo like '%7F000001-0000-0000-2BD0-36D20000402D%';
--找到数据后结束挖掘
call DBMS_LOGMNR.END_LOGMNR();

--以上挖掘sql需要在同一窗口执行
/********************disql使用问题***************************/
--disql 显示结果过长被截断，可通过以下设置，让数据显示全
set  long  32767
--忽略写入的数据中变量标识符（执行insert sql之前设置）
set define off
 /******运维常用脚本*******/

CREATE OR REPLACE
        FUNCTION sysdba.base_fun_001
                (
                        F NUMBER)
                RETURN VARCHAR
        IS
        BEGIN
                IF F>60 AND F<3600 THEN
                        RETURN CAST(F/60 AS INT)||'分钟'||mod(f, 60)||'秒';
                elseif f>3600 THEN
                        return cast(f/3600 as int)||'小时'||cast(mod(f, 3600)/60 as int)||'分钟'||mod(f, 60)||'秒';
                else
                        RETURN f||'秒';
                end if;
        END;
        
create or replace
procedure sysdba.gs
as
begin
        select
                'sp_close_session('
                ||sess_id
                ||');'                                     ,
                thrd_id                                    ,
                sess_id                                    ,
                trx_Id                                     ,
                datediff(ss, last_recv_time, sysdate) MsgTRs, --已执行时间 s
                to_char(sf_get_session_sql(sess_id)) "SQL" , --完整sql
                curr_sch                                   ,
                user_name                                  ,
                clnt_host                                  ,
                clnt_ip                                    ,
                clnt_type                                  ,
                osname                                     ,
                left(last_send_time, 19)
        from
                v$sessions
        where
                state='ACTIVE'
        order by
                5 desc;
end;
/

create or replace
procedure sysdba.gm1
as -- create or replace public synonym gm1 for sysdba.gm1;
begin
        select
                COUNT(              *) over(partition by name order by 1) 类型总数            ,
                trunc(avg(total_size/1024.0/1024) over(partition by name order by 1)) 平均总 ,
                trunc(avg(data_size /1024.0/1024) over(partition by name order by 1)) 平均在用,
                trunc(org_size      /1024.0/1024) 初始                                      ,
                trunc(data_size     /1024.0/1024) 在用                                      ,
                trunc(total_size    /1024.0/1024) 总的                                      ,
                trunc(target_size   /1024.0/1024) 水位                                      ,
                                    *
        from
                v$mem_pool
        where
                regexp_like(name, 'VIRTUAL|SESS');
end;
/

create or replace
procedure sysdba.gm2
as -- create or replace public synonym gm2 for sysdba.gm2;
begin
        select
                regexp_replace(name,'[0-9]'),count(*),
                trunc(sum((org_size    /1024.0/1024))) 初始,
                trunc(sum((data_size   /1024.0/1024))) 在用,
                trunc(sum((total_size  /1024.0/1024))) 总的,
                trunc(sum((target_size /1024.0/1024))) 水位
        from
                v$mem_pool
        group by
                regexp_replace(name,'[0-9]') order by 总的 desc;
end;
/

create or replace
procedure sysdba.gmmc
as -- create or replace public synonym gmmc for sysdba.gmmc;
-- 如果运行报错的话，把 listagg(distinct name,' $ ')within  中的distinct删除即可；最近的dmserver是支持这个语法了，但是以前的dmserver不支持
begin
        select
                creator 会话的线程号  ,count(*)                      ,
                trunc(sum((org_size    /1024.0/1024))) 初始,
                trunc(sum((data_size   /1024.0/1024))) 在用,
                trunc(sum((total_size  /1024.0/1024))) 总的,
                trunc(sum((target_size /1024.0/1024))) 水位,
                (select sql_text from v$sessions where thrd_id=CREATOR) 最近执行,
                listagg(distinct name,' $ ')within group(order by name) 涉及的内存池类型
        from
                v$mem_pool
        group by
                "V$MEM_POOL".CREATOR
        having
                count(*)<=4 order by 3 desc;
end;
/

create or replace
procedure sysdba.gbuf
as -- create or replace public synonym gbuf for sysdba.gbuf;
begin
        select
                name                                      ,
                count(*)                                  ,
                avg(rat_hit) 命中率75                        ,
                trunc(sum((n_pages /1024.0/1024*page))) 大小,
                trunc(sum((free    /1024.0/1024*page))) 空闲,
                trunc(sum((n_dirty /1024.0/1024*page))) 脏页,
                trunc(sum((n_clear /1024.0/1024*page))) 零页
                -- select *
        from
                v$bufferpool
        group by
                name;
end;
/

create or replace
procedure sysdba.gLOCK0(tbname_ varchar(400))
as -- create or replace public synonym gLOCK0 for sysdba.gLOCK0;
begin
with
        temp1 as
        (
                select
                        (
                                select
                                        listagg(sch.name
                                        ||'.'
                                        ||tab.name, char(10)) within group(
                                order by
                                        tab.id)
                                from
                                        sysobjects sch,
                                        sysobjects tab
                                where
                                        tab.schid=sch.id
                                    and tab.id in
                                        (
                                                select table_id from v$lock where trx_id=f.trx_id and trx_id!=0
                                        )
                        )
                        tabs,
                        *
                from
                        v$sessions f
                where
                        trx_id in
                        (
                                select id from v$trx where status='ACTIVE'
                        )
                ORDER BY
                        LAST_RECV_TIME
        )
select
        tabs 涉及到那些表上的锁,
        sess_id 会话id  ,
        'sp_close_session('
        ||sess_id
        ||');' close会话                                   ,
        last_send_time 操作时间                              ,
        cast(sf_get_session_sql(sess_id) as varchar) 最近语句,
        state 当前是否执行                                     ,
        curr_sch 用户名                                     ,
        clnt_host                                        ,
        appname                                          ,
        clnt_ip                                          ,
        osname                                           ,
        *
from
        temp1
where
        tabs is not null  and (tbname_ is null or regexp_like(tabs,tbname_)=1 )
order by
        操作时间;
end;
/

create or replace
procedure sysdba.gLOCK1
as -- create or replace public synonym gLOCK0 for sysdba.gLOCK1;
begin
        select
                (
                        select blocked from v$lock where trx_id=a.trx_id and blocked=1 limit 1
                )
                是否阻塞                                ,
                (sf_get_session_sql(sess_id)) 完整的sQL,
                (
                        select
                                ins_cnt
                                ||' '
                                ||del_cnt
                                ||' '
                                ||upd_cnt idu
                        from
                                v$trx
                        where
                                id    =a.trx_id
                            and trx_id>0
                )
                更新的数据idu                                                         ,
                sysdba.base_fun_001(datediff(ss, last_recv_time, sysdate))大概执行的时间,
                *
        from
                v$sessions a
        where
                state='ACTIVE'
        ORDER BY
                DATEDIFF(SS, LAST_RECV_TIME, SYSDATE) DESC;
end;
/

create or replace
procedure sysdba.gfuni
as -- create or replace public synonym gfuni for sysdba.gfuni;
begin
        select
        cast(dbms_metadata.get_ddl('INDEX',OBJ.NAME,sch.name) AS VARCHAR) DDL,
                DM_BIT_TEST(XTYPE, 2),
                sch.name             ,
                obj.name             ,
                *
        from
                SYSINDEXES idx,
                SYSOBJECTS sch,
                SYSOBJECTS obj
        where
                sch.id               =obj.schid
            and obj.id               =idx.id
            and DM_BIT_TEST(XTYPE, 2)=1;
--位图索引
--            and DM_BIT_TEST(XTYPE, 2)=0
--            and idx.TYPE$='BM'
end;
/
create or replace
procedure sysdba.gi
        (
                NAME varchar)
as
        sch varchar;
        tab varchar;
begin
        select upper(LEFTSTR(NAME, instr(NAME, '.')-1)) INTO sch FROM DUAL;
        select upper(RIGHTSTR(NAME, LEN(NAME)-instr(NAME, '.'))) INTO tab FROM DUAL;
WITH
        AA AS
        (
                select
                        INDEX_NAME,
                        LISTAGG(COLUMN_NAME
                        ||' '
                        ||DESCEND, ',')WITHIN GROUP (
                ORDER BY
                        COLUMN_POSITION) COL
                from
                        DBA_ind_columns
                where
                        table_name =tab
                    and table_owner=sch
                GROUP BY
                        TABLE_OWNER,
                        TABLE_NAME ,
                        INDEX_NAME
        )
        ,
        BB AS
        (
                SELECT
                        C.NAME,
                        C.CRTDATE
                FROM
                        sysobjects a,
                        sysobjects b,
                        sysobjects c
                where
                        a.id      =b.schid
                    and c.pid     =b.id
                    AND C.SUBTYPE$='INDEX'
                    and b.name    =tab
                    AND a.name    =sch
        )
SELECT bb.*, col FROM AA, BB WHERE AA.INDEX_NAME(+)=BB.NAME;
end;
/

create or replace
        function SYSDBA.gc
                (
                        owner      varchar,
                        table_name varchar)
                return bigint
        as
                a bigint;
        begin
                execute immediate 'select count(*) from '||owner||'.'||table_name into a;
                return a;
        end;

-- wait g0
-- wait gbuf speed
-- wait gallm
-- wait gkeyini

create or REPLACE PROCEDURE gl as
begin
select exec_time/1000.0 as Ex_ss, 
-- cast(sf_get_session_sql(sess_id) as varchar) 最近语句,
sql_text ,FINISH_TIME
from  V$LONG_EXEC_SQLS 
order by  FINISH_TIME desc;
end;

create or replace public synonym gs for sysdba.gs;
create or replace public synonym gm1 for sysdba.gm1;
create or replace public synonym gm2 for sysdba.gm2;
create or replace public synonym gmmc for sysdba.gmmc;
create or replace public synonym gbuf for sysdba.gbuf;
create or replace public synonym gLOCK0 for sysdba.gLOCK0;
create or replace public synonym gLOCK1 for sysdba.gLOCK1;
create or replace public synonym gfuni for sysdba.gfuni;
create or replace public synonym gl for sysdba.gl;
create or replace public synonym gi for sysdba.gi;

任何用户执行gs，可以获取数据库上的活动会话情况
任何用户执行gm1，可以获取数据库上的内存使用情况1
任何用户执行gm2，可以获取数据库上的内存使用情况2
任何用户执行gmmc，可以获取数据库上会话的内存使用情况
任何用户执行gbuf，可以获取数据库上的BUFFER使用情况
任何用户执行glock0，可以获取数据库上会话的上锁情况
任何用户执行glock1，可以获取数据库上活动会话的阻塞情况
任何用户执行gfuni，可以获取数据库上所有的函数索引和定义
CREATE TABLE "SYSDBA"."NOTE"
(
"V1" DATETIME(6) DEFAULT SYSDATE,
"V2" VARCHAR(32767),
"V3" CLOB,
"V4" VARCHAR(32767),
"V5" CLOB) STORAGE(ON "MAIN", CLUSTERBTR) ;

/******************************************
功能说明：定期清理僵死会话，以免阻塞其它会话
CREATE TABLE "SYSDBA"."OP_CLOSE_SESSION_HISTORY"
(
"OP_SQL" VARCHAR(39),
"OP_TIME" TIMESTAMP(6),
"CREATE_TIME" DATETIME(6),
"LAST_RECV_TIME" DATETIME(6),
"CLNT_HOST" VARCHAR(128),
"CLNT_IP" VARCHAR(128),
"APPNAME" VARCHAR(128),
"OSNAME" VARCHAR(128),
"USER_NAME" VARCHAR(128),
"SQL_TEXT" VARCHAR(1000)) STORAGE(ON "MAIN", CLUSTERBTR) ;
 ******************************************/

create or replace procedure dm_dba_close_session(v_hh int)as
 begin
 for rs in(
 select 'sp_close_session('
        ||a.sess_id
        ||');'  as op_sql,getdate() as op_time,  a.create_time,a.last_recv_time,a.clnt_host,a.clnt_ip,a.appname,a.osname,a.user_name,a.sql_text 
 from v$sessions a JOIN v$trx b on a.trx_id=b.id
 where a.state='IDLE' AND a.trx_id>0 and b.status='ACTIVE' and a.last_recv_time<=dateadd(hh,-v_hh,getdate())
 )
 loop
 execute immediate rs.op_sql;
 insert into OP_CLOSE_SESSION_HISTORY 
 values(rs.op_sql,rs.OP_TIME,rs.create_time,rs.last_recv_time,rs.clnt_host,rs.clnt_ip,rs.appname,rs.osname,rs.user_name,rs.sql_text)
 ;
 commit;
 end loop;
 end;

--看堆栈gdb attach 进程号（gdb dmserver ***.core）
bt
bt
q
--pstack
--psp 看堆栈 放在服务安装目录下

#!/bin/bash
nsamples=1
sleeptime=0
pid=$(pidof $PWD/dmserver)
ss=$(date +%F%H%M%S.dmserver.t)
for x in $(seq 1 $nsamples)
  do
    gdb -ex "set pagination 0" -ex "thread apply all bt" -batch -p $pid
    sleep $sleeptime
  done >$ss
cat $ss|awk '
  BEGIN { s = ""; }
  /^Thread/ { print s; s = ""; }
  /^\#/ { if (s != "" ) { s = s "," $4} else { s = $4 } }
  END { print s }' | \
sort | uniq -c | sort -r -n -k 1,1

---gdb dmserver  core文件
(gdb) set logging file dmstack.log
(gdb) set logging on
Copying output to dmstack.log.
(gdb) thread apply all bt
…
(gdb) set logging off
(gdb) detach
(gdb) quit
--dmrdc分析core文件，生成解析后的core.txt，其中包括当时堆栈中所有线程的sql语句
dmrdc sfile=core文件 dfile=生成解析后的txt文件
--根据dmstack.log堆栈日志中最后一个LWP线程号去core.txt 找对应的sql语句