oracle_ogg_sql_analyze

一.参考

文章有总结

https://www.modb.pro/db/168420

 

二.分析流程

2.1 前置条件

获取OGG进程在数据库中执行的用户名称，数据库信息

select user_id,con_id,username,account_status from cdb_users where username like '%OGG%';

 

2.2 找到时间消耗占比最高的event or Top sql

select
 to_char(SAMPLE_TIME,'yyyy-mm-dd hh24') as "date",
 event,count(*)
 from v$active_session_history
 where SAMPLE_TIME
    between to_date('20211115 13:00','yyyymmdd hh24:mi') 
         and to_date('20211115 18:00','yyyymmdd hh24:mi') 
         and CON_ID='3' and user_id=108
         and PROGRAM like '%oracle@ebxxx1%' 
   group by to_char(SAMPLE_TIME,'yyyy-mm-dd hh24'),event
     having(count(*))>10
       order by 1,3,2;


select sql_id,count(*)
 from v$active_session_history
 where SAMPLE_TIME
       between to_date('20211115 13:00','yyyymmdd hh24:mi')
           and to_date('20211115 18:00','yyyymmdd hh24:mi') 
           and CON_ID='3' and user_id=108
           and PROGRAM like '%oracle@ebxxb1%' 
    group by sql_id
  having(count(*))>10
  order by 2;

 

2.3 查询慢SQL 的执行计划

select * from table(dbms_xplan.display_cursor(‘0ajv7pxmcjduc’));

 

2.4 慢SQL处理

EXEC dbms_stats.gather_table_stats
(ownname=>'Cxx',TABNAME=>'Nxx',
CASCADE=>TRUE,DEGREE=>16,ESTIMATE_PERCENT=>3,NO_INVALIDATE=>fals);


select
 b.INDEX_OWNER,b.INDEX_NAME,b.INVALID,
a.COLUMN_NAME,b.CONSTRAINT_TYPE,
b.STATUS,b.CONSTRAINT_NAME
 from dba_cons_columns a,dba_constraints b
 where
 a.owner=b.owner and a.owner='Cxx'
 and b.TABLE_NAME=a.TABLE_NAME
 and a.table_name='Nxx'
  and a.CONSTRAINT_NAME=b.CONSTRAINT_NAME;

alter table Cxx.NGLxxNCES
 add constraint pk_rowid_xxES unique (row_id) enable Novalidate;

select b.INDEX_OWNER,b.INDEX_NAME,b.INVALID,
a.COLUMN_NAME,b.CONSTRAINT_TYPE,b.STATUS,
b.CONSTRAINT_NAME
 from dba_cons_columns a,dba_constraints b
 where a.owner=b.owner and a.owner='Cxx' 
     and b.TABLE_NAME=a.TABLE_NAME and a.table_name='NGLxx'
     and a.CONSTRAINT_NAME=b.CONSTRAINT_NAME and b.CONSTRAINT_TYPE='U';


comment on table  xx.xx is 'ogg_big_tab';
select owner,to_char(LAST_DDL_TIME,'yyyy-mm-dd hh24:mi:ss')
 from dba_objects where object_name='xx';
------------------------------------------------------------
xx                  2021-11-14 02:54:45

MAP FPxx.xxxL.xxxx,TARGET OGxx.GxxxS
,colmap (usedefaults , ROW_ID= @token ('TKN-ROWID')),keycols(ROW_ID);


--手工修改统计信息
begin 
 dbms_stats.set_table_stats(ownname=>'CxxL',
     tabname=>'NGLxx',
     numrows=>999999999999999999999,
     numblks=>8,
     avgrlen=>'800');
 end;
/
SQL> select NUM_ROWS,BLOCKS,EMPTY_BLOCKS,
to_char(LAST_ANALYZED,'yyyy-mm-dd hh24:mi:ss')
 from dba_tables where TABLE_NAME='NGxx';
  NUM_ROWS     BLOCKS EMPTY_BLOCKS TO_CHAR(LAST_ANALYZ
---------- ---------- ------------ -------------------
1.0000E+21          8            0 2021-11-15 16:06:42
建议手工收集统计信息后，对该表统计信息进行Lock，否则下一次再次收集统计信息会刷出手工设置的值，
那么OGG进程执行的SQL下一次解析可能会再次选择慢的执行计划！！！


exec dbms_stats.lock_table_stats('Cxx','NGL_FAxx');
col table_name for a20 
select table_name,stattype_locked
 from dba_tab_statistics where  table_name='xxS';
TABLE_NAME                        STATT
---------------------------------------------
NGxxx                              ALL





>edit param REPAP02
REPLICAT repap02
USERIDALIAS ExxOGG
ASSUMETARGETDEFS
GETTRUNCATES
DBOPTIONS NOSUPPRESSTRIGGERS
DBOPTIONS DEFERREFCONST
report at 1:59
reportrollover at 2:00
reperror default,abend
ALLOWNOOPUPDATES
INSERTMISSINGUPDATES
DISCARDFILE ./dirrpt/discard_repap02.dsc, PURGE
MAP Pxx,TARGET CxxS,FILTER (@GETENV('TRANSACTION','CSN') > 894300384284);
--add
NEW_OGGPRO=repap02
NEW_OGG_FILE=repap01/pa
ggsci <<EOF
dblogin USERIDALIAS ExxG
add checkpointtable C##OGG.${NEW_OGGPRO}_ckp_tab2
add replicat $NEW_OGGPRO ,integrated
,exttrail ./dirdat/${NEW_OGG_FILE},CHECKPOINTTABLE C##OGG.${NEW_OGGPRO}_ckp_tab2
register replicat ${NEW_OGGPRO} database
exit
EOF

alter REPLICAT REPAP02 ,extseqno 15,extrba 46286994
GGSCI (exx1) 3> start REPAP02
$ cat repap01.prm |grep GL_xx
$ cat repap02.prm |grep GL_xx
确认新建的复制进程正常复制后,删除延迟高的复制进程对应拆分的表后，启动之前stop的复制进程
--删除后，启动复制进程
GGSCI (ebsfiproddb1) > start REPAP01