SQL Server: INSERT INTO ... SELECT vs SELECT ... INTO

In a forum discussion ( in Chinese), a general comment implies that 'SELECT ... INTO' is faster than 'INSERT INTO ..SELECT' to load data to a table because of the minimal logging. However, I checked BOL document and found that we can also achieve minimal logging with 'INSERT INTO ... SELECT' construct given the requirements met as described below:

Using INSERT INTO .. SELECT to Bulk Load Data with Minimal Logging

You can use INSERT INTO SELECT FROM to efficiently transfer a large number of rows from one table, such as a staging table, to another table with minimal logging. Minimal logging can improve the performance of the statement and reduce the possibility of the operation filling the available transaction log space during the transaction . Minimal logging for this statement has the following requirements:

- The recovery model of the database is set to simple or bulk-logged.
- The target table is an empty or nonempty heap.
- The target table is not used in replication.
- The TABLOCK hint is specified for the target table.


BOL also describes the logging behaviour of SELECT ... INTO clause:

The amount of logging for SELECT...INTO depends on the recovery model in effect for the database. Under the simple recovery model or bulk-logged recovery model, bulk operations are minimally logged. With minimal logging, using the SELECT... INTO statement can be more efficient than creating a table and then populating the table with an INSERT statement.

Seeing is believing. So I planed several test cases in my SQL Express 2012 instance. In these test cases, I used different methods to populate 1M rows from an existing table called big_table to a new table called my_table.

Test case 1 using "SELECT ... INTO"

use dbadmin
go

drop table my_table;
go
  
DBCC SHRINKFILE (N'DBAdmin_log' , 0, TRUNCATEONLY)
go

declare @size_s  float 
declare @size_e  float

SELECT  @size_s = cntr_value
FROM sys.dm_os_performance_counters 
WHERE counter_name = 'Log File(s) Used Size (KB)'
AND instance_name = 'DBAdmin'

print 'start : ' + str(@size_s)

select  b.scattered_data, b.vc_small, b.id  into my_table
from big_table b 
 
SELECT  @size_e = cntr_value
FROM sys.dm_os_performance_counters 
WHERE counter_name = 'Log File(s) Used Size (KB)'
AND instance_name = 'DBAdmin'

print 'end  : ' +  str(@size_e)

PRINT 'size used (KB) =' + convert(varchar(30), @size_e - @size_s) 

I configured the database DBADMIN to different recovery mode and run the above code, here are the output

/*  test run 1 - simple 

(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
start :        428

(1000000 row(s) affected)
end  :        996
size used (KB) =568
*/

/* test run 2 - bulk-logged 
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
start :        450

(1000000 row(s) affected)
end  :       1078
size used (KB) =628

*/

/* test run 3  - full 

(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
start :        432

(1000000 row(s) affected)
end  :      33276
size used (KB) =32844

*/

It can be seen that with Simple or Bulk-logged mode, the transaction log used only 500-600 KB, while with Full recovery mode it is about 32 MB. After that, I have tested "INSERT INTO ... SELECT" with different recovery mode and also check the effect of 'TABLOCK' hint

Test case 2 using "INSERT INTO ... SELECT"

use dbadmin
go

drop table my_table;
go

create table my_table(
  A  int,
  B  varchar(10),
  C  int
  );

  
DBCC SHRINKFILE (N'DBAdmin_log' , 0, TRUNCATEONLY)
go

declare @size_s  float 
declare @size_e  float

SELECT  @size_s = cntr_value
FROM sys.dm_os_performance_counters 
WHERE counter_name = 'Log File(s) Used Size (KB)'
AND instance_name = 'DBAdmin'

print 'start : ' + str(@size_s)

-- insert into my_table (A,B,C)
insert into my_table with (TABLOCK) (A,B,C) 
select  b.scattered_data, b.vc_small, b.id 
from big_table b 
 
SELECT  @size_e = cntr_value
FROM sys.dm_os_performance_counters 
WHERE counter_name = 'Log File(s) Used Size (KB)'
AND instance_name = 'DBAdmin'

print 'end  : ' +  str(@size_e)

PRINT 'size used (KB) =' + convert(varchar(30), @size_e - @size_s)

Test results:

/* test run 1 - Simple
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
start :        450

(1000000 row(s) affected)
end  :       1009
size used (KB) =559

*/

/* test run 2  - Bulk-logged
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
start :        450

(1000000 row(s) affected)
end  :       1017
size used (KB) =567

*/

/*   test run 3 -- Full 
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
start :        450

(1000000 row(s) affected)
end  :      33408
size used (KB) =32958

*/

/* test run 4  -- Simple but without TABLOCK hint (Full, Bulk-logged have similar values)

(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
start :        428

(1000000 row(s) affected)
end  :     125834
size used (KB) =125406
*/

In the first three test run, I used the "TABLOCK" hint. It can be seen that in terms of the amount of transaction log used, using "INSERT INTO ... SELECT" with "TABLOCK" hint is essentially same as using "SELECT ... INTO" regardless of recovery mode. Without the "TABLOCK" hint, regardless of the recovery mode, using "INSERT INTO ... SELECT" will generate same amount of transaction log and 4 times more than that in the case of FULL recovery mode with the hint ( i.e. 125406 KB vs 32958 KB in my test case). People think "INSERT INTO ... SELECT" is slow may be because they don't always remember to add this hint when appropriate.

Drop indexes that have never been used

Here is the plan:


1. Identify the indexes that are not appeared in the execution plan


create table index_drop_candidates
as
select owner, index_name
from dba_indexes
where owner='INDEXOWNER'
minus
(
select object_owner, object_name
from v$sql_plan
where object_type ='INDEX'
and object_owner='INDEXOWNER'
union
select object_owner, object_name
from dba_hist_sql_plan
where object_type ='INDEX'
and object_owner='INDEXOWNER'
);


2. Exclude those indexes used for constraints


delete from index_drop_candidates
where index_name in
(
select constraint_name from dba_constraints where owner='INDEXOWNER' and constraint_type in ('P', 'U', 'R') ) ;



3. Run the following delete statement manually or put in the cron from time to time for some time

delete from index_drop_candidates
where index_name in
(
select object_name
from v$sql_plan
where object_type ='INDEX'
and object_owner='INDEXOWNER'
union
select object_name
from dba_hist_sql_plan
where object_type ='INDEX'
and object_owner='INDEXOWNER'
);


4. After some time, change those indexes still in the index_drop_candidates table to be invisible to observe any performance impact and decide if drop them accordingly



Note: There are cases that indexes have never been used in the execution plan, but they are needed to help CBO to choose a better plan. If such indexes are dropped, performance could be negatively impacted.

