THE RAJESH BLOG: August 2011

Saturday, August 13, 2011

Scalar subquery and Result Cache on 11g

Scalar subqueries are used to minimize the number of times a PL/SQL function is called from SQL. This is important because the overhead of going from SQL to PL/SQL over and over again can be quite expensive, and the scalar subquery caching database feature can dramatically decrease this overhead.

What exactly is a scalar subquery? It is a subquery in a SQL statement that returns exactly one column and zero rows or one row. That single column can be a complex object type, so it can consist of many attributes, but the subquery returns a single scalar value.

rajesh@ORA11GR2> create table t
2 nologging
3 as
4 select * from all_objects;

Table created.

Elapsed: 00:00:06.14
rajesh@ORA11GR2>
rajesh@ORA11GR2> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.76
rajesh@ORA11GR2> create or replace function f(x varchar2)
2 return number
3 as
4 begin
5 dbms_application_info.set_client_info( userenv('client_info') + 1 );
6 return (length(x));
7 end;
8 /

Function created.

Elapsed: 00:00:00.67
rajesh@ORA11GR2>
rajesh@ORA11GR2> select count(distinct owner) from t;

COUNT(DISTINCTOWNER)
--------------------
30

Elapsed: 00:00:00.01

rajesh@ORA11GR2>
rajesh@ORA11GR2> exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> variable cpu number;
rajesh@ORA11GR2> exec :cpu := dbms_utility.get_cpu_time;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
rajesh@ORA11GR2>
rajesh@ORA11GR2> set autotrace traceonly statistics;
rajesh@ORA11GR2> select object_id,f(owner) from t;

71598 rows selected.

Elapsed: 00:00:00.73
Statistics
----------------------------------------------------------
         55 recursive calls
          0 db block gets
       1527 consistent gets
          0 physical reads
          0 redo size
     808576 bytes sent via SQL*Net to client
       5663 bytes received via SQL*Net from client
        479 SQL*Net roundtrips to/from client
          4 sorts (memory)
          0 sorts (disk)
      71598 rows processed

rajesh@ORA11GR2> set autotrace off;
rajesh@ORA11GR2>
rajesh@ORA11GR2> select userenv('client_info') ,
2        dbms_utility.get_cpu_time - :cpu
3 from dual;

USERENV('CLIENT_INFO') DBMS_UTILITY.GET_CPU_TIME-:CPU
--------------------------- ------------------------------
71598 67

Elapsed: 00:00:00.09

rajesh@ORA11GR2>

you can see that the function was called once per row even though the inputs to the function were repeated over and over again. If you use a scalar subquery you will notice a massive reduction in calls to the function

rajesh@ORA11GR2> exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
rajesh@ORA11GR2> exec :cpu := dbms_utility.get_cpu_time;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
rajesh@ORA11GR2> set autotrace traceonly statistics;
rajesh@ORA11GR2> select object_id,(select f(owner) from dual) from t;

71598 rows selected.

Elapsed: 00:00:00.18

Statistics
----------------------------------------------------------
          1 recursive calls
          0 db block gets
       1495 consistent gets
          0 physical reads
          0 redo size
     808592 bytes sent via SQL*Net to client
       5663 bytes received via SQL*Net from client
        479 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
      71598 rows processed

rajesh@ORA11GR2> set autotrace off;
rajesh@ORA11GR2> select userenv('client_info') ,
2 dbms_utility.get_cpu_time - :cpu
3 from dual;

USERENV('CLIENT_INFO') DBMS_UTILITY.GET_CPU_TIME-:CPU
-------------------------- ------------------------------
64 8

Elapsed: 00:00:00.01

rajesh@ORA11GR2>

The function call went down from 71598 to 64 and CPU time got dropped. How about making the function DETERMINISTIC

rajesh@ORA11GR2> create or replace function f(x varchar2)
2 return number
3 deterministic
4 as
5 begin
6 dbms_application_info.set_client_info( userenv('client_info') + 1 );
7 return (length(x));
8 end;
9 /

Function created.

Elapsed: 00:00:00.06
rajesh@ORA11GR2>
rajesh@ORA11GR2> exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
rajesh@ORA11GR2> exec :cpu := dbms_utility.get_cpu_time;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
rajesh@ORA11GR2> set autotrace traceonly statistics;
rajesh@ORA11GR2> select object_id,f(owner) from t;

