没有合适的资源？快使用搜索试试~ 我知道了~

首页SQL Tuning Advisor使用总结

SQL Tuning Advisor使用总结

SQL

Tuning

Advisor

5星 · 超过95%的资源需积分: 50 480 浏览量更新于2023-05-26 评论 2 收藏 213KB DOC 举报

身份认证购VIP最低享 7 折!

领优惠券(最高得80元）

试读

17页

关于oracle的SQL Tuning Advisor全面的使用介绍，包括分析awr、cursor、sql_set、sql文本中获取sql并且建立、执行、获取结果的方法。并且介绍了sql_set，sql_profile的操作。里面涉及到的内容都有可执行的代码。

资源详情

资源评论

资源推荐

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页未读，继续阅读

甘甘

2013-08-05

得有点基础的人来看，看的我晕乎乎，不过还是感谢楼主分享，等我了解了此方面内容之后，这个文档就是一个宝贝了。谢谢

人工智能自动sql优化工具–SQLTuning for SQL Server

针对这种情况，人工智能自动SQL优化工具应运而生。现在我就向大家介绍这样一款工具:SQLTuning for SQL Server。 1. SQL Tuning 简介 SQL Turning是Quest公司出品的Quest Central软件中的一个工具。 QuestCentral（图1）是一款集成化、图形化、跨平台的数据库管理解决方案，可以同时管理Oracle、DB2 和 SQL serv

Oracel SQL tuning

Oracel SQL tuning Oracel SQL tuning Oracel SQL tuning Oracel SQL tuning Oracel SQL tuning Oracel SQL tuning

Oracle SQL Tuning with Oracle SQLTXPLAIN: Oracle Database 12c Edition

m21005

粉丝: 0
资源: 1

上传资源快速赚钱

我的内容管理收起

我的资源快来上传第一个资源

我的收益

登录查看自己的收益

我的积分登录查看自己的积分

我的C币登录后查看C币余额

我的收藏

我的下载

下载帮助

会员权益专享

SQL Tuning Advisor使用总结

评论2

会员权益专享

最新资源

SQL Tuning Advisor使用总结

评论2

人工智能自动sql优化工具–SQLTuning for SQL Server

Oracel SQL tuning

Oracle SQL Tuning with Oracle SQLTXPLAIN: Oracle Database 12c Edition

人工智能自动sql优化工具--SQLTuning for SQL Server

oracle sql performance tuning

Oracle SQL优化

高清彩版 oracle sql performance tuning and optimization

SQL Server 2017 Query Performance Tuning 5th Edition

SQL Server 2012 Query Performance Tuning(Apress,3ed,2012)

展讯平台Camera tuning总结.pdf

Deep Dive into Spark SQL with Advanced Performance Tuning

如何进行数据库堆栈信息分析

gpt3.5 fine tuning

openai fine-tuning

Inside Microsoft SQL Server 2008 T-SQL Querying

高通tuning面试题

BERT fine-tuning的中文标题分类实战

Pro SQL Server 2008 Replication pdf

Murach's SQL Server 2016 for Developers

会员权益专享

最新资源