oracle SQL Tuning Script

oracle SQL Tuning Script

======================================
1) use this script for seeing
How many days the database is UP ?
======================================

SET linesize 999
SET pagesize 50000
SET feedback 1
SET trimspool on
SET trimout on

SELECT instance_name, ROUND ((SYSDATE - startup_time), 1) up_days
FROM v$instance
/

======================================================================
2. use this script to see the distribution of code based on cpu cost.
here "sql_statements" means: how many sql consumed the CPU% ?
and "PCT_TOTAL" means:- how many percentise of CPU use?
======================================================================

COL sql_text format a700 trunc
COL pct_total format 990
COMPUTE sum of sql_statements on report
BREAK on report

SELECT cpu_time_log10, sql_statements, cpu_time_rounded,
ROUND (cpu_time) cpu_time,
100 * ratio_to_report (cpu_time) OVER () pct_total,
ROUND (SUM (cpu_time) OVER (ORDER BY cpu_time_log10))
running_cpu_time
FROM (SELECT TRUNC (LOG (10,
((CASE
WHEN cpu_time <= 0 THEN 1
ELSE cpu_time
END)
)
/ 1000000
)
) cpu_time_log10,
COUNT (*) sql_statements,
POWER
(10,
TRUNC (LOG (10,
((CASE
WHEN cpu_time <= 0 THEN 1
ELSE cpu_time
END
)
)
/ 1000000
)
)
) cpu_time_rounded,
SUM ((CASE
WHEN cpu_time <= 0 THEN 1
ELSE cpu_time
END) / 1000000
) cpu_time
FROM v$sqlarea
WHERE UPPER (sql_text) NOT LIKE 'BEGIN%'
AND UPPER (sql_text) NOT LIKE 'DECLARE%'
GROUP BY TRUNC (LOG (10,
((CASE
WHEN cpu_time <= 0 THEN 1
ELSE cpu_time
END)
)
/ 1000000
)
)) a
ORDER BY a.cpu_time_log10
/

=========================================================
3. here, what are the most CPU consumed SQL (in details)
==========================================================

COMPUTE sum of cpu_seconds on report
BREAK on report

SELECT TRUNC (cpu_time / 1000000) cpu_seconds,
TRUNC (elapsed_time / 1000000) eplapsed_seconds,
ROUND ( (SYSDATE
- TO_DATE (first_load_time, 'rrrr-mm-dd/hh24:mi:ss')
)
* 24
) hours_in_cache,
executions, sql_text
FROM v$sqlarea
WHERE UPPER (sql_text) NOT LIKE 'BEGIN%'
AND UPPER (sql_text) NOT LIKE 'DECLARE%'
AND cpu_time / 1000000 > 100
ORDER BY 1, 2
/

Automatic SQL Tuning Features

Automatic SQL Tuning Features
==========================

Because the manual SQL tuning process poses many challenges to the application developer, the SQL tuning process has been automated by the automatic SQL Tuning manageability features. Theses features have been designed to work equally well for OLTP and Data Warehouse type applications

ADDM

Automatic Database Diagnostic Monitor (ADDM) analyzes the information collected by the AWR for possible performance problems with the Oracle database, including high-load SQL statements.

SQL Tuning Advisor

SQL Tuning Advisor allows a quick and efficient technique for optimizing SQL statements without modifying any statements.

SQL Tuning Sets

When multiple SQL statements are used as input to ADDM or SQL Tuning Advisor, a SQL Tuning Set (STS) is constructed and stored. The STS includes the set of SQL statements along with their associated execution context and basic execution statistics.

SQLAccess Advisor

In addition to the SQL Tuning Advisor, Oracle provides the SQLAccess Advisor that provides advice on materialized views, indexes, and materialized view logs. The SQLAccess Advisor helps you achieve your performance goals by recommending the proper set of materialized views, materialized view logs, and indexes for a given workload. In general, as the number of materialized views and indexes and the space allocated to them is increased, query performance improves. The SQLAccess Advisor considers the trade-offs between space usage and query performance and recommends the most cost-effective configuration of new and existing materialized views and indexes.

Gathering Data

Gathering Data on the SQL Identified
====================================

If you are most concerned with CPU, then examine the top SQL statements that
performed the most BUFFER_GETS during that interval. Otherwise, start with the SQL
statement that performed the most DISK_READS.

Information to Gather During Tuning
-----------------------------------
The tuning process begins by determining the structure of the underlying tables and
indexes. The information gathered includes the following:

1. Complete SQL text from V$SQLTEXT

2. Structure of the tables referenced in the SQL statement, usually by describing the
table in SQL*Plus

3. Definitions of any indexes (columns, column orderings), and whether the indexes
are unique or non-unique

4. Optimizer statistics for the segments (including the number of rows each table,
selectivity of the index columns), including the date when the segments were last
analyzed

5. Definitions of any views referred to in the SQL statement

6. Repeat steps two, three, and four for any tables referenced in the view definitions found in step five

7. Optimizer plan for the SQL statement (either from EXPLAIN PLAN, V$SQL_PLAN,
or the TKPROF output)

8. Any previous optimizer plans for that SQL statement.

Tuning an Application

Tuning an Application / Reducing Load
-------------------------------------

If your whole application is performing suboptimally, or if you are attempting to reduce the overall CPU or I/O load on the database server, then identifying resource-intensive SQL involves the following steps:

1. Determine which period in the day you would like to examine; typically this is the
application's peak processing time.

2. Gather operating system and Oracle statistics at the beginning and end of that period. The minimum of Oracle statistics gathered should be file I/O (V$FILESTAT), system statistics (V$SYSSTAT), and SQL statistics (V$SQLAREA, V$SQL or V$SQLSTATS, V$SQLTEXT, V$SQL_PLAN, and V$SQL_PLAN_STATISTICS).

3. Using the data collected in step two, identify the SQL statements using the mostresources. A good way to identify candidate SQL statements is to query V$SQLSTATS. V$SQLSTATS contains resource usage information for all SQL statements in the shared pool. The data in V$SQLSTATS should be ordered by resource usage.

The most common resources are:

■ Buffer gets (V$SQLSTATS.BUFFER_GETS, for high CPU using statements)
■ Disk reads (V$SQLSTATS.DISK_READS, for high I/O statements)
■ Sorts (V$SQLSTATS.SORTS, for many sorts)

One method to identify which SQL statements are creating the highest load is to compare the resources used by a SQL statement to the total amount of that resource used in the period. For BUFFER_GETS, divide each SQL statement's BUFFER_GETS by the total number of buffer gets during the period. The total number of buffer gets in the system is available in the V$SYSSTAT table, for the statistic session logical reads.

Similarly, it is possible to apportion the percentage of disk reads a statement performs out of the total disk reads performed by the system by dividing V$SQL_STATS.DISK_READS by the value for the V$SYSSTAT statistic physical reads. The SQL sections of the Automatic Workload Repository report include this data, so you do not need to perform the percentage calculations manually.

After you have identified the candidate SQL statements, the next stage is to gather
information that is necessary to examine the statements and tune them.
Identifying High-Load SQL