Selectivity Calculation in SQL query

This article explains how the CBO determines the selectivity for various
predicates.



Selectivity
~~~~~~~~~~~
Selectivity is a measure of the proportion of a row source retrieved by
application of a particular predicate or combination of predicates.

Within the Oracle kernel it is expressed as a value between 0 and 1.
The closer the value is to 0 the more selective the predicate is.
Selectivity is only used by the CBO.

Basic Selectivity formula:
~~~~~~~~~~~~~~~~~~~~~~~~~~

                     Number of records satisfying a condition
Selectivity = -----------------------------------------
                     Total Number of records

In the optimizer, selectivity is used to compare the usefulness of various
predicates in combination with base object costs.

Knowing the proportion of the total data set that a column predicate defines
is very helpful in defining actual access costs.

By default, column selectivity is based on the high and low values and the
number of values in the column with an assumption of even distribution of
data between these two points.

Histogram data can give better selectivity estimates for unevenly distributed
column data. There is more discussion regarding Histograms later.

Selectivity is also used to define the cardinality of a particular row source
once predicates have been applied. Cardinality is the expected number of rows
that will be retrieved from a row source. Cardinality is useful in determining
nested loop join and sort costs. Application of selectivity to the original
cardinality of the row source will produce the expected (computed) cardinality
for the row source.


Glossary of Terms:
~~~~~~~~~~~~~~~~~~

NDV Number of Distinct Values
Cardinality Number of rows
Selectivity Proportion of a dataset returned by a particular predicate(or
group of predicates)

In the following illustrations there are 2 tables (T1 & T2) with columns (c1) and (c2) respectively.

Selectivities:
~~~~~~~~~~~~~~
Without histograms
~~~~~~~~~~~~~~~~~~
c1 = '4076'              1/NDV
c1 > '4076'              1 - (High - Value / High - Low)
c1 >= '4076'             1 - (High - Value / High - Low) + 1/NDV
c1 like '4076'           1/NDV

Join selectivity
~~~~~~~~~~~~~~~~

The selectivity of a join is defined as the selectivity of the most selective
join column adjusted by the proportion of not null values in each join column.


 Sel = 1/max[NDV(t1.c1),NDV(t2.c2)] *
