PIPELINED Vs Non-PIPELINED Function.

CREATE TYPE all_obj_type IS OBJECT
(
  OWNER              VARCHAR2(30),
  OBJECT_NAME        VARCHAR2(30),
  SUBOBJECT_NAME    VARCHAR2(30),
  OBJECT_ID          NUMBER,
  DATA_OBJECT_ID    NUMBER,
  OBJECT_TYPE        VARCHAR2(19),
  CREATED            DATE,
  LAST_DDL_TIME      DATE
); 
/

CREATE OR REPLACE TYPE all_obj_tt IS TABLE OF all_obj_type;
/

CREATE OR REPLACE FUNCTION non_pipe_fnc
RETURN all_obj_tt IS
    all_obj  all_obj_tt := all_obj_tt();
BEGIN
    FOR r IN (SELECT * FROM ALL_OBJECTS)
    LOOP
        all_obj.EXTEND;                   
        all_obj(all_obj.COUNT) := all_obj_type(r.OWNER,r.OBJECT_NAME,r.SUBOBJECT_NAME,r.OBJECT_ID,r.DATA_OBJECT_ID,r.OBJECT_TYPE,r.CREATED,r.LAST_DDL_TIME);   
    END LOOP;
    RETURN all_obj;
END non_pipe_fnc;
/

CREATE OR REPLACE FUNCTION pipe_fnc
RETURN all_obj_tt
PIPELINED IS
BEGIN
    FOR r IN (SELECT * FROM ALL_OBJECTS)
    LOOP       
        PIPE ROW(all_obj_type(r.OWNER,r.OBJECT_NAME,r.SUBOBJECT_NAME,r.OBJECT_ID,r.DATA_OBJECT_ID,r.OBJECT_TYPE,r.CREATED,r.LAST_DDL_TIME));
    END LOOP;
    RETURN;
END pipe_fnc;   
/




scott@10G> exec runstats_pkg.rs_start;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.21
scott@10G> select * from TABLE(non_pipe_fnc());

41042 rows selected.

Elapsed: 00:00:03.64

Execution Plan
----------------------------------------------------------
Plan hash value: 1517095174

--------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |              |  8168 | 16336 |    24   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| NON_PIPE_FNC |       |       |            |          |
--------------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
       6794  recursive calls
          0  db block gets
      87847  consistent gets
        101  physical reads
          0  redo size
    1587183  bytes sent via SQL*Net to client
       3399  bytes received via SQL*Net from client
        275  SQL*Net roundtrips to/from client
         33  sorts (memory)
          0  sorts (disk)
      41042  rows processed

scott@10G> exec runstats_pkg.rs_middle;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03
scott@10G> select * from TABLE(pipe_fnc());

41042 rows selected.

Elapsed: 00:00:02.84

Execution Plan
----------------------------------------------------------
Plan hash value: 2394767287

----------------------------------------------------------------------------------------------
| Id  | Operation                         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |          |  8168 | 16336 |    25   (4)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| PIPE_FNC |       |       |            |          |
----------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
       5819  recursive calls
          0  db block gets
      86292  consistent gets
          0  physical reads
          0  redo size
    1587183  bytes sent via SQL*Net to client
       3399  bytes received via SQL*Net from client
        275  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
      41042  rows processed

Name                                       Run1             Run2  Diff
STAT...session uga memory      261,856          0        -261,856
STAT...physical read total byt   851,968          0        -851,968
STAT...physical read bytes       851,968           0        -851,968
STAT...session pga memory     3,080,192        0      -3,080,192
STAT...session uga memory max  16,811,116  0     -16,811,116
STAT...session pga memory max  36,569,088  0     -36,569,088     

Run1 latches total versus runs -- difference and pct
        Run1        Run2        Diff       Pct
     266,350     266,319         -31    100.01%

PL/SQL procedure successfully completed.

Bind Peeking

Bind peeking - it means when the query is first Hard parsed, the optimizer will peek at the binds in order to determine how to optimize the query. Its not at every parse, only at Hard parsing.

create table t
as
select 1 as id,a.*
from all_objects a;

update t set id = 99 where rownum = 1;
create index t_ind on t(id);

exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>'T',estimate_percent=>100,method_opt=>'for all indexed columns size 254',cascade=>true);
/* massively skewed data, index on t(id) should be used if  ID is not 1..So we run these queries against this data */

variable x number;
exec :x := 1;

select * from t x_was_1 where id = :x;

exec :x := 99;
select * from t x_was_99 where id = :x;

/* Now we execute the same queries (soft parse) --but with the inputs flip flopped */
exec :x := 1;
select * from t x_was_99 where id = :x;

exec :x := 99;
select * from t x_was_1 where id = :x;

********************************************************************************
BEGIN :x := 1; END;

