没有合适的资源?快使用搜索试试~ 我知道了~
首页SQL Tuning Advisor使用总结
资源详情
资源评论
资源推荐
SQL Tuning Advisor
1 input:
Automatic Database Diagnostic Monitor
High-load SQL statements --awr report hight-load sql
Cursor cache
SQL Tuning Set
2 Tuning Options
limited
statistics checks, access path analysis, and SQL structure analysis. SQL Profile recommendations
are not generated
comprehensive
comprehensive option you can also specify a time limit for the tuning task, which by default is 30
minutes.
3 output
After analyzing the SQL statements, the SQL Tuning Advisor provides advice on
optimizing the execution plan, the rationale for the proposed optimization, the
estimated performance benefit, and the command to implement the advice. You
simply have to choose whether or not to accept the recommendations to optimize the
SQL statements.
4 DBMS_SQLTUNE
Using SQL Tuning Advisor APIs
A SQL tuning task can be created for a single SQL statement. For tuning
multiple statements, a SQL Tuning Set (STS) has to be rst created. An STS
is a database object that stores SQL statements along with their execution
context.
4.1 创建优化任务
--创建优化任务 Creating a SQL Tuning Task
--create_tuning_task 可传递如下三个参数:
The text of a SQL statement
The reference (sql_id) to a SQL statement stored in the shared pool
The reference (sql_id) to a SQL statement stored in the Automatic Workload Repository
The name of a SQL tuning set
--创建优化任务的方法有几种,具体如下所示:
SET SERVEROUTPUT ON
4.1.1 从 awr 报告中获取 sql
-- Tuning task created for specific a statement from the AWR.
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
begin_snap => 87,
end_snap => 88,
sql_id => '7gtztzv329wg0',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => '17gtztzv329wg0_AWR_tuning_task',
description => 'Tuning task for statement 19v5guvsgcd1v in AWR.');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/
4.1.2 从 cursor 中获取 sql
-- Tuning task created for specific a statement from the cursor cache.
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => '19v5guvsgcd1v',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => '19v5guvsgcd1v_tuning_task',
description => 'Tuning task for statement 19v5guvsgcd1v.');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/
4.1.3 从 sql set 中获取 sql 组
-- Tuning task created from an SQL tuning set.
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sqlset_name => 'test_sql_tuning_set',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => 'sqlset_tuning_task',
description => 'Tuning task for an SQL tuning set.');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/
4.1.4 人为指定 sql
-- Tuning task created for a manually specified statement.
DECLARE
l_sql VARCHAR2(500);
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql := 'SELECT e.*, d.* ' ||
'FROM emp e JOIN dept d ON e.deptno = d.deptno ' ||
'WHERE NVL(empno, ''0'') = :empno';
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_text => l_sql,
bind_list => sql_binds(anydata.ConvertNumber(100)),
user_name => 'scott',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => 'emp_dept_tuning_task',
description => 'Tuning task for an EMP to DEPT join query.');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/
DECLARE
my_task_name VARCHAR2 (30);
my_sqltext CLOB;
BEGIN
my_sqltext := 'select * from v$parameter';
my_task_name := dbms_sqltune.create_tuning_task (sql_text=> my_sqltext,
bind_list => null,
user_name => 'TEST',
剩余16页未读,继续阅读
m21005
- 粉丝: 0
- 资源: 1
上传资源 快速赚钱
- 我的内容管理 收起
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
会员权益专享
最新资源
- 数据结构1800题含完整答案详解.doc
- 医疗企业薪酬系统设计与管理方案.pptx
- 界面与表面技术界面理论与表面技术要点PPT学习教案.pptx
- Java集合排序及java集合类详解(Collection、List、Map、Set)讲解.pdf
- 网页浏览器的开发 (2).pdf
- 路由器原理与设计讲稿6-交换网络.pptx
- 火电厂锅炉过热汽温控制系统设计.doc
- 企业识别CIS系统手册[收集].pdf
- 物业管理基础知识.pptx
- 第4章财务预测.pptx
- 《集成电路工艺设计及器件特性分析》——实验教学计算机仿真系.pptx
- 局域网内共享文件提示没有访问权限的问题借鉴.pdf
- 第5章网络营销策略.pptx
- 固井质量测井原理PPT教案.pptx
- 毕业实习总结6篇.doc
- UGNX建模基础篇草图模块PPT学习教案.pptx
资源上传下载、课程学习等过程中有任何疑问或建议,欢迎提出宝贵意见哦~我们会及时处理!
点击此处反馈
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功
评论2