Hemant K Chitale
RMAN Consistent ("COLD" ?) Backup and Restore
The RMAN documentation differentiates between "inconsistent" and "consistent" backups on the basis of whether the database is OPEN or not (respectively) during the Backup.
The nomenclature "inconsistent backup" makes me nervous. Why not call it the good old "HOT" Backup ?
To do a consistent" backup with RMAN, the database must be mounted as RMAN needs to access and update the controlfiles. With an OS scripted backup pre-RMAN, the database was truly "COLD" -- there would be no Oracle processes running.
RMAN does not backup the Online Redo Log files. With an OS scripted backup, you had the option -- you could choose to include these files in your backup if you were careful about how you planned to use Cold Backups for Roll-Forward recoveries with ArchiveLog.
Since RMAN does not backup the Online Redo Log files, you must, perforce, OPEN RESETLOGS on a Restore. With a scripted backup, if you also included your Online Redo Log files in your backup and restore (provided that you did not plan to apply any ArchiveLogs), you could simply STARTUP the database and continue LogSequenceNumbers again. (Of course, you might be duplicating LogSequenceNumbers if the database had been active in ArchiveLog mode since the backup, so you have to be careful to distinguish the two "streams" of ArchiveLogs).
Here below is the simplest "consistent" Backup and Restore using RMAN :
The OPEN RESETLOGS is necessary because RMAN does not backup and restore
Online Redo Logs.
The nomenclature "inconsistent backup" makes me nervous. Why not call it the good old "HOT" Backup ?
To do a consistent" backup with RMAN, the database must be mounted as RMAN needs to access and update the controlfiles. With an OS scripted backup pre-RMAN, the database was truly "COLD" -- there would be no Oracle processes running.
RMAN does not backup the Online Redo Log files. With an OS scripted backup, you had the option -- you could choose to include these files in your backup if you were careful about how you planned to use Cold Backups for Roll-Forward recoveries with ArchiveLog.
Since RMAN does not backup the Online Redo Log files, you must, perforce, OPEN RESETLOGS on a Restore. With a scripted backup, if you also included your Online Redo Log files in your backup and restore (provided that you did not plan to apply any ArchiveLogs), you could simply STARTUP the database and continue LogSequenceNumbers again. (Of course, you might be duplicating LogSequenceNumbers if the database had been active in ArchiveLog mode since the backup, so you have to be careful to distinguish the two "streams" of ArchiveLogs).
Here below is the simplest "consistent" Backup and Restore using RMAN :
C:\>rman
Recovery Manager: Release 10.2.0.3.0 - Production on Thu May 8 22:20:17 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
RMAN> connect target /
connected to target database (not started)
RMAN> startup mount
Oracle instance started
database mounted
Total System Global Area 536870912 bytes
Fixed Size 1291652 bytes
Variable Size 297798268 bytes
Database Buffers 234881024 bytes
Redo Buffers 2899968 bytes
RMAN> backup database;
Starting backup at 08-MAY-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=58 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=C:\OR10G2DB\SYSTEM01.DBF
input datafile fno=00003 name=C:\OR10G2DB\SYSAUX01.DBF
input datafile fno=00002 name=F:\OR10G2DB\TEST_TBS_01.DBF
input datafile fno=00004 name=C:\OR10G2DB\USERS01.DBF
input datafile fno=00005 name=C:\OR10G2DB\EXAMPLE01.DBF
input datafile fno=00007 name=C:\OR10G2DB\UNDO.DBF
channel ORA_DISK_1: starting piece 1 at 08-MAY-08
channel ORA_DISK_1: finished piece 1 at 08-MAY-08
piece handle=C:\OR10G2DB_FLASH\OR10G2DB\BACKUPSET\2008_05_08\O1_MF_NNNDF_TAG200
0508T222041_42631X6C_.BKP tag=TAG20080508T222041 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:47
Finished backup at 08-MAY-08
Starting Control File and SPFILE Autobackup at 08-MAY-08
piece handle=C:\OR10G2DB_FLASH\OR10G2DB\AUTOBACKUP\2008_05_08\O1_MF_S_654214767
426358M1_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 08-MAY-08
RMAN>
RMAN> shutdown
database dismounted
Oracle instance shut down
RMAN>
******************* BACKUP COMPLETED *****************
******************************************************
========= database files deleted ====================
++++++++++++++++++++++++++++++++++++++++++++++++++++++
======================================================
C:\>rman target /
Recovery Manager: Release 10.2.0.3.0 - Production on Thu May 8 22:32:07 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
RMAN> startup nomount
Oracle instance started
Total System Global Area 536870912 bytes
Fixed Size 1291652 bytes
Variable Size 301992572 bytes
Database Buffers 230686720 bytes
Redo Buffers 2899968 bytes
RMAN>
RMAN> restore controlfile from autobackup;
Starting restore at 08-MAY-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=57 devtype=DISK
recovery area destination: \OR10G2DB_FLASH
database name (or database unique name) used for search: OR10G2DB
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: C:\OR10G2DB_FLASH\OR10G2DB\AUTOBACKUP\2008
_05_08\O1_MF_S_654214767_426358M1_.BKP
channel ORA_DISK_1: control file restore from autobackup complete
output filename=C:\OR10G2DB\CONTROL01.CTL
output filename=C:\OR10G2DB\CONTROL02.CTL
output filename=C:\OR10G2DB\CONTROL03.CTL
Finished restore at 08-MAY-08
RMAN>
RMAN> restore database;
Starting restore at 08-MAY-08
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 05/08/2008 22:33:28
ORA-01507: database not mounted
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN>
RMAN> restore database;
Starting restore at 08-MAY-08
Starting implicit crosscheck backup at 08-MAY-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=57 devtype=DISK
Crosschecked 45 objects
Finished implicit crosscheck backup at 08-MAY-08
Starting implicit crosscheck copy at 08-MAY-08
using channel ORA_DISK_1
Finished implicit crosscheck copy at 08-MAY-08
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: C:\OR10G2DB_FLASH\OR10G2DB\AUTOBACKUP\2008_05_08\O1_MF_S_654214767_42
6358M1_.BKP
using channel ORA_DISK_1
skipping datafile 2; already restored to file F:\OR10G2DB\TEST_TBS_01.DBF
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:\OR10G2DB\SYSTEM01.DBF
restoring datafile 00003 to C:\OR10G2DB\SYSAUX01.DBF
restoring datafile 00004 to C:\OR10G2DB\USERS01.DBF
restoring datafile 00005 to C:\OR10G2DB\EXAMPLE01.DBF
restoring datafile 00007 to C:\OR10G2DB\UNDO.DBF
channel ORA_DISK_1: reading from backup piece C:\OR10G2DB_FLASH\OR10G2DB\BACKUPS
ET\2008_05_08\O1_MF_NNNDF_TAG20080508T222041_42631X6C_.BKP
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\OR10G2DB_FLASH\OR10G2DB\BACKUPSET\2008_05_08\O1_MF_NNNDF_TAG2008
0508T222041_42631X6C_.BKP tag=TAG20080508T222041
channel ORA_DISK_1: restore complete, elapsed time: 00:02:05
Finished restore at 08-MAY-08
RMAN>
RMAN> alter database open;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 05/08/2008 22:36:48
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
RMAN>
RMAN> alter database open resetlogs;
database opened
RMAN>
The OPEN RESETLOGS is necessary because RMAN does not backup and restore
Online Redo Logs.
Categories: DBA Blogs
DBAs working long hours
A forums thread on why and how DBAs should handle the need to work long hours.
Categories: DBA Blogs
One Thing Leads to Another ....
I had written my post on Row Sizes and SORTs following the same forums posting that Jonathan Lewis used to build his test case in his blog post called "Manual Optimisation".
On reading the first version of Manual Optimisation (hey, did you notice that he is British ?!), I was then going to update my own post and reference his method. Quite cool and, at the same time, very complicated. However, I held back on that (and wrote another post on something else).
A few days later, I come back to his blog to read up on new comments, and I find a reference to Tom Kyte's response. Yes, I agree with Tom Kyte and do feel that depending on what could be a side-effect of behaviour isn't strictly usable in most scenarios -- but, maybe, Jonathan Lewis has found an exception. (Will I use it ? Probably not , I wouldn't be able to explain it to others !).
So, all things going around, what is THIS posting about ? Well actually, I found the example of the Employee Names and Telephone Numbers lookup that Tom Kyte has quoted as "Optimize to Your Most Frequently Asked Questions" quite intriguing. I've seen a similar application in Lotus Notes and wondered how it could be done in Oracle. Now, I know how it can be done !
So, from a forum posting, to my blog, to Jonathan Lewis's posting to Tom Kyte's response and to an example which gives me an insight into a solution for problem not strictly related to the first one (although there is a good link between the two -- the fact that the rowsize can be an issue in fetching data !).
On reading the first version of Manual Optimisation (hey, did you notice that he is British ?!), I was then going to update my own post and reference his method. Quite cool and, at the same time, very complicated. However, I held back on that (and wrote another post on something else).
A few days later, I come back to his blog to read up on new comments, and I find a reference to Tom Kyte's response. Yes, I agree with Tom Kyte and do feel that depending on what could be a side-effect of behaviour isn't strictly usable in most scenarios -- but, maybe, Jonathan Lewis has found an exception. (Will I use it ? Probably not , I wouldn't be able to explain it to others !).
So, all things going around, what is THIS posting about ? Well actually, I found the example of the Employee Names and Telephone Numbers lookup that Tom Kyte has quoted as "Optimize to Your Most Frequently Asked Questions" quite intriguing. I've seen a similar application in Lotus Notes and wondered how it could be done in Oracle. Now, I know how it can be done !
So, from a forum posting, to my blog, to Jonathan Lewis's posting to Tom Kyte's response and to an example which gives me an insight into a solution for problem not strictly related to the first one (although there is a good link between the two -- the fact that the rowsize can be an issue in fetching data !).
Categories: DBA Blogs
TEMPORARY Segments in Data/Index Tablespaces
Some misconceptions about Temporary Segments in a forums thread led me to run this demo to show how and when 'TEMPORARY' segments may be created in a "normal" tablespace -- one other than the TEMP tablespace.
I ran some CREATE, INSERT, REBUILD and MOVE commands from one session and monitored segments in that tablespace from another session concurrently. The timestamps in the outputs show how and when the "TEMPORARY" segments are created by Oracle. {Note : Take particular attention when using Parallel operations -- eg CREATE or REBUILD operations using Parallelism -- there are at least as many
Temporary Segments created as the number of Parallel Slaves as each slave creates it's own Segment (and extents) and these are merged by the Query Co-ordinator on
completion.}
This is my test session with the CREATE, INSERT, REBUILD and MOVE operations :
And this is what I see in another session monitoring the first :
Observations :
1. Temporary Segment names are named as 'filenumber.headerblocknumber'
2. The CREATE TABLE statement creates a TEMPORARY Segment "2.11"
which, on successful creation, is converted to a TABLE Segment.
(you can see how the filenumber and headerblocknumber are used)
3. When *extending* an already created Table (adding rows to it),
the new extents are added to the same Table segment -- they are
*NOT* a TEMPORARY Segment.
4. A CREATE INDEX also begins with a TEMPORARY Segment. However,
when creating an Index, you would see 2 such segments -- on in the TEMP
Tablespace where the SORT operations occur, and one in the _target_
Tablespace where the Index is supposed to be created. In this case, the
Index Segment starts with Block 6507.
5. An ALTER INDEX REBUILD creates a new TEMPORARY Segment
(Header Block 9227 in my case) and drops the old Index Segment only
after successful completion -- thus you see both the Block 6507 "TEST_TABLE_NDX_1"
and Block 9227 TEMPORARY Segment while the REBUILD is running
after which the Block 6507 is dropped.
6. An ALTER TABLE MOVE also creates it's own TEMPORARY Segment
(you can see that it reuses the Extents beginning with the one atBlock 6507
that were released by the "dropped" Index segment). On successful completion,
the "old" Table Segment (Block 11) is dropped and replaced by the new Segment
(Block 6507).
7. Another ALTER INDEX REBUILD again behaves in the same manner
(reusing the extents, beginning with the one at Block 11).
Why does Oracle use this manner of TEMPORARY Segments for CREATE
and REBUILD/MOVE ?
Consider what happens if the Tablespace doesn't have enough space.
Say the tablespace is a single datafile of 100MB and you begin running a
CREATE TABLE ... AS SELECT .... while will result in a final table size of
200MB (of, maybe, 1MB extents). When the CREATE begins, Oracle
does *not* know how large the target table will be. It begins with 1MB and
grows to 100MB and then fails to allocate another extent. Since this is
a CREATE table, it cannot allow an incomplete table of 100MB to continue
to exist. Oracle simply drops the TEMPORARY Segment it was using to
hold the table rows and returns a failure on the CREATE TABLE -- the
Table does NOT get created.
Exactly the same thing happens with a CREATE INDEX.
How about the REBUILD and MOVE ?
You might have an Index of 100MB Segment Size. However, you may have
deleted, say 90% of the rows from the Table. When you rebuild the Index,
the new Index might be only 10MB. Till such time as the new Index creation
completes, Oracle cannot drop the old index --- even if there is enough free
space for the 10MB segment to co-exist with the 100MB segment. What
would happen if the server were to fail or the database instance were to crash
because of some other bug while the REBUILD was running ? Had Oracle
dropped the original segment, you would end up with *NO* Index. Oracle
cannot allow that so it doesn't drop the original segment till the new one
is successfully built. The "switching" of the Segments is an "atomic" operation.
What if you hadn't deleted any rows from the table and yet were, simply,
rebuilding the Index. The new Index segment might require 100MB space.
However, when Oracle begins the REBUILD, it doesn't know precisely how
large the new segment *will* really be (it can't rely on the NUM_ROWS
statistics as those statistics might be outdated !). So, it has to first create the
new Index segment (and fail it if the Tablespace doesn't have the additional
100MB free space) before it can "drop" the old segment.
The same manner of behaviour has to occur with an ALTER TABLE ... MOVE.
Oracle cannot be sure, in advance, of how large the new Table segment will finally be.
It has to first create the Table segment successfully before it can "drop" the
old segment. Also, if any other server/instance failure occurred midway, this
method ensures that the "old" Table is still available 100% intact.
Note : Indexes get invalidated on a Table MOVE because the MOVE changes
ROWIDs causing the Indexes to point to incorrect rows.
Well, then, what about INSERT operations ? When you INSERT into a Table
that has already been created (even it was created as an empty table !), the
Table Segment already exists with 1 or more extents. The INSERT operation
simply needs to allocate Extents as necessary to "grow" the table. If there is
insuficient space mid-way, the INSERT is Rolled Back by running an UNDO
on it (the UNDO is "delete .. where rowid = ..." for each of the rows).
The extents that got allocated and the blocks that got "formatted" during the
INSERT remain allocated and formatted but are "cleared" of the rows.
Thus, this can mean that if a Table starts with 100 Extents (and 20,000 rows)
and undergoes an 80,000 row Insert that should have grown to 400 Extents
but fails after growing to 250 Extents (because the Tablespace datafiles are full),
then at the end of the Rollback the ROW COUNT reverts to the original count of
20,000 rows but the Table Segment's size *remains* at 250 Extents ! Those
Extents do not get deallocated. REMEMBER THAT the next time you attempt
a large INSERT !
I ran some CREATE, INSERT, REBUILD and MOVE commands from one session and monitored segments in that tablespace from another session concurrently. The timestamps in the outputs show how and when the "TEMPORARY" segments are created by Oracle. {Note : Take particular attention when using Parallel operations -- eg CREATE or REBUILD operations using Parallelism -- there are at least as many
Temporary Segments created as the number of Parallel Slaves as each slave creates it's own Segment (and extents) and these are merged by the Query Co-ordinator on
completion.}
This is my test session with the CREATE, INSERT, REBUILD and MOVE operations :
18:54:23 SQL>
18:54:23 SQL>
18:54:23 SQL> REM To demonstrate how and when Temporary segments NOT in the TEMP tablespace might get created
18:54:23 SQL>
18:54:23 SQL> rem Deliberately create the TBS with small 64K extents so that the operation is slow
18:54:23 SQL> rem and we can query and find the Temporary segments while existant for a short while
18:54:23 SQL>
18:54:23 SQL> create tablespace test_tbs
18:54:23 2 datafile 'F:\OR10G2DB\test_tbs_01.dbf' SIZE 200M autoextend on next 10M maxsize 2000M
18:54:23 3 extent management local uniform size 64K segment space management auto;
Tablespace created.
18:54:34 SQL>
18:54:34 SQL> alter session set workarea_size_policy='MANUAL';
Session altered.
18:54:34 SQL> alter session set sort_area_size=65536;
Session altered.
18:54:34 SQL>
18:54:34 SQL> pause press ENTER to proceed
press ENTER to proceed
18:54:43 SQL>
18:54:43 SQL> rem Create a largish table. Do NOT use NOLOGGING or APPEND -- just so that the operation is slower
18:54:43 SQL> pause press ENTER to proceed
press ENTER to proceed
18:54:45 SQL> create table test_table tablespace test_tbs as select * from dba_objects union all select * from dba_objects union all select * from dba_objects;
Table created.
18:54:50 SQL>
18:54:50 SQL>
18:54:50 SQL> rem Enlarge the table
18:54:50 SQL> pause press ENTER to proceed -- see if you can find TEMPORARY segments while the CREATE is running
press ENTER to proceed -- see if you can find TEMPORARY segments while the CREATE is running
18:54:54 SQL> insert into test_table select * from test_table union all select * from test_table;
310044 rows created.
18:55:06 SQL> pause press ENTER to proceed -- see if you can find TEMPORARY segments while the CREATE is running
press ENTER to proceed -- see if you can find TEMPORARY segments while the CREATE is running
18:55:08 SQL> create index test_table_ndx_1 on test_table(owner,object_name) tablespace test_tbs;
Index created.
18:55:47 SQL>
18:55:47 SQL> rem rebuild index and table
18:55:47 SQL> pause press ENTER to proceed -- see if you can find TEMPORARY segments while the REBUILD is running
press ENTER to proceed -- see if you can find TEMPORARY segments while the REBUILD is running
18:55:55 SQL> alter index test_table_ndx_1 rebuild;
Index altered.
18:56:11 SQL> pause press ENTER to proceed -- see if you can find TEMPORARY segments while the MOVE is running
press ENTER to proceed -- see if you can find TEMPORARY segments while the MOVE is running
18:56:16 SQL> alter table test_table move;
Table altered.
18:56:37 SQL> pause press ENTER to proceed -- see if you can find TEMPORARY segments while the REBUILD is running
press ENTER to proceed -- see if you can find TEMPORARY segments while the REBUILD is running
18:56:41 SQL> alter index test_table_ndx_1 rebuild;
Index altered.
18:57:23 SQL>
18:57:23 SQL>
And this is what I see in another session monitoring the first :
18:54:29 SQL> /
no rows selected
18:54:29 SQL> l
1 select segment_name,segment_type,header_file,header_block,extents,bytes/1024/1024 sz
2 from dba_segments
3 where segment_type = 'TEMPORARY'
4 and tablespace_name = 'TEST_TBS'
5 union
6 select segment_name,segment_type,header_file,header_block,extents,bytes/1024/1024 sz
7 from dba_segments
8 where segment_name in ('TEST_TABLE','TEST_TABLE_NDX_1')
9 and tablespace_name = 'TEST_TBS'
10* order by segment_name, header_file
18:54:31 SQL> /
no rows selected
18:54:32 SQL> /
no rows selected
18:54:41 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
2.11 TEMPORARY 2 11 143 8.94
18:54:48 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
TEST_TABLE TABLE 2 11 272 17.00
18:54:50 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
TEST_TABLE TABLE 2 11 375 23.44
18:54:56 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
TEST_TABLE TABLE 2 11 375 23.44
18:54:59 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
TEST_TABLE TABLE 2 11 714 44.63
18:55:05 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
TEST_TABLE TABLE 2 11 812 50.75
18:55:11 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
TEST_TABLE TABLE 2 11 812 50.75
18:55:13 SQL>
18:55:17 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
TEST_TABLE TABLE 2 11 812 50.75
18:55:17 SQL>
18:55:20 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
TEST_TABLE TABLE 2 11 812 50.75
18:55:21 SQL>
18:55:23 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
TEST_TABLE TABLE 2 11 812 50.75
18:55:24 SQL>
18:55:27 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
TEST_TABLE TABLE 2 11 812 50.75
18:55:28 SQL>
18:55:30 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
TEST_TABLE TABLE 2 11 812 50.75
18:55:31 SQL>
18:55:34 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
2.6507 TEMPORARY 2 6507 41 2.56
TEST_TABLE TABLE 2 11 812 50.75
18:55:35 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
2.6507 TEMPORARY 2 6507 114 7.13
TEST_TABLE TABLE 2 11 812 50.75
18:55:37 SQL>
18:55:44 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
2.6507 TEMPORARY 2 6507 271 16.94
TEST_TABLE TABLE 2 11 812 50.75
18:55:45 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
TEST_TABLE TABLE 2 11 812 50.75
TEST_TABLE_NDX_1 INDEX 2 6507 340 21.25
18:55:48 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
2.9227 TEMPORARY 2 9227 2 .13
TEST_TABLE TABLE 2 11 812 50.75
TEST_TABLE_NDX_1 INDEX 2 6507 340 21.25
18:55:57 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
2.9227 TEMPORARY 2 9227 62 3.88
TEST_TABLE TABLE 2 11 812 50.75
TEST_TABLE_NDX_1 INDEX 2 6507 340 21.25
18:56:00 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
2.9227 TEMPORARY 2 9227 259 16.19
TEST_TABLE TABLE 2 11 812 50.75
TEST_TABLE_NDX_1 INDEX 2 6507 340 21.25
18:56:05 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
2.9227 TEMPORARY 2 9227 278 17.38
TEST_TABLE TABLE 2 11 812 50.75
TEST_TABLE_NDX_1 INDEX 2 6507 340 21.25
18:56:08 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
TEST_TABLE TABLE 2 11 812 50.75
TEST_TABLE_NDX_1 INDEX 2 9227 340 21.25
18:56:12 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
2.6507 TEMPORARY 2 6507 175 10.94
TEST_TABLE TABLE 2 11 812 50.75
TEST_TABLE_NDX_1 INDEX 2 9227 340 21.25
18:56:19 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
2.6507 TEMPORARY 2 6507 407 25.44
TEST_TABLE TABLE 2 11 812 50.75
TEST_TABLE_NDX_1 INDEX 2 9227 340 21.25
18:56:25 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
2.6507 TEMPORARY 2 6507 639 39.94
TEST_TABLE TABLE 2 11 812 50.75
TEST_TABLE_NDX_1 INDEX 2 9227 340 21.25
18:56:30 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
TEST_TABLE TABLE 2 6507 814 50.88
TEST_TABLE_NDX_1 INDEX 2 9227 340 21.25
18:56:39 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
TEST_TABLE TABLE 2 6507 814 50.88
TEST_TABLE_NDX_1 INDEX 2 9227 340 21.25
18:56:44 SQL>
18:56:51 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
TEST_TABLE TABLE 2 6507 814 50.88
TEST_TABLE_NDX_1 INDEX 2 9227 340 21.25
18:56:52 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
TEST_TABLE TABLE 2 6507 814 50.88
TEST_TABLE_NDX_1 INDEX 2 9227 340 21.25
18:56:57 SQL>
18:57:02 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
TEST_TABLE TABLE 2 6507 814 50.88
TEST_TABLE_NDX_1 INDEX 2 9227 340 21.25
18:57:02 SQL>
18:57:07 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
2.11 TEMPORARY 2 11 56 3.50
TEST_TABLE TABLE 2 6507 814 50.88
TEST_TABLE_NDX_1 INDEX 2 9227 340 21.25
18:57:08 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
2.11 TEMPORARY 2 11 125 7.81
TEST_TABLE TABLE 2 6507 814 50.88
TEST_TABLE_NDX_1 INDEX 2 9227 340 21.25
18:57:12 SQL>
18:57:19 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
2.11 TEMPORARY 2 11 290 18.13
TEST_TABLE TABLE 2 6507 814 50.88
TEST_TABLE_NDX_1 INDEX 2 9227 340 21.25
18:57:20 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
TEST_TABLE TABLE 2 6507 814 50.88
TEST_TABLE_NDX_1 INDEX 2 11 340 21.25
18:57:25 SQL>
Observations :
1. Temporary Segment names are named as 'filenumber.headerblocknumber'
2. The CREATE TABLE statement creates a TEMPORARY Segment "2.11"
which, on successful creation, is converted to a TABLE Segment.
(you can see how the filenumber and headerblocknumber are used)
3. When *extending* an already created Table (adding rows to it),
the new extents are added to the same Table segment -- they are
*NOT* a TEMPORARY Segment.
4. A CREATE INDEX also begins with a TEMPORARY Segment. However,
when creating an Index, you would see 2 such segments -- on in the TEMP
Tablespace where the SORT operations occur, and one in the _target_
Tablespace where the Index is supposed to be created. In this case, the
Index Segment starts with Block 6507.
5. An ALTER INDEX REBUILD creates a new TEMPORARY Segment
(Header Block 9227 in my case) and drops the old Index Segment only
after successful completion -- thus you see both the Block 6507 "TEST_TABLE_NDX_1"
and Block 9227 TEMPORARY Segment while the REBUILD is running
after which the Block 6507 is dropped.
6. An ALTER TABLE MOVE also creates it's own TEMPORARY Segment
(you can see that it reuses the Extents beginning with the one atBlock 6507
that were released by the "dropped" Index segment). On successful completion,
the "old" Table Segment (Block 11) is dropped and replaced by the new Segment
(Block 6507).
7. Another ALTER INDEX REBUILD again behaves in the same manner
(reusing the extents, beginning with the one at Block 11).
Why does Oracle use this manner of TEMPORARY Segments for CREATE
and REBUILD/MOVE ?
Consider what happens if the Tablespace doesn't have enough space.
Say the tablespace is a single datafile of 100MB and you begin running a
CREATE TABLE ... AS SELECT .... while will result in a final table size of
200MB (of, maybe, 1MB extents). When the CREATE begins, Oracle
does *not* know how large the target table will be. It begins with 1MB and
grows to 100MB and then fails to allocate another extent. Since this is
a CREATE table, it cannot allow an incomplete table of 100MB to continue
to exist. Oracle simply drops the TEMPORARY Segment it was using to
hold the table rows and returns a failure on the CREATE TABLE -- the
Table does NOT get created.
Exactly the same thing happens with a CREATE INDEX.
How about the REBUILD and MOVE ?
You might have an Index of 100MB Segment Size. However, you may have
deleted, say 90% of the rows from the Table. When you rebuild the Index,
the new Index might be only 10MB. Till such time as the new Index creation
completes, Oracle cannot drop the old index --- even if there is enough free
space for the 10MB segment to co-exist with the 100MB segment. What
would happen if the server were to fail or the database instance were to crash
because of some other bug while the REBUILD was running ? Had Oracle
dropped the original segment, you would end up with *NO* Index. Oracle
cannot allow that so it doesn't drop the original segment till the new one
is successfully built. The "switching" of the Segments is an "atomic" operation.
What if you hadn't deleted any rows from the table and yet were, simply,
rebuilding the Index. The new Index segment might require 100MB space.
However, when Oracle begins the REBUILD, it doesn't know precisely how
large the new segment *will* really be (it can't rely on the NUM_ROWS
statistics as those statistics might be outdated !). So, it has to first create the
new Index segment (and fail it if the Tablespace doesn't have the additional
100MB free space) before it can "drop" the old segment.
The same manner of behaviour has to occur with an ALTER TABLE ... MOVE.
Oracle cannot be sure, in advance, of how large the new Table segment will finally be.
It has to first create the Table segment successfully before it can "drop" the
old segment. Also, if any other server/instance failure occurred midway, this
method ensures that the "old" Table is still available 100% intact.
Note : Indexes get invalidated on a Table MOVE because the MOVE changes
ROWIDs causing the Indexes to point to incorrect rows.
Well, then, what about INSERT operations ? When you INSERT into a Table
that has already been created (even it was created as an empty table !), the
Table Segment already exists with 1 or more extents. The INSERT operation
simply needs to allocate Extents as necessary to "grow" the table. If there is
insuficient space mid-way, the INSERT is Rolled Back by running an UNDO
on it (the UNDO is "delete .. where rowid = ..." for each of the rows).
The extents that got allocated and the blocks that got "formatted" during the
INSERT remain allocated and formatted but are "cleared" of the rows.
Thus, this can mean that if a Table starts with 100 Extents (and 20,000 rows)
and undergoes an 80,000 row Insert that should have grown to 400 Extents
but fails after growing to 250 Extents (because the Tablespace datafiles are full),
then at the end of the Rollback the ROW COUNT reverts to the original count of
20,000 rows but the Table Segment's size *remains* at 250 Extents ! Those
Extents do not get deallocated. REMEMBER THAT the next time you attempt
a large INSERT !
Categories: DBA Blogs
Row Sizes and Sort Operations
The Row Size can have a signicant impact on a sort operation. A SORT operation would be required not only when an explicit SORT BY is specified but also for operations like GROUP BY (10g has introduced "Group By Hash" operations to speed up Grouping without doing explicit sorts, but there are some bugs with this feature).
Here is a test case where I create a "TEST_SORT_TABLE" and index it on "ID_COLUMN" and "ITEM_TYPE".
In the first query, I query for only the indexed columns and request an ORDER BY on
these columns. Oracle is able to do an "Index Full Scan" (which retrieves the values *in order*) for all 155thousand rows at a "COST" of 873.
In the second query, I request 3 columns (ie, including one additional column not in the index). Since Oracle has to go to the Table to fetch the third column, it decides to do a Full Table Scan as it has to read ALL the rows from the table. It avoids using the Index. That is sensible. However, note that the total "Bytes" fetched has gone up from 4541K to 5904K because of the additional column. What is worse is that the SORT operation requires 15MB of TempSpace and increases the total "COST". Apparently, the "COST" of the SORT operation is 1,578 (2429-851).
One additional column ("ITEM_TYPE" varchar2(19) -- doesn't seem very large ?) has increased the "COST" of my query from 873 to 2,429
If we compare the third and fourth queries the TempSpace for the SORT operation is now 55MB and the "COST" goes up from 852 to 6,597. Why such a big difference ? Although my Sort is supposed to be on only 2 columns, since Oracle fetches the full row, it has to effectively "sort" all the columns. It might have been better if we could pre-sort from the Index and, then, go to the Table, to fetch the rows in the sorted order. Would it ? Actually , no. Even if we use the Index to pre-sort and get ROWIDs in sorted order, we'd be introducing multiple read calls to then fetch the rows from the table -- a single block read call for each row. Clustering of the table might help reducing physical reads but not 'consistent gets'. I leave that experiement for you to try.
So what we get is :
The next time you write a query that does a "SELECT *" stop and think about how the
Row Size affects the "COST". If you add an "ORDER BY" stop again and think twice
as hard.
Here is a test case where I create a "TEST_SORT_TABLE" and index it on "ID_COLUMN" and "ITEM_TYPE".
-- create the source table with slightly large row lengths
drop table test_sort_table;
create table test_sort_table (id_column number not null, item_name varchar2(36) not null, item_type varchar2(19) not null, owner_name varchar2(30) not null, other_cols varchar2(128)) ;
alter table test_sort_table nologging;
insert /*+ APPEND */ into test_sort_table
2 select object_id, substr(object_name,1,32), object_type, owner, rpad('a',124,'f')
3 from dba_objects
4 where object_id is not null
5 order by substr(object_type,1,6)substr(object_name,4,9) ;
commit;
insert /*+ APPEND */ into test_sort_table
2 select * from test_sort_table
3 union all select * from test_sort_table
4 order by substr(item_name,1,8)substr(owner_name,2,4);
commit;
create index test_sort_table_ndx_1 on test_sort_table (id_column , item_name);
exec dbms_stats.gather_table_Stats('','TEST_SORT_TABLE',method_opt=>'FOR ALL COLUMNS SIZE 1',estimate_percent=>100,cascade=>TRUE);
-- get a "feel" for the size of the table (row count, row length, block count)
select count(*) from test_sort_table;
COUNT(*)
----------
155025
select num_rows, avg_row_len, blocks from user_tables where table_name = 'TEST_SORT_TABLE';
NUM_ROWS AVG_ROW_LEN BLOCKS
---------- ----------- ----------
155025 168 3835
select num_rows, blevel, leaf_blocks from user_indexes where index_name = 'TEST_SORT_TABLE_NDX_1';
NUM_ROWS BLEVEL LEAF_BLOCKS
---------- ---------- -----------
155025 2 865
explain plan for
2 select /* only the index columns */ id_column, item_name from test_sort_table order by id_column , item_name ;
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2301815179
------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
------------------------------------------------------------------------------------------
0 SELECT STATEMENT 155K 4541K 873 (1) 00:00:11
1 INDEX FULL SCAN TEST_SORT_TABLE_NDX_1 155K 4541K 873 (1) 00:00:11
------------------------------------------------------------------------------------------
explain plan for
2 select /* one additional column */ id_column, item_name, item_type from test_sort_table order by id_column , item_name ;
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1390622354
----------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes TempSpc Cost (%CPU) Time
----------------------------------------------------------------------------------------------
0 SELECT STATEMENT 155K 5904K 2429 (2) 00:00:30
1 SORT ORDER BY 155K 5904K 15M 2429 (2) 00:00:30
2 TABLE ACCESS FULL TEST_SORT_TABLE 155K 5904K 851 (2) 00:00:11
----------------------------------------------------------------------------------------------
explain plan for
2 select /* all columns, not ordered */ * from test_sort_table ;
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2991649059
-------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
-------------------------------------------------------------------------------------
0 SELECT STATEMENT 155K 24M 852 (2) 00:00:11
1 TABLE ACCESS FULL TEST_SORT_TABLE 155K 24M 852 (2) 00:00:11
-------------------------------------------------------------------------------------
explain plan for
2 select /* all columns, ordered */ * from test_sort_table order by id_column , item_name ;
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1390622354
----------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes TempSpc Cost (%CPU) Time
----------------------------------------------------------------------------------------------
0 SELECT STATEMENT 155K 24M 6597 (1) 00:01:20
1 SORT ORDER BY 155K 24M 55M 6597 (1) 00:01:20
2 TABLE ACCESS FULL TEST_SORT_TABLE 155K 24M 852 (2) 00:00:11
----------------------------------------------------------------------------------------------
In the first query, I query for only the indexed columns and request an ORDER BY on
these columns. Oracle is able to do an "Index Full Scan" (which retrieves the values *in order*) for all 155thousand rows at a "COST" of 873.
In the second query, I request 3 columns (ie, including one additional column not in the index). Since Oracle has to go to the Table to fetch the third column, it decides to do a Full Table Scan as it has to read ALL the rows from the table. It avoids using the Index. That is sensible. However, note that the total "Bytes" fetched has gone up from 4541K to 5904K because of the additional column. What is worse is that the SORT operation requires 15MB of TempSpace and increases the total "COST". Apparently, the "COST" of the SORT operation is 1,578 (2429-851).
One additional column ("ITEM_TYPE" varchar2(19) -- doesn't seem very large ?) has increased the "COST" of my query from 873 to 2,429
If we compare the third and fourth queries the TempSpace for the SORT operation is now 55MB and the "COST" goes up from 852 to 6,597. Why such a big difference ? Although my Sort is supposed to be on only 2 columns, since Oracle fetches the full row, it has to effectively "sort" all the columns. It might have been better if we could pre-sort from the Index and, then, go to the Table, to fetch the rows in the sorted order. Would it ? Actually , no. Even if we use the Index to pre-sort and get ROWIDs in sorted order, we'd be introducing multiple read calls to then fetch the rows from the table -- a single block read call for each row. Clustering of the table might help reducing physical reads but not 'consistent gets'. I leave that experiement for you to try.
So what we get is :
1. Fetch Only 2 Indexed Columns in Sorted Order : COST 873, TempSpace NIL
3. Fetch 2+1 Columns in Sorted Order : COST 2,429, TempSpace 15M
4. Fetch ALL Columns without a Sort : COST 852, TempSpace NIL
5. Fetch ALL columns and Sort on 2 columns : COST 6,597, TempSpace 55M
The next time you write a query that does a "SELECT *" stop and think about how the
Row Size affects the "COST". If you add an "ORDER BY" stop again and think twice
as hard.
Categories: DBA Blogs
Using SYS
Many DBAs regularly use SYS -- either in SQLPlus or in Enterprise Manager or in RMAN scripts. DBAs not familiar with the reasoning behind the "AS SYSDBA" might not even be aware that they connect as SYS.
I prefer to avoid using SYS as far as possible. There is no reason to use SYS to query data dictionary views and performance views if you can use DBSNMP or PERFSTAT or setup an account with the MONITORER role. Best would be to setup an account with the required privileges, reading from the privileges granted to DBSNMP and PERFSTAT.
Besides the fact that SYS is owner of the data dictionary, the "normal rules" of behaviour for monitoring and DBA accounts do not always apply to SYS. For example SYS does not get audited in the normal database audit defined by AUDIT_TRAIL. 9i introduced AUDIT_SYS_OPERATIONS to cater to the requirement to audit SYS but this does not provide the same structure as the DBA_AUDIT% views. Another difference in SYS is in read consistency. If you use SYS for Exports, please stop doing so. SYS may be used for Transportable Tablespaces in 8i/9i or under Oracle Support's direction but *not* for regular Exports. If you have any doubts, please read MetaLink Note#277237.1.
I prefer to avoid using SYS as far as possible. There is no reason to use SYS to query data dictionary views and performance views if you can use DBSNMP or PERFSTAT or setup an account with the MONITORER role. Best would be to setup an account with the required privileges, reading from the privileges granted to DBSNMP and PERFSTAT.
Besides the fact that SYS is owner of the data dictionary, the "normal rules" of behaviour for monitoring and DBA accounts do not always apply to SYS. For example SYS does not get audited in the normal database audit defined by AUDIT_TRAIL. 9i introduced AUDIT_SYS_OPERATIONS to cater to the requirement to audit SYS but this does not provide the same structure as the DBA_AUDIT% views. Another difference in SYS is in read consistency. If you use SYS for Exports, please stop doing so. SYS may be used for Transportable Tablespaces in 8i/9i or under Oracle Support's direction but *not* for regular Exports. If you have any doubts, please read MetaLink Note#277237.1.
Categories: DBA Blogs
Indexed column (unique or not) -- What if it is NULLable
One of the numerous facets of the Optimizer that Jonathan Lewis touches on is the fact that the Optimizer can and does make use of information about the possible presence of NULLs in an indexed column. Where we have a single column index (or even a multi-column index if none of the columns is NOT NULL), the Optimizer has to assume that there may be one or more NULLs present for that column. It doesn't matter if statistics have been gathered very recently (just before a query is to be optimized) ; it doesn't matter if we (the DBA and Analyst) *know* that there are no NULLs in the column.
The Optimizer must assume that a column not defined as NOT NULL may have NULLs.
From that assumption, it must also assume that not every row in the table is included in an index on that column as NULLs are not indexed. {See my other postings [1] and [2] on indexing NULLs}.
Whether a column is NULLable or is explicitly NOT NULL can help Oracle determine if an Index is to be used.
I've built a small case study here. Assume that we have a PARTS table with a Unique Key (but not defined as a Primary Key) on PART_ID. What would be the COST and CARD estimates for queries against PART_ID ? These become more important when a query on PARTS is a subquery in or part of a much larger, complex query.
(note : I've substituted "geq" and "leq" for ">=" and "<=" in the Predicate information section of the explain plan for the Range Scan queries so that the ">" and "<" don't get misinterpreted)
While the column (PART_ID) was not a NOT NULL, Oracle would choose a
FullTableScan for queries attempting to retrieve all the rows from the table.
The ORDER BY in the first query also adds to the COST.
Queries for a single value (20001) or a range of values (25000 to 25050) would be executed by Unique Scan and Range Scan respectively, as we expect.
What happens when the column is defined as a NOT NULL ? This, being a DDL,
invalidates parsed statements so the next execution of the same SQLs requires re-parsing.
This time, the Optimizer DOES decide to use the Index on PART_ID for the first two queries. We also see that where the query specifies an ORDER BY, Oracle does an Index Full Scan -- it "walks" the Leaf Blocks from the lowest value to the highest value, retrieving all the values in order. Oracle can now avoid having to do a SORT. Where an ORDER BY is not required, Oracle does a faster Index Fast Full Scan.
We have learnt three things :
1. If a column is guaranteed to contain no NULLs, it is best to explicitly specify it as a NOT NULL so that the Optimizer can take a "better informed" decision on the usability of an index on it.
2. When Oracle percieves a lower cost to an Index access, this can have a greater impact on the execution plan of a larger query which the table and column under consideration may only be a part of.
3. An ordered retrieval of rows can be executed by an Index Full Scan.
What I have not done in this test case is to force a fetch from table rows. The query can be satisfied by the Index alone, in this case. In the real world, too, we do come across and/or can create such indexes where a query or subquery is satisfied by an Index alone.
The Optimizer must assume that a column not defined as NOT NULL may have NULLs.
From that assumption, it must also assume that not every row in the table is included in an index on that column as NULLs are not indexed. {See my other postings [1] and [2] on indexing NULLs}.
Whether a column is NULLable or is explicitly NOT NULL can help Oracle determine if an Index is to be used.
I've built a small case study here. Assume that we have a PARTS table with a Unique Key (but not defined as a Primary Key) on PART_ID. What would be the COST and CARD estimates for queries against PART_ID ? These become more important when a query on PARTS is a subquery in or part of a much larger, complex query.
(note : I've substituted "geq" and "leq" for ">=" and "<=" in the Predicate information section of the explain plan for the Range Scan queries so that the ">" and "<" don't get misinterpreted)
SQL>
SQL> set pages600
SQL> set linesize132
SQL> set SQLPrompt ''
set feedback off
-- create the PARTS table
drop table parts;
create table parts (part_id number, part_name varchar2(128), part_descr varchar2(128)) nologging;
insert /*+ APPEND */ into parts
2 select object_id, object_name'_'rownum, owner'_'object_name'_'object_type
3 from dba_objects where object_id is not null
4 -- deliberately order by part_id to get good clustering ?
5 order by object_id ;
commit;
-- currently the column is NULLable, let's create a Unique Index
create unique index parts_uk on parts(part_id) nologging;
alter system flush buffer_cache;
-- force any delayed block cleanout and load blocks into sga. This isn't important to the Optimizer in 10g.
select /*+ FULL (p) */ count(*) from parts p;
COUNT(*)
----------
51750
select /*+ INDEX (p parts_uk) */ count(*) from parts p where part_id is not null;
COUNT(*)
----------
51750
exec dbms_stats.gather_table_stats('',tabname=>'PARTS',method_opt=>'FOR ALL COLUMNS SIZE 1',estimate_percent=>100,cascade=>TRUE);
select blocks, num_rows, avg_row_len from user_tables where table_name = 'PARTS';
BLOCKS NUM_ROWS AVG_ROW_LEN
---------- ---------- -----------
581 51750 73
select blevel, leaf_blocks, num_rows, clustering_factor from user_indexes where index_name = 'PARTS_UK';
BLEVEL LEAF_BLOCKS NUM_ROWS CLUSTERING_FACTOR
---------- ----------- ---------- -----------------
1 107 51750 563
-- begin analysis . What are Oracle's COST and CARD for this table ?
-- the impact is to be considered if these queries were subqueries in more detailed/complex queries
-- first case : All PART_IDs, ordered
explain plan for select part_id from parts order by part_id;
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3769467330
------------------------------------------------------------------------------------
Id Operation Name Rows Bytes TempSpc Cost (%CPU) Time
------------------------------------------------------------------------------------
0 SELECT STATEMENT 51750 252K 297 (4) 00:00:04
1 SORT ORDER BY 51750 252K 1240K 297 (4) 00:00:04
2 TABLE ACCESS FULL PARTS 51750 252K 131 (2) 00:00:02
------------------------------------------------------------------------------------
rollback;
-- second case : All PART_IDs, order doesn't matter
explain plan for select part_id from parts;
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3931018009
---------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
---------------------------------------------------------------------------
0 SELECT STATEMENT 51750 252K 131 (2) 00:00:02
1 TABLE ACCESS FULL PARTS 51750 252K 131 (2) 00:00:02
---------------------------------------------------------------------------
rollback;
-- third case : Single PART_ID
explain plan for select part_id from parts where part_id=20001;
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2169424942
------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
------------------------------------------------------------------------------
0 SELECT STATEMENT 1 5 1 (0) 00:00:01
* 1 INDEX UNIQUE SCAN PARTS_UK 1 5 1 (0) 00:00:01
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("PART_ID"=20001)
rollback;
-- third case : Range of PART_IDs
explain plan for select part_id from parts where part_id between 25000 and 25050;
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2044929039
-----------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
-----------------------------------------------------------------------------
0 SELECT STATEMENT 47 235 2 (0) 00:00:01
* 1 INDEX RANGE SCAN PARTS_UK 47 235 2 (0) 00:00:01
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("PART_ID"geq25000 AND "PART_ID"leq25050)
rollback;
REM REM REM !!!!!!!!!!!!!!!!!!!!!!!!!!!!!
-- We now set PART_ID to be NOT NULL
REM
ALTER TABLE PARTS MODIFY (PART_ID NOT NULL);
-- We re-run the test queries
-- first case : All PART_IDs, ordered
explain plan for select part_id from parts order by part_id;
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1189395249
-----------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
-----------------------------------------------------------------------------
0 SELECT STATEMENT 51750 252K 110 (2) 00:00:02
1 INDEX FULL SCAN PARTS_UK 51750 252K 110 (2) 00:00:02
-----------------------------------------------------------------------------
rollback;
-- second case : All PART_IDs, order doesn't matter
explain plan for select part_id from parts;
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2423297136
---------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
---------------------------------------------------------------------------------
0 SELECT STATEMENT 51750 252K 26 (4) 00:00:01
1 INDEX FAST FULL SCAN PARTS_UK 51750 252K 26 (4) 00:00:01
---------------------------------------------------------------------------------
rollback;
-- third case : Single PART_ID
explain plan for select part_id from parts where part_id=20001;
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2169424942
------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
------------------------------------------------------------------------------
0 SELECT STATEMENT 1 5 1 (0) 00:00:01
* 1 INDEX UNIQUE SCAN PARTS_UK 1 5 1 (0) 00:00:01
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("PART_ID"=20001)
rollback;
-- third case : Range of PART_IDs
explain plan for select part_id from parts where part_id between 25000 and 25050;
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2044929039
-----------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
-----------------------------------------------------------------------------
0 SELECT STATEMENT 47 235 2 (0) 00:00:01
* 1 INDEX RANGE SCAN PARTS_UK 47 235 2 (0) 00:00:01
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("PART_ID"geq25000 AND "PART_ID"leq25050)
rollback;
While the column (PART_ID) was not a NOT NULL, Oracle would choose a
FullTableScan for queries attempting to retrieve all the rows from the table.
The ORDER BY in the first query also adds to the COST.
Queries for a single value (20001) or a range of values (25000 to 25050) would be executed by Unique Scan and Range Scan respectively, as we expect.
What happens when the column is defined as a NOT NULL ? This, being a DDL,
invalidates parsed statements so the next execution of the same SQLs requires re-parsing.
This time, the Optimizer DOES decide to use the Index on PART_ID for the first two queries. We also see that where the query specifies an ORDER BY, Oracle does an Index Full Scan -- it "walks" the Leaf Blocks from the lowest value to the highest value, retrieving all the values in order. Oracle can now avoid having to do a SORT. Where an ORDER BY is not required, Oracle does a faster Index Fast Full Scan.
We have learnt three things :
1. If a column is guaranteed to contain no NULLs, it is best to explicitly specify it as a NOT NULL so that the Optimizer can take a "better informed" decision on the usability of an index on it.
2. When Oracle percieves a lower cost to an Index access, this can have a greater impact on the execution plan of a larger query which the table and column under consideration may only be a part of.
3. An ordered retrieval of rows can be executed by an Index Full Scan.
What I have not done in this test case is to force a fetch from table rows. The query can be satisfied by the Index alone, in this case. In the real world, too, we do come across and/or can create such indexes where a query or subquery is satisfied by an Index alone.
Categories: DBA Blogs
The Worst Ever SQL Rewrite
What is the worst ever re-write you've done in attempting to tune an SQL statement ?
This is what I did a few weeks ago. I took a complicated (it had 3 correlated complex sub-queries) of the form :
SELECT A.column_1
, A.column_2
...
FROM table_A A
,table_P P
...
WHERE A.column_1=E.column_1
...
AND D.column_15 = (
SELECT /*+ NO_MERGE */ MAX(column_25)
FROM
....
.. 71 line SQL statement
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 888888888
------------------------------------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes TempSpc Cost (%CPU) Time
------------------------------------------------------------------------------------------------------------------------
0 SELECT STATEMENT 1 337 47M (1)159:03:36
* 1 FILTER
* 2 HASH JOIN RIGHT OUTER 48M 15G 177K (1) 00:35:26
3 INDEX FAST FULL SCAN A_TABLE 2687 67175 5 (0) 00:00:01
so you can see that it had already been hinted during the course of a tuning exercise ...
and I transformed it with
SELECT /*+ ORDERED */ A.column_1
...
-- FROM table_A A
-- , table_P P
FROM
table_E E
, table_D D
... 74lines
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 999999999
------------------------------------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes TempSpc Cost (%CPU) Time
------------------------------------------------------------------------------------------------------------------------
0 SELECT STATEMENT 1 337 250T (2)999:59:59
* 1 FILTER
* 2 HASH JOIN RIGHT OUTER 48M 15G 250T (2)999:59:59
3 INDEX FAST FULL SCAN A_TABLE 2687 67175 5 (0) 00:00:01
I managed to increase the "COST" from 47million to 250trillion.
Oracle's projected query runtime went up from 159hours to to in excess of 1000 hours. !
And my mistake ? Wrongly ordering the tables in the FROM clause after putting in an ORDERED Hint ! Very fortunately, I hadn't run the query else I would have waited too long before realising that I had forced Oracle into an atrocious execution plan.
Moral of the story ? Hints do work as Directives ! Be careful with Hints.
Categories: DBA Blogs
Complex View Merging -- 7
My final test. What happens if I disable Cost Based Transformation ?
Much more expensive. The 10.2 Cost Based Transformation WAS better.
SQL> alter session set "_optimizer_cost_based_transformation"=OFF;
Session altered.
SQL>
SQL> select /* disabled cost_based_trasnfm */ m.author, m.book_name from my_fat_book_list m
2 where
3 m.published_date
4 = (select max(l.published_date)
5 from library_fat_book_list l
6 where l.author=m.author
7 and l.book_name=m.book_name)
8 order by 1,2
9 /
(output deleted)
123 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1796690992
-------------------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes TempSpc Cost (%CPU) Time
-------------------------------------------------------------------------------------------------------
0 SELECT STATEMENT 101K 7666K 3315 (2) 00:00:40
1 MERGE JOIN 101K 7666K 3315 (2) 00:00:40
2 SORT JOIN 101K 3982K 10M 3310 (2) 00:00:40
3 VIEW VW_SQ_1 101K 3982K 2250 (2) 00:00:28
4 HASH GROUP BY 101K 6969K 16M 2250 (2) 00:00:28
5 TABLE ACCESS FULL LIBRARY_FAT_BOOK_LIST 101K 6969K 556 (2) 00:00:07
* 6 SORT JOIN 260 9620 5 (20) 00:00:01
7 TABLE ACCESS FULL MY_FAT_BOOK_LIST 260 9620 4 (0) 00:00:01
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("AUTHOR"="M"."AUTHOR" AND "BOOK_NAME"="M"."BOOK_NAME" AND
"M"."PUBLISHED_DATE"="VW_COL_1")
filter("BOOK_NAME"="M"."BOOK_NAME" AND "AUTHOR"="M"."AUTHOR" AND
"M"."PUBLISHED_DATE"="VW_COL_1")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2470 consistent gets
0 physical reads
0 redo size
5107 bytes sent via SQL*Net to client
483 bytes received via SQL*Net from client
10 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
123 rows processed
SQL>
Much more expensive. The 10.2 Cost Based Transformation WAS better.
Categories: DBA Blogs
Complex View Merging - 4,5,6
Continuing the tests that I did in postings 1 , 2 , 3 , I now use "fatter" tables, adding 128 bytes in the row size.
This is the Test Data Creation :
Notice that the Row Count is almost exactly the same as in the first two test. However,
the Block Count is 4 times as much as earlier (see the first posting).
So, what do I see for MY_FAT_BOOK_LIST vis-a-vis MY_BOOK_LIST ?
Now, that is surprising ! The Cardinality Estimate for MY_FAT_BOOK_LIST is the same
as was for MY_BOOK_LIST. But Oracle now decides to use the FILTER operation against the subquery and *not* merge the subquery ! It's estimate of the resulting rowset count (101K) is way off ! Also, note the expected Byte count against that for the MY_BOOK_LIST query. Expanding the row size has made a significant difference.
And what happens with an explicit NO_MERGE Hint ?
Yes, it is the same. So, Oracle had chosen NOT to merge the view even if I didn't provide
the Hint ! (could this be the "cost based transformation" that 9i doesn't do ?)
What happens with FRIENDS_FAT_BOOK_LIST ? How does that fare against FRIENDS_BOOK_LIST ?
Let's see :
Hmm ! This is similar to FRIENDS_BOOK_LIST. Oracle *did* decide to merge the subquery
and use a Hash Join !
Let's see the NO_MERGE against MY_FRIENDS_BOOK_LIST.
The expected Cardinality and Cost have changed, along with Byte count.
This is the Test Data Creation :
SQL> drop table library_fat_book_list;
SQL> drop table my_fat_book_list;
SQL> drop table friends_fat_book_list;
SQL>
SQL> create table library_fat_book_list as
2 select owner author, substr(object_name,1,30) book_name, rpad('a',128,'f') other_cols, trunc(created) published_date from dba_objects
3 union
4 select owner author, substr(object_name,1,30) book_name, rpad('a',128,'f') other_cols, trunc(created)+object_id published_date from dba_objects
5 /
SQL>
SQL> create table my_fat_book_list as
2 select owner author, substr(object_name,1,30) book_name, rpad('a',128,'f') other_cols, trunc(created) published_date from dba_objects where mod(object_id,400)=0
3 union
4 select owner author, substr(object_name,1,30) book_name, rpad('a',128,'f') other_cols, trunc(created)+object_id published_date from dba_objects where mod(object_id,400)=0
5 /
SQL>
SQL> create table friends_fat_book_list as
2 select owner author, substr(object_name,1,30) book_name, rpad('a',128,'f') other_cols, trunc(created) published_date from dba_objects where mod(object_id,80)=0
3 union
4 select owner author, substr(object_name,1,30) book_name, rpad('a',128,'f') other_cols, trunc(created)+object_id from dba_objects where mod(object_id,80)=0
5 /
SQL>
SQL>
SQL> create index library_bl_ndx on library_fat_book_list(author,book_name);
SQL> create index my_bl_ndx on my_fat_book_list(author,book_name);
SQL>
SQL> exec dbms_stats.gather_table_stats('','LIBRARY_FAT_BOOK_LIST',method_opt=>'FOR ALL COLUMNS SIZE 1',estimate_percent=>100,cascade=>TRUE);
SQL> exec dbms_stats.gather_table_stats('','MY_FAT_BOOK_LIST',method_opt=>'FOR ALL COLUMNS SIZE 1',estimate_percent=>100,cascade=>TRUE);
SQL> exec dbms_stats.gather_table_stats('','FRIENDS_FAT_BOOK_LIST',method_opt=>'FOR ALL COLUMNS SIZE 1',estimate_percent=>100,cascade=>TRUE);
SQL>
SQL> select count(*) from library_fat_book_list;
COUNT(*)
----------
101968
SQL> select count(*) from my_fat_book_list;
COUNT(*)
----------
260
SQL> select count(*) from friends_fat_book_list;
COUNT(*)
----------
1296
SQL> select table_name, blocks, num_rows from user_tables where table_name in ('LIBRARY_FAT_BOOK_LIST','MY_FAT_BOOK_LIST','FRIENDS_FAT_BOOK_LIST') order by 1;
TABLE_NAME BLOCKS NUM_ROWS
------------------------------ ---------- ----------
FRIENDS_FAT_BOOK_LIST 36 1296
LIBRARY_FAT_BOOK_LIST 2502 101968
MY_FAT_BOOK_LIST 10 260
SQL>
Notice that the Row Count is almost exactly the same as in the first two test. However,
the Block Count is 4 times as much as earlier (see the first posting).
So, what do I see for MY_FAT_BOOK_LIST vis-a-vis MY_BOOK_LIST ?
SQL> select m.author, m.book_name from my_fat_book_list m
2 where
3 m.published_date
4 = (select max(l.published_date)
5 from library_fat_book_list l
6 where l.author=m.author
7 and l.book_name=m.book_name)
8 order by 1,2
9 /
(deleting the output)
123 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4124939080
--------------------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
--------------------------------------------------------------------------------------------------------
0 SELECT STATEMENT 101K 3684K 526 (1) 00:00:07
1 SORT ORDER BY 101K 3684K 526 (1) 00:00:07
* 2 FILTER
3 TABLE ACCESS FULL MY_FAT_BOOK_LIST 260 9620 4 (0) 00:00:01
4 SORT AGGREGATE 1 39
5 TABLE ACCESS BY INDEX ROWID LIBRARY_FAT_BOOK_LIST 1 39 4 (0) 00:00:01
* 6 INDEX RANGE SCAN LIBRARY_BL_NDX 2 3 (0) 00:00:01
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("M"."PUBLISHED_DATE"= (SELECT MAX("L"."PUBLISHED_DATE") FROM
"LIBRARY_FAT_BOOK_LIST" "L" WHERE "L"."BOOK_NAME"=:B1 AND "L"."AUTHOR"=:B2))
6 - access("L"."AUTHOR"=:B1 AND "L"."BOOK_NAME"=:B2)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
405 consistent gets
0 physical reads
0 redo size
4218 bytes sent via SQL*Net to client
406 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
123 rows processed
Now, that is surprising ! The Cardinality Estimate for MY_FAT_BOOK_LIST is the same
as was for MY_BOOK_LIST. But Oracle now decides to use the FILTER operation against the subquery and *not* merge the subquery ! It's estimate of the resulting rowset count (101K) is way off ! Also, note the expected Byte count against that for the MY_BOOK_LIST query. Expanding the row size has made a significant difference.
And what happens with an explicit NO_MERGE Hint ?
SQL> select m.author, m.book_name from my_fat_book_list m
2 where
3 m.published_date
4 = (select /*+ NO_MERGE */ max(l.published_date)
5 from library_fat_book_list l
6 where l.author=m.author
7 and l.book_name=m.book_name)
8 order by 1,2
9 /
(deleting the output
123 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4124939080
--------------------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
--------------------------------------------------------------------------------------------------------
0 SELECT STATEMENT 101K 3684K 526 (1) 00:00:07
1 SORT ORDER BY 101K 3684K 526 (1) 00:00:07
* 2 FILTER
3 TABLE ACCESS FULL MY_FAT_BOOK_LIST 260 9620 4 (0) 00:00:01
4 SORT AGGREGATE 1 39
5 TABLE ACCESS BY INDEX ROWID LIBRARY_FAT_BOOK_LIST 1 39 4 (0) 00:00:01
* 6 INDEX RANGE SCAN LIBRARY_BL_NDX 2 3 (0) 00:00:01
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("M"."PUBLISHED_DATE"= (SELECT /*+ NO_MERGE */ MAX("L"."PUBLISHED_DATE") FROM
"LIBRARY_FAT_BOOK_LIST" "L" WHERE "L"."BOOK_NAME"=:B1 AND "L"."AUTHOR"=:B2))
6 - access("L"."AUTHOR"=:B1 AND "L"."BOOK_NAME"=:B2)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
405 consistent gets
0 physical reads
0 redo size
4218 bytes sent via SQL*Net to client
406 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
123 rows processed
SQL>
)
Yes, it is the same. So, Oracle had chosen NOT to merge the view even if I didn't provide
the Hint ! (could this be the "cost based transformation" that 9i doesn't do ?)
What happens with FRIENDS_FAT_BOOK_LIST ? How does that fare against FRIENDS_BOOK_LIST ?
Let's see :
SQL> select f.author, f.book_name from friends_fat_book_list f
2 where
3 f.published_date
4 = (select max(l.published_date)
5 from library_fat_book_list l
6 where l.author=f.author
7 and l.book_name=f.book_name)
8 order by 1,2
9 /
(deleting output)
628 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 700107581
----------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
----------------------------------------------------------------------------------------------
0 SELECT STATEMENT 2 240 569 (2) 00:00:07
* 1 FILTER
2 SORT GROUP BY 2 240 569 (2) 00:00:07
* 3 HASH JOIN 1296 151K 568 (2) 00:00:07
4 TABLE ACCESS FULL FRIENDS_FAT_BOOK_LIST 1296 64800 10 (0) 00:00:01
5 TABLE ACCESS FULL LIBRARY_FAT_BOOK_LIST 101K 6970K 556 (2) 00:00:07
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("F"."PUBLISHED_DATE"=MAX("L"."PUBLISHED_DATE"))
3 - access("L"."AUTHOR"="F"."AUTHOR" AND "L"."BOOK_NAME"="F"."BOOK_NAME")
Statistics
----------------------------------------------------------
24 recursive calls
0 db block gets
2496 consistent gets
0 physical reads
0 redo size
19720 bytes sent via SQL*Net to client
461 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
628 rows processed
SQL>
Hmm ! This is similar to FRIENDS_BOOK_LIST. Oracle *did* decide to merge the subquery
and use a Hash Join !
Let's see the NO_MERGE against MY_FRIENDS_BOOK_LIST.
SQL> select f.author, f.book_name from friends_fat_book_list f
2 where
3 f.published_date
4 = (select /*+ NO_MERGE */ max(l.published_date)
5 from library_fat_book_list l
6 where l.author=f.author
7 and l.book_name=f.book_name)
8 order by 1,2
9 /
(output deleted)
628 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1487416122
--------------------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
--------------------------------------------------------------------------------------------------------
0 SELECT STATEMENT 101K 3783K 2607 (1) 00:00:32
1 SORT ORDER BY 101K 3783K 2607 (1) 00:00:32
* 2 FILTER
3 TABLE ACCESS FULL FRIENDS_FAT_BOOK_LIST 1296 49248 10 (0) 00:00:01
4 SORT AGGREGATE 1 39
5 TABLE ACCESS BY INDEX ROWID LIBRARY_FAT_BOOK_LIST 1 39 4 (0) 00:00:01
* 6 INDEX RANGE SCAN LIBRARY_BL_NDX 2 3 (0) 00:00:01
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("F"."PUBLISHED_DATE"= (SELECT /*+ NO_MERGE */ MAX("L"."PUBLISHED_DATE") FROM
"LIBRARY_FAT_BOOK_LIST" "L" WHERE "L"."BOOK_NAME"=:B1 AND "L"."AUTHOR"=:B2))
6 - access("L"."AUTHOR"=:B1 AND "L"."BOOK_NAME"=:B2)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2010 consistent gets
0 physical reads
0 redo size
19720 bytes sent via SQL*Net to client
461 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
628 rows processed
SQL>
The expected Cardinality and Cost have changed, along with Byte count.
Categories: DBA Blogs
Programming for MultiCore architectures
This one is not about Oracle databases at all, but worth a read :
Chip industry confronts 'software gap' between multicore, programming
Chip industry confronts 'software gap' between multicore, programming
Categories: DBA Blogs
Complex View Merging -- 3
Continuing the testing of Complex View Merging, this time with the "larger" FRIENDS_BOOK_LIST Table :
Oracle has chose to Join the two tables (it has "merged" the
subquery into the parent query).
So let's see what happens when I use the NO_MERGE Hint :
Aah ! Something more complicated. Oracle has instantiated the subquery as a View.
However, this time we see that the NO_MERGE that we forced Oracle to do has significantly increased the total effort. The "COST" has gone up from 151 to 2,596 but, more importantly, the "consistent gets" is up from 625 to 1,966.
So, with a larger FRIENDS_BOOK_LIST table, Oracle was actually better off doing Complex View Merging !
There is no "hard-and-fast" rule as to whether you should have Complex View Merging enabled or not. Oracle 10g does evaluate costs when choosing between a Merged and an Instantiated representation of the Aggregation subquery ("complex view").
In the next few postings, I will explore a "fatter" table (more columns increasing the number of table blocks without increasing the number of rows at all) and also see if I can figure out how the cost based transformation works.
SQL> REM -------------------------------------------------------------------------------------------------------------- REM REM REM ******* With Complex View Merging ******
SQL>
SQL> select f.author, f.book_name from friends_book_list f
2 where
3 f.published_date
4 = (select max(l.published_date)
5 from library_book_list l
6 where l.author=f.author
7 and l.book_name=f.book_name)
8 order by 1,2
9 /
(deleted output)
628 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2027338862
------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
------------------------------------------------------------------------------------------
0 SELECT STATEMENT 2 148 151 (6) 00:00:02
* 1 FILTER
2 SORT GROUP BY 2 148 151 (6) 00:00:02
* 3 HASH JOIN 1294 95756 149 (5) 00:00:02
4 TABLE ACCESS FULL FRIENDS_BOOK_LIST 1294 47878 4 (0) 00:00:01
5 TABLE ACCESS FULL LIBRARY_BOOK_LIST 101K 3685K 143 (3) 00:00:02
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("F"."PUBLISHED_DATE"=MAX("L"."PUBLISHED_DATE"))
3 - access("L"."AUTHOR"="F"."AUTHOR" AND "L"."BOOK_NAME"="F"."BOOK_NAME")
Statistics
----------------------------------------------------------
24 recursive calls
0 db block gets
625 consistent gets
0 physical reads
0 redo size
19721 bytes sent via SQL*Net to client
461 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
628 rows processed
Oracle has chose to Join the two tables (it has "merged" the
subquery into the parent query).
So let's see what happens when I use the NO_MERGE Hint :
SQL> REM -------------------------------------------------------------------------------------------------------------- REM REM REM ******* WITHOUT Complex View Merging ******
SQL>
SQL> select f.author, f.book_name from friends_book_list f
2 where
3 f.published_date
4 = (select /*+ NO_MERGE */ max(l.published_date)
5 from library_book_list l
6 where l.author=f.author
7 and l.book_name=f.book_name)
8 order by 1,2
9 /
(deleted output)
628 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 258794817
----------------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
----------------------------------------------------------------------------------------------------
0 SELECT STATEMENT 101K 3685K 2596 (1) 00:00:32
1 SORT ORDER BY 101K 3685K 2596 (1) 00:00:32
* 2 FILTER
3 TABLE ACCESS FULL FRIENDS_BOOK_LIST 1294 47878 4 (0) 00:00:01
4 SORT AGGREGATE 1 37
5 TABLE ACCESS BY INDEX ROWID LIBRARY_BOOK_LIST 1 37 4 (0) 00:00:01
* 6 INDEX RANGE SCAN LIBRARY_BL_NDX 2 3 (0) 00:00:01
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("F"."PUBLISHED_DATE"= (SELECT /*+ NO_MERGE */ MAX("L"."PUBLISHED_DATE") FROM
"LIBRARY_BOOK_LIST" "L" WHERE "L"."BOOK_NAME"=:B1 AND "L"."AUTHOR"=:B2))
6 - access("L"."AUTHOR"=:B1 AND "L"."BOOK_NAME"=:B2)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1966 consistent gets
0 physical reads
0 redo size
19721 bytes sent via SQL*Net to client
461 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
628 rows processed
SQL>
Aah ! Something more complicated. Oracle has instantiated the subquery as a View.
However, this time we see that the NO_MERGE that we forced Oracle to do has significantly increased the total effort. The "COST" has gone up from 151 to 2,596 but, more importantly, the "consistent gets" is up from 625 to 1,966.
So, with a larger FRIENDS_BOOK_LIST table, Oracle was actually better off doing Complex View Merging !
There is no "hard-and-fast" rule as to whether you should have Complex View Merging enabled or not. Oracle 10g does evaluate costs when choosing between a Merged and an Instantiated representation of the Aggregation subquery ("complex view").
In the next few postings, I will explore a "fatter" table (more columns increasing the number of table blocks without increasing the number of rows at all) and also see if I can figure out how the cost based transformation works.
Categories: DBA Blogs
Complex View Merging -- 2
After having described the test data , here are the results of my first test :
[on 10.2.0.3 on 32-bit Windows (8KB Block Size) ]
Querying for My Private Library (fewer books) against the Public Library, with Complex View Merging enabled by default, I see that the tables MY_BOOK_LIST and LIBRARY_BOOK_LIST are joined. The implicit view in the Subquery that does gets the max(published_date) has actually been merged into the parent query.
[on 10.2.0.3 on 32-bit Windows (8KB Block Size) ]
Querying for My Private Library (fewer books) against the Public Library, with Complex View Merging enabled by default, I see that the tables MY_BOOK_LIST and LIBRARY_BOOK_LIST are joined. The implicit view in the Subquery that does gets the max(published_date) has actually been merged into the parent query.
SQL> REM -------------------------------------------------------------------------------------------------------------- REM REM REM ******* With Complex View Merging ******
SQL>
SQL>
SQL>
SQL>
SQL> select m.author, m.book_name from my_book_list m
2 where
3 m.published_date
4 = (select max(l.published_date)
5 from library_book_list l
6 where l.author=m.author
7 and l.book_name=m.book_name)
8 order by 1,2
9 /
(deleting the output)
123 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2352755801
------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
------------------------------------------------------------------------------------------
0 SELECT STATEMENT 2 146 149 (5) 00:00:02
* 1 FILTER
2 SORT GROUP BY 2 146 149 (5) 00:00:02
* 3 HASH JOIN 260 18980 148 (5) 00:00:02
4 TABLE ACCESS FULL MY_BOOK_LIST 260 9360 3 (0) 00:00:01
5 TABLE ACCESS FULL LIBRARY_BOOK_LIST 101K 3685K 143 (3) 00:00:02
------------------------------------------------------------------------------------------