ORACLE PERFORMANCE TUNING OVERVIEW

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

CALL Value	Meaning
PARSE	Translates the SQL statement into an execution plan, including checks for proper security authorization and checks for the existence of tables, columns, and other referenced objects.
EXECUTE	Actual execution of the statement by Oracle. For INSERT, UPDATE, and DELETE statements, this modifies the data. For SELECT statements, this identifies the selected rows.
FETCH	Retrieves rows returned by a query. Fetches are only performed for SELECT statements.

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 current is the total number of buffers accessed, also called Logical I/Os.

Trace Statistics for Parses, Executes, and Fetches.

SQL Trace Statistic	Meaning
COUNT	Number of times a statement was parsed, executed, or fetched.
CPU	Total CPU time in seconds for all parse, execute, or fetch calls for the statement. This value is zero (0) if TIMED_STATISTICS is not turned on.
ELAPSED	Total elapsed time in seconds for all parse, execute, or fetch calls for the statement. This value is zero (0) if TIMED_STATISTICS is not turned on.
DISK	Total number of data blocks physically read from the datafiles on disk for all parse, execute, or fetch calls.
QUERY	Total number of buffers retrieved in consistent mode for all parse, execute, or fetch calls. Usually, buffers are retrieved in consistent mode for queries.
CURRENT	Total number of buffers retrieved in current mode. Buffers are retrieved in current mode for statements such as INSERT, UPDATE, and DELETE.

Statistics about the processed rows appear in the ROWS column.

Trace Statistics for the ROWS Column

SQL Trace Statistic	Meaning
ROWS	Total number of rows processed by the SQL statement. This total does not include rows processed by sub-queries of the SQL statement.

For SELECT statements, the number of rows returned appears for the fetch step.

For UPDATE, DELETE, and INSERT statements, 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

Oracle Database Optimization: Measuring Oracle disk I/O speed


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 disks provide 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                                                            % Total Event                        Waits         Time (s)       Ela Time --------------------------- ------------ -----------      -------- db file sequential read       2,598          7,146          48.54 db file scattered read       25,519          3,246          22.04 library cache load lock         673          1,363           9.26 CPU time                                     1,154           7.83 log 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.999 col c2 heading 'Average Waits|for Index|Read I/O'            format 9999.999 col c3 heading 'Percent of| I/O Waits|for Full Scans'        format 9.99 col c4 heading 'Percent of| I/O Waits|for Index Scans'       format 9.99 col 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           c5 from   v$system_event  a,   v$system_event  b where    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:

• 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.

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.

SQL Tuning Advisor

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 bytes

aggregate PGA auto target                                                 4248576   bytes

global memory bound                                                         524288     bytes

total PGA inuse                                                   5760000   bytes

total PGA allocated                                            10342400 bytes

maximum PGA allocated                                                   42925056 bytes

total freeable PGA memory                                               65536       bytes

PGA memory freed back to OS                          9306112   bytes

total PGA used for auto workareas                    0               bytes

maximum PGA used for auto workareas            631808     bytes

total PGA used for manual workareas                0              bytes

maximum PGA used for manual workareas                       529408    bytes

over allocation count                                           9201

bytes processed                                                   55100416  bytes

extra bytes read/written                                      159971328 bytes

cache 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:

Because ADDM runs automatically after each new AWR snapshot is taken, no manual steps are required to generate its findings. But you can run ADDM on demand by creating a new snapshot manually, by using either Oracle Enterprise Manager (OEM) or the command-line interface. The following shows creation of a snapshot from the command line:

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.