How to make use of the GATHER_PLAN_STATISTICS hint

Use the gather_plan_statistics hint to view the optimizer's estimation of rows vs. the actual returned number of rows.

Notice the case - gather_plan_statistics - I had trouble getting it to work when stated in capital letters as GATHER_PLAN_STATISTICS. So stick to lower case!

Use the hint in the SELECT part of your query, for example:

select /*+ gather_plan_statistics */
from mytable t1 join mytable t2....

set linesize 200
set pagesize 0
select * from table
(dbms_xplan.display_cursor(NULL,NULL,'TYPICAL ALLSTATS LAST'));
exit

Alternatively, after the query has run to completion, check the cursor cache for the details of your specific SQL ID, and use the display_cursor with the SQL ID explicitly set:

set linesize 200
set pagesize 0
select * from table
(dbms_xplan.display_cursor( 'dnyrjuumj8psq',1,'TYPICAL ALLSTATS LAST'));
exit

Either way will work and give you the following example output:



Plan hash value: 4147659309

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |         |      1 |        |       |  1862 (100)|          |   3012 |00:08:14.63 |     576K|    553K|       |       |          |
|   1 |  SORT ORDER BY                           |         |      1 |      2 |   294 |  1862   (1)| 00:00:01 |   3012 |00:08:14.63 |     576K|    553K|   372K|   372K|  330K (0)|
|*  2 |   FILTER                                 |         |      1 |        |       |            |          |   3012 |00:08:45.50 |     576K|    553K|       |       |          |
|   3 |    NESTED LOOPS                          |         |      1 |      2 |   294 |  1861   (1)| 00:00:01 |   3012 |00:08:45.50 |     576K|    553K|       |       |          |
|   4 |     NESTED LOOPS                         |         |      1 |      2 |   294 |  1861   (1)| 00:00:01 |   3012 |00:08:45.34 |     573K|    553K|       |       |          |
|   5 |      NESTED LOOPS                        |         |      1 |      2 |   270 |  1859   (1)| 00:00:01 |   3012 |00:08:45.31 |     573K|    553K|       |       |          |
|*  6 |       TABLE ACCESS BY INDEX ROWID BATCHED| T1      |      1 |      1 |    31 |     3   (0)| 00:00:01 |    439 |00:00:00.27 |      89 |     73 |       |       |          |
|*  7 |        INDEX RANGE SCAN                  | T1_IDX4 |      1 |      4 |       |     2   (0)| 00:00:01 |    440 |00:00:00.01 |       3 |      1 |       |       |          |
|*  8 |       TABLE ACCESS BY INDEX ROWID BATCHED| T2      |    439 |      2 |   208 |  1856   (1)| 00:00:01 |   3012 |00:08:54.15 |     573K|    553K|       |       |          |
|*  9 |        INDEX RANGE SCAN                  | T2_IDX4 |    439 |  23118 |       |    69   (0)| 00:00:01 |     12M|00:00:54.73 |   38436 |  37439 |       |       |          |
|* 10 |      INDEX UNIQUE SCAN                   | T3_IDX1 |   3012 |      1 |       |     0   (0)|          |   3012 |00:00:00.01 |       9 |      2 |       |       |          |
|  11 |     TABLE ACCESS BY INDEX ROWID          | T3      |   3012 |      1 |    12 |     1   (0)| 00:00:01 |   3012 |00:00:00.01 |    3012 |      2 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


Notice the column "E-Rows". It deviates substantially from the column A-Rows, which are the actual rows returned.



So the optimizer is clearly not able to make a good estimation about the number of rows.



Let's add a couple of statements which will give the optimizer better statistics. This particular database is not certified with any of the 12.1 adaptive tuning features, so I will have to feed the optimizer using other methods:



alter session set statistics_level='ALL';
alter session set optimizer_dynamic_sampling=11;


After running it a second time, the following plan is generated:



Plan hash value: 1509429641

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name   | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |        |      1 |        |       |   735K(100)|          |   3012 |00:09:44.25 |     572K|    551K|       |       |          |
|   1 |  SORT ORDER BY                           |        |      1 |   2986 |   428K|   735K  (1)| 00:00:29 |   3012 |00:09:44.25 |     572K|    551K|   372K|   372K|  330K (0)|
|*  2 |   FILTER                                 |        |      1 |        |       |            |          |   3012 |00:09:44.25 |     572K|    551K|       |       |          |
|*  3 |    HASH JOIN                             |        |      1 |   2986 |   428K|   735K  (1)| 00:00:29 |   3012 |00:09:44.25 |     572K|    551K|  1066K|  1066K|  954K (0)|
|   4 |     NESTED LOOPS                         |        |      1 |   2986 |   393K|   735K  (1)| 00:00:29 |   3012 |00:09:43.01 |     569K|    547K|       |       |          |
|   5 |      NESTED LOOPS                        |        |      1 |     10M|   393K|   735K  (1)| 00:00:29 |     12M|00:00:38.92 |   38488 |  37114 |       |       |          |
|*  6 |       TABLE ACCESS BY INDEX ROWID BATCHED| T1     |      1 |    439 | 13609 |    72   (0)| 00:00:01 |    439 |00:00:00.01 |      76 |      0 |       |       |          |
|*  7 |        INDEX RANGE SCAN                  | T1_IDX4|      1 |    440 |       |     2   (0)| 00:00:01 |    440 |00:00:00.01 |       3 |      0 |       |       |          |
|*  8 |       INDEX RANGE SCAN                   | T2_IDX4|    439 |  23118 |       |    69   (0)| 00:00:01 |     12M|00:00:28.47 |   38412 |  37114 |       |       |          |
|*  9 |      TABLE ACCESS BY INDEX ROWID         | T2     |     12M|      7 |   728 |  1856   (1)| 00:00:01 |   3012 |00:08:47.65 |     531K|    510K|       |       |          |
|  10 |     TABLE ACCESS FULL                    | T3     |      1 |  74866 |   877K|   572   (1)| 00:00:01 |  74866 |00:00:01.22 |    3022 |   3017 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


