Sunday, January 23, 2011

Part 26, Using SQL Plan Management(SPM) in 11g with Tutorial

Put this into your mind, that there is no one except the Lord. There never was, and there never shall be. He is pervading everywhere. You shall be absorbed into Him, O mind, if you come to His
Sanctuary. In this Dark Age of Kali Yuga, only the Naam, the Name of the Lord, shall be of any real use to you. So many work and slave continually, but they come to regret and repent in the end.



Index to all the posts of Gurpartap Singh's Blog

First of all lets create the data for testing and comparing sql baselines,sql profiles and and sql_outlines.
This is a great feature to use if you are upgrading from 9i/10g to 11g. I will try to cover all the steps in few posts.


For that I will create a user in the database named 'PRABGUN' and grant dba role to it as:


oracle : rac1.rac.meditate.com : @simar1 : /home/oracle/sql_profile
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Jan 16 10:40:01 2011

Copyright (c) 1982, 2009, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> create user prabgun identified by prabgun default tablespace users temporary tablespace temp;

User created.

SQL> grant connect,resource,dba to prabgun;

Grant succeeded.

SQL> conn prabgun/prabgun@simarsv1
Connected.
SQL>


-------------------
Now execute the following script to create the data and indexes to do testing:
This script will create table customer and add 100000 records with different firstname and same lastname and
then adds 100000 records with same firstname and different lastname. then it creates two indexes on
firstname and then no lastname each and then collects teh stats on table and indexes.


---------
oracle : rac1.rac.meditate.com : @simar1 : /home/oracle/sql_plan_base_lines
$ cat create_data_index_stats.sql
@/home/oracle/sql_plan_base_lines/conn.sql
@/home/oracle/sql_plan_base_lines/case1_with_different_firstname_same_lastname.sql
--@case1_a.sql
--@create_outline_and_check_its_use_for_case1_a.sql

oracle : rac1.rac.meditate.com : @simar1 : /home/oracle/sql_plan_base_lines
$

----------

oracle : rac1.rac.meditate.com : @simar1 : /home/oracle/sql_plan_base_lines
$ cat /home/oracle/sql_plan_base_lines/conn.sql
--Please note I have removed password from the script. The user has DBA role
conn prabgun/prabgun@simarsv1
set line 300
set pages 100
select * from v$instance;
show user


oracle : rac1.rac.meditate.com : @simar1 : /home/oracle/sql_plan_base_lines
$

----------

oracle : rac1.rac.meditate.com : @simar1 : /home/oracle/sql_plan_base_lines
$ cat /home/oracle/sql_plan_base_lines/case1_with_different_firstname_same_lastname.sql
--Clean stuff
set feedback on
drop table customer;
drop sequence CUSTOMER_ID_SEQ;

--Create stuff
-- Creates table Customer:
create table customer(
cust_id number(20) CONSTRAINT cust_id_nn NOT NULL,
firstname varchar2(20),
lastname varchar2(20),
address varchar2(30),
phone_no varchar2(15),
CONSTRAINT cust_id_pk PRIMARY KEY (cust_id)
);

-- Creates Sequence CUSTOMER_ID_SEQ
CREATE SEQUENCE CUSTOMER_ID_SEQ
START WITH 10001
MAXVALUE 99999999
MINVALUE 0
CACHE 100
ORDER;

-- Populate table Customer with 100000 records with first name different but last name same
set serveroutput on
variable con1 varchar2(15);
variable ite1 varchar2(15);
variable first1 varchar2(15);
DECLARE
first varchar2(15) := 'PRABGUN';
ite varchar2(15);
con varchar2(15);
BEGIN
FOR i IN 1..100000 LOOP
select 'PRABGUN' into first from dual;
:first1:=first;
select i into ite from dual;
:ite1:=ite;
select :first1||to_char(:ite1) into con from dual;
:con1:=con;
insert into customer values (CUSTOMER_ID_seq.nextval,:con1,'SINGH','City the Beautiful - Vancouver','3392');
END LOOP;
commit;
END;
/

-- Populate table Customer with firstname same and last name different
set serveroutput on
variable con1 varchar2(15);
variable ite1 varchar2(15);
variable last1 varchar2(15);
DECLARE
last varchar2(15) := 'BOURNE';
ite varchar2(15);
con varchar2(15);
BEGIN
FOR i IN 1..100000 LOOP
select 'BOURNE' into last from dual;
:last1:=last;
select i into ite from dual;
:ite1:=ite;
select :last1||to_char(:ite1) into con from dual;
:con1:=con;
insert into customer values (CUSTOMER_ID_seq.nextval,'JASON',:con1,'70 Beverly Hills','9826');
END LOOP;
commit;
END;
/


CREATE INDEX FIRSTNAME_IX ON CUSTOMER(FIRSTNAME)
LOGGING;

CREATE INDEX LASTNAME_IX ON CUSTOMER(LASTNAME)
LOGGING;

BEGIN
SYS.DBMS_STATS.GATHER_TABLE_STATS (
OwnName => 'PRABGUN'
,TabName => 'CUSTOMER'
,Estimate_Percent => 15
,Degree => 5
,method_opt => 'FOR ALL INDEXED COLUMNS'
,Cascade => TRUE
,No_Invalidate => FALSE);
END;
/

set line 300
set pages 100
select * from customer where cust_id between 10001 and 10020;
select count(*) from customer;
select * from customer where cust_id between 110001 and 110020;

select TABLE_NAME,NUM_ROWS,AVG_ROW_LEN,SAMPLE_SIZE,LAST_ANALYZED from user_tables;
select INDEX_NAME,TABLE_OWNER,TABLE_NAME,NUM_ROWS,SAMPLE_SIZE,LAST_ANALYZED from user_indexes;


oracle : rac1.rac.meditate.com : @simar1 : /home/oracle/sql_plan_base_lines
$
--------------------------------------

Output is as:

SQL> @create_data_index_stats.sql
Connected.

INSTANCE_NUMBER INSTANCE_NAME HOST_NAME VERSION STARTUP_TIME STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
--------------- ---------------- ---------------------------------------------------------------- ----------------- ------------------ ------------ --- ---------- ------- --------------------------------------------- ---------- --- ----------------- ------------------ --------- ---
1 simar1 rac1.rac.meditate.com 11.2.0.1.0 13-JAN-11 OPEN YES 1 STARTED ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO

1 row selected.

USER is "PRABGUN"
drop table customer
*
ERROR at line 1:
ORA-00942: table or view does not exist


drop sequence CUSTOMER_ID_SEQ
*
ERROR at line 1:
ORA-02289: sequence does not exist



Table created.


Sequence created.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Index created.


Index created.


PL/SQL procedure successfully completed.


CUST_ID FIRSTNAME LASTNAME ADDRESS PHONE_NO
---------- -------------------- -------------------- ------------------------------ ---------------
10002 PRABGUN1 SINGH City the Beautiful - Vancouver 3392
10003 PRABGUN2 SINGH City the Beautiful - Vancouver 3392
10004 PRABGUN3 SINGH City the Beautiful - Vancouver 3392
10005 PRABGUN4 SINGH City the Beautiful - Vancouver 3392
10006 PRABGUN5 SINGH City the Beautiful - Vancouver 3392
10007 PRABGUN6 SINGH City the Beautiful - Vancouver 3392
10008 PRABGUN7 SINGH City the Beautiful - Vancouver 3392
10009 PRABGUN8 SINGH City the Beautiful - Vancouver 3392
10010 PRABGUN9 SINGH City the Beautiful - Vancouver 3392
10011 PRABGUN10 SINGH City the Beautiful - Vancouver 3392
10012 PRABGUN11 SINGH City the Beautiful - Vancouver 3392
10013 PRABGUN12 SINGH City the Beautiful - Vancouver 3392
10014 PRABGUN13 SINGH City the Beautiful - Vancouver 3392
10015 PRABGUN14 SINGH City the Beautiful - Vancouver 3392
10016 PRABGUN15 SINGH City the Beautiful - Vancouver 3392
10017 PRABGUN16 SINGH City the Beautiful - Vancouver 3392
10018 PRABGUN17 SINGH City the Beautiful - Vancouver 3392
10019 PRABGUN18 SINGH City the Beautiful - Vancouver 3392
10020 PRABGUN19 SINGH City the Beautiful - Vancouver 3392

