Hemant K Chitale
I am an Oracle Database Expert in Singapore.
get an rss feed of this blog at http://hemantoracledba.blogspot.com/feeds/posts/default?alt=rss
get an rss feed of this blog at http://hemantoracledba.blogspot.com/feeds/posts/default?alt=rss
Updated: 27 min 47 sec ago
DROP A Tablespace After a Backup
What happens if you drop a tablespace after it is backed up ?
Here's the backup :
RMAN> backup as compressed backupset database;
Starting backup at 16-JUN-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00001 name=/home/oracle/app/oracle/oradata/orcl/system01.dbf
input datafile file number=00004 name=/home/oracle/app/oracle/oradata/orcl/users01.dbf
input datafile file number=00003 name=/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
input datafile file number=00011 name=/home/oracle/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_hemant_8pnowslc_.dbf
input datafile file number=00005 name=/home/oracle/app/oracle/oradata/orcl/example01.dbf
input datafile file number=00010 name=/home/oracle/app/oracle/oradata/orcl/APEX_2614203650434107.dbf
channel ORA_DISK_1: starting piece 1 at 16-JUN-13
channel ORA_DISK_1: finished piece 1 at 16-JUN-13
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_06_16/o1_mf_nnndf_TAG20130616T080419_8vvnzn37_.bkp tag=TAG20130616T080419 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:36
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 16-JUN-13
channel ORA_DISK_1: finished piece 1 at 16-JUN-13
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_06_16/o1_mf_ncsnf_TAG20130616T080419_8vvo4k8w_.bkp tag=TAG20130616T080419 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 16-JUN-13
RMAN>
Here's the subsequent DROP TABLESPACE :
SQL> drop tablespace APEX_2614203650434107 including contents and datafiles;
Tablespace dropped.
SQL>
What happens when I try to LIST the BACKUP of the datafile / tablespace ?
RMAN> list backup of datafile 10;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of list command at 06/16/2013 08:07:44
RMAN-20201: datafile not found in the recovery catalog
RMAN-06010: error while looking up datafile: 10
RMAN>
RMAN> list backup of tablespace APEX_2614203650434107;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of list command at 06/16/2013 08:12:12
RMAN-20202: Tablespace not found in the recovery catalog
RMAN-06019: could not translate tablespace name "APEX_2614203650434107"
RMAN>
What does a full LIST BACKUP OF DATABASE show ?
RMAN> list backup of database ;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4 Full 799.58M DISK 00:02:30 16-JUN-13
BP Key: 4 Status: AVAILABLE Compressed: YES Tag: TAG20130616T080419
Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_06_16/o1_mf_nnndf_TAG20130616T080419_8vvnzn37_.bkp
List of Datafiles in backup set 4
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 14093203 16-JUN-13 /home/oracle/app/oracle/oradata/orcl/system01.dbf
2 Full 14093203 16-JUN-13 /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
3 Full 14093203 16-JUN-13 /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
4 Full 14093203 16-JUN-13 /home/oracle/app/oracle/oradata/orcl/users01.dbf
5 Full 14093203 16-JUN-13 /home/oracle/app/oracle/oradata/orcl/example01.dbf
10 Full 14093203 16-JUN-13
11 Full 14093203 16-JUN-13 /home/oracle/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_hemant_8pnowslc_.dbf
RMAN>
Datafile 10 appears as a NULL entry. It cannot be restored as it no longer belongs to the database.
A RESTORE obviously fails :
RMAN> restore datafile 10;
Starting restore at 16-JUN-13
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 06/16/2013 08:15:32
RMAN-20201: datafile not found in the recovery catalog
RMAN-06010: error while looking up datafile: 10
RMAN>
So, there you have it. Once a datafile doesn't belong to the database it cannot be restored.
.
.
.
Categories: DBA Blogs
Bug 10013177 running Aggregation on Expression indexed by an FBI
I noticed an interesting Bug on MOS today. When running an Aggregation against an Expression that has a Function Based Index, results may be truncated. The bug notes that dumps and internal errors are also possible.
Here's a demo. (tested in 11.2.0.2)
Start with a table and an FBI
.
.
.
Here's a demo. (tested in 11.2.0.2)
Start with a table and an FBI
SQL> create table test_10013177 (col_1 number(6,4));Funnily, the results in the last query are truncated !
Table created.
SQL> insert into test_10013177 values (12.3456);
1 row created.
SQL> create index index_10013177 on test_10013177(col_1+1);
Index created.
SQL>
SQL> exec dbms_stats.gather_table_stats('','TEST_10013177');
PL/SQL procedure successfully completed.
SQL> select col_1+1 from test_10013177;
COL_1+1
----------
13.3456
SQL> select max(col_1+1) from test_10013177;
MAX(COL_1+1)
------------
13.3456
SQL> select /*+ full (t) */ max(col_1+1) from test_10013177 t;
MAX(COL_1+1)
------------
13
SQL>
.
.
.
Categories: DBA Blogs
BACKUP CURRENT CONTROLFILE creates a Snapshot Controlfile
A short post today.
Recently, there was a discussion on forums about when a Snapshot controlfile is created. It is known that the snapshot controlfile is created to re-synchronise with the Catalog.
But in my test database environment, I don't use a Catalog. I only use the controlfile (and backups of it).
One poser said that he ran BACKUP DATABASE followed by BACKUP CURRENT CONTROLFILE. He asked if the same snapshot controlfile was used [through the two commands]. I pointed out that since they were two *separate* BACKUP calls, Oracle would create a separate Snapshot Controlfile for the second BACKUP command.
Here is a simple demo (note : I don't use a Catalog)
First the current state of the directory ($ORACLE_HOME/dbs in my case) where the Snapshot Controlfile would be created :
Next, I run a BACKUP CURRENT CONTROLFILE :
Is there a snapshot controlfile ?
.
.
.
Recently, there was a discussion on forums about when a Snapshot controlfile is created. It is known that the snapshot controlfile is created to re-synchronise with the Catalog.
But in my test database environment, I don't use a Catalog. I only use the controlfile (and backups of it).
One poser said that he ran BACKUP DATABASE followed by BACKUP CURRENT CONTROLFILE. He asked if the same snapshot controlfile was used [through the two commands]. I pointed out that since they were two *separate* BACKUP calls, Oracle would create a separate Snapshot Controlfile for the second BACKUP command.
Here is a simple demo (note : I don't use a Catalog)
First the current state of the directory ($ORACLE_HOME/dbs in my case) where the Snapshot Controlfile would be created :
[oracle@localhost dbs]$ pwdThere is no snapshot currently present.
/home/oracle/app/oracle/product/11.2.0/dbhome_2/dbs
[oracle@localhost dbs]$ date
Mon May 20 08:17:46 PDT 2013
[oracle@localhost dbs]$ ls -ltr|tail -3
-rw-r----- 1 oracle oracle 1536 Oct 2 2010 orapworcl
-rw-rw---- 1 oracle oracle 1544 May 20 08:16 hc_orcl.dat
-rw-r----- 1 oracle oracle 3584 May 20 08:16 spfileorcl.ora
Next, I run a BACKUP CURRENT CONTROLFILE :
[oracle@localhost dbs]$ rman target /The controlfile backup got written to the FRA.
Recovery Manager: Release 11.2.0.2.0 - Production on Mon May 20 08:17:53 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1229390655)
RMAN> backup current controlfile;
Starting backup at 20-MAY-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=69 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 20-MAY-13
channel ORA_DISK_1: finished piece 1 at 20-MAY-13
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_05_20/o1_mf_ncnnf_TAG20130520T081800_8snhob80_.bkp tag=TAG20130520T081800 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 20-MAY-13
RMAN> quit
Recovery Manager complete.
Is there a snapshot controlfile ?
[oracle@localhost dbs]$ ls -ltr|tail -3Yes, a snapshot controlfile did get created !
-rw-rw---- 1 oracle oracle 1544 May 20 08:16 hc_orcl.dat
-rw-r----- 1 oracle oracle 3584 May 20 08:16 spfileorcl.ora
-rw-rw---- 1 oracle oracle 9748480 May 20 08:18 snapcf_orcl.f
[oracle@localhost dbs]$
.
.
.
Categories: DBA Blogs
Preparing for Oracle Certification
Links to useful information on preparing for Oracle Certification.
1. Youtube "HOW TO : Preparing For Your Oracle Certification" by OracleCertification
2. Youtube "Oracle Certification Practice Exams" by OracleCertification
3. Certification Blog "Oracle Certification" by Oracle Certification Program
4. Forum "Certification" by Oracle Forums
5. WebSites
a. "Oracle Certification Prep" by Matthew Morris
b. "MindHub" by PearsonVue
6. Test Software Vendors
a. SelfTestSoftware
b. Transcender
c. UCertify
.
.
.
1. Youtube "HOW TO : Preparing For Your Oracle Certification" by OracleCertification
2. Youtube "Oracle Certification Practice Exams" by OracleCertification
3. Certification Blog "Oracle Certification" by Oracle Certification Program
4. Forum "Certification" by Oracle Forums
5. WebSites
a. "Oracle Certification Prep" by Matthew Morris
b. "MindHub" by PearsonVue
6. Test Software Vendors
a. SelfTestSoftware
b. Transcender
c. UCertify
.
.
.
Categories: DBA Blogs
SSD Performance for Oracle Databases
A link to a post by Gwen (Chen) Shapira on SSD Performance for Oracle Databases.
A link to a post by Kevin Closson Manly Men Only Use Solid State Disk For Redo Logging. LGWR I/O is Simple, But Not LGWR Processing
A link to a post by Flashdba Does My Database Need Flash ?
.
.
.
A link to a post by Kevin Closson Manly Men Only Use Solid State Disk For Redo Logging. LGWR I/O is Simple, But Not LGWR Processing
A link to a post by Flashdba Does My Database Need Flash ?
.
.
.
Categories: DBA Blogs
Single Row Fetch from a LOB
When dealing with a LOB column, Oracle does not use array fetches but always does a single-row fetch.
For example, for a CLOB, one row may have a 20-character string but the next row may have a 2billion character string. Each FETCH is restricted to 1 row. The SQLPlus command SET ARRAYSIZE is ignore.
Here is a simple test case : Firs create the test table with 100 rows
Fetch from the VARCHAR2 column with SET ARRAYSIZE 20 and view the results of the trace.
Fetch from the CLOB Column with SET ARRAYSIZE 20 and view the results of the trace :
Notice that the number of blocks read (consistent-reads) is 7 and the COST is 3 in both cases. So, "consistent gets" and COST are not always the true explanation of the COST and elapsed time for a query.
.
.
.
For example, for a CLOB, one row may have a 20-character string but the next row may have a 2billion character string. Each FETCH is restricted to 1 row. The SQLPlus command SET ARRAYSIZE is ignore.
Here is a simple test case : Firs create the test table with 100 rows
create table MY_VC_CLOB
(id_col number,
vc_col varchar2(200),
clob_col clob)
/
insert into my_vc_clob
select rownum,
'Row : ' || rownum ||' : This is the VARCHAR2 column ',
'Row : ' || rownum ||' : This is the CLOB column '
from dual
connect by level < 101
/
Fetch from the VARCHAR2 column with SET ARRAYSIZE 20 and view the results of the trace.
set arraysize 20So, there were 5 (+1 extra always) FETCHes and 5 (+1) SQLNet round-trips. Ignore the time for the SQL*Net message from client -- I had a 2second extra wait when executing the SQL.
alter session set tracefile_identifier='Fetch_VC';
exec dbms_session.session_trace_enable;
select id_col, vc_col
from my_vc_clob
order by id_col
/
select id_col, vc_col
from my_vc_clob
order by id_col
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 6 0.00 0.00 0 7 0 100
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8 0.00 0.00 0 8 0 100
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 353
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
100 100 100 SORT ORDER BY (cr=7 pr=0 pw=0 time=140 us cost=4 size=11500 card=100)
100 100 100 TABLE ACCESS FULL MY_VC_CLOB (cr=7 pr=0 pw=0 time=133 us cost=3 size=11500 card=100)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 6 0.00 0.00
SQL*Net message from client 6 2.81 2.83
********************************************************************************
Fetch from the CLOB Column with SET ARRAYSIZE 20 and view the results of the trace :
set arraysize 20So, there were 100 (+1 extra always) FETCHEs and 100 SQLNet round-trips.
alter session set tracefile_identifier='Fetch_CLOB';
exec dbms_session.session_trace_enable;
select id_col, clob_col
from my_vc_clob
order by id_col
/
select id_col, clob_col
from my_vc_clob
order by id_col
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 101 0.00 0.00 0 7 0 100
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 103 0.00 0.00 0 8 0 100
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 353
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
100 100 100 SORT ORDER BY (cr=7 pr=0 pw=0 time=231 us cost=48 size=201500 card=100)
100 100 100 TABLE ACCESS FULL MY_VC_CLOB (cr=7 pr=0 pw=0 time=256 us cost=3 size=201500 card=100)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 102 0.00 0.00
SQL*Net message from client 102 1.29 1.34
********************************************************************************
Notice that the number of blocks read (consistent-reads) is 7 and the COST is 3 in both cases. So, "consistent gets" and COST are not always the true explanation of the COST and elapsed time for a query.
.
.
.
Categories: DBA Blogs
Oracle Forums due for Upgrade
forums.oracle.com will be undergoing an upgrade in a few days.
This is the announcement.
This is the discussion thread. (requires login to forums.oracle.com)
.
.
.
This is the announcement.
This is the discussion thread. (requires login to forums.oracle.com)
.
.
.
Categories: DBA Blogs
Useful Oracle Youtube videos
This will be a periodically updated list of useful Oracle Youtube videos.
17-Mar-13 Jonathan Lewis -- Pessimist Exadata
17-Mar-13 Connor McDonald -- RAC Upgrade Nightmare
19-Mar-13 Cary Millsap (with Kerry Osborne) -- My Perspective on Exadata
24-Mar-13 Tim Gorman -- Data Warehousing
13-Apr-13 Tom Kyte -- What are we still doing wrong ?
03-Jun-13 How to Download and Install Virtual Box
16-Jun-13 Gwen (Chen) Shapira -- Hadoop and Oracle Database
.
.
.
17-Mar-13 Jonathan Lewis -- Pessimist Exadata
17-Mar-13 Connor McDonald -- RAC Upgrade Nightmare
19-Mar-13 Cary Millsap (with Kerry Osborne) -- My Perspective on Exadata
24-Mar-13 Tim Gorman -- Data Warehousing
13-Apr-13 Tom Kyte -- What are we still doing wrong ?
03-Jun-13 How to Download and Install Virtual Box
16-Jun-13 Gwen (Chen) Shapira -- Hadoop and Oracle Database
.
.
.
Categories: DBA Blogs
Segment Size of a Partition (11.2.0.2 and above)
In 11.2.0.2, Oracle introduced a change to the default size of Partition Segment.
According to Support Note "Initial Extent Size of a Partition Changed To 8MB From 64KB After Upgrade to 11.2.0.2 [ID 1295484.1]", the INITIAL Extent of a Partition is now 8MB. Furthermore, Deferred Segment Creation also applies to Partitions.
Thus, when you initially create a Partitioned Table, (and DEFERRED_SEGMENT_CREATION is left at the default value of TRUE), the Partitions are segmentless. As soon as you populate a Partition, it is created as a Segment with an INITIAL of 8MB and NEXT of 1MB.
Here is a simple demo :
Note how the two Partitions are created with "SEGMENT CREATION DEFERRED" and there is no Storage allocated to them yet. Let's look for the Segments and Extents.
So, although the three Partitions exist, no Segments and Extents yet exist. Now, let's populate one Partition -- just inserting a single row will suffice
.
Now, let's look for the Segment and Extent. Note that Partition P_ID_200 would have the row with ID=150.
So, Partition P_ID_200 now has an 8MB Segment with an Initial Extent of 8MB. Let's view the DDL for the Table.
Note how Partitions P_ID_100 and P_MAX are unchanged but Partition P_ID_200 now has SEGMENT CREATION IMMEDIATE and STORAGE (INITIAL 8M NEXT 1M) !
What if I were to split the Partitions to create new Partitions ?
So remember : the default behaviour of Segment creation for Partitions has been changed since 11.2.0.2
Having said that, the note that I mentinoed at the beginning does document how this default behaviour can be overridden.
You need this information when you are creating a table with, say, 125 partitions but have only 1 to a few thousand rows in each partition and have to explain why the table is taking up 1GB of space.
Also note that once a Partition has had an 8MB extent allocated to it, any new Partitions that you create out of splitting it will also hae 8MB extents allocated --even if they are empty !
.
.
.
According to Support Note "Initial Extent Size of a Partition Changed To 8MB From 64KB After Upgrade to 11.2.0.2 [ID 1295484.1]", the INITIAL Extent of a Partition is now 8MB. Furthermore, Deferred Segment Creation also applies to Partitions.
Thus, when you initially create a Partitioned Table, (and DEFERRED_SEGMENT_CREATION is left at the default value of TRUE), the Partitions are segmentless. As soon as you populate a Partition, it is created as a Segment with an INITIAL of 8MB and NEXT of 1MB.
Here is a simple demo :
SQL*Plus: Release 11.2.0.2.0 Production on Sat Mar 9 16:45:49 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter user-name: hemant/hemant
ERROR:
ORA-28002: the password will expire within 7 days
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL>
SQL> create tablespace PARTITION_TEST ;
Tablespace created.
SQL>
SQL> select extent_management, allocation_type, segment_space_management,
2 initial_extent, next_extent, pct_increase
3 from dba_tablespaces
4 where tablespace_name = 'PARTITION_TEST'
5 /
EXTENT_MAN ALLOCATIO SEGMEN INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE
---------- --------- ------ -------------- ----------- ------------
LOCAL SYSTEM AUTO 65536
SQL>
SQL> l
1 create table a_partitioned_table
2 (id number not null,
3 data_column varchar2(20)
4 )
5 partition by range (id)
6 (
7 partition P_ID_100 values less than ('101') tablespace PARTITION_TEST,
8 partition P_ID_200 values less than ('201') tablespace PARTITION_TEST,
9 partition P_MAX values less than (MAXVALUE) tablespace PARTITION_TEST
10* )
SQL> /
Table created.
SQL>
SQL> set pages600
SQL> set long 32767
SQL> select dbms_metadata.get_ddl('TABLE','A_PARTITIONED_TABLE','HEMANT') from dual;
DBMS_METADATA.GET_DDL('TABLE','A_PARTITIONED_TABLE','HEMANT')
--------------------------------------------------------------------------------
CREATE TABLE "HEMANT"."A_PARTITIONED_TABLE"
( "ID" NUMBER NOT NULL ENABLE,
"DATA_COLUMN" VARCHAR2(20)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
PARTITION BY RANGE ("ID")
(PARTITION "P_ID_100" VALUES LESS THAN ('101') SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "PARTITION_TEST" ,
PARTITION "P_ID_200" VALUES LESS THAN ('201') SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "PARTITION_TEST" ,
PARTITION "P_MAX" VALUES LESS THAN (MAXVALUE) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "PARTITION_TEST" )
SQL>
Note how the two Partitions are created with "SEGMENT CREATION DEFERRED" and there is no Storage allocated to them yet. Let's look for the Segments and Extents.
SQL> select segment_name, partition_name, blocks, bytes/1024
2 from dba_segments
3 where tablespace_name = 'PARTITION_TEST';
no rows selected
SQL> select segment_name, partition_name, extent_id, file_id, block_id, blocks
2 from dba_extents
3 where tablespace_name = 'PARTITION_TEST';
no rows selected
SQL> select table_name, partition_name from user_tab_partitions;
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
A_PARTITIONED_TABLE P_MAX
A_PARTITIONED_TABLE P_ID_200
A_PARTITIONED_TABLE P_ID_100
SQL>
So, although the three Partitions exist, no Segments and Extents yet exist. Now, let's populate one Partition -- just inserting a single row will suffice
.
SQL> insert into a_partitioned_table values (150,'HundredFifty');
1 row created.
SQL> commit;
Commit complete.
SQL>
Now, let's look for the Segment and Extent. Note that Partition P_ID_200 would have the row with ID=150.
SQL> select segment_name, partition_name, blocks, bytes/1024
2 from dba_segments
3 where tablespace_name = 'PARTITION_TEST';
SEGMENT_NAME
--------------------------------------------------------------------------------
PARTITION_NAME BLOCKS BYTES/1024
------------------------------ ---------- ----------
A_PARTITIONED_TABLE
P_ID_200 1024 8192
SQL> select segment_name, partition_name, extent_id, file_id, block_id, blocks
2 from dba_extents
3 where tablespace_name = 'PARTITION_TEST';
SEGMENT_NAME
--------------------------------------------------------------------------------
PARTITION_NAME EXTENT_ID FILE_ID BLOCK_ID BLOCKS
------------------------------ ---------- ---------- ---------- ----------
A_PARTITIONED_TABLE
P_ID_200 0 6 128 1024
SQL>
So, Partition P_ID_200 now has an 8MB Segment with an Initial Extent of 8MB. Let's view the DDL for the Table.
SQL> select dbms_metadata.get_ddl('TABLE','A_PARTITIONED_TABLE','HEMANT') from dual;
DBMS_METADATA.GET_DDL('TABLE','A_PARTITIONED_TABLE','HEMANT')
--------------------------------------------------------------------------------
CREATE TABLE "HEMANT"."A_PARTITIONED_TABLE"
( "ID" NUMBER NOT NULL ENABLE,
"DATA_COLUMN" VARCHAR2(20)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
PARTITION BY RANGE ("ID")
(PARTITION "P_ID_100" VALUES LESS THAN ('101') SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "PARTITION_TEST" ,
PARTITION "P_ID_200" VALUES LESS THAN ('201') SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "PARTITION_TEST" ,
PARTITION "P_MAX" VALUES LESS THAN (MAXVALUE) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "PARTITION_TEST" )
SQL>
Note how Partitions P_ID_100 and P_MAX are unchanged but Partition P_ID_200 now has SEGMENT CREATION IMMEDIATE and STORAGE (INITIAL 8M NEXT 1M) !
What if I were to split the Partitions to create new Partitions ?
SQL> alter table A_PARTITIONED_TABLE split partition P_ID_100 at ('51')
2 into (partition P_ID_50, partition P_ID_100);
Table altered.
SQL> alter table A_PARTITIONED_TABLE split partition P_ID_200 at ('151')
2 into (partition P_ID_150, partition P_ID_200);
Table altered.
SQL>
SQL> select dbms_metadata.get_ddl('TABLE','A_PARTITIONED_TABLE','HEMANT') from dual;
DBMS_METADATA.GET_DDL('TABLE','A_PARTITIONED_TABLE','HEMANT')
--------------------------------------------------------------------------------
CREATE TABLE "HEMANT"."A_PARTITIONED_TABLE"
( "ID" NUMBER NOT NULL ENABLE,
"DATA_COLUMN" VARCHAR2(20)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
PARTITION BY RANGE ("ID")
(PARTITION "P_ID_50" VALUES LESS THAN ('51') SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "PARTITION_TEST" ,
PARTITION "P_ID_100" VALUES LESS THAN ('101') SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "PARTITION_TEST" ,
PARTITION "P_ID_150" VALUES LESS THAN ('151') SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "PARTITION_TEST" ,
PARTITION "P_ID_200" VALUES LESS THAN ('201') SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "PARTITION_TEST" ,
PARTITION "P_MAX" VALUES LESS THAN (MAXVALUE) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "PARTITION_TEST" )
SQL>
SQL> select segment_name, partition_name, blocks, bytes/1024
2 from dba_segments
3 where tablespace_name = 'PARTITION_TEST';
SEGMENT_NAME
--------------------------------------------------------------------------------
PARTITION_NAME BLOCKS BYTES/1024
------------------------------ ---------- ----------
A_PARTITIONED_TABLE
P_ID_200 1024 8192
A_PARTITIONED_TABLE
P_ID_150 1024 8192
SQL> select segment_name, partition_name, extent_id, file_id, block_id, blocks
2 from dba_extents
3 where tablespace_name = 'PARTITION_TEST';
SEGMENT_NAME
--------------------------------------------------------------------------------
PARTITION_NAME EXTENT_ID FILE_ID BLOCK_ID BLOCKS
------------------------------ ---------- ---------- ---------- ----------
A_PARTITIONED_TABLE
P_ID_150 0 6 128 1024
A_PARTITIONED_TABLE
P_ID_200 0 6 1152 1024
SQL>
So, only the two Partitions that have been created out of P_ID_200 have been materialized as Segments and Extents while the two Partitions split out of P_ID_100 are still Segment-less (without Storage allocated). So remember : the default behaviour of Segment creation for Partitions has been changed since 11.2.0.2
Having said that, the note that I mentinoed at the beginning does document how this default behaviour can be overridden.
You need this information when you are creating a table with, say, 125 partitions but have only 1 to a few thousand rows in each partition and have to explain why the table is taking up 1GB of space.
Also note that once a Partition has had an 8MB extent allocated to it, any new Partitions that you create out of splitting it will also hae 8MB extents allocated --even if they are empty !
.
.
.
Categories: DBA Blogs
Short-Circuiting the COST
The Oracle Optimizer can be smart when evaluating predicates in a query.
If it finds a predicate that causes a "short-circuit" --- one that prevents rows from being returned by the query --- it can evaluate the COST of the query to 0 (zero). Normally, we would never expect a COST to zero --- even where we know zero rows will be returned by the query, Oracle may have to undertake some I/O (a Full Table Scan or an Index Range Scan in the simplest cases) and some CPU cycles to verify the resulting blocks for the expected result.
Here I show a simple example of a short-circuit.
First, I build a Table and an Index, with statistics :
The actual resulting row count is 3. (The 24 row count is an estimate)
The estimated cost of the Index Range Scan is 5, the estimated cost of the Table Access (1741 rowids) and Filter is 47 (52-5) and the estimated cost of the Sort (for 24 rows) is 1 (53-52), resulting in a total cost of 53.
Oracle read a total of 5 blocks (consistent gets).
What happens if I add an "AND 1=2" predicate (which is always FALSE) ?
But notice the "filter (NULL IS NOT NULL)". This is the short-circuit that Oracle adds. This will always evaluate to FALSE. This is Oracle's way of translating the "1=2" condition that I added to the query.
Although there is a supposed COST of 5 for the 1,741 rowids from the Index Range Scan and a supposed COST of 47 (52-5) for the Table Access of the 1,741 rowids and filtering to 24 rows for "OBJECT_NAME LIKE 'S%'), Oracle then proceeds to add the NULL IS NOT NULL FILTER before the SORT ORDER BY. This converts the COST to 0.
We can see that Oracle*did not do any I/O* because the Statistics show 0 block gets (consistent gets).
Let me say that again : Oracle did NOT do any I/O inspite of the supposed Index Range Scan and Table Access by Index Rowid.
.
.
.
If it finds a predicate that causes a "short-circuit" --- one that prevents rows from being returned by the query --- it can evaluate the COST of the query to 0 (zero). Normally, we would never expect a COST to zero --- even where we know zero rows will be returned by the query, Oracle may have to undertake some I/O (a Full Table Scan or an Index Range Scan in the simplest cases) and some CPU cycles to verify the resulting blocks for the expected result.
Here I show a simple example of a short-circuit.
First, I build a Table and an Index, with statistics :
SQL> create table my_tableSo we know that all the statistics (Row Count in the table and number of Distinct values in the Index) are consistent. I then run a simple query :
2 as select * from dba_objects;
Table created.
SQL> select count(*) from my_table;
COUNT(*)
----------
76609
SQL> create index my_table_ndx on my_table(owner);
Index created.
SQL> exec dbms_stats.gather_table_stats('','MY_TABLE',estimate_percent=>100,cascade=>TRUE);
PL/SQL procedure successfully completed.
SQL>
SQL> select num_rows, sample_size
2 from user_tab_statistics
3 where table_name = 'MY_TABLE';
NUM_ROWS SAMPLE_SIZE
---------- -----------
76609 76609
SQL> select num_distinct
2 from user_tab_col_statistics
3 where table_name = 'MY_TABLE'
4 and column_name = 'OWNER';
NUM_DISTINCT
------------
44
SQL> select leaf_blocks, distinct_keys, num_rows, sample_size
2 from user_ind_statistics
3 where table_name = 'MY_TABLE'
4 and index_name = 'MY_TABLE_NDX';
LEAF_BLOCKS DISTINCT_KEYS NUM_ROWS SAMPLE_SIZE
----------- ------------- ---------- -----------
183 44 76609 76609
SQL>
SQL> col object_name format a30Oracle has estimated that it will fetch 1741 rowids from the index in the plan step id=3 and then fetch 1,741 rows and also apply a filter for object_name in plan step id=2 to reduce the row count to 24.
SQL> col object_type format a18
SQL> set autotrace on
SQL> select object_name, object_type
2 from my_table
3 where owner = 'HEMANT'
4 and object_name like 'S%'
5 order by 1,2;
OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------
SOURCE_PK INDEX
SOURCE_PK1 INDEX
SOURCE_TABLE TABLE
Execution Plan
----------------------------------------------------------
Plan hash value: 1587485563
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 24 | 984 | 53 (2)| 00:00:01 |
| 1 | SORT ORDER BY | | 24 | 984 | 53 (2)| 00:00:01 |
|* 2 | TABLE ACCESS BY INDEX ROWID| MY_TABLE | 24 | 984 | 52 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | MY_TABLE_NDX | 1741 | | 5 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_NAME" LIKE 'S%')
3 - access("OWNER"='HEMANT')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
579 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
3 rows processed
SQL>
The actual resulting row count is 3. (The 24 row count is an estimate)
The estimated cost of the Index Range Scan is 5, the estimated cost of the Table Access (1741 rowids) and Filter is 47 (52-5) and the estimated cost of the Sort (for 24 rows) is 1 (53-52), resulting in a total cost of 53.
Oracle read a total of 5 blocks (consistent gets).
What happens if I add an "AND 1=2" predicate (which is always FALSE) ?
SQL> select object_name, object_typeThe query returns no rows (i.e. zero rows).
2 from my_table
3 where owner = 'HEMANT'
4 and object_name like 'S%'
5 and 1=2
6 order by 1,2;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 422461895
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 41 | 0 (0)| |
| 1 | SORT ORDER BY | | 1 | 41 | | |
|* 2 | FILTER | | | | | |
|* 3 | TABLE ACCESS BY INDEX ROWID| MY_TABLE | 24 | 984 | 52 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | MY_TABLE_NDX | 1741 | | 5 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(NULL IS NOT NULL)
3 - filter("OBJECT_NAME" LIKE 'S%')
4 - access("OWNER"='HEMANT')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
356 bytes sent via SQL*Net to client
408 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed
SQL>
But notice the "filter (NULL IS NOT NULL)". This is the short-circuit that Oracle adds. This will always evaluate to FALSE. This is Oracle's way of translating the "1=2" condition that I added to the query.
Although there is a supposed COST of 5 for the 1,741 rowids from the Index Range Scan and a supposed COST of 47 (52-5) for the Table Access of the 1,741 rowids and filtering to 24 rows for "OBJECT_NAME LIKE 'S%'), Oracle then proceeds to add the NULL IS NOT NULL FILTER before the SORT ORDER BY. This converts the COST to 0.
We can see that Oracle*did not do any I/O* because the Statistics show 0 block gets (consistent gets).
Let me say that again : Oracle did NOT do any I/O inspite of the supposed Index Range Scan and Table Access by Index Rowid.
.
.
.
Categories: DBA Blogs