select *
from
 t x_was_1 where id = :x


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      344      0.04       0.06          0       1073          0       51437
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      346      0.04       0.06          0       1073          0       51437

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

Rows     Row Source Operation
-------  ---------------------------------------------------
  51437  TABLE ACCESS FULL T (cr=1073 pr=0 pw=0 time=34 us)

/* This is what expected, when the bind was set to 1 during Hard parse. A Full Table scan */
********************************************************************************
BEGIN :x := 99; END;

select *
from
 t x_was_99 where id = :x


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          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          3          0           1

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

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS BY INDEX ROWID T (cr=3 pr=0 pw=0 time=30 us)
      1   INDEX RANGE SCAN T_IND (cr=2 pr=0 pw=0 time=21 us)(object id 65996)

/*  Expected plan with Bind is 99 - Index range scan */

********************************************************************************
BEGIN :x := 1; END;

select *
from
 t x_was_99 where id = :x


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      344      0.07       0.09          0       1512          0       51437
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      346      0.07       0.09          0       1512          0       51437

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

Rows     Row Source Operation
-------  ---------------------------------------------------
  51437  TABLE ACCESS BY INDEX ROWID T (cr=1512 pr=0 pw=0 time=102909 us)
  51437   INDEX RANGE SCAN T_IND (cr=445 pr=0 pw=0 time=24 us)(object id 65996)

/*  Here the bind variable is in fact 1, if you remember from the test case. The plan was fixed during the Hard parse and same got reused. */
********************************************************************************
BEGIN :x := 99; END;

select *
from
 t x_was_1 where id = :x


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        735          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0        735          0           1

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

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS FULL T (cr=735 pr=0 pw=0 time=50 us)

/* Same here now. Full table scan is not appropriate */
********************************************************************************

Binds - Are Mandatory on systems that parses many queries / second.
Binds - Not mandatory on systems that have many seconds between parses ( typically data warehouses, and queries that run for minutes or hours )

Global Variables in Packages

I like to say from time to time "I learn something new about Oracle every day".  I am learned something new about PL/SQL that i was not aware of.

scott@10G> create or replace package my_pkg as
  2     procedure p;
  3  end my_pkg;
  4  /

Package created.

Elapsed: 00:00:00.15
scott@10G> create or replace package body my_pkg as
  2     g_global number;
  3
  4     procedure private(l_num in number) as
  5     begin
  6             dbms_output.put_line (' The value of l_num before was '||l_num);
  7             g_global := 65;
  8             dbms_output.put_line (' The value of l_num after was '||l_num);
  9     end;
 10
 11     procedure p as
 12     begin
 13             g_global := 55;
 14             private(g_global);
 15     end p;
 16
 17  end my_pkg;
 18  /

Package body created.

Elapsed: 00:00:00.03
scott@10G> exec my_pkg.p;
 The value of l_num before was 55
 The value of l_num after was 65

PL/SQL procedure successfully completed.

It comes from the fact that IN parameters are passed by reference (as a pointer).The other approach, when FORCED to use globals, assign them to another variable OR cause a temporary to be created (|| '' for strings, +0 for dates, numbers...)

scott@10G> create or replace package my_pkg as
  2     procedure p;
  3  end my_pkg;
  4  /

Package created.

Elapsed: 00:00:00.00
scott@10G> create or replace package body my_pkg as
  2     g_global number;
  3
  4     procedure private(l_num in number) as
  5     begin
  6             dbms_output.put_line (' The value of l_num before was '||l_num);
  7             g_global := 65;
  8             dbms_output.put_line (' The value of l_num after was '||l_num);
  9     end;
 10
 11     procedure p as
 12     begin
 13             g_global := 55;
 14             private(g_global+0);
 15     end p;
 16
 17  end my_pkg;
 18  /

Package body created.

Elapsed: 00:00:00.01
scott@10G> exec my_pkg.p;
 The value of l_num before was 55
 The value of l_num after was 55

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

So, one approach is either try to avoid global variables in packages or when Forced to use globals assign them to another variable OR cause a temporary to be created. as i did above.

SQLLDR Defaults to CHAR(255)

The default length of input fields is 255 characters. If your field is longer than this, you will receive an error message:

scott@10G> select dbms_metadata.get_ddl('TABLE','T') from dual;

