Skip navigation.

Hemant K Chitale

Syndicate content
I am an Oracle Database Specialist in Singapore.
get an rss feed of this blog at http://hemantoracledba.blogspot.com/feeds/posts/default?alt=rss
follow me on twitter : @HemantKChitale
Updated: 8 hours 29 min ago

Compression -- 3 : Index (Key) Compression

Sun, 2016-03-13 03:34
Unlike Table Compression that uses deduplication of column values, Index Compression is based on the keys.  Key Compression is also called Prefix Compression.

This relies on repeated leading key values being eliminated.  Thus, for example, if the leading column of the composite index has frequently repeated values and because an Index is always an organised (sorted) structure, we find the repeated values appearing as if "sequentially".  Key Compression can eliminate the repeated values.

Thus, it becomes obvious that Index Key Compression is usable for
a.  A Composite Index of 2 or more columns
b.  Repeated appearances of values in the *leading* key columns
c.  Compression defined for a maximum of n-1 columns  (where n is the number of columns in the index).  That is, the last column cannot be compressed.
Note that a Non-Unique Index automatically has the ROWID appended to it, so Key Compression can be applied to all the columns defined.

Let's look at a few examples.

Starting with creating a fairly large table (that is a multiplied copy of DBA_OBJECTS)

PDB1@ORCL> create table target_data as select * from source_data where 1=2;

Table created.

PDB1@ORCL> insert /*+ APPEND */ into target_data select * from source_data;

364496 rows created.

PDB1@ORCL> commit;

Commit complete.

PDB1@ORCL> insert /*+ APPEND */ into target_data select * from source_data;

364496 rows created.

PDB1@ORCL> commit;

Commit complete.

PDB1@ORCL> insert /*+ APPEND */ into target_data select * from source_data;

364496 rows created.

PDB1@ORCL> commit;

Commit complete.

PDB1@ORCL>
PDB1@ORCL> desc target_data
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(128)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(128)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(23)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NUMBER
EDITION_NAME VARCHAR2(128)
SHARING VARCHAR2(13)
EDITIONABLE VARCHAR2(1)
ORACLE_MAINTAINED VARCHAR2(1)

PDB1@ORCL>


What composite index is a good candidate for Key Compression ?
*Not* an Index that begins with OBJECT_ID as that is a Unique value.

Let's compare two indexes (compressed and non-compressed) on (OWNER, OBJECT_TYPE, OBJECT_NAME).

PDB1@ORCL> create index target_data_ndx_1_comp on
2 target_data (owner, object_type, object_name) compress 2;

Index created.

PDB1@ORCL> exec dbms_stats.gather_index_stats('','TARGET_DATA_NDX_1_COMP');

PL/SQL procedure successfully completed.

PDB1@ORCL> select leaf_blocks
2 from user_indexes
3 where index_name = 'TARGET_DATA_NDX_1_COMP'
4 /

LEAF_BLOCKS
-----------
5629

PDB1@ORCL>


PDB1@ORCL> drop index target_data_ndx_1_comp
2 /

Index dropped.

PDB1@ORCL> create index target_data_ndx_2_nocomp on
2 target_data (owner, object_type, object_name) ;

Index created.

PDB1@ORCL> exec dbms_stats.gather_index_stats('','TARGET_DATA_NDX_2_NOCOMP');

PL/SQL procedure successfully completed.

PDB1@ORCL> select leaf_blocks
2 from user_indexes
3 where index_name = 'TARGET_DATA_NDX_2_NOCOMP'
4 /

LEAF_BLOCKS
-----------
7608

PDB1@ORCL>


Note the "compress 2" specification for the first index.  That is an instruction to compress based on the leading 2 columns.
Thus, the compressed index is 5,629 blocks but the normal, non-compressed index is 7,608 blocks.  We make a gain of 26% in the index size.

Why did I choose OWNER, OBJECT_TYPE as the leading columns ?  Because I expected a high level of repetition on these column names.


Note : I have not explored Advanced Index Compression available in 12.1.0.2
Advanced Index Compression tested in 12.1.0.2
.
.

Categories: DBA Blogs

COMPRESSION -- 2 : Compressed Table Partitions

Sun, 2016-03-06 09:38
A Partitioned Table can choose to have a mix of COMPRESS and NOCOMPRESS Partitions.

As in this case where the first 3 partitions are defined as COMPRESS and the last as NOCOMPRESS :  :

[oracle@localhost Hemant]$ sqlplus hemant/hemant@orcl

SQL*Plus: Release 12.1.0.2.0 Production on Sun Mar 6 23:20:19 2016

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

Last Successful login time: Sun Mar 06 2016 23:19:11 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> @create_SALESHIST
SQL> spool create_SALESHIST
SQL>
SQL> drop table SALESHIST;

Table dropped.

SQL>
SQL> alter session set "_partition_large_extents"=FALSE;

Session altered.

SQL>
SQL> create table SALESHIST
2 (
3 PROD_ID NUMBER NOT NULL ,
4 CUST_ID NUMBER NOT NULL ,
5 TIME_ID DATE NOT NULL ,
6 CHANNEL_ID NUMBER NOT NULL ,
7 PROMO_ID NUMBER NOT NULL ,
8 QUANTITY_SOLD NUMBER(10,2) NOT NULL ,
9 AMOUNT_SOLD NUMBER(10,2) NOT NULL
10 )
11 NOCOMPRESS LOGGING
12 TABLESPACE USERS
13 PARTITION BY RANGE (TIME_ID)
14 (
15 PARTITION SALES_1998
16 VALUES LESS THAN (TO_DATE('1999-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
17 'NLS_CALENDAR=GREGORIAN'))
18 SEGMENT CREATION IMMEDIATE
19 COMPRESS BASIC NOLOGGING
20 TABLESPACE SALES_1998 ,
21 PARTITION SALES_1999
22 VALUES LESS THAN (TO_DATE('2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
23 'NLS_CALENDAR=GREGORIAN'))
24 SEGMENT CREATION IMMEDIATE
25 COMPRESS BASIC NOLOGGING
26 TABLESPACE SALES_1999 ,
27 PARTITION SALES_2000
28 VALUES LESS THAN (TO_DATE('2001-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
29 'NLS_CALENDAR=GREGORIAN'))
30 SEGMENT CREATION IMMEDIATE
31 COMPRESS BASIC NOLOGGING
32 TABLESPACE SALES_2000 ,
33 PARTITION SALES_2001
34 VALUES LESS THAN (TO_DATE('2002-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
35 'NLS_CALENDAR=GREGORIAN'))
36 SEGMENT CREATION IMMEDIATE
37 NOCOMPRESS NOLOGGING
38 TABLESPACE SALES_2001 )
39 /

Table created.

SQL>
SQL> spool off
SQL>
SQL> col partition_name format a30
SQL> select partition_name, compression, compress_for
2 from user_tab_partitions
3 where table_name = 'SALESHIST'
4 order by partition_position;

PARTITION_NAME COMPRESS COMPRESS_FOR
------------------------------ -------- ------------------------------
SALES_1998 ENABLED BASIC
SALES_1999 ENABLED BASIC
SALES_2000 ENABLED BASIC
SALES_2001 DISABLED

SQL>
SQL> select compression, compress_for
2 from user_tables
3 where table_name = 'SALESHIST';

COMPRESS COMPRESS_FOR
-------- ------------------------------


SQL>


The Compression attributes actually apply to the Segments so the Partition Segments have the definition but the Table, being segmentless, does not show the definition.

Note that I am still demonstrating BASIC Compression. So the compression is applied only on Direct Path INSERT.  As in :

SQL> insert /*+ APPEND */ into saleshist select * from sh.sales;

918843 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> select partition_name, tablespace_name, bytes/1024
2 from user_segments
3 where segment_name = 'SALESHIST'
4 and segment_type = 'TABLE PARTITION'
5 order by 1;

PARTITION_NAME TABLESPACE_NAME BYTES/1024
------------------------------ ------------------------------ ----------
SALES_1998 SALES_1998 3072
SALES_1999 SALES_1999 4096
SALES_2000 SALES_2000 4096
SALES_2001 SALES_2001 11264

SQL>


This demonstrates that it is possible to
a) Have some Partitions defined as COMPRESSed and others as NOCOMPRESSed
b) Create different Partitions in different Tablespaces

Before the advent of (and still without using) the 12c ILM features, this was and is a method to manage historical data with compression and moving or placing data (i.e. the respective tablespace datafile(s)) in low-cost storage as desired.

