SQL Tuning Health-Check Script

-
SQL Health Check


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.

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:

    1. 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)
    2. A valid SQL_ID for the SQL to be analyzed.  
For Example:
# sqlplus / as sysdba
SQL> START sqlhc.sql T djkbyr8vkc64h



Download Link : https://support.oracle.com/epmos/main/downloadattachmentprocessor?parent=DOCUMENT&sourceId=1366133.1&attachid=1366133.1:SQLHC_TOOL_12108&clickstream=yes
SQL Tuning Health-Check Script (SQLHC) (Doc ID 1366133.1)

Comments

Post a Comment

Popular posts from this blog

RMAN backup Status (Remaining Time and Percentage)

-
On Running a huge database backup we often need to know the status of backup. 1) How much backup is taken. 2) How much is remain. 3) Estimate time to complete the backup. After lot of search i find out this amazing query below. col dbsize_mbytes for 99,999,990.00 justify right head "DBSIZE_MB" col input_mbytes for 99,999,990.00 justify right head "READ_MB" col output_mbytes for 99,999,990.00 justify right head "WRITTEN_MB" col output_device_type for a10 justify left head "DEVICE" col complete for 990.00 justify right head "COMPLETE %" col compression for 990.00 justify right head "COMPRESS|% ORIG" col est_complete for a20 head "ESTIMATED COMPLETION" col recid for 9999999 head "ID" select recid , output_device_type , dbsize_mbytes , input_bytes/1024/1024 input_mbytes , output_bytes/1024/1
Read more

EBS R12.2 Autoconfig Failed. Resolved

-
Hi, Please do the following and retest the issue: Due to the method required for "cleaning out" / "re-synchronizing" the following tables, it is EXPECTED / REQUIRED that the Applications have been shutdown. The only thing running should be the Database Tier. Note: A full backup should also be taken before any testing begins. Test the following steps in a development instance, and then migrate accordingly once the desired result is confirmed: 1. Backup the fnd_oam_context_files, fnd_nodes, and adop_valid_nodes tables in the EBS env: sqlplus applsys/pwd create table fnd_oam_context_files_bkp as select * from fnd_oam_context_files; create table fnd_nodes_bk as select * from fnd_nodes; create table adop_valid_nodes_bk as select * from adop_valid_nodes; 2. Truncate the following tables: truncate table fnd_oam_context_files; truncate table fnd_nodes; truncate table adop_valid_nodes; 3. Run AutoConfig on the DB tier
Read more

ADOP options availble in R12.2

-
 Need to find all the availble options in ADOP, here is the command. adop -examples [oracle@preprod appsTier]$ adop -examples Applications DBA Online Patching Tool (adop) See Oracle E-Business Suite Maintenance Guide for a full description of adop features, operation, and usage. Standard Online Patching Cycle:     In Oracle E-Business Suite Release 12.2, patches are normally applied     using an online patching cycle.  The online patching cycle consists of     five phases which are executed in order.     Example - Typical online patching cycle:         source <ebs_root>/EBSapps.env run         adop phase=prepare         adop phase=apply patches=123456         adop phase=finalize         adop phase=cutover         source <ebs_root>/EBSapps.env run         adop phase=cleanup     Note that after cutover the command line environment should be re-loaded     as the run edition file system has changed.     In a multi-node deployment, adop commands are only executed from the   
Read more