Oracle 如何使用 SQLT 进行 SQL 调优

作者 | JiekeXu

如需转载请联系授权 | (个人微信 ID:JiekeXu_DBA)

SQLT main methods 连接到数据库并收集执行计划、基于成本的优化器 CBO 统计信息、架构对象元数据、性能统计信息、配置参数以及影响正在分析的一个 SQL 的性能的其他元素。

在安装此工具期间,将创建 2 个用户 SQLTXADMIN 和 SQLTXPLAIN 以及一个角色 SQLT_USER_ROLE。sql 存储库由用户 SQLTXPLAIN 拥有。每次使用任何 main 方法时,sql 用户都需要提供 SQLTXPLAIN 密码。SQLTXPLAIN 用户被授予以下系统权限:

CREATE SESSION

CREATE TABLE

PL/SQL包和视图的 SQL 集由用户SQLTXADMIN拥有。该 SQLTXADMIN 用户被锁定并由随机密码标识。SQLTXADMIN 被授予以下系统特权:

ADMINISTER SQL MANAGEMENT OBJECT

ADMINISTER SQL TUNING SET

ADVISOR

ALTER SESSION

ANALYZE ANY

SELECT ANY DICTIONARY

SELECT_CATALOG_ROLE

所有的 SQL 用户在使用任何main方法之前都必须被授予 SQLT_USER_ROLE。该 SQLT_USER_ROLE 角色被授予以下系统权限:

ADVISOR

SELECT_CATALOG_ROLE

注意:不建议使用 SYS 或其他 DBA 账户来运行主要方法,因为收集可能会失败。授予 SQLT_USER_ROLE 并运行主要方法的最佳用户是应用程序的所有者。

如果必须使用 SYS 或其他 DBA 账户,那么在 12c 中,由于 PL/SQL 的安全模型的更改,需要手动执行额外的授权。要解决此更改,需要在 SYS 或 DBA 账户上授予 SQLTXADMIN 用户 INHERIT PRIVILEGES 继承特权。

GRANT INHERIT PRIVILEGESONUSERSYSTO SQLTXADMIN;

下载 SQLTMOS 下载地址:Download SQLTXPLAIN (SQLT) (Doc ID 215187.1)

sqlt_10g_11g_12c_18c_19c_5th_June_2020.zip,也可添加我个人微信【JiekeXu_DBA】获取。

墨天轮下载地址:https://www.modb.pro/doc/86093

安装方法unzip sqlt_10g_11g_12c_18c_19c_5th_June_2020.zip

以 SYS 用户执行 ./sqlt/install/sqcreate.sql 脚本,需要输入以下信息。

这里说一下 Oracle pack license 这里,SQLT 本身不需要 licence,输入 T 的话收集的结果会包含 sql tuning 结果,输入 D 的话会包含 awr 报告信息,输入 N 这里就不包含这两项信息。如果不想使用 SQLT 的话可以使用脚本 ./sqlt/install/sqdrop.sql 直接卸载。

安装过程示例这里以单机 12.2.0.1 多租户环境为例,首先进入到容器 JIEKEXUPDB1 下,可创建 SQLT 用户 SQLTXPLAIN,也可以不用建,跑脚本时自动创建 SQLTXPLAIN 用户。

然后执行脚本 @sqcreate.sql 输入连接串,创建 SQLT 用户的密码以及默认表空间等信息。

输入 T 后稍等一会当看到如下信息说明安装成功。

SQLT users must be granted SQLT_USER_ROLE before using this tool.

SQCREATE completed. Installation completed successfully.

然后可以把 SQLT 角色 SQLT_USER_ROLE 给予其他业务用户,例如 SCOTT 等其他业务用户。

涉及到的主要 SQL

SYS@JiekeXu> alter session set container=JIEKEXUPDB1;SYS@JiekeXu> create user SQLTXPLAIN identified by SQLTXPLAIN; --当然也可以不用提前创建用户SYS@JiekeXu> grant CREATE SESSION,CREATE TABLE TO SQLTXPLAIN;SYS@JiekeXu> @sqcreate.sql adding: 221113152749_01_sqcreate.log (deflated 87%)Optional Connect Identifier (ie: @PROD): @JIEKEXUPDB1 <--- 输入 TNS 连接名

