分类目录归档:读书写字

SQL Tuning Health-Check Script (SQLHC) (文档 ID 1366133.1)

Applies to: 
Oracle Database – Personal Edition – Version 10.2.0.1 and later
Oracle Database – Enterprise Edition – Version 10.2.0.1 and later
Oracle Database – Standard Edition – Version 10.2.0.1 and later
Oracle Database Products > Oracle Database Suite > Oracle Database
Information in this document applies to any platform.

Purpose 

Download the SQL Tuning Health-Check Script

SQL Health Check

Download the SQLHC Script Here (.zip archive).

 

What is the SQL Tuning Health-Check Script (SQLHC)?

The SQL Tuning Health-Check Script is a tool developed by the Oracle Server Technologies Center of Expertise. The tool, also known as SQLHC, is used to check the environment in which a single SQL Statement runs, checking Cost-based Optimizer (CBO) statistics, schema object metadata, configuration parameters and other elements that may influence the performance of the one SQL being analyzed.

Licensing (SQLHC requires no license and is FREE)

As in the title of this section, SQLHC requires no license and is FREE. 
Since SQLHC can use information from AWR reports if the Diagnostic or Tuning pack are installed, it asks if these packs are licensed at your site. For more details refer to the licensing section in the SQLHC FAQ:

Document 1454160.1 FAQ: SQL Health Check (SQLHC) Frequently Asked Questions
 

Overview of the SQL Tuning Health-Check Script

An overview video about SQLHC is available here :

Document 1455583.1 SQL Tuning Health-Check Script (SQLHC) Video


The intention of SQLHC is to allow users to avoid SQL Performance from being affected by avoidable problems by ensuring that the environment that an individual SQL runs in is sound.

It does this while leaving "no database footprint" ensuring it can be run on all systems.

When executed for one SQL_ID, this script generates an HTML report with the results of a set of health-checks around the one SQL statement provided. You can find the SQL_ID of a statement from an AWR or ASH report or you can select it from the database using the V$SQL view. See:

Document 1627387.1 How to Determine the SQL_ID for a SQL Statement

Health-checks are performed over:
•CBO Statistics for schema objects accessed by the one SQL statement being analyzed
•CBO Parameters
•CBO System Statistics
•CBO Data Dictionary Statistics
•CBO Fixed-objects Statistics

NOTE: A webcast has been recorded entitled: "How to Improve SQL Performance with the New Health Check Tool?".

This can be found, along with many other recorded webcasts, here:

Document 740964.1 Advisor Webcast Archived Recordings

A FAQ for the SQL Healthcheck script (SQLHC) can be found here:


Document 1417774.1 FAQ: SQLHC HealthCheck Frequently Asked Questions

Additionally, we welcome any additional health-checks that you may suggest. If any specific health-checks are needed and not covered by this script, then as long as the health-check can be produced with a SQL Command (leaving no database footprint ) then these can be implemented in future versions.

Please add comments to this Document for any desired additions.
 

Best Practices

Pro-Active Problem Avoidance and Diagnostic Collection

Although some problems may be unforeseen, in many cases problems may be avoidable if signs are detected early enough. Additionally, if an issue does occur, it is no use collecting information about that issue after the event. SQLHC is one of the tools that support recommend for collecting such diagnostics. For information on suggested uses, other proactive preparations and diagnostics, see:

Document 1482811.1 Best Practices: Proactively Avoiding Database and Query Performance Issues
Document 1477599.1 Best Practices Around Data Collection For Performance Issues

Ask Questions, Get Help, And Share Your Experiences With This Article

Would you like to explore this topic further with other Oracle Customers, Oracle Employees, and Industry Experts?

Click here to join the discussion where you can ask questions, get help from others, and share your experiences with this specific article.
Discover discussions about other articles and helpful subjects by clicking here to access the main My Oracle Support Community page for Database Tuning.

Requirements

Execute this script from SQL*Plus connecting as SYS, DBA or a user with access to Data Dictionary views.

NOTE: The script ADDS NO OBJECTS TO THE DATABASE. It simply reports and advises on existing objects

Configuring

There is no configuration required.

Instructions
1.Login to the database server and set the environment used by the Database Instance
2.Download the "sqlhc.zip" archive file and extract the contents to a suitable directory/folder
3.Connect into SQL*Plus as SYS, a DBA account, or a user with access to Data Dictionary views and simply execute the "sqlhc.sql" script. It will request to enter two parameters:

i.Oracle Pack License (Tuning, Diagnostics or None) [T|D|N] (required)
If site has both Tuning and Diagnostics licenses then specify T (Oracle Tuning pack includes Oracle Diagnostics)