Note : For the purposes of this demo, I used the parameter "_partition_large_extents"=FALSE.  Oracle's default Partition size since 11.2.0.2 has been 8MB for Table Partitions and that would have been excessively large for this demo.  If you are using Hybrid Columnar Compression and/or Exadata, Oracle advises against this.
.
.
.

Categories: DBA Blogs

Compression -- 1b : (more on) BASIC Table Compression

Sun, 2016-02-28 04:38
In the previous blog, I demonstrated creating an empty table with BASIC Compression and then populating it.

What if you have a pre-existing table with data that you want to compress ?

Here I start with a table that already has rows but doesn't have compression configured.

PDB1@ORCL> select tablespace_name, segment_type, bytes/1048576
2 from user_segments
3 where segment_name = 'REGULAR_1';

TABLESPACE_NAME SEGMENT_TYPE BYTES/1048576
------------------------------ ------------------ -------------
HEMANT TABLE 49

PDB1@ORCL> select pct_free, blocks, num_rows, blocks*8192/1048576
2 from user_tables
3 where table_name = 'REGULAR_1';

PCT_FREE BLOCKS NUM_ROWS BLOCKS*8192/1048576
---------- ---------- ---------- -------------------
10 6224 364496 48.625

PDB1@ORCL>


I then proceed to rebuild it as a COMPRESSed table.

PDB1@ORCL> alter table regular_1 move compress;

Table altered.

PDB1@ORCL> exec dbms_stats.gather_table_stats('','REGULAR_1');

PL/SQL procedure successfully completed.

PDB1@ORCL> select tablespace_name, segment_type, bytes/1048576
2 from user_segments
3 where segment_name = 'REGULAR_1';

TABLESPACE_NAME SEGMENT_TYPE BYTES/1048576
------------------------------ ------------------ -------------
HEMANT TABLE 12

PDB1@ORCL> select pct_free, blocks, num_rows, blocks*8192/1048576
2 from user_tables
3 where table_name = 'REGULAR_1';

PCT_FREE BLOCKS NUM_ROWS BLOCKS*8192/1048576
---------- ---------- ---------- -------------------
0 1448 364496 11.3125

PDB1@ORCL>


Note how not only did the table size shrink to less than 12MB, the PCT_FREE also got  reset to 0 !
.
.
.

Categories: DBA Blogs

Compression -- 1 : BASIC Table Compression

Sun, 2016-02-21 08:30
Now, starting a new series of blog posts on Compression.

Beginning with  BASIC Table Compression.

Basic Table Compression was introduced in 9i Release 2 (9.2.0).  It is free with the Enterprise Edition.  Basic Compression works only with Direct Path (Bulk Load) INSERTs.  It does not apply to "normal" INSERT and UPDATE operations.
Basic Compression is actually "de-duplication".  It identifies values that are repeated within the same database block and replaces them with tokens mapped to the list of values.   Note the section that I have underlined.  De-duplication does not span database blocks.

Here is a first demo of Basic Compression using INSERT /*+ APPEND */  (for Direct Path Insert).

I start with a table that is a multiplied copy of DBA_OBJECTS, named as SOURCE_DATA.

PDB1@ORCL> show user
USER is "HEMANT"
PDB1@ORCL> select count(*) from source_data;

COUNT(*)
----------
364496

PDB1@ORCL>
PDB1@ORCL> select tablespace_name, segment_type, bytes/1048576
2 from user_segments
3 where segment_name = 'SOURCE_DATA';

TABLESPACE_NAME SEGMENT_TYPE BYTES/1048576
------------------------------ ------------------ -------------
HEMANT TABLE 49

PDB1@ORCL>


I then create table to hold compressed data.  Note (let me repeat again : Basic Compression works only with DIRECT PATH INSERT).

PDB1@ORCL> create table compressed_1 tablespace hemant compress as select * from source_data where 1=2;

Table created.

PDB1@ORCL> insert /*+ APPEND */ into compressed_1
2 select * from source_data;

364496 rows created.

PDB1@ORCL> commit;

Commit complete.

PDB1@ORCL>
PDB1@ORCL> col segment_name format a30
PDB1@ORCL> select segment_name, segment_type, bytes/1048576
2 from user_Segments
3 where tablespace_name = 'HEMANT';

SEGMENT_NAME SEGMENT_TYPE BYTES/1048576
------------------------------ ------------------ -------------
COMPRESSED_1 TABLE 12
SOURCE_DATA TABLE 49

PDB1@ORCL>


So, a 49MB table is compressed down to 12MB.  What if I UPDATE these rows ?

PDB1@ORCL> update compressed_1
2 set owner = owner;

364496 rows updated.

PDB1@ORCL> commit;

Commit complete.

PDB1@ORCL> select segment_name, segment_type, bytes/1048576
2 from user_segments
3 where tablespace_name = 'HEMANT';

SEGMENT_NAME SEGMENT_TYPE BYTES/1048576
------------------------------ ------------------ -------------
COMPRESSED_1 TABLE 22
SOURCE_DATA TABLE 49

PDB1@ORCL>


Updating only a single column without changing the length of the data in that column has increased the size of the table.

Let me continue the UPDATE experiment further, without increasing the length of data in any columns.

PDB1@ORCL> update compressed_1
2 set owner = owner, object_name = object_name, subobject_name = subobject_name, object_id = object_id,
3 data_object_id = data_object_id, object_type = object_type, created = created
4 /

364496 rows updated.

PDB1@ORCL> commit;

Commit complete.

PDB1@ORCL> select segment_name, segment_type, bytes/1048576
2 from user_segments
3 where tablespace_name = 'HEMANT';

SEGMENT_NAME SEGMENT_TYPE BYTES/1048576
------------------------------ ------------------ -------------
COMPRESSED_1 TABLE 22
SOURCE_DATA TABLE 49

PDB1@ORCL>


No further degradation ? (unless my UPDATE expands the size of data in one or more column(s)).

Note a difference between the two tables :

PDB1@ORCL> select table_name, compression, compress_for, pct_free
2 from user_tables;

TABLE_NAME COMPRESS COMPRESS_FOR PCT_FREE
------------------------------ -------- ------------------------------ ----------
COMPRESSED_1 ENABLED BASIC 0
SOURCE_DATA DISABLED 10

PDB1@ORCL>


The Compressed table is created with PCT_FREE=10.  PCT_FREE=0  (Note that this can also have an impact when you issue UPDATEs subsequently, UPDATEs that increase the size of data in one or more columns).

UPDATE : In 12c the syntax for BASIC Compression is "ROW STORE COMPRESS BASIC". Simply specifying "COMPRESS" or "COMPRESS BASIC" is supported for backward compatibility.

.
.
.

Categories: DBA Blogs

RMAN : Unused Block Compression and Null Block Compression

Fri, 2016-02-19 08:56
To quote the 11.2 documentation,  "Although it is referred to as block compression, it might be helpful to think of block compression as block skipping".  11.2 differentiates between Unused Block Compression and Null Block Compression.
UPDATE 28-Feb-16 : Please note the list of 5 pre-requisites for Unused Block Compression.

In response to a community thread, here is a quick demo to see both Null Block Compression and Unused Block Compression.

I start with a 100MB tablespace.

[oracle@ora11204 Desktop]$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 19 22:37:47 2016

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


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

SQL> create tablespace hemant;

Tablespace created.

SQL> alter user hemant quota unlimited on hemant;

User altered.

SQL> select file_name, bytes/1048576 from dba_data_files
2 where tablespace_name = 'HEMANT';

FILE_NAME
--------------------------------------------------------------------------------
BYTES/1048576
-------------
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf
100


SQL>


First I take a backup of this empty tablespace.

RMAN> backup tablespace hemant;

Starting backup at 19-FEB-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=152 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00011 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf
channel ORA_DISK_1: starting piece 1 at 19-FEB-16
channel ORA_DISK_1: finished piece 1 at 19-FEB-16
piece handle=/u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T224255_cdgbhhyh_.bkp tag=TAG20160219T224255 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 19-FEB-16

Starting Control File and SPFILE Autobackup at 19-FEB-16
piece handle=/u02/FRA/ORCL/autobackup/2016_02_19/o1_mf_s_904257776_cdgbhkcx_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 19-FEB-16

RMAN> list backup of tablespace hemant;


List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
25 Full 1.03M DISK 00:00:01 19-FEB-16
BP Key: 25 Status: AVAILABLE Compressed: NO Tag: TAG20160219T224255
Piece Name: /u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T224255_cdgbhhyh_.bkp
List of Datafiles in backup set 25
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
11 Full 4806951 19-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf

RMAN>

This 100MB datafile backed up to 1.03MB is Null Blocm compression.

I next load data into it and  then take a backup.

[oracle@ora11204 Desktop]$ sqlplus hemant/hemant

SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 19 22:45:12 2016

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


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

SQL> create table hkc_all_obj tablespace hemant
2 as select * from dba_objects;

Table created.

SQL> insert into hkc_all_obj select * from hkc_all_obj;

28105 rows created.

SQL> /

56210 rows created.

SQL> /

112420 rows created.

SQL> /

224840 rows created.

SQL> commit;

Commit complete.

SQL> select segment_name, bytes/1048576
2 from user_segments
3 where tablespace_name = 'HEMANT';

SEGMENT_NAME
--------------------------------------------------------------------------------
BYTES/1048576
-------------
HKC_ALL_OBJ
49


SQL>
RMAN> backup tablespace hemant;

Starting backup at 19-FEB-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00011 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf
channel ORA_DISK_1: starting piece 1 at 19-FEB-16
channel ORA_DISK_1: finished piece 1 at 19-FEB-16
piece handle=/u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T224721_cdgbqw0k_.bkp tag=TAG20160219T224721 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 19-FEB-16

Starting Control File and SPFILE Autobackup at 19-FEB-16
piece handle=/u02/FRA/ORCL/autobackup/2016_02_19/o1_mf_s_904258049_cdgbr179_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 19-FEB-16

RMAN> list backup of tablespace hemant;


List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
25 Full 1.03M DISK 00:00:01 19-FEB-16
BP Key: 25 Status: AVAILABLE Compressed: NO Tag: TAG20160219T224255
Piece Name: /u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T224255_cdgbhhyh_.bkp
List of Datafiles in backup set 25
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
11 Full 4806951 19-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
27 Full 49.67M DISK 00:00:04 19-FEB-16
BP Key: 27 Status: AVAILABLE Compressed: NO Tag: TAG20160219T224721
Piece Name: /u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T224721_cdgbqw0k_.bkp
List of Datafiles in backup set 27
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
11 Full 4807559 19-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf

RMAN>


So, I now have 49.67MB backup of the datafile in the tablespace. What happens if I drop all objects (in this case only the 1 table) in that tablespace, with RECYLEBIN ?

SQL> show parameter recycle

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_recycle string
db_recycle_cache_size big integer 0
recyclebin string on
SQL> drop table hkc_all_obj;

Table dropped.

SQL> select object_name, original_name, ts_name, space, space*8192/1048576
2 from user_recyclebin;

OBJECT_NAME ORIGINAL_NAME
------------------------------ --------------------------------
TS_NAME SPACE SPACE*8192/1048576
------------------------------ ---------- ------------------
BIN$LCGWc71HDSzgUwEAAH/27g==$0 HKC_ALL_OBJ
HEMANT 6272 49


SQL>
RMAN> backup tablespace hemant;

Starting backup at 19-FEB-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=26 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00011 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf
channel ORA_DISK_1: starting piece 1 at 19-FEB-16
channel ORA_DISK_1: finished piece 1 at 19-FEB-16
piece handle=/u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T225023_cdgbxh72_.bkp tag=TAG20160219T225023 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 19-FEB-16

Starting Control File and SPFILE Autobackup at 19-FEB-16
piece handle=/u02/FRA/ORCL/autobackup/2016_02_19/o1_mf_s_904258224_cdgbxjg4_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 19-FEB-16

RMAN> list backup of tablespace hemant;


List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
25 Full 1.03M DISK 00:00:01 19-FEB-16
BP Key: 25 Status: AVAILABLE Compressed: NO Tag: TAG20160219T224255
Piece Name: /u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T224255_cdgbhhyh_.bkp
List of Datafiles in backup set 25
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
11 Full 4806951 19-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
27 Full 49.67M DISK 00:00:04 19-FEB-16
BP Key: 27 Status: AVAILABLE Compressed: NO Tag: TAG20160219T224721
Piece Name: /u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T224721_cdgbqw0k_.bkp
List of Datafiles in backup set 27
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
11 Full 4807559 19-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
29 Full 49.67M DISK 00:00:00 19-FEB-16
BP Key: 29 Status: AVAILABLE Compressed: NO Tag: TAG20160219T225023
Piece Name: /u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T225023_cdgbxh72_.bkp
List of Datafiles in backup set 29
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
11 Full 4807909 19-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf

RMAN>


The latest backup (BackupSet 29) is still 49.67MB although, technically, the tablespace has no Tables/Indexes.  It does have something in the RECYCLEBIN.

Let me purge the RECYCLEBIN and then run a fresh backup.

SQL> select object_name, original_name, ts_name, space, space*8192/1048576
2 from user_recyclebin;

OBJECT_NAME ORIGINAL_NAME
------------------------------ --------------------------------
TS_NAME SPACE SPACE*8192/1048576
------------------------------ ---------- ------------------
BIN$LCGWc71HDSzgUwEAAH/27g==$0 HKC_ALL_OBJ
HEMANT 6272 49


SQL> purge hkc_all_obj;
purge hkc_all_obj
*
ERROR at line 1:
ORA-38302: invalid PURGE option


SQL> purge table hkc_all_obj;

Table purged.

SQL> select object_name, original_name, ts_name, space, space*8192/1048576
2 from user_recyclebin;

no rows selected

SQL>
RMAN> backup tablespace hemant;

Starting backup at 19-FEB-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=144 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00011 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf
channel ORA_DISK_1: starting piece 1 at 19-FEB-16
channel ORA_DISK_1: finished piece 1 at 19-FEB-16
piece handle=/u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T225323_cdgc33tc_.bkp tag=TAG20160219T225323 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 19-FEB-16

Starting Control File and SPFILE Autobackup at 19-FEB-16
piece handle=/u02/FRA/ORCL/autobackup/2016_02_19/o1_mf_s_904258404_cdgc351n_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 19-FEB-16

RMAN> list backup of tablespace hemant;


List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
25 Full 1.03M DISK 00:00:01 19-FEB-16
BP Key: 25 Status: AVAILABLE Compressed: NO Tag: TAG20160219T224255
Piece Name: /u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T224255_cdgbhhyh_.bkp
List of Datafiles in backup set 25
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
11 Full 4806951 19-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
27 Full 49.67M DISK 00:00:04 19-FEB-16
BP Key: 27 Status: AVAILABLE Compressed: NO Tag: TAG20160219T224721
Piece Name: /u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T224721_cdgbqw0k_.bkp
List of Datafiles in backup set 27
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
11 Full 4807559 19-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
29 Full 49.67M DISK 00:00:00 19-FEB-16
BP Key: 29 Status: AVAILABLE Compressed: NO Tag: TAG20160219T225023
Piece Name: /u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T225023_cdgbxh72_.bkp
List of Datafiles in backup set 29
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
11 Full 4807909 19-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
31 Full 1.03M DISK 00:00:00 19-FEB-16
BP Key: 31 Status: AVAILABLE Compressed: NO Tag: TAG20160219T225323
Piece Name: /u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T225323_cdgc33tc_.bkp
List of Datafiles in backup set 31
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
11 Full 4808966 19-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf

RMAN>
[oracle@ora11204 Desktop]$ ls -l /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf
-rw-rw----. 1 oracle oracle 104865792 Feb 19 22:53 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf
[oracle@ora11204 Desktop]$


Aha ! BackupSet 31 is now 1.03MB only.  So, Unused Block Compression has kicked in !  The blocks that belonged to that table are yet formatted and in prior versions that did not have Unused Block Compression would still be backed up even though the table has been dropped.
(Note : I did not shrink the datafile, it is still 100MB).


UPDATE 28-Feb-16 :  Testing for TRUNCATE

SQL> select segment_name, bytes/1048576
2 from user_segments
3 where tablespace_name = 'HEMANT';

SEGMENT_NAME BYTES/1048576
--------------------------------------------------------------------------------- -------------
HKC_ALL_OBJ 49

SQL>
RMAN> backup tablespace hemant;

Starting backup at 28-FEB-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00011 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cf5nc41d_.dbf
channel ORA_DISK_1: starting piece 1 at 28-FEB-16
channel ORA_DISK_1: finished piece 1 at 28-FEB-16
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2016_02_28/o1_mf_nnndf_TAG20160228T190655_cf5o6lw3_.bkp tag=TAG20160228T190655 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:26
Finished backup at 28-FEB-16