Install Sample AdventureWorksLT2012 OLTP database on my SQL Server 2012 Express

I downloaded the mdf file from http://msftdbprodsamples.codeplex.com/releases/view/55330 and placed it at C:\mssql_data. The in SSMS window, I issued:

CREATE DATABASE AdventureWorks2012
ON (FILENAME = 'C:\mssql_data\AdventureWorksLT2012_Data.mdf')
FOR ATTACH_REBUILD_LOG ;

The message recieved are as follows:

File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorksLT2012_log.ldf" may be incorrect.
New log file 'C:\mssql_data\AdventureWorksLT2012_Data_log.ldf' was created.
Converting database 'AdventureWorks2012' from version 705 to the current version 706.
Database 'AdventureWorks2012' running the upgrade step from version 705 to version 706.

It should be noted that I have to change the file permission in order to install it sucessfully. The screen shot for the file permission is shown below:

The "full control" should be selected.

SQL Server - Query shows hourly log backup size of a database in the tabular format

The query uses PIVOT feature:

--- in SQL Server, for databases using FULL recovery model, their transaction logs are backed up
--- quite frequently in a typical env. This query gives hourly log backup size of a database for 
--- each day in the tabular format
with B (backup_day, backup_hour, LogBackupSize_MB)
as
(
 select backup_day, backup_hour, cast(sum(backup_size)/1000000 as int) LogBackupSize_MB
 from 
 (
 select 
        convert(char(23), bs.backup_start_date, 111) as backup_day
      , cast(datepart(hh, bs.backup_start_date) as varchar) backup_hour 
      , bs.backup_size as  backup_size
 from
    msdb.dbo.backupset bs  
 WHERE  (CONVERT(datetime, bs.backup_start_date, 102) >= GETDATE() - 14)  
 and bs.database_name = 'MYDBNAME'
 and bs.type='L' 
 ) A
 group by backup_day, backup_hour
) 
select * from B  -- cols to pivot: backup_day, hour, LogBackupSize_MB
pivot (
   max(LogBackupSize_MB) -- Pivot on this column
   for backup_hour in ([0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],
                [13],[14],[15], [16],[17],[18],[19],[20],[21],[22],[23],[24] ))  -- make backup_hour one of this     
   as LogBackupSizeHourly  -- Pivot table alias

In one of our production SQL Server databases, the transaction log backup happens every 15 min. Below is the sample output from the query:

SQL Server - Script to show the size of all databases and database files

As a practise to write T-SQL code, comes up with the following script:

-- show size of all databases and database files
-- uncommenet out the following 4 lines if used in  sqlcmd

:setvar sqlcmdheaders            40     -- "page size"
:setvar sqlcmdcolwidth          132     -- line width
:setvar sqlcmdmaxfixedtypewidth  32     -- max column width (fixed length)
:setvar sqlcmdmaxvartypewidth    32     -- max column width (varying length)




IF Object_id(N'tempdb..#tempTbl') IS NOT NULL
DROP TABLE #temptbl


CREATE TABLE #tempTbl
( DBName VARCHAR(50),
  DBFileName VARCHAR(50),
  PhysicalFileName NVARCHAR(260),
  FileSizeMB decimal(18,1)
)



declare @cmd1 varchar(500)
set @cmd1 ='
insert into #tempTbl
SELECT ''?'' as DBName
   ,cast(f.name as varchar(25)) as DBFileName
   ,f.Filename as PhysicalFileName
   ,cast (round((f.size*8)/1024.0,2) as decimal(18,2)) as FileSizeinMB
FROM ?..SYSFILES f
'
exec sp_MSforeachdb @command1=@cmd1


select *
from #tempTbl
order by DBName

select case when (GROUPING(DBName)=1) then '*** Total size ***'
       else isnull(DBname, 'UNKNOWN')
       end AS DBName
      ,SUM(FileSizeMB) As FileSizeMBSum
from #tempTbl
group by DBName with ROLLUP
order by FileSizeMBSum

Sample output when used in sqlcmd:

$ sqlcmd -i dbfile_size.sql
DBName                           DBFileName                       PhysicalFileName                 FileSizeMB
-------------------------------- -------------------------------- -------------------------------- --------------------
DBAdmin                          DBAdmin                          c:\Program Files\Microsoft SQL S                  4.0
DBAdmin                          DBAdmin_log                      c:\Program Files\Microsoft SQL S                  1.0
DBAdmin                          DBAdmin_P01                      c:\Program Files\Microsoft SQL S                  4.0
master                           master                           c:\Program Files\Microsoft SQL S                  4.0
master                           mastlog                          c:\Program Files\Microsoft SQL S                   .8
model                            modeldev                         c:\Program Files\Microsoft SQL S                  3.1
model                            modellog                         c:\Program Files\Microsoft SQL S                   .8
msdb                             MSDBData                         c:\Program Files\Microsoft SQL S                 13.8
msdb                             MSDBLog                          c:\Program Files\Microsoft SQL S                   .8
ReportServer$SQLEXPRESS          ReportServer$SQLEXPRESS          c:\Program Files\Microsoft SQL S                  5.1
ReportServer$SQLEXPRESS          ReportServer$SQLEXPRESS_l        c:\Program Files\Microsoft SQL S                  4.2
ReportServer$SQLEXPRESSTempDB    ReportServer$SQLEXPRESSTe        c:\Program Files\Microsoft SQL S                  4.1
ReportServer$SQLEXPRESSTempDB    ReportServer$SQLEXPRESSTe        c:\Program Files\Microsoft SQL S                  1.1
tempdb                           tempdev                          c:\Program Files\Microsoft SQL S                  3.1
tempdb                           templog                          c:\Program Files\Microsoft SQL S                   .8

(15 rows affected)
DBName                           FileSizeMBSum
-------------------------------- ----------------------------------------
model                                                                 3.9
tempdb                                                                3.9
master                                                                4.8
ReportServer$SQLEXPRESSTempDB                                         5.2
DBAdmin                                                               9.0
ReportServer$SQLEXPRESS                                               9.3
msdb                                                                 14.6
*** Total size ***                                                   50.7

(8 rows affected)

Using SQL Performance Analyzer in the planning of 11g upgrade for a critical database

In my working environment, there is a one-way replication setup from database ttpseprd to ttpsefh. Database ttpseprd has OLTP-type workload and ttpsefh is a reporting database. The two databases have almost same schemas and objects. Some time ago ttpsefh was upgraded from 10g to 11g and now the ttpseprd is planned to be upgraded to 11g soon. So the ttpsefh could be an ideal environment to test if there are any SQLs currentely run in the 10g ttpseprd database would be regressed after upgrade. For that purpose, I have conducted several SQL Peformnace Analyzer (SPA) experiments in a test 11gR2 database environment called dbahrgrd.