Password for user SQLTXPLAIN: <--- 输入专用用户密码Re-enter password: <--- 再次输入专用用户密码

Type YES or NO [Default NO]: YES <--- 输入专用用户表空间和临时表空间名,大写Default tablespace [USERS]: USERSTemporary tablespace [TEMP]: TEMPMain application user of SQLT: SQLTXPLAIN <--- 输入专用用户名,大写

"T"if you have license for Diagnostic and Tuning"D"if you have license only for Oracle Diagnostic"N"if you do not have these two licenses

Oracle Pack license [T]: T <--- 输入 license T SQLT users must be granted SQLT_USER_ROLE before using this tool.

SQCREATE completed. Installation completed successfully.

SYS@JiekeXu> GRANT INHERIT PRIVILEGES ON USER SYS TO SQLTXADMIN;SYS@JiekeXu> grant SQLT_USER_ROLE to sys;SYS@JiekeXu> grant SQLT_USER_ROLE to SCOTT;

主要的执行方法一般是 sqltxtract.sql 加 sqlid, 输入 SQLT 密码,然后便会生成 sqlt+日期+sqlid 结尾的 zip 包,解压后内容很多,我们一般情况下只需要关注 sqlt*lite.html 和 sqlt*main.html 结尾的文件就好,sqltlite.html 算是轻量级的 SQLT,sqltmain.html 是详细的信息。XTRACT Method、XECUTE Method 等其他方法可参考 SQLT 的安装介绍文档:sqlt_instructions.html

SCOTT@jiekexupdb1> selectcount(*) from scott.t a,scott.test b where a.object_id=b.object_id /*JIekeXu*/;

COUNT(*)----------72783SCOTT@jiekexupdb1> select sql_id,sql_text from v$sql where sql_text like '%JIekeXu%' and sql_text not like '%like%';

SQL_ID-------------SQL_TEXT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------4mvsgjurg63fdselectcount(*) from scott.t a,scott.test b where a.object_id=b.object_id /*JIekeXu*/

SCOTT@jiekexupdb1>@/home/oracle/tmp/SQLT/sqlt/run/sqltxtract.sql 4mvsgjurg63fd

--然后输入 SQLT 密码即可。

13:57:03 SYS@test> @/u01/soft/SQLT/sqlt/run/sqltxtract.sql 66jty4hfyt8mh SQLTXPLAIN     --也可以直接跟 sqltxplain 密码再次说明不建议使用 SYS 或其他 DBA 账户来运行 main 方法,因为集合可能会失败。

授予和运行 main 方法的最佳用户是应用程序的所有者。

执行期间可查看这个视图监控执行过程:

SELECT * FROM SQLTXADMIN.sqlt$_log_v;

然后会生成一个以 sqlt+日期+sqlid_S.zip 的文件,sqlt_20221116_1428_60jj9axkt0v9u_S.zip 解压后有如下 20 个文件:

sqlt_s51483_10053_i1_c1_extract.trcsqlt_s51483_cell_state.zipsqlt_s51483_main.htmlsqlt_s51483_sqldx.zipsqlt_s51483_tcx.zipsqlt_s51483_addmrpt_0007.zipsqlt_s51483_driver.zipsqlt_s51483_opatch.zipsqlt_s51483_tcb.zipsqlt_s51483_tc.zipsqlt_s51483_ashrpt_0007.zipsqlt_s51483_lite.htmlsqlt_s51483_readme.htmlsqlt_s51483_tc_script.sqlsqlt_s51483_trc.zipsqlt_s51483_10053_explain.trc  sqlt_s51483_awrrpt_0007.zip          sqlt_s51483_log.zip         sqlt_s51483_sql_detail_active.html  sqlt_s51483_tc_sql.sql

sqlt*lite.html 内容

这个文件算是轻量级的 sqlt 了,里面包含六大块内容,主要涉及到表、索引、索引列、执行计划这些信息。

sqlt*main.html 内容

215187.1 SQLT XTRACT 19.1.200226 Report: sqlt_s51484_main.html

