Collection: boobooke video lecture

Bewlow are the list of my video lectures on http://www.boobooke.com/, which is an educational site for various IT technologies hosted in China.

What I am trying to do is to present key concepts of Oracle Database through concrete examples and hands-on labs. All materials are written in English, however, some lectures are spoken in Chinese.

Oracle Database Core Concepts for DBA - Instance and Database Part I
http://www.boobooke.com/v/bbk2187

Oracle Database Core Concepts for DBA - Instance and Database Part II
http://www.boobooke.com/v/bbk2213

Oracle Database Core Concepts for DBA - Instance and Database Part III
http://www.boobooke.com/v/bbk2214

Oracle Database Core Concepts for DBA - Data Dictionary
http://www.boobooke.com/v/bbk2064 (in Chinese)

Oracle Database Core Concepts for DBA - Data Block Part I
http://www.boobooke.com/v/bbk2067 (in Chinese)

Oracle Database Core Concepts for DBA - Data Block Part II
http://www.boobooke.com/v/bbk2068 (in Chinese)

Oracle Database Core Concepts for DBA - DML Data Locks
http://www.boobooke.com/v/bbk2053 (in Chinese)

Discovering Performance Issues Using OEM, AWR and ADDM - Part I
http://www.boobooke.com/v/bbk2405

Discovering Performance Issues Using OEM, AWR and ADDM - Part II
http://www.boobooke.com/v/bbk2406

Below are video lectures presented by other people that are interested to me:

Installation of Cygwin Software
http://www.boobooke.com/v/bbk1473


(last updated: Mar 23, 2009)

Be careful when using SYSDATE in the predicate on 9i databases

I had a task to check a SQL and modify it to be used to generate hourly report. The original SQL given looks like (table and column names modified as usual):

---- literal string for the date  
SELECT To_Char(mas.o_save_date, 'MM/DD/YYYY HH24') o_time,
       DECODE(tr.nt_id, '201','D','209','F','217','V','222','FV') svc_type,
       tr.tr_type_id,
       Count(DISTINCT tr.bo_id) o_count
  FROM o_tr tr, 
       o_mas mas
 WHERE mas.is_pq = 'N'
   AND mas.int_o_id = tr.int_o_id
   AND tr.nt_id IN (201, 209, 217, 222)
   AND mas.o_save_date >= To_Date('01/22/2009 00:00:00','MM/DD/YYYY HH24:MI:SS') 
   AND mas.o_save_date < To_Date('01/23/2009 00:00:00','MM/DD/YYYY HH24:MI:SS')
 GROUP BY To_Char(mas.o_save_date, 'MM/DD/YYYY HH24'), tr.nt_id, tr.tr_type_id
;

I run it and the excution plan looks like:

-------------------------------------------------------------------------
| Id  | Operation                      |  Name    |Rows | Bytes | Cost  |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |     |       |    41 |
|   1 |  SORT GROUP BY                 |          |   4 |   148 |    41 |
|   2 |   TABLE ACCESS BY INDEX ROWID  | O_TR     |   2 |    40 |     9 |
|   3 |    NESTED LOOPS                |          |   4 |   148 |    25 |
|   4 |     TABLE ACCESS BY INDEX ROWID| O_MAS    |   2 |    34 |     7 |
|   5 |      INDEX RANGE SCAN          | O_MAS_IX2|   3 |       |     4 |
|   6 |     INDEX RANGE SCAN           | O_TR_FK3 |  14 |       |     3 |
-------------------------------------------------------------------------

The SQL returns in less than a minute. Then I execuate the following SQL which uses
SYSDATE instead of literal date.

SELECT To_Char(mas.o_save_date, 'MM/DD/YYYY HH24') o_time,
       DECODE(tr.nt_id, '201','D','209','F','217','V','222','FV') svc_type,
       tr.tr_type_id,
       Count(DISTINCT tr.bo_id) o_count
  FROM o_tr tr, 
       o_mas mas
 WHERE mas.is_pq = 'N'
   AND mas.int_o_id = tr.int_o_id
   AND tr.nt_id IN (201, 209, 217, 222)
   AND mas.o_save_date >= trunc(sysdate)-1 
   AND mas.o_save_date < trunc(sysdate) 
 GROUP BY To_Char(mas.o_save_date, 'MM/DD/YYYY HH24'), tr.nt_id, tr.tr_type_id