From the Grid Control homepage of the dbahrgrd database instance, by clicking the following links: Advisor Central => SQL Performance Analyzer => Guided Workflow, I can see the following five steps regarding how to execute a successful two-trial SPA test:

1. Create SQL Performance Analyzer Task based on SQL Tuning Set
2. Create SQL Trial in Initial Environment
3. Create SQL Trial in Changed Environment
4. Compare Step 2 and Step 3
5. View Trial Comparison Report

To complete the step 1, I need a STS obviously. I first created a STS called STS_TTPSEPRD_10G in the ttpseprd database and used OEM Grid Control to load the set from past 7 days of AWR data and from cursors in the shared pool as well. Behind the scene, the code used by OEM looks like:

DECLARE 
   sqlset_cur dbms_sqltune.sqlset_cursor;
   bf VARCHAR2(80); 
BEGIN 
   bf := q'#UPPER(PARSING_SCHEMA_NAME) IN ('TTQMIGADMIN', 'TTQ2TACPVIEW', 'TTQ2_TACP') #'; 
   OPEN sqlset_cur FOR 
      SELECT VALUE(P) 
         FROM TABLE( dbms_sqltune.select_workload_repository( '57490', '58146', bf, NULL, NULL, NULL, NULL, 1, NULL, NULL)) P; 
   dbms_sqltune.load_sqlset( sqlset_name=>'STS_TTPSEPRD_10G'
                           , populate_cursor=>sqlset_cur
                           , load_option => 'MERGE'
                           , update_option => 'ACCUMULATE'
                           , sqlset_owner=>'VUSERID'); 
END;
 


DECLARE 
   sqlset_cur dbms_sqltune.sqlset_cursor; 
   bf VARCHAR2(80); 
BEGIN 
   bf := q'#UPPER(PARSING_SCHEMA_NAME) IN ('TTQMIGADMIN', 'TTQ2TACPVIEW', 'TTQ2_TACP') #'; 
   OPEN sqlset_cur FOR 
      SELECT VALUE(P) FROM TABLE( dbms_sqltune.select_cursor_cache(bf, NULL, NULL, NULL, NULL, 1, NULL, 'TYPICAL')) P; 
   dbms_sqltune.load_sqlset( sqlset_name=>'STS_TTPSEPRD_10G'
                           , populate_cursor=>sqlset_cur
                           , load_option => 'MERGE'
                           , update_option => 'ACCUMULATE'
                           , sqlset_owner=>'VUSERID'); 
END;

Then following the procedure described in this Oracle document , I've transported STS_TTPSEPRD_10G from ttpseprd to dbahrgrd. There are 25993 SQLs in the set originally. I deleted all SQLs starting with 'insert', 'update' and 'detele' using the following syntax:

BEGIN
  DBMS_SQLTUNE.DELETE_SQLSET(
      sqlset_name  => 'STS_TTPSEPRD_10G',
      basic_filter => 'UPPER(SQL_TEXT) LIKE ''DELETE%''');
END;
/

After the deletion, the total number of the SQLs in the set come down to 19201. The SQLs in the set can be further categorized based on the number of executions. The following queries showed the number of SQLs in each category based on the executions range I chose.

SQL> select count(*) from DBA_SQLSET_STATEMENTS WHERE SQLSET_OWNER ='VUSERID'  AND SQLSET_NAME= 'STS_TTPSEPRD_10G' and executions > 200;

  COUNT(*)
----------
      3210

SQL> select count(*) from DBA_SQLSET_STATEMENTS WHERE SQLSET_OWNER ='VUSERID'  AND SQLSET_NAME= 'STS_TTPSEPRD_10G' and executions between 5 and 200;

  COUNT(*)
----------
      3509

SQL> select count(*) from DBA_SQLSET_STATEMENTS WHERE SQLSET_OWNER ='VUSERID'  AND SQLSET_NAME= 'STS_TTPSEPRD_10G' and executions between 2 and 4;

  COUNT(*)
----------
      8160

SQL> select count(*) from DBA_SQLSET_STATEMENTS WHERE SQLSET_OWNER ='VUSERID'  AND SQLSET_NAME= 'STS_TTPSEPRD_10G' and executions=1;

  COUNT(*)
----------
      4322

It can be seen that there are many SQLs with executions equal one. Most of them was due to SQLs not using bind variables. For each category, I created a corresponding STS and did a SPA test on it. Below I will describe the test on the STS_TTPSEPRD_4 that contains SQLs with executions > 200.

The STS_TTPSEPRD_4 was created through the following code:

-- create a new STS from an existing STS
BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET(
    sqlset_name => 'STS_TTPSEPRD_10G_4', 
    description  => 'subset of STS_SSODPRD_10G executions > 200');
END;
/

-- select from a sql tuning set and pass cursor to load_sqlset
DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT VALUE (P) 
    FROM table(dbms_sqltune.select_sqlset(
        'STS_TTPSEPRD_10G',
        basic_filter => 'executions > 200' 
   )) P;
 
  -- Process each statement (or pass cursor to load_sqlset)

    DBMS_SQLTUNE.LOAD_SQLSET(
             sqlset_name     => 'STS_TTPSEPRD_10G_4',
             populate_cursor => cur);
 
  CLOSE cur;
END;
/

With the STS ready, it is straightforward to create the SPA test task through OEM.

In step 2, I executed the SQLs  in the 10g ttpseprd database:

begin 
  dbms_sqlpa.execute_analysis_task(task_name => 'TTPSEPRD_TASK4'
            , execution_type => 'TEST EXECUTE'
            , execution_name => 'SQL_TRIAL_1349293404432'
            , execution_desc => 'execute in prd'
            , execution_params => dbms_advisor.arglist('LOCAL_TIME_LIMIT', '300', 'TIME_LIMIT', 'UNLIMITED', 'DATABASE_LINK', 'TTPSEPRD.MYCOMPA.COM')
                                  ); 
end;

In step 3, I executed the SQLs in the 11g ttpsefh database:

begin 
  dbms_sqlpa.execute_analysis_task(task_name => 'TTPSEPRD_TASK4'
                                 , execution_type => 'TEST EXECUTE'
                                 , execution_name => 'SQL_TRIAL_1349294911119'
                                 , execution_desc => 'in ttpsefh'
                                 , execution_params => dbms_advisor.arglist('LOCAL_TIME_LIMIT', '300', 'TIME_LIMIT', 'UNLIMITED', 'DATABASE_LINK', 'TTPSEFH.MYCOMPA.COM')
                                  ); 