DBMS_METADATA.GET_DDL('TABLE','T')
--------------------------------------------------------------------------

  CREATE TABLE "SCOTT"."T"
   (    "X" NUMBER,
        "Y" DATE,
        "Z" VARCHAR2(4000)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS_DATA"

load data
infile *
into table t
truncate
fields terminated by ','
(
    x                                    ,
    y     " to_date(:y,'mm/dd/yyyy ') "    ,
    z    
)

begindata   
1,02/03/2010,VERSION INFORMATION:TNS for 32-bit Windows: Version 10.1.0.2.0 - Production:Oracle Bequeath NT Protocol Adapter for 32-bit Windows: Version 10.1.0.2.0 - ProductionTime: 09-FEB-2009 11:12:21Tracing not turned on.TNS-12560: TNS:protocol adapter errorTNS:protocol adapter error

Table T, loaded from every logical record.
Insert option in effect for this table: TRUNCATE

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
X                                   FIRST     *   ,       CHARACTER           
Y                                    NEXT     *   ,       CHARACTER           
    SQL string for column : " to_date(:y,'mm/dd/yyyy ') "
Z                                    NEXT     *   ,       CHARACTER           

Record 1: Rejected - Error on table T, column Z.
Field in data file exceeds maximum length

This does not mean the data will not fit into the database column, but rather SQLLDR was expecting 255 bytes or less of input data, and received somewhat more than that. The solution is to simply use CHAR(N) in the control file, where N is big enough to accommodate the largest field length in the input file.

load data
infile *
into table t
truncate
fields terminated by ','
(
    x                                    ,
    y     " to_date(:y,'mm/dd/yyyy ') "    ,
    z     char(10000)
)

begindata   
1,02/03/2010,VERSION INFORMATION:TNS for 32-bit Windows: Version 10.1.0.2.0 - Production:Oracle Bequeath NT Protocol Adapter for 32-bit Windows: Version 10.1.0.2.0 - ProductionTime: 09-FEB-2009 11:12:21Tracing not turned on.TNS-12560: TNS:protocol adapter errorTNS:protocol adapter error


scott@10G> select x,y,length(z) from T;

         X Y          LENGTH(Z)
---------- --------- ----------
         1 03-FEB-10        274

Bind variables

Bind Variables are important because one of the design feature in Oracle is ability to reuse the optimizer plans whenever possible. whenever you submit any SQL or PL/SQL blocks to database, Oracle will look if the query is already parsed and optimized (Shared pool) if it finds then the execution plan can be reused. If it cannot find then Oracle Hard parses the query, performs security checks and optimizes the plan and so on..This not only consumes CPU but tends to lock the portion of the library cache for relatively long periods. The more people hard parsing the query increases then longer the wait.

Here is the demonstration about Not using Bind variables in multiuser environment.

scott@9iR2> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
CORE    9.2.0.8.0       Production
TNS for 32-bit Windows: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

scott@9iR2> SELECT tablespace_name, segment_space_management
  2  FROM dba_tablespaces
  3  where tablespace_name ='DATA1';

TABLESPACE_NAME                SEGMEN
------------------------------ ------
DATA1                          MANUAL

Elapsed: 00:00:00.04

create table t(x number,y number,z number) tablespace  DATA1 storage(freelists 5);

create global temporary table temp_sess
on commit preserve rows
as
select *
from v$session_event where 1 = 0;

scott@9iR2> insert into temp_sess
  2  select * from v$session_event
  3  where sid = (select sid from v$mystat where rownum = 1);

12 rows created.

Elapsed: 00:00:00.06

scott@9iR2> declare
  2     l_value number;
  3  begin
  4     for i in 1..100000
  5     loop
  6             l_value := dbms_random.value;
  7             execute immediate ' insert into t (x,y,z) values ('||l_value||','||l_value||','||l_value||')';
  8     end loop;
  9  end;
 10  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:52.95

scott@9iR2> SELECT aft.event,(nvl(aft.total_waits,0) - nvl(bfr.total_waits,0)) as total_waits
  2               (nvl(aft.time_waited,0) - nvl(bfr.time_waited,0)) as time_waited
  3  FROM V$SESSION_EVENT aft,temp_sess bfr
  4  WHERE aft.SID = (select sid from v$mystat where rownum = 1)
  5  and   aft.event = bfr.event(+)
  6  and   abs(aft.total_waits - nvl(bfr.total_waits,0)) > 0 ;

EVENT                                         TOTAL_WAITS     TIME_WAITED
---------------------------------------------------------------- ----------- -----------
latch free                                        226                          30
enqueue                                             3                            5
free buffer waits                                 1                           67
buffer busy waits                               33                           0
log file switch completion                   2                           6
db file sequential read                        8                           4
row cache lock                                 15                          31
SQL*Net message to client               8                           0
SQL*Net message from client           7                           11895

9 rows selected.

The Oracle wait interface are documented in Oracle 9i Reference manual.
This latch free is actually a latch in shared sql area.So how this can be reduced. one way by using Bind variables.

Now using Bind variables Latch Free has gone down.

scott@9iR2> declare
  2     l_value number;
  3  begin
  4     for i in 1..100000
  5     loop
  6             l_value := dbms_random.value;
  7             execute immediate ' insert into t (x,y,z) values (:x,:y,:z) ' using l_value,l_value,l_value;
  8     end loop;
  9  end;
 10  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:19.29
scott@9iR2> SELECT aft.event,(nvl(aft.total_waits,0) - nvl(bfr.total_waits,0)) as total_waits,
  2               (nvl(aft.time_waited,0) - nvl(bfr.time_waited,0)) as time_waited
  3  FROM V$SESSION_EVENT aft,temp_sess bfr
  4  WHERE aft.SID = (select sid from v$mystat where rownum = 1)
  5  and   aft.event = bfr.event(+)
  6  and   abs(aft.total_waits - nvl(bfr.total_waits,0)) > 0 ;

EVENT                                        TOTAL_WAITS       TIME_WAITED
---------------------------------------------------------------------------
latch free                                          123                           8
buffer busy waits                                1000                         350
log file switch completion                    3                               9
db file sequential read                         677                         373
SQL*Net message to client                5                            0
SQL*Net message from client            4                            4538

6 rows selected.

Elapsed: 00:00:00.04

Another way to drop this considerably is static SQL's in Pl/SQL block.

scott@9iR2> declare
  2     l_value number;
  3  begin
  4     for i in 1..100000
  5     loop
  6             l_value := dbms_random.value;
  7             insert into t (x,y,z)  values (l_value,l_value,l_value);
  8     end loop;
  9  end;
 10  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:07.55
scott@9iR2> SELECT aft.event,(nvl(aft.total_waits,0) - nvl(bfr.total_waits,0)) as total_waits,
  2               (nvl(aft.time_waited,0) - nvl(bfr.time_waited,0)) as time_waited
  3  FROM V$SESSION_EVENT aft,temp_sess bfr
  4  WHERE aft.SID = (select sid from v$mystat where rownum = 1)
  5  and   aft.event = bfr.event(+)
  6  and   abs(aft.total_waits - nvl(bfr.total_waits,0)) > 0 ;

EVENT                                      TOTAL_WAITS  TIME_WAITED
-----------------------------------------------------------------------
enqueue                                        9                           0
free buffer waits                            5                          70
write complete waits                     1                          20
buffer busy waits                           3                           0
log buffer space                             7                          64
log file switch completion               2                          18
db file sequential read                    2                           1
SQL*Net message to client           5                           0
SQL*Net message from client       4                        3020

9 rows selected.

Now when executing static sql's in Pl./Sql block ( PL/SQL will cache my cursor for me – that is one of the major advantages of PL/SQL. This insert will typically be soft parsed once per session for me if it was in a
procedure ) . The latching is totally gone.