;

The execution plan changed to hash join and checked v$longops, indicating table scan will takes more than 1 hour, ie:

-------------------------------------------------------------------------------
| Id  | Operation                      |  Name     |Rows  |Bytes |TempSpc| Cost |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |           |      |      |       |   699K|
|   1 |  SORT GROUP BY                 |           | 2445K|   86M|   243M|   699K|
|   2 |   FILTER                       |           |      |      |       |       |
|   3 |    HASH JOIN                   |           | 2445K|   86M|    30M|   683K|
|   4 |     TABLE ACCESS BY INDEX ROWID| O_MAS     | 1113K|   18M|       | 38909 |
|   5 |      INDEX RANGE SCAN          | O_MAS_IX2 |  401K|      |       |  2976 |
|   6 |     TABLE ACCESS FULL          | O_TR      |   55M| 1053M|       |   617K|
---------------------------------------------------------------------------------

I tried to use hint to force the Oracle to use the first execution plan, the cost is 10M, no surprise that Oracle won't choose this plan.

SELECT 
/*+ use_nl( mas, tr) index(tr O_TR_FK3) */
SELECT To_Char(mas.o_save_date, 'MM/DD/YYYY HH24') o_time,
       DECODE(tr.nt_id, '201','D','209','F','217','V','222','FV') svc_type,
       tr.tr_type_id,
       Count(DISTINCT tr.bo_id) o_count
  FROM o_tr tr, 
       o_mas mas
 WHERE mas.is_pq = 'N'
   AND mas.int_o_id = tr.int_o_id
   AND tr.nt_id IN (201, 209, 217, 222)
   AND mas.o_save_date >= trunc(sysdate)-1 
   AND mas.o_save_date < trunc(sysdate) 
 GROUP BY To_Char(mas.o_save_date, 'MM/DD/YYYY HH24'), tr.nt_id, tr.tr_type_id
;


-------------------------------------------------------------------------------------
| Id  | Operation                       |  Name     | Rows  | Bytes |TempSpc| Cost  |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |           |       |       |       |    10M|
|   1 |  SORT GROUP BY                  |           |  2445K|    86M|   243M|    10M|
|   2 |   FILTER                        |           |       |       |       |       |
|   3 |    TABLE ACCESS BY INDEX ROWID  | O_TR      |     2 |    40 |       |     9 |
|   4 |     NESTED LOOPS                |           |  2445K|    86M|       |    10M|
|   5 |      TABLE ACCESS BY INDEX ROWID| O_MAS     |  1113K|    18M|       | 38909 |
|   6 |       INDEX RANGE SCAN          | O_MAS_IX2 |   401K|       |       |  2976 |
|   7 |      INDEX RANGE SCAN           | O_TR_FK3  |    14 |       |       |     3 |
-------------------------------------------------------------------------------------

For the hourly report, I finally obtained the following SQL:

SELECT  /*+ use_nl( mas, tr) index(mas O_MAS_IX2) index(tr O_TR_FK3) */
       To_Char(mas.o_save_date, 'MM/DD/YYYY HH24') o_time,
       DECODE(tr.nt_id, '201','D','209','F','217','V','222','FV') svc_type,
       tr.tr_type_id,
       Count(DISTINCT tr.bo_id) o_count 
 FROM 
      o_mas mas,
      o_tr tr 
WHERE mas.is_pq = 'N'
  AND mas.int_o_id = tr.int_o_id
  AND tr.nt_id IN (201, 209, 217, 222)
  AND mas.o_save_date >= trunc(sysdate,'HH24') - 1/24 
  AND mas.o_save_date <  trunc(sysdate, 'HH24')
GROUP BY To_Char(mas.o_save_date, 'MM/DD/YYYY HH24'), tr.nt_id, tr.tr_type_id
/

This SQl returns in seconds. If not using the hint, Oracle will choose hash join. What happend here? I seek answers from Jonathan Lewis's book "Cost-Based Oracle Fundamentals" as I vaguelly remember there is something about SYSDATE. I am right. His book has a section titled "Surprising Sysdate" (p130) that shed light on this issue. Basically, Oracle treat "Sysdate+N" as unknown, and treat it same as bind variable. As a result, optimizer can not calculate the cardinality and selectivity in the same way as the case of literal string. Oracle 10g has identified and addressed this problem.

