SQL Monitor and SQL Quarantine
SQL Monitor belongs Oracle Tuning Pack
STATISTICS_LEVEL=TYPICAL(default) or all, will start QAL monitor features.
Events to Trigger SQL monitor
单次执行消耗大于等于5秒cpu/IO时间的SQL PL/SQL
并行执行的语句
加/* + MONITOR*/ hint的SQL语句
用sql_monitor事件指定SQL ID对应的SQL
alter system set events 'sql_monitor [sql:5hc07qvt8v737 | sql:9ht3ba3arrzt3] force=true
利用存储过程
DBMS_SQL_MONITOR.BEGNIS_OPERATION and DBMS_SQL_MONITOR.END_OPERATION 定义的会话中的所有SQL PL/SQL
CONTROL_MANAGEMENT_PACK_ACCESS is the default Oracle parameter.
Set the default parameter: Diagnostic + Tuning.
CONTROL_MANAGEMENT_PACK_ACCESS
SQL监控方法
cloud control
sql developer
DBMS_SQL_MONITOR package
SQL Monitor interface
monitor_hint, no_monitor_hint
dba_hist_reports
dba_hist_reports_details
V$SQL_MONITOR
V$SQL_PLAN_MONITOR
V$SQL_MONITOR_SESSTAT
v$active_session_history, v$session, v$session_longops, v$sql, v$sqlplan
SQL监控权限
普通用户可以监控自己运行的SQL，不需要其它额外的权限
SELECT_CATALOG_ROLE权限的用户还可以监控其它用户的SQL
如何预防由于执行计划改变引起的SQL性能衰减？
SQL执行计划管理（SQL PLAN Management）是一种预防性机制，使优化器可以自动管理执行计划，从而确保数据库仅使用已知或验证过的计划
SQL Plan Baseline and SQL Profile ???
Automatic SPM Evolve Advisor
SPM Evolve Advisor is a SQL consultant, used to auto exam the SQL performace
第一阶段， 基准捕获（Capture Baseline）
自动捕获
设置 OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE
手工捕获
手工捕获
DBMS_SPM.LOAD_PLANS_FROM_%
第二阶段， 选择
再次解析相同的语句，但是创建了不同的计划。
新计划被添加到Plan History记录中，知道经过验证，才会被使用
two parameter should be kept as default
optimizer_use_sql_plan_baselines=true
optimizer_capture_sql_plan_baselines=false
第三阶段，演进
New SYS_AUTO_SPM_EVOLVE_TASK job as part of the Automatic SQL Tuning Task
Report: DBMS_SPM.REPORT_AUTO_EVOLVE_TASK
Manual: DBMS_SPM.CREATE_EVOLVE_TASK
同等或者更加优化的执行计划会被添加到SQL Plan Baseline
SPM设置
查看配置
select parameter_name, parameter_value from DBA_SQL_MANAGEMENT_CONFIG;
变更保留期
exec DBMS_SPM.CONFIGURE('plan_retention_weeks',5)
改变空间消耗
exec DBMS_SPM.CONFIGURE('space_budget_percenet',5)
加载计划的来源
exec DBMS_SPM.LOAD_PLANS_FROM_AWR(begin_snap+.29, end_snap+>69)
SQL隔离
通过隔离SQL执行计划，更科学的防止失控SQL对系统资源 不必要的消耗。
从Oracle Database 19c开始，SQL隔离会自 动隔离由资源管理器终止的SQL语句的执行计 划，以便不允许它们再次运行。
手动隔离 - 为SQL语句的执行计划创建隔离配置
使用以下任一包函数为SQL语句的执行计划创建隔离配置：
DBMS_SQLQ.CREATE_QUARANTINE_BY_SQL_ID
DBMS_SQLQ .CREATE_QUARANTINE_BY_SQL_TEXT
DECLARE
quarantine_config VARCHAR2(30);
BEGIN
quarantine_config := DBMS_SQLQ.CREATE_QUARANTINE_BY_SQL_ID( SQL_ID => ’8vu7s907prbgr’, PLAN_HASH_VALUE => '3488063716');
END;
/
DECLARE quarantine_config VARCHAR2(30);
BEGIN
quarantine_config := DBMS_SQLQ.CREATE_QUARANTINE_BY_SQL_ID( SQL_ID => '152sukb473gsk');
END;
/
DECLARE quarantine_config VARCHAR2(30);
BEGIN
quarantine_config := DBMS_SQLQ.CREATE_QUARANTINE_BY_SQL_TEXT( SQL_TEXT => to_clob('select count(*) from emp'));
END;
/
手动隔离 -在隔离配置中指定隔离阈值
可指定如下隔离阈值：
- CPU time
- Elapsed time
- I/O in megabytes
- Number of physical I/O requests
- Number of logical I/O requests
例：
BEGIN
DBMS_SQLQ.ALTER_QUARANTINE( QUARANTINE_NAME => 'SQL_QUARANTINE_3z0mwuq3aqsm8cfe7a0e4', PARAMETER_NAME => 'CPU_TIME', PARAMETER_VALUE => '5');
DBMS_SQLQ.ALTER_QUARANTINE( QUARANTINE_NAME => 'SQL_QUARANTINE_3z0mwuq3aqsm8cfe7a0e4', PARAMETER_NAME => 'ELAPSED_TIME', PARAMETER_VALUE => '10');
END;
/
注： 如果任何Resource Manager阈值等于或小于SQL语句的隔离配置中指定的隔离阈值，则该SQL语句（如果使 用其隔离配置中指定的执行计划）则不允许运行
Overview of SQL Plan Management：
https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/overview-of-sql-planmanagement.html#GUID-F1C45056-F998-43E5-B362-83F88DA49E58
Monitoring Database Operations：
https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/monitoring-databaseoperations.html#GUID-C941CE9D-97E1-42F8-91ED-4949B2B710BF
Quarantine for Execution Plans for SQL Statements Consuming Excessive System Resources： https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/diagnosing-and-resolving-problems.html#GUID-1CF7E2B7-1BF8-4907-889E-1107CAA83E51
手把手教你19c新特性：SQL隔离 ：
https://mp.weixin.qq.com/s?__biz=MzI3OTM3MDkyNg==&mid=2247489388&idx=1&sn=89e7f43ee306a72392e1ce053fa984c2&chksm=eb499aaddc3e13bb3453a17ac3eae1b6769f7 5f61ce4979a4039751ec7b4a1f9288c45fe560d&mpshare=1&scene=1&srcid=&sharer_sharetime=1581493432215&sharer_shareid=166f7736f01ec1a6c9b45e9d9bde8d51&key=e4586ba 676168b970ea50196cf8d4ca7746f0fff69ed5ddf1387325e34afe121416ee942752638387df5817c47e7ad8888b2c5fc65614bb5b75427e42eb6ba7992691e30fb6cd21e846a7746974f10fe &ascene=0&uin=MTA3MjQyMDI0MQ%3D%3D&devicetype=iMac+MacBookPro14%2C1+OSX+OSX+10.14.6+build(18G3020)&version=12031d10&nettype=WIFI&lang=zh_CN&fontScal e=100&exportkey=AUTitqq8BDZyi2M0ZL%2F86aY%3D&pass_ticket=A9Fq3frJwLHL1WWsZ9dtJx0jIYFrVlPMjAdWaTtDi4r%2F%2FLP5agh7r1gzleIFgalD##
-------------------------------------------------
------------SPM Demo Script---------------
-------------------------------------------------
select * from dba_sql_plan_baselines;
origin, accept
loading plan from AWR
select *
from (select snap_id, snap_level, to_char(begin_interval_time, 'dd/mm/yy hh24:mi:ss') begin
from dba_hist_snapshot
order by snap_id desc)
where rownum <=3;
--Loading plan from AWR
variable v_plan_cnt number
exec :v_plan_cnt := dbms_spm.load_plans_from_awr(begin_snap => 132, end_snap => 133);
select sql_handle, sql_text, plan_name, origin, enabled, accepted
from dba-sql_plan_baselines
where origin= 'MANUAL-LOAD-FROM-AWR';
--Loading plans from the Shared SQL Area
alter system flush shared_pool;
alter system flush buffer_cache;
select /*load_cc*/ *
from sh.sales
where quantity_sold > 40
order by prod_id;
select sql_id, child_number as "Child Num", plan_hash_value as "Plan Hash", optimizer_env_hash_value as "Opt Env Hash"
from v$SQL
where sql_text like 'SELECT /*load_cc*/%';
variable v_plan_cnt number
begin
:v_plan_cnt := dbms_spm.load_plans_from_cursor_cache(sql_id => '99asdf9ak3asdf3');
end;
/
select sql_handle, sql_text, plan_name, origin, enabled, accepted
from dba_sql_plan_baselines
where origin= 'MANUAL-LOAD-FROM-CURSOR-CACHE';
--Loading plans from the SQL Tuning Set
alter system flush shared_pool;
alter system flush buffer_cache;
select /*q1_group_by */ prod_name, sum(quantity_sold)
from sh.products_test p, sh.sales_test s
where p.prod_id = s.prod_id and p.prod_category_id = 203
group by prod_name;
exec dbms_sqltune.create_sqlset(sqlset_name => 'MY_SYS')
declare
l_cursor dbms_sqltune,sqlset_cursor;
begin
open l_cursor for
select value(a) from table (
dbms_sqltune.select_cursor_cache (
basic_filter => 'sql_text like ' "SELECT /* q1_group_by */% " ' ',
attribute_list => 'ALL' )
) a;
dbms_sqltune.load_sqlset( sqlset_name => 'MY_SYS', populate_cursor => l_cursor);
end;
/
select * from dba_sqlset_statements where sqlset_name = 'MY_SYS';
variable v_plan_cnt number
execute :v_plan_cnt := dbms_spm.load_plans_from_sqlset ( -
sqlset_name => 'MY_SYS', -
basic_filter => 'sql_text like ' "SELECT /* q1_group_by */% " ' ',);
select sql_handle, sql_text, plan_name, origin, enabled, accepted
from dba_sql_plan_baselines
where origin= 'MANUAL-LOAD-FROM-STS'
order by sql_handle, accepted;
--Evolve Plan
set serveroutput on
declare
tk_name varchar2(50);
exe_name varchar2(50);
evol_out clob;
begin
tk_name := dbms_spm.create_evolve_task ( sql_handle => 'SQL_429qwer0wqweru',
plan_name => 'SQL_PLAN_45b3233fassfdqwq24');
exe_name := dbms_spm.execute_evolve_task (task_name => tk_name);
evol_cout := dbms_spm.report_evolve_task ( task_name => tk_name, execution_name=> exe_name);
dbms_output.put_line(evol_out);
end;
/
--clean up
exec dbms_sqltune.drop_sqlset("MY_SYS');
--SQL Quarantine-- only available on Exadata at this time (March 2020)
ORA-00040
ORA-56955
alter system set RESOURCE_MANAGER_PLAN = 'LIMIT_RESOURCE" scope = memory;
alter system flush shared_pool;
select sql_text, sql_id, ,sql_quarantine, avoided_executions
from v$sql where sql_quarantine is not null;
--clean up
begin
for quarantineObj in (select name from dba_sql_quarantine) loop
sys.dbms_sqlq.drop_quarantine(quarantineObj.name);
end loop;
end;
/
--End of File--
Comments
Post a Comment