The above demonstrates that using Bind variable is crucial to performance.

Loading LOB data that is Out of Line

A Common scenario is to have data file that contains the name of the file to load, instead of having lob data mix with structured data. we can tell sqlldr how to parse the LOB data from its structured data using LOBFILE in sqlldr.

scott@10G> desc T;
 Name              Null?    Type
 ----------------- -------- -------------
 CREATED_BY                    VARCHAR2(30)
 T_DATA                     CLOB


load data
infile *
truncate
into table t
(   
    created_by position(48:62),
    x         filler position(63:80),
    t_data lobfile(x) terminated by eof)

begindata
09/15/2009  11:30 AM               544 BUILTIN\Administrators process_3121.log
09/15/2009  12:40 PM               550 BUILTIN\Administrators process_3122.log
09/15/2009  01:23 PM               550 BUILTIN\Administrators process_3123.log
09/15/2009  01:36 PM               550 BUILTIN\Administrators process_3124.log
09/15/2009  01:50 PM               550 BUILTIN\Administrators process_3125.log
09/15/2009  01:53 PM               550 BUILTIN\Administrators process_3126.log
09/15/2009  01:58 PM               550 BUILTIN\Administrators process_3127.log
09/15/2009  01:59 PM               550 BUILTIN\Administrators process_3128.log
09/15/2009  02:47 PM               550 BUILTIN\Administrators process_3129.log
09/15/2009  02:49 PM               550 BUILTIN\Administrators process_3130.log
09/15/2009  02:51 PM               553 BUILTIN\Administrators process_3131.log
09/15/2009  03:00 PM               553 BUILTIN\Administrators process_3132.log
09/15/2009  03:06 PM               553 BUILTIN\Administrators process_3133.log
09/15/2009  03:24 PM               553 BUILTIN\Administrators process_3134.log
09/15/2009  03:30 PM               553 BUILTIN\Administrators process_3135.log
09/15/2009  03:35 PM               553 BUILTIN\Administrators process_3136.log
09/15/2009  03:42 PM               553 BUILTIN\Administrators process_3137.log
09/15/2009  03:49 PM               553 BUILTIN\Administrators process_3138.log
09/15/2009  03:54 PM               553 BUILTIN\Administrators process_3139.log
09/15/2009  04:09 PM               553 BUILTIN\Administrators process_3140.log
09/15/2009  04:35 PM               553 BUILTIN\Administrators process_3141.log
09/15/2009  04:44 PM               553 BUILTIN\Administrators process_3142.log
09/15/2009  04:49 PM               553 BUILTIN\Administrators process_3143.log
09/15/2009  04:49 PM               553 BUILTIN\Administrators process_3144.log
09/15/2009  06:18 PM               553 BUILTIN\Administrators process_3161.log
09/15/2009  06:26 PM               553 BUILTIN\Administrators process_3162.log
09/15/2009  06:34 PM               553 BUILTIN\Administrators process_3163.log
11/18/2009  01:13 PM                67 BUILTIN\Administrators process_3184.log
11/18/2009  01:21 PM                67 BUILTIN\Administrators process_3185.log
11/18/2009  01:43 PM                67 BUILTIN\Administrators process_3186.log
11/18/2009  02:48 PM                67 BUILTIN\Administrators process_3187.log
11/18/2009  03:51 PM                67 BUILTIN\Administrators process_3188.log
11/18/2009  05:01 PM                67 BUILTIN\Administrators process_3189.log
11/18/2009  05:18 PM                67 BUILTIN\Administrators process_3190.log
11/18/2009  05:34 PM                67 BUILTIN\Administrators process_3191.log
11/18/2009  05:42 PM                67 BUILTIN\Administrators process_3192.log
11/18/2009  07:33 PM                67 BUILTIN\Administrators process_3193.log
11/18/2009  08:11 PM                67 BUILTIN\Administrators process_3194.log
11/23/2009  10:31 AM               366 BUILTIN\Administrators process_3204.log
11/23/2009  10:37 AM               469 BUILTIN\Administrators process_3205.log
11/30/2009  04:36 PM                67 BUILTIN\Administrators process_3224.log
12/08/2009  10:25 PM               761 BUILTIN\Administrators process_3245.log
12/11/2009  06:23 PM                67 BUILTIN\Administrators process_3246.log
12/11/2009  06:27 PM                67 BUILTIN\Administrators process_3247.log
12/11/2009  06:41 PM                67 BUILTIN\Administrators process_3248.log
12/11/2009  06:55 PM                67 BUILTIN\Administrators process_3249.log
12/11/2009  06:56 PM                67 BUILTIN\Administrators process_3250.log
12/17/2009  08:06 PM                67 BUILTIN\Administrators process_3264.log
02/11/2010  09:27 PM                67 BUILTIN\Administrators process_3304.log
02/11/2010  09:36 PM                67 BUILTIN\Administrators process_3305.log
02/11/2010  10:48 PM                67 BUILTIN\Administrators process_3306.log
02/12/2010  12:45 PM                67 BUILTIN\Administrators process_3324.log
02/12/2010  12:51 PM                67 BUILTIN\Administrators process_3325.log
02/12/2010  05:40 PM                67 BUILTIN\Administrators process_3326.log
02/16/2010  01:28 PM                67 BUILTIN\Administrators process_3344.log