Installation of Oracle 10gR2 on CentOS 4.7 for personal study

I bought a new laptop with Windows XP professional SP3 - I requested to downgrade to Windows XP from Vista when I placed the order. However I found that Windows XP can only recognize 3GB memory though BIOS shows 4GB installed. I have to live with that for now since I don't want to use Vista. I assigned myself a project to install Oracle 10gR2 software on CentOS 4.7 and use virtual machine technology.

The steps I took to accomplish this project will be described as follows:

1. Download VMware Server 2.0 from http://www.vmware.com/download/server/

2. Download CentOS 4.7 from http://www.centos.org

3. Create a virtual machine through VMware Infrastructure Web Access.
The vitural machine was created successfully with inital RAM 768M and host-only for the network configuration. After creating the virtual machine, I login with VMware Console. I obtained the IP address of the vitual machine by ifconfig, which is 192.168.233.128. I did not specify anything during installation, so this IP is kind of automatically configured. I verified that I can ping and ssh using this IP from host machine.

4. Configure Cygwin/X
Since I knew I need X-windows to lanch Oracle Universal Installer and I decided not to run it within the server (virtual machine), instead from the host machine, I configured the Cygwin/X. The User guide about how to install and config Cygwin/X is here. I verified I can ssh to the virtual machine and launch xclient such as XCLOK. The command involved:

In the Cygwin terminal:
$ sh /usr/bin/startxwin.sh

Cygwin X terminal shown up, then:
$ ssh -Y -l oracle 192.168.233.128

I found that I did not need set DISPLAY environment variable at all.

5. Download Oracle 10gR2 and sftp to the virtual machine

6. Install Oracle 10gR2

I followed this article ( http://www.oracle.com/technology/pub/articles/smiley_10gdb_install.html) to install the software. When I checked the required packages, I found that two package are missing:
- sysstat
- libaio is not installed

I decide to ignore it, but the first installation failed, Oracle complains that libaio is not found and also indicated the minimum memory requirement is not met. So I downloaded these two packages from http://mirror.astate.edu/pub/centos/4.7/os/i386/CentOS/RPMS After installing them and increased the memory size of the virtual machine to 1G, the Oracle software got installed successfully.


Note: 'usermod' can be used to modify user group, ex.

[root@localhost /]# groupadd oinstall
[root@localhost /]# usermod -g oinstall -G dba oracle
[root@localhost /]# id oracle
uid=500(oracle) gid=502(oinstall) groups=502(oinstall),501(dba)

Self anti-join - failed to obtain optimal exectuion plan on a 9i database

I continued to study the query I described in the previous post on Jan 18. I realized that the first part of the query is essentially a self anti-join.

The column statistics of the table BM in the production is as follows:

 COLUMN_NAME       LOW_VAL        HIGH_VAL   NUM_DISTINCT  NUM_NULLS
 ------------- ------------- --------------  -----------   ----------
 BID               3             598219339    314914980          0
 BSCI              B             S                    2          0
 BSC               1             7                    7          0
 VP_BID            0             598218162     25905093          0

I thus constructed a baseline test case as following:

create table bm
as
select
   rownum    bid,
   trunc(dbms_random.value(1,500))          vp_bid,
   mod(rownum, 7)  + 1                  bsc,
   decode(mod(rownum,2),
          0, 'A',
          1, 'S')     bsci
from all_objects 
where rownum <= 6500;  
  
create index bm_fk5 on bm(vp_bid);     

begin        
dbms_stats.gather_table_stats( user,  'bm',  cascade => true); 
end; 
/  

set autotrace traceonly  
SELECT *   FROM bm  
WHERE bm.bsc = 3    
AND bm.bsci = 'S'    
AND NOT EXISTS (           
          SELECT  1             
            FROM bm bm1            
           WHERE bm.vp_bid = bm1.vp_bid              
             AND bm1.bsci = 'S'              
             AND bm1.bsc NOT IN (3, 4)); 
set autotrace off  

Here are the results of Autotrace

1. When executed the baseline test case in a 9.2.0.8 database:

Execution Plan
-------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=14 Card=1 Bytes=12)
1    0   FILTER
2    1     TABLE ACCESS (FULL) OF 'BM' (Cost=4 Card=1 Bytes=12)
3    1     TABLE ACCESS (BY INDEX ROWID) OF 'BM' (Cost=10 Card=1 Bytes=12)
4    3       INDEX (RANGE SCAN) OF 'BM_FK5' (NON-UNIQUE) (Cost=1 Card=13)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1508  consistent gets
          0  physical reads
          0  redo size
        278  bytes sent via SQL*Net to client
        234  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

