gather_plan_statistics

Its often recommended when you are tuning a sql statement the optimizer cardinality estimates are accurate, but how to verify that? by using an undocumented hint  gather_plan_statistics 

rajesh@ORA10GR2> create table t1 as select * from all_objects;

Table created.

rajesh@ORA10GR2> create table t2 as select * from all_objects;

Table created.

rajesh@ORA10GR2>
rajesh@ORA10GR2> alter table t1 add constraint t1_pk primary key(object_id);

Table altered.

rajesh@ORA10GR2> alter table t2 add constraint t2_pk primary key(object_id);

Table altered.

rajesh@ORA10GR2>
rajesh@ORA10GR2> begin
  2     dbms_stats.gather_table_stats(user,'T1');
  3     dbms_stats.gather_table_stats(user,'T2');
  4  end;
  5  /

PL/SQL procedure successfully completed.

rajesh@ORA10GR2>


rajesh@ORA10GR2> @d:\script.sql
rajesh@ORA10GR2> variable x varchar2(20);
rajesh@ORA10GR2> exec :x :='SYS';

PL/SQL procedure successfully completed.

rajesh@ORA10GR2>
rajesh@ORA10GR2> set termout off;
rajesh@ORA10GR2>
rajesh@ORA10GR2>
rajesh@ORA10GR2>
rajesh@ORA10GR2> select * from table( dbms_xplan.display_cursor(null,0,'ALLSTATS +peeked_binds'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------
SQL_ID  f6k8ugj5gkwz0, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from t1, t2 where t1.object_id = t2.objecT_id and t1.owner = :x
Plan hash value: 1838229974
-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem |  O/1/M   |
-------------------------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN         |      |      1 |   1812 |  23083 |00:00:06.32 |    1781 |   1614 |  3333K|   953K|     1/0/0|
|*  2 |   TABLE ACCESS FULL| T1   |      1 |   1766 |  23083 |00:00:00.01 |     814 |    807 |       |       |          |
|   3 |   TABLE ACCESS FULL| T2   |      1 |  58267 |  58267 |00:00:31.69 |     967 |    807 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------
   1 - (VARCHAR2(30), CSID=178): 'SYS'
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   2 - filter("T1"."OWNER"=:X)

25 rows selected.
rajesh@ORA10GR2>

Starts:  This is the number of times the particular step is executed. Most of the times it is 1, but in case of a nested loop you will likely see a higher number. Note that it shows the actual number of times the operation has been performed. So it only shows up when plan statistics have been gathered by using the /*+ gather_plan_statistics */ hint or by setting the statistics_level parameter to all.

Incase of Nested Loops the "starts" looks like this

rajesh@ORA10GR2>
rajesh@ORA10GR2> select * from table( dbms_xplan.display_cursor(null,0,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
SQL_ID  cnr0m6hbgaq4c, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ t1.owner, t2.object_id from t1, t2 where t1.objecT_id =
t2.objecT_id and t1.object_id between 50 and 60
Plan hash value: 3696715680
---------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS                |       |      1 |      4 |     11 |00:00:00.03 |      18 |      4 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1    |      1 |      5 |     11 |00:00:00.01 |       5 |      2 |
|*  3 |    INDEX RANGE SCAN          | T1_PK |      1 |      5 |     11 |00:00:00.01 |       3 |      2 |
|*  4 |   INDEX UNIQUE SCAN          | T2_PK |     11 |      1 |     11 |00:00:00.02 |      13 |      2 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("T1"."OBJECT_ID">=50 AND "T1"."OBJECT_ID"<=60)
   4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
       filter(("T2"."OBJECT_ID"<=60 AND "T2"."OBJECT_ID">=50))

23 rows selected.
rajesh@ORA10GR2>

We scanned index T1_PK once with filter T1.OBJECT_ID>=50 AND T1.OBJECT_ID<=60 and found 11 rows, for each of these rows we hit the index T2_PK to find the object_id identified by step#3 exists in step#4.  So 11 rows followed by 11 hits and hence Starts=11 at step#4.

E-Rows:
This is the estimated number of rows. This is the number you would previously see in the Rows column after a "explain plan for " and "select * from table(dbms_xplan.display)". It is the cardinality of this step, which the cost based optimizer calculated during its parse phase. It may be totally off, because it is an expectation based on the statistics. For several reasons an explain plan can "lie", so you'll want to be careful with this statistic.
A-Rows:
This is the actual number of rows. This number is taken from the plan_statistics, so it only shows up when plan statistics have been gathered. In a tkprof file, this number corresponds to the Rows column in the "Row source operation" section. It is the real number of rows processed by this step. You can get valuable information when this A-rows number differs a lot from E-rows

Reads: This is the number of physical reads performed

Buffers: This represents number of logic IO's performed.

MEMSTATS: This displays statistics regarding the estimated sizes of the required PGA workareas to do a SORT, HASH JOIN, BITMAP MERGE or BITMAP CREATE actions. The three columns are "OMem", "1Mem" and "Used-Mem" will only be displayed if there is an operation involved in the query that used PGA workarea memory.  the memory stats matchup with entries in v$sql_workarea

rajesh@ORA10GR2> select estimated_optimal_size/1024 as estimated_kb,
  2         estimated_onepass_size/1024 as estimated_01pass_kb,
  3         last_memory_used/1024 as last_used_kb
  4  from v$sql_workarea
  5  where sql_id ='f6k8ugj5gkwz0'
  6  and child_number = 0 ;

ESTIMATED_KB ESTIMATED_01PASS_KB LAST_USED_KB
------------ ------------------- ------------
        3333                 953         3471

1 row selected.

rajesh@ORA10GR2>

you can use 'PEEKED_BINDS' for the format parameter, giving a section with the values of bind variables that are used. This is a lot easier than digging through the trace file looking for a "#BIND" and a "value=" clause.

Configuring Clients to Use the External Password Store

Hardcoding passwords in shell scripts is a bad practice. The source code may be shared by many developers, may resides on unsecure servers (CVS), may be printed, etc... The passwords may change often too.

So without Hardcoding passwords how to connect to database server? Its all possible by means of storing password in a Client side wallet.  If you want a client to use the secure external password store feature, then perform the following configuration task:

Step#1 Create a wallet on the client by using the following syntax at the command

D:\app\179818\product\11.2.0>mkstore -wrl d:\app\179818\product\11.2.0 -create
Oracle Secret Store Tool : Version 12.1.0.1
Copyright (c) 2004, 2012, Oracle and/or its affiliates. All rights reserved.
Enter password:
Enter password again:
D:\app\179818\product\11.2.0>


d:\app\179818\product\11.2.0  - is the path to the directory where you want to create and store the wallet

Step#2 Create database connection credentials in the wallet by using the following syntax at the command line

D:\app\179818\product\11.2.0>mkstore -wrl D:\app\179818\product\11.2.0 -createCredential iradsnvl rajesh
Oracle Secret Store Tool : Version 12.1.0.1
Copyright (c) 2004, 2012, Oracle and/or its affiliates. All rights reserved.
Your secret/Password is missing in the command line
Enter your secret/Password:
Re-enter your secret/Password:
Enter wallet password:
Create credential oracle.security.client.connect_string1
D:\app\179818\product\11.2.0>

Step#3 In the client sqlnet.ora file, enter the WALLET_LOCATION parameter and set it to the directory location of the wallet you created in Step#1 and enter the SQLNET.WALLET_OVERRIDE parameter and set it to TRUE as follows

D:\app\client\179818\product\12.1.0\client_1\network\admin>type sqlnet.ora
# sqlnet.ora Network Configuration File: D:\app\client\179818\product\12.1.0\client_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.
# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
SQLNET.WALLET_OVERRIDE = TRUE
WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
  (DIRECTORY = D:\app\179818\product\11.2.0)
  )
 )

Once done with the above steps, you can connect to database without providing credentials. all those details will be available from Wallet.

C:\Users\179818>sqlplus /@iradsnvl
SQL*Plus: Release 12.1.0.1.0 Production on Sat Nov 9 13:23:41 2013
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
rajesh@ORA10GR2>