71598 rows selected.

Elapsed: 00:00:00.21

Statistics
----------------------------------------------------------
         17 recursive calls
          0 db block gets
       1519 consistent gets
          0 physical reads
          0 redo size
     808576 bytes sent via SQL*Net to client
       5663 bytes received via SQL*Net from client
        479 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
      71598 rows processed

rajesh@ORA11GR2> set autotrace off;
rajesh@ORA11GR2>
rajesh@ORA11GR2> select userenv('client_info') ,
2 dbms_utility.get_cpu_time - :cpu
3 from dual;

USERENV('CLIENT_INFO') DBMS_UTILITY.GET_CPU_TIME-:CPU
------------------------------ ------------------------------
954 16

Elapsed: 00:00:00.01

rajesh@ORA11GR2>

DETERMINISTIC function reduced the function calls, but not as smart as scalar subquery caching. so going one step futher how about making the function as result_cache in Oracle 11g?

rajesh@ORA11GR2> create or replace function f(x varchar2)
2 return number
3 result_cache
4 as
5 begin
6 dbms_application_info.set_client_info( userenv('client_info') + 1 );
7 return (length(x));
8 end;
9 /

Function created.

Elapsed: 00:00:00.06
rajesh@ORA11GR2>
rajesh@ORA11GR2> exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
rajesh@ORA11GR2> exec :cpu := dbms_utility.get_cpu_time;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
rajesh@ORA11GR2> set autotrace traceonly statistics;
rajesh@ORA11GR2> select object_id,f(owner) from t;

71598 rows selected.

Elapsed: 00:00:00.54

rajesh@ORA11GR2> set autotrace off;
rajesh@ORA11GR2> select userenv('client_info') ,
2 dbms_utility.get_cpu_time - :cpu
3 from dual;

USERENV('CLIENT_INFO') DBMS_UTILITY.GET_CPU_TIME-:CPU
---------------------------- ------------------------------
30 44

Elapsed: 00:00:00.03

rajesh@ORA11GR2>

The function called is equal to the number of distinct values present in the column OWNER. If you run the query again then function call becomes zero but still the cpu utilization time is longer than scalar subquery.

rajesh@ORA11GR2> exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
rajesh@ORA11GR2> exec :cpu := dbms_utility.get_cpu_time;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
rajesh@ORA11GR2> set autotrace traceonly statistics;
rajesh@ORA11GR2> select object_id,f(owner) from t;

71598 rows selected.

Elapsed: 00:00:00.50

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
       1495 consistent gets
          0 physical reads
          0 redo size
     808576 bytes sent via SQL*Net to client
       5663 bytes received via SQL*Net from client
        479 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
      71598 rows processed

rajesh@ORA11GR2> set autotrace off;
rajesh@ORA11GR2>
rajesh@ORA11GR2> select userenv('client_info') ,
2 dbms_utility.get_cpu_time - :cpu
3 from dual;

USERENV('CLIENT_INFO') DBMS_UTILITY.GET_CPU_TIME-:CPU
------------------------------ ------------------------------
0 42

Elapsed: 00:00:00.01

rajesh@ORA11GR2>

This shows that even if the function is defined as DETERMINISTIC or RESULT_CACHE its better to have the function called as scalar subquery for efficient CPU utilization.

Sunday, August 7, 2011

Good Bye to Cardinality Hint and Welcome to Cardinality Feedback

rajesh@ORA10GR2> create table t
2 as
3 select * from all_objects;

Table created.

Elapsed: 00:00:04.21

rajesh@ORA10GR2>
rajesh@ORA10GR2> create index t_ind on t(object_id) nologging;

Index created.

Elapsed: 00:00:00.32
rajesh@ORA10GR2>
rajesh@ORA10GR2> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.42
rajesh@ORA10GR2>
rajesh@ORA10GR2>
rajesh@ORA10GR2> set autotrace traceonly explain statistics;
rajesh@ORA10GR2>
rajesh@ORA10GR2> select *
2 from t
3 where object_id in (select * from table ( sys.odcinumberlist(1,2,3,4,5,6) ) );

Elapsed: 00:00:00.07

