Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Giri.

Asked: March 08, 2016 - 5:03 pm UTC

Last updated: October 27, 2016 - 1:40 am UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

Hi,

We are migrating from 10.2.0.4 database to 11.2.0.4 database.

We identified one of the insert query taking longer time than it was in 10g.

We changed the OFE to 10.2.0.4 at session level and ran the same query which completed in 8 secs.

We tried creating the SQL Plan Baseline for the Original query and tried using it with the sql id and plan hash value of the query with OFE, 10.2.0.4.

When we used the below query to check the plan, it gave a new plan which was also running longer time:

select * from table(dbms_xplan.display_sql_plan_baseline( sql_handle => '<sql handle>', format => 'typical'));

Kindly advise to make use of the OFE, 10.2.0.4 into the Baseline, so the query runs faster.

Thanks much for your time!!

Regards,
Giri

and Connor said...

1. In a SQL*Plus session run the non-hinted SQL statement to begin the SQL plan baseline capture

SQL> variable b1 varchar2(30)
SQL> exec :b1 := 'Software/Other';

PL/SQL procedure successfully completed.

SQL>
SQL> set autotrace on explain
SQL> select prod_name, sum(amount_sold)
  2  from sh.sales s, sh.products p
  3  where s.prod_id=p.prod_id
  4  and prod_category = :b1
  5  group by prod_name;

PROD_NAME                                          SUM(AMOUNT_SOLD)
-------------------------------------------------- ----------------
OraMusic CD-R, Pack of 10                                  711741.8
CD-R with Jewel Cases, pACK OF 12                         170405.76
O/S Documentation Set - French                            590575.79
O/S Documentation Set - Italian                           296648.58
Keyboard Wrist Rest                                       348408.98
CD-R Mini Discs                                           384553.62
3 1/2" Bulk diskettes, Box of 100                         370204.56
External 101-key keyboard                                  456568.7
CD-RW, High Speed Pack of 5                               230233.35
DVD-R Disc with Jewel Case, 4.7 GB                        577420.62
DVD-RW Discs, 4.7GB, Pack of 3                            312036.54
CD-R, Professional Grade, Pack of 10                      170270.13
Unix/Windows 1-user pack                                 3543725.89
Mouse Pad                                                 306708.12
Laptop carrying case                                      623881.12
O/S Documentation Set - English                          1314478.74
O/S Documentation Set - Kanji                             509073.63
Music CD-R                                                 301848.2
CD-RW, High Speed, Pack of 10                             106468.41
DVD-R Discs, 4.7GB, Pack of 5                              904537.2
1.44MB External 3.5" Diskette                             219618.47
O/S Documentation Set - German                            604081.91
O/S Documentation Set - Spanish                           406840.96
3 1/2" Bulk diskettes, Box of 50                          254128.79
DVD-RAM Jewel Case, Double-Sided, 9.4G                    120395.81

25 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3584564314

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                      |    14 |   784 |   527   (3)| 00:00:01 |       |       |
|   1 |  HASH GROUP BY                        |                      |    14 |   784 |   527   (3)| 00:00:01 |       |       |
|*  2 |   HASH JOIN                           |                      |   183K|     9M|   522   (2)| 00:00:01 |       |       |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| PRODUCTS             |    14 |   658 |     3   (0)| 00:00:01 |       |       |
|*  4 |     INDEX RANGE SCAN                  | PRODUCTS_PROD_CAT_IX |    14 |       |     1   (0)| 00:00:01 |       |       |
|   5 |    PARTITION RANGE ALL                |                      |   918K|  8075K|   517   (2)| 00:00:01 |     1 |    28 |
|   6 |     TABLE ACCESS FULL                 | SALES                |   918K|  8075K|   517   (2)| 00:00:01 |     1 |    28 |
------------------------------------------------------------------------------------------------------------------------------

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

   2 - access("S"."PROD_ID"="P"."PROD_ID")
   4 - access("PROD_CATEGORY"=:B1)

Note
-----
   - this is an adaptive plan


2. Find SQL_ID for the statement in V$SQL

