怎样使用oracle 的DBMS_SQLTUNE package 来执行 Sql Tuning Advisor 进行sql 自己主动调优
怎样使用oracle 的DBMS_SQLTUNE package 来执行 Sql Tuning Advisor 进行sql 自己主动调优
1》。这里简单举个样例来说明DBMS_SQLTUNE 的使用
首先现运行下某个想要调优的sql,然后获取sqlid
SQL> select * from v$sqltext where sql_text like 'select * from dual%';
ADDRESS HASH_VALUE SQL_ID COMMAND_TYPE PIECE SQL_TEXT
---------------- ---------- ------------- ------------ ---------- ----------------------------------------------------------------
0000000069BC2BE0 942515969 a5ks9fhw2v9s1 3 0 select * from dual
1 row selected.
2》。执行sqltrpt 脚本
sqltrpt 里默认记录两种数据
15 Most expensive SQL in the cursor cache
15 Most expensive SQL in the workload repository
当然这里我们也能够手动输入我们想要调整的其它sql
SQL> @?/rdbms/admin/sqltrpt
15 Most expensive SQL in the cursor cache
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID ELAPSED SQL_TEXT_FRAGMENT
------------- ---------- -------------------------------------------------------
b6usrg82hwsa3 97.69 call dbms_stats.gather_database_stats_job_proc ( )
6gvch1xu9ca3g 38.88 DECLARE job BINARY_INTEGER := :job; next_date DATE := :
cvn54b7yz0s8u 21.34 select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length
dbvkky621gqtr 16.22 SELECT /*+ parallel */ EXTRACTVALUE(VALUE(T), '/select_
3ktacv9r56b51 9.68 select owner#,name,namespace,remoteowner,linkname,p_tim
ga9j9xk5cy9s0 7.01 select /*+ index(idl_sb4$ i_idl_sb41) +*/ piece#,length
39m4sx9k63ba2 6.09 select /*+ index(idl_ub2$ i_idl_ub21) +*/ piece#,length
8swypbbr0m372 5.90 select order#,columns,types from access$ where d_obj#=:
db78fxqxwxt7r 5.62 select /*+ rule */ bucket, endpoint, col#, epvalue from
g5m0bnvyy37b1 5.38 select sql_id, plan_hash_value, bucket_id, begin
424h0nf7bhqzd 5.02 SELECT sqlset_row(sql_id, force_matching_signature,
SQL_ID ELAPSED SQL_TEXT_FRAGMENT
------------- ---------- -------------------------------------------------------
32hbap2vtmf53 4.31 select position#,sequence#,level#,argument,type#,charse
9s0xa5dgvuq55 4.29 DECLARE job BINARY_INTEGER := :job; next_date TIMESTAM
d4taszv1bpc0w 4.02 DECLARE cnt NUMBER; bid NUMBER; eid
96g93hntrzjtr 3.78 select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null
15 Most expensive SQL in the workload repository
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID ELAPSED
------------- ----------
SQL_TEXT_FRAGMENT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
b6usrg82hwsa3 198.03
call dbms_stats.gather_database_stats_job_proc ( )
6gvch1xu9ca3g 169.58
DECLARE job BINARY_INTEGER := :job; next_date DATE := :
1jqcpqf8fpdr8 139.13
select count(*) from dba_objects a, dba_objects b where
SQL_ID ELAPSED
------------- ----------
SQL_TEXT_FRAGMENT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
cvn54b7yz0s8u 82.99
select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length
f6cz4n8y72xdc 63.29
SELECT space_usage_kbytes FROM v$sysaux_occupants WH
6mcpb06rctk0x 44.62
call dbms_space.auto_space_advisor_job_proc ( )
SQL_ID ELAPSED
------------- ----------
SQL_TEXT_FRAGMENT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3ktacv9r56b51 42.79
select owner#,name,namespace,remoteowner,linkname,p_tim
12a2xbmwn5v6z 39.87
select owner, segment_name, blocks from dba_segments wh
05s9358mm6vrr 37.59
begin dbms_feature_usage_internal.exec_db_usage_samplin
SQL_ID ELAPSED
------------- ----------
SQL_TEXT_FRAGMENT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
5zruc4v6y32f9 33.12
DECLARE job BINARY_INTEGER := :job; next_date TIMESTAM
dbvkky621gqtr 31.66
SELECT /*+ parallel */ EXTRACTVALUE(VALUE(T), '/select_
63n9pwutt8yzw 28.03
MERGE /*+ dynamic_sampling(ST 4) dynamic_sampling_est_c
SQL_ID ELAPSED
------------- ----------
SQL_TEXT_FRAGMENT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
7xa8wfych4mad 27.86
SELECT SUM(blocks) FROM x$kewx_segments WHERE segment
8swypbbr0m372 26.81
select order#,columns,types from access$ where d_obj#=:
db78fxqxwxt7r 26.37
select /*+ rule */ bucket, endpoint, col#, epvalue from
Specify the Sql id
~~~~~~~~~~~~~~~~~~
Enter value for sqlid: a5ks9fhw2v9s1
Sql Id specified: a5ks9fhw2v9s1
Tune the sql -----------------------------------------------这里为sql tuning advisor 的 建议
~~~~~~~~~~~~
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : TASK_219
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 05/17/2014 17:07:54
Completed at : 05/17/2014 17:07:54
-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID : a5ks9fhw2v9s1
SQL Text : select * from dual
-------------------------------------------------------------------------------
There are no recommendations to improve the statement.
-------------------------------------------------------------------------------
备注:在生产环境下没有測试过,不知道Sql Tuning Advisor 的效果怎样,这个有待然后验证下!
相关文章
- Oracle数据库:oracle执行计划性能代价cost,全表扫描速度慢,索引扫描速度快
- Oracle数据库:oracle组函数,聚合函数,多行函数,avg,sum,min,max,count,group by,having
- Oracle报错:ORA-06508: PL/SQL: 无法找到正在调用的程序单元
- 【Docker】安装Presto连接Hive、mysql、oracle、postgresql、SQL server等7种类型数据库
- 《Oracle PL/SQL必知必会》——第1章 了解SQL
- ORACLE PL/SQL 程序包的创建与应用
- PL/SQL不安装Oracle连接,Oracle instantclient安装
- oracle函数的分类
- 转: oracle 存储过程 执行动态 实现sql
- 转: Windows下安装Oracle Database 12c Release 1(12.1.0.2.0) - Enterprise Edition
- sql:SQL Server metadata queries
- 浅析SQL中 in 与 exists 用法的区别及其各自执行流程、not in/not exists区别、sql优化应该如何选择in还是exists
- Oracle 性能调优之:使用 V$SQL_PLAN 视图查询内存中的执行计划
- Oracle数据库 | SQL语句执行计划、语句跟踪与优化实例
- [Oracle工程师手记] 利用 DBMS_SQLTUNE.report_sql_monitor 生成 SQL 语句的监控信息
- [Oracle工程师手记]如何找到 RMAN 的所有 session
- [Oracle工程师手记]Linux环境中,反复调用SQLPLUS 执行SQL语句的例子
- 【翻译自mos文章】检查$ORACLE_HOME是否是RAC的HOME的方法以及relink RAC的Oracle binary的方法
- Oracle rownum影响运行计划
- Oracle数据库案例整理-Oracle系统执行失败-sql_trace至TRUE导致Oracle在根文件夹中缺乏可用空间
- 怎样使用oracle 的DBMS_SQLTUNE package 来执行 Sql Tuning Advisor 进行sql 自己主动调优
- 【sql server压测】sql server使用Diskspd测试SQL Server IO存储
- Oracle修改密码后,登录sys或者sysdba时一直提示:ORA-01017: invalid username/password; logon denied
- Mybatis+Oracle搭配insert空值报错之myBatis+mysql驱动+oracle驱动的源码分析