Execution Plan
----------------------------------------------------------
Plan hash value: 1584247153
----------------------------------------------------------------------------
| Id | Operation                              | Name | Rows | Cost (%CPU)|
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |      |     1 |   187   (4)|
|* 1 | HASH JOIN RIGHT SEMI                  |      |     1 |   187   (4)|
|   2 |   COLLECTION ITERATOR CONSTRUCTOR FETCH|      |       |            |
|   3 |   TABLE ACCESS FULL                    | T    | 50096 |   161   (3)|
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("OBJECT_ID"=VALUE(KOKBF$))

Statistics
----------------------------------------------------------
        274 recursive calls
          0 db block gets
        797 consistent gets
          1 physical reads
          0 redo size
       1394 bytes sent via SQL*Net to client
        396 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
         21 sorts (memory)
          0 sorts (disk)
          5 rows processed

rajesh@ORA10GR2>

Optimizer is not using the index, since cardinality is not attached to plan at id=2, So how can we instruct the optimizer about cardinality? using undocumented hint cardinality

rajesh@ORA10GR2> select *
2 from t
3 where object_id in (select /*+ cardinality(t,5) */ * from table ( sys.odcinumberlist(1,2,3,4,5,6) ) t where rownum > 0);

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 561482654
-------------------------------------------------------------------------------
| Id | Operation                                   | Name     | Rows | Cost |
-------------------------------------------------------------------------------|
|   0 | SELECT STATEMENT                            |          |     1 |    27 |
|   1 | TABLE ACCESS BY INDEX ROWID                | T        |     1 |     2 |
|   2 |   NESTED LOOPS                              |          |     1 |    27 |
|   3 |    VIEW                                     | VW_NSO_1 |     5 |    24 |
|   4 |     HASH UNIQUE                             |          |     1 |       |
|   5 |      COUNT                                  |          |       |       |
|* 6 |       FILTER                                |          |       |       |
|   7 |        COLLECTION ITERATOR CONSTRUCTOR FETCH|          |       |       |
|* 8 |    INDEX RANGE SCAN                         | T_IND    |     1 |     1 |
--------------------------------------------------------------------------------

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

6 - filter(ROWNUM>0)
8 - access("OBJECT_ID"="COLUMN_VALUE")

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

rajesh@ORA10GR2>
rajesh@ORA10GR2>

The predicate rownum > 0 is added to have the subquery "materialized". Now we have the correct cardinality in plan & the optimizer picked up the index to answer our queries.

Even the MEMBER OF is not looking index at this point -- even when hinted to use index, it does Fast Full Scan and not Range scan.

rajesh@ORA10GR2> create or replace type numarray is table of number;
2 /

Type created.

Elapsed: 00:00:00.06
rajesh@ORA10GR2>
rajesh@ORA10GR2> variable x refcursor;
rajesh@ORA10GR2>

rajesh@ORA10GR2> declare
2     arr numarray := numarray(1,2,3,4,5,6);
3 begin
4     open :x for
5     select object_id,data_object_id,owner,object_type,object_name
6     from t
7     where object_id member of ( arr );
8 end;
9 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

SELECT OBJECT_ID,DATA_OBJECT_ID,OWNER,OBJECT_TYPE,OBJECT_NAME
FROM
T WHERE OBJECT_ID MEMBER OF ( :B1 )

call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.01       0.02          0          0          0           0
Fetch        1      0.04       0.03          0        694          0           5
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total        3      0.06       0.05          0        694          0           5

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 61

Rows Row Source Operation
------- ---------------------------------------------------
5 TABLE ACCESS FULL T (cr=694 pr=0 pw=0 time=140 us)

rajesh@ORA10GR2>

rajesh@ORA10GR2> declare
2     arr numarray := numarray(1,2,3,4,5,6);
3 begin
4     open :x for
5     select /*+ index(t,t_ind) */ object_id,data_object_id,owner,object_type,object_name
6     from t
7     where object_id member of ( arr );
8 end;
9 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
rajesh@ORA10GR2>

SELECT /*+ index(t,t_ind) */ OBJECT_ID,DATA_OBJECT_ID,OWNER,OBJECT_TYPE,
OBJECT_NAME
FROM
T WHERE OBJECT_ID MEMBER OF ( :B1 )

call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.01       0.01          0          0          0           0
Fetch        1      0.03       0.03          0        113          0           5
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total        3      0.04       0.04          0        113          0           5

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 61