Starting Control File and SPFILE Autobackup at 28-FEB-16
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/autobackup/2016_02_28/o1_mf_s_905022445_cf5o7lyq_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 28-FEB-16

RMAN> list backup of tablespace hemant completed after "trunc(sysdate)";


List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 49.55M DISK 00:00:22 28-FEB-16
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20160228T190655
Piece Name: /u01/app/oracle/flash_recovery_area/ORCL/backupset/2016_02_28/o1_mf_nnndf_TAG20160228T190655_cf5o6lw3_.bkp
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
11 Full 4776386 28-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cf5nc41d_.dbf

RMAN>
SQL> show parameter recyclebin;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string on
SQL> truncate table hkc_all_obj;

Table truncated.

SQL> select segment_name, bytes/1048576
2 from user_segments
3 where tablespace_name = 'HEMANT';

SEGMENT_NAME BYTES/1048576
--------------------------------------------------------------------------------- -------------
HKC_ALL_OBJ .0625

SQL>
RMAN> backup tablespace hemant;

Starting backup at 28-FEB-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00011 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cf5nc41d_.dbf
channel ORA_DISK_1: starting piece 1 at 28-FEB-16
channel ORA_DISK_1: finished piece 1 at 28-FEB-16
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2016_02_28/o1_mf_nnndf_TAG20160228T190946_cf5octqc_.bkp tag=TAG20160228T190946 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 28-FEB-16

Starting Control File and SPFILE Autobackup at 28-FEB-16
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/autobackup/2016_02_28/o1_mf_s_905022589_cf5oczwq_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 28-FEB-16

RMAN> list backup of tablespace hemant completed after "trunc(sysdate)";


List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 49.55M DISK 00:00:22 28-FEB-16
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20160228T190655
Piece Name: /u01/app/oracle/flash_recovery_area/ORCL/backupset/2016_02_28/o1_mf_nnndf_TAG20160228T190655_cf5o6lw3_.bkp
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
11 Full 4776386 28-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cf5nc41d_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3 Full 1.53M DISK 00:00:01 28-FEB-16
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20160228T190946
Piece Name: /u01/app/oracle/flash_recovery_area/ORCL/backupset/2016_02_28/o1_mf_nnndf_TAG20160228T190946_cf5octqc_.bkp
List of Datafiles in backup set 3
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
11 Full 4776656 28-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cf5nc41d_.dbf

RMAN>


The TRUNCATE did reduce the size of the table and the backup !
.
.
.

Categories: DBA Blogs

Trace Files -- 12 : Tracing a Particular Process

Sun, 2016-02-14 02:59
Unlike tracing for particular SQL statements, you can also trace by PID  (Oracle PID) or Server Process ID (SPID).

SQL> select s.sid, p.pid
2 from v$session s join v$process p
3 on (s.paddr=p.addr)
4 and s.username = 'HEMANT';

SID PID
---------- ----------
19 22

SQL>
SQL> alter system set events 'sql_trace {process: orapid=22}';

System altered.

SQL>
SQL> select s.sid, p.pid, p.spid
2 from v$session s join v$process p
3 on (s.paddr=p.addr)
4 and s.username = 'HR';

SID PID SPID
---------- ---------- ------------------------
14 26 3207

SQL> alter system set events 'sql_trace {process:3207}';

System altered.

SQL>


Tracing for the processes is disabled with :

SQL> alter system set events 'sql_trace {process: orapid=22} off';

System altered.

SQL> alter system set events 'sql_trace {process:3207} off';

System altered.

SQL>


Tracing seems to get disabled after the next SQL, not immediately.

Remember : This is SQL Tracing, not Optimizer Tracing.
.
.
.

Categories: DBA Blogs

Trace Files -- 11b : Using DBMS_SQLDIAG to trace the Optimization of an SQL Statement

Sun, 2016-02-07 07:46
My previous blogpost covered using ALTER SYSTEM/SESSION to set tracing for a specific SQL_ID that has been determined in advance.   The SQL may be executed in the future after the ALTER SYSTEM/SESSION.

Here is a method for an SQL that has already been executed.

SQL> select count(*) from all_objects_many_list
2 where created > sysdate-365;

COUNT(*)
----------
25548

SQL> begin
2 dbms_sqldiag.dump_trace(p_sql_id=>'b086mzzp82x7w',
3 p_component=>'Optimizer',
4 p_file_id=>'OPT_TRACE_b086mzzp82x7w');
5 end;
6 /

PL/SQL procedure successfully completed.

SQL>
SQL> select value from v$diag_info where name = 'Default Trace File';

VALUE
------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2987_OPT_TRACE_b086mzzp82x7w.trc

SQL>


Let's review the trace file.

Registered qb: SEL$1 0x99b9000 (PARSER)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$1 nbfros=1 flg=0
fro(0): flg=4 objn=35014 hint_alias="ALL_OBJECTS_MANY_LIST"@"SEL$1"

SPM: statement not found in SMB
SPM: statement not a candidate for auto-capture

**************************
Automatic degree of parallelism (ADOP)
**************************
Automatic degree of parallelism is disabled: Parameter.