ii.A valid SQL_ID for the SQL to be analyzed.  


For Example:

# sqlplus / as sysdba
SQL> START sqlhc.sql T djkbyr8vkc64h

#SQLHC报告说明

1_health_check.html
    Observations:显示health-checks输出的可能存在问题的项目,确认是否需要更改.例如:对象统计信息过旧
    SQL Text:要检查的sql文本
    Tables Summary:显示相关表的统计信息大概
    Indexes Summary:显示相关索引的统计信息大概
2_diagnostics.html
    SQL Text:要检查的sql文本
    SQL Plan Baselines (DBA_SQL_PLAN_BASELINES):DBA_SQL_PLAN_BASELINES中此sql的plan history
    SQL Profiles (DBA_SQL_PROFILES):DBA_SQL_PROFILES中此sql的profiles
    SQL Patches (DBA_SQL_PATCHES):DBA_SQL_PATCHES中此sql的相关patches
    Cursor Sharing and Reason:GV$SQL_SHARED_CURSOR是此sql的cursor sharing
    Cursor Sharing List:GV$SQL_SHARED_CURSOR中此sql的cursor sharing
    Current Plans Summary (GV$SQL):GV$SQL中此sql的平均消耗
    Current SQL Statistics (GV$SQL):GV$SQL中此sql的消耗
    Historical Plans Summary (DBA_HIST_SQLSTAT):DBA_HIST_SQLSTAT中此sql的历史平均消耗
    Historical SQL Statistics – Delta (DBA_HIST_SQLSTAT):DBA_HIST_SQLSTAT中此sql的历史消耗
    Historical SQL Statistics – Total (DBA_HIST_SQLSTAT):DBA_HIST_SQLSTAT中此sql的历史消耗
    Active Session History by Plan (GV$ACTIVE_SESSION_HISTORY):GV$ACTIVE_SESSION_HISTORY中此sql的session state统计
    Active Session History by Plan Line (GV$ACTIVE_SESSION_HISTORY):GV$ACTIVE_SESSION_HISTORY中此sql的plan line统计
    AWR Active Session History by Plan (DBA_HIST_ACTIVE_SESS_HISTORY):DBA_HIST_ACTIVE_SESS_HISTORY中此sql的session state统计
    AWR Active Session History by Plan Line (DBA_HIST_ACTIVE_SESS_HISTORY):DBA_HIST_ACTIVE_SESS_HISTORY中此sql的plan line统计
    DBMS_STATS System Preferences:DBMS_STATS系统参数
    Tables:表相关的统计信息
    DBMS_STATS Table Preferences:DBMS_STATS表参数
    Table Columns:列相关的统计信息
    Table Partitions:表分区相关的统计信息
    Table Constraints:表上的约束信息
    Tables Statistics Versions:表相关的统计信息
    Indexes:索引相关的统计信息
    Index Columns:索引列相关的统计信息
    Index Partitions:索引分区相关的统计信息
    Indexes Statistics Versions:索引相关的统计信息
    System Parameters with Non-Default or Modified Values:GV$SYSTEM_PARAMETER2中isdefault = 'FALSE' OR ismodified != 'FALSE'的参数
    Instance Parameters:V$SYSTEM_PARAMETER2中的系统参数
    Metadata:表和索引的建立语句
3_execution_plans.html
    SQL Text:要检查的sql文本
    Current Execution Plans (last execution):按child cursor显示此sql的内存中的执行计划
    Current Execution Plans (all executions):按child cursor显示此sql的内存中的执行计划
    Historical Execution Plans:按child cursor显示此sql的awr的执行计划
4_sql_detail.html
    图形化显示sql的运行统计
5_sql_monitor.zip
    图形化显示sql的监控信息
6_10053_trace_from_cursor.trc
    显示sql的10053跟踪文件
8_sqldx.zip
    sql health check数据来源
9_log.zip
    sql health check生成日志

对于SQLHC(SQL Health Check,SQL性能健康检查脚本)工具,Mos文档1626277.1有非常详细的介绍。SQLHC是Oracle Server Technologies Center of Expertise开发的一个工具。SQLHC用于检查单条SQL语句运行的环境,包括基于成本的优化器(CBO)的统计数据,用户对象的元数据定义,配置参数和其它可能影响到目标SQL性能的因素。SQLHC和SQLT工具一样,本身都是免费的,不需要任何许可证(License)。当对某一个SQL_ID运行SQLHC后,该脚本会生成一系列针对该SQL语句健康检查的一份HTML报告。SQLHC会检查的内容包括:① 待分析的单条SQL涉及的用户对象的CBO统计信息;② CBO参数;③ CBO系统统计信息;④ CBO数据字典统计信息;⑤ CBO固定对象(Fixed-Objects)统计信息。

