Performance tuning is a broad, vast and complex topic area when it comes to Oracle databases.
As a performance Tuner or DBA, our main aim is to fix the issue when user reports a problem about a slow or poor performing application or query.
Performance tuning requires a different, although related, method to the initial configuration of a system. Configuring a system involves allocating resources in an ordered manner so that the initial system configuration is functional. Tuning is driven by identifying the most significant bottleneck and making the appropriate changes to reduce or eliminate the effect of that bottleneck. Usually, tuning is performed reactively, either while the system is preproduction or after it is live.
When a SQL statement is executed on an Oracle database, the Oracle query optimizer determines the most efficient execution plan after considering many factors related to the objects referenced and the conditions specified in the query. This determination is an important step in the processing of any SQL statement and can greatly affect execution time. During the evaluation process, the query optimizer reviews statistics gathered on the system to determine the best data access path and other considerations. You can override the execution plan of the query optimizer with hints inserted in SQL statement.
SQL TRACE AND TKPROF
The SQL Trace facility and TKPROF let you accurately assess the efficiency of the SQL statements an application runs. For best results, use these tools with EXPLAIN PLAN rather than using EXPLAIN PLAN alone.
We can run the TKPROF program to format the contents of the trace file and place the output into a readable output file.
TO TAKE TRACE FILES AND TKPROFS:
Need to give the below commands in SQL plus.
Queries:
Alter session set sql_trace = true;
Alter session set tracefile_identifier = ‘identifier_name’;
OPTIONS TO TAKE TKPROFS:
Explain plan, Sys = no, aggregate = yes, sort = Exeela, fchela
HOW TO READ TABULAR STATISTICS IN TKPROF FILE:
TKPROF lists the statistics for a SQL statement returned by the SQL Trace facility in rows and columns. Each row corresponds to one of three steps of SQL statement processing. Statistics are identified by the value of the CALL column.CALL Column Values
The other columns of the SQL Trace facility output are combined statistics for all parses, all executes, and all fetches of a statement. The sum of
query and currentis the total number of buffers accessed, also called Logical I/Os.Trace Statistics for Parses, Executes, and Fetches.
Trace Statistics for the ROWS Column
For
UPDATE, DELETE, and INSERTstatements, the number of rows processed appears for the execute step.RE-BUILDING OF TABLES AND ITS INDEXES:
- Need to rebuild the table and the indexes to compact it and minimize fragmented space.
Queries:
ALTER TABLE <table_name> MOVE TABLESPACE <tablespace_name> parallel 5;
ALTER INDEX <index_name> REBUILD parallel 8;
ANALYZE OF TABLES AND INDEXES
- When last analyzed column is NULL or very old.
Queries:
EXECUTE DBMS_STATS.GATHER_TABLE_STATS('owner', 'table_name',
estimate_percent=>50,
block_sample=>TRUE, cascade=>TRUE,
degree=>5) ;
To avoid calling DBMS_STATS after the index creation or rebuild, include the COMPUTE STATISTICS statement on the CREATE or REBUILD.
COMMON HINTS USED IN DIFFERENT SITUATIONS:
APPEND – This hint applies to the INSERT, UPDATE, and DELETE to use parallel option
DRIVE SITE – For the query which uses remote table in FROM clause
PARALLEL – For select statement which fetches many number of rows
INDEX – To force the index of some tables in the FROM clause when it is not used automatically
CHOOSE – To force the RULE based query to use CHOOSE optimizer
FIRST_ROWS(n) – To make the select statement to fetch first ‘n’ rows very fast.
ALL_ROWS – default to fetch all rows in a query
NO_INDEX – To explicitly disallow a set of indexes for the specified table.
The NO_INDEX hint applies to function-based, B*-tree, bitmap, cluster, or domain indexes.
ORDERED – To force Oracle to only join tables in the order in which they appear in the FROM clause
USE_NL (t1 t2) – Causes Oracle to join each specified table to another row source with a nested loops join
using the specified table as the inner table.
Below table shows the list of all possible Hints which is used in Oracle tuning.
| Hint | Meaning |
| + | Must be immediately after comment indicator, tells Oracle this is a list of hints. |
| ALL_ROWS | Use the cost based approach for best throughput. |
| CHOOSE | Default, if statistics are available will use cost, if not, rule. |
| FIRST_ROWS | Use the cost based approach for best response time. |
| RULE | Use rules based approach; this cancels any other hints specified for this statement. |
| Access Method Hints: | |
| CLUSTER(table) | This tells Oracle to do a cluster scan to access the table. |
| FULL(table) | This tells the optimizer to do a full scan of the specified table. |
| HASH(table) | Tells Oracle to explicitly choose the hash access method for the table. |
| HASH_AJ(table) | Transforms a NOT IN subquery to a hash anti-join. |
| ROWID(table) | Forces a rowid scan of the specified table. |
| INDEX(table [index]) | Forces an index scan of the specified table using the specified index(s). If a list of indexes is specified, the optimizer chooses the one with the lowest cost. If no index is specified then the optimizer chooses the available index for the table with the lowest cost. |
| INDEX_ASC (table [index]) | Same as INDEX only performs an ascending search of the index chosen, this is functionally identical to the INDEX statement. |
| INDEX_DESC(table [index]) | Same as INDEX except performs a descending search. If more than one table is accessed, this is ignored. |
| INDEX_COMBINE(table index) | Combines the bitmapped indexes on the table if the cost shows that to do so would give better performance. |
| INDEX_FFS(table index) | Perform a fast full index scan rather than a table scan. |
| MERGE_AJ (table) | Transforms a NOT IN subquery into a merge anti-join. |
| AND_EQUAL(table index index [index index index]) | This hint causes a merge on several single column indexes. Two must be specified, five can be. |
| NL_AJ | Transforms a NOT IN subquery into a NL anti-join (nested loop). |
| HASH_SJ(t1, t2) | Inserted into the EXISTS subquery; This converts the subquery into a special type of hash join between t1 and t2 that preserves the semantics of the subquery. That is, even if there is more than one matching row in t2 for a row in t1, the row in t1 is returned only once. |
| MERGE_SJ (t1, t2) | Inserted into the EXISTS subquery; This converts the subquery into a special type of merge join between t1 and t2 that preserves the semantics of the subquery. That is, even if there is more than one matching row in t2 for a row in t1, the row in t1 is returned only once. |
| NL_SJ | Inserted into the EXISTS subquery; This converts the subquery into a special type of nested loop join between t1 and t2 that preserves the semantics of the subquery. That is, even if there is more than one matching row in t2 for a row in t1, the row in t1 is returned only once. |
| Hints for join orders and transformations: | |
| ORDERED | This hint forces tables to be joined in the order specified. If you know table X has fewer rows, then ordering it first may speed execution in a join. |
| STAR | Forces the largest table to be joined last using a nested loops join on the index. |
| STAR_TRANSFORMATION | Makes the optimizer use the best plan in which a start transformation is used. |
| FACT(table) | When performing a star transformation use the specified table as a fact table. |
| NO_FACT(table) | When performing a star transformation do not use the specified table as a fact table. |
| PUSH_SUBQ | This causes nonmerged subqueries to be evaluated at the earliest possible point in the execution plan. |
| REWRITE(mview) | If possible forces the query to use the specified materialized view, if no materialized view is specified, the system chooses what it calculates is the appropriate view. |
| NOREWRITE | Turns off query rewrite for the statement, use it for when data returned must be concurrent and can’t come from a materialized view. |
| USE_CONCAT | Forces combined OR conditions and IN processing in the WHERE clause to be transformed into a compound query using the UNION ALL set operator. |
| NO_MERGE (table) | This causes Oracle to join each specified table with another row source without a sort-merge join. |
| NO_EXPAND | Prevents OR and IN processing expansion. |
| Hints for Join Operations: | |
| USE_HASH (table) | This causes Oracle to join each specified table with another row source with a hash join. |
| USE_NL(table) | This operation forces a nested loop using the specified table as the controlling table. |
| USE_MERGE(table,[table,…]) | This operation forces a sort-merge-join operation of the specified tables. |
| DRIVING_SITE | The hint forces query execution to be done at a different site than that selected by Oracle. This hint can be used with either rule-based or cost-based optimization. |
| LEADING(table) | The hint causes Oracle to use the specified table as the first table in the join order. |
| Hints for Parallel Operations: | |
| [NO]APPEND | This specifies that data is to be or not to be appended to the end of a file rather than into existing free space. Use only with INSERT commands. |
| NOPARALLEL (table | This specifies the operation is not to be done in parallel. |
| PARALLEL(table, instances) | This specifies the operation is to be done in parallel. |
| PARALLEL_INDEX | Allows parallelization of a fast full index scan on any index. |
| Other Hints: | |
| CACHE | Specifies that the blocks retrieved for the table in the hint are placed at the most recently used end of the LRU list when the table is full table scanned. |
| NOCACHE | Specifies that the blocks retrieved for the table in the hint are placed at the least recently used end of the LRU list when the table is full table scanned. |
| [NO]APPEND | For insert operations will append (or not append) data at the HWM of table. |
| UNNEST | Turns on the UNNEST_SUBQUERY option for statement if UNNEST_SUBQUERY parameter is set to FALSE. |
| NO_UNNEST | Turns off the UNNEST_SUBQUERY option for statement if UNNEST_SUBQUERY parameter is set to TRUE. |
| PUSH_PRED | Pushes the join predicate into the view. |
INDEXES and Types
Oracle includes numerous methods to improve the speed of Oracle SQL queries. Taking advantage of the low cost of disk storage, Oracle includes many new indexing ways that dramatically increase the speed of the queries.
Common indexes used are:
Normal Index (B – TREE index) - Need to be created for the Columns which has high cardinality
Bit map index - Need to be created for the Columns which has low cardinality
Function based index - Need to be created for the function based Columns
SOME VERY USEFUL AND BASIC TIPS FOR TUNING THE SQL QUERIES:
- Use truncate instead of deleting all records in a table.
- Use FORALL statement instead of looping through DML
- Use BULK COLLECT INTO clause on SELECT Instead of fetching data row by row
- When comparing date value Instead of TRUNC(expected_date) > TRUNC(SYSDATE) Convert to
Expected_date >= TRUNC(SYSDATE)+1
- Do not use outer join unless absolutely necessary, Avoid joining too many tables
- Use IN if the sub-query is more selective and Use EXISTS if the sub-query is less selective
- Always use NOT EXISTS instead of NOT IN
(NOT EXISTS will return rows with NULL value, NOT IN will NOT return rows with NULL value)
- Remove DISTINCT keyword from SELECT if UNION is used
- Can create a local Materialized view for the remote table or view
- Need to drop unwanted or unused Indexes in a table
- Don’t use Append hint with Parallel hint ( Because one is for block level other is at session level processing). Use hints as appropriate.
- Use Append hint for Insert statements like INSERT INTO TABLE_NAME SELECT COLUMN_NAME FROM TABLE_NAME1
- Use parallel hint for queries having explain plan with Nested loop
- Avoid creating bitmap index on transactional table which requires frequent Insertion, deletion or updation.
- Avoid Cartesian products
- Avoid full table scans on large tables
- Use SQL standards and conventions to reduce parsing
- Monitor V$SESSION_LONGOPS to detect long running operations
- Use the SHARED_CURSOR parameter
- Avoid unnecessary sorting
- Monitor query statistics
- Use different tablespaces for tables and indexes (as a general rule)
- Use table partitioning (and local indexes) when appropriate (but partitioning is an extra cost feature)
- Use literals in the WHERE clause (use bind variables)
- Keep statistics up to date
Materialized Views (MV):
Oracle materialized views are one of the most important SQL tuning tools which improves performance by reducing repetitive I/O. MV is a table whose contents are periodically refreshed using a query against a remote table. Since when we are working with various databases running in different system, many times we may need to fetch table datas from the remote location which is quite expensive in terms of resource of fetching data directly from remote location. In order to minimize the response time and to increase the throughput, we may need to create the copy of the remote object (tables or views) to the local database which is Known as materialized view which can be refreshed periodically as and when the data changes in underlying objects.
Basic session settings for MVs:
alter session set query_rewrite_enabled=true;
alter session set query_rewrite_integrity=enforced;
Refresh Options
COMPLETE – totally refreshes the view
Can be done at any time;
Can be time consuming
FAST – incrementally applies data changes
A materialized view log is required on each detail table
Data changes are recorded in MV logs or direct loader logs
Many other requirements must be met for fast refreshes
FORCE – does a FAST refresh in favor of a COMPLETE
The default refresh option
Refresh Modes
ON COMMIT – refreshes occur whenever a commit is performed on one of the view’s underlying detail table(s)
Available only with single table aggregate or join based views
Keeps view data transactionally accurate
Need to check alert log for view creation errors
ON DEMAND – refreshes are initiated manually using one of the procedures in the DBMS_MVIEW package
Can be used with all types of materialized views
Manual Refresh Procedures
DBMS_MVIEW.REFRESH(<mv_name>, <refresh_option>)
DBMS_MVIEW.REFRESH_ALL_MVIEWS()
START WITH [NEXT] <date> - refreshes start at a specified date/time and continue at regular intervals
Because all Oracle databases retrieve and store data, the relative cost of physical disk access is an important topic. In Oracle, we see two types of data block access:
- db file sequential read—A single-block read (i.e., index fetch by ROWID)
- db file scattered read—A multiblock read (a full-table scan, OPQ, sorting)
DBFile Sequential and Scattered Reads
Both "db file sequential read" and "db file scattered read" events signify time waited for I/O read requests to complete. Time is reported in 100's of a second for Oracle 8i releases and below, and 1000's of a second for Oracle 9i and above. Most people confuse these events with each other as they think of how data is read from disk. Instead they should think of how data is read into the SGA buffer cache.
DB file sequential read:
A sequential read operation reads data into contiguous memory (usually a single-block read with p3=1, but can be multiple blocks). Single block I/Os are usually the result of using indexes. This event is also used for rebuilding the controlfile and reading datafile headers (P2=1). In general, this event is indicative of disk contention on index reads.
DB file scattered read:
Similar to db file sequential reads, except that the session is reading multiple data blocks and scatters them into different discontinuous buffers in the SGA. This statistic is NORMALLY indicating disk contention on full table scans. Rarely, data from full table scans could be fitted into a contiguous buffer area, these waits would then show up as sequential reads instead of scattered reads.
The following query shows average wait time for sequential versus scattered reads:
prompt "AVERAGE WAIT TIME FOR READ REQUESTS"
select a.average_wait "SEQ READ", b.average_wait "SCAT READ"
from sys.v_$system_event a, sys.v_$system_event b
where a.event = 'db file sequential read'
and b.event = 'db file scattered read';
Physical disk speed is an important factor in weighing these costs. Faster disk access speeds can reduce the costs of a full-table scan vs. single block reads to a negligible level.
For example, the new solid state disksprovide up to 100,000 I/Os per second, six times faster than traditional disk devices. In a solid-state disk environment, disk I/O is much faster and multiblock reads become far cheaper than with traditional disks.The standard STATSPACK report can be generated when the database is processing a peak load, and you can get a super-detailed report of all elapsed-time metrics. The most important of these metrics is the STATSPACK top-five timed events. This report is critical because it shows the database events that constitute the bottleneck for the system. We can also see the same phenomenon where a system is disk I/O bound. In the STATSPACK report in Listing A, we see that the system is clearly constrained by disk I/O.
| Listing A |
| |
| |
| Top 5 Timed Events % TotalEvent Waits Time (s) Ela Time--------------------------- ------------ ----------- --------db file sequential read 2,598 7,146 48.54db file scattered read 25,519 3,246 22.04library cache load lock 673 1,363 9.26CPU time 1,154 7.83log file parallel write 19,157 837 5.68 |
Here we see that reads and a write constitute the majority of the total database time. In this case, we would want to increase the RAM size of the db_cache_size to reduce disk I/O, tune the SQL to reduce disk I/O, or invest in a faster disk I/O subsystem.
The ideal optimizer settings depend on your environment and are heavily influenced by your system's costs for scattered disk reads vs. sequential disk reads. Listing B shows a great script you can use to measure these I/O costs on your database.
| Listing B |
| |
| |
| col c1 heading 'Average Waits|forFull| Scan Read I/O' format 9999.999col c2 heading 'Average Waits|for Index|Read I/O' format 9999.999col c3 heading 'Percent of| I/O Waits|for Full Scans' format 9.99col c4 heading 'Percent of| I/O Waits|for Index Scans' format 9.99col c5 heading 'Starting|Value|for|optimizer|index|cost|adj' format 999 select a.average_wait c1, b.average_wait c2, a.total_waits /(a.total_waits + b.total_waits) c3, b.total_waits /(a.total_waits + b.total_waits) c4, (b.average_wait / a.average_wait)*100 c5from v$system_event a, v$system_event bwhere a.event = 'db file scattered read'and b.event = 'db file sequential read'; |
Scattered reads and full-table scans
Contrary to some opinions, full-table scans are not necessarily a detriment to performance, and they are often the fastest way to access the table rows. The CBO (cost-based optimizer) choice of performing a full-table scan depends on the settings for Oracle Parallel Query, the db_block_size, the clustering_factor, the estimated percentage of rows returned by the query (according to the CBO statistics), and many other factors.
Once Oracle has chosen a full-table scan, the speed of performing a full-table scan (SOFTS) depends on internal and external factors:
- The number of CPUs on the system
- The setting for Oracle Parallel Query (parallel hints, alter table)
- Table partitioning
- The speed of the disk I/O subsystem (e.g., hardware-cached I/O, solid-state disk RAM 3)
With all of these factors, it may be impossible to determine the exact best setting for the weight in optimizer_index_cost_adj. In the real world, the decision to invoke a full-table scan is heavily influenced by run-time factors such as:
- The availability of free blocks in the data buffers
- The amount of TEMP tablespace (if the FTS has an order by clause)
- The current demands on the CPUs
Hence, it follows that the optimizer_index_cost_adj should change frequently, as the load changes on the server.
However, is it safe to assume that all of the SOFTS factors are reflected in the relative I/O speed of FTS vs. index access? If we make this assumption, we've measured the relative speed in v$system_event and have a foundation for creating a self-tuning parameter. To do this, we must accept the following assumptions:
However, is it safe to assume that all of the SOFTS factors are reflected in the relative I/O speed of FTS vs. index access? If we make this assumption, we've measured the relative speed in v$system_event and have a foundation for creating a self-tuning parameter. To do this, we must accept the following assumptions:
- No systems are alike, and good DBAs must adjust optimizer_index_cost_adj according to their configuration and data access patterns.
- The SOFTS is measurable and is reflected in the wait times in v$system_event.
- The overall amount of time spent performing full-table scans is equal to the percentage of db file sequential read waits as a percentage of total I/O waits from v$system_event (see Listing C).
| Listing C |
| |
| |
(avg-wait-for-db-file-sequential-read / avg-wait-for-db-file-scattered-read) * 100 |
Control disk I/O
Disk I/O-intensive systems are common to Oracle databases, and the time spent performing disk I/O often consumes the majority of the wait time. The job of the Oracle professional is to examine all SQL statements to ensure that they're performing the minimum amount of disk I/O and to know their relative costs of index access vs. full-table scan access.
Disk I/O-intensive systems are common to Oracle databases, and the time spent performing disk I/O often consumes the majority of the wait time. The job of the Oracle professional is to examine all SQL statements to ensure that they're performing the minimum amount of disk I/O and to know their relative costs of index access vs. full-table scan access.
TOOLS/UTILITIES FOR AUTOMATED ORACLE PERFORMANCE TUNING:
Oracle provide the following tools/ utilities to assist with performance monitoring and tuning:
- ADDM (Automated Database Diagnostics Monitor) introduced in Oracle 10g
- TKProf
- Statspack
- Oracle Enterprise Manager - Tuning Pack (cost option)
- Old UTLBSTAT.SQL and UTLESTAT.SQL - Begin and end stats monitoring
- SQL Tuning Advisor
- SQLAccess Advisor
ADDM:
ADDM (Automatic Database Diagnostic Monitor) can be describe as the database's doctor. It allows an Oracle database to diagnose itself and determine how potential problems could be resolved. ADDM runs automatically after each AWR statistics capture, making the performance diagnostic data readily available
AWR:
AWR (Automatic Workload Repository) is a built-in repository (in the SYSAUX tablespace) that exists in every Oracle Database. At regular intervals, the Oracle Database makes a snapshot of all of its vital statistics and workload information and stores them in the AWR.
TKPROF:
TKProf is an Oracle database utility used to format SQL Trace output into human readable format. The TKProf executable is located in the ORACLE HOME/bin directory.
Statspack:
Statspack is a set of performance monitoring and reporting utilities provided by Oracle starting from Oracle 8i and above. Statspack provides improved BSTAT/ESTAT functionality, though the old BSTAT/ESTAT scripts are still available.
The spauto.sql script can be customized and executed to schedule the collection of STATPACK snapshots.
OEM:
Oracle Enterprise Manager (OEM) is a set of systems management tools provided by Oracle Corporation for managing the Oracle environment. It provides tools to monitor the Oracle environment and automate tasks (both one-time and repetitive in nature) to take database and application administration a step closer to "Lights Out" management.
The Automatic SQL Tuning capabilities are exposed through a server utility called the SQL Tuning Advisor. The SQL Tuning Advisor takes one or more SQL statements as an input and invokes the Automatic Tuning Optimizer to perform SQL tuning on the statements. The output of the SQL Tuning Advisor is in the form of an advice or recommendations, along with a rationale for each recommendation and its expected benefit. The recommendation relates to collection of statistics on objects, creation of new indexes, restructuring of the SQL statement, or creation of SQL Profile. A user can choose to accept the recommendation to complete the tuning of the SQL statements.
The SQL Tuning Advisor input can be a single SQL statement or a set of statements. For tuning multiple statements, a SQL Tuning Set (STS) has to be first created. An STS is a database object that stores SQL statements along with their execution context.
SQLAccess Advisor
In addition to the SQL Tuning Advisor, Oracle provides the SQLAccess Advisor, which is a tuning tool 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.
TUNING PGA_AGGREGATE_TARGET in Oracle 9i:
Oracle 9i introduced the PGA_AGGREGATE_TARGET parameter to help better manage session working areas in a session’s Program Global Area (PGA).
Need to set the PGA_AGGREGATE_TARGET initialization parameter as shown below
For example.,
SQL> alter system set pga_aggregate_target=160M;System altered. SQL> show parameter pga_aggregate_target NAME TYPE VALUE------------------------------------ ----------- ---------------------------pga_aggregate_target big integer 167772160
Oracle 9i includes a new view called V$PGASTAT. This view can give you additional statistics on how well the dynamic working area memory management is performing.
For example.,
SQL> select * from v$pgastat; NAME VALUE UNIT-------------------------------------------------------------------- ---------- ------------aggregate PGA target parameter 10485760 bytesaggregate PGA auto target 4248576 bytesglobal memory bound 524288 bytestotal PGA inuse 5760000 bytestotal PGA allocated 10342400 bytesmaximum PGA allocated 42925056 bytestotal freeable PGA memory 65536 bytesPGA memory freed back to OS 9306112 bytestotal PGA used for auto workareas 0 bytesmaximum PGA used for auto workareas 631808 bytestotal PGA used for manual workareas 0 bytesmaximum PGA used for manual workareas 529408 bytesover allocation count 9201bytes processed 55100416 bytesextra bytes read/written 159971328 bytescache hit percentage 25.61 percent 16 rows selected.
TUNING SGA SIZE IN ORACLE 9I:
The SGA sizing tasks include optimizing settings for sga_max_size and the various configuration parameters for db_cache_size, db_xk_cache_size, shared_pool_size, large_pool_size, and other memory objects give the dba better control of system global area (SGA) component sizing. Some parameters to consider in SGA sizing include:sga_max_size This parameter sets the hard limit up to which sga_target can dynamically adjust sizes. Usually, sga_max_size and sga_target will be the same value, but there may be times when you want to have the capability to adjust for peak loads. By setting this parameter higher than sga_target, you allow dynamic adjustment of the sga_target parameter.
sga_target This parameter is new in Oracle Database 10g and reflects the total size of memory footprint a SGA can consume. It includes in its boundaries the fixed SGA and other internal allocations, the (redo) log buffers, the shared pool, Java pool, streams pool, buffer cache, keep/recycle caches, and if they are specified, the non-standard block size caches.1) TO GENERATE SQL TUNING ADVISOR REPORT:
Creation of a Tuning Task
CREATE OR REPLACE PROCEDURE DMADM.DM_DBMS_SQLTUNE (
hint_in IN VARCHAR2 DEFAULT NULL
) AS
v_task VARCHAR2(30);
v_sql CLOB;
BEGIN
/* Assign our SQL statement... */
v_sql := ' our SQL query ';
/* Drop the task in case we are re-running... */
BEGIN
DBMS_SQLTUNE.DROP_TUNING_TASK(
task_name => 'sql_tuning_task'
);
EXCEPTION
WHEN OTHERS THEN -- ORA-13605
NULL;
END;
/* Create a SQL Tuning task for our SQL... */
v_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => v_sql,
time_limit => 1,
scope => 'COMPREHENSIVE',
task_name => 'sql_tuning_task',
description => 'Demo of DBMS_SQLTUNE'
);
/* Execute the task... */
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(
task_name => 'sql_tuning_task'
);
/* We want to run this again... */
ROLLBACK;
END DM_DBMS_SQLTUNE;
/
Execution of a Tuning Task
exec DM_DBMS_SQLTUNE;
Reporting of a Tuning Task
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('sql_tuning_task') AS recs FROM dual;
2) TO GENERATE AWR REPORT:
Two procedures that generate AWR reports are awr_report_text and awr_report_html . These procedures generate the AWR report for the specified snapshot range in TEXT or HTML formats, respectively. The following script shows one way of retrieving the AWR text report for the particular snapshot range:
Select
output
from
table
(dbms_workload_repository. awr_report_text
(37933856,1,2900,2911)
);
3) TO GENERATE ADDM REPORT:
exec dbms_workload_repository.create_snapshot();
Note:
The privilege you need from DBA team is:
GRANT ADVISOR TO user_that_will_run_the_reports;
To Run ADDM Report for SQL tuning, execute the following script:
$ORACLE_HOME/rdbms/admin/addmrpt.sql
To Run AWR Report for an instance wide health check (more like STATSPAK) execute the following script:
$ORACLE_HOME/rdbms/admin/awrrpt.sql
To Run SQL Tuning Advisor Report, execute the following script:
$ORACLE_HOME/rdbms/admin/sqltrpt.sql
This script will list the 15 most expensive SQLs in the cursor cache, and the 15 most expensive SQLs in the Workload Repository first.
Then prompt you to enter a SQL_ID to be analyzed. Be careful that it may take a long time to return, and the kind of recommendations retuned maybe elementary.
This is the only script that des not prompt you to enter 2 snaps.
Note:
. Once the script starts, follow the prompt and select a begin snap and end snap to get an 'in the moment' view of the database instance.
. If in a RAC environment, these scripts need to run per instance for the same snap period.
. Focus on the ADDM recommendations that constitute higher % of impact. Continue monitoring the recommendations with lower % of impact.
. For AWR Reports, focus on the following sections to start with:
- Top 5 Timed Events
- SQL ordered by Elapsed Time
- Top xxx Segments
COMMON PITFALLS
A common pitfall in performance tuning is to mistake the symptoms of a problem for the actual problem itself. It is important to recognize that many performance statistics indicate the symptoms, and that identifying the symptom is not sufficient data to implement a remedy. For example:
Slow physical I/O
Generally, this is caused by poorly-configured disks. However, it could also be
caused by a significant amount of unnecessary physical I/O on those disks
issued by poorly-tuned SQL.
Latch contention
Rarely is latch contention tunable by reconfiguring the instance. Rather, latch
contention usually is resolved through application changes.
Excessive CPU usage
Excessive CPU usage usually means that there is little idle CPU on the system.
This could be caused by an inadequately-sized system, by untuned SQL
statements, or by inefficient application programs.
No comments:
Post a Comment