SQL> set autotrace off
SQL> select SQL_ID, SQL_FULLTEXT from v$SQL where upper(SQL_TEXT) like '%SELECT PROD_NAME, SUM(%';

SQL_ID        SQL_FULLTEXT
------------- --------------------------------------------------------------------------------
gkkbdz8hqd17n select prod_name, sum(amount_sold)
              from sh.sales s, sh.products p
              where s.prod_i

ayv56nq2qp4qq select SQL_ID, SQL_FULLTEXT from v$SQL where upper(SQL_TEXT) like '%SELECT PROD_
85dbpayqtyhns EXPLAIN PLAN SET STATEMENT_ID='PLUS1185966' FOR select prod_name, sum(amount_sol


3. Create a SQL plan baseline for the statement   

SQL> variable cnt number;
SQL> execute :cnt :=dbms_spm.load_plans_from_cursor_cache(sql_id=>'gkkbdz8hqd17n');

PL/SQL procedure successfully completed.



4. The plan that was captured is incorrect and will need to be disabled. The SQL_HANDLE & PLAN_NAME are required. These can found by looking in DBA_SQL_PLAN_BASELINE and using DBMS_SPM.ALTER_SQL_PLAN_BASELINE to disable the bad plan

SQL> select sql_handle, sql_text, plan_name, enabled from dba_sql_plan_baselines
  2  where created > sysdate - 1/ 24;^K

SQL_HANDLE
--------------------------------------------------------------------------------------------------------------------------------
SQL_TEXT
----------------------------------------------------------------
PLAN_NAME
--------------------------------------------------------------------------------------------------------------------------------
ENA
---
SQL_48206f252e8ac92a
select prod_name, sum(amount_sold)
from sh.sales s, sh.products p
where s.prod_i
SQL_PLAN_4h83g4nr8pk9a6ff7b491
YES




SQL> variable cnt number;
SQL> begin
  2    :cnt := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
  3                SQL_HANDLE=>'SQL_48206f252e8ac92a',
  4                PLAN_NAME=>'SQL_PLAN_4h83g4nr8pk9a6ff7b491',
  5                ATTRIBUTE_NAME=>'enabled',
  6                ATTRIBUTE_VALUE=>'NO');
  7  end;
  8  /

PL/SQL procedure successfully completed.



5. Modify the SQL statement using hints & execute it

SQL> set autotrace on explain
SQL> select /*+ full(p) */ prod_name, sum(amount_sold)
  2  from sh.sales s, sh.products p
  3  where s.prod_id=p.prod_id
  4  and prod_category = :b1
  5  group by prod_name;

PROD_NAME                                          SUM(AMOUNT_SOLD)
-------------------------------------------------- ----------------
OraMusic CD-R, Pack of 10                                  711741.8
CD-R with Jewel Cases, pACK OF 12                         170405.76
O/S Documentation Set - French                            590575.79
O/S Documentation Set - Italian                           296648.58
Keyboard Wrist Rest                                       348408.98
CD-R Mini Discs                                           384553.62
3 1/2" Bulk diskettes, Box of 100                         370204.56
External 101-key keyboard                                  456568.7
CD-RW, High Speed Pack of 5                               230233.35
DVD-R Disc with Jewel Case, 4.7 GB                        577420.62
DVD-RW Discs, 4.7GB, Pack of 3                            312036.54
CD-R, Professional Grade, Pack of 10                      170270.13
Unix/Windows 1-user pack                                 3543725.89
Mouse Pad                                                 306708.12
Laptop carrying case                                      623881.12
O/S Documentation Set - English                          1314478.74
O/S Documentation Set - Kanji                             509073.63
Music CD-R                                                 301848.2
CD-RW, High Speed, Pack of 10                             106468.41
DVD-R Discs, 4.7GB, Pack of 5                              904537.2
1.44MB External 3.5" Diskette                             219618.47
O/S Documentation Set - German                            604081.91
O/S Documentation Set - Spanish                           406840.96
3 1/2" Bulk diskettes, Box of 50                          254128.79
DVD-RAM Jewel Case, Double-Sided, 9.4G                    120395.81

25 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3535171836