end;

It should be noted that in order to execute step 3 and 4, a database link should be valid from dbahrgrd to ttpsefhd and ttpseprd, respectively. In addition, the connecting user should be granted ADVISOR role and the privillege to execute SYS.DBMS_SQLPA procedure.

In step 4, I did comparsizon based on Buffer Gets:

begin 
  dbms_sqlpa.execute_analysis_task(task_name => 'TTPSEPRD_TASK4'
                                 , execution_type => 'compare performance'
                                 , execution_name => 'COMPARE_1349297988711'
                                 , execution_params => dbms_advisor.arglist('comparison_metric', 'BUFFER_GETS', 'execution_name1', 
                                           'SQL_TRIAL_1349293404432', 'execution_name2', 'SQL_TRIAL_1349294911119', 'TIME_LIMIT', 'UNLIMITED')
                                  ); 
end;

At last,  come to the exciting part - step 5. It took just one click to view the result. The following is a screenshot of the overall report.

It can be seen that there are Improvement Impact 21% and Regression Impact -2% in the 11g environment. By click the -2% link, we can check in detail about the regressed SQL. In this case only one SQL found and actually the plan was same essentially. So no real regression at all. I did similar tests on other three categories of SQLs. With these experiments, I am very confident that the upgrade will be successful at least in the area of SQL performance.

Create a SQL Plan Baseline based on the good execution plan in AWR

We can create SQL plan baseline for a SQL to ensure the SQL execute with a good execution plan in 11g. The desired good execution plan sometimes can be obtained from AWR.


Today, a colleague sent team a note about the steps he adopted to create a SQL plan baseline for fixing a problem reporting query as follows:

1) -- create a SQL tuning set

exec DBMS_SQLTUNE.CREATE_SQLSET('test');

2) -- load the SQL tuning set with the good execution plan from AWR

declare
baseline_ref_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
begin
open baseline_ref_cursor for
select VALUE(p) from table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(70837, 71044,
'sql_id=' || CHR(39)||'8w0vxcj017b0m'||CHR(39)'  and plan_hash_value=2753253816',NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;

DBMS_SQLTUNE.LOAD_SQLSET('test', baseline_ref_cursor);
end;
/



3) -- Verify the good executon plan

SELECT * FROM table (
DBMS_XPLAN.DISPLAY_SQLSET(
'test','8w0vxcj017b0m'));


4) -- Verify the sql text in the sql tuing set

select *
from dba_sqlset_statements
where sqlset_name = 'test'
order by sql_id;


5) -- create the sql plan baseline from sql tuning set



set serveroutput on
declare
my_integer pls_integer;

begin
my_integer := dbms_spm.load_plans_from_sqlset (
sqlset_name => 'test',
sqlset_owner => 'Vxxxxx',
fixed => 'NO',
enabled => 'YES');
DBMS_OUTPUT.PUT_line(my_integer);
end;
/



6) -- verify

select * from dba_sql_plan_baselines



update Aug. 26, 2013 -

In the latest sqlt tool utl directory, there is a script called  coe_load_sql_baseline.sql
, which can be used to create sql plan baseline for  a sql based on sqlid and plan hash value conveniently.

Ref: SQLT (SQLTXPLAIN) - Tool that helps to diagnose a SQL statement performing poorly or one that produces wrong results (Doc ID 215187.1)

Why 11g chooses the access path of index scan with inlist as filter?