Main 文件内容更加丰富,主要包含以下八类信息。带有下划线的内容点击均可进入到相应的模块,大家可自行点击查看,尤其 Golbal 相关的信息,更为重要,建议大家详细查看。

如下示例,列出了数据库中非默认的优化器参数及参数值。

SQLHC这里顺便说一句比 SQLT 更简洁的 sqlhc 工具,这个工具收集的信息也很全面,值得大家尝试,研读。

执行方法:sqlplus/ as sysdbaSQL>@/home/oracle/tmp/sqlhc.sql T 9a4tv1dduu9u4或者SQL>@/home/oracle/tmp/sqlhc.sqlParameter1:OraclePack License (Tuning, Diagnostics or None) [T|D|N] (required)Entervalue for 1: TPL/SQLprocedure successfully completed.Parameter2:SQL_IDof the SQL to be analyzed (required)Entervalue for 2: 9a4tv1dduu9u4 <----输入 sql_id 等待 5 分钟左右

时间有可能更长或者更短(根据 AWR 保存周期、字典表大小不同相差较大,一般系统应该在 5 分钟以内能够完成),对数据库没有影响。执行过程有 log,也有屏幕输出。执行过程会 insert 数据到 plan_table 表,执行结束时会 rollback。

结束后生成的文件名以 sqlhc 开头,依次是日期、时间、sql_id。类似这样:sqlhc_20211125_1810_9a4tv1dduu9u4.zip

其中 4 个 html 文件和 log.zip 是通常存在的。

10053 trace 文件的生成需要 11.2 版本以上,sql_id 仍在 library cache 内的情况下。

如果 sql_monitor.zip 也包含在 sqlhc 压缩包内,说明 SQL 执行时间超过了 5s,或者是并行的 SQL,而且收集 sqlhc 时仍保留在 sql monitor 的内存里。sql monitor 对分析 sql 执行计划有很大帮助,如果遇到问题收集 sqlhc 信息及时,就非常有可能收集到 sql monitor 文件。如果一个 sql 执行完后超过半小时没有收集 sqlhc,sql monitor 信息就就非常有可能被刷出内存。

主要分析的的 3 个 html 文件是:

*_health_check.html*_diagnostics.html*_execution_plan.html

内容也非常丰富,可以多收集一些看看,那么今天就到这里啦。

参考资料All About the SQLT Diagnostic Tool (Doc ID 215187.1)SQLT Usage Instructions (Doc ID 1614107.1)SQL Tuning Health-Check Script (SQLHC) (Doc ID1366133.1)How ToCollect10046Trace (SQL_TRACE) DiagnosticsforPerformance Issues (Doc ID376442.1)How toCreate a SQL-testcase Using the DBMS_SQLDIAG Package(Doc ID727863.1)MonitoringSQL statements withReal-TimeSQLMonitoring (DocID 1380492.1)

全文完,希望可以帮到正在阅读的你,如果觉得此文对你有帮助,可以分享给你身边的朋友,同事,你关心谁就分享给谁,一起学习共同进步~~~

----------------------------

CSDN :https://blog.csdn.net/JiekeXu

墨天轮:https://www.modb.pro/u/4347

腾讯云:https://cloud.tencent.com/developer/user/5645107

----------------------------

Oracle 表碎片检查及整理方案

Oracle 19c RAC 遇到的几个问题OGG|Oracle 数据迁移后比对一致性利用 OGG 迁移 Oracle11g 到 19COGG|Oracle GoldenGate 微服务架构Oracle 查询表空间使用率超慢问题一则国产数据库|TiDB 5.4 单机快速安装初体验Oracle ADG 备库停启维护流程及增量恢复Linux 环境搭建 MySQL8.0.28 主从同步环境

「点点赞赏,手留余香」

赞赏

  • 0人赞过
0
0
0
评论 0 请文明上网,理性发言