--------------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |    14 |   784 |   527   (3)| 00:00:01 |       |       |
|   1 |  HASH GROUP BY        |          |    14 |   784 |   527   (3)| 00:00:01 |       |       |
|*  2 |   HASH JOIN           |          |   183K|     9M|   522   (2)| 00:00:01 |       |       |
|*  3 |    TABLE ACCESS FULL  | PRODUCTS |    14 |   658 |     3   (0)| 00:00:01 |       |       |
|   4 |    PARTITION RANGE ALL|          |   918K|  8075K|   517   (2)| 00:00:01 |     1 |    28 |
|   5 |     TABLE ACCESS FULL | SALES    |   918K|  8075K|   517   (2)| 00:00:01 |     1 |    28 |
--------------------------------------------------------------------------------------------------

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

   2 - access("S"."PROD_ID"="P"."PROD_ID")
   3 - filter("PROD_CATEGORY"=:B1)

Note
-----
   - this is an adaptive plan


6 Find new SQL_ID and PLAN_HASH_VALUE for the hinted SQL stmt

SQL> set autotrace off
SQL> select SQL_ID, PLAN_HASH_VALUE, SQL_FULLTEXT from v$SQL where upper(SQL_TEXT) like '%SELECT%FULL% PROD_NAME, SUM(%';

SQL_ID        PLAN_HASH_VALUE SQL_FULLTEXT
------------- --------------- --------------------------------------------------------------------------------
585hmwunrt2q7      3535171836 EXPLAIN PLAN SET STATEMENT_ID='PLUS1185966' FOR select /*+ full(p) */ prod_name,
ayv56nq2qp4qq       903671040 select SQL_ID, SQL_FULLTEXT from v$SQL where upper(SQL_TEXT) like '%SELECT PROD_
986n7n6b096cp       903671040 select SQL_ID, PLAN_HASH_VALUE, SQL_FULLTEXT from v$SQL where upper(SQL_TEXT) li
cqudpw3gaxduu      3535171836 select /*+ full(p) */ prod_name, sum(amount_sold)
                              from sh.sales s, sh.products p

gp6pddrxu30mn      3584564314 EXPLAIN PLAN SET STATEMENT_ID='PLUS1185966' FOR select /* + full(p) */ prod_name

6 rows selected.



7. Create new accepted plan for original SQL stmt by associating the modified plan to the original statement's SQL HANDLE 

SQL> begin
  2    :cnt:= dbms_spm.LOAD_PLANS_FROM_CURSOR_CACHE(
  3      SQL_ID=>'cqudpw3gaxduu',
  4      PLAN_HASH_VALUE=>3535171836,
  5      SQL_HANDLE=>'SQL_48206f252e8ac92a');
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> select sql_handle, sql_text, plan_name, enabled from dba_sql_plan_baselines
  2  where created > sysdate - 1/ 24;

SQL_HANDLE
--------------------------------------------------------------------------------------------------------------------------------
SQL_TEXT
----------------------------------------------------------------
PLAN_NAME
--------------------------------------------------------------------------------------------------------------------------------
ENA
---
SQL_48206f252e8ac92a
select prod_name, sum(amount_sold)
from sh.sales s, sh.products p
where s.prod_i
SQL_PLAN_4h83g4nr8pk9a42949306
YES

SQL_48206f252e8ac92a
select prod_name, sum(amount_sold)
from sh.sales s, sh.products p
where s.prod_i
SQL_PLAN_4h83g4nr8pk9a6ff7b491
NO


Hope this helps. Thanks for Nigel Bayliss (Optimizer Product Manager) for his assistance with this.

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Why not just pick the right plan?

Rajeshwaran, Jeyabal, March 09, 2016 - 10:20 am UTC

1. In a SQL*Plus session run the non-hinted SQL statement to begin the SQL plan baseline capture
2. Find SQL_ID for the statement in V$SQL
3. Create a SQL plan baseline for the statement
4. The plan that was captured is incorrect and will need to be disabled. The SQL_HANDLE & PLAN_NAME are required.
These can found by looking in DBA_SQL_PLAN_BASELINE and using DBMS_SPM.ALTER_SQL_PLAN_BASELINE to disable the bad plan
5. Modify the SQL statement using hints & execute it
6 Find new SQL_ID and PLAN_HASH_VALUE for the hinted SQL stmt
7. Create new accepted plan for original SQL stmt by associating the modified plan to the original statement's SQL HANDLE