19 rows selected.


COUNT(*)
----------
200000

1 row selected.


CUST_ID FIRSTNAME LASTNAME ADDRESS PHONE_NO
---------- -------------------- -------------------- ------------------------------ ---------------
110001 PRABGUN100000 SINGH City the Beautiful - Vancouver 3392
110002 JASON BOURNE1 70 Beverly Hills 9826
110003 JASON BOURNE2 70 Beverly Hills 9826
110004 JASON BOURNE3 70 Beverly Hills 9826
110005 JASON BOURNE4 70 Beverly Hills 9826
110006 JASON BOURNE5 70 Beverly Hills 9826
110007 JASON BOURNE6 70 Beverly Hills 9826
110008 JASON BOURNE7 70 Beverly Hills 9826
110009 JASON BOURNE8 70 Beverly Hills 9826
110010 JASON BOURNE9 70 Beverly Hills 9826
110011 JASON BOURNE10 70 Beverly Hills 9826
110012 JASON BOURNE11 70 Beverly Hills 9826
110013 JASON BOURNE12 70 Beverly Hills 9826
110014 JASON BOURNE13 70 Beverly Hills 9826
110015 JASON BOURNE14 70 Beverly Hills 9826
110016 JASON BOURNE15 70 Beverly Hills 9826
110017 JASON BOURNE16 70 Beverly Hills 9826
110018 JASON BOURNE17 70 Beverly Hills 9826
110019 JASON BOURNE18 70 Beverly Hills 9826
110020 JASON BOURNE19 70 Beverly Hills 9826

20 rows selected.


TABLE_NAME NUM_ROWS AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED
------------------------------ ---------- ----------- ----------- ------------------
CUSTOMER 200173 52 30026 16-JAN-11

1 row selected.


INDEX_NAME TABLE_OWNER TABLE_NAME NUM_ROWS SAMPLE_SIZE LAST_ANALYZED
------------------------------ ------------------------------ ------------------------------ ---------- ----------- ------------------
FIRSTNAME_IX PRABGUN CUSTOMER 200000 200000 16-JAN-11
CUST_ID_PK PRABGUN CUSTOMER 200000 200000 16-JAN-11
LASTNAME_IX PRABGUN CUSTOMER 200000 200000 16-JAN-11

3 rows selected.

SQL>


--------------------------------------------------