scott@10G> select created_by,dbms_lob.getlength(t_data) as lob_length from T;

CREATED_BY                     LOB_LENGTH
------------------------------ ----------
Administrators                        550
Administrators                        553
Administrators                        553
Administrators                        553
Administrators                        553
Administrators                        553
Administrators                        553
Administrators                        553
Administrators                        553
Administrators                        553
Administrators                        553
Administrators                        553
Administrators                        553
Administrators                        553
Administrators                        553
Administrators                        553
Administrators                        553
Administrators                         67
Administrators                         67
Administrators                         67
Administrators                         67
Administrators                         67
Administrators                        366
Administrators                        469
Administrators                        761
Administrators                         67
Administrators                         67
Administrators                         67
Administrators                         67
Administrators                         67
Administrators                         67
Administrators                         67
Administrators                        544
Administrators                        550
Administrators                        550
Administrators                        550
Administrators                        550
Administrators                        550
Administrators                        550
Administrators                        550
Administrators                        550
Administrators                        553
Administrators                         67
Administrators                         67
Administrators                         67
Administrators                         67
Administrators                         67
Administrators                         67
Administrators                         67
Administrators                         67
Administrators                         67
Administrators                         67
Administrators                         67
Administrators                         67
Administrators                         67

55 rows selected.

Data Unloader - Plsql utility

One thing SQLLDR does not do, and that Oracle supplies no tools for, is the unloading of data in a format understandable by SQLLDR. This would be useful for moving data from system to system without using EXP/IMP. We will develop a small PL/SQL utility that may be used to unload data on a server in a SQLLDR‐friendly format  (Also this utility has some limitations, like handling LOBS, RAW & BFile)