2. When executed the baseline test case in a 10.2.0.1 database:

---------------------------------------------------------------------------
 Id   Operation           Name  Rows   Bytes  Cost (%CPU) Time  
---------------------------------------------------------------------------
   0  SELECT STATEMENT              1     21     14  (15) 00:00:01
*  1   HASH JOIN ANTI               1     21     14  (15) 00:00:01
*  2    TABLE ACCESS FULL BM      464   5568      7  (15) 00:00:01
*  3    TABLE ACCESS FULL BM     2388  21492      7  (15) 00:00:01
---------------------------------------------------------------------------

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

   1 - access("BM"."VP_BID"="BM1"."VP_BID")
   2 - filter("BM"."BSC"=3 AND "BM"."BSCI"='S')
   3 - filter("BM1"."BSCI"='S' AND "BM1"."BSC"<>3 AND "BM1"."BSC"<>4)


Statistics
----------------------------------------------------------
        166  recursive calls
          0  db block gets
         55  consistent gets
          0  physical reads
          0  redo size
        689  bytes sent via SQL*Net to client
        381  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
          8  rows processed

Note: 10g picked up hash join anti acccess path and the consistent gets is only 55 vs 1508 in the case of 9i.

3. Drop the index BM_FK5, and run the baseline test case in the 9.2.0.8 database

   Execution Plan
   ----------------------------------------------------------
      0      SELECT STATEMENT Optimizer=CHOOSE (Cost=9 Card=1 Bytes=21)
      1    0   HASH JOIN (ANTI) (Cost=9 Card=1 Bytes=21)
      2    1     TABLE ACCESS (FULL) OF 'BM' (Cost=4 Card=464 Bytes=5568)
      3    1     TABLE ACCESS (FULL) OF 'BM' (Cost=4 Card=2388 Bytes=21492)


   Statistics
   ----------------------------------------------------------
           153  recursive calls
             0  db block gets
            54  consistent gets
             0  physical reads
             0  redo size
           389  bytes sent via SQL*Net to client
           234  bytes received via SQL*Net from client
             2  SQL*Net roundtrips to/from client
             3  sorts (memory)
             0  sorts (disk)
             8  rows processed

Note: without that index, 9i can choose right executin plan.

I then tested different ways in order to get 9.2.0.8 database to pick up the good execution plan when the index is present.

I tried:

1. NO_INDEX hint
2. collect histogram on the indexed column
3. rewrite the SQL to use NOT IN
4. NOT IN with HASH_AJ hint
5. HASH_AJ hint
6. Not analyzing the table, using dynamic sampling feaure

None of above succeeded. After playing with it the whole afternoon. I decided to move on and document it here for reference.

Anti-Join - A test case showing difference between NOT IN and NOT EXISTS on a 9i database

This test case is built based on Tom Kyte's book "Effective Oracle by Design" p477.

Summary of logical reads of anti-join queris when using deffierent structure

---------------------------------------------------------
Structure    NOT IN       NOT EXISTS      Outer join
---------------------------------------------------------
9i               160           20133          160
10g              155            155            155
----------------------------------------------------------

 The test case is as follows: 

 
rem script: anti_join_cbo.sql
rem    
rem    anti-join: used to return rows from a table that are not 
rem               present in some other row source
rem 

set echo on
drop table t1;
drop table t2;

create table t1 as select *
from all_objects where rownum <=10000;


create table t2 as select *
from all_objects where rownum <=9950;

create index t2_idx on t2(object_id);



begin
      dbms_stats.gather_table_stats(
            user,
            't1',
           cascade => true
      );
end;
/

begin
      dbms_stats.gather_table_stats(
            user,
            't2',
           cascade => true
      );
end;
/