相关文章

  • 前期准备: 一台windows10操作系统的电脑 Oracle11g安装包,sqldevleoper安装包 (百度网盘提取链接:https://pan.baidu.com/s/15FxijLNpTSbYn1CQdKnWqQ 提取码:eqjl,不想使用百度网盘的可以自行百度至官网下载) 安装Oracle解压缩文件后点击可
    全球影视Ozi5 5 0 0 条评论
  • 微酒论坛再度来袭!10月22日-10月25日,湖南?长沙秋季糖酒会我们不见不散! 秋糖论坛,要听就听权威的、专业的顶级论坛。 10月23日下午,长沙明城国际大酒店,酒业外脑、行业第一咨询公司--盛初咨询的最新研究成果"214模式"首次公开发布!还不够?再加上世界百强企业重磅大咖压阵如何? 最新内部消息,世界百强企业甲骨
    景景景景行- 6 0 0 条评论
  • 2014年6月10日,OracleCEO拉里·埃里森在总部宣布了一项突破性的创新技术:OracleDatabaseIn-Memory。和Multitenant一样,DatabaseIn-Memory是Oracle数据库12c版本中的一个全新选件,是继Exadata之后重要的数据库创新之一。今天,Oracle所倡导的融
    浪痰湍妆 9 0 0 条评论
  • 编辑|舞春秋 来源|至顶网 Oracle近日在美国拉斯维加斯举行的OracleCloudWorld大会上推出了一款用于支持本地云环境的基础设施平台,包括多个新的公有云服务、以及多项对数据库产品组合的重大更新。Oracle近日在美国拉斯维加斯举行的OracleCloudWorld大会上推出了一款用于支持本地云环境的基础
    勿畏1 7 1 0 条评论
  • 源/云头条一位Oracle程序员在HackerNews上吐槽自己的工作,引起了热议,内容如下: Oracle数据库12.2。它有近2500万行C代码。 这实在太恐怖了,简直难以想象!你做不到在不破坏成千上万个现有测试的情况下更改产品中的单单一行代码。好几代程序员在很紧的项目期限内编写了这些代码,代码中充斥着各种各样的
    风荡人间 4 0 0 条评论
  • 文|局长 出品|OSC开源社区(ID:oschina2013) DB-Engines数据库流行度排行榜发布了2月份的更新: 可以看到,TOP20的数据库中只有Snowflake的排名上升,从上个月的第17名上升至现在的第15名。分数更是比去年同期增加了64.96分。 至于三巨头--Oracle、MySQL和Micro
    竹子澀苜 5 1 0 条评论
  • DB-Engines发布了2018年1月份的数据库排名。排前20名的数据库中,Oracle稳居第一,Cassandra超过Redis,夺回第8的位置。第14名的Solr被第13名的SAPAdaptiveServer赶超,SQLite有小幅回升趋势。整体排名没有太大的变化。 完整排名请查看:https://db-en
    meiyit388 7 1 0 条评论
  • 编辑|田晓旭 Oracle中国区研发中心裁员消息最终被确认。 5月7日上午,Oracle召开了面向全中国区的电话会议,亚太区人力资源负责人在会上简要介绍道,公司正进行业务结构调整,导致一部分人要离开岗位,这将是全球性的。短暂介绍后,没有任何提问环节,Oracle北京地区便开始了一对一面谈,介绍赔偿内容。 据悉,此次主
    Jazery2009 7 0 0 条评论
  • 全球都在去Oracle化,曾经的纯软件巨头未来该何去何从? Oracle狠甩MySQL,涨幅大增 近日,DB-Engines最新发布了2018年8月数据库排名榜,Oracle稳居第一,同时是本月涨幅最大的数据库技术,涨幅高达34.24,狠甩第二名MySQL。 亚马逊:2020年第一季度,我们将完全弃用Oracle!
    土豆足球83 4 5 0 条评论
  • 作者|李冬梅 西方对俄罗斯的全面制裁,可能会加快中国在基础软件等"卡脖子"技术上自力更生的步伐。 1全球多家科技巨头宣布制裁俄罗斯 随着俄乌战争局势日益焦灼,西方各国相继宣布了要全面制裁俄罗斯的消息,美国更是将俄罗斯列入科技出口管制名单中。令人意外的是,俄乌战争影响范围之广,几乎波及了全球科技网。以谷歌、苹果、微软、
    xiao6663 7 0 0 条评论