Instead of this, why not just pick the right plan and fix it up in the plan-baselines ?

rajesh@ORA11G> show parameter optimizer

NAME                                          TYPE        VALUE
--------------------------------------------- ----------- -------------------------
optimizer_capture_sql_plan_baselines          boolean     FALSE
optimizer_dynamic_sampling                    integer     2
optimizer_features_enable                     string      11.2.0.4
optimizer_index_caching                       integer     0
optimizer_index_cost_adj                      integer     100
optimizer_mode                                string      ALL_ROWS
optimizer_secure_view_merging                 boolean     TRUE
optimizer_use_invisible_indexes               boolean     FALSE
optimizer_use_pending_statistics              boolean     FALSE
optimizer_use_sql_plan_baselines              boolean     TRUE
rajesh@ORA11G> variable b1 varchar2(30)
rajesh@ORA11G> exec :b1 := 'Software/Other';

PL/SQL procedure successfully completed.

rajesh@ORA11G> alter session set optimizer_features_enable='10.2.0.4';

Session altered.

rajesh@ORA11G> set serveroutput off
rajesh@ORA11G> select prod_name, sum(amount_sold)
  2  from sh.sales s, sh.products p
  3  where s.prod_id=p.prod_id
  4  and prod_category = :b1
  5  group by prod_name;

PROD_NAME                                          SUM(AMOUNT_SOLD)
-------------------------------------------------- ----------------
OraMusic CD-R, Pack of 10                                  711741.8
CD-R with Jewel Cases, pACK OF 12                         170405.76
O/S Documentation Set - French                            590575.79
O/S Documentation Set - Italian                           296648.58
Keyboard Wrist Rest                                       348408.98
CD-R Mini Discs                                           384553.62
3 1/2" Bulk diskettes, Box of 100                         370204.56
External 101-key keyboard                                  456568.7
CD-RW, High Speed Pack of 5                               230233.35
DVD-R Disc with Jewel Case, 4.7 GB                        577420.62
DVD-RW Discs, 4.7GB, Pack of 3                            312036.54
CD-R, Professional Grade, Pack of 10                      170270.13
Unix/Windows 1-user pack                                 3543725.89
Mouse Pad                                                 306708.12
Laptop carrying case                                      623881.12
O/S Documentation Set - English                          1314478.74
O/S Documentation Set - Kanji                             509073.63
Music CD-R                                                 301848.2
CD-RW, High Speed, Pack of 10                             106468.41
DVD-R Discs, 4.7GB, Pack of 5                              904537.2
1.44MB External 3.5" Diskette                             219618.47
O/S Documentation Set - German                            604081.91
O/S Documentation Set - Spanish                           406840.96
3 1/2" Bulk diskettes, Box of 50                          254128.79
DVD-RAM Jewel Case, Double-Sided, 9.4G                    120395.81

25 rows selected.

rajesh@ORA11G> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  gkkbdz8hqd17n, child number 0
-------------------------------------
select prod_name, sum(amount_sold) from sh.sales s, sh.products p where
s.prod_id=p.prod_id and prod_category = :b1 group by prod_name

Plan hash value: 1329740961

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                      |       |       |  1159 (100)|          |       |       |
|   1 |  HASH GROUP BY                |                      |    14 |   784 |  1159  (18)| 00:00:06 |       |       |
|*  2 |   HASH JOIN                   |                      |   183K|     9M|  1100  (13)| 00:00:06 |       |       |
|   3 |    TABLE ACCESS BY INDEX ROWID| PRODUCTS             |    14 |   658 |     3   (0)| 00:00:01 |       |       |
|*  4 |     INDEX RANGE SCAN          | PRODUCTS_PROD_CAT_IX |    14 |       |     1   (0)| 00:00:01 |       |       |
|   5 |    PARTITION RANGE ALL        |                      |   918K|  8075K|  1060  (10)| 00:00:06 |     1 |    28 |
|   6 |     TABLE ACCESS FULL         | SALES                |   918K|  8075K|  1060  (10)| 00:00:06 |     1 |    28 |
----------------------------------------------------------------------------------------------------------------------

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

   2 - access("S"."PROD_ID"="P"."PROD_ID")
   4 - access("PROD_CATEGORY"=:B1)