alter session set tracefile_identifier = anti_join;
alter session set timed_statistics=true;
alter session set events '10046 trace name context forever, level 12';

-- 1. NOT IN
select count(*) from t1 cbo
where object_id not in (select object_id from t2);


-- 2. NOT EXISTS

select count(*) from t1 cbo
where not exists (select null from t2 where t2.object_id = cbo.object_id );


-- 3. OUTER JOIN

select count(*) from t1, t2 cbo
where  t1.object_id = cbo.object_id(+) 
 and  cbo.object_id is NULL;


alter session set events '10046 trace name context off'; 
exit;

set doc off
doc

------- 9.2.0.8   optimizer_mode=choose  ------

1. NOT IN

   select count(*) from t1 cbo
   where object_id not in (select object_id from t2)

   call     count cpu  elapsed  disk  query  current   rows
   ------- ------ ---- -------- ----- ------ --------  -----
   Parse        1 0.01     0.00     0      0        0      0
   Execute      1 0.00     0.00     0      0        0      0
   Fetch        2 0.04     0.03     0    160        0      1
   ------- ------ ---- -------- ----- ------ --------  -----
   total        4 0.05     0.03     0    160        0      1

   Misses in library cache during parse: 1
   Optimizer goal: CHOOSE
   Parsing user id: 178

   Rows     Row Source Operation
   -------  ---------------------------------------------------
  1  SORT AGGREGATE
 50   HASH JOIN ANTI
     10000    TABLE ACCESS FULL T1
      9950    INDEX FAST FULL SCAN T2_IDX (object id 443635)





2. NOT EXISTS

select count(*) from t1 cbo
where not exists (select null from t2 where t2.object_id = cbo.object_id )

call     count    cpu  elapsed   disk  query current   rows
------- ------  ----- -------- ------ ------ -------  -----
Parse        1   0.00     0.00      0      0       0      0
Execute      1   0.00     0.00      0      0       0      0
Fetch        2   0.17     0.16      0  20133       0      1
------- ------  ----- -------- ------ ------ -------  -----
total        4   0.17     0.16      0  20133       0      1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 178

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE
     50   FILTER
  10000    TABLE ACCESS FULL T1
   9950    INDEX RANGE SCAN T2_IDX (object id 443635)


3. OUTER JOIN

select count(*) from t1, t2 cbo
where  t1.object_id = cbo.object_id(+)
 and  cbo.object_id is NULL

call     count    cpu   elapsed  disk  query  current   rows
------- ------  ----- --------- ----- ------ --------  -----
Parse        1   0.01      0.00     0      0        0      0
Execute      1   0.00      0.00     0      0        0      0
Fetch        2   0.04      0.04     0    160        0      1
------- ------  ----- --------- ----- ------ --------  -----
total        4   0.05      0.04     0    160        0      1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 178

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE
     50   FILTER
  10000    HASH JOIN OUTER
  10000     TABLE ACCESS FULL T1
   9950     INDEX FAST FULL SCAN T2_IDX (object id 443635)

------- 10.2.0.1   optimizer_mode=all_rows  ------


1. NOT IN 
   select count(*) from t1 cbo
   where object_id not in (select object_id from t2)

   call     count    cpu  elapsed disk   query  current  rows
   ------- ------  ----- -------- ---- ------- --------  ----
   Parse        1   0.01     0.11    0       0        0     0
   Execute      1   0.00     0.03    0       0        0     0
   Fetch        2   0.01     0.02    0     155        0     1
   ------- ------  ----- -------- ---- ------- --------  ----
   total        4   0.03     0.17    0     155        0     1

   Misses in library cache during parse: 1
   Optimizer mode: ALL_ROWS
   Parsing user id: 62

   Rows     Row Source Operation
   -------  ---------------------------------------------------
  1  SORT AGGREGATE (cr=155 pr=0 pw=0 time=25632 us)
 50   HASH JOIN ANTI (cr=155 pr=0 pw=0 time=25386 us)
     10000    TABLE ACCESS FULL T1 (cr=129 pr=0 pw=0 time=30170 us)
      9950    INDEX FAST FULL SCAN T2_IDX (cr=26 pr=0 pw=0 time=29952 us)(object id 58211)

2. NOT EXISTS

    Same as "NOT IN"


3. Outer join

    Same as "NOT IN"

#