利用rman备份库调优生产sql

一、检查自动任务

生产环境一般会关闭自动顾问等自动任务，可以利用rman备份恢复的环境启用自动调优助手等任务或者手工调用自动调优助手帮助识别并调整业务sql，因为rman备份环境存有awr数据，可以利用里面的生产awr数据采集业务sql并调整。

SQL> SELECT client_name, status, consumer_group, window_group 
 2 FROM dba_autotask_client 
 3 ORDER BY client_name; 

 
CLIENT_NAME STATUS CONSUMER_GROUP WINDOW_GROUP 
---------------------------------------- -------- ------------------------------ ---------------------------------------- 
auto optimizer stats collection DISABLED ORA$AUTOTASK ORA$AT_WGRP_OS 
auto space advisor DISABLED ORA$AUTOTASK ORA$AT_WGRP_SA 
sql tuning advisor DISABLED ORA$AUTOTASK ORA$AT_WGRP_SQ 

 
复制

SQL> SQL> SELECT task_name, status, TO_CHAR(execution_end,'DD-MON-YY HH24:MI') 
 2 FROM dba_advisor_executions 
 3 WHERE task_name='SYS_AUTO_SQL_TUNING_TASK' 
 4 ORDER BY execution_end; 

 
no rows selected 
复制

SQL> SELECT window_name,TO_CHAR(window_next_time,'DD-MON-YY HH24:MI:SS') 
 2 ,sql_tune_advisor, optimizer_stats, segment_advisor 
 3 FROM dba_autotask_window_clients; 

 