Rows     Row Source Operation
------- ---------------------------------------------------
      5 TABLE ACCESS BY INDEX ROWID T (cr=113 pr=0 pw=0 time=97 us)
      5   INDEX FULL SCAN T_IND (cr=112 pr=0 pw=0 time=60 us)(object id 55213)

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03
rajesh@ORA10GR2>

SELECT OBJECT_ID,DATA_OBJECT_ID,OWNER,OBJECT_TYPE,OBJECT_NAME
FROM
T WHERE OBJECT_ID IN ( SELECT /*+ cardinality(t1,5) */ * FROM TABLE ( :B1 )
T1 )

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          9          0           0
Fetch        1      0.00       0.00          0          9          0           5
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total        3      0.00       0.00          0         18          0           5

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

Rows     Row Source Operation
------- ---------------------------------------------------
      5 TABLE ACCESS BY INDEX ROWID T (cr=9 pr=0 pw=0 time=278 us)
     12   NESTED LOOPS (cr=8 pr=0 pw=0 time=889 us)
      6    SORT UNIQUE (cr=0 pr=0 pw=0 time=92 us)
      6     COLLECTION ITERATOR PICKLER FETCH (cr=0 pr=0 pw=0 time=28 us)
      5    INDEX RANGE SCAN T_IND (cr=8 pr=0 pw=0 time=93 us)(object id 55213)

When working with Oracle 10g database, we need to Instruct cardinality to optimizer using Cardinality Hint. But when repeating the same test in Oracle 11g database

rajesh@ORA11GR2>
rajesh@ORA11GR2> select object_id,data_object_id,owner,object_type,object_name
2 from t
3 where object_id in (select * from table ( sys.odcinumberlist(1,2,3,4,5,6) ) );

OBJECT_ID DATA_OBJECT_ID OWNER      OBJECT_TYPE     OBJECT_NAME
---------- -------------- ---------- --------------- --------------------
         3              3 SYS        INDEX           I_OBJ#
         6              6 SYS        CLUSTER         C_TS#
         5              2 SYS        TABLE           CLU$
         2              2 SYS        CLUSTER         C_OBJ#
         4              2 SYS        TABLE           TAB$

Elapsed: 00:00:00.07
rajesh@ORA11GR2>

select object_id,data_object_id,owner,object_type,object_name
from t
where object_id in (select * from table ( sys.odcinumberlist(1,2,3,4,5,6) ) )

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.03       0.02          0       1026          0           5
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total        4      0.03       0.02          0       1026          0           5

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

Rows     Row Source Operation
------- ---------------------------------------------------
      5 HASH JOIN RIGHT SEMI (cr=1026 pr=0 pw=0 time=0 us cost=316 size=49 card=1)
      6   COLLECTION ITERATOR CONSTRUCTOR FETCH (cr=0 pr=0 pw=0 time=5 us cost=29 size=16336 card=8168)
71598   TABLE ACCESS FULL T (cr=1026 pr=0 pw=0 time=99596 us cost=286 size=3365106 card=71598)

rajesh@ORA11GR2> select object_id,data_object_id,owner,object_type,object_name
2 from t
3 where object_id in (select * from table ( sys.odcinumberlist(1,2,3,4,5,6) ) );

OBJECT_ID DATA_OBJECT_ID OWNER      OBJECT_TYPE     OBJECT_NAME
---------- -------------- ---------- --------------- --------------------
         2              2 SYS        CLUSTER         C_OBJ#
         3              3 SYS        INDEX           I_OBJ#
         4              2 SYS        TABLE           TAB$
         5              2 SYS        TABLE           CLU$
         6              6 SYS        CLUSTER         C_TS#

Elapsed: 00:00:00.04

rajesh@ORA11GR2>

select object_id,data_object_id,owner,object_type,object_name
from t
where object_id in (select * from table ( sys.odcinumberlist(1,2,3,4,5,6) ) )

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.00       0.00          0         12          0           5
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total        4      0.00       0.00          0         12          0           5

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

Rows Row Source Operation

