A few days ago, PACKT Publishing invited me to review the book "Oracle 10g/11g Data and Database Management Utilities" by Hector R. Madrid.
If you visit their website, you will notice that they publish books which are "off the mainstream". Their collection of Oracle Books isn't what you see from, say, Oracle Pubishing or McGrawHill or Apress. Obviously, they aren't looking at very high volume sales but want to attract buyers of books on specific or even specialised topics.
I have being reading the "Oracle 10g/11g Data and Database Management Utilities" eBook. I appreciate the chapters on
SQL Loader,
External Tables,
RMAN,
Session Management (locking and waiting for locks, killing sessions, resource manager, ASH, even Service Registration) {never before have I seen all these topics put together in a Chapter, although I wish that this chapter could be expanded, even doubled in size},
Scheduler,
DBCA, OUI, EMCA and OPatch -- each being a seperate Chapter.
(there are other chapters as well).
All in all, this is a book worth having available on one's desktop (either in hard copy or soft copy), as a Reference.
Hector Madrid maintains an Oracle Blog.
Hans Forbrich (many of us know him on forums.oracle.com) is a Reviewer.
.
.
.
I am an Oracle Database Specialist in Singapore.
Please note that this site uses cookies.
30 July, 2009
25 July, 2009
Direct Path Read cannot do delayed block cleanouts
A quick post. Christian Antognini, here, has posted how 11g's "direct read" operations for (serial) table scan have to repeatedly do delayed block cleanouts. I've titled my post as "cannot do delayed block cleanouts". They repeatedly re-attempt cleanouts but cannot really clean the blocks.
As should be evident, the "direct read" may sometimes be slower (then a normal read through the buffer cache) if the table has undergone significant changes recently *if* such a read has to be performed repeatedly. Note : Christian Antognini has even presented his performance findings in one of the comments).
.
As should be evident, the "direct read" may sometimes be slower (then a normal read through the buffer cache) if the table has undergone significant changes recently *if* such a read has to be performed repeatedly. Note : Christian Antognini has even presented his performance findings in one of the comments).
.
UPDATE :
21 July, 2009
The difference between NOT IN and NOT EXISTS
There is a popular misconception that NOT IN and NOT EXISTS are two ways to filter out rows present in one table and not in another table, with both methods being usable interchangeably. Popular wisdom is that the difference between the two is only in terms of performance (being on the basis of whether the larger table is the "outer" or the "inner" in the query) but that the results are the same.
However, we must remember that such an operation being an Anti-Join fails when NULLs are involved. In Oracle a NULL cannot be compared to any other value, not even another NULL. Therefore, a NOT IN operation would fail if the result set being probed returns a NULL. In such a case, the results of a NOT IN query is 0 rows while a NOT EXISTS query would still show the rows present in the one table but not in the other table.
Here is a simple demonstration :
You can see that if there is a single row with a NULL value on the query predicate (the OWNER column in QUERY_TABLE), the NOT IN query actually fails.
Tom Kyte has explained this in the section on Anti-Joins in his "Effective Oracle By Design" book. However, I prefer the much better explanation by Roger Schrag
.
.
.
However, we must remember that such an operation being an Anti-Join fails when NULLs are involved. In Oracle a NULL cannot be compared to any other value, not even another NULL. Therefore, a NOT IN operation would fail if the result set being probed returns a NULL. In such a case, the results of a NOT IN query is 0 rows while a NOT EXISTS query would still show the rows present in the one table but not in the other table.
Here is a simple demonstration :
SQL> -- create the two test tables
SQL>
SQL> drop table results_table purge;
Table dropped.
SQL> drop table query_table purge;
Table dropped.
SQL>
SQL> create table results_table
2 as select owner,object_name,object_type from dba_objects where owner in ('HEMANT','DBSNMP','OUTLN');
Table created.
SQL> create table query_table
2 as select owner,object_name,object_type from dba_objects where owner in ('DBSNMP','OUTLN');
Table created.
SQL>
SQL> -- a NOT IN query
SQL> select r.owner, r.object_name from results_table r
2 where r.owner not in (select q.owner from query_table q)
3 order by 1,2;
OWNER OBJECT_NAME
------------------------------ ------------------------------
HEMANT DUPDB
HEMANT MY_T_A
HEMANT RESULTS_TABLE
HEMANT SOURCE_TABLE
HEMANT TEST_APPEND
HEMANT TRACE_USER_SESSIONS
HEMANT TRACE_USER_SESSIONS_BEGIN
HEMANT TRACE_USER_SESSIONS_END
8 rows selected.
SQL>
SQL> -- a NOT EXISTS query
SQL> select r.owner, r.object_name from results_table r
2 where not exists (select '1' from query_table q where r.owner=q.owner)
3 order by 1,2;
OWNER OBJECT_NAME
------------------------------ ------------------------------
HEMANT DUPDB
HEMANT MY_T_A
HEMANT RESULTS_TABLE
HEMANT SOURCE_TABLE
HEMANT TEST_APPEND
HEMANT TRACE_USER_SESSIONS
HEMANT TRACE_USER_SESSIONS_BEGIN
HEMANT TRACE_USER_SESSIONS_END
8 rows selected.
SQL>
SQL> ---
SQL> REM So far, NOT IN and NOT EXISTS have presented the same results
SQL>
SQL> REM What happens if there is a row with a NULL value ?
SQL>
SQL> insert into query_table values (NULL,'ABCDEFGH','TABLE');
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL> -- retry the NOT IN query
SQL> select r.owner, r.object_name from results_table r
2 where r.owner not in (select q.owner from query_table q)
3 order by 1,2;
no rows selected
SQL>
SQL> -- retry the NOT EXISTS query
SQL> select r.owner, r.object_name from results_table r
2 where not exists (select '1' from query_table q where r.owner=q.owner)
3 order by 1,2;
OWNER OBJECT_NAME
------------------------------ ------------------------------
HEMANT DUPDB
HEMANT MY_T_A
HEMANT RESULTS_TABLE
HEMANT SOURCE_TABLE
HEMANT TEST_APPEND
HEMANT TRACE_USER_SESSIONS
HEMANT TRACE_USER_SESSIONS_BEGIN
HEMANT TRACE_USER_SESSIONS_END
8 rows selected.
SQL>
SQL> ---
SQL> REM Surprise ?! The NOT IN returned 0 rows !
SQL> REM Why ? Because of the presence of a NULL in the query_table !
SQL> REM
SQL> REM REMEMBER : A "NOT IN" anti-join fails because a NULL returned cannot be compared !
SQL>
SQL> --
SQL> REM One "workaround" is to filter out rows which contain NULLs
SQL> REM .... but think carefully before you do so. Are you sure you want to exclude them ?
SQL>
SQL> REM In the ideal world, such columns should be defined as NOT NULL columns !
SQL> REM That would be the right schema design !
SQL>
SQL> -- test the suggested workaround
SQL> select r.owner, r.object_name from results_table r
2 where r.owner not in (select q.owner from query_table q WHERE OWNER IS NOT NULL)
3 order by 1,2;
OWNER OBJECT_NAME
------------------------------ ------------------------------
HEMANT DUPDB
HEMANT MY_T_A
HEMANT RESULTS_TABLE
HEMANT SOURCE_TABLE
HEMANT TEST_APPEND
HEMANT TRACE_USER_SESSIONS
HEMANT TRACE_USER_SESSIONS_BEGIN
HEMANT TRACE_USER_SESSIONS_END
8 rows selected.
SQL>
You can see that if there is a single row with a NULL value on the query predicate (the OWNER column in QUERY_TABLE), the NOT IN query actually fails.
Tom Kyte has explained this in the section on Anti-Joins in his "Effective Oracle By Design" book. However, I prefer the much better explanation by Roger Schrag
.
.
.
18 July, 2009
Simple Tracing
A recent forums.oracle.com thread indicating possible errors in DUAL led me to create this post.
See this extract from a trace I created (Note : It is NOT the complete trace !) :
These are my notes :
1. Cursor#27 (being of 18 characters, indicated by "len") is the SQL statement "select * from dual"
2. There are 2 "FETCH" calls for this. The first returns 1 row but Oracle does another fetch as well.
3. The STAT line for this cursor indicate that it returned 1 row (indicated by "cnt") doing a "TABLE ACCESS FULL DUAL".
4. Finaly there is an End Transaction marker with a "XCTEND rlbk=0". Why is this so ? Because the *subsequent* SQL statement is a DDL. A DDL causes an Implicit Commit.
So, now we see
5. Cursor#20 is the SQL statement "create synonym my_t_a for test_append"
6. This causes multiple *recursive* calls. Of these, I paste only the first three ! ("dep=1" indicates that the next SQLs are recursive).
7. Before Cursor#20 can complete, it is the recursive calls, beginning with Cursor #12 that must execute.
8. Cursor #12 is "select dummy from dual where ora_dict_obj_type = 'SYNONYM' AND ora_dict_obj_owner = 'PUBLIC'" which means that Oracle is doing some validation.
9. The STAT lines for Cursor #12 indicate that Oracle does a FULL TABLE SCAN of DUAL. This actually returns 0 rows (indicated by "cnt=0"), as it should (you can validate it by running the same SQL).
10. The next recursive SQL is Cursor #25 which is obviously a lookup on OBJ$.
Here, I've also posted the Binds as well. So you can see that my DDL statement that attempted to create a synonym called "MY_T_A" is now being validated to see if "MY_T_A" exists in OBJ$ (as name = :2 where 2, being Bind#1 is "MY_T_A") {Note : owner#=:1 being '64' is the USER_ID for 'HEMANT').
What would you say about cursor#26 and it's binds ?
By the way, the entire Trace file for the the completion of that CREATE SYNONYM statement continues to 1,213 lines, with a number of other recursive SQLs thrown in. (The success of the CREATE is finally indicated to me by an "insert into obj$" statement, followed by another "XCTEND rblk=0").
.
.
.
See this extract from a trace I created (Note : It is NOT the complete trace !) :
PARSING IN CURSOR #27 len=18 dep=0 uid=64 oct=3 lid=64 tim=1218679391705217 hv=942515969 ad='8f207e60'
select * from dual
END OF STMT
PARSE #27:c=1000,e=1643,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1218679391705206
BINDS #27:
EXEC #27:c=0,e=178,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1218679391705795
WAIT #27: nam='SQL*Net message to client' ela= 6 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1218679391705922
FETCH #27:c=0,e=128,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=1,tim=1218679391706151
WAIT #27: nam='SQL*Net message from client' ela= 440 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1218679391706794
FETCH #27:c=0,e=5,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1218679391706966
WAIT #27: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1218679391707111
*** 2009-07-18 22:35:07.104
WAIT #27: nam='SQL*Net message from client' ela= 9761620 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1218679401468827
STAT #27 id=1 cnt=1 pid=0 pos=1 obj=258 op='TABLE ACCESS FULL DUAL (cr=3 pr=0 pw=0 time=116 us)'
XCTEND rlbk=0, rd_only=1
=====================
PARSING IN CURSOR #20 len=37 dep=0 uid=64 oct=19 lid=64 tim=1218679401470001 hv=0 ad='aca5fc08'
create synonym my_t_a for test_append
END OF STMT
PARSE #20:c=0,e=555,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1218679401469996
BINDS #20:
=====================
PARSING IN CURSOR #12 len=94 dep=1 uid=47 oct=3 lid=47 tim=1218679401475875 hv=1451648271 ad='8f2851e0'
select dummy from dual where ora_dict_obj_type = 'SYNONYM' AND ora_dict_obj_owner = 'PUBLIC'
END OF STMT
PARSE #12:c=4999,e=5279,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=1218679401475861
BINDS #12:
EXEC #12:c=0,e=443,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1218679401476760
FETCH #12:c=0,e=11,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1218679401476870
STAT #12 id=1 cnt=0 pid=0 pos=1 obj=0 op='FILTER (cr=0 pr=0 pw=0 time=288 us)'
STAT #12 id=2 cnt=0 pid=1 pos=1 obj=258 op='TABLE ACCESS FULL DUAL (cr=0 pr=0 pw=0 time=0 us)'
=====================
PARSING IN CURSOR #25 len=198 dep=1 uid=0 oct=3 lid=0 tim=1218679401478610 hv=4125641360 ad='942f7470'
select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null
END OF STMT
PARSE #25:c=1000,e=1079,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1218679401478602
BINDS #25:
kkscoacd
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=2aaaacafcbd8 bln=22 avl=02 flg=05
value=64
Bind#1
oacdty=01 mxl=32(06) mxlc=00 mal=00 scl=00 pre=00
oacflg=18 fl2=0001 frm=01 csi=31 siz=32 off=0
kxsbbbfp=2aaaacafcba0 bln=32 avl=06 flg=05
value="MY_T_A"
Bind#2
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=2aaaacafcb70 bln=24 avl=02 flg=05
value=1
EXEC #25:c=3000,e=3453,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1218679401482554
FETCH #25:c=0,e=94,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,tim=1218679401482787
STAT #25 id=1 cnt=0 pid=0 pos=1 obj=18 op='TABLE ACCESS BY INDEX ROWID OBJ$ (cr=2 pr=0 pw=0 time=104 us)'
STAT #25 id=2 cnt=0 pid=1 pos=1 obj=37 op='INDEX RANGE SCAN I_OBJ2 (cr=2 pr=0 pw=0 time=90 us)'
BINDS #26:
kkscoacd
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=2aaaacafcbd8 bln=22 avl=02 flg=05
value=1
Bind#1
oacdty=01 mxl=32(06) mxlc=00 mal=00 scl=00 pre=00
oacflg=18 fl2=0001 frm=01 csi=31 siz=32 off=0
kxsbbbfp=2aaaacafcba0 bln=32 avl=06 flg=05
value="MY_T_A"
Bind#2
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=2aaaacafcb70 bln=24 avl=02 flg=05
value=1
=====================
PARSING IN CURSOR #26 len=198 dep=1 uid=0 oct=3 lid=0 tim=1218679401484758 hv=4125641360 ad='942f7470'
select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null
END OF STMT
EXEC #26:c=1000,e=1261,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1218679401484749
WAIT #26: nam='db file sequential read' ela= 7469 file#=1 block#=55199 blocks=1 obj#=0 tim=1218679401492652
FETCH #26:c=1000,e=7766,p=1,cr=2,cu=0,mis=0,r=0,dep=1,og=4,tim=1218679401492875
=====================
These are my notes :
1. Cursor#27 (being of 18 characters, indicated by "len") is the SQL statement "select * from dual"
2. There are 2 "FETCH" calls for this. The first returns 1 row but Oracle does another fetch as well.
3. The STAT line for this cursor indicate that it returned 1 row (indicated by "cnt") doing a "TABLE ACCESS FULL DUAL".
4. Finaly there is an End Transaction marker with a "XCTEND rlbk=0". Why is this so ? Because the *subsequent* SQL statement is a DDL. A DDL causes an Implicit Commit.
So, now we see
5. Cursor#20 is the SQL statement "create synonym my_t_a for test_append"
6. This causes multiple *recursive* calls. Of these, I paste only the first three ! ("dep=1" indicates that the next SQLs are recursive).
7. Before Cursor#20 can complete, it is the recursive calls, beginning with Cursor #12 that must execute.
8. Cursor #12 is "select dummy from dual where ora_dict_obj_type = 'SYNONYM' AND ora_dict_obj_owner = 'PUBLIC'" which means that Oracle is doing some validation.
9. The STAT lines for Cursor #12 indicate that Oracle does a FULL TABLE SCAN of DUAL. This actually returns 0 rows (indicated by "cnt=0"), as it should (you can validate it by running the same SQL).
10. The next recursive SQL is Cursor #25 which is obviously a lookup on OBJ$.
Here, I've also posted the Binds as well. So you can see that my DDL statement that attempted to create a synonym called "MY_T_A" is now being validated to see if "MY_T_A" exists in OBJ$ (as name = :2 where 2, being Bind#1 is "MY_T_A") {Note : owner#=:1 being '64' is the USER_ID for 'HEMANT').
What would you say about cursor#26 and it's binds ?
By the way, the entire Trace file for the the completion of that CREATE SYNONYM statement continues to 1,213 lines, with a number of other recursive SQLs thrown in. (The success of the CREATE is finally indicated to me by an "insert into obj$" statement, followed by another "XCTEND rblk=0").
.
.
.
15 July, 2009
Sizing OR Growing a Table in AUTOALLOCATE
The recommended extent management clauses in Tablespaces are :
The second method allows you to specify *fixed* (ie, "uniform") extent sizes for all segments in the tablespace. However, I prefer the former as it generally (**) uses Extent Sizes of 64K, 1MB, 8MB and 64MB as a table/index segment grows. This allows you to "mix" small tables/indexes with larger ones in the same tablespace.
(** : for exceptions to the "generally" rule see Examples Of Odd Extent Sizes In Tablespaces With AUTOALLOCATE )
Here are some simple methods of using AUTOALLOCATE to "create" or "grow" a *larger* table, not relying on very many 64KB extents.
First, I create the Tablespace :
The first table I demonstrate is automatically created with a single 64KB extent :
For the second table, Oracle pre-allocates 16 extents of 64KB, precisely because I explicitly specified a storage clause demanding a 1MB allocation :
In this third table, I explicitly requested 400MB allocation, so Oracle skipped from 64KB extents to 8MB extents, giving me 50 such extents :
In this final example, I *grow* a table from a single 64KB extent to approximately 104MB, comprising of 1MB and 8MB extents : (Since the extent size of the last extent is already 8MB, Oracle leaves it at 8MB and does not truncate it down).
Thus, you can see that it is very easy to use the AUTOALLOCATE rules to "pre-create" or "grow" a table very large. You don't really need to create multiple tablespaces of different Uniform sizes (as some do with 3 or 4 differently Uniformed sizes of 10MB, 50MB, 100MB, 400MB etc). A simple AUTOALLOCATE policy would suffice.
.
.
.
EXTENT MANAGEMENT LOCAL AUTOALLOCATE; EXTENT MANAGEMENT LOCAL UNIFORM SIZE xM;
The second method allows you to specify *fixed* (ie, "uniform") extent sizes for all segments in the tablespace. However, I prefer the former as it generally (**) uses Extent Sizes of 64K, 1MB, 8MB and 64MB as a table/index segment grows. This allows you to "mix" small tables/indexes with larger ones in the same tablespace.
(** : for exceptions to the "generally" rule see Examples Of Odd Extent Sizes In Tablespaces With AUTOALLOCATE )
Here are some simple methods of using AUTOALLOCATE to "create" or "grow" a *larger* table, not relying on very many 64KB extents.
First, I create the Tablespace :
SQL> create tablespace test_autoallocate 2 datafile '/oracle_fs/Databases/ORT24FS/test_autoallocate.dbf' size 1000M autoextend on next 100M maxsize 4000M 3 extent management local autoallocate; Tablespace created. SQL>
The first table I demonstrate is automatically created with a single 64KB extent :
SQL> create table first_table (column_1 varchar2(5)) tablespace test_autoallocate;
Table created.
SQL> select extent_id, bytes/1024 from user_extents where segment_name = 'FIRST_TABLE' order by extent_id;
EXTENT_ID BYTES/1024
---------- ----------
0 64
SQL>
For the second table, Oracle pre-allocates 16 extents of 64KB, precisely because I explicitly specified a storage clause demanding a 1MB allocation :
SQL> create table second_table (column_1 varchar2(5)) tablespace test_autoallocate storage (initial 1M);
Table created.
SQL> select extent_id, bytes/1024 from user_extents where segment_name = 'SECOND_TABLE' order by extent_id;
EXTENT_ID BYTES/1024
---------- ----------
0 64
1 64
2 64
3 64
4 64
5 64
6 64
7 64
8 64
9 64
10 64
11 64
12 64
13 64
14 64
15 64
16 rows selected.
SQL>
In this third table, I explicitly requested 400MB allocation, so Oracle skipped from 64KB extents to 8MB extents, giving me 50 such extents :
SQL> create table third_table (column_1 varchar2(5)) tablespace test_autoallocate storage (initial 100M next 100M minextents 4);
Table created.
SQL> select bytes/1024, count(*) from user_extents where segment_name = 'THIRD_TABLE' group by bytes/1024 order by 1;
BYTES/1024 COUNT(*)
---------- ----------
8192 50
SQL>
In this final example, I *grow* a table from a single 64KB extent to approximately 104MB, comprising of 1MB and 8MB extents : (Since the extent size of the last extent is already 8MB, Oracle leaves it at 8MB and does not truncate it down).
SQL> create table grow_this_table (column_1 varchar2(5)) tablespace test_autoallocate;
Table created.
SQL> select extent_id, bytes/1024 from user_extents where segment_name = 'GROW_THIS_TABLE' order by extent_id;
EXTENT_ID BYTES/1024
---------- ----------
0 64
SQL> alter table grow_this_table allocate extent (size 100M);
Table altered.
SQL> select bytes/1024, count(*) from user_extents where segment_name = 'GROW_THIS_TABLE' group by bytes/1024 order by 1;
BYTES/1024 COUNT(*)
---------- ----------
64 1
1024 56
8192 6
SQL>
Thus, you can see that it is very easy to use the AUTOALLOCATE rules to "pre-create" or "grow" a table very large. You don't really need to create multiple tablespaces of different Uniform sizes (as some do with 3 or 4 differently Uniformed sizes of 10MB, 50MB, 100MB, 400MB etc). A simple AUTOALLOCATE policy would suffice.
.
.
.
Subscribe to:
Posts (Atom)