25 rows selected.

rajesh@ORA11G> exec dbms_output.put_line(dbms_spm.load_plans_from_cursor_cache(sql_id=>'gkkbdz8hqd17n',plan_hash_value=>1329740961));

PL/SQL procedure successfully completed.

rajesh@ORA11G> select t2.*
  2  from dba_sql_plan_baselines t1,
  3    table(dbms_xplan.display_sql_plan_baseline(t1.sql_handle,t1.plan_name)) t2
  4  /

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------
SQL handle: SQL_48206f252e8ac92a
SQL text: select prod_name, sum(amount_sold) from sh.sales s, sh.products p where
          s.prod_id=p.prod_id and prod_category = :b1 group by prod_name
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_4h83g4nr8pk9ac2717242         Plan id: 3262214722
Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD
--------------------------------------------------------------------------------

Plan hash value: 1329740961

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                      |    14 |   784 |  1159  (18)| 00:00:06 |       |       |
|   1 |  HASH GROUP BY                |                      |    14 |   784 |  1159  (18)| 00:00:06 |       |       |
|*  2 |   HASH JOIN                   |                      |   183K|     9M|  1100  (13)| 00:00:06 |       |       |
|   3 |    TABLE ACCESS BY INDEX ROWID| PRODUCTS             |    14 |   658 |     3   (0)| 00:00:01 |       |       |
|*  4 |     INDEX RANGE SCAN          | PRODUCTS_PROD_CAT_IX |    14 |       |     1   (0)| 00:00:01 |       |       |
|   5 |    PARTITION RANGE ALL        |                      |   918K|  8075K|  1060  (10)| 00:00:06 |     1 |    28 |
|   6 |     TABLE ACCESS FULL         | SALES                |   918K|  8075K|  1060  (10)| 00:00:06 |     1 |    28 |
----------------------------------------------------------------------------------------------------------------------

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

   2 - access("S"."PROD_ID"="P"."PROD_ID")
   4 - access("PROD_CATEGORY"=:B1)

31 rows selected.

rajesh@ORA11G> conn rajesh@ora11g
Enter password:
Connected.
rajesh@ORA11G> show parameter optimizer_features_enable

NAME                                          TYPE        VALUE
--------------------------------------------- ----------- -------------------------
optimizer_features_enable                     string      11.2.0.4
rajesh@ORA11G> set autotrace traceonly explain
rajesh@ORA11G> select prod_name, sum(amount_sold)
  2  from sh.sales s, sh.products p
  3  where s.prod_id=p.prod_id
  4  and prod_category = :b1
  5  group by prod_name;

Execution Plan
----------------------------------------------------------
Plan hash value: 1329740961

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                      |    14 |   784 |  1159  (18)| 00:00:01 |       |       |
|   1 |  HASH GROUP BY                |                      |    14 |   784 |  1159  (18)| 00:00:01 |       |       |
|*  2 |   HASH JOIN                   |                      |   183K|     9M|  1100  (13)| 00:00:01 |       |       |
|   3 |    TABLE ACCESS BY INDEX ROWID| PRODUCTS             |    14 |   658 |     3   (0)| 00:00:01 |       |       |
|*  4 |     INDEX RANGE SCAN          | PRODUCTS_PROD_CAT_IX |    14 |       |     1   (0)| 00:00:01 |       |       |
|   5 |    PARTITION RANGE ALL        |                      |   918K|  8075K|  1060  (10)| 00:00:01 |     1 |    28 |
|   6 |     TABLE ACCESS FULL         | SALES                |   918K|  8075K|  1060  (10)| 00:00:01 |     1 |    28 |
----------------------------------------------------------------------------------------------------------------------

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

   2 - access("S"."PROD_ID"="P"."PROD_ID")
   4 - access("PROD_CATEGORY"=:B1)