Consider the same column - the E-Rows and the A-Rows, and how much more accurate the optimizer is estimating the rows to be returned. 

Notice also that the former of these two plans generates an index lookup on the T3 table, while the ladder generates a full table scan. The FTS proved to be the most efficient, since the runtime dropped from 15 to 9 minutes.




NOTE:
You can only get the results you expect when you actually execute the statement. 
If you try to add the hint along with the EXPLAIN PLAN FOR statement, you will see warnings as these in your output:


Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level



These will of course be shown even if you do set the statistics_level to ALL. 
As Mathew McPeak points out in an aswer on stackoverflow.com

The way you are using it, you are printing the plan of the last statement you explained, not the last statement you executed. And "explain" will not execute the query, so it will not benefit from a gather_plan_statistics hint.
  

How to use dbms_spm.load_plans_from_sqlset

To load a specific SQL ID with a specific plan hash value from an STS into your SQL Plan Management Base, use:

set serveroutput on
declare
   l_num_plans PLS_INTEGER;
begin
 l_num_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET (sqlset_name=> 'LongRunningSQL1',sqlset_owner=>'DBADMIN' ,basic_filter=>'sql_id=''83fhwyajathc3'' and plan_hash_value=3643960001 ');
 DBMS_OUTPUT.put_line('Number of plans loaded: ' || l_num_plans);
end;
/
exit

Documented here

A list of eBS users

To view the users that are installed as a part of an eBS installation, use the following query:

SELECT EBS.ORACLE_USERNAME,U.PROFILE 
FROM APPS.FND_ORACLE_USERID EBS JOIN DBA_USERS U
ON (EBS.ORACLE_USERNAME = U.USERNAME);

How to execute and capture an SQL statement in the SMB at the same time

An experienced co-worker showed me this neat trick to execute an SQL statement and capture its execution plan in the SQL Plan Management Base at the same time.

/* First, select the bind variables used in the SQL statement you're interested in, into the variable value_string.
Record them in a text file. You will be using them later
*/
SELECT DECODE (value_string, 'NULL,', ''''',', value_string)
  FROM (  SELECT DECODE (datatype_string,
                         'VARCHAR2(32)', '''' || value_string || ''',',
                         value_string || ',')
                    value_string
            FROM v$sql_bind_capture
           WHERE sql_id = '4d6sdzm63st1u' AND child_number = 1
        ORDER BY position);

/*
Get the SQL text including the bind usage (but not the bind variable values)
*/
SET SERVEROUTPUT ON

DECLARE
   l_sql      VARCHAR2 (32000);
   l_sql_id   VARCHAR2 (30) := '4d6sdzm63st1u';
BEGIN
   BEGIN
      SELECT sql_fulltext
        INTO l_sql
        FROM v$sql                                          
       WHERE sql_id = l_sql_id AND ROWNUM < 2;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         SELECT sql_text
           INTO l_sql
           FROM dba_hist_sqltext
          WHERE sql_id = l_sql_id AND ROWNUM < 2;
   END;

   DBMS_OUTPUT.put_line (l_sql);

/* Capture the baseline for this statement */ 
   EXECUTE IMMEDIATE
      'alter session set optimizer_capture_sql_plan_baselines=true';

/* Send the SQL including the bind variable values to the database */
   EXECUTE IMMEDIATE l_sql
      USING 5022000,
            9834,
            9822,
            9,
            4,
            6;
/* Turn the capture off again */
   EXECUTE IMMEDIATE
      'alter session set optimizer_capture_sql_plan_baselines=false';
END;
/

How to match an exact string using grep

grep -w string

See it in use here

How to remove unwanted parameters from init.ora using a shell script

1. Create a "removal list" containing your unwanted parameters. For example, put the following into a text file called removal_list.txt, and save it in $ORACLE_HOME/dbs:

_b_tree_bitmap_plans
_fast_full_scan_enabled
_grant_secure_role
_like_with_bind_as_equality
_projection_pushdown

2. create a Shell script to loop through the lines in remval_list.txt, called remove_params.sh:

for a in $(cat removal_list.txt ); do
 echo now testing $a
 sleep 1
 curr_param=`grep -w $a init${ORACLE_SID}.ora | awk -F '[=]' '{ print $1 }'`
 if [ $curr_param ]; then
  echo removing $curr_param
  sed -i "/${curr_param}/d" init${ORACLE_SID}.ora
  sleep 1
 else
  echo $a not found in parameter file. Skipping...
  sleep 1
 fi;
done
exit 0

It is important to use the -w flag with grep, as many Oracle initialization parameters can be named similarly and the script will then save multiple values in the curr_param variable

3. Make Your script executable:

chmod 755 remove_params.sh

4. execute the script:

./remove_params.sh

Expected output:

now testing _b_tree_bitmap_plans
removing _b_tree_bitmap_plans
now testing _fast_full_scan_enabled
removing _fast_full_scan_enabled
now testing _grant_secure_role
_grant_secure_role not found in parameter file. Skipping...
now testing _like_with_bind_as_equality
removing _like_with_bind_as_equality
now testing _projection_pushdown
_projection_pushdown not found in parameter file. Skipping...