create or replace package pkg_unloader as

    procedure dump_data (
        p_query in varchar2,
        p_directory in varchar2 default 'DATA_PUMP_DIR',  /* default directory for data pump in oracle 10g */
        p_file_name in varchar2    default 'sample.dat',
        p_delimited in varchar2 default '|',
        p_status   out number );
   
end pkg_unloader;   
/
create or replace package body pkg_unloader as

        procedure dump_data (
            p_query in varchar2,
            p_directory in varchar2 default 'DATA_PUMP_DIR',
            p_file_name in varchar2 default 'sample.dat',
            p_delimited in varchar2 default '|',
            p_status   out number )        /* 0 - Sucess / other than 0 is error */
        as
            l_exec_status number;
            l_file_handle utl_file.file_type;
            l_desc_tab    dbms_sql.desc_tab;
            l_column_count number ;
            l_line varchar2(32760) := null;
            l_column_value varchar2(4000);           
            g_cursor number := dbms_sql.open_cursor;
            l_nls_values nls_database_parameters.value%type;
        begin
            p_status := 0;
            select value
            into l_nls_values
            from nls_database_parameters
            where parameter = 'NLS_DATE_FORMAT';
           
            execute immediate ' alter session set nls_date_format = ''ddmmyyyyhh24miss'' ';
           
            l_file_handle := utl_file.fopen (p_directory,p_file_name,'w',32760);
           
            dbms_sql.parse(g_cursor,p_query,dbms_sql.native);
            dbms_sql.describe_columns (g_cursor,l_column_count,l_desc_tab);
           
            for i in 1..l_column_count
            loop
                dbms_sql.define_column (g_cursor,i,l_column_value,4000);
            end loop;
           
            l_exec_status := dbms_sql.execute(g_cursor);
           
            while ( dbms_sql.fetch_rows(g_cursor) > 0 )
            loop
                l_line := null;
                for i in 1..l_column_count
                loop
                    dbms_sql.column_value(g_cursor,i,l_column_value);
                    l_line := l_line ||p_delimited||l_column_value;
                end loop;
                l_line := l_line || chr(13) || chr(10); /* line  terminator in windows */
                utl_file.put(l_file_handle,l_line);
            end loop;
           
            utl_file.fclose(l_file_handle);
           
            execute immediate ' alter session set nls_date_format = '''|| l_nls_values ||'''';
           
        exception
            when others then
                if dbms_sql.is_open(g_cursor) then
                    dbms_sql.close_cursor(g_cursor);
                end if;
               
                if utl_file.is_open (l_file_handle) then
                    utl_file.fclose(l_file_handle);
                end if;
                p_status := sqlcode;
                raise_application_error ( -20458,sqlerrm);
        end dump_data;
end pkg_unloader;
/

Sample output

scott@10G> declare
  2   l_status number;
  3  begin
  4     pkg_unloader.dump_data('select * from emp',
  5      'DATA_PUMP_DIR',
  6      'emp.dat',
  7      '|',
  8     l_status );
  9  end;
 10  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
scott@10G> host
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\179818>d:

D:\>cd D:\oracle\product\10.2.0\admin\10g\dpdump

D:\oracle\product\10.2.0\admin\10G\dpdump>type emp.dat
|7782|CLARK|MANAGER|7839|09061981000000|2450||10
|7839|KING|PRESIDENT||17111981000000|5000|0|10
|7934|MILLER|CLERK|7782|23011982000000|1300||10
|7844|TURNER|SALESMAN|7698|08091981000000|1500|0|30
|7788|SCOTT|ANALYST|7566|19041987000000|3000||20
|7698|BLAKE|MANAGER|7839|01051981000000|2850||30
|7566|JONES|MANAGER|7839|02041981000000|2975||20
|7499|ALLEN|SALESMAN|7698|20021981000000|1600|300|30
|7521|WARD|SALESMAN|7698|22021981000000|1250|500|30
|7902|FORD|ANALYST|7566|03121981000000|3000||20
|7654|MARTIN|SALESMAN|7698|28091981000000|1250|1400|30
|7369|SMITH|CLERK|7902|17121980000000|800||20
|7876|ADAMS|CLERK|7788|23051987000000|1100||20
|7900|JAMES|CLERK|7698|03121981000000|950||30

D:\oracle\product\10.2.0\admin\10G\dpdump>

Print Table Procedure

My first try about DBMS_SQL package. some thing i learnt newly today.

create or replace
procedure print_table(p_query in varchar2)
authid current_user
as
    l_cursor number := dbms_sql.open_cursor;
    l_sql varchar2(100) := p_query;
    l_column_count  number := 0;
    l_desctab   dbms_sql.desc_tab;
    l_column_value varchar2(4000);
    l_status number;
begin
    dbms_sql.parse(l_cursor,l_sql,dbms_sql.native);
    dbms_sql.describe_columns (l_cursor,l_column_count,l_desctab);

    for i in 1..l_column_count
    loop
        dbms_sql.define_column (l_cursor,i,l_column_value,4000);
    end loop;

    l_status := dbms_sql.execute(l_cursor);

    while ( dbms_sql.fetch_rows(l_cursor) > 0 )
    loop
        for i in 1..l_column_count
        loop
            dbms_sql.column_value (l_cursor,i,l_column_value);
            dbms_output.put_line ( rpad(' ',5,' ')||rpad(l_desctab(i).col_name,20,'-') ||' '|| l_column_value );
        end loop;
        dbms_output.put_line (' ');
    end loop;

    dbms_sql.close_cursor(l_cursor);
end print_table;


scott@10G> exec print_table (' select * from emp where rownum <= 2 ');
     EMPNO--------------- 7782
     ENAME--------------- CLARK
     JOB----------------- MANAGER
     MGR----------------- 7839
     HIREDATE------------ 09-JUN-81
     SAL----------------- 2450
     COMM----------------
     DEPTNO-------------- 10

     EMPNO--------------- 7839
     ENAME--------------- KING
     JOB----------------- PRESIDENT
     MGR-----------------
     HIREDATE------------ 17-NOV-81
     SAL----------------- 5000
     COMM---------------- 0
     DEPTNO-------------- 10

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04

Improved Tablespace Managment

Prior to Oracle9i Database, if the DEFAULT TABLESPACE was not specified when the user was created, it would default to the SYSTEM tablespace. If the user did not specify a tablespace explicitly while creating a segment, it was created in SYSTEM—provided the user had quota there, either explicitly granted or through the system privilege UNLIMITED TABLESPACE. Oracle9i alleviated this problem by allowing the DBA to specify a default, temporary tablespace for all users created without an explicit temporary tablespace clause

   In Oracle Database 10g, you can similarly specify a default tablespace for users. During database creation Or After creation, you Can make a tablespace default by issuing.

    ALTER DATABASE DEFAULT TABLESPACE tsname;

scott@10G> SELECT NAME FROM V$TABLESPACE;

NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS_DATA
TEMP
EXAMPLE
TTS_TBL
TTS_INDX

8 rows selected.

scott@10G> ALTER DATABASE DEFAULT TABLESPACE TTS_TBL;

Database altered.

scott@10G> CREATE USER TEST_USER IDENTIFIED BY TEST_USER;

User created.

scott@10G> SELECT username, default_tablespace, temporary_tablespace
  2  FROM dba_users
  3  WHERE username ='TEST_USER'
  4  /

USERNAME        DEFAULT_TABLESPACE     TEMPORARY_TABLESPACE
------------------ ------------------------------ --------------------------
TEST_USER       TTS_TBL                         TEMP

If the default Tablespace is not specified during the database creation , it defaults to SYSTEM.
But how do you know which tablespace is default for existing database?

scott@10G> SELECT property_value
  2  FROM database_properties
  3  WHERE property_name = 'DEFAULT_PERMANENT_TABLESPACE';

PROPERTY_VALUE
---------------------------------------------------------------
NEW_TBLS

It is common in data warehouse environments, typically for data mart architectures, to transport tablespaces between databases. But the source and target databases must not have tablespaces with the same names. Oralce 10g offers a Convenient solution:
You can simply rename an existing tablespace using the command: 

ALTER TABLESPACE old_name  RENAME TO  new_name;

scott@10G> ALTER TABLESPACE TTS_TBL RENAME TO NEW_TBLS;

Tablespace altered.

scott@10G> SELECT username, default_tablespace, temporary_tablespace
  2  FROM dba_users
  3  WHERE username ='TEST_USER'
  4  /

USERNAME     DEFAULT_TABLESPACE   TEMPORARY_TABLESPACE
---------------- ---------------------------  -----------------------------
TEST_USER    NEW_TBLS                     TEMP

Index Skip Scan

Index Skip Scan were Introduced in Oracle 9i 

Index Skip Scan improves index scan by non-prefix columns.
Skip scanning splits the composite index to be logically split into smaller subindexes.
The number of logical Subindexes is determined by number of distinct values of Initial column.
Skip scanning is advantage if there are few distinct values in leading column and many distinct values on non-leading key columns.

create table t as select * from all_objects;
create index t_ind on t(object_type,object_id);
exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>'T',estimate_percent=>100,cascade=>true,method_opt=>'for all columns size 254');

scott@10G> select object_type,count(*)
  2  from T
  3  group by object_type;

OBJECT_TYPE           COUNT(*)
-------------------            ----------
CONSUMER GROUP       2
INDEX PARTITION           446
JAVA DATA                     306
RULE                               7
SCHEDULE                     1
SEQUENCE                     227
TABLE PARTITION          298
OPERATOR                    57
PROCEDURE                 131
WINDOW                        2
LIBRARY                        152
LOB                                6
PACKAGE                      895
PACKAGE BODY           836
PROGRAM                     12
RULE SET                      26
CONTEXT                      5
JAVA RESOURCE          772
TYPE BODY                  176
XML SCHEMA                25
DIRECTORY                  5
JOB CLASS                   2
MATERIALIZED VIEW    2
TRIGGER                      261
FUNCTION                    294
INDEX                            2143
INDEXTYPE                   10
JAVA CLASS                 16420
SYNONYM                     20110
TABLE                           1948
VIEW                              3761
WINDOW GROUP          1
CLUSTER                       10
EVALUATION CONTEXT    14
JOB                                11
TYPE                            2013

36 rows selected.

So, splitting this Composite Index will result into 36 logical subindexes one for each object_type. 

scott@10G> select *
  2  from t
  3  where object_id between 100 and 200;

100 rows selected.

Elapsed: 00:00:00.03

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |   100 |  9300 |   106   (1)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |   100 |  9300 |   106   (1)| 00:00:02 |
|*  2 |   INDEX SKIP SCAN           | T_IND |    99 |       |   101   (1)| 00:00:02 |
-------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID">=100 AND "OBJECT_ID"<=200)
       filter("OBJECT_ID"<=200 AND "OBJECT_ID">=100)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         28  consistent gets
          0  physical reads
          0  redo size
       3890  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)
        100  rows processed

Explain Plan - The Oldest Bug in Oracle

Under what conditions, autotrace and Explain plan cannot give the correct explain plan of a Sql?

create table t as select a.*,1 as id from all_objects a where rownum = 1;
create index t_ind on t(id);
alter session set sql_trace = true;
select * from t where id = 1;
insert into t select a.*,1 as id from all_objects a;
select * from t where id = 1;
alter session set sql_trace = false;

********************************************************************************
select *
from t
where id = 1

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

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54  (SCOTT)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS BY INDEX ROWID T (cr=2 pr=0 pw=0 time=19 us)
      1   INDEX RANGE SCAN T_IND (cr=1 pr=0 pw=0 time=14 us)(object id 64947)


Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
      1   TABLE ACCESS (FULL) OF 'T' (TABLE)

********************************************************************************
select *
from  t
where id = 1

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      344      0.10       0.11          0       1534          0       51396
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      346      0.10       0.11          0       1534          0       51396

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 54  (SCOTT)

Rows     Row Source Operation
-------  ---------------------------------------------------
  51396  TABLE ACCESS BY INDEX ROWID T (cr=1534 pr=0 pw=0 time=102827 us)
  51396   INDEX RANGE SCAN T_IND (cr=474 pr=0 pw=0 time=25 us)(object id 64947)


Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
  51396   TABLE ACCESS (FULL) OF 'T' (TABLE)

********************************************************************************

Now how the Row Source Operation (what is called reality) differ from Execution Plan (what is called estimation). what happened here was that the  Row Source Operation captured in the Trace file at the time of execution - reflects what REALLY took place as the query executed. The sequence of operation followed for the query is

1) Load a single row into the table T
2) ran the query against T - that did the hard parse using dynamic sampling sampled the table at run time and founded that using Index leads to efficient execution.
3) loaded more data with id =1 into table T
4) ran the same query from step#2 - that did soft parse and reused the plan generated back when only one row existed in the table. that leads to inefficient execution.
5) executing TKPROF with explain = / shows an entierly different execution plan. This is because explain plan always does hard parse - It dynamically sampled the table again - founded it to be large for id =1 - it would full scan. How ever the real execution of the query Index range scanned by using the plan available in shared pool.

An important note for this example - the placement of the ALTER SESSION SET SQL_TRACE=TRUE is important. As an exercise - move it to just be before the second execution of the query and you'll find the query is Hard parsed and the Row Source Operation in the Tkprof is Full table scan.

drop table t purge;
create table t as select a.*,1 as id from all_objects a where rownum = 1;
create index t_ind on t(id);
select * from t where id = 1;
insert into t select a.*,1 as id from all_objects a;
alter session set sql_trace = true;
select * from t where id = 1;
alter session set sql_trace = false;

********************************************************************************
select *
from
 t where id = 1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          2          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      344      0.10       0.06          0       1093          0       51397
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      346      0.10       0.07          0       1095          0       51397

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54  (SCOTT)

Rows     Row Source Operation
-------  ---------------------------------------------------
  51397  TABLE ACCESS FULL T (cr=1093 pr=0 pw=0 time=34 us)


Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
  51397   TABLE ACCESS (FULL) OF 'T' (TABLE)

********************************************************************************