PM: Considering predicate move-around in query block SEL$1 (#0)
**************************
Predicate Move-Around (PM)
**************************
OPTIMIZER INFORMATION

******************************************
----- Current SQL Statement for this session (sql_id=0b1t991khf449) -----
/* SQL Analyze(148,0) */ select count(*) from all_objects_many_list
where created > sysdate-365
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
object line object
handle number name
0x6be0d530 145 package body SYS.DBMS_SQLTUNE_INTERNAL
0x6be0d530 12098 package body SYS.DBMS_SQLTUNE_INTERNAL
0x6be6b738 1229 package body SYS.DBMS_SQLDIAG
0x6becaea8 2 anonymous block
*******************************************
Legend
The following abbreviations are used by optimizer trace.
CBQT - cost-based query transformation
JPPD - join predicate push-down
OJPPD - old-style (non-cost-based) JPPD
FPD - filter push-down
PM - predicate move-around
CVM - complex view merging
SPJ - select-project-join
SJC - set join conversion
SU - subquery unnesting
OBYE - order by elimination
OST - old style star transformation
ST - new (cbqt) star transformation
CNT - count(col) to count(*) transformation
JE - Join Elimination
JF - join factorization
CBY - connect by
SLP - select list pruning
DP - distinct placement
qb - query block
LB - leaf blocks
DK - distinct keys
LB/K - average number of leaf blocks per key
DB/K - average number of data blocks per key
CLUF - clustering factor
NDV - number of distinct values
Resp - response cost
Card - cardinality
Resc - resource cost
NL - nested loops (join)
SM - sort merge (join)
HA - hash (join)
CPUSPEED - CPU Speed
IOTFRSPEED - I/O transfer speed
IOSEEKTIM - I/O seek time
SREADTIM - average single block read time
MREADTIM - average multiblock read time
MBRC - average multiblock read count
MAXTHR - maximum I/O system throughput
SLAVETHR - average slave I/O throughput
dmeth - distribution method
1: no partitioning required
2: value partitioned
4: right is random (round-robin)
128: left is random (round-robin)
8: broadcast right and partition left
16: broadcast left and partition right
32: partition left using partitioning of right
64: partition right using partitioning of left
256: run the join in serial
0: invalid distribution method
sel - selectivity
ptn - partition
***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************
*************************************
PARAMETERS WITH ALTERED VALUES
******************************
Compilation Environment Dump
is_recur_flags = 8
Bug Fix Control Environment


*************************************
PARAMETERS WITH DEFAULT VALUES
******************************
Compilation Environment Dump
optimizer_mode_hinted = false
optimizer_features_hinted = 0.0.0
parallel_execution_enabled = true
parallel_query_forced_dop = 0
parallel_dml_forced_dop = 0
parallel_ddl_forced_degree = 0
parallel_ddl_forced_instances = 0
_query_rewrite_fudge = 90
optimizer_features_enable = 11.2.0.4
_optimizer_search_limit = 5
.... continued to a long list of parameters ........
....................................................
Bug Fix Control Environment
fix 3834770 = 1
fix 3746511 = enabled
fix 4519016 = enabled
.... continued to a long list of bug fixes ........
...................................................
***************************************
PARAMETERS IN OPT_PARAM HINT
****************************
***************************************
Column Usage Monitoring is ON: tracking level = 1
***************************************

Considering Query Transformations on query block SEL$1 (#0)
**************************
Query transformations (QT)
**************************
JF: Checking validity of join factorization for query block SEL$1 (#0)
JF: Bypassed: not a UNION or UNION-ALL query block.
ST: not valid since star transformation parameter is FALSE
TE: Checking validity of table expansion for query block SEL$1 (#0)
TE: Bypassed: No partitioned table in query block.
CBQT bypassed for query block SEL$1 (#0): no complex view, sub-queries or UNION (ALL) queries.
CBQT: Validity checks failed for 0b1t991khf449.
CSE: Considering common sub-expression elimination in query block SEL$1 (#0)
*************************
Common Subexpression elimination (CSE)
*************************
CSE: CSE not performed on query block SEL$1 (#0).
OBYE: Considering Order-by Elimination from view SEL$1 (#0)
***************************
Order-by elimination (OBYE)
***************************
OBYE: OBYE bypassed: no order by to eliminate.
CVM: Considering view merge in query block SEL$1 (#0)
OJE: Begin: find best directive for query block SEL$1 (#0)
OJE: End: finding best directive for query block SEL$1 (#0)
CNT: Considering count(col) to count(*) on query block SEL$1 (#0)
*************************
Count(col) to Count(*) (CNT)
*************************
CNT: COUNT() to COUNT(*) not done.
query block SEL$1 (#0) unchanged
Considering Query Transformations on query block SEL$1 (#0)
**************************
Query transformations (QT)
**************************
JF: Checking validity of join factorization for query block SEL$1 (#0)
JF: Bypassed: not a UNION or UNION-ALL query block.
ST: not valid since star transformation parameter is FALSE
TE: Checking validity of table expansion for query block SEL$1 (#0)
TE: Bypassed: No partitioned table in query block.
CBQT bypassed for query block SEL$1 (#0): no complex view, sub-queries or UNION (ALL) queries.
CBQT: Validity checks failed for 0b1t991khf449.
CSE: Considering common sub-expression elimination in query block SEL$1 (#0)
*************************
Common Subexpression elimination (CSE)
*************************
CSE: CSE not performed on query block SEL$1 (#0).
SU: Considering subquery unnesting in query block SEL$1 (#0)
********************
Subquery Unnest (SU)
********************
SJC: Considering set-join conversion in query block SEL$1 (#0)
*************************
Set-Join Conversion (SJC)
*************************
SJC: not performed
PM: Considering predicate move-around in query block SEL$1 (#0)
**************************
Predicate Move-Around (PM)
**************************
PM: PM bypassed: Outer query contains no views.
PM: PM bypassed: Outer query contains no views.
query block SEL$1 (#0) unchanged
FPD: Considering simple filter push in query block SEL$1 (#0)
"ALL_OBJECTS_MANY_LIST"."CREATED">SYSDATE@!-365
try to generate transitive predicate from check constraints for query block SEL$1 (#0)
finally: "ALL_OBJECTS_MANY_LIST"."CREATED">SYSDATE@!-365

apadrv-start sqlid=398332482954924169
:
call(in-use=2008, alloc=16344), compile(in-use=56240, alloc=59416), execution(in-use=2456, alloc=4032)

*******************************************
Peeked values of the binds in SQL statement
*******************************************

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT COUNT(*) "COUNT(*)" FROM "HEMANT"."ALL_OBJECTS_MANY_LIST" "ALL_OBJECTS_MANY_LIST" WHERE "ALL_OBJECTS_MANY_LIST"."CREATED">SYSDATE@!-365
kkoqbc: optimizing query block SEL$1 (#0)

:
call(in-use=2056, alloc=16344), compile(in-use=57320, alloc=59416), execution(in-use=2456, alloc=4032)

kkoqbc-subheap (create addr=0x7f4409c4fb18)
****************
QUERY BLOCK TEXT
****************
select count(*) from all_objects_many_list
where created > sysdate-365
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0
fro(0): flg=0 objn=35014 hint_alias="ALL_OBJECTS_MANY_LIST"@"SEL$1"

-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
Using NOWORKLOAD Stats
CPUSPEEDNW: 937 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 8)


And here is the actual information about how the Costing is done and Execution Plan determined.

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: ALL_OBJECTS_MANY_LIST Alias: ALL_OBJECTS_MANY_LIST
#Rows: 7197952 #Blks: 98279 AvgRowLen: 93.00 ChainCnt: 0.00
Index Stats::
Index: ALL_OBJ_M_L_CRTD_NDX Col#: 7
LVLS: 2 #LB: 19093 #DK: 1232 LB/K: 15.00 DB/K: 351.00 CLUF: 432893.00
Access path analysis for ALL_OBJECTS_MANY_LIST
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for ALL_OBJECTS_MANY_LIST[ALL_OBJECTS_MANY_LIST]
Column (#7): CREATED(
AvgLen: 8 NDV: 1232 Nulls: 0 Density: 0.000812 Min: 2455803 Max: 2457343
Table: ALL_OBJECTS_MANY_LIST Alias: ALL_OBJECTS_MANY_LIST
Card: Original: 7197952.000000 Rounded: 1313133 Computed: 1313133.42 Non Adjusted: 1313133.42
Access Path: TableScan
Cost: 27174.11 Resp: 27174.11 Degree: 0
Cost_io: 26619.00 Cost_cpu: 6242311042
Resp_io: 26619.00 Resp_cpu: 6242311042
Access Path: index (index (FFS))
Index: ALL_OBJ_M_L_CRTD_NDX
resc_io: 5173.00 resc_cpu: 4598699894
ix_sel: 0.000000 ix_sel_with_filters: 1.000000
Access Path: index (FFS)
Cost: 5581.95 Resp: 5581.95 Degree: 1
Cost_io: 5173.00 Cost_cpu: 4598699894
Resp_io: 5173.00 Resp_cpu: 4598699894
Access Path: index (IndexOnly)
Index: ALL_OBJ_M_L_CRTD_NDX
resc_io: 3486.00 resc_cpu: 287452140
ix_sel: 0.182432 ix_sel_with_filters: 0.182432
Cost: 3511.56 Resp: 3511.56 Degree: 1
Best:: AccessPath: IndexRange
Index: ALL_OBJ_M_L_CRTD_NDX
Cost: 3511.56 Degree: 1 Resp: 3511.56 Card: 1313133.42 Bytes: 0

***************************************


OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
Join order[1]: ALL_OBJECTS_MANY_LIST[ALL_OBJECTS_MANY_LIST]#0
***********************
Best so far: Table#: 0 cost: 3511.5623 card: 1313133.4203 bytes: 10505064
***********************
(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:2000

*********************************
Number of join permutations tried: 1
*********************************
Enumerating distribution method (advanced)

Trying or-Expansion on query block SEL$1 (#0)
Transfer Optimizer annotations for query block SEL$1 (#0)
id=0 frofkks[i] (index start key) predicate="ALL_OBJECTS_MANY_LIST"."CREATED">SYSDATE@!-365
Final cost for query block SEL$1 (#0) - All Rows Plan:
Best join order: 1
Cost: 3511.5623 Degree: 1 Card: 1313133.0000 Bytes: 10505064
Resc: 3511.5623 Resc_io: 3486.0000 Resc_cpu: 287452140
Resp: 3511.5623 Resp_io: 3486.0000 Resc_cpu: 287452140
kkoqbc-subheap (delete addr=0x7f4409c4fb18, in-use=26384, alloc=32840)
kkoqbc-end:
:
call(in-use=8664, alloc=49288), compile(in-use=59856, alloc=63560), execution(in-use=2456, alloc=4032)

kkoqbc: finish optimizing query block SEL$1 (#0)
apadrv-end
:
call(in-use=8664, alloc=49288), compile(in-use=60768, alloc=63560), execution(in-use=2456, alloc=4032)


Starting SQL statement dump

user_id=87 user_name=HEMANT module=SQL*Plus action=
sql_id=0b1t991khf449 plan_hash_value=1689651126 problem_type=3
----- Current SQL Statement for this session (sql_id=0b1t991khf449) -----
/* SQL Analyze(148,0) */ select count(*) from all_objects_many_list
where created > sysdate-365
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
object line object
handle number name
0x6be0d530 145 package body SYS.DBMS_SQLTUNE_INTERNAL
0x6be0d530 12098 package body SYS.DBMS_SQLTUNE_INTERNAL
0x6be6b738 1229 package body SYS.DBMS_SQLDIAG
0x6becaea8 2 anonymous block
sql_text_length=96
sql=/* SQL Analyze(148,0) */ select count(*) from all_objects_many_list
where created > sysdate-365
----- Explain Plan Dump -----
----- Plan Table -----

============
Plan Table
============
-------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 3512 | |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
| 2 | INDEX RANGE SCAN | ALL_OBJ_M_L_CRTD_NDX| 1282K | 10M | 3512 | 00:00:43 |
-------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - access("CREATED">SYSDATE@!-365)

Content of other_xml column
===========================
db_version : 11.2.0.4
parse_schema : HEMANT
plan_hash : 1689651126
plan_hash_2 : 1742296710
Outline Data:
/*+
BEGIN_OUTLINE_DATA

*** 2016-02-07 21:29:15.838
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "ALL_OBJECTS_MANY_LIST"@"SEL$1" ("ALL_OBJECTS_MANY_LIST"."CREATED"))
END_OUTLINE_DATA
*/

Optimizer state dump:
Compilation Environment Dump
optimizer_mode_hinted = false
optimizer_features_hinted = 0.0.0
parallel_execution_enabled = true
parallel_query_forced_dop = 0
parallel_dml_forced_dop = 0
parallel_ddl_forced_degree = 0
parallel_ddl_forced_instances = 0
_query_rewrite_fudge = 90
optimizer_features_enable = 11.2.0.4
_optimizer_search_limit = 5
...... long list of optimizer parameters ...........
.....................................................
Bug Fix Control Environment
fix 3834770 = 1
fix 3746511 = enabled
fix 4519016 = enabled
...... long list of Bug Fixes .......................
.....................................................

Query Block Registry:
SEL$1 0x99b9000 (PARSER) [FINAL]

:
call(in-use=11728, alloc=49288), compile(in-use=90704, alloc=155568), execution(in-use=6408, alloc=8088)

End of Optimizer State Dump
Dumping Hints
=============
====================== END SQL Statement Dump ======================


So, this is also a supported method.  This DBMS_SQLDIAG package is available from 11.2
Unfortunately, however, DUMP_TRACE is not documented !  (see the 11.2 documentation on DBMS_SQLDIAG).

Note : If the SQL Statement and/or Plan have already been aged out / purged from the Shared Pool , a DUMP_TRACE would, obviously, not be able to print anything.
.
.
.

Categories: DBA Blogs

Trace Files -- 11 : Tracing the Optimization of an SQL Statement

Sun, 2016-01-31 07:53
So far, the previous examples have been on tracing the Execution of SQL statements and/or the Execution Plan used.

But what if you want to trace the Optimization --- identify how the Optimizer determined an "optimal" execution plan -- of an SQL statement.

Note : Pre-11g methods involved Event 10053.   But as with Event 10046, I prefer to use methods where I don't have to use an Event Number but a Name.  So, here I am not demonstrating the Event 10053 method itself.

Let's assume that there is a particular SQL identified as SQL_ID='b086mzzp82x7w' for which we need to know not just the Execution Plan but also how Oracle arrived at the Execution Plan.

Here's one way :

SQL> alter session set events 'trace[rdbms.SQL_Optimizer.*][sql:b086mzzp82x7w]';

Session altered.

SQL> select 'abc' from dual;

'AB
---
abc

SQL> select count(*) from small_insert;

COUNT(*)
----------
4

SQL> select count(*) from all_objects_many_list
2 where created > sysdate-365;

COUNT(*)
----------
25548

SQL> select count(*) from all_objects_many_list;

COUNT(*)
----------
7254201

SQL> select value from v$diag_info where name = 'Default Trace File';

VALUE
------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3102.trc

SQL>

I have multiple SQLs executed in the session but am interested in the Optimization of only 1 SQL.  Note how the specific SQL_ID is specified in the ALTER SESSION SET EVENTS command.

The resultant trace file is a very long trace file with a listing of all the instance/session parameters (hidden and public), all the Bug Fixes and the costing done for the SQL.  The trace file captures only the SQL of interest, all the other SQLs in the same session are *not* in the trace file.

Here is an extract from the trace file :

Registered qb: SEL$1 0x2173aea0 (PARSER)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$1 nbfros=1 flg=0
fro(0): flg=4 objn=35014 hint_alias="ALL_OBJECTS_MANY_LIST"@"SEL$1"

SPM: statement not found in SMB

**************************
Automatic degree of parallelism (ADOP)
**************************
Automatic degree of parallelism is disabled: Parameter.

PM: Considering predicate move-around in query block SEL$1 (#0)
**************************
Predicate Move-Around (PM)
**************************
OPTIMIZER INFORMATION

******************************************
----- Current SQL Statement for this session (sql_id=b086mzzp82x7w) -----
select count(*) from all_objects_many_list
where created > sysdate-365
*******************************************
Legend
The following abbreviations are used by optimizer trace.
CBQT - cost-based query transformation
JPPD - join predicate push-down
OJPPD - old-style (non-cost-based) JPPD
FPD - filter push-down
PM - predicate move-around
CVM - complex view merging
SPJ - select-project-join
SJC - set join conversion
SU - subquery unnesting
OBYE - order by elimination
OST - old style star transformation
ST - new (cbqt) star transformation
CNT - count(col) to count(*) transformation
JE - Join Elimination
JF - join factorization
CBY - connect by
SLP - select list pruning
DP - distinct placement
qb - query block
LB - leaf blocks
DK - distinct keys
LB/K - average number of leaf blocks per key
DB/K - average number of data blocks per key
CLUF - clustering factor
NDV - number of distinct values
Resp - response cost
Card - cardinality
Resc - resource cost
NL - nested loops (join)
SM - sort merge (join)
HA - hash (join)
CPUSPEED - CPU Speed
IOTFRSPEED - I/O transfer speed
IOSEEKTIM - I/O seek time
SREADTIM - average single block read time
MREADTIM - average multiblock read time
MBRC - average multiblock read count
MAXTHR - maximum I/O system throughput
SLAVETHR - average slave I/O throughput
dmeth - distribution method
1: no partitioning required
2: value partitioned
4: right is random (round-robin)
128: left is random (round-robin)
8: broadcast right and partition left
16: broadcast left and partition right
32: partition left using partitioning of right
64: partition right using partitioning of left
256: run the join in serial
0: invalid distribution method
sel - selectivity
ptn - partition
***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************
*************************************
PARAMETERS WITH ALTERED VALUES
Compilation Environment Dump
Bug Fix Control Environment


*************************************
PARAMETERS WITH DEFAULT VALUES
******************************
Compilation Environment Dump
........... long list of parameters and their values .........
..............................................................
.. followed by ...
........... long list of Bug Fixes that are enabled ..........
..............................................................


***************************************
PARAMETERS IN OPT_PARAM HINT
****************************
***************************************
Column Usage Monitoring is ON: tracking level = 1
***************************************

Considering Query Transformations on query block SEL$1 (#0)
**************************
Query transformations (QT)
**************************
JF: Checking validity of join factorization for query block SEL$1 (#0)
JF: Bypassed: not a UNION or UNION-ALL query block.
ST: not valid since star transformation parameter is FALSE
TE: Checking validity of table expansion for query block SEL$1 (#0)
TE: Bypassed: No partitioned table in query block.
CBQT bypassed for query block SEL$1 (#0): no complex view, sub-queries or UNION (ALL) queries.
CBQT: Validity checks failed for b086mzzp82x7w.
CSE: Considering common sub-expression elimination in query block SEL$1 (#0)
*************************
Common Subexpression elimination (CSE)
*************************
CSE: CSE not performed on query block SEL$1 (#0).
OBYE: Considering Order-by Elimination from view SEL$1 (#0)
***************************
Order-by elimination (OBYE)
***************************
OBYE: OBYE bypassed: no order by to eliminate.
CVM: Considering view merge in query block SEL$1 (#0)
OJE: Begin: find best directive for query block SEL$1 (#0)
OJE: End: finding best directive for query block SEL$1 (#0)
CNT: Considering count(col) to count(*) on query block SEL$1 (#0)
*************************
Count(col) to Count(*) (CNT)
*************************
CNT: COUNT() to COUNT(*) not done.
query block SEL$1 (#0) unchanged
Considering Query Transformations on query block SEL$1 (#0)
**************************
Query transformations (QT)
**************************
JF: Checking validity of join factorization for query block SEL$1 (#0)
JF: Bypassed: not a UNION or UNION-ALL query block.
ST: not valid since star transformation parameter is FALSE
TE: Checking validity of table expansion for query block SEL$1 (#0)
TE: Bypassed: No partitioned table in query block.
CBQT bypassed for query block SEL$1 (#0): no complex view, sub-queries or UNION (ALL) queries.
CBQT: Validity checks failed for b086mzzp82x7w.
CSE: Considering common sub-expression elimination in query block SEL$1 (#0)
*************************
Common Subexpression elimination (CSE)
*************************
CSE: CSE not performed on query block SEL$1 (#0).
SU: Considering subquery unnesting in query block SEL$1 (#0)
********************
Subquery Unnest (SU)
********************
SJC: Considering set-join conversion in query block SEL$1 (#0)
*************************
Set-Join Conversion (SJC)
*************************
SJC: not performed
PM: Considering predicate move-around in query block SEL$1 (#0)
**************************
Predicate Move-Around (PM)
**************************
PM: PM bypassed: Outer query contains no views.
PM: PM bypassed: Outer query contains no views.
query block SEL$1 (#0) unchanged
FPD: Considering simple filter push in query block SEL$1 (#0)
"ALL_OBJECTS_MANY_LIST"."CREATED">SYSDATE@!-365
try to generate transitive predicate from check constraints for query block SEL$1 (#0)
finally: "ALL_OBJECTS_MANY_LIST"."CREATED">SYSDATE@!-365

apadrv-start sqlid=12691376846034531580
:
call(in-use=2008, alloc=16344), compile(in-use=56240, alloc=58632), execution(in-use=2504, alloc=4032)

*******************************************
Peeked values of the binds in SQL statement
*******************************************

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT COUNT(*) "COUNT(*)" FROM "HEMANT"."ALL_OBJECTS_MANY_LIST" "ALL_OBJECTS_MANY_LIST" WHERE "ALL_OBJECTS_MANY_LIST"."CREATED">SYSDATE@!-365
kkoqbc: optimizing query block SEL$1 (#0)

:
call(in-use=2056, alloc=16344), compile(in-use=57184, alloc=58632), execution(in-use=2504, alloc=4032)

kkoqbc-subheap (create addr=0x7f5f216ff9d0)
****************
QUERY BLOCK TEXT
****************
select count(*) from all_objects_many_list
where created > sysdate-365
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0
fro(0): flg=0 objn=35014 hint_alias="ALL_OBJECTS_MANY_LIST"@"SEL$1"

-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
Using NOWORKLOAD Stats
CPUSPEEDNW: 937 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 8)

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: ALL_OBJECTS_MANY_LIST Alias: ALL_OBJECTS_MANY_LIST
#Rows: 7197952 #Blks: 98279 AvgRowLen: 93.00 ChainCnt: 0.00
Index Stats::
Index: ALL_OBJ_M_L_CRTD_NDX Col#: 7
LVLS: 2 #LB: 19093 #DK: 1232 LB/K: 15.00 DB/K: 351.00 CLUF: 432893.00
Access path analysis for ALL_OBJECTS_MANY_LIST
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for ALL_OBJECTS_MANY_LIST[ALL_OBJECTS_MANY_LIST]
Column (#7): CREATED(
AvgLen: 8 NDV: 1232 Nulls: 0 Density: 0.000812 Min: 2455803 Max: 2457343
Table: ALL_OBJECTS_MANY_LIST Alias: ALL_OBJECTS_MANY_LIST
Card: Original: 7197952.000000 Rounded: 1346076 Computed: 1346075.60 Non Adjusted: 1346075.60
Access Path: TableScan
Cost: 27174.11 Resp: 27174.11 Degree: 0
Cost_io: 26619.00 Cost_cpu: 6242311042
Resp_io: 26619.00 Resp_cpu: 6242311042
Access Path: index (index (FFS))
Index: ALL_OBJ_M_L_CRTD_NDX
resc_io: 5173.00 resc_cpu: 4598699894
ix_sel: 0.000000 ix_sel_with_filters: 1.000000
Access Path: index (FFS)
Cost: 5581.95 Resp: 5581.95 Degree: 1
Cost_io: 5173.00 Cost_cpu: 4598699894
Resp_io: 5173.00 Resp_cpu: 4598699894
Access Path: index (IndexOnly)
Index: ALL_OBJ_M_L_CRTD_NDX
resc_io: 3573.00 resc_cpu: 294660105
ix_sel: 0.187008 ix_sel_with_filters: 0.187008
Cost: 3599.20 Resp: 3599.20 Degree: 1
Best:: AccessPath: IndexRange
Index: ALL_OBJ_M_L_CRTD_NDX
Cost: 3599.20 Degree: 1 Resp: 3599.20 Card: 1346075.60 Bytes: 0

***************************************


OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
Join order[1]: ALL_OBJECTS_MANY_LIST[ALL_OBJECTS_MANY_LIST]#0
***********************
Best so far: Table#: 0 cost: 3599.2033 card: 1346075.6041 bytes: 10768608
***********************
(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:2000

*********************************
Number of join permutations tried: 1
*********************************
Enumerating distribution method (advanced)

Trying or-Expansion on query block SEL$1 (#0)
Transfer Optimizer annotations for query block SEL$1 (#0)
id=0 frofkks[i] (index start key) predicate="ALL_OBJECTS_MANY_LIST"."CREATED">SYSDATE@!-365
Final cost for query block SEL$1 (#0) - All Rows Plan:
Best join order: 1
Cost: 3599.2033 Degree: 1 Card: 1346076.0000 Bytes: 10768608
Resc: 3599.2033 Resc_io: 3573.0000 Resc_cpu: 294660105
Resp: 3599.2033 Resp_io: 3573.0000 Resc_cpu: 294660105
kkoqbc-subheap (delete addr=0x7f5f216ff9d0, in-use=26384, alloc=32840)
kkoqbc-end:
:
call(in-use=8664, alloc=49288), compile(in-use=59704, alloc=62776), execution(in-use=2504, alloc=4032)

kkoqbc: finish optimizing query block SEL$1 (#0)
apadrv-end
:
call(in-use=8664, alloc=49288), compile(in-use=60616, alloc=62776), execution(in-use=2504, alloc=4032)


Starting SQL statement dump

user_id=87 user_name=HEMANT module=SQL*Plus action=
sql_id=b086mzzp82x7w plan_hash_value=1689651126 problem_type=3
----- Current SQL Statement for this session (sql_id=b086mzzp82x7w) -----
select count(*) from all_objects_many_list
where created > sysdate-365
sql_text_length=71
sql=select count(*) from all_objects_many_list
where created > sysdate-365
----- Explain Plan Dump -----
----- Plan Table -----

============
Plan Table
============
-------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 3599 | |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
| 2 | INDEX RANGE SCAN | ALL_OBJ_M_L_CRTD_NDX| 1315K | 10M | 3599 | 00:00:44 |
-------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - access("CREATED">SYSDATE@!-365)

Content of other_xml column
===========================
db_version : 11.2.0.4
parse_schema : HEMANT
plan_hash : 1689651126
plan_hash_2 : 1742296710
Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "ALL_OBJECTS_MANY_LIST"@"SEL$1" ("ALL_OBJECTS_MANY_LIST"."CREATED"))
END_OUTLINE_DATA
*/

Optimizer state dump:
Compilation Environment Dump
optimizer_mode_hinted = false
optimizer_features_hinted = 0.0.0
........... long list of parameters and their values .........
..............................................................
.. followed by ...
........... long list of Bug Fixes that are enabled ..........
..............................................................

Query Block Registry:
SEL$1 0x2173aea0 (PARSER) [FINAL]

:
call(in-use=11728, alloc=49288), compile(in-use=90576, alloc=152120), execution(in-use=6440, alloc=8088)

End of Optimizer State Dump
Dumping Hints
=============
====================== END SQL Statement Dump ======================

The trace file captured only the SQL of interest.  It also shows all the instance /session parameters and Bug Fixes that are relevant (these are very long lists so I have not reproduced them in entirety).
Note : The listing of parameters and Bug Fixes are very important in that if you have different execution plans in two different databases, you must verify the parameters and bug fixes and ensure that any differences in them are not relevant.

From the trace file, we can determine that this is the Execution Plan chosen :
-------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 3599 | |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
| 2 | INDEX RANGE SCAN | ALL_OBJ_M_L_CRTD_NDX| 1315K | 10M | 3599 | 00:00:44 |
-------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - access("CREATED">SYSDATE@!-365)

Content of other_xml column
===========================
db_version : 11.2.0.4
parse_schema : HEMANT
plan_hash : 1689651126
plan_hash_2 : 1742296710
Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "ALL_OBJECTS_MANY_LIST"@"SEL$1" ("ALL_OBJECTS_MANY_LIST"."CREATED"))
END_OUTLINE_DATA
*/

The computation of Cost appears here :
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for ALL_OBJECTS_MANY_LIST[ALL_OBJECTS_MANY_LIST]
Column (#7): CREATED(
AvgLen: 8 NDV: 1232 Nulls: 0 Density: 0.000812 Min: 2455803 Max: 2457343
Table: ALL_OBJECTS_MANY_LIST Alias: ALL_OBJECTS_MANY_LIST
Card: Original: 7197952.000000 Rounded: 1346076 Computed: 1346075.60 Non Adjusted: 1346075.60
Access Path: TableScan
Cost: 27174.11 Resp: 27174.11 Degree: 0
Cost_io: 26619.00 Cost_cpu: 6242311042
Resp_io: 26619.00 Resp_cpu: 6242311042
Access Path: index (index (FFS))
Index: ALL_OBJ_M_L_CRTD_NDX
resc_io: 5173.00 resc_cpu: 4598699894
ix_sel: 0.000000 ix_sel_with_filters: 1.000000
Access Path: index (FFS)
Cost: 5581.95 Resp: 5581.95 Degree: 1
Cost_io: 5173.00 Cost_cpu: 4598699894
Resp_io: 5173.00 Resp_cpu: 4598699894
Access Path: index (IndexOnly)
Index: ALL_OBJ_M_L_CRTD_NDX
resc_io: 3573.00 resc_cpu: 294660105
ix_sel: 0.187008 ix_sel_with_filters: 0.187008
Cost: 3599.20 Resp: 3599.20 Degree: 1
Best:: AccessPath: IndexRange
Index: ALL_OBJ_M_L_CRTD_NDX
Cost: 3599.20 Degree: 1 Resp: 3599.20 Card: 1346075.60 Bytes: 0

Note how different Access Paths (Table Scan, Index FFS, IndexOnly,IndexRange) are all listed. The Best is shown as an IndexRange on the ALL_OBJ_M_L_CRTD_NDX with a Cost of 3599.20 More details appear here :
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
Join order[1]: ALL_OBJECTS_MANY_LIST[ALL_OBJECTS_MANY_LIST]#0
***********************
Best so far: Table#: 0 cost: 3599.2033 card: 1346075.6041 bytes: 10768608
***********************
(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:2000

*********************************
Number of join permutations tried: 1
*********************************
Enumerating distribution method (advanced)

Trying or-Expansion on query block SEL$1 (#0)
Transfer Optimizer annotations for query block SEL$1 (#0)
id=0 frofkks[i] (index start key) predicate="ALL_OBJECTS_MANY_LIST"."CREATED">SYSDATE@!-365
Final cost for query block SEL$1 (#0) - All Rows Plan:
Best join order: 1
Cost: 3599.2033 Degree: 1 Card: 1346076.0000 Bytes: 10768608
Resc: 3599.2033 Resc_io: 3573.0000 Resc_cpu: 294660105
Resp: 3599.2033 Resp_io: 3573.0000 Resc_cpu: 294660105

This is a very detailed listing for an SQL query on a single Table (no joins) and a single candidate index.  Try running this with an SQL with Join of two or more tables and more than one candidate Index on each and see how complicated the Cost calculation becomes.


Note : To disable tracing in the session, I would run :

ALTER SESSION SET EVENTS 'trace[rdbms.SQL_Optimizer.*] off';


This sort of tracing can also be done with ALTER SYSTEM if you are not sure which session will be running the SQL_ID of interest and cannot interactively invoke the SQL from a private session.  
.
.
.



Categories: DBA Blogs

Trace Files -- 10c : Query and DML (INSERT)

Sun, 2016-01-24 04:52
In the previous posts, I have traced either
SELECT
or
INSERT or UPDATE or DELETE
statements

I have pointed out that the block statistics are reported as "FETCH" statistics for SELECTs and "EXECUTE" statistics for the DMLs

What if we have an INSERT ... AS SELECT ?

SQL ID: 5pf0puy1pmcvc Plan Hash: 2393429040

insert into all_objects_many_list select * from all_objects_short_list


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 0.73 16.19 166 1655 14104 28114
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.74 16.21 166 1655 14104 28114

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 87
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 LOAD TABLE CONVENTIONAL (cr=1766 pr=166 pw=0 time=16204380 us)
28114 28114 28114 TABLE ACCESS FULL ALL_OBJECTS_SHORT_LIST (cr=579 pr=0 pw=0 time=127463 us cost=158 size=2614881 card=28117)


insert into all_objects_many_list
select * from all_objects_short_list
where rownum < 11

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 6 11 10
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 6 11 10

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 87
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 LOAD TABLE CONVENTIONAL (cr=6 pr=0 pw=0 time=494 us)
10 10 10 COUNT STOPKEY (cr=3 pr=0 pw=0 time=167 us)
10 10 10 TABLE ACCESS FULL ALL_OBJECTS_SHORT_LIST (cr=3 pr=0 pw=0 time=83 us cost=158 size=2614881 card=28117)


Since it is an INSERT statement, the block statistics are reported against EXECUTE (nothing reported against the FETCH), even though the Row Source Operations section shows use Table Access (i.e. SELECT) against the ALL_OBJECTS_SHORT_LIST table. Also note, as we have seen in the previous trace on INSERTs, the target table does not get reported in the Row Source Operations.

Here's another example.

SQL ID: 5fgnrpgk3uumc Plan Hash: 2703984749

insert into all_objects_many_list select * from dba_objects


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.04 0.25 0 0 0 0
Execute 1 0.63 3.94 82 2622 13386 28125
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.67 4.20 82 2622 13386 28125

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 87
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 LOAD TABLE CONVENTIONAL (cr=2736 pr=82 pw=0 time=3954384 us)
28125 28125 28125 VIEW DBA_OBJECTS (cr=360 pr=2 pw=0 time=1898588 us cost=105 size=5820219 card=28117)
28125 28125 28125 UNION-ALL (cr=360 pr=2 pw=0 time=1735711 us)
0 0 0 TABLE ACCESS BY INDEX ROWID SUM$ (cr=2 pr=2 pw=0 time=65154 us cost=1 size=11 card=1)
1 1 1 INDEX UNIQUE SCAN I_SUM$_1 (cr=1 pr=1 pw=0 time=61664 us cost=0 size=0 card=1)(object id 986)
0 0 0 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=0 pr=0 pw=0 time=0 us cost=3 size=25 card=1)
0 0 0 INDEX RANGE SCAN I_OBJ1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)(object id 36)
28124 28124 28124 FILTER (cr=354 pr=0 pw=0 time=1094905 us)
28124 28124 28124 HASH JOIN (cr=354 pr=0 pw=0 time=911322 us cost=102 size=3402036 card=28116)
68 68 68 TABLE ACCESS FULL USER$ (cr=5 pr=0 pw=0 time=328 us cost=3 size=1224 card=68)
28124 28124 28124 HASH JOIN (cr=349 pr=0 pw=0 time=612040 us cost=99 size=2895948 card=28116)
68 68 68 INDEX FULL SCAN I_USER2 (cr=1 pr=0 pw=0 time=159 us cost=1 size=1496 card=68)(object id 47)
28124 28124 28124 TABLE ACCESS FULL OBJ$ (cr=348 pr=0 pw=0 time=147446 us cost=98 size=2277396 card=28116)
0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=2 size=30 card=1)
0 0 0 INDEX SKIP SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=20 card=1)(object id 47)
0 0 0 INDEX RANGE SCAN I_OBJ4 (cr=0 pr=0 pw=0 time=0 us cost=1 size=10 card=1)(object id 39)
1 1 1 NESTED LOOPS (cr=4 pr=0 pw=0 time=262 us cost=3 size=38 card=1)
1 1 1 TABLE ACCESS FULL LINK$ (cr=2 pr=0 pw=0 time=180 us cost=2 size=20 card=1)
1 1 1 TABLE ACCESS CLUSTER USER$ (cr=2 pr=0 pw=0 time=50 us cost=1 size=18 card=1)
1 1 1 INDEX UNIQUE SCAN I_USER# (cr=1 pr=0 pw=0 time=21 us cost=0 size=0 card=1)(object id 11)

Only the source tables are reported in the Row Source Operations section. All the blocks are reported in the EXECUTE phase. Why does the "query" count in the EXECUTE statistics differ from the "cr" count reported for the LOAD TABLE CONVENTIONAL. (LOAD TABLE CONVENTIONAL indicates a regular non-direct-path INSERT).
.
.
.

Categories: DBA Blogs