设为首页收藏本站

Botang唐波's Oracle Station

查看: 164|回复: 0

课程第6/7次(2018-06-29星期五/2018-07-02星期一)

[复制链接]

719

主题

1081

帖子

7868

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
7868
发表于 2018-6-29 20:33:24 | 显示全部楼层 |阅读模式
做SPAbig实验的时候注意尽量完整地捕获工作负载:
Screenshot.png

如果不选择”Upgrade from 10.2 or 11g“,也可以选择“guided workflow“:
Screenshot-1.png

选择第一次运行:
a.png

  1. begin dbms_sqlpa.execute_analysis_task(task_name => 'SPA_JFV1', execution_type => 'TEST EXECUTE', execution_name => 'SPA_JFV1_DB10G', execution_desc => 'SPA_JFV1_db10g', execution_params => dbms_advisor.arglist('LOCAL_TIME_LIMIT', 'UNLIMITED', 'TIME_LIMIT', 'UNLIMITED', 'DATABASE_LINK', 'DB10G')); end;
复制代码

SPA出按钮的画面:
a.png

在图形界面上,可以也定时捕获SQL调优集:
b.png


SPA后手工治疗的过程STA:
  1. select  * from dba_advisor_executions
  2. where task_name='SPA_JFV1';

  3. declare
  4. v1 varchar2(200);
  5. begin
  6.   v1 :=dbms_sqltune.create_tuning_task(SPA_TASK_NAME=>'SPA_JFV1',
  7.   spa_compare_exec => 'COMPARE_1530270460899');
  8.   dbms_output.put_line(v1);
  9. end;



  10. select  * from DBA_ADVISOR_TASKS;

  11. begin
  12.    dbms_advisor.execute_task('TASK_48');
  13. end;
复制代码

SPM的结果:
spm.png

递归地进行SPA:

Top 10 SQL Statements Based on Impact on Workload




SQL IDNet Impact on Workload (%)Elapsed Time (sec)Net Impact on SQL (%)New Plan
SQL Trial 1SQL Trial 2
dvaxhpys7hpdr4.7700.9570.23275.720Y
2kfsh5m3vk2dn3.1800.6120.12879.030Y
1wfywkvcwm2sp-2.8307.9818.412-5.390N
dgpfj2a3vf83s2.7100.5520.14174.440Y
cpm5u0m6b68k5-1.2200.9621.147-19.270N
3gshh31g2s0x8-1.2000.2080.391-87.710Y
bxajng3zk2vn1-0.9200.2170.358-64.730Y
amny7vwjss6pp-0.9100.2830.422-49.010Y
73w25qm4um8bu-0.6900.2320.337-44.900Y
dyqwss9yg3uzc-0.3900.1010.160-57.960Y

原来的TOP10:
Top 10 SQL Statements Based on Impact on Workload


SQL IDNet Impact on Workload (%)Elapsed Time (sec)Net Impact on SQL (%)New Plan
SQL Trial 1SQL Trial 2
1wfywkvcwm2sp-18.7207.98110.825-35.630Y
dvaxhpys7hpdr4.8000.9570.22876.180Y
2kfsh5m3vk2dn3.2400.6120.11980.600Y
dgpfj2a3vf83s3.0300.5520.09283.290Y
7866641pah2zg-2.7300.8881.303-46.790Y
cpm5u0m6b68k5-2.4300.9621.330-38.320Y
3gshh31g2s0x8-0.5900.2080.297-42.840Y
bxajng3zk2vn1-0.4800.2170.290-33.440Y
73w25qm4um8bu-0.3800.2320.290-24.790Y
ay7s4k9vz34hk0.3100.1100.06343.180Y


在接受完SQL Profile:
基线变多(每条语句对应3根基线,其中带Profile的基线还是自动接受):
c.png

-------------------------------------------------
考试过程中,要求定特定语句的基线(固定它的执行计划):
  1. select * from v$sql where sql_text like '%btbt%';

  2. begin
  3.   DBMS_SCHEDULER.CREATE_JOB(job_name => 'LOAD_STS_MonJul2_164227_107', job_type => 'PLSQL_BLOCK', job_action => 'DECLARE sqlset_cur dbms_sqltune.sqlset_cursor; bf VARCHAR2(37); BEGIN bf := q''#UPPER(SQL_ID) = ''CA6CBQ3KFM5U5'' #''; OPEN sqlset_cur FOR SELECT VALUE(P) FROM TABLE( dbms_sqltune.select_cursor_cache(attribute_list=>''TYPICAL'', basic_filter=>bf)) P; dbms_sqltune.load_sqlset( sqlset_name=>''BT'', populate_cursor=>sqlset_cur, load_option => ''MERGE'', update_option => ''ACCUMULATE'', sqlset_owner=>''SYS''); END;', enabled => TRUE);
  4.   end;

  5. select  * from dba_sqlset_statements  ss where  ss.sqlset_name='BT';

  6. declare
  7.   v1 varchar2(100);
  8. begin
  9.    v1:= dbms_spm.load_plans_from_sqlset(sqlset_name => 'BT');
  10. end;
  11.    
复制代码





回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|Botang唐波's Oracle Station   

GMT+8, 2018-9-20 12:44 , Processed in 0.190755 second(s), 27 queries .

快速回复 返回顶部 返回列表