Note
-----
   - SQL plan baseline "SQL_PLAN_4h83g4nr8pk9ac2717242" used for this statement

rajesh@ORA11G> set autotrace off
rajesh@ORA11G>
rajesh@ORA11G>

Giri, March 09, 2016 - 2:40 pm UTC

Connor,

I tried the suggested option but when the baseline was created with the SQL ID and plan hash value of the hinted sql, it had a different plan.

The Baseline had completely a new plan and not the hinted plan.

I am not sure if this is a one off scenario. The Sql text and plan is little big, hence i didnt post here.

As this had stuck our migration dates, we had gone with hinting the sql statement with OFE 10.2.0.4 in the Procedure.

Just curious to know if there could be anything else that could cause this change during run time.

As usual, thanks much for your time for looking into this.

Any help/suggestion in this would be great.

Regards,
Giri
Chris Saxon
March 10, 2016 - 3:02 am UTC

I used a hinted plan *as a demonstration*... In your case, you could have just set optimizer_features_enabled to 10.2 and got the desired plan from that, and then locked in *that* plan using the method I described.

SQL Profile helped

Giri, March 11, 2016 - 8:49 pm UTC

Since the Baseline was not creating the desired plan, i used a different way for this issue.

Thanks to Kerry Osborne's Script for creating the SQL Profile manually by passing the hint as a parameter which fixed my problem.

Thanks Connor for taking your time and looking into this.

Regards,
Giri
Connor McDonald
March 11, 2016 - 10:49 pm UTC

Glad you got a solution.

SQL_Handle and SQL_ID

Ian, October 26, 2016 - 1:18 pm UTC

Hi

Is there any reason that SQL Plan Baselines use SQL_Handles and Plan_Names rather than SQL_IDs and plan_hash_values? And why the SQL Plan Baseline contains no reference to the SQL_ID and plan_hash_value?

Given that SQL_IDs and plan_hash_values are used everywhere else (V$, AWR etc), it would be kind of useful if the Baseline contained these values.

Just wondering.

Thanks

Ian
Connor McDonald
October 27, 2016 - 1:40 am UTC

Because they are *often* 1-to-1, they do not always have to be so. We do a normalisation of the sql text before creating a baseline, hence for example

SQL> create table t as select * From dba_objects;

Table created.

SQL>
SQL> create index IX1 on T ( owner );

Index created.

SQL>
SQL> select * from t where owner = 'QWE';

no rows selected

SQL> SELECT * FROM T WHERE owner = 'QWE';

no rows selected

SQL> select * FROM     t where owner = 'QWE';

no rows selected

SQL>
SQL> select sql_id, sql_text
  2  from v$sql
  3  where lower(sql_text) like '%qwe%'
  4  and sql_text not like '%v$sql%';

SQL_ID        SQL_TEXT
------------- ----------------------------------------------------------------
05rnznx171g60 select * from t where owner = 'QWE'
92pgjn6m5tpsk SELECT * FROM T WHERE owner = 'QWE'
cjt02x9a8msfd select * FROM     t where owner = 'QWE'

3 rows selected.

SQL>
SQL>
SQL> variable x number
SQL> exec :x := dbms_spm.load_plans_from_cursor_cache(attribute_name => 'SQL_TEXT',attribute_value => '%QWE%');

PL/SQL procedure successfully completed.

SQL> print x

         X
----------
         3


So 3 sql's and 3 plans loaded...But how many baselines ?

SQL>
SQL> select sql_handle, plan_name from DBA_SQL_PLAN_BASELINES;

SQL_HANDLE
----------------------------------------------------------------------------------------------------------------
PLAN_NAME
----------------------------------------------------------------------------------------------------------------
SQL_7e523a2febfc55a1
SQL_PLAN_7wnju5zpzspd136349550


1 row selected.


If you want to map between them, some good info here

http://oracleprof.blogspot.com.au/2011/07/how-to-find-sqlid-and-planhashvalue-in.html

http://kerryosborne.oracle-guy.com/2011/08/baselines-and-sql_id/
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.