SQLHC运行时不会在数据库中创建任何对象(“数据库中不留足迹”),它只是对已有的对象提供报告和建议,可以确保它在所有系统上运行。SQLHC的脚本需要以SYS、DBA或者能访问数据字典视图的用户通过SQL*Plus连接运行。SQLHC一共包含3个脚本,分别为sqlhc.sql、sqldx.sql和sqlhcxec.sql,其中sqlhc.sql里边会调用sqldx.sql脚本。sqlhcxec.sql是单独执行的,不过该脚本需要输入一个脚本文件作为入参,而且该脚本文件可以包含绑定变量。

本地测试:http://blog.itpub.net/28539951/viewspace-2100920/

SQL 性能健康检查脚本 (SQLHC) (文档 ID 1626277.1)

表收集信息(防止过旧信息):

begin
  fnd_stats.GATHER_TABLE_STATS('SYS', 'TAB$');
end;

EBS系统数据库统计信息收集总结- gather_schema_stats [final]_ITPUB博客

分析一个SCEHMA (EBS系统) :
exec  fnd_stats.GATHER_TABLE_STATS('PA');

分析一个SCEHMA (一般系统) :
exec dbms_stats.gather_schema_stats(
  ownname          => 'HRM',
  options          => 'GATHER AUTO',
  estimate_percent => dbms_stats.auto_sample_size,
  method_opt       => 'for all columns size repeat', — 稳定后推荐repeat
      cascade          =>TRUE,
  degree           => 12
    )

–一天内执行的sql

select a.*,b.*
  from v$sqltext a, v$session b
 where a.sql_id = b.sql_id
   and b.logon_time between sysdate – 1 and sysdate;

 

select * from dba_hist_active_sess_history t;

扩展:

All About the SQLT Diagnostic Tool (文档 ID 215187.1)

职场上,什么最痛?

职场上,什么最痛?

是升不了职吗?是加不了薪吗?如果你不断地追问自己,你会发现,都不是,你真正的焦虑来自于:你,看不清未来的道路。

你刚刚加入一个新公司,你不知道三个月以后能不能融得进去,你不知道怎么能让老板和同事都喜欢你;你在一家公司干了两三年了,干的都是重复的事,日复一日,年复一年,你不知道这样无休止的重复,什么时候是个头;你在一家公司干了五八年了,你的领导过得是什么样的生活,你看得一清二楚,他不就是你未来的样子吗?你憎恨这一眼看得到头的工作,但是你舍得走吗?

其实所有的问题都源自于你不知道五年以后你能坐到什么位置、管多少人、挣多少钱,你不知道你挣的那些钱,能给你和你的家人什么样的生活;你不知道二十年以后,你拿什么养活自己?

你的焦虑,其实来自于你对未来的不确定。

我们很多人都有个误区,认为职业发展,就是把职场当成了宫斗剧。

有些人,宫斗剧看多了,他们不停地算计别人,不停地希望获取上司和老板的好感。就像一个已经得宠的嫔妃,还是希望每天晚上皇上都来。他们以为,一定有那样一个数字,攒够了,就升了。比如皇上来宠幸100次,就升皇后了。所以他们攒着,盼着,算计着,很怕别人先被宠幸了100次,很怕自己没到100次就失宠了。

事实上,这是职场的真相吗?

关于职场,你需要获取的第一个认知就是:在初期95%的竞争中,你碰到的都是配角!

就像宫斗剧里,那些额头贴着“坏蛋”的人,基本一出场,你就能判断这个人活不过两集。他们的存在,就是为了衬托你这个主角的光环。

所以,这个时候,对你来说最重要的,是一边看着其他人犯错,一边偷偷地投资自己,完善自己的能力模型。等一个特定的机会出现的时候,其他人死伤得差不多了,这个时候老板掐指一算,谁有本事去领这个事,结果你在还活着的人里排第一,就算老板之前没宠幸过你几次,他也会选你。

选了你,你的机会就来了,你的台阶就上去了,你就进入下一个战斗区了。之前的那些所谓对手,斗死斗活,都跟你不在一个层面竞争了。这,就是最基础的职场真相。

所以,如何找准自己的能力模型定位,如何比别人先一步看到机会,如何在机会出现的时候稳准狠地拿下,这才是混职场的核心。那些人际关系的小套路,那些周几找老板谈加薪的小技巧,你会,更好;不会,也不耽误大事。

转载自:https://mp.weixin.qq.com/s/_nLfS7LpMU_QB_waodPP0A