Now check the plan when you supply following variables to the bind values
Firstname=PRABGUN1, lastname=SINGH
sql is : select * from customer where firstname= :firstname and lastname = :lastname ;
All can be done by running
exit
rsql (alias of 'sqlplus /nolog)
@conn
@case_default_a_PRABGUN1_SINGH.sql (takes the awr snapshot after executing the sql)
Result : Picks index on column firstname.

---------------------------------------------------------

Scripts are as follows:

oracle : rac1.rac.meditate.com : @simar1 : /home/oracle/sql_plan_base_lines
$ cat case_default_a_PRABGUN1_SINGH.sql
set line 300
set pages 100
alter system flush shared_pool;

set serveroutput on
var firstname varchar2(15)
var lastname varchar2(15)
begin
select 'PRABGUN1', 'SINGH' into :firstname, :lastname from dual;
end;
/

print firstname
print lastname

--select * from customer where firstname= :firstname and lastname = :lastname ;
set echo on
@/home/oracle/sql_plan_base_lines/a_bind.sql

col SQL_TEXT format a80
select SQL_ID,PLAN_HASH_VALUE,SQL_TEXT from v$sql where sql_text like '%select * from customer where%';

select * from table(DBMS_XPLAN.DISPLAY_CURSOR('8k9nu55nxr72g', NULL, 'ALL'));

EXEC dbms_workload_repository.create_snapshot;


oracle : rac1.rac.meditate.com : @simar1 : /home/oracle/sql_plan_base_lines
$






---------------------------------------------------------

Output is as:


SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

oracle : rac1.rac.meditate.com : @simar1 : /home/oracle/sql_plan_base_lines
$ rsql

SQL*Plus: Release 11.2.0.1.0 Production on Sun Jan 16 12:12:55 2011

Copyright (c) 1982, 2009, Oracle. All rights reserved.

SQL> @conn


Connected.

INSTANCE_NUMBER INSTANCE_NAME HOST_NAME VERSION STARTUP_TIME STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
--------------- ---------------- ---------------------------------------------------------------- ----------------- ------------------ ------------ --- ---------- ------- --------------------------------------------- ---------- --- ----------------- ------------------ --------- ---
1 simar1 rac1.rac.meditate.com 11.2.0.1.0 13-JAN-11 OPEN YES 1 STARTED ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO

USER is "PRABGUN"
SQL> SQL> SQL> @case_default_a_PRABGUN1_SINGH.sql

System altered.


PL/SQL procedure successfully completed.


FIRSTNAME
--------------------------------
PRABGUN1


LASTNAME
--------------------------------
SINGH

SQL> @/home/oracle/sql_profile/a_bind.sql
SQL> select * from customer where firstname= :firstname and lastname = :lastname ;

CUST_ID FIRSTNAME LASTNAME ADDRESS PHONE_NO
---------- -------------------- -------------------- ------------------------------ ---------------
10002 PRABGUN1 SINGH City the Beautiful - Vancouver 3392

SQL>
SQL>
SQL> col SQL_TEXT format a80
SQL> select SQL_ID,PLAN_HASH_VALUE,SQL_TEXT from v$sql where sql_text like '%select * from customer where%';

SQL_ID PLAN_HASH_VALUE SQL_TEXT
------------- --------------- --------------------------------------------------------------------------------
8k9nu55nxr72g 3832074785 select * from customer where firstname= :firstname and lastname = :lastname
f2apdwdf8m94q 903671040 select SQL_ID,PLAN_HASH_VALUE,SQL_TEXT from v$sql where sql_text like '%select *
from customer where%'


SQL>
SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR('8k9nu55nxr72g', NULL, 'ALL'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8k9nu55nxr72g, child number 0
-------------------------------------
select * from customer where firstname= :firstname and lastname =
:lastname

Plan hash value: 3832074785

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
|* 1 | TABLE ACCESS BY INDEX ROWID| CUSTOMER | 1 | 50 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | FIRSTNAME_IX | 1 | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1 / CUSTOMER@SEL$1
2 - SEL$1 / CUSTOMER@SEL$1

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

1 - filter("LASTNAME"=:LASTNAME)
2 - access("FIRSTNAME"=:FIRSTNAME)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - "CUSTOMER"."CUST_ID"[NUMBER,22], "FIRSTNAME"[VARCHAR2,20],
"LASTNAME"[VARCHAR2,20], "CUSTOMER"."ADDRESS"[VARCHAR2,30],
"CUSTOMER"."PHONE_NO"[VARCHAR2,15]
2 - "CUSTOMER".ROWID[ROWID,10], "FIRSTNAME"[VARCHAR2,20]


35 rows selected.

SQL>
SQL> EXEC dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

SQL>


-------------------------------------------------

4. Check the plan when you supply following varables to the bind values
Firstname=JASON, lastname=BOURNE1
sql is : select * from customer where firstname= :firstname and lastname = :lastname ;
All can be done by running
@case_default_b_JASON_BOURNE1.sql
Result : Picks index on column lastname.

------------------------------------------------

Script is as follows:

oracle : rac1.rac.meditate.com : @simar1 : /home/oracle/sql_plan_base_lines
$ cat case_default_b_JASON_BOURNE1.sql
set feed on
set line 300
set pages 100
alter system flush shared_pool;

set serveroutput on
var firstname varchar2(15)
var lastname varchar2(15)
begin
select 'JASON', 'BOURNE1' into :firstname, :lastname from dual;
end;
/

print firstname
print lastname

--select * from customer where firstname= :firstname and lastname = :lastname ;
set echo on
@/home/oracle/sql_plan_base_lines/a_bind.sql

col SQL_TEXT format a80
select SQL_ID,PLAN_HASH_VALUE,SQL_TEXT from v$sql where sql_text like '%select * from customer where%';

select * from table(DBMS_XPLAN.DISPLAY_CURSOR('8k9nu55nxr72g', NULL, 'ALL'));

EXEC dbms_workload_repository.create_snapshot;


oracle : rac1.rac.meditate.com : @simar1 : /home/oracle/sql_plan_base_lines
$


------------------------------------------------------------------------------

Output is as:

SQL> @case_default_b_JASON_BOURNE1.sql
SQL> set feed on
SQL> set line 300
SQL> set pages 100
SQL> alter system flush shared_pool;

System altered.

SQL>
SQL> set serveroutput on
SQL> var firstname varchar2(15)
SQL> var lastname varchar2(15)
SQL> begin
2 select 'JASON', 'BOURNE1' into :firstname, :lastname from dual;
3 end;
4 /

PL/SQL procedure successfully completed.

SQL>
SQL> print firstname

FIRSTNAME
--------------------------------
JASON

SQL> print lastname

LASTNAME
--------------------------------
BOURNE1

SQL>
SQL> --select * from customer where firstname= :firstname and lastname = :lastname ;
SQL> set echo on
SQL> @/home/oracle/sql_plan_base_lines/a_bind.sql
SQL> select * from customer where firstname= :firstname and lastname = :lastname ;

CUST_ID FIRSTNAME LASTNAME ADDRESS PHONE_NO
---------- -------------------- -------------------- ------------------------------ ---------------
110002 JASON BOURNE1 70 Beverly Hills 9826

1 row selected.

SQL>
SQL>
SQL> col SQL_TEXT format a80
SQL> select SQL_ID,PLAN_HASH_VALUE,SQL_TEXT from v$sql where sql_text like '%select * from customer where%';

SQL_ID PLAN_HASH_VALUE SQL_TEXT
------------- --------------- --------------------------------------------------------------------------------
8k9nu55nxr72g 1685033199 select * from customer where firstname= :firstname and lastname = :lastname
f2apdwdf8m94q 903671040 select SQL_ID,PLAN_HASH_VALUE,SQL_TEXT from v$sql where sql_text like '%select *
from customer where%'


2 rows selected.

SQL>
SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR('8k9nu55nxr72g', NULL, 'ALL'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8k9nu55nxr72g, child number 0
-------------------------------------
select * from customer where firstname= :firstname and lastname =
:lastname

Plan hash value: 1685033199

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
|* 1 | TABLE ACCESS BY INDEX ROWID| CUSTOMER | 1 | 50 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | LASTNAME_IX | 1 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1 / CUSTOMER@SEL$1
2 - SEL$1 / CUSTOMER@SEL$1

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

1 - filter("FIRSTNAME"=:FIRSTNAME)
2 - access("LASTNAME"=:LASTNAME)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - "CUSTOMER"."CUST_ID"[NUMBER,22], "FIRSTNAME"[VARCHAR2,20],
"LASTNAME"[VARCHAR2,20], "CUSTOMER"."ADDRESS"[VARCHAR2,30],
"CUSTOMER"."PHONE_NO"[VARCHAR2,15]
2 - "CUSTOMER".ROWID[ROWID,10], "LASTNAME"[VARCHAR2,20]


35 rows selected.

SQL>
SQL> EXEC dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

SQL>
SQL>

---------------------------------------------------------------------

5. Create a composite index on columns firstname and lastname and collect stats on it and
run the above commands and they pick new composite index.
@case_default_composite_index.sql
@case_default_a_PRABGUN1_SINGH.sql (cat on this script is given above)
@case_default_b_JASON_BOURNE1.sql (cat on this script is given above)
Result : Picks new composite index.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

oracle : rac1.rac.meditate.com : @simar1 : /home/oracle/sql_plan_base_lines
$ cat case_default_composite_index.sql
CREATE INDEX COMPOSITE_IX ON CUSTOMER(FIRSTNAME,LASTNAME)
LOGGING;

EXEC DBMS_STATS.gather_index_stats('PRABGUN', 'COMPOSITE_IX', estimate_percent => 5);


oracle : rac1.rac.meditate.com : @simar1 : /home/oracle/sql_plan_base_lines
$

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Outut for other scripts is as:

SQL> @case_default_composite_index.sql
SQL> CREATE INDEX COMPOSITE_IX ON CUSTOMER(FIRSTNAME,LASTNAME)
2 LOGGING;

Index created.

SQL>
SQL> EXEC DBMS_STATS.gather_index_stats('PRABGUN', 'COMPOSITE_IX', estimate_percent => 5);

PL/SQL procedure successfully completed.

SQL>

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

SQL> @case_default_a_PRABGUN1_SINGH.sql
SQL> set line 300
SQL> set pages 100
SQL> alter system flush shared_pool;

System altered.

SQL>
SQL> set serveroutput on
SQL> var firstname varchar2(15)
SQL> var lastname varchar2(15)
SQL> begin
2 select 'PRABGUN1', 'SINGH' into :firstname, :lastname from dual;
3 end;
4 /

PL/SQL procedure successfully completed.

SQL>
SQL> print firstname

FIRSTNAME
--------------------------------
PRABGUN1

SQL> print lastname

LASTNAME
--------------------------------
SINGH

SQL>
SQL> --select * from customer where firstname= :firstname and lastname = :lastname ;
SQL> set echo on
SQL> @/home/oracle/sql_plan_base_lines/a_bind.sql
SQL> select * from customer where firstname= :firstname and lastname = :lastname ;

CUST_ID FIRSTNAME LASTNAME ADDRESS PHONE_NO
---------- -------------------- -------------------- ------------------------------ ---------------
10002 PRABGUN1 SINGH City the Beautiful - Vancouver 3392

1 row selected.

SQL>
SQL>
SQL> col SQL_TEXT format a80
SQL> select SQL_ID,PLAN_HASH_VALUE,SQL_TEXT from v$sql where sql_text like '%select * from customer where%';

SQL_ID PLAN_HASH_VALUE SQL_TEXT
------------- --------------- --------------------------------------------------------------------------------
8k9nu55nxr72g 1780780284 select * from customer where firstname= :firstname and lastname = :lastname
f2apdwdf8m94q 903671040 select SQL_ID,PLAN_HASH_VALUE,SQL_TEXT from v$sql where sql_text like '%select *
from customer where%'


2 rows selected.

SQL>
SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR('8k9nu55nxr72g', NULL, 'ALL'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8k9nu55nxr72g, child number 0
-------------------------------------
select * from customer where firstname= :firstname and lastname =
:lastname

Plan hash value: 1780780284

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| CUSTOMER | 1 | 50 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | COMPOSITE_IX | 1 | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1 / CUSTOMER@SEL$1
2 - SEL$1 / CUSTOMER@SEL$1

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

2 - access("FIRSTNAME"=:FIRSTNAME AND "LASTNAME"=:LASTNAME)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - "CUSTOMER"."CUST_ID"[NUMBER,22], "FIRSTNAME"[VARCHAR2,20],
"LASTNAME"[VARCHAR2,20], "CUSTOMER"."ADDRESS"[VARCHAR2,30],
"CUSTOMER"."PHONE_NO"[VARCHAR2,15]
2 - "CUSTOMER".ROWID[ROWID,10], "FIRSTNAME"[VARCHAR2,20], "LASTNAME"[VARCHAR2,20]


34 rows selected.

SQL>
SQL> EXEC dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

SQL>


- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

SQL> @case_default_b_JASON_BOURNE1.sql
SQL> set feed on
SQL> set line 300
SQL> set pages 100
SQL> alter system flush shared_pool;

System altered.

SQL>
SQL> set serveroutput on
SQL> var firstname varchar2(15)
SQL> var lastname varchar2(15)
SQL> begin
2 select 'JASON', 'BOURNE1' into :firstname, :lastname from dual;
3 end;
4 /

PL/SQL procedure successfully completed.

SQL>
SQL> print firstname

FIRSTNAME
--------------------------------
JASON

SQL> print lastname

LASTNAME
--------------------------------
BOURNE1

SQL>
SQL> --select * from customer where firstname= :firstname and lastname = :lastname ;
SQL> set echo on
SQL> @/home/oracle/sql_plan_base_lines/a_bind.sql
SQL> select * from customer where firstname= :firstname and lastname = :lastname ;

CUST_ID FIRSTNAME LASTNAME ADDRESS PHONE_NO
---------- -------------------- -------------------- ------------------------------ ---------------
110002 JASON BOURNE1 70 Beverly Hills 9826

1 row selected.

SQL>
SQL>
SQL> col SQL_TEXT format a80
SQL> select SQL_ID,PLAN_HASH_VALUE,SQL_TEXT from v$sql where sql_text like '%select * from customer where%';

SQL_ID PLAN_HASH_VALUE SQL_TEXT
------------- --------------- --------------------------------------------------------------------------------
8k9nu55nxr72g 1780780284 select * from customer where firstname= :firstname and lastname = :lastname
f2apdwdf8m94q 903671040 select SQL_ID,PLAN_HASH_VALUE,SQL_TEXT from v$sql where sql_text like '%select *
from customer where%'


2 rows selected.

SQL>
SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR('8k9nu55nxr72g', NULL, 'ALL'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8k9nu55nxr72g, child number 0
-------------------------------------
select * from customer where firstname= :firstname and lastname =
:lastname

Plan hash value: 1780780284

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| CUSTOMER | 1 | 50 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | COMPOSITE_IX | 1 | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1 / CUSTOMER@SEL$1
2 - SEL$1 / CUSTOMER@SEL$1

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

2 - access("FIRSTNAME"=:FIRSTNAME AND "LASTNAME"=:LASTNAME)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - "CUSTOMER"."CUST_ID"[NUMBER,22], "FIRSTNAME"[VARCHAR2,20],
"LASTNAME"[VARCHAR2,20], "CUSTOMER"."ADDRESS"[VARCHAR2,30],
"CUSTOMER"."PHONE_NO"[VARCHAR2,15]
2 - "CUSTOMER".ROWID[ROWID,10], "FIRSTNAME"[VARCHAR2,20], "LASTNAME"[VARCHAR2,20]


34 rows selected.

SQL>
SQL> EXEC dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

SQL>
SQL>

------------------------------------------------------------------------------------------

6. Drop stats on all the indexes including histograms and run the same statements.
@drop_stats_on_all_indexes.sql
@case_default_a_PRABGUN1_SINGH.sql (cat already given above)
@case_default_b_JASON_BOURNE1.sql (cat already given above)
Result : Picks new composite index.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

oracle : rac1.rac.meditate.com : @simar1 : /home/oracle/sql_plan_base_lines
$ cat drop_stats_on_all_indexes.sql
exec dbms_stats.delete_index_stats('PRABGUN','LASTNAME_IX');
exec dbms_stats.delete_index_stats('PRABGUN','FIRSTNAME_IX');
exec dbms_stats.delete_index_stats('PRABGUN','CUST_ID_PK');
exec dbms_stats.delete_index_stats('PRABGUN','COMPOSITE_IX');

-- Drop Histograms
EXEC DBMS_STATS.DELETE_COLUMN_STATS('PRABGUN', 'CUSTOMER','FIRSTNAME');
EXEC DBMS_STATS.DELETE_COLUMN_STATS('PRABGUN', 'CUSTOMER','LASTNAME');
EXEC DBMS_STATS.DELETE_COLUMN_STATS('PRABGUN', 'CUSTOMER','CUST_ID');



oracle : rac1.rac.meditate.com : @simar1 : /home/oracle/sql_plan_base_lines
$

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Output is as:
SQL> @drop_stats_on_all_indexes.sql
SQL> exec dbms_stats.delete_index_stats('PRABGUN','LASTNAME_IX');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.delete_index_stats('PRABGUN','FIRSTNAME_IX');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.delete_index_stats('PRABGUN','CUST_ID_PK');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.delete_index_stats('PRABGUN','COMPOSITE_IX');

PL/SQL procedure successfully completed.

SQL>
SQL> -- Drop Histograms
SQL> EXEC DBMS_STATS.DELETE_COLUMN_STATS('PRABGUN', 'CUSTOMER','FIRSTNAME');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.DELETE_COLUMN_STATS('PRABGUN', 'CUSTOMER','LASTNAME');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.DELETE_COLUMN_STATS('PRABGUN', 'CUSTOMER','CUST_ID');

PL/SQL procedure successfully completed.

SQL>


- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -



SQL> @case_default_a_PRABGUN1_SINGH.sql
SQL> set line 300
SQL> set pages 100
SQL> alter system flush shared_pool;

System altered.

SQL>
SQL> set serveroutput on
SQL> var firstname varchar2(15)
SQL> var lastname varchar2(15)
SQL> begin
2 select 'PRABGUN1', 'SINGH' into :firstname, :lastname from dual;
3 end;
4 /

PL/SQL procedure successfully completed.

SQL>
SQL> print firstname

FIRSTNAME
--------------------------------
PRABGUN1

SQL> print lastname

LASTNAME
--------------------------------
SINGH

SQL>
SQL> --select * from customer where firstname= :firstname and lastname = :lastname ;
SQL> set echo on
SQL> @/home/oracle/sql_plan_base_lines/a_bind.sql
SQL> select * from customer where firstname= :firstname and lastname = :lastname ;

CUST_ID FIRSTNAME LASTNAME ADDRESS PHONE_NO
---------- -------------------- -------------------- ------------------------------ ---------------
10002 PRABGUN1 SINGH City the Beautiful - Vancouver 3392

1 row selected.

SQL>
SQL>
SQL> col SQL_TEXT format a80
SQL> select SQL_ID,PLAN_HASH_VALUE,SQL_TEXT from v$sql where sql_text like '%select * from customer where%';

SQL_ID PLAN_HASH_VALUE SQL_TEXT
------------- --------------- --------------------------------------------------------------------------------
8k9nu55nxr72g 1780780284 select * from customer where firstname= :firstname and lastname = :lastname
f2apdwdf8m94q 903671040 select SQL_ID,PLAN_HASH_VALUE,SQL_TEXT from v$sql where sql_text like '%select *
from customer where%'


2 rows selected.

SQL>
SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR('8k9nu55nxr72g', NULL, 'ALL'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8k9nu55nxr72g, child number 0
-------------------------------------
select * from customer where firstname= :firstname and lastname =
:lastname

Plan hash value: 1780780284

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 9 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| CUSTOMER | 20 | 1040 | 9 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | COMPOSITE_IX | 2002 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1 / CUSTOMER@SEL$1
2 - SEL$1 / CUSTOMER@SEL$1

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

2 - access("FIRSTNAME"=:FIRSTNAME AND "LASTNAME"=:LASTNAME)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - "CUSTOMER"."CUST_ID"[NUMBER,22], "FIRSTNAME"[VARCHAR2,20],
"LASTNAME"[VARCHAR2,20], "CUSTOMER"."ADDRESS"[VARCHAR2,30],
"CUSTOMER"."PHONE_NO"[VARCHAR2,15]
2 - "CUSTOMER".ROWID[ROWID,10], "FIRSTNAME"[VARCHAR2,20], "LASTNAME"[VARCHAR2,20]


34 rows selected.

SQL>
SQL> EXEC dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

SQL>


- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -



SQL> @case_default_b_JASON_BOURNE1.sql
SQL> set feed on
SQL> set line 300
SQL> set pages 100
SQL> alter system flush shared_pool;

System altered.

SQL>
SQL> set serveroutput on
SQL> var firstname varchar2(15)
SQL> var lastname varchar2(15)
SQL> begin
2 select 'JASON', 'BOURNE1' into :firstname, :lastname from dual;
3 end;
4 /

PL/SQL procedure successfully completed.

SQL>
SQL> print firstname

FIRSTNAME
--------------------------------
JASON

SQL> print lastname

LASTNAME
--------------------------------
BOURNE1

SQL>
SQL> --select * from customer where firstname= :firstname and lastname = :lastname ;
SQL> set echo on
SQL> @/home/oracle/sql_plan_base_lines/a_bind.sql
SQL> select * from customer where firstname= :firstname and lastname = :lastname ;

CUST_ID FIRSTNAME LASTNAME ADDRESS PHONE_NO
---------- -------------------- -------------------- ------------------------------ ---------------
110002 JASON BOURNE1 70 Beverly Hills 9826

1 row selected.

SQL>
SQL>
SQL> col SQL_TEXT format a80
SQL> select SQL_ID,PLAN_HASH_VALUE,SQL_TEXT from v$sql where sql_text like '%select * from customer where%';

SQL_ID PLAN_HASH_VALUE SQL_TEXT
------------- --------------- --------------------------------------------------------------------------------
8k9nu55nxr72g 1780780284 select * from customer where firstname= :firstname and lastname = :lastname
f2apdwdf8m94q 903671040 select SQL_ID,PLAN_HASH_VALUE,SQL_TEXT from v$sql where sql_text like '%select *
from customer where%'


2 rows selected.

SQL>
SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR('8k9nu55nxr72g', NULL, 'ALL'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8k9nu55nxr72g, child number 0
-------------------------------------
select * from customer where firstname= :firstname and lastname =
:lastname

Plan hash value: 1780780284

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 9 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| CUSTOMER | 20 | 1040 | 9 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | COMPOSITE_IX | 2002 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1 / CUSTOMER@SEL$1
2 - SEL$1 / CUSTOMER@SEL$1

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

2 - access("FIRSTNAME"=:FIRSTNAME AND "LASTNAME"=:LASTNAME)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - "CUSTOMER"."CUST_ID"[NUMBER,22], "FIRSTNAME"[VARCHAR2,20],
"LASTNAME"[VARCHAR2,20], "CUSTOMER"."ADDRESS"[VARCHAR2,30],
"CUSTOMER"."PHONE_NO"[VARCHAR2,15]
2 - "CUSTOMER".ROWID[ROWID,10], "FIRSTNAME"[VARCHAR2,20], "LASTNAME"[VARCHAR2,20]


34 rows selected.

SQL>
SQL> EXEC dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

SQL>
SQL>


-----------------------------------------------------------------------------------------

7. Drop all the indexes except primary key index on this table and run the same statements again.
@drop_all_indexes.sql
@case_default_a_PRABGUN1_SINGH.sql
@case_default_b_JASON_BOURNE1.sql
Result : It picks table access full.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

oracle : rac1.rac.meditate.com : @simar1 : /home/oracle/sql_plan_base_lines
$ cat drop_all_indexes.sql
drop index composite_ix ;
drop index firstname_ix ;
drop index lastname_ix ;


oracle : rac1.rac.meditate.com : @simar1 : /home/oracle/sql_plan_base_lines
$


- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Output is as:

SQL>
SQL> @case_default_a_PRABGUN1_SINGH.sql
SQL> set line 300
SQL> set pages 100
SQL> alter system flush shared_pool;

System altered.

SQL>
SQL> set serveroutput on
SQL> var firstname varchar2(15)
SQL> var lastname varchar2(15)
SQL> begin
2 select 'PRABGUN1', 'SINGH' into :firstname, :lastname from dual;
3 end;
4 /

PL/SQL procedure successfully completed.

SQL>
SQL> print firstname

FIRSTNAME
--------------------------------
PRABGUN1

SQL> print lastname

LASTNAME
--------------------------------
SINGH

SQL>
SQL> --select * from customer where firstname= :firstname and lastname = :lastname ;
SQL> set echo on
SQL> @/home/oracle/sql_plan_base_lines/a_bind.sql
SQL> select * from customer where firstname= :firstname and lastname = :lastname ;

CUST_ID FIRSTNAME LASTNAME ADDRESS PHONE_NO
---------- -------------------- -------------------- ------------------------------ ---------------
10002 PRABGUN1 SINGH City the Beautiful - Vancouver 3392

1 row selected.

SQL>
SQL>
SQL> col SQL_TEXT format a80
SQL> select SQL_ID,PLAN_HASH_VALUE,SQL_TEXT from v$sql where sql_text like '%select * from customer where%';

SQL_ID PLAN_HASH_VALUE SQL_TEXT
------------- --------------- --------------------------------------------------------------------------------
8k9nu55nxr72g 2844954298 select * from customer where firstname= :firstname and lastname = :lastname
f2apdwdf8m94q 903671040 select SQL_ID,PLAN_HASH_VALUE,SQL_TEXT from v$sql where sql_text like '%select *
from customer where%'


2 rows selected.

SQL>
SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR('8k9nu55nxr72g', NULL, 'ALL'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8k9nu55nxr72g, child number 0
-------------------------------------
select * from customer where firstname= :firstname and lastname =
:lastname

Plan hash value: 2844954298

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 445 (100)| |
|* 1 | TABLE ACCESS FULL| CUSTOMER | 20 | 1040 | 445 (1)| 00:00:06 |
------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1 / CUSTOMER@SEL$1

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

1 - filter(("FIRSTNAME"=:FIRSTNAME AND "LASTNAME"=:LASTNAME))

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - "CUSTOMER"."CUST_ID"[NUMBER,22], "FIRSTNAME"[VARCHAR2,20],
"LASTNAME"[VARCHAR2,20], "CUSTOMER"."ADDRESS"[VARCHAR2,30],
"CUSTOMER"."PHONE_NO"[VARCHAR2,15]


31 rows selected.

SQL>
SQL> EXEC dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

SQL>
SQL> @case_default_b_JASON_BOURNE1.sql
SQL> set feed on
SQL> set line 300
SQL> set pages 100
SQL> alter system flush shared_pool;

System altered.

SQL>
SQL> set serveroutput on
SQL> var firstname varchar2(15)
SQL> var lastname varchar2(15)
SQL> begin
2 select 'JASON', 'BOURNE1' into :firstname, :lastname from dual;
3 end;
4 /

PL/SQL procedure successfully completed.

SQL>
SQL> print firstname

FIRSTNAME
--------------------------------
JASON

SQL> print lastname

LASTNAME
--------------------------------
BOURNE1

SQL>
SQL> --select * from customer where firstname= :firstname and lastname = :lastname ;
SQL> set echo on
SQL> @/home/oracle/sql_plan_base_lines/a_bind.sql
SQL> select * from customer where firstname= :firstname and lastname = :lastname ;

CUST_ID FIRSTNAME LASTNAME ADDRESS PHONE_NO
---------- -------------------- -------------------- ------------------------------ ---------------
110002 JASON BOURNE1 70 Beverly Hills 9826

1 row selected.

SQL>
SQL>
SQL> col SQL_TEXT format a80
SQL> select SQL_ID,PLAN_HASH_VALUE,SQL_TEXT from v$sql where sql_text like '%select * from customer where%';

SQL_ID PLAN_HASH_VALUE SQL_TEXT
------------- --------------- --------------------------------------------------------------------------------
8k9nu55nxr72g 2844954298 select * from customer where firstname= :firstname and lastname = :lastname
f2apdwdf8m94q 903671040 select SQL_ID,PLAN_HASH_VALUE,SQL_TEXT from v$sql where sql_text like '%select *
from customer where%'


2 rows selected.

SQL>
SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR('8k9nu55nxr72g', NULL, 'ALL'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8k9nu55nxr72g, child number 0
-------------------------------------
select * from customer where firstname= :firstname and lastname =
:lastname

Plan hash value: 2844954298

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 445 (100)|
|* 1 | TABLE ACCESS FULL| CUSTOMER | 20 | 1040 | 445 (1)| 00:00:0
---------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1 / CUSTOMER@SEL$1

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

1 - filter(("FIRSTNAME"=:FIRSTNAME AND "LASTNAME"=:LASTNAME))

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - "CUSTOMER"."CUST_ID"[NUMBER,22], "FIRSTNAME"[VARCHAR2,20],
"LASTNAME"[VARCHAR2,20], "CUSTOMER"."ADDRESS"[VARCHAR2,30],
"CUSTOMER"."PHONE_NO"[VARCHAR2,15]


31 rows selected.

SQL>
SQL> EXEC dbms_workload_repository.create_snapshot;


PL/SQL procedure successfully completed.

SQL>
SQL> SQL>
SQL>


-----------------------------------------------------------------------------------



- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

If you want you can load sqls to sql base lines from sql tuning set as follows:

First create the sql tuning set as:





BEGIN
DBMS_SQLTUNE.create_sqlset (
sqlset_name => 'PRABGUN_STS',
description => 'A test SQL tuning set.');
END;
/

Output is as:

SQL> show user
USER is "PRABGUN"
SQL> BEGIN
DBMS_SQLTUNE.create_sqlset (
sqlset_name => 'PRABGUN_STS',
description => 'A test SQL tuning set.');
END;
/
2 3 4 5 6
PL/SQL procedure successfully completed.

SQL>


- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

SET WRAP OFF
SET LINE 140
COL NAME FOR A15
COL DESCRIPTION FOR A50 WRAPPED
select name,created,last_modified,statement_count,description
from DBA_SQLSET;


Output is as:

SQL> SET WRAP OFF
SET LINE 140
COL NAME FOR A15
COL DESCRIPTION FOR A50 WRAPPED
select name,created,last_modified,statement_count,description
from DBA_SQLSET;SQL> SQL> SQL> SQL> 2

NAME CREATED LAST_MODIFIED STATEMENT_COUNT DESCRIPTION
--------------- ------------------ ------------------ --------------- --------------------------------------------------
PRABGUN_STS 16-JAN-11 16-JAN-11 0 A test SQL tuning set.

SQL>


Now we will load the sqls into the sqlset as:

-- Load the SQL set from the Automatic Workload Repository (AWR) for 1 sql based in sql_text.
DECLARE
l_cursor DBMS_SQLTUNE.sqlset_cursor;
BEGIN
OPEN l_cursor FOR
SELECT VALUE(p)
FROM TABLE (DBMS_SQLTUNE.select_workload_repository (
5800, -- begin_snap
5822, -- end_snap
'sql_text like ''select * from customer where firstname%''', -- basic_filter
NULL, -- object_filter
NULL, -- ranking_measure1
NULL, -- ranking_measure2
NULL, -- ranking_measure3
NULL, -- result_percentage
1) -- result_limit
) p;
DBMS_SQLTUNE.load_sqlset (
sqlset_name => 'PRABGUN_STS',
populate_cursor => l_cursor);
END;
/


Output is as:

SQL> -- Load the SQL set from the Automatic Workload Repository (AWR) for 1 sql based in sql_text.
SQL> DECLARE
l_cursor DBMS_SQLTUNE.sqlset_cursor;
BEGIN
OPEN l_cursor FOR
SELECT VALUE(p)
FROM TABLE (DBMS_SQLTUNE.select_workload_repository (
2 3 4 5 6 7 5800, -- begin_snap
5822, -- end_snap
'sql_text like ''select * from customer where firstname%''', -- basic_filter
NULL, -- object_filter
NULL, -- ranking_measure1
NULL, -- ranking_measure2
8 9 10 11 12 13 NULL, -- ranking_measure3
NULL, -- result_percentage
1) -- result_limit
) p;
DBMS_SQLTUNE.load_sqlset (
14 15 16 17 18 sqlset_name => 'PRABGUN_STS',
19 populate_cursor => l_cursor);
20 END;
21 /


PL/SQL procedure successfully completed.

SQL>



Now lets see the contents of STS as:

SELECT * FROM TABLE(DBMS_SQLTUNE.select_sqlset ('PRABGUN_STS'));
SELECT * FROM TABLE(DBMS_SQLTUNE.select_sqlset ('PRABGUN_STS')) where sql_id='8k9nu55nxr72g';




SQL> set feed on
SQL> l
1* SELECT * FROM TABLE(DBMS_SQLTUNE.select_sqlset ('PRABGUN_STS'))
SQL> /

SQL_ID FORCE_MATCHING_SIGNATURE SQL_TEXT
------------- ------------------------ --------------------------------------------------------------------------------
OBJECT_LIST
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
BIND_DATA PARSING_SCHEMA_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------
MODULE ACTION ELAPSED_TIME CPU_TIME BUFFER_GETS DISK_READS DIRECT_WRITES ROWS_PROCESSED FETCHES EXECUTIONS END_OF_FETCH_COUNT OPTIMIZER_COST
------------------------------------------------ -------------------------------- ------------ ---------- ----------- ---------- ------------- -------------- ---------- ---------- ------------------ --------------
OPTIMIZER_ENV PRIORITY COMMAND_TYPE FIRST_LOAD_TIME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- ------------ -------------------
STAT_PERIOD ACTIVE_STAT_PERIOD OTHER PLAN_HASH_VALUE
----------- ------------------ -------------------------------------------------------------------------------- ---------------
SQL_PLAN(STATEMENT_ID, PLAN_ID, TIMESTAMP, REMARKS, OPERATION, OPTIONS, OBJECT_NODE, OBJECT_OWNER, OBJECT_NAME, OBJECT_ALIAS, OBJECT_INSTANCE, OBJECT_TYPE, OPTIMIZER, SEARCH_COLUMNS, ID, PARENT_ID, DEPTH, POSITION, COST, CARDINALITY, BYTES, OTHER_TAG, PARTITION_START, PARTITION_STOP, PARTITION_ID, D
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
BIND_LIST()
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
8k9nu55nxr72g 2.8586E+18 select * from customer where firstname= :firstname and lastn
ame = :lastname

BEDA0B2001004D336725000203F001200369054A41534F4EF00120036907424F55524E4531 PRABGUN
SQL*Plus 38084 20996 1703 0 0 1 2 1 1 445
E289FB89E126A800340110006EF9C3E2CFEA331056414555519521105555551545545558591555449665851D5511058555555155515122555415A0EA0E5551454265455454449081566E001696635615551403025415505AE126A8000502000002000000100000000100002000000208D0070000005C0300002003000101000 3
2844954298
SQL_PLAN_TABLE_TYPE(SQL_PLAN_ROW_TYPE(NULL, NULL, '25-DEC-10', NULL, 'SELECT STATEMENT', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'ALL_ROWS', 0, 0, NULL, 0, 377, 377, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL), SQL_PLAN_ROW_TYPE(NULL, NULL, '25-D
EC-10', NULL, 'TABLE ACCESS', 'FULL', NULL, 'X115321', 'CUSTOMER', 'CUSTOMER@SEL$1', NULL, 'TABLE', NULL, 0, 1, 0, 1, 1, 377, 20, 840, NULL, NULL, NULL, NULL, NULL, 57546901, 375, NULL, NULL, NULL, NULL, 5, 'SEL$1', '


Please note the sql_plan_hash value in STS is "2844954298". Lets see which plan is this:


set linesize 132
break on plan_hash_value skip 1 dup
col operation format a55
col cost format 99999999
col kbytes format 999999
col object format a30
select plan_hash_value,
lpad(' ',2*depth)||operation||' '||options||decode(id, 0, substr(optimizer,1, 6)||' Cost='||to_char(cost)) operation,
object_name object,
cost,
cardinality,
round(bytes / 1024) kbytes
from DBA_HIST_SQL_PLAN where PLAN_HASH_VALUE='&PLAN_HASH_VALUE' order by ID;


Output is as:

SQL> set linesize 132
break on plan_hash_value skip 1 dup
col operation format a55
SQL> SQL> SQL> col cost format 99999999
col kbytes format 999999
col object format a30
select plan_hash_value,
lpad(' ',2*depth)||operation||' '||options||decode(id, 0, substr(optimizer,1, 6)||' Cost='||to_char(cost)) operation,
object_name object,
SQL> SQL> SQL> 2 3 4 cost,
cardinality,
round(bytes / 1024) kbytes
5 6 7 from DBA_HIST_SQL_PLAN where PLAN_HASH_VALUE='&PLAN_HASH_VALUE' order by ID;
Enter value for plan_hash_value: 2844954298
old 7: from DBA_HIST_SQL_PLAN where PLAN_HASH_VALUE='&PLAN_HASH_VALUE' order by ID
new 7: from DBA_HIST_SQL_PLAN where PLAN_HASH_VALUE='2844954298' order by ID

PLAN_HASH_VALUE OPERATION OBJECT COST CARDINALITY KBYTES
--------------- ------------------------------------------------------- ------------------------------ --------- ----------- -------
2844954298 SELECT STATEMENT ALL_RO Cost=377 377
2844954298 SELECT STATEMENT HINT: Cost=377 377
2844954298 TABLE ACCESS FULL CUSTOMER 377 1 0
2844954298 TABLE ACCESS FULL CUSTOMER 377 20 1


4 rows selected.

SQL>






Now lets create the sql baseline from this STS as:

variable pls number;
exec :pls := dbms_spm.load_plans_from_sqlset(sqlset_name => 'PRABGUN_STS', basic_filter => 'sql_text like ''select * from customer where firstname%''');

Output is as:

SQL> variable pls number;
exec :pls := dbms_spm.load_plans_from_sqlset(sqlset_name => 'PRABGUN_STS', basic_filter => 'sql_text like ''select * from customer where firstname%''');
SQL>
PL/SQL procedure successfully completed.

SQL>


Lets see if the basde line hasbenn created or not as:

select sql_text, plan_name, enabled, accepted from dba_sql_plan_baselines;

Output is as:

SQL> select sql_text, plan_name, enabled, accepted from dba_sql_plan_baselines;

SQL_TEXT PLAN_NAME ENA ACC
-------------------------------------------------------------------------------- ------------------------------ --- ---
select * from customer where firstname= :firstname and lastn SQL_PLAN_2gawxbw1p79kj8a124eae YES YES
ame = :lastname


1 row selected.

SQL>

Now lets re-create the data and the index and then test the sql.
@create_data_index_stats.sql


Now lets test this sql baseline as following, even though it should pick the index on firstname but it didn't
because of the sql baseline :

Please note there is a note on the end of teh explain plan showing tht sql baseline has been used.

oracle : rac1.rac.meditate.com : @simar1 : /home/oracle/sql_plan_base_lines
$ rsql

SQL*Plus: Release 11.2.0.1.0 Production on Sun Jan 16 20:37:51 2011

Copyright (c) 1982, 2009, Oracle. All rights reserved.

SQL> @conn

Connected.

INSTANCE_NUMBER INSTANCE_NAME HOST_NAME VERSION STARTUP_TIME STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
--------------- ---------------- ---------------------------------------------------------------- ----------------- ------------------ ------------ --- ---------- ------- --------------------------------------------- ---------- --- ----------------- ------------------ --------- ---
1 simar1 rac1.rac.meditate.com 11.2.0.1.0 13-JAN-11 OPEN YES 1 STARTED ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO

USER is "PRABGUN"
SQL> SQL> @case_default_a_PRABGUN1_SINGH.sql

System altered.


PL/SQL procedure successfully completed.


FIRSTNAME
--------------------------------
PRABGUN1


LASTNAME
--------------------------------
SINGH

SQL> @/home/oracle/sql_plan_base_lines/a_bind.sql
SQL> select * from customer where firstname= :firstname and lastname = :lastname ;

CUST_ID FIRSTNAME LASTNAME ADDRESS PHONE_NO
---------- -------------------- -------------------- ------------------------------ ---------------
10002 PRABGUN1 SINGH City the Beautiful - Vancouver 3392

SQL>
SQL>
SQL> col SQL_TEXT format a80
SQL> select SQL_ID,PLAN_HASH_VALUE,SQL_TEXT from v$sql where sql_text like '%select * from customer where%';

SQL_ID PLAN_HASH_VALUE SQL_TEXT
------------- --------------- --------------------------------------------------------------------------------
8k9nu55nxr72g 2844954298 select * from customer where firstname= :firstname and lastname = :lastname
f2apdwdf8m94q 903671040 select SQL_ID,PLAN_HASH_VALUE,SQL_TEXT from v$sql where sql_text like '%select *
from customer where%'


SQL>
SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR('8k9nu55nxr72g', NULL, 'ALL'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8k9nu55nxr72g, child number 0
-------------------------------------
select * from customer where firstname= :firstname and lastname =
:lastname

Plan hash value: 2844954298

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 445 (100)| |
|* 1 | TABLE ACCESS FULL| CUSTOMER | 1 | 50 | 445 (1)| 00:00:06 |
------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1 / CUSTOMER@SEL$1

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

1 - filter(("FIRSTNAME"=:FIRSTNAME AND "LASTNAME"=:LASTNAME))

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - "CUSTOMER"."CUST_ID"[NUMBER,22], "FIRSTNAME"[VARCHAR2,20],
"LASTNAME"[VARCHAR2,20], "CUSTOMER"."ADDRESS"[VARCHAR2,30],
"CUSTOMER"."PHONE_NO"[VARCHAR2,15]

Note
-----
- SQL plan baseline SQL_PLAN_2gawxbw1p79kj8a124eae used for this statement


35 rows selected.

SQL>
SQL> EXEC dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

SQL>
SQL>


------------------------------------------------------------------------------

Test the second sql that uses the index on lastname but again it will use sql basesline and will do the
full tablescan as:

SQL> @conn
SQL> --Please note I have removed password from the script. The user has DBA role
SQL> conn prabgun/prabgun@simarsv1
Connected.
SQL> set line 300
SQL> set pages 100
SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME HOST_NAME VERSION STARTUP_TIME STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
--------------- ---------------- ---------------------------------------------------------------- ----------------- ------------------ ------------ --- ---------- ------- --------------------------------------------- ---------- --- ----------------- ------------------ --------- ---
1 simar1 rac1.rac.meditate.com 11.2.0.1.0 13-JAN-11 OPEN YES 1 STARTED ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO

1 row selected.

SQL> show user
USER is "PRABGUN"
SQL>
SQL> @case_default_b_JASON_BOURNE1.sql
SQL> set feed on
SQL> set line 300
SQL> set pages 100
SQL> alter system flush shared_pool;

System altered.

SQL>
SQL> set serveroutput on
SQL> var firstname varchar2(15)
SQL> var lastname varchar2(15)
SQL> begin
2 select 'JASON', 'BOURNE1' into :firstname, :lastname from dual;
3 end;
4 /

PL/SQL procedure successfully completed.

SQL>
SQL> print firstname

FIRSTNAME
--------------------------------
JASON

SQL> print lastname

LASTNAME
--------------------------------
BOURNE1

SQL>
SQL> --select * from customer where firstname= :firstname and lastname = :lastname ;
SQL> set echo on
SQL> @/home/oracle/sql_plan_base_lines/a_bind.sql
SQL> select * from customer where firstname= :firstname and lastname = :lastname ;

CUST_ID FIRSTNAME LASTNAME ADDRESS PHONE_NO
---------- -------------------- -------------------- ------------------------------ ---------------
110002 JASON BOURNE1 70 Beverly Hills 9826

1 row selected.

SQL>
SQL>
SQL> col SQL_TEXT format a80
SQL> select SQL_ID,PLAN_HASH_VALUE,SQL_TEXT from v$sql where sql_text like '%select * from customer where%';

SQL_ID PLAN_HASH_VALUE SQL_TEXT
------------- --------------- --------------------------------------------------------------------------------
8k9nu55nxr72g 2844954298 select * from customer where firstname= :firstname and lastname = :lastname
f2apdwdf8m94q 903671040 select SQL_ID,PLAN_HASH_VALUE,SQL_TEXT from v$sql where sql_text like '%select *
from customer where%'


2 rows selected.

SQL>
SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR('8k9nu55nxr72g', NULL, 'ALL'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8k9nu55nxr72g, child number 0
-------------------------------------
select * from customer where firstname= :firstname and lastname =
:lastname

Plan hash value: 2844954298

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 445 (100)| |
|* 1 | TABLE ACCESS FULL| CUSTOMER | 1 | 50 | 445 (1)| 00:00:06 |
------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1 / CUSTOMER@SEL$1

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

1 - filter(("LASTNAME"=:LASTNAME AND "FIRSTNAME"=:FIRSTNAME))

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - "CUSTOMER"."CUST_ID"[NUMBER,22], "FIRSTNAME"[VARCHAR2,20],
"LASTNAME"[VARCHAR2,20], "CUSTOMER"."ADDRESS"[VARCHAR2,30],
"CUSTOMER"."PHONE_NO"[VARCHAR2,15]

Note
-----
- SQL plan baseline SQL_PLAN_2gawxbw1p79kj8a124eae used for this statement


35 rows selected.

SQL>
SQL> EXEC dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

SQL>
SQL>

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Now lets create the composite index and even then it will use the sql baseline and will do the full tablescan as:

SQL> @case_default_composite_index.sql
SQL> CREATE INDEX COMPOSITE_IX ON CUSTOMER(FIRSTNAME,LASTNAME)
2 LOGGING;

Index created.

SQL>
SQL> EXEC DBMS_STATS.gather_index_stats('PRABGUN', 'COMPOSITE_IX', estimate_percent => 5);

PL/SQL procedure successfully completed.

SQL>
SQL> @case_default_a_PRABGUN1_SINGH.sql
SQL> set line 300
SQL> set pages 100
SQL> alter system flush shared_pool;

System altered.

SQL>
SQL> set serveroutput on
SQL> var firstname varchar2(15)
SQL> var lastname varchar2(15)
SQL> begin
2 select 'PRABGUN1', 'SINGH' into :firstname, :lastname from dual;
3 end;
4 /

PL/SQL procedure successfully completed.

SQL>
SQL> print firstname

FIRSTNAME
--------------------------------
PRABGUN1

SQL> print lastname

LASTNAME
--------------------------------
SINGH

SQL>
SQL> --select * from customer where firstname= :firstname and lastname = :lastname ;
SQL> set echo on
SQL> @/home/oracle/sql_plan_base_lines/a_bind.sql
SQL> select * from customer where firstname= :firstname and lastname = :lastname ;

CUST_ID FIRSTNAME LASTNAME ADDRESS PHONE_NO
---------- -------------------- -------------------- ------------------------------ ---------------
10002 PRABGUN1 SINGH City the Beautiful - Vancouver 3392

1 row selected.

SQL>
SQL>
SQL> col SQL_TEXT format a80
SQL> select SQL_ID,PLAN_HASH_VALUE,SQL_TEXT from v$sql where sql_text like '%select * from customer where%';

SQL_ID PLAN_HASH_VALUE SQL_TEXT
------------- --------------- --------------------------------------------------------------------------------
8k9nu55nxr72g 2844954298 select * from customer where firstname= :firstname and lastname = :lastname
f2apdwdf8m94q 903671040 select SQL_ID,PLAN_HASH_VALUE,SQL_TEXT from v$sql where sql_text like '%select *
from customer where%'


2 rows selected.

SQL>
SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR('8k9nu55nxr72g', NULL, 'ALL'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8k9nu55nxr72g, child number 0
-------------------------------------
select * from customer where firstname= :firstname and lastname =
:lastname

Plan hash value: 2844954298

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 445 (100)| |
|* 1 | TABLE ACCESS FULL| CUSTOMER | 1 | 50 | 445 (1)| 00:00:06 |
------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1 / CUSTOMER@SEL$1

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

1 - filter(("FIRSTNAME"=:FIRSTNAME AND "LASTNAME"=:LASTNAME))

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - "CUSTOMER"."CUST_ID"[NUMBER,22], "FIRSTNAME"[VARCHAR2,20],
"LASTNAME"[VARCHAR2,20], "CUSTOMER"."ADDRESS"[VARCHAR2,30],
"CUSTOMER"."PHONE_NO"[VARCHAR2,15]

Note
-----
- SQL plan baseline SQL_PLAN_2gawxbw1p79kj8a124eae used for this statement


35 rows selected.

SQL>
SQL> EXEC dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

SQL>
SQL> @case_default_b_JASON_BOURNE1.sql
SQL> set feed on
SQL> set line 300
SQL> set pages 100
SQL> alter system flush shared_pool;

System altered.

SQL>
SQL> set serveroutput on
SQL> var firstname varchar2(15)
SQL> var lastname varchar2(15)
SQL> begin
2 select 'JASON', 'BOURNE1' into :firstname, :lastname from dual;
3 end;
4 /

PL/SQL procedure successfully completed.

SQL>
SQL> print firstname

FIRSTNAME
--------------------------------
JASON

SQL> print lastname

LASTNAME
--------------------------------
BOURNE1

SQL>
SQL> --select * from customer where firstname= :firstname and lastname = :lastname ;
SQL> set echo on
SQL> @/home/oracle/sql_plan_base_lines/a_bind.sql
SQL> select * from customer where firstname= :firstname and lastname = :lastname ;

CUST_ID FIRSTNAME LASTNAME ADDRESS PHONE_NO
---------- -------------------- -------------------- ------------------------------ ---------------
110002 JASON BOURNE1 70 Beverly Hills 9826

1 row selected.

SQL>
SQL>
SQL> col SQL_TEXT format a80
SQL> select SQL_ID,PLAN_HASH_VALUE,SQL_TEXT from v$sql where sql_text like '%select * from customer where%';

SQL_ID PLAN_HASH_VALUE SQL_TEXT
------------- --------------- --------------------------------------------------------------------------------
8k9nu55nxr72g 2844954298 select * from customer where firstname= :firstname and lastname = :lastname
f2apdwdf8m94q 903671040 select SQL_ID,PLAN_HASH_VALUE,SQL_TEXT from v$sql where sql_text like '%select *
from customer where%'


2 rows selected.

SQL>
SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR('8k9nu55nxr72g', NULL, 'ALL'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8k9nu55nxr72g, child number 0
-------------------------------------
select * from customer where firstname= :firstname and lastname =
:lastname

Plan hash value: 2844954298

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 445 (100)| |
|* 1 | TABLE ACCESS FULL| CUSTOMER | 1 | 50 | 445 (1)| 00:00:06 |
------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1 / CUSTOMER@SEL$1

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

1 - filter(("LASTNAME"=:LASTNAME AND "FIRSTNAME"=:FIRSTNAME))

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - "CUSTOMER"."CUST_ID"[NUMBER,22], "FIRSTNAME"[VARCHAR2,20],
"LASTNAME"[VARCHAR2,20], "CUSTOMER"."ADDRESS"[VARCHAR2,30],
"CUSTOMER"."PHONE_NO"[VARCHAR2,15]

Note
-----
- SQL plan baseline SQL_PLAN_2gawxbw1p79kj8a124eae used for this statement


35 rows selected.

SQL>
SQL> EXEC dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

SQL>
SQL>

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Now lets see the contents on the sql base line as:

SQL>
SQL> select sql_text, plan_name, enabled, accepted from dba_sql_plan_baselines;

SQL_TEXT PLAN_NAME ENA ACC
-------------------------------------------------------------------------------- ------------------------------ --- ---
select * from customer where firstname= :firstname and lastn SQL_PLAN_2gawxbw1p79kj8a124eae YES YES
ame = :lastname

select * from customer where firstname= :firstname and lastn SQL_PLAN_2gawxbw1p79kjab69ce10 YES NO
ame = :lastname

select * from customer where firstname= :firstname and lastn SQL_PLAN_2gawxbw1p79kjaf90f144 YES NO
ame = :lastname

select * from customer where firstname= :firstname and lastn SQL_PLAN_2gawxbw1p79kjdea3bfd2 YES NO
ame = :lastname


4 rows selected.

SQL>


There is only one accepted plan that was added by us manually in the baselines the others are not accepted
and will not be used, we can accept these plans if we like and then the optimizer would pick the best plan
out of the selected plan when hard parse will be done as per situation at that time(including bind variable values).
Here is the list of the database parametes in the database.
:

SQL> show parameter sql

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
plsql_ccflags string
plsql_code_type string INTERPRETED
plsql_debug boolean FALSE
plsql_optimize_level integer 2
plsql_v2_compatibility boolean FALSE
plsql_warnings string DISABLE:ALL
sql92_security boolean FALSE
sql_trace boolean FALSE
sqltune_category string DEFAULT
SQL>


We will continue with Baselines in next post.



After wandering and wandering for so long, you have come; in this Dark Age of Kali Yuga, you have obtained this human body, so very difficult to obtain. This opportunity shall not come into your hands again. So chant the Naam, the Name of the Lord, and the noose of Death shall be cut away. You shall not have to come and go in reincarnation over and over again, if you chant and meditate on the One and Only Lord.

No comments:

Post a Comment