In a previous post ( http://oracle-study-notes.blogspot.com/2012/07/using-sql-plan-baseline-to-ensure-10g.html), I described that a SQL performed badly after upgrade from 10g to 11g due to the change of execution plan and I used "SQL Plan Baseline" feature to force the sql executing with the good plan. The bad plan uses "inlist as filter" in an index scan access path. In this post I will try to understand why the execution plan changed from 10g to 11g. To simplify the investigation, I only explored a single table access query shown as follows:

select fds_seq,order_id 
from  fds
where
     fds.status IN ( 'LD', 'RT' )  
and  fds.thread_id = '1'  
;

In this query, the table fds is created from the production table by CTAS and it has only one index fds_ix1 on (thread_id,status). After upgrade to 11g, we used the same statistics gathering script as in 10g. I collected the stats for table fds using the same options as those in the 11g or 10g database:

exec dbms_stats.gather_table_stats(user,'FDS',method_opt => 'FOR ALL COLUMNS SIZE 1', estimate_percent =>10, cascade => TRUE,degree => 4) ;

The column statistics of this table is shown below:

COLUMN_NAME                      DISTINCT DENSITY      NULLS BKTS LO               HI
------------------------------ ---------- ------- ---------- ---- ---------------- ----------------
FDS_SEQ                         101188250   0.000          0    1          2756031        109967122
ORDER_ID                         19009758   0.000   49654490    1 CADX010101010    RMOG293464421,CM
RETRY_COUNT                            48   0.021  100892510    1 1                9
STATUS                                 14   0.071          0    1 DD               XX
THREAD_ID                           46932   0.000          0    1 0                bnscqpa4+9999
TIMESTAMP                        22878788   0.000          0    1 27-DEC-2004      23-JUL-2012

In the 11g (11.2.0.3) database, the execution plan of the query and execution statistics are obtained from AUTOTRACE:

Execution Plan
----------------------------------------------------------
Plan hash value: 137039772

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |  4312 |   101K|    63   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| FDS     |  4312 |   101K|    63   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | FDS_IX1 |  2224 |       |    10   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   2 - access("FDS"."THREAD_ID"='1')
       filter("FDS"."STATUS"='LD' OR "FDS"."STATUS"='RT')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
     153373  consistent gets
     153361  physical reads
          0  redo size
      19755  bytes sent via SQL*Net to client
        294  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        696  rows processed

By looking at the the Predicate Information section, we can know that Oracle chooses "inlist as filiter" access path. I also created the same FDS table in a 10g (10.2.0.4) database and analyzed it with same option. The columns stats shown as follows:

COLUMN_NAME                      DISTINCT DENSITY      NULLS BKTS LO               HI
------------------------------ ---------- ------- ---------- ---- ---------------- ----------------
FDS_SEQ                         101182940   0.000          0    1          2756031        109967120
ORDER_ID                         19024012   0.000   49606920    1 CADX010101010    RMOG292175979,CM
RETRY_COUNT                            47   0.021  100889040    1 1                9
STATUS                                 14   0.071          0    1 DD               XX
THREAD_ID                           46926   0.000          0    1 0                bnscqpa4+9999
TIMESTAMP                        22875629   0.000          0    1 27-DEC-2004      23-JUL-2012

It can be seen that the column statistics of the table is almost identifical in 10g and 11g. However, the AUTOTRACE report in the 10g database shows the different execution plan and much less gets-per-execution value (132 vs 153373)

Execution Plan
----------------------------------------------------------
Plan hash value: 3825939584

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |   308 |  7392 |    13   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR             |         |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| FDS     |   308 |  7392 |    13   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | FDS_IX1 |   315 |       |     5   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   3 - access("FDS"."THREAD_ID"='1' AND ("FDS"."STATUS"='LD' OR
              "FDS"."STATUS"='RT'))


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        132  consistent gets
        105  physical reads
          0  redo size
       8924  bytes sent via SQL*Net to client
        289  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        696  rows processed

I did 10053 traces for the query in 10g and 11g database respectivly. The 10053 trace excerpts are shown in Appendix. It can be seen that Oracle CBO evaluated both execution plans in 10g and 11g. The following table gives the cost of each plan in 10g and 11g as seen in the trace

------------------------------------------------------
Plan        Index scan with           Index scan with  
            inlist as filter          INLIST ITERATOR
------------------------------------------------------
10.2.0.4           18.00                      13.03 
11.2.0.3           63.27                     122.30 
-------------------------------------------------------

Based on the cost value, it is easy to understand that 10g and 11g choose different plans as their "best" plan. By comparing the 10053 trace, I think one key difference that leads to the different cost values lies in the index selectivity of the two-column index FDS_IX1. Index selectivity: FDS_IX1 on (thread_id, status )

------------------------------------------------------------------------------
Plan        Index  selectivity           #DK of        NDV of            NDV of
                                        Index         thread_id         status
-------------------------------------------------------------------------------
10.2.0.4         3.0443e-06              46926          46926             14 
11.2.0.3           0.000043              46932          46932             14
------------------------------------------------------------------------------

In 10g: index selectivity = (1/46926 ) * (1/14) *2 = 3.0443e-06 In 11g: index selectivity = (1/46932 ) * 2 = 0.0000043 It shows that Oracle uses different the formula to calculate the two-column index selectivity. In 10g the selectivity is based on 1/num_distinct for the two columns; while in 11g 1/distinct_keys for the index. I believe this difference on index selectivity explains the execution plan changes from 10g to 11g in this particular case, especially we use same stats gathering option in  11g as in 10g ( i.e. no histogram)

Appendix - 10053 trace:

-- script 
alter session set events '10053 trace name context forever';

explain plan for
select fds_seq,order_id 
from  fds
where
     fds.status IN ( 'LD', 'RT' )  
and  fds.thread_id = '1'  
;

alter session set events '10053 trace name context off';


---- from  a 10g database -----------

*****************************
SYSTEM STATISTICS INFORMATION
*****************************
  Using NOWORKLOAD Stats
  CPUSPEED: 765 millions instruction/sec
  IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
  IOSEEKTIM: 10 milliseconds (default is 10)
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: FDS  Alias: FDS
    #Rows: 101182940  #Blks:  499443  AvgRowLen:  31.00
Index Stats::
  Index: FDS_IX1  Col#: 5 2
    LVLS: 3  #LB: 285370  #DK: 46926  LB/K: 27.00  DB/K: 223.00  CLUF: 2337470.00
***************************************
SINGLE TABLE ACCESS PATH
  -----------------------------------------
  BEGIN Single Table Cardinality Estimation
  -----------------------------------------
  Column (#2): STATUS(CHARACTER)
    AvgLen: 3.00 NDV: 14 Nulls: 0 Density: 0.071429
  Column (#5): THREAD_ID(VARCHAR2)
    AvgLen: 6.00 NDV: 46926 Nulls: 0 Density: 2.1310e-05
  Table: FDS  Alias: FDS
    Card: Original: 101182940  Rounded: 308  Computed: 308.03  Non Adjusted: 308.03
  -----------------------------------------
  END   Single Table Cardinality Estimation
  -----------------------------------------
  Access Path: TableScan
    Cost:  112728.85  Resp: 112728.85  Degree: 0
      Cost_io: 109255.00  Cost_cpu: 31889016166
      Resp_io: 109255.00  Resp_cpu: 31889016166
  Access Path: index (RangeScan)
    Index: FDS_IX1
    resc_io: 13.00  resc_cpu: 249823
    ix_sel: 3.0443e-06  ix_sel_with_filters: 3.0443e-06
    Cost: 13.03  Resp: 13.03  Degree: 1
  Considering index with inlist as filter
  Access Path: index (RangeScan)
    Index: FDS_IX1
    resc_io: 18.00  resc_cpu: 794233
    ix_sel: 2.1310e-05  ix_sel_with_filters: 3.0443e-06
    Cost: 18.09  Resp: 18.09  Degree: 1
  Rejected inlist as filter
  ****** trying bitmap/domain indexes ******
  ****** finished trying bitmap/domain indexes ******
  Best:: AccessPath: IndexRange  Index: FDS_IX1
         Cost: 13.03  Degree: 1  Resp: 13.03  Card: 308.03  Bytes: 0
***************************************



--- from a 11g database ----------------

-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
  Using NOWORKLOAD Stats
  CPUSPEEDNW: 769 millions instructions/sec (default is 100)
  IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
  IOSEEKTIM:  10 milliseconds (default is 10)
  MBRC:       NO VALUE blocks (default is 16)
 
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: FDS  Alias: FDS
    #Rows: 101188250  #Blks:  500926  AvgRowLen:  32.00  ChainCnt:  0.00
Index Stats::
  Index: FDS_IX1  Col#: 5 2
    LVLS: 3  #LB: 290350  #DK: 46932  LB/K: 27.00  DB/K: 228.00  CLUF: 2450470.00
Access path analysis for FDS
***************************************
SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for FDS[FDS]
  Column (#2): STATUS(
    AvgLen: 3 NDV: 14 Nulls: 0 Density: 0.071429
  Column (#5): THREAD_ID(
    AvgLen: 6 NDV: 46932 Nulls: 0 Density: 0.000021
  ColGroup (#1, Index) FDS_IX1
    Col#: 2 5    CorStregth: 14.00
  ColGroup Usage:: PredCnt: 2  Matches Full: #1  Partial:  Sel: 0.0000
  Table: FDS  Alias: FDS
    Card: Original: 101188250.000000  Rounded: 4312  Computed: 4312.12  Non Adjusted: 4312.12
  Access Path: TableScan
    Cost:  113037.39  Resp: 113037.39  Degree: 0
      Cost_io: 109579.00  Cost_cpu: 31901063983
      Resp_io: 109579.00  Resp_cpu: 31901063983
  ColGroup Usage:: PredCnt: 2  Matches Full: #1  Partial:  Sel: 0.0000
  ColGroup Usage:: PredCnt: 2  Matches Full: #1  Partial:  Sel: 0.0000
  Access Path: index (RangeScan)
    Index: FDS_IX1
    resc_io: 122.00  resc_cpu: 2803250
    ix_sel: 0.000043  ix_sel_with_filters: 0.000043
    Cost: 122.30  Resp: 122.30  Degree: 1
  Considering index with inlist as filter
 
  ColGroup Usage:: PredCnt: 2  Matches Full: #1  Partial:  Sel: 0.0000
  Access Path: index (RangeScan)
    Index: FDS_IX1
    resc_io: 63.00  resc_cpu: 2477875
    ix_sel: 0.000021  ix_sel_with_filters: 0.000021

 ***** Logdef predicate Adjustment ******
 Final IO cst 0.00 , CPU cst 0.00
 ***** End Logdef Adjustment ******
    Cost: 63.27  Resp: 63.27  Degree: 1
  Accepted inlist as filter
  ****** trying bitmap/domain indexes ******
  ****** finished trying bitmap/domain indexes ******
  Best:: AccessPath: IndexRange
  Index: FDS_IX1
         Cost: 63.27  Degree: 1  Resp: 63.27  Card: 4312.12  Bytes: 0
 
***************************************

Notes:
(1) In 11g, by fooling CBO through setting column stats manually, I am able to get the 10g access path with much better gets per execution:

Execution Plan
----------------------------------------------------------
Plan hash value: 3825939584

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |  4040 | 96960 |   119   (0)| 00:00:02 |
|   1 |  INLIST ITERATOR             |         |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| FDS     |  4040 | 96960 |   119   (0)| 00:00:02 |
|*  3 |    INDEX RANGE SCAN          | FDS_IX1 |  4229 |       |    15   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   3 - access("FDS"."THREAD_ID"='1' AND ("FDS"."STATUS"='LD' OR
              "FDS"."STATUS"='RT'))


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        132  consistent gets
        104  physical reads
          0  redo size
      19755  bytes sent via SQL*Net to client
        294  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        696  rows processed

(2)If we re-write the original query with UNION ALL, we also obtain a much better execution plan:

SQL> select fds_seq,order_id
  2  from  fds
  3  where
  4       fds.status IN ( 'LD' )
  5  and  fds.thread_id = '1'
  6  union all
  7  select fds_seq,order_id
  8  from fds
  9  where
 10       fds.status IN ('RT' )
 11  and  fds.thread_id = '1'
 12  ;

696 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3716244554

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |  4040 | 96960 |   122  (50)| 00:00:02 |
|   1 |  UNION-ALL                   |         |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| FDS     |  2020 | 48480 |    61   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | FDS_IX1 |  2115 |       |     9   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| FDS     |  2020 | 48480 |    61   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | FDS_IX1 |  2115 |       |     9   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   3 - access("FDS"."THREAD_ID"='1' AND "FDS"."STATUS"='LD')
   5 - access("FDS"."THREAD_ID"='1' AND "FDS"."STATUS"='RT')


Statistics
----------------------------------------------------------
         21  recursive calls
          0  db block gets
        156  consistent gets
        108  physical reads
          0  redo size
       8851  bytes sent via SQL*Net to client
        294  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
        696  rows processed

(3) In the 11g database, under some circumstances when gathering table stats, there could probably be histograms on the columns and Oracle could generate optimal execution plan in this case. (I did observe in one case when played around stats gathering on the FDS table)

Using SQL Plan Baseline to ensure a 10g execution plan after upgrade to 11g

Recently I have got a chance to use the SQL Plan Management feature - SQL Plan Baseline to fix a regressed sql after upgrade to 11g from 10g. The problem sql looks like:

SELECT 
       fc.gjmfmpbe_type, 
       fc.gjmfmpbe_config_seq, 
       fi.gjmfmpbe_info_seq, 
       fd.gjmfmpbe_detail_seq, 
       fi.physical_gjmf_date, 
       fi.gjmf_name, 
       fi.mpbe_timestamp, 
       fd.gjmf_detail, 
       fc.mog_prefix, 
       fd.gjmf_detail_enc, 
       fds.retry_count, 
       fc.retry_config 
FROM   
       gjmfmpbe_config fc, 
       gjmfmpbe_detail_status fds,
       gjmfmpbe_detail fd, 
       gjmfmpbe_info fi 
WHERE  ( fi.gjmfmpbe_info_seq = fd.gjmfmpbe_info_seq ) 
       AND ( fds.gjmfmpbe_detail_seq = fd.gjmfmpbe_detail_seq ) 
       AND ( fi.gjmfmpbe_config_seq = fc.gjmfmpbe_config_seq ) 
       AND ( fds.status IN ( 'LD', 'RT' ) ) 
       AND ( fc.gjmf_directory = 'BillCycle' ) 
       AND ( fds.thread_id = '1' ) 
ORDER  BY fd.gjmfmpbe_detail_seq;  

The execution plans of this sql in 10g and 11g are shown below, respectively:

10 g execution plan
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                            |       |       |  1477 (100)|          |
|   1 |  SORT ORDER BY                    |                            |    13 |  4641 |  1477   (1)| 00:00:18 |
|   2 |   NESTED LOOPS                    |                            |    13 |  4641 |  1476   (1)| 00:00:18 |
|   3 |    NESTED LOOPS                   |                            |   486 |   116K|   990   (1)| 00:00:12 |
|   4 |     MERGE JOIN CARTESIAN          |                            |   486 | 25758 |    17   (0)| 00:00:01 |
|   5 |      TABLE ACCESS BY INDEX ROWID  | GJMFMPBE_CONFIG            |     1 |    34 |     2   (0)| 00:00:01 |
|   6 |       INDEX RANGE SCAN            | GJMFMPBE_CONFIG_FILEDIR    |     1 |       |     1   (0)| 00:00:01 |
|   7 |      BUFFER SORT                  |                            |   486 |  9234 |    15   (0)| 00:00:01 |
|   8 |       INLIST ITERATOR             |                            |       |       |            |          |
|   9 |        TABLE ACCESS BY INDEX ROWID| GJMFMPBE_DETAIL_STATUS     |   486 |  9234 |    15   (0)| 00:00:01 |
|  10 |         INDEX RANGE SCAN          | GJMFMPBE_DETAIL_STATUS_IX1 |   486 |       |     6   (0)| 00:00:01 |
|  11 |     TABLE ACCESS BY INDEX ROWID   | GJMFMPBE_DETAIL            |     1 |   193 |     2   (0)| 00:00:01 |
|  12 |      INDEX UNIQUE SCAN            | PK_GJMFMPBE_DETAIL         |     1 |       |     1   (0)| 00:00:01 |
|  13 |    TABLE ACCESS BY INDEX ROWID    | GJMFMPBE_INFO              |     1 |   111 |     1   (0)| 00:00:01 |
|  14 |     INDEX UNIQUE SCAN             | PK_GJMFMPBE_INFO           |     1 |       |     0   (0)|          |
----------------------------------------------------------------------------------------------------------------


11 g execution plan 

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                            |       |       | 11130 (100)|       |
|   1 |  SORT ORDER BY                   |                            |   108 | 37692 | 11130   (1)| 00:02:14 |
|*  2 |   HASH JOIN                      |                            |   108 | 37692 | 11129   (1)| 00:02:14 |
|   3 |    TABLE ACCESS BY INDEX ROWID   | GJMFMPBE_CONFIG            |     1 |    25 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN             | GJMFMPBE_CONFIG_FILEDIR    |     1 |       |     1   (0)| 00:00:01 |
|*  5 |    HASH JOIN                     |                            |  4333 |  1370K| 11127   (1)| 00:02:14 |
|   6 |     NESTED LOOPS                 |                            |       |       |            |       |
|   7 |      NESTED LOOPS                |                            |  4333 |   901K|  8733   (1)| 00:01:45 |
|   8 |       TABLE ACCESS BY INDEX ROWID| GJMFMPBE_DETAIL_STATUS     |  4333 | 77994 |    59   (0)| 00:00:01 |
|*  9 |        INDEX RANGE SCAN          | GJMFMPBE_DETAIL_STATUS_IX1 |  2183 |       |    18   (0)| 00:00:01 |
|* 10 |       INDEX UNIQUE SCAN          | PK_GJMFMPBE_DETAIL         |     1 |       |     1   (0)| 00:00:01 |
|  11 |      TABLE ACCESS BY INDEX ROWID | GJMFMPBE_DETAIL            |     1 |   195 |     2   (0)| 00:00:01 |
|  12 |     TABLE ACCESS FULL            | GJMFMPBE_INFO              |   466K|    49M|  2388   (1)| 00:00:29 |
---------------------------------------------------------------------------------------------------------------

The difference lies in the join order. In 10g join order: fc -> fds -> fd -> fi, and in 11g join order: fc -> (fds -> fd -> fi). In 11g the buffer gets per execution is around 427K as shown with below query:

select sql_id, child_number, buffer_gets/executions, executions, sql_plan_baseline from v$sql where sql_id='cz1myj2gx5xwv';

SQL_ID        CHILD_NUMBER BUFFER_GETS/EXECUTIONS EXECUTIONS SQL_PLAN_BASELINE
------------- ------------ ---------------------- ---------- ------------------------------
cz1myj2gx5xwv            0             427447.751        233

Before upgrade, we backed up 10g execution plan in a sql tuning set. so I was able to obtain the buffer gets per execution in 10g by:

SELECT sql_id, plan_hash_value, executions, buffer_gets/executions from
   table(dbms_sqltune.select_sqlset(
       'SPM_STS'
      ,'sql_id=''cz1myj2gx5xwv'''
      , SQLSET_OWNER=>'OPS$ORACLE'
 )
   );  
  


SQL_ID        PLAN_HASH_VALUE EXECUTIONS BUFFER_GETS/EXECUTIONS
------------- --------------- ---------- ----------------------
cz1myj2gx5xwv      1052698328       2742             31115.0627

Now come to the fix. I executed the following procedure to load the 10 plan stored in a SQL tuning set (STS) into SQL plan baselines for this problem sql

DECLARE
  l_plans_loaded  PLS_INTEGER;
BEGIN
  l_plans_loaded := DBMS_SPM.load_plans_from_sqlset(
    SQLSET_NAME => 'SPM_STS'
   ,SQLSET_OWNER => 'OPS$ORACLE'
   ,BASIC_FILTER =>  'sql_id=''cz1myj2gx5xwv'''
 );
END;
/

To verify the problem is fixed, I checked the execution stats shortly after:

select sql_id, child_number, buffer_gets/executions,round(elapsed_time/1000000/executions) ela_secs_exe, executions, sql_plan_baseline from v$sql where sql_id='cz1myj2gx5xwv';

SQL_ID        CHILD_NUMBER BUFFER_GETS/EXECUTIONS ELA_SECS_EXE EXECUTIONS SQL_PLAN_BASELINE
------------- ------------ ---------------------- ------------ ---------- ------------------------------
cz1myj2gx5xwv            0              433577.21         2414        233
cz1myj2gx5xwv            2                  11936            4         14 SQL_PLAN_4090fd80m1dzb80df0a95

WOW! 2414 seconds vs 4 seconds ??? So why the 11g chose the bad execution plan in the first place? I will investigate further if possible.


 ----------------- update Jul. 10, 2012 -----------------

The difference between 11g and 10g plan is not about join order. It is that in 11g Oracle CBO accepted "inlist as filter" access path. I did 10053 trace for the following sql:

select * from  ttq_bbbb.gjmfmpbc_detail_status fds where fds.thread_id= '1' and fds.status in ('LD', 'RT');

The execution plan looks like:

----------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                       | Rows  | Bytes | Cost (%CPU)| Time  |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                            |  4332 |   135K|    59   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| GJMFMPBC_DETAIL_STATUS     |  4332 |   135K|    59   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | GJMFMPBC_DETAIL_STATUS_IX1 |  2167 |       |    18   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

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

   2 - access("FDS"."THREAD_ID"='1')
       filter("FDS"."STATUS"='LD' OR "FDS"."STATUS"='RT')

Note: the index GJMFMPBC_DETAIL_STATUS_IX1 is on GJMFMPBC_DETAIL_STATUS ( thread_id, status). In the trace, it can be seen:

Access Path: index (RangeScan)
    Index: GJMFMPBC_DETAIL_STATUS_IX1
    resc_io: 115.00  resc_cpu: 2790630
    ix_sel: 0.000043  ix_sel_with_filters: 0.000043
    Cost: 115.30  Resp: 115.30  Degree: 1
  Considering index with inlist as filter
 
  ColGroup Usage:: PredCnt: 2  Matches Full: #1  Partial:  Sel: 0.0000
  Access Path: index (RangeScan)
    Index: GJMFMPBC_DETAIL_STATUS_IX1
    resc_io: 59.00  resc_cpu: 2439947
    ix_sel: 0.000021  ix_sel_with_filters: 0.000021
 ***** Logdef predicate Adjustment ******
 Final IO cst 0.00 , CPU cst 0.00
 ***** End Logdef Adjustment ******
    Cost: 59.26  Resp: 59.26  Degree: 1
  Accepted inlist as filter
  ****** trying bitmap/domain indexes ******
  ****** finished trying bitmap/domain indexes ******
  Best:: AccessPath: IndexRange
  Index: GJMFMPBC_DETAIL_STATUS_IX1
         Cost: 59.26  Degree: 1  Resp: 59.26  Card: 4331.78  Bytes: 0

Capture current 10g execution plans before upgrading to 11g

We are planning upgrading our major databases from 10g to 11g. It is desirable to save the current execution plans before upgrading.

Oracle offers a SQL Tuning Set (STS) feature which can be used to achieve our goal. First of all we can create a SQL set:

-- create sql set --
BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET (
 sqlset_name => 'SPM_STS',
 description => '10g plans');
END;
/

Secondly, we can populate an STS from the workload repository, another STS or from the cursor cache. Below statement is to capture the SQL statements and their execution plans from the cursor cache:

DECLARE
  stscur dbms_sqltune.sqlset_cursor;
BEGIN
   OPEN stscur FOR
   SELECT VALUE(P)
     FROM TABLE(dbms_sqltune.select_cursor_cache( 'parsing_schema_name <> ''SYS''',
      null, null, null, null, 1, null, 'ALL')) P;
-- populate the sqlset
   dbms_sqltune.load_sqlset(sqlset_name => 'SPM_STS', populate_cursor => stscur);
END;
/

We can query the DBA_SQLSET to see how many statements captured in the STS:

-- query dba_sqlset 
   SQL> select * from dba_sqlset;
  
 ID NAME            OWNER                DESCRIPTION          CREATED   LAST_MODI STATEMENT_COUNT
---------- --------------- -------------------- -------------------- --------- --------- ---------------
  1 SPM_STS         OPS$VZZZZZZ          10g plans            23-MAR-12 23-MAR-12           10688

Given a SQL_ID, we can otaine the execution plan captured in the STS, for an example:

select * from table (DBMS_XPLAN.DISPLAY_SQLSET(
      'SPM_STS'       -- sqlset_name       
    , '3ubgcaay2xjd8' -- sql_id           
    ,  null           -- plan_hash_value  
    , 'ALLSTATS'       -- format  'TYPICAL', 'ALLSTATS' etc 
    , null            -- sqlset_owner  
 )) ;  

  
 PLAN_TABLE_OUTPUT
 ---------------------------------------------------------------------------------------------------
 SQL Tuning Set Name: SPM_STS
 SQL Tuning Set Owner: OPS$VZZZZZZ
 SQL_ID: 3ubgcaay2xjd8
 SQL Text: SELECT DISTINCT a.BO_STATUS_CODE   FROM            BO_MAPPING a,   CPF_PRODUCT b
    WHERE           a.BO_ID=b.BO_ID AND   a.VOL_PARENT_BO_ID=:1 AND   b.PACKAGE_ID=:2 AND
    a.NODE_TYPE_ID=800
 --------------------------------------------------------------------------------
  
 Plan hash value: 2194803941
  
 -----------------------------------------------------------------
 | Id  | Operation                     | Name           | E-Rows |
 -----------------------------------------------------------------
 |   1 |  HASH UNIQUE                  |                |      1 |
 |   2 |   NESTED LOOPS                |                |      1 |
 |   3 |    TABLE ACCESS BY INDEX ROWID| BO_MAPPING     |      1 |
 |   4 |     INDEX RANGE SCAN          | BO_MAPPING_FK5 |     13 |
 |   5 |    TABLE ACCESS BY INDEX ROWID| CPF_PRODUCT    |      1 |
 |   6 |     INDEX UNIQUE SCAN         | CPF_PRODUCT_PK |      1 |
 -----------------------------------------------------------------
  
 Note
 -----
    - Warning: basic plan statistics not available. These are only collected when:
        * hint 'gather_plan_statistics' is used for the statement or
        * parameter 'statistics_level' is set to 'ALL', at session or system level
  
  

We can also obtain the execution statistics of a SQL, such as buffer gets per execution

SELECT sql_id, plan_hash_value, executions, buffer_gets/executions from
    table(dbms_sqltune.select_sqlset(
    'SPM_STS'
 , 'sql_id = ''3ubgcaay2xjd8'''
 ));  
  


 SQL_ID        PLAN_HASH_VALUE EXECUTIONS BUFFER_GETS/EXECUTIONS
 ------------- --------------- ---------- ----------------------
 3ubgcaay2xjd8      2194803941   16075233              79.076609
  

Finally, here is an example to obtain SQL_IDs given the partail sql text:

SELECT sql_id, sql_text, plan_hash_value, executions, buffer_gets/executions from
    table(dbms_sqltune.select_sqlset(
    'SPM_STS'
 , 'sql_text like ''select%MASTER_SERVICE%'''
 ));  

References


http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_sqltun.htm
http://www.oracle-base.com/articles/10g/AutomaticSQLTuning10g.php


DBMS_SQLTUNE.SELECT_SQLSET (
sqlset_name IN VARCHAR2,
basic_filter IN VARCHAR2 := NULL,
object_filter IN VARCHAR2 := NULL,
ranking_measure1 IN VARCHAR2 := NULL,
ranking_measure2 IN VARCHAR2 := NULL,
ranking_measure3 IN VARCHAR2 := NULL,
result_percentage IN NUMBER := 1,
result_limit IN NUMBER := NULL)
attribute_list IN VARCHAR2 := NULL,
plan_filter IN VARCHAR2 := NULL,
sqlset_owner IN VARCHAR2 := NULL)
RETURN sys.sqlset PIPELINED;


DBMS_XPLAN.DISPLAY_SQLSET(
sqlset_name IN VARCHAR2,
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER := NULL,
format IN VARCHAR2 := 'TYPICAL',
sqlset_owner IN VARCHAR2 := NULL)
RETURN DBMS_XPLAN_TYPE_TABLE PIPELINED;

Using CASE WHEN statement

Suggested dev team to change the following SQL:

update t1 
set b=(SELECT    
               decode(count(*),0,'N','Y')
          FROM t2 
         WHERE t1.id = t2.id);

to the following structruce:

update t1  set b= case when  
                     exists ( select null from t2 where t1.id =t2.id  )
                       then 'Y' 
                       else 'N' end;