( (Card t1 - # t1.c1 NULLs) / Card t1) *
( (Card t2 - # t2.c2 NULLs) / Card t2)

Bind Variable selectivity
~~~~~~~~~~~~~~~~~~~~~~~~~

Bind variables present a special case because the optimizer has no idea what
the bind variable value is prior to query optimization. This does not present
a problem with equality predicates since a uniform distribution of data is
assumed and the selectivity is taken as 1/NDV for the column. However for
range predicates it presents a major issue because the optimizer does not
know where the range starts or stops. Because of this the optimizer has to
make some assumptions as follows:

c1 =    :bind1           1/NDV
c1 >    :bind1           Default of 5%
c1 >=   :bind1           Default of 5%
c1 like :bind1           Default of 25%

For more information on bind variables see Note:70075.1

Selectivity With Histograms
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Histograms provide additional information about column selectivity for
columns whose distribution is non uniform. Histograms store information about
column data value ranges. Each range is stored in a single row and is often
called a 'bucket'. There are 2 different methods for storing histograms in
Oracle. If there are a small number of distinct
column values (i.e. less than the number of buckets), the column value
and the count of that value is stored. If not then a series of endpoints
are stored to enable more accurate selectivity to be determined.

The first method allows the accurate figures to be used. However with
inexact histograms the terms popular and non-popular value are introduced
and are used to help determine selectivity. A popular value is a value that
spans multiple endpoints whereas a non-popular value does not.
See Note:72539.1 for more information on histograms.

Exact histograms
~~~~~~~~~~~~~~~~
c1 = '4706'         count of value '4076' / Total Number of Rows
c1 > value          count of values > '4076' / Total Number of Rows

InExact Histograms
~~~~~~~~~~~~~~~~~~
col = pop value         # popular buckets / # buckets
col = non pop           (Density)
col > value             # buckets > value / # buckets


Rules for combining selectivity
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Let P1 and P2 be 2 distinct predicates of query Q

 P1 AND P2
       S(P1&P2) = S(P1) * S(P2)
 P1 OR P2
       S(P1|P2) = S(P1) + S(P2) -[S(P1) * S(P2)]

Index Selectivity for concatenated indexes
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Starting with 10.2, when a concatenated index, with all its columns having
equality predicates, is used as an access path, the optimizer uses 1/NDK as
the selectivity (where NDK is the number of distinct keys in the index).

On 9.2.0.7 and 9.2.0.8 this may be enabled with event 38060 level 1.
On 10.1.0.4 and 10.1.0.5 this may be enabled with event 38059 level 1.
On 10.2 adjustments will be made to the selectivity to account for nulls
in the index keys. This also occurs on 10.1.0.5 (with event 38059.)


Join cardinality
~~~~~~~~~~~~~~~~

 Card(Pj) = Card(T1) * Card(T2) * Sel(Pj)




Reference:  https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=334064325845090&parent=DOCUMENT&sourceId=740052.1&id=68992.1&_afrWindowMode=0&_adf.ctrl-state=rjetmzhiv_144

Using SQL Tuning Advisor for a SQL

                                                 

Summary: 

You can create an SQL TUNING TASK manually ad hoc with the following simple steps.

ALTER SESSION SET NLS_LANGUAGE='AMERICAN';

1. Find the sql_id of the oracle session you would like to analyze. Usually the AWR has the top sql_ids.
In case this is a current sql running use the v$session.

select sql_id from v$session where sid = :x

2. Login as SYSTEM (or any other user) at sqlplus and create the tuning task:

SET SERVEROUTPUT ON
declare
stmt_task VARCHAR2(40);
begin
stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => 'd15cdr0zt3vtp');
DBMS_OUTPUT.put_line('task_id: ' || stmt_task );
end;
/
task_id: TASK_137090

2. Run the SQL TUNING TASK

begin
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'TASK_137090');
end;
/

3. You can monitor the processing of the tuning task with the statement

SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME = 'TASK_137090';

4. When the task has a status=COMPLETED, then run:

SET LONG 5000
SET LONGCHUNKSIZE 4000
SET LINESIZE 200

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_137090') AS recommendations FROM dual;

5. Examine the recommendations from Oracle, in case you agree, then accept the best SQL profile. 

begin
DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(task_name => 'TASK_137090', task_owner => 'SYSTEM', replace => TRUE);
end;
/

6. You can check the database sql profiles with the statement:

select * from dba_sql_profiles;

In case you want to disable an sql profile use the statement:

begin
DBMS_SQLTUNE.ALTER_SQL_PROFILE('SQL_PROFILE','STATUS','DISABLED');
end;
/

E.g: 

SYS> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_137090') AS recommendations FROM dual;

RECOMMENDATIONS
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : TASK_137090
Tuning Task Owner  : SYS
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status  : COMPLETED
Started at         : 03/09/2015 20:11:41
Completed at       : 03/09/2015 20:16:12

-------------------------------------------------------------------------------
Schema Name: DBSNMP
SQL ID     : d15cdr0zt3vtp
SQL Text   : SELECT TO_CHAR(current_timestamp AT TIME ZONE 'GMT', 'YYYY-MM-DD
             HH24:MI:SS TZD') AS curr_timestamp, COUNT(username) AS
             failed_count FROM  sys.dba_audit_session WHERE returncode != 0
             AND TO_CHAR(timestamp, 'YYYY-MM-DD HH24:MI:SS') >=
             TO_CHAR(current_timestamp - TO_DSINTERVAL('0 0:30:00'),
             'YYYY-MM-DD HH24:MI:SS')

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  A potentially better execution plan was found for this statement.

  Recommendation (estimated benefit: 99.13%)
  ------------------------------------------
  - Consider accepting the recommended SQL profile to use parallel execution
    for this statement.
    execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_137090',
            task_owner => 'SYS', replace => TRUE, profile_type =>
            DBMS_SQLTUNE.PX_PROFILE);

  Executing this query parallel with DOP 128 will improve its response time
  99.13% over the original plan. However, there is some cost in enabling
  parallel execution. It will increase the statement's resource consumption by
  an estimated 11.21% which may result in a reduction of system throughput.
  Also, because these resources are consumed over a much smaller duration, the
  response time of concurrent statements might be negatively impacted if
  sufficient hardware capacity is not available.

  The following data shows some sampled statistics for this SQL from the past
  week and projected weekly values when parallel execution is enabled.

                                 Past week sampled statistics for this SQL
                                 -----------------------------------------
  Number of executions                                                   0
  Percent of total activity                                              0
  Percent of samples with #Active Sessions > 2*CPU                       0
  Weekly DB time (in sec)                                                0

                              Projected statistics with Parallel Execution
                              --------------------------------------------
  Weekly DB time (in sec)                                                0

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original
-----------
Plan hash value: 4127218927

-----------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                         |     1 |    52 |   167K  (2)| 00:33:33 |
|   1 |  SORT AGGREGATE           |                         |     1 |    52 |            |          |
|   2 |   NESTED LOOPS OUTER      |                         |   809 | 42068 |   167K  (2)| 00:33:33 |
|*  3 |    HASH JOIN RIGHT OUTER  |                         |   809 | 38832 |   167K  (2)| 00:33:33 |
|   4 |     INDEX FULL SCAN       | I_SYSTEM_PRIVILEGE_MAP  |   208 |  1040 |     1   (0)| 00:00:01 |
|*  5 |     HASH JOIN RIGHT OUTER |                         |   809 | 34787 |   167K  (2)| 00:33:33 |
|   6 |      INDEX FULL SCAN      | I_SYSTEM_PRIVILEGE_MAP  |   208 |  1040 |     1   (0)| 00:00:01 |
|*  7 |      HASH JOIN RIGHT OUTER|                         |   809 | 30742 |   167K  (2)| 00:33:33 |
|*  8 |       INDEX RANGE SCAN    | I_AUDIT_ACTIONS         |     3 |    12 |     1   (0)| 00:00:01 |
|*  9 |       TABLE ACCESS FULL   | AUD$                    |   809 | 27506 |   167K  (2)| 00:33:33 |
|* 10 |    INDEX RANGE SCAN       | I_STMT_AUDIT_OPTION_MAP |     1 |     4 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("SPX"."PRIVILEGE"(+)=(-"AUD"."PRIV$USED"))
   5 - access("SPM"."PRIVILEGE"(+)=(-"AUD"."LOGOFF$DEAD"))
   7 - access("AUD"."ACTIO

Query contents of Trace files

There are a number of ways to make trace files available to developers, but one simple way is to allow them query the contents of a trace file from SQL*Plus and spool it to a file on their local PC.

The following example creates a locked user with the necessary code to read the contents of specified trace files. This code is then made accessible to public and a directory object pointing to the trace location is created.

CONN / AS SYSDBA

-- Create locked user to hold code.
CREATE USER trace_user IDENTIFIED BY trace_user ACCOUNT LOCK;

-- Create a type and pipelined table function to read the specified trace file.
CREATE TYPE trace_user.t_trace_tab AS TABLE OF VARCHAR2(32767);
/

CREATE OR REPLACE FUNCTION trace_user.get_trace_file (p_trace_file IN VARCHAR2)
  RETURN trace_user.t_trace_tab PIPELINED
AS
  l_file UTL_FILE.file_type; 
  l_text VARCHAR2(32767);
BEGIN
  l_file := UTL_FILE.fopen('TRACE_DIR', p_trace_file, 'r', 32767);
  BEGIN
    LOOP
      UTL_FILE.get_line(l_file, l_text);
      PIPE ROW (l_text);
    END LOOP;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      NULL;
  END;
  UTL_FILE.fclose(l_file);
  RETURN;
EXCEPTION
  WHEN OTHERS THEN
    PIPE ROW ('ERROR: ' || SQLERRM);
    IF UTL_FILE.is_open(l_file) THEN
      UTL_FILE.fclose(l_file);
    END IF;
    RETURN;
END;
/

-- Make the code accessible to PUBLIC.
GRANT EXECUTE ON trace_user.t_trace_tab TO PUBLIC;
GRANT EXECUTE ON trace_user.get_trace_file TO PUBLIC;
CREATE PUBLIC SYNONYM get_trace_file FOR trace_user.get_trace_file;

-- Create a directory object to the trace location.
CREATE OR REPLACE DIRECTORY trace_dir AS '/u01/app/oracle/diag/rdbms/db11g/DB11G/trace/';
GRANT READ ON DIRECTORY trace_dir TO trace_user;

With that in place we can see how a developer would create and return a trace file to their local PC.

-- Make sure the user has access to DBMS_MONITOR.
CONN / AS SYSDBA
GRANT EXECUTE ON dbms_monitor TO hr;

CONN hr/hr

-- Identify the current trace file.
SELECT value
FROM   v$diag_info
WHERE  name = 'Default Trace File';

VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/db11g/DB11G/trace/DB11G_ora_6309.trc

SQL>

-- Turn of tracing.
EXEC DBMS_MONITOR.session_trace_enable(waits=>TRUE, binds=>FALSE);

-- Do something you wish to trace.
SET LINESIZE 100
COLUMN employee_name FORMAT A30
COLUMN department_name FORMAT A20
COLUMN manager_name FORMAT A30

SELECT e.first_name || ' ' || e.last_name AS employee_name,
       d.department_name,
       e2.first_name || ' ' || e2.last_name AS manager_name
FROM   employees e
       JOIN departments d ON e.department_id = d.department_id
       JOIN employees e2 ON e.manager_id = e2.employee_id
WHERE  d.department_name = 'Finance'
ORDER BY d.department_name;

EMPLOYEE_NAME                  DEPARTMENT_NAME      MANAGER_NAME
------------------------------ -------------------- ------------------------------
Nancy Greenberg                Finance              Neena Kochhar
Luis Popp                      Finance              Nancy Greenberg
Jose Manuel Urman              Finance              Nancy Greenberg
Ismael Sciarra                 Finance              Nancy Greenberg
John Chen                      Finance              Nancy Greenberg
Daniel Faviet                  Finance              Nancy Greenberg

6 rows selected.

SQL> 

-- Turn off tracing.
EXEC DBMS_MONITOR.session_trace_disable;

-- Spool the contents of the relevant trace file to a local file.
SET PAGESIZE 0 FEEDBACK OFF TRIMSPOOL ON TAB OFF
SPOOL C:\tracefile.trc

SELECT *
FROM   TABLE(get_trace_file('DB11G_ora_6309.trc'));

SPOOL OFF
SET PAGESIZE 14 FEEDBACK ON

The developer can now use a local installation of TKPROF or SQL Developer to translate the trace file.

Reference: http://oracle-base.com/articles/misc/sql-trace-10046-trcsess-and-tkprof.php

SQL Plan Management in Oracle

How Does SQL Plan Management Work?

When a SQL statement is hard parsed, the cost based optimizer produces several execution plans and selects the one with the lowest cost. If a SQL plan baseline is present, the optimizer compares the plan it just produced with the plans in the SQL plan baseline. If a matching plan is found that is flagged as accepted the plan is used. If the SQL plan baseline doesn't contain an accepted plan matching the one it just created, the optimizer evaluates the accepted plans in the SQL plan baseline and uses the one with the lowest cost. If the execution plan originally produced by the optimizer has a lower cost than those present in the SQL plan baseline, it is added to the baseline as a not-accepted plan, so it is not used until it is verified not to cause a reduction in performance. If a system change affects all existing accepted plans, so they are considered non-reproducible, the optimizer will use the plan with the lowest cost.

Automatic Plan Capture:

The value of the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter, whose default value is FALSE, determines if the system should automatically capture SQL plan baselines. When set to TRUE, the system records a plan history for SQL statements. The first plan for a specific statement is automatically flagged as accepted. Alternative plans generated after this point are not used until it is verified they do not cause performance degradations. Plans with acceptable performance are added to the SQL plan baseline during the evolution phase.


  SQL> ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE;

  System altered.

Manual Plan Loading:



DECLARE
  l_plans_loaded  PLS_INTEGER;
BEGIN
  l_plans_loaded := DBMS_SPM.load_plans_from_sqlset(
    sqlset_name => 'my_sqlset');
END;
/


DECLARE
  l_plans_loaded  PLS_INTEGER;
BEGIN
  l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
    sql_id => '1abc93ed0123p');
END;
/

Evolving SQL Plan Baselines:

Evolving a SQL plan baseline is the process by which the optimizer determines if non-accepted plans in the baseline should be accepted. As mentioned previously, manually loaded plans are automatically marked as accepted, so manual loading forces the evolving process. When plans are loaded automatically, the baselines are evolved using the EVOLVE_SQL_PLAN_BASELINE function, which returns a CLOB reporting its results.

SET LONG 10000
SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => 'SYS_SQL_7b76323ad90440b9')
FROM  dual;

Steps for SQL access advisor usage

DECLARE
  taskname varchar2(30) := 'SQLACCESS3638195';
  task_desc varchar2(256) := 'SQL Access Advisor';
  task_or_template varchar2(30) := 'SQLACCESS_EMTASK';
  task_id number := 0;
  num_found number;
  sts_name varchar2(256) := 'SQLACCESS3638195_sts';
  sts_cursor dbms_sqltune.sqlset_cursor;
BEGIN
  /* Create Task */
  dbms_advisor.create_task(DBMS_ADVISOR.SQLACCESS_ADVISOR,
                           task_id,
                           taskname,
                           task_desc,
                           task_or_template);

  /* Reset Task */
  dbms_advisor.reset_task(taskname);

  /* Delete Previous STS Workload Task Link */
  select count(*)
  into   num_found
  from   user_advisor_sqla_wk_map
  where  task_name = taskname
  and    workload_name = sts_name;
  IF num_found > 0 THEN
    dbms_advisor.delete_sqlwkld_ref(taskname,sts_name,1);
  END IF;

  /* Delete Previous STS */

 /* select count(*)
  into   num_found
  from   user_advisor_sqlw_sum
  where  workload_name = sts_name;
  IF num_found > 0 THEN
    dbms_sqltune.delete_sqlset(sts_name);
  END IF; */

  /* Create STS */
  dbms_sqltune.create_sqlset(sts_name, 'Obtain workload from cursor cache');

  /* Select all statements in the cursor cache. */
  OPEN sts_cursor FOR
    SELECT VALUE(P)
    FROM TABLE(dbms_sqltune.select_cursor_cache) P;

  /* Load the statements into STS. */
  dbms_sqltune.load_sqlset(sts_name, sts_cursor);
  CLOSE sts_cursor;

  /* Link STS Workload to Task */
  dbms_advisor.add_sqlwkld_ref(taskname,sts_name,1);

  /* Set STS Workload Parameters */
  dbms_advisor.set_task_parameter(taskname,'VALID_ACTION_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
  dbms_advisor.set_task_parameter(taskname,'VALID_MODULE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
  dbms_advisor.set_task_parameter(taskname,'SQL_LIMIT','25');
  dbms_advisor.set_task_parameter(taskname,'VALID_USERNAME_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
  dbms_advisor.set_task_parameter(taskname,'VALID_TABLE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
  dbms_advisor.set_task_parameter(taskname,'INVALID_TABLE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
  dbms_advisor.set_task_parameter(taskname,'INVALID_ACTION_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
  dbms_advisor.set_task_parameter(taskname,'INVALID_USERNAME_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
  dbms_advisor.set_task_parameter(taskname,'INVALID_MODULE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
  dbms_advisor.set_task_parameter(taskname,'VALID_SQLSTRING_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
  dbms_advisor.set_task_parameter(taskname,'INVALID_SQLSTRING_LIST','"@!"');

  /* Set Task Parameters */
  dbms_advisor.set_task_parameter(taskname,'ANALYSIS_SCOPE','ALL');
  dbms_advisor.set_task_parameter(taskname,'RANKING_MEASURE','PRIORITY,OPTIMIZER_COST');
  dbms_advisor.set_task_parameter(taskname,'DEF_PARTITION_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED);
  dbms_advisor.set_task_parameter(taskname,'TIME_LIMIT',10000);
  dbms_advisor.set_task_parameter(taskname,'MODE','LIMITED');
  dbms_advisor.set_task_parameter(taskname,'STORAGE_CHANGE',DBMS_ADVISOR.ADVISOR_UNLIMITED);
  dbms_advisor.set_task_parameter(taskname,'DML_VOLATILITY','TRUE');
  dbms_advisor.set_task_parameter(taskname,'WORKLOAD_SCOPE','PARTIAL');
  dbms_advisor.set_task_parameter(taskname,'DEF_INDEX_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED);
  dbms_advisor.set_task_parameter(taskname,'DEF_INDEX_OWNER',DBMS_ADVISOR.ADVISOR_UNUSED);
  dbms_advisor.set_task_parameter(taskname,'DEF_MVIEW_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED);
  dbms_advisor.set_task_parameter(taskname,'DEF_MVIEW_OWNER',DBMS_ADVISOR.ADVISOR_UNUSED);
  dbms_advisor.set_task_parameter(taskname,'DEF_MVLOG_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED);
  dbms_advisor.set_task_parameter(taskname,'CREATION_COST','TRUE');
  dbms_advisor.set_task_parameter(taskname,'JOURNALING','4');
  dbms_advisor.set_task_parameter(taskname,'DAYS_TO_EXPIRE','30');

  /* Execute Task */
  dbms_advisor.execute_task(taskname);
END;
/

++++++++  Check recommendations ++++++++

SET LONG 100000
SET PAGESIZE 50000
SELECT DBMS_ADVISOR.get_task_script('SQLACCESS3638195') AS script FROM   dual;
SET PAGESIZE 24

SQL Performance Analyzer

Real Application Testing is used to test the impact of any change e.g upgrade, on the database. It has two components:

 1. Database Replay
 2. SQL Performance Analyzer

Database Replay is a tool for capturing the real workload in database and play it at will. But it captures the entire workload of the database.
SQL performance Analyzer is used to know the impact of any change e.g parameter change, on one or more SQLs.

Steps to use SQLPA:

1. Create SQLSET using DBMS_SQLTUNE.CREATE_SQLSET

2. Load SQLSET ( from Cursors,awr or sql traces) using DBMS_SQLTUNE.LOAD_SQLSET

3. Create SQLPA analysis task using DBMS_SQLPA.CREATE_ANALYSIS_TASK

4. Execute analysis task using DBMS_SQLPA.EXECUTE_ANALYSIS_TASK

5. Report analysis task using DBMS_SQLPA.REPORT_ANALYSIS_TASK