------- ---------------------------------------------------
      5 NESTED LOOPS (cr=12 pr=0 pw=0 time=0 us)
      5   NESTED LOOPS (cr=10 pr=0 pw=0 time=136 us cost=36 size=294 card=6)
      6    SORT UNIQUE (cr=0 pr=0 pw=0 time=5 us cost=29 size=12 card=6)
      6     COLLECTION ITERATOR CONSTRUCTOR FETCH (cr=0 pr=0 pw=0 time=5 us cost=29 size=12 card=6)
      5    INDEX RANGE SCAN T_IND (cr=10 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 79427)
      5   TABLE ACCESS BY INDEX ROWID T (cr=2 pr=0 pw=0 time=0 us cost=2 size=47 card=1)

You see the optimizer hard parsed the query again & came up with right explain plan to answer this query. And further execution optimizer has reused the plan again.

rajesh@ORA11GR2> select object_id,data_object_id,owner,object_type,object_name
2 from t
3 where object_id in (select * from table ( sys.odcinumberlist(1,2,3,4,5,6) ) );

Elapsed: 00:00:00.03
rajesh@ORA11GR2>

select object_id,data_object_id,owner,object_type,object_name
from t
where object_id in (select * from table ( sys.odcinumberlist(1,2,3,4,5,6) ) )

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.00       0.00          0         12          0           5
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total        4      0.00       0.00          0         12          0           5

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 91

Rows     Row Source Operation
------- ---------------------------------------------------
      5 NESTED LOOPS (cr=12 pr=0 pw=0 time=0 us)
      5   NESTED LOOPS (cr=10 pr=0 pw=0 time=120 us cost=36 size=294 card=6)
      6    SORT UNIQUE (cr=0 pr=0 pw=0 time=5 us cost=29 size=12 card=6)
      6     COLLECTION ITERATOR CONSTRUCTOR FETCH (cr=0 pr=0 pw=0 time=10 us cost=29 size=12 card=6)
      5    INDEX RANGE SCAN T_IND (cr=10 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 79427)
      5   TABLE ACCESS BY INDEX ROWID T (cr=2 pr=0 pw=0 time=0 us cost=2 size=47 card=1)

This proper cardinality is supplied to 11g optimizer by a new feature "cardinality feedback"

Books I follow...

Advanced SQL Functions in Oracle 10g - by Richard Walsh Earp
Cost-Based Oracle Fundamentals - By Jonathan Lewis
Effective Oracle by Design - by Thomas kyte
Expert One on One Oracle - by Thomas kyte
Expert Oracle - by Thomas kyte
Expert Oracle Database Architecture Oracle Database 9i, 10g - Thomas Kyte
Expert Oracle Database Architecture Oracle Database 9i, 10g, and 11g Programming Techniques and Solutions - Thomas Kyte
Expert Oracle Practices Oracle Database Administration from the Oak Table
High-Performance Oracle Database Applications - by Donald K. Burleson
Mastering Oracle PL/SQL: Practical Solutions - by Connor McDonald
Mastering Oracle Scheduler in Oracle 11g Databases - by Ronald Rood
Mastering Oracle SQL and SQL*Plus - By LEX DE HAAN
Optimizing Oracle Performance - By Cary Millsap
Oracle Database 10g Performance Tuning Tips & Techniques - By Richard J. Niemiec
Oracle Database 10g XML & SQL: Design, Build & Manage XML Applications in Java, C, C++ & PL/SQL - by Mark V. Scardina, Ben Chang and Jinyu Wang
Oracle Database 10g: The Complete Reference - by Kevin Loney
Oracle Database 11g DBA Handbook - by Bob Bryla , Kevin Loney
Oracle Database 11g New Features - Maximize the new capabilities of the Latest Database Release - by Robert G. Freeman
Oracle Performance Tuning for 10gR2 - By Gavin Powell
Oracle PL/SQL for DBAs By Steven Feuerstein, Arup Nanda
Oracle SQL*Plus: The Definitive Guide - By Jonathan Gennick
Oracle Wait Interface: A Practical Guide to Performance Diagnostics & Tuning by Richmond Shee, Kirtikumar Deshpande and K Gopalakrishnan
Oracle8i Internal Services for Waits, Latches, Locks, and Memory - by Steve Adams
Professional Oracle 8i Application Programming - By Thomas Kyte
Secrets of the Oracle Database - By Norbert Debes
Troubleshooting Oracle Performance - By Christian Antognini
Tuning Third-party Vendor Oracle systems Tuning when you can't touch the code - By Mike Ault
Using Oracle SQL Stored Outlines & Optimizer Plan Stability - By Mike Ault