WINDOW_NAME TO_CHAR(WINDOW_NEXT_T SQL_TUNE OPTIMIZE SEGMENT_ 
---------------------------------------- --------------------- -------- -------- -------- 
MONDAY_WINDOW 06-MAR-23 22:00:00 DISABLED DISABLED DISABLED 
TUESDAY_WINDOW 28-FEB-23 22:00:00 DISABLED DISABLED DISABLED 
WEDNESDAY_WINDOW 01-MAR-23 22:00:00 DISABLED DISABLED DISABLED 
THURSDAY_WINDOW 02-MAR-23 22:00:00 DISABLED DISABLED DISABLED 
FRIDAY_WINDOW 03-MAR-23 22:00:00 DISABLED DISABLED DISABLED 
SATURDAY_WINDOW 04-MAR-23 06:00:00 DISABLED DISABLED DISABLED 
SUNDAY_WINDOW 05-MAR-23 06:00:00 DISABLED DISABLED DISABLED 

 
7 rows selected. 

 
复制

二、启用自动调优

SQL> BEGIN 
 2 DBMS_AUTO_TASK_ADMIN.enABLE( 
 3 client_name => 'sql tuning advisor', 
 4 operation => NULL, 
 5 window_name => NULL); 
 6 END; 
 7 / 

 
PL/SQL procedure successfully completed. 

 
SQL> SELECT client_name, status, consumer_group, window_group 
 2 FROM dba_autotask_client 
 3 ORDER BY client_name; 

 
auto optimizer stats collection DISABLED ORA$AUTOTASK ORA$AT_WGRP_OS 
auto space advisor DISABLED ORA$AUTOTASK ORA$AT_WGRP_SA 
sql tuning advisor ENABLED ORA$AUTOTASK ORA$AT_WGRP_SQ 

 
复制

查看调度窗口

SQL> SELECT window_name,TO_CHAR(window_next_time,'DD-MON-YY HH24:MI:SS') 
 2 ,sql_tune_advisor, optimizer_stats, segment_advisor 
 3 FROM dba_autotask_window_clients; 

 
WINDOW_NAME TO_CHAR(WINDOW_NEXT_T SQL_TUNE OPTIMIZE SEGMENT_ 
---------------------------------------- --------------------- -------- -------- -------- 
MONDAY_WINDOW 06-MAR-23 22:00:00 ENABLED DISABLED DISABLED 
TUESDAY_WINDOW 28-FEB-23 22:00:00 ENABLED DISABLED DISABLED 
WEDNESDAY_WINDOW 01-MAR-23 22:00:00 ENABLED DISABLED DISABLED 
THURSDAY_WINDOW 02-MAR-23 22:00:00 ENABLED DISABLED DISABLED 
FRIDAY_WINDOW 03-MAR-23 22:00:00 ENABLED DISABLED DISABLED 
SATURDAY_WINDOW 04-MAR-23 06:00:00 ENABLED DISABLED DISABLED 
SUNDAY_WINDOW 05-MAR-23 06:00:00 ENABLED DISABLED DISABLED 

 
7 rows selected. 
复制

SQL> SELECT window_name, to_char(start_time,'dd-mon-yy hh24:mi'), duration 
 2 FROM dba_autotask_schedule 
 3 ORDER BY start_time; 

 
WINDOW_NAME TO_CHAR(START_TIME DURATION 
---------------------------------------- ------------------ ------------------------------ 
TUESDAY_WINDOW 28-feb-23 22:00 +000 04:00:00 
WEDNESDAY_WINDOW 01-mar-23 22:00 +000 04:00:00 
THURSDAY_WINDOW 02-mar-23 22:00 +000 04:00:00 
FRIDAY_WINDOW 03-mar-23 22:00 +000 04:00:00 
SATURDAY_WINDOW 04-mar-23 06:00 +000 20:00:00 
SUNDAY_WINDOW 05-mar-23 06:00 +000 20:00:00 
MONDAY_WINDOW 06-mar-23 22:00 +000 04:00:00 
TUESDAY_WINDOW 07-mar-23 22:00 +000 04:00:00 
WEDNESDAY_WINDOW 08-mar-23 22:00 +000 04:00:00 
THURSDAY_WINDOW 09-mar-23 22:00 +000 04:00:00 
FRIDAY_WINDOW 10-mar-23 22:00 +000 04:00:00 

 
WINDOW_NAME TO_CHAR(START_TIME DURATION 
---------------------------------------- ------------------ ------------------------------ 
SATURDAY_WINDOW 11-mar-23 06:00 +000 20:00:00 
SUNDAY_WINDOW 12-mar-23 06:00 +000 20:00:00 
MONDAY_WINDOW 13-mar-23 22:00 +000 04:00:00 
TUESDAY_WINDOW 14-mar-23 22:00 +000 04:00:00 
WEDNESDAY_WINDOW 15-mar-23 22:00 +000 04:00:00 
THURSDAY_WINDOW 16-mar-23 22:00 +000 04:00:00 
FRIDAY_WINDOW 17-mar-23 22:00 +000 04:00:00 
SATURDAY_WINDOW 18-mar-23 06:00 +000 20:00:00 
SUNDAY_WINDOW 19-mar-23 06:00 +000 20:00:00 
MONDAY_WINDOW 20-mar-23 22:00 +000 04:00:00 
TUESDAY_WINDOW 21-mar-23 22:00 +000 04:00:00 

 
WINDOW_NAME TO_CHAR(START_TIME DURATION 
---------------------------------------- ------------------ ------------------------------ 
WEDNESDAY_WINDOW 22-mar-23 22:00 +000 04:00:00 
THURSDAY_WINDOW 23-mar-23 22:00 +000 04:00:00 
FRIDAY_WINDOW 24-mar-23 22:00 +000 04:00:00 
SATURDAY_WINDOW 25-mar-23 06:00 +000 20:00:00 
SUNDAY_WINDOW 26-mar-23 06:00 +000 20:00:00 
MONDAY_WINDOW 27-mar-23 22:00 +000 04:00:00 
TUESDAY_WINDOW 28-mar-23 22:00 +000 04:00:00 
WEDNESDAY_WINDOW 29-mar-23 22:00 +000 04:00:00 
THURSDAY_WINDOW 30-mar-23 22:00 +000 04:00:00 
FRIDAY_WINDOW 31-mar-23 22:00 +000 04:00:00 
SATURDAY_WINDOW 01-apr-23 06:00 +000 20:00:00 

 
33 rows selected. 
复制

三、利用awr数据创建调优集

SQL> select owner, name, id, created, statement_count from dba_sqlset; 

 
OWNER NAME ID CREATED STATEMENT_COUNT 
---------- ------------------------------ ---------- ------------------- --------------- 
SYS SYS_AUTO_STS 1 2019-04-17 01:14:26 0 

 
SQL> BEGIN 
 2 DBMS_SQLTUNE.CREATE_SQLSET( 
 3 sqlset_name => 'HIGH_IO', 
 4 description => 'High disk read tuning set'); 
 5 END; 
 6 / 

 
PL/SQL procedure successfully completed. 

 
SQL> select owner, name, id, created, statement_count from dba_sqlset; 

 
OWNER NAME ID CREATED STATEMENT_COUNT 
---------- ------------------------------ ---------- ------------------- --------------- 
SYS SYS_AUTO_STS 1 2019-04-17 01:14:26 0 
SYS HIGH_IO 5 2023-02-28 09:40:25 0 
复制

四、查看awr中资源密集sql

SQL> SELECT sql_id 
 2 ,disk_reads, cpu_time, elapsed_time,EXECUTIONS,module,COMMAND_TYPE  
 3 FROM table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(29100,29364, 
 4 null, null, 'disk_reads',null, null, null, 100)) where EXECUTIONS>20 and parsing_schema_name <> 'SYS'  
 5 ORDER BY disk_reads DESC,MODULE; 

 
SQL_ID DISK_READS CPU_TIME ELAPSED_TIME EXECUTIONS MODULE COMMAND_TYPE 
------------- -------------------- -------------------- -------------------- -------------------- -------------------------------------------------- -------------------- 
apmcb6fm10jsw 8099201919 178675049564 364732671309 1266 SQL*Plus 2 
fvx120zqr7f14 1890642116 62681302576 181271628823 449 JDBC Thin Client 3 
36sq3q4b35yyv 1812718553 58040471566 161011246393 263 UnifyFileDeal@ljkclnx-2 (TNS V1-V3) 3 
dhj22t3vypqg6 1795108559 260677250672 1150341616658 525 JDBC Thin Client 3 
ak3c4z1ca5njj 1465851043 32008033856 65710002220 77 JDBC Thin Client 3 
4h5xcu5txbabu 1132984248 25639979294 53031851992 457 SQL*Plus 7 
bh2fgcq0sw600 1114547751 52478103134 228695637975 295 LocOTTMBHInfoSyn@lbkprocnx-1 (TNS V1-V3) 3 
gdy1v6u3qgc7a 1108803084 26793421578 55823437266 532 SQL*Plus 7 
fj8cyj52b2s2r 1054861801 24798460389 53087369289 449 SQL*Plus 7 
8b2sppz333g4f 1024633879 25252786947 51713907037 530 SQL*Plus 7 
0uakp6kb3cjf7 951567470 26405004422 50999412371 703 SQL*Plus 7 
baq4hpx23bxdy 918886356 39486637374 116358370938 261 UnifyFileDeal@ljkclnx-2 (TNS V1-V3) 3 
gzgmtyga27nyt 688593207 25359242505 79656628550 263 UnifyFileDeal@ljkclnx-2 (TNS V1-V3) 3 
1jvvk1un0sh99 630332631 16067470267 33135776028 355 SQL*Plus 7 
dx1tuwhnh0u2j 577924931 35974291841 70199486452 1643 JDBC Thin Client 3 
dpr691krc9j82 222980072 11532216814 27131416633 128 SQL*Plus 3 
bf2uu1xn6w7ag 207957666 22106500405 145083945164 758 UnifyFileDeal@ljkclnx-2 (TNS V1-V3) 3 
642un4txf910v 145767333 62656467788 72436540638 262 UnifyFileDeal@ljkclnx-2 (TNS V1-V3) 3 
fvx120zqr7f14 142484284 21360291749 101327116237 52 JDBC Thin Client 3 
gup9dyndmuu03 121436588 3730103202 7465907047 88 JDBC Thin Client 3 
b7qzn9jr138gn 100903531 61611779559 81267199490 262 UnifyFileDeal@ljkclnx-2 (TNS V1-V3) 3 
7cn58fzxt4hst 73546534 1898909732 4483742939 82 sqlshell@kjfcbenx-11 (TNS V1-V3) 6 
4npzs9gxmxrn1 67790093 22078672259 27115872758 242 UnifyFileDeal@ljkclnx-2 (TNS V1-V3) 3 
fcgxhmx4640a1 58211273 20912194403 32581556742 4540 JDBC Thin Client 3 
cyc7jnzhmxtjz 54269409 3019552006 12194950540 28 SQL*Plus 6 
fhh1vk45xrwff 53066460 2818608441 13226758352 28 SQL*Plus 6 
84tc7x1h4wruj 45209872 28685799058 55087838427 713813 RunSchedule@ljkclnx-1 (TNS V1-V3) 6 
fpxk5crkjc7cf 37394425 2874708553 6153616963 131 PaymentRollback@lbkprocnx-1 (TNS V1-V3) 3 
9vkrwcankphgm 33944968 3300164718 32205561270 216 JDBC Thin Client 3 
ckjhmx1f667nm 31051576 3167953223 32078507617 215 JDBC Thin Client 3 
4pq38aknnmy8g 31024838 3556856470 21563255623 3415598 LOverdueNoticeToDB@lbkprocnx-1 (TNS V1-V3) 3 
5va54r557mvv4 30974190 3158333406 31579892812 212 JDBC Thin Client 3 
9ggfmapy1mxum 30859936 3159917455 31811983529 214 JDBC Thin Client 3 
byf31ddybry5a 30519143 3203493348 32553084135 218 JDBC Thin Client 3 
52jcjzkay2mfa 28909406 3031521088 31088340440 208 JDBC Thin Client 3 
5xm5h8b6y00fr 27635257 2008561440 7869404054 107 LocPersonCardInfoSync@kbkprocnx-1 (TNS V1-V3) 3 
c2zwtngdrz1b7 24213320 12860944407 14102849386 221 UnifyFileDeal@ljkclnx-1 (TNS V1-V3) 3 
139amnfsps2qd 21877996 3665281311 7499194517 815 JDBC Thin Client 3 
5h271rsq9khc4 19043475 2377443817 15837174245 95715 LFPlusChkFileToDB@kibossnx-11 (TNS V1-V3) 3 
bdg7qvddkcwcz 14394604 42409438217 46778427496 15223 JDBC Thin Client 3 
4trg5k568drxw 14055318 8967127041 12526743054 132 UnifyFileDeal@ljkclnx-2 (TNS V1-V3) 3 

 
41 rows selected. 
复制

五、加载进入优化集

BEGIN 
 dbms_sqltune.create_sqlset(  
 sqlset_name => 'IO_STS', 
 description => 'STS from AWR'); 
END; 
/ 

 
select snap_id, begin_interval_time 
from dba_hist_snapshot order by 1; 

 
DECLARE 
 base_cur dbms_sqltune.sqlset_cursor; 
BEGIN 
 OPEN base_cur FOR 
 SELECT value(x) 
FROM table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(29100,29364, 
 null, null, 'disk_reads',null, null, null, 100)) x where EXECUTIONS>20 and parsing_schema_name <> 'SYS' ; 
 -- 
 dbms_sqltune.load_sqlset( 
 sqlset_name => 'IO_STS', 
 populate_cursor => base_cur); 
END; 
/ 
复制

六、查看优化集sql

此时也可以利用dbms_sqltune手工删除部分sql

SQL> SELECT sqlset_name, elapsed_time 
 2 ,cpu_time, buffer_gets, disk_reads  
 3 FROM dba_sqlset_statements 
 4 WHERE sqlset_name = 'IO_STS'; 

 
SQLSET_NAME ELAPSED_TIME CPU_TIME BUFFER_GETS DISK_READS 
-------------------- ------------ ---------- ----------- ---------- 
IO_STS 3.6473E+11 1.7868E+11 8198258499 8099201919 
IO_STS 1.8127E+11 6.2681E+10 2177953579 1890642116 
IO_STS 1.6101E+11 5.8040E+10 1903298474 1812718553 
IO_STS 1.1503E+12 2.6068E+11 2394175767 1795108559 
IO_STS 6.5710E+10 3.2008E+10 1466290690 1465851043 
IO_STS 5.3032E+10 2.5640E+10 1196722876 1132984248 
IO_STS 2.2870E+11 5.2478E+10 2508450349 1114547751 
IO_STS 5.5823E+10 2.6793E+10 1407868540 1108803084 
IO_STS 5.3087E+10 2.4798E+10 1189046395 1054861801 
IO_STS 5.1714E+10 2.5253E+10 1394952065 1024633879 
IO_STS 5.0999E+10 2.6405E+10 1836456707 951567470 

 
SQLSET_NAME ELAPSED_TIME CPU_TIME BUFFER_GETS DISK_READS 
-------------------- ------------ ---------- ----------- ---------- 
IO_STS 1.1636E+11 3.9487E+10 1355568949 918886356 
IO_STS 7.9657E+10 2.5359E+10 941619996 688593207 
IO_STS 3.3136E+10 1.6067E+10 925314560 630332631 
IO_STS 7.0199E+10 3.5974E+10 2749382610 577924931 
IO_STS 2.7131E+10 1.1532E+10 352679032 222980072 
IO_STS 1.4508E+11 2.2107E+10 612133638 207957666 
IO_STS 7.2437E+10 6.2656E+10 156787854 145767333 
IO_STS 1.0133E+11 2.1360E+10 184710351 142484284 
IO_STS 7465907047 3730103202 191237172 121436588 
IO_STS 8.1267E+10 6.1612E+10 213920731 100903531 
IO_STS 4483742939 1898909732 100850297 73546534 

 
SQLSET_NAME ELAPSED_TIME CPU_TIME BUFFER_GETS DISK_READS 
-------------------- ------------ ---------- ----------- ---------- 
IO_STS 2.7116E+10 2.2079E+10 76883277 67790093 
IO_STS 3.2582E+10 2.0912E+10 1883820475 58211273 
IO_STS 1.2195E+10 3019552006 97094359 54269409 
IO_STS 1.3227E+10 2818608441 103808891 53066460 
IO_STS 5.5088E+10 2.8686E+10 1653624828 45209872 
IO_STS 6153616963 2874708553 617121622 37394425 
IO_STS 3.2206E+10 3300164718 260187139 33944968 
IO_STS 3.2079E+10 3167953223 258911329 31051576 
IO_STS 2.1563E+10 3556856470 443478655 31024838 
IO_STS 3.1580E+10 3158333406 255475487 30974190 
IO_STS 3.1812E+10 3159917455 257708931 30859936 

 
SQLSET_NAME ELAPSED_TIME CPU_TIME BUFFER_GETS DISK_READS 
-------------------- ------------ ---------- ----------- ---------- 
IO_STS 3.2553E+10 3203493348 262523409 30519143 
IO_STS 3.1088E+10 3031521088 250608164 28909406 
IO_STS 7869404054 2008561440 100739299 27635257 
IO_STS 1.4103E+10 1.2861E+10 63328173 24213320 
IO_STS 7499194517 3665281311 556283282 21877996 
IO_STS 1.5837E+10 2377443817 70617678 19043475 
IO_STS 4.6778E+10 4.2409E+10 8458202204 14394604 
IO_STS 1.2527E+10 8967127041 30488912 14055318 

 
41 rows selected. 
复制

六、创建调优任务

SQL> variable mytt varchar2(30); 
SQL> exec :mytt := DBMS_SQLTUNE.CREATE_TUNING_TASK(sqlset_name => 'IO_STS', task_name => 'tune_test4'); 

 
PL/SQL procedure successfully completed. 
复制

七、调整调优任务参数

exec dbms_sqltune.SET_TUNING_TASK_PARAMETER(TASK_NAME=>'tune_test4',PARAMETER=>'TIME_LIMIT',value=>180000); 
复制

八、运行调优任务

exec dbms_sqltune.execute_tuning_task(task_name=>'tune_test4'); 
复制

九、查看调优报告

select dbms_sqltune.report_tuning_task('tune_test4') from dual; 
复制