You may use "Truncate table table_name REUSE STORAGE" to make sure that you don't waste any space.

if there were spaces under the high water mark, and the the direct load only using the space above the high water mark, why it is not considered as wastful of the space? since the HWM can not be released till truncating it, then the HWM will increase every time as the direct loading.

What if all I do is:
1. Direct path insert
2. Convention path delete

Will this waste space?

Will the APPEND hint extend the datafile even though there is available space in that datafile ?

Tom

As usual, your valuable insights are very much appreciated.

Hypothetically. If I used 100M for conventional path inserts. HWM now is 100M. Then do another 100M of direct path inserts. And do another 100M of conventional inserts. What is the HWM now?

Thanks so much

Hi Tom,

I understand from your answer that the white space ("wasted") should be minimal. We experienced something a bit more than minimal though.

From the documentation, after we saw a table growing bigger than the data warranted.

"Parallel DML Tip 2: Direct-Path INSERT
The append mode is the default during a parallel insert: data is always inserted
into a new block which is allocated to the table. Therefore the APPEND hint is
optional. You should use append mode to increase the speed of INSERT operations,
but not when space utilization needs to be optimized. You can use NOAPPEND to
override append mode."

"High Water Mark
The high water mark is the boundary between used and unused space in a segment.
As requests for new free blocks that cannot be satisfied by existing free lists
are received, the block to which the high water mark points becomes a used
block, and the high water mark is advanced to the next block. In other words,
the segment space to the left of the high water mark is used, and the space to
the right of it is unused."

Armed with this knowledge, and knowing we were doing parallel MERGEs, and our
tablespace is defined as

Property Value
--------------------------------------
- BLOCK_SIZE 8192
- INITIAL_EXTENT 1048576
- NEXT_EXTENT 1048576
- MIN_EXTENTS 1
- MAX_EXTENTS 2147483645
- PCT_INCREASE 0
- MIN_EXTLEN 1048576
- STATUS ONLINE
- CONTENTS PERMANENT
- LOGGING NOLOGGING
- FORCE_LOGGING NO
- EXTENT_MANAGEMENT LOCAL
- ALLOCATION_TYPE UNIFORM
- PLUGGED_IN NO
- SEGMENT_SPACE_MANAGEMENT MANUAL
- DEF_TAB_COMPRESSION DISABLED

We did the following tests:

--#########################################################################
Connected to Oracle9i Enterprise Edition Release 9.2.0.4.0
SET ECHO ON;
COLUMN segment_name FORMAT a12;

DROP TABLE src;
CREATE TABLE src (
PK INTEGER NOT NULL,
COL INTEGER);
ALTER TABLE src ADD CONSTRAINT PK_src PRIMARY KEY (PK) USING INDEX;

DROP TABLE trgt;
CREATE TABLE trgt (
PK INTEGER NOT NULL,
COL INTEGER);
ALTER TABLE trgt ADD CONSTRAINT PK_trgt PRIMARY KEY (PK) USING INDEX;

DECLARE
i INTEGER;
BEGIN
FOR i IN 1 .. 8 LOOP
INSERT INTO src VALUES (i,i);
END LOOP;
COMMIT;
END;
/

SELECT segment_name, SUM(bytes) bytes, SUM(blocks) blocks, COUNT(*) ext_cnt
FROM user_extents ue
WHERE segment_name = 'TRGT'
GROUP BY segment_name;

SEGMENT_NAME BYTES BLOCKS EXT_CNT
------------ ---------- ---------- ----------
TRGT 1048576 128 1

ALTER SESSION ENABLE PARALLEL DML;

DECLARE
i INTEGER;
BEGIN
FOR i IN 1 .. 2 LOOP
MERGE /*+ PARALLEL (mt,2) */ INTO trgt mt
USING
(SELECT *
FROM src
WHERE src.pk = i) ms
ON
(mt.pk = ms.pk)
WHEN MATCHED THEN UPDATE SET
mt.COL = ms.COL
WHEN NOT MATCHED THEN INSERT (mt.pk,mt.COL)
VALUES (ms.pk,ms.COL);
COMMIT;
END LOOP;
END;
/

-- See above for actual SQL
SEGMENT_NAME BYTES BLOCKS EXT_CNT
------------ ---------- ---------- ----------
TRGT 3145728 384 3
--#########################################################################

OK, so it does seem to be the implicit APPEND hint.
1) However, why is it adding an extent per insert/commit, when there are plenty of blocks empty?
2) Is the HWM kept at extent level instead of block level?

So, following up on this, is the MERGE different than the INSERT?

--#########################################################################
DECLARE
i INTEGER;
BEGIN
FOR i IN 3 .. 4 LOOP
INSERT /*+ PARALLEL(mt,2) */ INTO trgt mt
SELECT *
FROM src

WHERE src.pk = i;
COMMIT;
END LOOP;
END;
/

-- See above for actual SQL
SEGMENT_NAME BYTES BLOCKS EXT_CNT
------------ ---------- ---------- ----------
TRGT 5242880 640 5
--#########################################################################

Well, that's even more confusing.
3) Why does INSERT PARALLEL act different from MERGE PARALLEL for inserts?

Well, OK, does the NOAPPEND hint avoid extents from being added, as the documentation suggests?

--#########################################################################
DECLARE
i INTEGER;
BEGIN
FOR i IN 5 .. 6 LOOP
MERGE /*+ NOAPPEND PARALLEL (mt,2) */ INTO trgt mt
USING
(SELECT *
FROM src
WHERE src.pk = i) ms
ON
(mt.pk = ms.pk)
WHEN MATCHED THEN UPDATE SET
mt.COL = ms.COL
WHEN NOT MATCHED THEN INSERT (mt.pk,mt.COL)
VALUES (ms.pk,ms.COL);
COMMIT;
END LOOP;
END;
/

-- See above for actual SQL
SEGMENT_NAME BYTES BLOCKS EXT_CNT
------------ ---------- ---------- ----------
TRGT 5242880 640 5
--#########################################################################

Yes, NOAPPEND hint does avoid extents from being added.
So what does an INSERT APPEND do?

--#########################################################################
DECLARE
i INTEGER;
BEGIN
FOR i IN 7 .. 8 LOOP
INSERT /*+ APPEND */ INTO trgt mt
SELECT *
FROM src
WHERE src.pk = i;
COMMIT;
END LOOP;
END;
/

-- See above for actual SQL
SEGMENT_NAME BYTES BLOCKS EXT_CNT
------------ ---------- ---------- ----------
TRGT 6291456 768 6
--#########################################################################

So, INSERT PARALLEL and INSERT APPEND are acting the same, but INSERT PARALLEL and MERGE PARALLEL, although we're only doing inserts, are not acting the same.

Any clarification of the behaviour observed is appreciated.

Hi Tom,
i have a small question regarding the append hint.
i usally do it like this,which i learned from you.
mark indexes unuasble,
disable primary key,
alter table nologging,
do an insert /*+ append */ into table (select ..)
enable primary key,
rebuild indexes nologging.

the issue here is one of my scripts throw an error
'Unable to Extend tablespace SORT'
and my DBA asks me not to mark indexes unusable and do the insert append.do you think that this was the cause for the problem.i told him that the error is because of something else.
Thanks,
M S Reddy.

Tom,
Thanks a lot for the quick response.
Actually i think it is the problem with the select ,it is doing a full scan for quite a few tables.i will try to add indexes on those columns which the query is using and then see if it does the full table scan.do you think indexing those columns will eliminate the full scan and do an index scan.we are doing a join on some varchar2 columns.if i add indexes on them will it help me to eliminate this error
Thanks,
M S Reddy

Tom,
I did not understand your previous reply.
you mean to say
TABLE ACCESS FULL -- GOOD
INDEX FULL SCAN -- BAD
or is it the other way around.
when i add indexes to those columns that iam using in the join
TABLE ACCESS FULL is changed to
INDEX FULL SCAN in the explain plan.
Is that how it should behave?
Thanks for the help!
Thanks,
M S Reddy

Tom,
what does this do ? SYS_OP_MAP_NONNULL
we are using it in an inline view update
and has SYS_OP_MAP_NONNULL(and column name)
what is the advantage?

Thanks,
M S Reddy

Tom

I have one table TAB1(x NUMBER(4), y NUMBER(10), Z VARCHAR2(10));
column x and Y are the part of primary key and table is partitioned on X. when I am using the direct insert it takes 3 hoursto load 3M records.
INSERT /*+ APPEND PARALLEL(TAB1,8) */ INTO TAB1
SELECT /*+ PARALLEL(a,8) */FROm TAB2;
TAB2 has the same structure as TAB1.

while if I use PL/SQL table then it finishes in 1 hour 30 mins

why it is so.

I mean if I insert 100,000 records using the ProC code then it is faster the the direct insert.
Why this is happening.

Can you please tell me to make the insert faster what all the things we should consider like partition size etc.

Thanks

I mean if I insert 100,000 records in loop using the ProC code then it is faster the then
direct insert.
Why this is happening.

Even through proc it is take approx 3 min for 100,000 records.

Can you please tell me to make the insert faster what all the things we should
consider like partition size etc.

Thanks

Oracle9i Database Administrator's Guide Release 2 (9.2)
chapter 5 
Managing Oracle Processes 5-19 
said that :

-------------------------------------------------
Altering Parallel Execution for a Session 
Disabling Parallel Execution 
All subsequent DML (INSERT, UPDATE, DELETE), DDL (CREATE, ALTER), or query (SELECT) statements will not be parallelized after an ALTER SESSION DISABLE PARALLEL DML|DDL|QUERY statement is issued. They will be executed serially, regardless of any PARALLEL clause or parallel hints associated with the statement. The following statement disables parallel DDL: 
ALTER SESSION DISABLE PARALLEL DDL; 
-------------------------------------------------


then do the following operation:

SQL> create table test as select * from dba_objects; 

表已创建。 

SQL> set autot on exp 

SQL> select count(*) from test; 

COUNT(*) 
---------- 
37279 


Execution Plan 
---------------------------------------------------------- 
0 SELECT STATEMENT Optimizer=CHOOSE 
1 0 SORT (AGGREGATE) 
2 1 TABLE ACCESS (FULL) OF 'TEST' 


SQL> select /*+ parallel(test, 2) */ count(*) from test; 

COUNT(*) 
---------- 
37279 


Execution Plan 
---------------------------------------------------------- 
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=25 Card=1) 
1 0 SORT (AGGREGATE) 
2 1 SORT* (AGGREGATE) :Q37000 
3 2 TABLE ACCESS* (FULL) OF 'TEST' (Cost=25 Card=41331) :Q37000 


2 PARALLEL_TO_SERIAL SELECT /*+ PIV_SSF */ SYS_OP_MSR(COUNT(*)) F 
ROM (SELECT /*+ NO_EXPAND ROWID(A2) 

3 PARALLEL_COMBINED_WITH_PARENT 

SQL> alter session disable parallel query; 

会话已更改。 

SQL> select /*+ parallel(test, 2) */ count(*) from test; 

COUNT(*) 
---------- 
37279 


Execution Plan 
---------------------------------------------------------- 
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=50 Card=1) 
1 0 SORT (AGGREGATE) 
2 1 SORT* (AGGREGATE) :Q39000 
3 2 TABLE ACCESS* (FULL) OF 'TEST' (Cost=50 Card=41331) :Q39000 


2 PARALLEL_TO_SERIAL SELECT /*+ PIV_SSF */ SYS_OP_MSR(COUNT(*)) F 
ROM (SELECT /*+ NO_EXPAND ROWID(A2) 

3 PARALLEL_COMBINED_WITH_PARENT 

SQL> alter system flush shared_pool; 

系统已更改。 

SQL> select /*+ parallel(test, 2) */ count(*) from test; 

COUNT(*) 
---------- 
37279 


Execution Plan 
---------------------------------------------------------- 
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=50 Card=1) 
1 0 SORT (AGGREGATE) 
2 1 SORT* (AGGREGATE) :Q41000 
3 2 TABLE ACCESS* (FULL) OF 'TEST' (Cost=50 Card=41331) :Q41000 


2 PARALLEL_TO_SERIAL SELECT /*+ PIV_SSF */ SYS_OP_MSR(COUNT(*)) F 
ROM (SELECT /*+ NO_EXPAND ROWID(A2) 

3 PARALLEL_COMBINED_WITH_PARENT 

相关参数 
SQL> show parameter parallel 

NAME TYPE VALUE 
------------------------------------ ----------- ------------------------------ 
fast_start_parallel_rollback string LOW 
log_parallelism integer 1 
parallel_adaptive_multi_user boolean TRUE 
parallel_automatic_tuning boolean TRUE 
parallel_execution_message_size integer 4096 
parallel_instance_group string 
parallel_max_servers integer 10 
parallel_min_percent integer 0 
parallel_min_servers integer 2 
parallel_server boolean FALSE 
parallel_server_instances integer 1 
parallel_threads_per_cpu integer 2 
recovery_parallelism integer 0 
SQL>

what's wrong ? or the document's error?

Thank you!
 

Hi Tom,
Can you please tell me what is the use of this HINT.

/*+BYPASS_UJVC*/ .

Thanks,
M S Reddy

Hi tom,
I have a question on this hint.
Do you think using this hint in a query that uses
a dblink would cause any error and bring down the database.

Thanks,
M S Reddy.

Would the append hint be used in the following SQL statement? The statement involves a dblink.

INSERT /*+ APPEND */ INTO t1
SELECT * FROM t2@dblink;

I am a bit stuck with my loading approach.
It involves:

(1)Initial load from a production database into reporting data mart
(2)Inserts/Updates thereafter

As per your suggestion in this site, I have used INSERT /*+ APPEND */ to perform the initial load after disabling constraints/indexes and re-enabling them after load. The approach was fantastic because the tables were empty initially.

Now, should I adopt the same approach for delta updates or use conventional insert/update to apply new changes. We are talking of inserting/updating few thousand records on a daily basis.

Please advise.

Thanks

Dear Tom,

The PL/SQL runs extremely fast after I created the index for MTL_MATERIAL_TRANSACTIONS table. Apparently, this statment caused the bottleneck for the whole program. A part from this problem, which part of the sql do you I think I can improve further. Thanks in advance. Another query is how many time this SQL executed (MAX(DECODE(xxx) || MAX(DECODE(XXX)).

select substr(a.index_name,1,30) index_name,
max(decode( b.column_position, 1, substr(b.column_name,1,30),NULL )) ||
max(decode( b.column_position, 2, ', '||
substr(b.column_name,1,30), NULL ))
from dba_indexes a, dba_ind_columns b
where a.owner = 'INV'
and a.table_name = UPPER('MTL_MATERIAL_TRANSACTIONS')
and b.table_name = a.table_name
and b.table_owner = a.owner
and a.index_name = b.index_name
group by substr(a.index_name,1,30), a.uniqueness

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.13 0.13 0 3 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 3 0.72 0.72 0 32756 0 20
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.85 0.85 0 32759 0 20

Rgds
SHGoh

Tom,

We are using Siebel, and the database use "RULE" based optimizer.

The following sql runs extremely slow, takes more than 7 min. There are 4.66 million rows in table S_POSTN_CON and 1.38 million rows in S_CONTACT. There is an index on S_CONTACT (row_id) column, and a composite index (CON_ID,POSTN_ID,CONFLICT_ID)on table S_POSTN_CON.

SELECT DISTINCT c.row_id, c.x_me_number, pc.last_call_dt, pc.pr_addr_id
FROM siebel.s_contact c, siebel.s_postn_con pc
WHERE c.row_id = pc.con_id
AND c.x_me_number = '123456789'

The problem is that from the explain plan, the first step is the full table scan for S_POSTN_CON. Is there anyway to modify the sql and let it take advantage of the composite index on S_POSTN_CON table?

6 SELECT STATEMENT
5 SORT [UNIQUE]
4 NESTED LOOPS
1 SIEBEL.S_POSTN_CON TABLE ACCESS [FULL]
3 SIEBEL.S_CONTACT TABLE ACCESS [BY INDEX ROWID]
2 SIEBEL.S_CONTACT_P1 INDEX [UNIQUE SCAN]

Thanks,
Belinda

seems to me an index on x_me_number is what you want to "make this speedy"?

but if you can, reverse the order of the tables in the from clause.  Right now, they are a "tie" for driving tables.  the brain dead rbo reads the from list backwards -- picking PC to be the driving table:

ops$tkyte@ORA9IR2> set autotrace traceonly explain
ops$tkyte@ORA9IR2> SELECT DISTINCT c.row_id, c.x_me_number, pc.last_call_dt, pc.pr_addr_id
  2  FROM t1 c, t2 pc
  3  WHERE c.row_id = pc.con_id
  4  AND c.x_me_number = '123456789'
  5  /
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (UNIQUE)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'T1'
   3    2       NESTED LOOPS
   4    3         TABLE ACCESS (FULL) OF 'T2'
   5    3         INDEX (RANGE SCAN) OF 'T1_IDX' (NON-UNIQUE)
 
 
 
ops$tkyte@ORA9IR2> SELECT DISTINCT c.row_id, c.x_me_number, pc.last_call_dt, pc.pr_addr_id
  2  FROM t2 pc, t1 c
  3  WHERE c.row_id = pc.con_id
  4  AND c.x_me_number = '123456789'
  5  /
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (UNIQUE)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'T2'
   3    2       NESTED LOOPS
   4    3         TABLE ACCESS (FULL) OF 'T1'
   5    3         INDEX (RANGE SCAN) OF 'T2_IDX' (NON-UNIQUE)
 
 
 
ops$tkyte@ORA9IR2> set autotrace off
 

Dear Tom,

How many time the SQL executed (MAX(DECODE(a.column1) || MAX(DECODE(a.column1)) internally. Both also select the info from the same table and column. Thanks.

select substr(a.index_name,1,30),
max(decode( b.column_position, 1, substr(b.column_name,1,30),NULL )) ||
max(decode( b.column_position, 2, ', '||
substr(b.column_name,1,30), NULL ))
from dba_indexes a, dba_ind_columns b
where a.owner = 'INV'
and a.table_name = UPPER('MTL_MATERIAL_TRANSACTIONS')
and b.table_name = a.table_name
and b.table_owner = a.owner
and a.index_name = b.index_name
group by substr(a.index_name,1,30), a.uniqueness

Rgds
SHGoh

Tom,
Followed your suggestion to reverse the sequence of the tables at the FROM clause, the query ran in 15 seconds instead of 7.5 minutes, marvelous improvement! but it's still not speedy to the user, so we might need to create an index. thank you so much for your help.

Hello Tom,

Hope you are doing well.
I've a question with what exactly happens when we do something like

insert /*+ append parallel(z,4) */ into z
select /*+ parallel(tab,8) */ * from remote_tab@db_link tab;

I've 4 CPUs in Target (local) DB & 8 CPUs in Source (Remote) DB.
How does oracle fetches data from the remote database when i say /*+ parallel */ ??
Does it spawan diferent processes in both Source & Target DBs ??

Thanks as always for all the great work you are doing.

Tom,

We are using Siebel and preformance is constantly a problem.
Is any way you see that can improve the performance of the following query? it is doing full table scan for the table
SIEBEL.S_CONTACT_XM CXM, any way we can bypass the full tablescan? especially without adding an index? as it's not easy to add an index to production without fully tested.
There are almost 13 million rows in the S_CONTACT_XM table. currently there is a composite index on the following columns for this table(PAR_ROW_ID,ATTRIB_08, ATTRIB_02)

Query Plan
---------------------------------------------------------
SELECT STATEMENT Cost =
SORT GROUP BY
FILTER
NESTED LOOPS OUTER
FILTER
NESTED LOOPS OUTER
FILTER
NESTED LOOPS OUTER
NESTED LOOPS OUTER
NESTED LOOPS
NESTED LOOPS
TABLE ACCESS FULL S_CONTACT_XM
TABLE ACCESS BY INDEX ROWID S_CONTACT
INDEX UNIQUE SCAN S_CONTACT_P1
TABLE ACCESS BY INDEX ROWID S_CON_ADDR
INDEX RANGE SCAN S_CON_ADDR_F1
TABLE ACCESS BY INDEX ROWID S_STATE_LIC_LS
INDEX RANGE SCAN S_STATE_LIC_LS_F1
TABLE ACCESS BY INDEX ROWID S_POSTN_CON
INDEX RANGE SCAN S_POSTN_CON_U1
TABLE ACCESS BY INDEX ROWID S_ADDR_PER
INDEX UNIQUE SCAN S_ADDR_PER_P1
TABLE ACCESS BY INDEX ROWID S_POSTN
INDEX UNIQUE SCAN S_POSTN_P1

24 rows selected.

Query:

SELECT C.row_id,
C.X_PLACE_FIELD1 JJID,
ca.ROW_ID c_row_id,
L.LICENSE_NUM sln,
p.name position,
TO_CHAR(SYSDATE,'MMDDYYYY')trans_date,
MAX(NVL(pc.X_REP_TARGET,'N')) rep_target,
PC.X_FLAG_1 ho_target,
P.name,
C.x_place_field_10 ods_fst_name,
C.csn mid_name,
C.x_place_field5 ods_Last_name,
C.per_title per_title,
C.per_title_suffix per_title_suffix,
AD.ADDR addr,
AD.X_ADDR_LINE2 addr2,
AD.CITY city,
AD.STATE state,
AD.ZIPCODE zip,
C.X_IMS_NUMBER ims_num
FROM SIEBEL.S_CONTACT C,
SIEBEL.S_POSTN_CON PC,
SIEBEL.S_STATE_LIC_LS L,
SIEBEL.S_POSTN P,
SIEBEL.S_ADDR_PER AD,
SIEBEL.S_CON_ADDR CA,
SIEBEL.S_CONTACT_XM CXM
WHERE C.ROW_ID = PC.CON_ID(+)
AND PC.POSTN_ID = P.ROW_ID(+)
AND PC.PR_ADDR_ID = AD.ROW_ID(+)
AND AD.STATE = L.STATE_CD
AND C.ROW_ID = L.CONTACT_ID (+)
AND C.ROW_ID = CA.CONTACT_ID
AND CA.ADDR_PER_ID = AD.ROW_ID
AND C.ROW_ID = CXM.PAR_ROW_ID
AND NVL(CA.X_DEL_FLG,'N') = 'N'
AND PC.X_FLAG_1 = 'Y'
AND CXM.ATTRIB_08 = 'Y'
AND SUBSTR(P.NAME,1,9) IN ('100-07-24',
'100-0T-24',
'100-0T-22',
'100-07-22',
'100-0G-62')
GROUP BY
C.row_id,
C.X_PLACE_FIELD1,
CA.ROW_ID,
L.LICENSE_NUM,
P.name,
SYSDATE,
PC.X_REP_TARGET,
PC.X_FLAG_1,
P.name,
C.x_place_field_10,
C.csn,
C.x_place_field5,
C.per_title,
C.per_title_suffix,
AD.ADDR,
AD.X_ADDR_LINE2,
AD.CITY,
AD.STATE,
AD.ZIPCODE,
C.X_IMS_NUMBER

thanks,

Hi Tom,

Good morning.
The test case what i did is, without enabling PDML I did the following & the processes spawn are shown below.

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.4.0 - 64bit Production

SQL> set timing on
SQL> set autotrace on
SQL> create table z nologging as select * from  dba_objects where 1=0;

Table created.

Elapsed: 00:00:00.08

SQL> insert /*+ append parallel(z,4) */ into z
  2  select /*+ parallel (tab,8) */ * from z@DEV4_TO_ST4.WORLD tab;

1144064 rows created.

Elapsed: 00:00:20.89

Execution Plan
----------------------------------------------------------
ERROR:
ORA-12840: cannot access a remote table after parallel/insert direct load txn


SP2-0612: Error generating AUTOTRACE EXPLAIN report

Statistics
----------------------------------------------------------
       3072  recursive calls
        384  db block gets
       1048  consistent gets
          8  physical reads
      77776  redo size
        633  bytes sent via SQL*Net to client
        541  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
         17  sorts (memory)
          0  sorts (disk)
    1144064  rows processed

SQL> rollback;

Rollback complete.

Elapsed: 00:00:00.00
+++++++++++++++++++++++++++++++++++++++++++++++++++++

$ > ps -ef |grep p0

oracle 20270     1 23 09:46:49 ?    0:00 ora_p001_gst4
oracle  5626     1  0  Jun 28  ?    24:00 ora_snp0_gst4
oracle 20268     1  8 09:46:49 ?    0:00 ora_p000_gst4
oracle 20274     1  9 09:46:49 ?    0:00 ora_p003_gst4
oracle  8067     1  0  Jul  2  ?    9:33 ora_snp0_gdev4
oracle 20276     1 22 09:46:49 ?    0:00 ora_p004_gst4
oracle 20272     1  1 09:46:49 ?    0:00 ora_p002_gst4

+++++++++++++++++++++++++++++++++++++++++++++++++++++
So we can see 0 process of LOCAL DB (gdev4) & 5 processes of REMOTE DB (gst4). How comes? Well thats not my real question though.

Then i enabled PDML & did the exact same thing.


SQL> alter session enable parallel dml;

Session altered.

Elapsed: 00:00:00.00

SQL> insert /*+ append parallel(z,4) */ into z
  2  select /*+ parallel (tab,8) */ * from z@DEV4_TO_ST4.WORLD tab;

1144064 rows created.

Elapsed: 00:00:37.43

Execution Plan
----------------------------------------------------------
ERROR:
ORA-12840: cannot access a remote table after parallel/insert direct load txn


SP2-0612: Error generating AUTOTRACE EXPLAIN report

Statistics
----------------------------------------------------------
       2041  recursive calls
        225  db block gets
        599  consistent gets
          0  physical reads
      56656  redo size
        633  bytes sent via SQL*Net to client
        541  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
    1144064  rows processed

+++++++++++++++++++++++++++++++++++++++++++++++++++++
$ ps -ef | grep p0

oracle 20663     1 27 09:50:10 ?         0:04 ora_p001_gdev4
oracle 20270     1  7 09:46:49 ?         0:04 ora_p001_gst4
oracle  5626     1  0  Jun 28  ?        24:00 ora_snp0_gst4
oracle 20665     1 34 09:50:10 ?         0:04 ora_p002_gdev4
oracle 20661     1 18 09:50:09 ?         0:01 ora_p000_gdev4
oracle 20268     1 34 09:46:49 ?         0:04 ora_p000_gst4
oracle 20274     1 23 09:46:49 ?         0:03 ora_p003_gst4
oracle  8067     1  0  Jul  2  ?         9:33 ora_snp0_gdev4
oracle 20276     1  7 09:46:49 ?         0:02 ora_p004_gst4
oracle 20272     1  5 09:46:49 ?         0:02 ora_p002_gst4
oracle 20667     1 15 09:50:10 ?         0:01 ora_p003_gdev4

So we can see again 5 processes of REMOTE DB & NOW 4 processes of LOCAL DB.
But inspite of that the time taken by this is MORE than that of previos where PDML was not enabled.

WHat I'm missing?

Both my LOCAL & REMOTE DBs are on same HP machine. Is this the reason?

Thanks very much as always Tom. 

Tom

Thanks. It's clear to me now.

Hi Tom
We have a lot of Insert/*+ append */ in our procedures. We dont truncate the data from the tables at all. We delete historical data.I think we might hit a problem sometime in the future with High Water Mark. Whats the best way to reset it? I can think of export/import , moving tables across tablespaces or creating a temp table ,move data from original table to temp table ,truncate the original table and then do an insert append to the original table from temp table? please suggest

Tom,
Thanks for the suggestion. followd your guidance, we modified the query to remove all the outer joins, and then move the table S_POSTN (which has 17700 rows) to the last of the table list, got the result back in 10 min (as opposed results never came back), the plan shows full table scan of S_POSTN. at least the user can get the report done now.
If we keep the old sequence of the table list, the explain plan still shows full table scan of the big table S_CONTACT_XM.

New:
FROM SIEBEL.S_CONTACT C,
SIEBEL.S_POSTN_CON PC,
SIEBEL.S_CONTACT_XM CXM,
SIEBEL.S_STATE_LIC_LS L,
SIEBEL.S_ADDR_PER AD,
SIEBEL.S_CON_ADDR CA,
SIEBEL.S_POSTN P
WHERE C.ROW_ID = PC.CON_ID
AND PC.POSTN_ID = P.ROW_ID
AND PC.PR_ADDR_ID = AD.ROW_ID
AND AD.STATE = L.STATE_CD
AND C.ROW_ID = L.CONTACT_ID
AND C.ROW_ID = CA.CONTACT_ID
AND CA.ADDR_PER_ID = AD.ROW_ID
AND CXM.PAR_ROW_ID = C.ROW_ID
AND NVL(ca.X_DEL_FLG,'N') = 'N'
AND cxm.ATTRIB_08 = 'Y'
AND PC.X_FLAG_1 = 'Y'
AND SUBSTR(P.NAME,1,9) IN ('100-07-24',
'100-0T-24',
'100-0T-22',
'100-07-22',
'100-0G-62')
GROUP BY
c.row_id,
C.X_PLACE_FIELD1,
ca.ROW_ID,
L.LICENSE_NUM,
p.name,
SYSDATE,
pc.X_REP_TARGET,
PC.X_FLAG_1,
p.name,
C.x_place_field_10,
c.csn,
C.x_place_field5,
c.per_title,
c.per_title_suffix,
AD.ADDR,
ad.X_ADDR_LINE2,
AD.CITY,
AD.STATE,
AD.ZIPCODE,
C.X_IMS_NUMBER


thanks very much again for your help.

Tom,

I have the folowing query which do full table scan for all the tables involved and taking 3.5 hours to finish, the table t2 (S_EVT_ACT) is huge (26 million rows) (Optimizer: rule)

SELECT /*+ USE_HASH (t1 t2 t3 t4 t5 t6 t7) */ t1.name "c1",
t2.appt_start_dt "c2", t2.todo_cd "c3", t2.subtype_cd "c4",
t3.last_name "c5", t3.fst_name "c6", t3.x_flag_3 "c7",
t3.x_ims_number "c8", t3.x_me_number "c9", t3.x_pri_spec "c10",
t3.x_pri_spec_jan "c11", t3.x_place_field1 "c12", t4.explanation "c13",
t4.discussion "c14", t5.x_mat_usd_1 "c15", t5.x_mat_usd_2 "c16",
t5.x_phy_resp "c17", decode(substr(t1.name, 1, 3), '100', '07', '101',
'08', NULL) || substr(t1.name, 5, 2) "c18", decode(substr(t1.name, 1, 3),
'100', '07', '101', '08', NULL) || substr(t1.name, 8, 7) "c19",
decode(substr(t1.name, 1, 3), '100', '07', '101', '08', NULL) ||
substr(t1.name, 8, 2) "c20", decode(substr(t1.name, 1, 3), '100', '07',
'101', '08', NULL) || substr(t1.name, 8, 5) "c21", t6.specialty "c22",
decode(substr(t1.name, 1, 3), '100', '07', '101', '08', NULL) "c23"
FROM siebel.s_postn t1, siebel.s_evt_act t2, siebel.s_contact t3,
siebel.s_act_prdint t7, siebel.s_act_prod_iss t5, siebel.s_iss t4,
siebel.s_postn_con t6
WHERE t1.row_id = t2.owner_postn_id
AND t2.target_per_id = t3.row_id
AND t2.row_id = t7.activity_id
AND t7.row_id = t5.act_prdint_id
AND t5.iss_id = t4.row_id
AND t6.postn_id = t1.row_id
AND substr(t1.name, 1, 3) IN ('100', '101')
AND substr(t1.name, 5, 2) <> '00'
AND t6.con_id = t3.row_id

The explain plan is as follow:
Step # Step Name
14 SELECT STATEMENT
13 HASH JOIN
1 SIEBEL.S_ISS TABLE ACCESS [FULL]
12 HASH JOIN
2 SIEBEL.S_ACT_PROD_ISS TABLE ACCESS [FULL]
11 HASH JOIN
3 SIEBEL.S_POSTN_CON TABLE ACCESS [FULL]
10 HASH JOIN
4 SIEBEL.S_ACT_PRDINT TABLE ACCESS [FULL]
9 HASH JOIN
7 HASH JOIN
5 SIEBEL.S_POSTN TABLE ACCESS [FULL]
6 SIEBEL.S_EVT_ACT TABLE ACCESS [FULL]
8 SIEBEL.S_CONTACT TABLE ACCESS [FULL]

If I remove the hint and switch the table sequence after from clause (swap t1 and t6, t1 is small with 17700 rows, t6 has 4 million rows), the query becomes:

SELECT t1.name "c1",
t2.appt_start_dt "c2", t2.todo_cd "c3", t2.subtype_cd "c4",
t3.last_name "c5", t3.fst_name "c6", t3.x_flag_3 "c7",
t3.x_ims_number "c8", t3.x_me_number "c9", t3.x_pri_spec "c10",
t3.x_pri_spec_jan "c11", t3.x_place_field1 "c12", t4.explanation "c13",
t4.discussion "c14", t5.x_mat_usd_1 "c15", t5.x_mat_usd_2 "c16",
t5.x_phy_resp "c17", decode(substr(t1.name, 1, 3), '100', '07', '101',
'08', NULL) || substr(t1.name, 5, 2) "c18", decode(substr(t1.name, 1, 3),
'100', '07', '101', '08', NULL) || substr(t1.name, 8, 7) "c19",
decode(substr(t1.name, 1, 3), '100', '07', '101', '08', NULL) ||
substr(t1.name, 8, 2) "c20", decode(substr(t1.name, 1, 3), '100', '07',
'101', '08', NULL) || substr(t1.name, 8, 5) "c21", t6.specialty "c22",
decode(substr(t1.name, 1, 3), '100', '07', '101', '08', NULL) "c23"
FROM siebel.s_postn_con t6, siebel.s_evt_act t2, siebel.s_contact t3,
siebel.s_act_prdint t7, siebel.s_act_prod_iss t5, siebel.s_iss t4,
siebel.s_postn t1
WHERE t1.row_id = t2.owner_postn_id
AND t2.target_per_id = t3.row_id
AND t2.row_id = t7.activity_id
AND t7.row_id = t5.act_prdint_id
AND t5.iss_id = t4.row_id
AND t6.postn_id = t1.row_id
AND substr(t1.name, 1, 3) IN ('100', '101')
AND substr(t1.name, 5, 2) <> '00'
AND t6.con_id = t3.row_id
The explain plan becomes:

Query Plan
----------------------------------------------------------
SELECT STATEMENT Cost =
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
TABLE ACCESS FULL S_POSTN
TABLE ACCESS BY INDEX ROWID S_EVT_ACT
INDEX RANGE SCAN S_EVT_ACT_F58
TABLE ACCESS BY INDEX ROWID S_CONTACT
INDEX UNIQUE SCAN S_CONTACT_P1
TABLE ACCESS BY INDEX ROWID S_POSTN_CON
INDEX RANGE SCAN S_POSTN_CON_U1
TABLE ACCESS BY INDEX ROWID S_ACT_PRDINT
INDEX RANGE SCAN S_ACT_PRDINT_U1
TABLE ACCESS BY INDEX ROWID S_ACT_PROD_ISS
INDEX RANGE SCAN S_ACT_PROD_ISS_U1
TABLE ACCESS BY INDEX ROWID S_ISS
INDEX UNIQUE SCAN S_ISS_P1

This no hint query returns the first row very quick (within seconds), but the user says the query can not finish after 19+ hours. Could you please shed some light on this? Is it because all the nested loops?

thanks,

Hi Tom,
I read somewhere that if you issue parallel hint then append hint is not required.
Is it correct?
Thanks

Hi Tom,
If I issue
INSERT /*+ parallel(e,6) */ INTO .....
SELECT /*+ parallel(x,6) */ FROM
will NOT use append hint AND
INSERT /*+ append parallel(e,6) */ INTO .....
SELECT /*+ parallel(x,6) */ FROM
will use append hint
AND
ALTER SESSION ENABLE PARALLEL DML;
INSERT /*+ parallel(e,6) */ INTO .....
SELECT /*+ parallel(x,6) */ FROM
will implicitly use append hint
Correct?
Regards

Tom,

We are using Siebel, use RULE based optimizer (8174).
Sql statements follow different plans in out UAT environment, it would not use the index as is the case with other environment (seems prefer full tablescan). This instance was recently rebuilt for a larger db_block_size (from 8K to 16K), nothing else was suppose to be changed.

Could you pl point out what are the possible causes for this behavior?

thanks,

Tom,

Here they are:

1) SQL> select * from global_name;

GLOBAL_NAME                                                                     
---------------UATPHT1                                                                         

SQL> set autotrace traceonly explain;
SQL> SELECT
  2        T2.CONFLICT_ID,
  3        T3.POSTN_ID,
  4        T13.X_IMS_NUMBER,
  5        T13.PR_POSTN_ID,
  6        T13.LAST_UPD,
  7        T13.PR_REP_SYS_FLG,
  8        T13.X_SEC_SPECIALTY,
  9        T13.CSN,
 10        T2.ROW_ID,
 11        T8.CITY,
 12        T13.MID_NAME,
 13        T13.ALT_PH_NUM,
 14        T3.ROW_ID,
 15        T2.CREATED,
 16        T10.CREATED,
 17        T13.DISA_CLEANSE_FLG,
 18        T13.EMAIL_ADDR,
 19        T2.ATTRIB_38,
 20        T13.CREATED_BY,
 21        T2.MODIFICATION_NUM,
 22        T5.PH_NUM,
 23        T7.NAME,
 24        T8.ROW_ID,
 25        T2.ATTRIB_27,
 26        T8.COUNTRY,
 27        T9.ROW_ID,
 28        T3.CON_X_LST_NAME,
 29        T13.EMP_ID,
 30        T11.PR_BL_ADDR_ID,
 31        T13.PR_REP_DNRM_FLG,
 32        T2.ATTRIB_09,
 33        T13.LAST_NAME,
 34        T12.PRESCRIBER_FLG,
 35        T5.ROW_ID,
 36        T13.CONFLICT_ID,
 37        T3.CON_FST_NAME,
 38        T1.PR_ADDR_ID,
 39        T10.LAST_UPD_BY,
 40        T13.PR_TERR_ID,
 41        T13.PR_REP_MANL_FLG,
 42        T9.PR_EMP_ID,
 43        T13.PR_PER_ADDR_ID,
 44        T13.CREATED,
 45        T13.DEGREE,
 46        T11.PR_SRV_AGREE_ID,
 47        T11.PR_SHIP_ADDR_ID,
 48        T8.X_ADDR_LINE2,
 49        T13.ROW_ID,
 50        T8.ADDR,
 51        T13.CONSUMER_FLG,
 52        T13.DEDUP_TOKEN,
 53        T13.FST_NAME,
 54        T2.LAST_UPD_BY,
 55        T4.LOGIN,
 56        T13.MED_SPEC_ID,
 57        T8.STATE,
 58        T11.X_NAME,
 59        T2.ATTRIB_01,
 60        T13.X_ME_NUMBER,
 61        T13.X_SCRUB_FLG,
 62        T2.ATTRIB_47,
 63        T13.BU_ID,
 64        T3.CON_MID_NAME,
 65        T5.X_MOBILEPHONE,
 66        T10.PAR_ROW_ID,
 67        T13.PER_TITLE_SUFFIX,
 68        T11.LOC,
 69        T8.PROVINCE,
 70        T10.CREATED_BY,
 71        T8.ZIPCODE,
 72        T3.ROW_STATUS,
 73        T13.BIRTH_DT,
 74        T10.MODIFICATION_NUM,
 75        T13.PR_AFFL_ID,
 76        T11.PR_BL_PER_ID,
 77        T9.NAME,
 78        T13.PR_STATE_LIC_ID,
 79        T8.ADDR_TYPE_CD,
 80        T6.ROW_ID,
 81        T13.MODIFICATION_NUM,
 82        T10.SPOUSE_NAME,
 83        T13.X_FLAG_3,
 84        T2.PAR_ROW_ID,
 85        T13.PR_OPTY_ID,
 86        T2.ATTRIB_12,
 87        T11.ROW_ID,
 88        T10.EDUCATION,
 89        T13.X_PLACE_FIELD8,
 90        T11.INTEGRATION_ID,
 91        T10.CONFLICT_ID,
 92        T8.X_CCC_FIELD1,
 93        T3.CON_LAST_NAME,
 94        T3.CON_X_FST_NAME,
 95        T10.ROW_ID,
 96        T11.PR_SHIP_PER_ID,
 97        T13.X_PLACE_FIELD_10,
 98        T13.OWNER_PER_ID,
 99        T2.LAST_UPD,
100        T10.LAST_UPD,
101        T5.X_PHONE2,
102        T13.X_PLACE_FIELD5,
103        T13.WORK_PH_NUM,
104        T13.LAST_UPD_BY,
105        T13.INDUST_ID,
106        T8.X_PRACTICE_TYPE,
107        T13.CELL_PH_NUM,
108        T2.ATTRIB_03,
109        T2.ATTRIB_04,
110        T13.PR_DEPT_OU_ID,
111        T2.CREATED_BY,
112        T13.SEX_MF,
113        T12.NAME,
114        T3.LAST_CALL_DT,
115        T2.ATTRIB_13,
116        T8.COMMENTS,
117        T13.PER_TITLE,
118        T5.X_CON_ADDR_FAX_NO,
119        T2.ATTRIB_39,
120        T6.LICENSE_NUM,
121        T13.JOB_TITLE,
122        T13.SPEAKER_FLG,
123        T13.OK_TO_SAMPLE_FLG
124     FROM
125         SIEBEL.S_POSTN_CON T1,
126         SIEBEL.S_CONTACT_X T2,
127         SIEBEL.S_POSTN_CON T3,
128         SIEBEL.S_EMPLOYEE T4,
129         SIEBEL.S_CON_ADDR T5,
130         SIEBEL.S_STATE_LIC_LS T6,
131         SIEBEL.S_MED_SPEC T7,
132         SIEBEL.S_ADDR_PER T8,
133         SIEBEL.S_POSTN T9,
134         SIEBEL.S_CONTACT_T T10,
135         SIEBEL.S_ORG_EXT T11,
136         SIEBEL.S_INDUST T12,
137         SIEBEL.S_CONTACT T13
138   WHERE
139        T13.ROW_ID = T1.CON_ID (+) AND
140        T13.INDUST_ID = T12.ROW_ID (+) AND
141        T13.MED_SPEC_ID = T7.ROW_ID (+) AND
142        T13.PR_DEPT_OU_ID = T11.ROW_ID (+) AND
143        T13.ROW_ID = T2.PAR_ROW_ID (+) AND
144        T13.ROW_ID = T10.PAR_ROW_ID (+) AND
145        T13.PR_STATE_LIC_ID = T6.ROW_ID (+) AND
146        T13.PR_PER_ADDR_ID = T8.ROW_ID (+) AND
147        T13.PR_PER_ADDR_ID = T5.ADDR_PER_ID (+) AND T13.ROW_ID = T5.CONTACT_ID (+) AND
148        T13.PR_POSTN_ID = T9.ROW_ID AND
149        T13.PR_POSTN_ID = T3.POSTN_ID AND T13.ROW_ID = T3.CON_ID AND
150        T9.PR_EMP_ID = T4.ROW_ID (+) AND
151        (T13.PRIV_FLG = 'N')
152     ORDER BY
153        T13.LAST_NAME, T13.FST_NAME, T13.MID_NAME
154  /

Execution Plan
----------------------------------------------------------                      
   0      SELECT STATEMENT Optimizer=RULE                                       
   1    0   SORT (ORDER BY)                                                     
   2    1     NESTED LOOPS (OUTER)                                              
   3    2       NESTED LOOPS (OUTER)                                            
   4    3         NESTED LOOPS (OUTER)                                          
   5    4           NESTED LOOPS                                                
   6    5             NESTED LOOPS (OUTER)                                      
   7    6               NESTED LOOPS (OUTER)                                    
   8    7                 NESTED LOOPS (OUTER)                                  
   9    8                   NESTED LOOPS (OUTER)                                
  10    9                     NESTED LOOPS (OUTER)                              
  11   10                       NESTED LOOPS                                    
  12   11                         NESTED LOOPS (OUTER)                          
  13   12                           NESTED LOOPS (OUTER)                        
  14   13                             TABLE ACCESS (FULL) OF 'S_CONTAC          
          T'                                                                    
                                                                                
  15   13                             TABLE ACCESS (BY INDEX ROWID) OF          
           'S_INDUST'                                                           
                                                                                
  16   15                               INDEX (UNIQUE SCAN) OF 'S_INDU          
          ST_P1' (UNIQUE)                                                       
                                                                                
  17   12                           TABLE ACCESS (BY INDEX ROWID) OF '          
          S_ORG_EXT'                                                            
                                                                                
  18   17                             INDEX (UNIQUE SCAN) OF 'S_ORG_EX          
          T_P1' (UNIQUE)                                                        
                                                                                
  19   11                         TABLE ACCESS (BY INDEX ROWID) OF 'S_          
          POSTN'                                                                
                                                                                
  20   19                           INDEX (UNIQUE SCAN) OF 'S_POSTN_P1          
          ' (UNIQUE)                                                            
                                                                                
  21   10                       TABLE ACCESS (BY INDEX ROWID) OF 'S_AD          
          DR_PER'                                                               
                                                                                
  22   21                         INDEX (UNIQUE SCAN) OF 'S_ADDR_PER_P          
          1' (UNIQUE)                                                           
                                                                                
  23    9                     TABLE ACCESS (BY INDEX ROWID) OF 'S_MED_          
          SPEC'                                                                 
                                                                                
  24   23                       INDEX (UNIQUE SCAN) OF 'S_MED_SPEC_P1'          
           (UNIQUE)                                                             
                                                                                
  25    8                   TABLE ACCESS (BY INDEX ROWID) OF 'S_STATE_          
          LIC_LS'                                                               
                                                                                
  26   25                     INDEX (UNIQUE SCAN) OF 'S_STATE_LIC_LS_P          
          1' (UNIQUE)                                                           
                                                                                
  27    7                 TABLE ACCESS (BY INDEX ROWID) OF 'S_EMPLOYEE          
          '                                                                     
                                                                                
  28   27                   INDEX (UNIQUE SCAN) OF 'S_EMPLOYEE_P1' (UN          
          IQUE)                                                                 
                                                                                
  29    6               TABLE ACCESS (BY INDEX ROWID) OF 'S_CON_ADDR'           
  30   29                 INDEX (RANGE SCAN) OF 'S_CON_ADDR_F1' (NON-U          
          NIQUE)                                                                
                                                                                
  31    5             TABLE ACCESS (BY INDEX ROWID) OF 'S_POSTN_CON'            
  32   31               INDEX (RANGE SCAN) OF 'S_POSTN_CON_U1' (UNIQUE          
          )                                                                     
                                                                                
  33    4           TABLE ACCESS (BY INDEX ROWID) OF 'S_CONTACT_T'              
  34   33             INDEX (RANGE SCAN) OF 'S_CONTACT_T_U1' (UNIQUE)           
  35    3         TABLE ACCESS (BY INDEX ROWID) OF 'S_CONTACT_X'                
  36   35           INDEX (RANGE SCAN) OF 'S_CONTACT_X_U1' (UNIQUE)             
  37    2       TABLE ACCESS (BY INDEX ROWID) OF 'S_POSTN_CON'                  
  38   37         INDEX (RANGE SCAN) OF 'S_POSTN_CON_U1' (UNIQUE)       

2) SQL> select * from global_name;

GLOBAL_NAME                                                                     
---------------
INTPHT1    

Execution Plan
----------------------------------------------------------                      
   0      SELECT STATEMENT Optimizer=RULE                                       
   1    0   NESTED LOOPS (OUTER)                                                
   2    1     NESTED LOOPS (OUTER)                                              
   3    2       NESTED LOOPS (OUTER)                                            
   4    3         NESTED LOOPS                                                  
   5    4           NESTED LOOPS (OUTER)                                        
   6    5             NESTED LOOPS (OUTER)                                      
   7    6               NESTED LOOPS (OUTER)                                    
   8    7                 NESTED LOOPS (OUTER)                                  
   9    8                   NESTED LOOPS (OUTER)                                
  10    9                     NESTED LOOPS                                      
  11   10                       NESTED LOOPS (OUTER)                            
  12   11                         NESTED LOOPS (OUTER)                          
  13   12                           TABLE ACCESS (BY INDEX ROWID) OF '          
          S_CONTACT'                                                            
                                                                                
  14   13                             INDEX (FULL SCAN) OF 'S_CONTACT_          
          U1' (UNIQUE)                                                          
                                                                                
  15   12                           TABLE ACCESS (BY INDEX ROWID) OF '          
          S_INDUST'                                                             
                                                                                
  16   15                             INDEX (UNIQUE SCAN) OF 'S_INDUST          
          _P1' (UNIQUE)                                                         
                                                                                
  17   11                         TABLE ACCESS (BY INDEX ROWID) OF 'S_          
          ORG_EXT'                                                              
                                                                                
  18   17                           INDEX (UNIQUE SCAN) OF 'S_ORG_EXT_          
          P1' (UNIQUE)                                                          
                                                                                
  19   10                       TABLE ACCESS (BY INDEX ROWID) OF 'S_PO          
          STN'                                                                  
                                                                                
  20   19                         INDEX (UNIQUE SCAN) OF 'S_POSTN_P1'           
          (UNIQUE)                                                              
                                                                                
  21    9                     TABLE ACCESS (BY INDEX ROWID) OF 'S_ADDR          
          _PER'                                                                 
                                                                                
  22   21                       INDEX (UNIQUE SCAN) OF 'S_ADDR_PER_P1'          
           (UNIQUE)                                                             
                                                                                
  23    8                   TABLE ACCESS (BY INDEX ROWID) OF 'S_MED_SP          
          EC'                                                                   
                                                                                
  24   23                     INDEX (UNIQUE SCAN) OF 'S_MED_SPEC_P1' (          
          UNIQUE)                                                               
                                                                                
  25    7                 TABLE ACCESS (BY INDEX ROWID) OF 'S_STATE_LI          
          C_LS'                                                                 
                                                                                
  26   25                   INDEX (UNIQUE SCAN) OF 'S_STATE_LIC_LS_P1'          
           (UNIQUE)                                                             
                                                                                
  27    6               TABLE ACCESS (BY INDEX ROWID) OF 'S_EMPLOYEE'           
  28   27                 INDEX (UNIQUE SCAN) OF 'S_EMPLOYEE_P1' (UNIQ          
          UE)                                                                   
                                                                                
  29    5             TABLE ACCESS (BY INDEX ROWID) OF 'S_CON_ADDR'             
  30   29               INDEX (RANGE SCAN) OF 'S_CON_ADDR_F1' (NON-UNI          
          QUE)                                                                  
                                                                                
  31    4           TABLE ACCESS (BY INDEX ROWID) OF 'S_POSTN_CON'              
  32   31             INDEX (RANGE SCAN) OF 'S_POSTN_CON_U1' (UNIQUE)           
  33    3         TABLE ACCESS (BY INDEX ROWID) OF 'S_CONTACT_T'                
  34   33           INDEX (RANGE SCAN) OF 'S_CONTACT_T_U1' (UNIQUE)             
  35    2       TABLE ACCESS (BY INDEX ROWID) OF 'S_CONTACT_X'                  
  36   35         INDEX (RANGE SCAN) OF 'S_CONTACT_X_U1' (UNIQUE)               
  37    1     TABLE ACCESS (BY INDEX ROWID) OF 'S_POSTN_CON'                    
  38   37       INDEX (RANGE SCAN) OF 'S_POSTN_CON_U1' (UNIQUE)                 

thanks, 

Tom,

the result:

SQL> select * from global_name;

GLOBAL_NAME                                                                     
-----------------
UATPHT1  

                               Is                                               
INDEX_NAME                     Unique COLUMNS                                   
------------------------------ ------ --------------------------------          
S_CONTACT_CR851                No     X_IMS_NUMBER                              
S_CONTACT_CUST_1_X             No     X_PLACE_FIELD1, X_PLACE_FIELD5,           
                                      X_PLACE_FIELD_10                          
                                                                                
S_CONTACT_CUST_2_X             No     X_PLACE_FIELD5, X_PLACE_FIELD_10          
S_CONTACT_CUST_3_X             No     X_PLACE_FIELD_10, X_PLACE_FIELD5          
S_CONTACT_CUST_OBI_X           No     EMAIL_ADDR                                
S_CONTACT_F1                   No     LAST_UPD                                  
S_CONTACT_F2                   No     CON_ASST_PER_ID                           
S_CONTACT_F3                   No     CON_MANAGER_PER_ID                        

                               Is                                               
INDEX_NAME                     Unique COLUMNS                                   
------------------------------ ------ --------------------------------          
S_CONTACT_F4                   No     SRC_ID                                    
S_CONTACT_F5                   No     PREF_LANG_ID                              
S_CONTACT_F50                  No     PR_PROD_ID                                
S_CONTACT_F51                  No     AREA_ID                                   
S_CONTACT_F52                  No     PR_STATE_LIC_ID                           
S_CONTACT_F53                  No     INDUST_ID                                 
S_CONTACT_F6                   No     OWNER_PER_ID, LAST_NAME,                  
                                      FST_NAME                                  
                                                                                
S_CONTACT_F7                   No     EMP_ID                                    

                               Is                                               
INDEX_NAME                     Unique COLUMNS                                   
------------------------------ ------ --------------------------------          
S_CONTACT_F8                   No     MED_SPEC_ID                               
S_CONTACT_F9                   No     REG_AS_EMP_ID                             
S_CONTACT_M1                   No     CREATED_BY                                
S_CONTACT_M10                  No     LOGIN                                     
S_CONTACT_M11                  No     BU_ID, CONSUMER_FLG, LAST_NAME,           
                                      FST_NAME, MID_NAME                        
                                                                                
S_CONTACT_M2                   No     DEDUP_TOKEN                               
S_CONTACT_M3                   No     PR_OU_ADDR_ID                             
S_CONTACT_M4                   No     ALIAS_NAME                                

                               Is                                               
INDEX_NAME                     Unique COLUMNS                                   
------------------------------ ------ --------------------------------          
S_CONTACT_M51                  No     ASGN_EXCLD_FLG2                           
S_CONTACT_M6                   No     ASGN_USR_EXCLD_FLG                        
S_CONTACT_M8                   No     CSN                                       
S_CONTACT_M9                   No     CONSUMER_FLG, LAST_NAME,                  
                                      FST_NAME, MID_NAME                        
                                                                                
S_CONTACT_OBI                  No     X_ME_NUMBER                               
S_CONTACT_P1                   Yes    ROW_ID                                    
S_CONTACT_U1                   Yes    LAST_NAME, FST_NAME, MID_NAME,            
                                      PR_DEPT_OU_ID, OWNER_PER_ID,              

                               Is                                               
INDEX_NAME                     Unique COLUMNS                                   
------------------------------ ------ --------------------------------          
                                      BU_ID, CONFLICT_ID                        
                                                                                
S_CONTACT_U2                   Yes    PR_DEPT_OU_ID, LAST_NAME,                 
                                      FST_NAME, MID_NAME, BU_ID,                
                                      OWNER_PER_ID, CONFLICT_ID                 
                                                                                
S_CONTACT_U3                   Yes    BU_ID, LAST_NAME, FST_NAME,               
                                      MID_NAME, PR_DEPT_OU_ID,                  
                                      OWNER_PER_ID, CONFLICT_ID                 
                                                                                

                               Is                                               
INDEX_NAME                     Unique COLUMNS                                   
------------------------------ ------ ---------------------         
S_CONTACT_V1                   No     PR_ACT_ID                                 
S_CONTACT_V2                   No     PR_OPTY_ID                                
S_CONTACT_V3                   Yes    PR_POSTN_ID, ROW_ID                       

36 rows selected.

SQL> select * from global_name;

GLOBAL_NAME                                                                     
-------------------------------------------
INTPHT1    
                               Is                                               
INDEX_NAME                     Unique COLUMNS                                   
------------------------------ ------ --------------------------------          
S_CONTACT_CR851                No     X_IMS_NUMBER                              
S_CONTACT_CUST_1_X             No     X_PLACE_FIELD1, X_PLACE_FIELD5,           
                                      X_PLACE_FIELD_10                          
                                                                                
S_CONTACT_CUST_2_X             No     X_PLACE_FIELD5, X_PLACE_FIELD_10          
S_CONTACT_CUST_3_X             No     X_PLACE_FIELD_10, X_PLACE_FIELD5          
S_CONTACT_CUST_OBI_X           No     EMAIL_ADDR                                
S_CONTACT_F1                   No     LAST_UPD                                  
S_CONTACT_F2                   No     CON_ASST_PER_ID                           
S_CONTACT_F3                   No     CON_MANAGER_PER_ID                        

                               Is                                               
INDEX_NAME                     Unique COLUMNS                                   
------------------------------ ------ --------------------------------          
S_CONTACT_F4                   No     SRC_ID                                    
S_CONTACT_F5                   No     PREF_LANG_ID                              
S_CONTACT_F50                  No     PR_PROD_ID                                
S_CONTACT_F51                  No     AREA_ID                                   
S_CONTACT_F52                  No     PR_STATE_LIC_ID                           
S_CONTACT_F53                  No     INDUST_ID                                 
S_CONTACT_F6                   No     OWNER_PER_ID, LAST_NAME,                  
                                      FST_NAME                                  
                                                                                
S_CONTACT_F7                   No     EMP_ID                                    

                               Is                                               
INDEX_NAME                     Unique COLUMNS                                   
------------------------------ ------ --------------------------------          
S_CONTACT_F8                   No     MED_SPEC_ID                               
S_CONTACT_F9                   No     REG_AS_EMP_ID                             
S_CONTACT_M1                   No     CREATED_BY                                
S_CONTACT_M10                  No     LOGIN                                     
S_CONTACT_M11                  No     BU_ID, CONSUMER_FLG, LAST_NAME,           
                                      FST_NAME, MID_NAME                        
                                                                                
S_CONTACT_M2                   No     DEDUP_TOKEN                               
S_CONTACT_M3                   No     PR_OU_ADDR_ID                             
S_CONTACT_M4                   No     ALIAS_NAME                                

                               Is                                               
INDEX_NAME                     Unique COLUMNS                                   
------------------------------ ------ --------------------------------          
S_CONTACT_M51                  No     ASGN_EXCLD_FLG2                           
S_CONTACT_M6                   No     ASGN_USR_EXCLD_FLG                        
S_CONTACT_M8                   No     CSN                                       
S_CONTACT_M9                   No     CONSUMER_FLG, LAST_NAME,                  
                                      FST_NAME, MID_NAME                        
                                                                                
S_CONTACT_OBI                  No     X_ME_NUMBER                               
S_CONTACT_P1                   Yes    ROW_ID                                    
S_CONTACT_U1                   Yes    LAST_NAME, FST_NAME, MID_NAME,            
                                      PR_DEPT_OU_ID, OWNER_PER_ID,              

                               Is                                               
INDEX_NAME                     Unique COLUMNS                                   
------------------------------ ------ --------------------------------          
                                      BU_ID, CONFLICT_ID                        
                                                                                
S_CONTACT_U2                   Yes    PR_DEPT_OU_ID, LAST_NAME,                 
                                      FST_NAME, MID_NAME, BU_ID,                
                                      OWNER_PER_ID, CONFLICT_ID                 
                                                                                
S_CONTACT_U3                   Yes    BU_ID, LAST_NAME, FST_NAME,               
                                      MID_NAME, PR_DEPT_OU_ID,                  
                                      OWNER_PER_ID, CONFLICT_ID                 
                                                                                

                               Is                                               
INDEX_NAME                     Unique COLUMNS                                   
------------------------------ ------ --------------------------------          
S_CONTACT_V1                   No     PR_ACT_ID                                 
S_CONTACT_V2                   No     PR_OPTY_ID                                
S_CONTACT_V3                   Yes    PR_POSTN_ID, ROW_ID                       

36 rows selected.

It seems not only this query follows different exec path, here is another simply one.

in UATPHT1:
SQL> select /*+ index_ffs(s) */ count(*) from siebel.s_employee s 
  2  /

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE (Cost=20 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'S_EMPLOYEE' (Cost=20 Card=2000)

While in INTPHT1:

SQL> select /*+ index_ffs(s) */ count(*) from siebel.s_employee s ;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE (Cost=4 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (FAST FULL SCAN) OF 'S_EMPLOYEE_M1' (UNIQUE) (Cost
          =4 Card=2000)

thanks, 

Tom,

You suspect right.
in UAT:
SQL> select * from global_name;

GLOBAL_NAME
---------------------------------------
UATPHT1

SQL> desc s_contact
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------
 ROW_ID                                             VARCHAR2(15)
 CREATED                                            DATE
 CREATED_BY                                         VARCHAR2(15)
 LAST_UPD                                           DATE
 LAST_UPD_BY                                        VARCHAR2(15)
 DCKING_NUM                                         NUMBER(22,7)
 MODIFICATION_NUM                                   NUMBER(10)
 CONFLICT_ID                                        VARCHAR2(15)
 BU_ID                                              VARCHAR2(15)
 DISA_CLEANSE_FLG                                   CHAR(1)
 EMAIL_SR_UPD_FLG                                   CHAR(1)
 FST_NAME                                           VARCHAR2(50)
 INVSTGTR_FLG                                       CHAR(1)
 LAST_NAME                                          VARCHAR2(50)
 PRIV_FLG                                           CHAR(1)
 SPEAKER_FLG                                        CHAR(1)
 SUPPRESS_EMAIL_FLG                                 CHAR(1)
::::

In INT:
SQL> desc s_contact
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------
 ROW_ID                                    NOT NULL VARCHAR2(1
 CREATED                                   NOT NULL DATE
 CREATED_BY                                NOT NULL VARCHAR2(1
 LAST_UPD                                  NOT NULL DATE
 LAST_UPD_BY                               NOT NULL VARCHAR2(1
 DCKING_NUM                                         NUMBER(22,
 MODIFICATION_NUM                          NOT NULL NUMBER(10)
 CONFLICT_ID                               NOT NULL VARCHAR2(1
 BU_ID                                     NOT NULL VARCHAR2(1
 DISA_CLEANSE_FLG                          NOT NULL CHAR(1)
 EMAIL_SR_UPD_FLG                          NOT NULL CHAR(1)
 FST_NAME                                  NOT NULL VARCHAR2(5
 INVSTGTR_FLG                              NOT NULL CHAR(1)
 LAST_NAME                                 NOT NULL VARCHAR2(5
 PRIV_FLG                                  NOT NULL CHAR(1)
 SPEAKER_FLG                               NOT NULL CHAR(1)
 SUPPRESS_EMAIL_FLG                        NOT NULL CHAR(1)
 SUPPRESS_FAX_FLG                          NOT NULL CHAR(1)
 ENTERPRISE_FLAG                                    CHAR(1)
 OK_TO_SAMPLE_FLG                                   CHAR(1)
 PR_REP_DNRM_FLG                                    CHAR(1)

But the tables were imported from the same production DB. we refresh the DB regularly, drop table and import. never suspect the table definition different. 

Tom,

Thanks very much for your help.
On more question: How big is the impact of setting db_file_multiblock_read_count=32 (with 16K block size). Will this cause the optimizer prefer full tablescan in all cases?

Tom,

The problem solved once we changed the null columns to not null. You are the best!!! You are the first one I come to when I encounter problems. I don't have high confidence with TARs.

thanks again.

Interesting snippet discovered by tracing a call to sync a text based index:

insert /*+ APPEND */ into ctxsys....
values (....)

Someone needs to explain the APPEND hint to those Text developers :-)

If i have to make insert into .. select....
into temporary table and if i use the APPEND hint
will it be faster?
Thank you.
Kati

I need to know why calling insert /*+ APPEND */ into dbName.. for many records doens't keep the records in the same order they were inserted in. I can't afford to use ORDER BY my program must be fast.

First I want to say I really appreciate the quick response.

If you know any resources out there on the web about IOT's or clusters It could help me out greatly.

So are you saying that even if I use IOT's or clusters to force the order to be the same I still have to use ORDER BY? or did you mean that ORDER BY is the only really safe way to quarrantee that the order is the same?

I appreciate the response as I don't know a whole lot about Oracle I've just started using it about 5 months ago. I really need updates (insert statements in my program to be super quick).

Hello Tom,

I'm attempting to do an insert append of large amounts of
data over a db link. Indeed (as you stated above) you can set
both sides of the operation to parallel and set DML to parallel,
but so far I've found it has zero benefit. Looking at the waits
it seems that SQL*Net is a major bottleneck. Do you know of any
way to speed it up?

In statspack 80% of the time is spent waiting on :
PX Deq Credit: send blkd

I'm on 9.2.0.4 and to try to cut out network interference I'm
using the db link to loop back to myself (for testing purposes).

Steps:

1.
create large table, in my case a copy of dba_objects inserted
repeatedly into itself until we have 9 million rows; call it
BIG_TABLE, and the database link DBLINK looping back to the
same database. Neither table has an index.

2.
drop table BIG_TABLE_COPY;
create table BIG_TABLE_COPY as select * from BIG_TABLE where 1=2;
alter table BIG_TABLE_COPY parallel (degree 15);
alter table BIG_TABLE parallel (degree 5);
alter session enable parallel dml;

set timing on

insert /*+ APPEND */ into BIG_TABLE_COPY
select * from BIG_TABLE@DBLINK;



The results are basically:

with no parallel anywhere : 160 seconds
with parallel only on BIG_TABLE : 160 seconds
with parallel only on BIG_TABLE_COPY : 160 seconds
with parallel on both, and parallel DML: 160 seconds
with parallel on both, and parallel DML,
and *not* using a DBLINK : 8 seconds


I can see by looking at the seesions that the parallel
processes are indeed starting up as expected.

This is of course only an example, in reality I have
cases where it's 3 minutes locally and two hours over
a database link. Using parallel is such an effective
way to cut processing times, but is it powerless against
database links?

Note that I set SDU and TDU to the maximum (32K) for the db link.

thanks for any thoughts

Hello Tom,

Thanks for the response.

In answer to your questions:

- I didn't actually set SDU and TDU to 32K, that's the way
it is and it would take a lot of effort to change it.
Do you think it could make a difference?

- it can't be an IO restriction as in both cases it's writing
to the same (one and only) database. The only difference
between the 8s and the 160s scenarios are a database link -
which loops back to the same database. Hence the difference
is, I would say, due to the database link.

- the system has 30 CPUs

- fair point about the 15/5 mismatch, it has its origin in
the production version of this problem (using 2 databases;
I'm presenting to the you the test version, to keep things
simple).
However, no degree of parallelism on either side seems
to make any difference - 15/5, 15/15, 5/5 or 0/0. Because
the database link is the weakest link, it seems.

- we are indeed using TCP/IP

I think it's a realistic simulation, I've simplified things
to cut out down the number of factors, e.g. network bandwidth.

I've had a look a programming solution, for example setting
up two jobs to copy across, simultaneously, one half each
of the table, and then merging them back together on the
destination database. The WHERE on the SELECT would slow it
down but that doesn't matter because the database link is still
slower. Hence I can copy half the table in half the time, a
quarter of the table in a quarter of the time etc. As long as
these are done simultaneously I can save time, and can spend
a trivial amount merging the data back together once it's all
on the far side of the database link.

Of course, I would prefer it if there was a non-programming
solution, some type of parallel setting for the database link?

many thanks

Tom,

The following query in Siebel database (RULE BASED) runs slow (30 min, less than 70000 rows in the new table)

CREATE TABLE TEST_011905
AS
SELECT c.row_id,
C.X_PLACE_FIELD1 JJID,
ca.ROW_ID c_row_id,
L.LICENSE_NUM sln,
p.name position,
TO_CHAR(SYSDATE,'MMDDYYYY') trans_date,
MAX(NVL(pc.X_REP_TARGET,'N')) rep_target,
PC.X_FLAG_1 ho_target,
p.name,
substr(C.x_place_field_10,1,20) ods_fst_name,
c.csn mid_name,
substr(C.x_place_field5,1,30) ods_Last_name,
c.per_title per_title,
c.per_title_suffix per_title_suffix,
SUBSTR(AD.ADDR,1,35) addr,
substr(AD.X_ADDR_LINE2,1,35) addr2,
substr(AD.CITY,1,25) city,
SUBSTR(AD.STATE,1,2) state,
SUBSTR(AD.ZIPCODE,1,5) zip,
substr(C.X_IMS_NUMBER,1,15) ims_num,
pc.X_FLAG_2
FROM SIEBEL.S_CONTACT C,
SIEBEL.S_POSTN_CON PC,
SIEBEL.S_CONTACT_XM CXM,
SIEBEL.S_STATE_LIC_LS L,
SIEBEL.S_ADDR_PER AD,
SIEBEL.S_CON_ADDR CA,
SIEBEL.S_POSTN P
WHERE C.ROW_ID = PC.CON_ID
AND PC.POSTN_ID = P.ROW_ID
AND PC.PR_ADDR_ID = AD.ROW_ID
AND AD.STATE = L.STATE_CD
AND C.ROW_ID = L.CONTACT_ID
AND C.ROW_ID = CA.CONTACT_ID
AND CA.ADDR_PER_ID = AD.ROW_ID
AND CXM.PAR_ROW_ID = C.ROW_ID
--this eliminates extracting addresses that are checked for deletion --
AND NVL(ca.X_DEL_FLG,'N') = 'N'
-- extracts only active DID --
AND cxm.ATTRIB_08 = 'Y'
-- HO Target--
AND ((PC.X_FLAG_1 = 'Y') OR (PC.X_FLAG_1 = 'N' AND pc.X_REP_TARGET = 'Y'))
--AND pc.X_FLAG_2 = 'N'
AND SUBSTR(P.NAME,1,6) IN ('100-07','100-0G')
GROUP BY
c.row_id,
C.X_PLACE_FIELD1,
ca.ROW_ID,
L.LICENSE_NUM,
p.name,
SYSDATE,
pc.X_REP_TARGET,
PC.X_FLAG_1,
p.name,
C.x_place_field_10,
c.csn,
C.x_place_field5,
c.per_title,
c.per_title_suffix,
AD.ADDR,
ad.X_ADDR_LINE2,
AD.CITY,
AD.STATE,
AD.ZIPCODE,
C.X_IMS_NUMBER,
pc.X_FLAG_2
UNION
--CNS I -------
SELECT c.row_id,
C.X_PLACE_FIELD1 JJID,
ca.ROW_ID c_row_id,
L.LICENSE_NUM sln,
p.name position,
TO_CHAR(SYSDATE,'MMDDYYYY')trans_date,
MAX(NVL(pc.X_REP_TARGET,'N')) rep_target,
MAX(NVL(PC.X_FLAG_1,'N')) HO_TARGET,
p.name,
C.x_place_field_10 ods_fst_name,
c.csn mid_name,
C.x_place_field5 ods_Last_name,
c.per_title per_title,
c.per_title_suffix per_title_suffix,
AD.ADDR addr,
AD.X_ADDR_LINE2 addr2,
AD.CITY city,
AD.STATE state,
AD.ZIPCODE zip,
C.X_IMS_NUMBER ims_num,
pc.X_FLAG_2
FROM SIEBEL.S_CONTACT C,
SIEBEL.S_POSTN_CON PC,
SIEBEL.S_CONTACT_XM CXM,
SIEBEL.S_STATE_LIC_LS L,
SIEBEL.S_ADDR_PER AD,
SIEBEL.S_CON_ADDR CA,
SIEBEL.S_POSTN P
WHERE C.ROW_ID = PC.CON_ID
AND PC.POSTN_ID = P.ROW_ID
AND PC.PR_ADDR_ID = AD.ROW_ID
AND AD.STATE = L.STATE_CD
AND C.ROW_ID = L.CONTACT_ID
AND C.ROW_ID = CA.CONTACT_ID
AND CA.ADDR_PER_ID = AD.ROW_ID
AND CXM.PAR_ROW_ID = C.ROW_ID
--this eliminates extracting addresses that are checked for deletion --
AND NVL(ca.X_DEL_FLG,'N') = 'N'
-- extracts only active DID --
AND cxm.ATTRIB_08 = 'Y'
-- No show flag--
AND pc.X_FLAG_2 = 'N'
-- Rep Rating --
AND pc.practice_type IN ('4-High','5-Very High')
AND SUBSTR(P.NAME,1,6) = '100-0I'
GROUP BY c.row_id,
C.X_PLACE_FIELD1,
ca.ROW_ID,
L.LICENSE_NUM,
p.name,
SYSDATE,
pc.X_REP_TARGET,
PC.X_FLAG_1,
p.name,
C.x_place_field_10,
c.csn,
C.x_place_field5,
c.per_title,
c.per_title_suffix,
AD.ADDR,
ad.X_ADDR_LINE2,
AD.CITY,
AD.STATE,
AD.ZIPCODE,
C.X_IMS_NUMBER,
pc.X_FLAG_2
union
SELECT c.row_id,
C.X_PLACE_FIELD1 JJID,
ca.ROW_ID c_row_id,
L.LICENSE_NUM sln,
p.name position,
TO_CHAR(SYSDATE,'MMDDYYYY')trans_date,
MAX(NVL(pc.X_REP_TARGET,'N')) rep_target,
PC.X_FLAG_1 ho_target,
p.name,
C.x_place_field_10 ods_fst_name,
c.csn mid_name,
C.x_place_field5 ods_Last_name,
c.per_title per_title,
c.per_title_suffix per_title_suffix,
SUBSTR(AD.ADDR,1,35) addr,
AD.X_ADDR_LINE2 addr2,
AD.CITY city,
SUBSTR(AD.STATE,1,2) state,
SUBSTR(AD.ZIPCODE,1,5) zip,
C.X_IMS_NUMBER ims_num,
pc.X_FLAG_2
FROM SIEBEL.S_CONTACT C,
SIEBEL.S_POSTN_CON PC,
SIEBEL.S_CONTACT_XM CXM,
SIEBEL.S_STATE_LIC_LS L,
SIEBEL.S_ADDR_PER AD,
SIEBEL.S_CON_ADDR CA,
SIEBEL.S_POSTN P
WHERE C.ROW_ID = PC.CON_ID
AND PC.POSTN_ID = P.ROW_ID
AND PC.PR_ADDR_ID = AD.ROW_ID
AND AD.STATE = L.STATE_CD
AND C.ROW_ID = L.CONTACT_ID
AND C.ROW_ID = CA.CONTACT_ID
AND CA.ADDR_PER_ID = AD.ROW_ID
AND CXM.PAR_ROW_ID = C.ROW_ID
--this eliminates extracting addresses that are checked for deletion --
AND NVL(ca.X_DEL_FLG,'N') = 'N'
-- extracts only active DID --
AND cxm.ATTRIB_08 = 'Y'
AND SUBSTR(P.NAME,1,9) ='100-0K-29'
GROUP BY
c.row_id,
C.X_PLACE_FIELD1,
ca.ROW_ID,
L.LICENSE_NUM,
p.name,
SYSDATE,
pc.X_REP_TARGET,
PC.X_FLAG_1,
p.name,
C.x_place_field_10,
c.csn,
C.x_place_field5,
c.per_title,
c.per_title_suffix,
AD.ADDR,
ad.X_ADDR_LINE2,
AD.CITY,
AD.STATE,
AD.ZIPCODE,
C.X_IMS_NUMBER,
pc.X_FLAG_2;

The plan is followed:

Query Plan
-----------------------------------
SELECT STATEMENT Cost =
SORT UNIQUE
UNION-ALL
SORT GROUP BY
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
TABLE ACCESS FULL S_POSTN
TABLE ACCESS BY INDEX ROWID S_POSTN_CON
INDEX RANGE SCAN S_POSTN_CON_JJ1
TABLE ACCESS BY INDEX ROWID S_ADDR_PER
INDEX UNIQUE SCAN S_ADDR_PER_P1
TABLE ACCESS BY INDEX ROWID S_CONTACT
INDEX UNIQUE SCAN S_CONTACT_P1
TABLE ACCESS BY INDEX ROWID S_CON_ADDR
INDEX RANGE SCAN S_CON_ADDR_F1
TABLE ACCESS BY INDEX ROWID S_STATE_LIC_LS
INDEX RANGE SCAN S_STATE_LIC_LS_F1
INDEX RANGE SCAN S_CONTACT_XM_ASGN1
SORT GROUP BY
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
TABLE ACCESS FULL S_POSTN
TABLE ACCESS BY INDEX ROWID S_POSTN_CON
INDEX RANGE SCAN S_POSTN_CON_JJ1
TABLE ACCESS BY INDEX ROWID S_ADDR_PER
INDEX UNIQUE SCAN S_ADDR_PER_P1
TABLE ACCESS BY INDEX ROWID S_CONTACT
INDEX UNIQUE SCAN S_CONTACT_P1
TABLE ACCESS BY INDEX ROWID S_CON_ADDR
INDEX RANGE SCAN S_CON_ADDR_F1
TABLE ACCESS BY INDEX ROWID S_STATE_LIC_LS
INDEX RANGE SCAN S_STATE_LIC_LS_F1
INDEX RANGE SCAN S_CONTACT_XM_ASGN1
SORT GROUP BY
NESTED LOOPS
NESTED LOOPS

Query Plan
--------------------------------
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
TABLE ACCESS FULL S_POSTN
TABLE ACCESS BY INDEX ROWID S_POSTN_CON
INDEX RANGE SCAN S_POSTN_CON_JJ1
TABLE ACCESS BY INDEX ROWID S_ADDR_PER
INDEX UNIQUE SCAN S_ADDR_PER_P1
TABLE ACCESS BY INDEX ROWID S_CONTACT
INDEX UNIQUE SCAN S_CONTACT_P1

Query Plan
-------------------
TABLE ACCESS BY INDEX ROWID S_CON_ADDR
INDEX RANGE SCAN S_CON_ADDR_F1
TABLE ACCESS BY INDEX ROWID S_STATE_LIC_LS
INDEX RANGE SCAN S_STATE_LIC_LS_F1
INDEX RANGE SCAN S_CONTACT_XM_ASGN1


The table S_POSTN is small 25000 rows. and the query seems using all the other indexes. Do you see an easy way to improve the performance? Also if the query is going through a DB link (from a cost based DB), will it behave the same way as if running on the database directly? (suppose network speed is not an issue)

thanks

Tom,
The database is 817, and Siebel does not support CBO for Siebel 6.4. The only way is to place hint in the query?

thanks,

Hello Tom,

Thanks again for your follow-up.

- maybe I am getting I/O contention, but that is the
least of my worries. If I insert from table A into table B
(in the same database) it takes x seconds; if I make use of a
database link to again insert from table A into table B
(in the same database) then it takes 20 times as long.
The difference isn't due to I/O contention.

- you say that there should be N database links and ask how many
sessions I see on the remote site. So, I tested this out again
using 2 databases on two different machines. If I look on unix
level and search for ora_p jobs, I see exactly what I expect:
a number of parallel job matching the degree of parallelism
on each database (same in v$session). So far so good. But I do
not see N database links. I selected from v$session where saddr
not in (select k2gtdses from sys.x$k2gte), which I think shows
all active database link connections, and it showed just
one database link during my insert append.

Is there a setting to allow one to get multiple parallel database links?
Or am I looking in the wrong place?

Just as a reminder: I (now) set the remote table to parallel (degree 5) and
then on the local database do:

alter table BIG_TABLE_COPY parallel(degree 5);
alter session enable parallel dml;

insert /*+ APPEND */ into BIG_TABLE_COPY
select * from BIG_TABLE@DBLINK;


many thanks

Hello Tom,

Thanks again for your follow-up.

As you requested:

insert /*+ append */ into big_table_copy select * from big_table where
rownum <= 1000000

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.01 0.00 0 0 0 0
Execute 2 12.15 15.20 12096 55342 26664 2000000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 12.16 15.21 12096 55342 26664 2000000


Rows Row Source Operation
------- ---------------------------------------------------
1 LOAD AS SELECT (cr=27671 r=12096 w=12096 time=8981770 us)
1000000 COUNT STOPKEY (cr=12107 r=12096 w=0 time=5018698 us)
1000000 TABLE ACCESS FULL BIG_TABLE (cr=12107 r=12096 w=0 time=4335505 us)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
control file sequential read 8 0.00 0.00
db file scattered read 395 0.12 3.29
direct path write 4 0.00 0.00
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 3.36 6.26



insert /*+ append */ into big_table_copy2 select * from big_table@loopback where
rownum <= 1000000

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.03 0.02 0 9 2 0
Execute 2 39.91 39.10 0 31128 25212 2000000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 39.94 39.12 0 31137 25214 2000000


Rows Row Source Operation
------- ---------------------------------------------------
1 LOAD AS SELECT (cr=15564 r=0 w=12096 time=19603122 us)
1000000 COUNT STOPKEY (cr=0 r=0 w=0 time=15678283 us)
1000000 REMOTE (cr=0 r=0 w=0 time=14997832 us)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
control file sequential read 8 0.00 0.00
SQL*Net message to dblink 64 0.00 0.00
SQL*Net message from dblink 64 0.00 0.14
SQL*Net more data from dblink 2662 0.00 0.60
direct path write 4 0.00 0.00
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 10.97 18.15


I see that I'm getting a lot more "SQL*Net message from client" waits than you were.

You'll note that the difference in times here is a mere 15s vs 39s, but remember that I was talking about a parallel insert over a db link vs not over a dblink. I tried it with a 19M row table just now (and without the rownum <=) and got 12s vs 356s, quite a difference.

As always, I'd appreciate any thoughts.

Hello Tom,

As requested. Note that now (using a real db link) there is a db link wait.

Just for clarity, I am using a BIG_TABLE which is a copy of DBA_OBJECTS, inserted into itself until 1,000,000 rows is reached.

insert /*+ append */ into big_table_copy2 select * from big_table@realdblink where
rownum <= 1000000

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.21 0 0 1 0
Execute 1 20.49 22.18 0 15564 12609 1000000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 20.52 22.39 0 15564 12610 1000000


Rows Row Source Operation
------- ---------------------------------------------------
1 LOAD AS SELECT (cr=15564 r=0 w=11985 time=22180168 us)
1000000 COUNT STOPKEY (cr=0 r=0 w=0 time=17993832 us)
1000000 REMOTE (cr=0 r=0 w=0 time=17300964 us)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
control file sequential read 4 0.00 0.00
SQL*Net message to dblink 32 0.00 0.00
SQL*Net message from dblink 32 0.01 0.19
log file sync 1 0.00 0.00
SQL*Net more data from dblink 1810 1.04 2.11
direct path write 2 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 13.01 13.01

thanks again

Hello Tom,

I suppose in theory you could expect inserting from a real db link to be somewhat quicker than from a loopback, as there is no reading and writing to the same disks. But none of this shows up in the waits.

I think I'll try doing it a number of times to get averages.

The 20-to-1 ratio is what I was getting when I was using *parallel* over a db link compared to parallel locally (i.e. not over a db link). The bigger the table, the bigger the ratio. I found that when using a dblink, loopback or no, parallel has no effect whatsoever because there seems to be just one dblink and everything has to squeeze through that. You suggested earlier on that there should be multiple db links for parallel operations, but I only ever see one. Could you demonstrate that?

thanks again

I remember that you hit a problem with sql_trace on 9.2.0.4, the version Rick is using, affecting the recorded cpu time in tkprof reports, in a conversation with me - maybe this is altering Rick's measurements ?

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:23510514650237#23557777360185 <code>

"I used 9204 -- the cpu timings in mine might have been affected by a sql_trace issue that was resolved in 9205."

> I found that when using a dblink, loopback or no,
> parallel has no effect whatsoever because there seems
> to be just one dblink and everything has to squeeze
> through that.

It may be consistent with the following doc fragment:

</code> http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96520/tuningpe.htm#64754 <code>

"A DML operation cannot be parallelized if it is in a distributed transaction or if the DML or the query operation is against a remote object".

"Example 1 Distributed Transaction Parallelization

In this example, the DML statement queries a remote object:

INSERT /* APPEND PARALLEL (t3,2) */ INTO t3 SELECT * FROM t4@dblink;

The query operation is executed serially without notification because it references a remote object."

Since you noticed that the select does get parallelized on the remote site ... "executed serially" may just mean "a single db link is used".

> come to think of it -- we don't know what his version is do we?

Yes we know - couple of thousands ;) lines above:

> I'm on 9.2.0.4 and ...

Many thanks Alberto, now I know that I can stop searching for whatever parameter it was that might have been stopping a parallel dblink!

It's a pity that you can't use parallel when you have a few hundred million rows to transfer between databases...

Great, thanks Tom, I'll give it a try. I'd also figured out a DIY method of doing it but it was using rownums and very messy. Maybe in the future we'll get parallel db links.

cheers

What about reading the source table consistently ?

create table t as select rownum x from all_objects where rownum <= 1000;

create table t_target as select * from t where 1=0;

dellera@ORACLE9I> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
53798606

-- poor man's DIY by "pk" - you would use your "rowid ranges" instead of course

create or replace view consistent_view_0 as select * from t AS OF SCN 53798606 where mod(x,2) = 0 ;

create or replace view consistent_view_1 as select * from t AS OF SCN 53798606 where mod(x,2) = 1 ;

delete from t; -- to show that the flashback views works

commit;

declare
l_job number;
begin
dbms_job.submit (l_job,
'insert into t_target select * from consistent_view_0@loopback;');

dbms_job.submit (l_job,
'insert into t_target select * from consistent_view_1@loopback;');
end;
/

commit;

dellera@ORACLE9I> select count(*) from t_target;

COUNT(*)
----------
0

dellera@ORACLE9I> select count(*) from t_target;

COUNT(*)
----------
1000

You gain "read consistency" on the source table,
and loose "write consistency" on the target table since
the "slave jobs" commit at different times ...
but isn't it pretty ?

Unfortunately you can't insert /*+ append */ into the one table simultaneously so I'd have to change Tom's method to insert into separate tables, which I then merge together with a single CTAS or insert /*+ append */ (in massively parallel) or partition exchange.

Sounds laborious but it's still quicker than waiting for everything to shuffle through a single db link! I need the insert /*+ append */ to avoid writing redo.

Interesting method Alberto, but in this case the data is static. I'll remember that one for future problems.

I totally agree, but we're not running in archivelog mode, and we need to avoid generating redo to avoid impacting the other activities happening on the database at the same time.

The situation is an all-or-nothing mass migration in a weekend, hence the noarchivelog.

thanks

Tom,

Your procedure (above) for grabbing chunks of the table by rowid works wonderfully.

I took the pulling rather than the pushing approach, but if I use dba_objects instead of user_objects then I get an ORA-600 (!). But I can live with user_objects.

thanks again

I'm sorry to harp about this question and this is the last time I'll bug you with it. Someone I know truely believes that /*+ APPEND */ and /*+ PARALLEL */ used with insert statements guarantees that records will be in the same order (after all inserts are finished) as the order they were insert in.

Just to clarify and make sure there was no confusion in understanding my question a week or two ago. Heres an example:

I have 100,000 records.
Each record has a field called "rowidentfier" that contains the number of the row.

If I call:

for (int i = 1; i <= 100000; i++)
{ sprintf(szSQLStatement, "insert /*+ APPEND */ /*+ PARALLEL */ into databasename (rowidentifier) values(%d), i);
Oracle.Execute(szSQLStatement);
}

After this code is executed and I go to the oracle database and do a "SELECT * FROM databasename" should I expect the records to show up like this:

rowidentifier
1
2
3
4
...
99999
100000

Right now even using those hints I'm getting something like this:

rowidentifier
540
541
...

I'm not saying your wrong, In fact I can't find any documentation that says append hint guarantees the records will be in order, and as shown above, large number of inserts with my program don't show up in the correct order either. I just need to know that you understood my question as I might not have explained it the best the first time.

thanks.

Ryan.

The restriction when using insert /*+append*/ is that the rows cannot be read until a commit.

This poses a problem in say a batch process or any process really where atomicity is desired i.e. if this direct path insert is part of a bigger transaction where everything should either succeed or fail and I need to read the direct-path inserted table to process further tables. The commit will kill my transaction boundaries and potentially leave data in a inconsistent state if something further on aborts.

Any workaround for this? Thanks

OK, but then can you help me understand why a COMMIT is needed to see the data inserted? A direct-path load just puts the data above the HWM, but the data is still there. Why cant the HWM be "raised" just for my session so I can see it before commiting?

Hi Tom,
In case we use APPEND hint to do the insert and there is a free blocks in the extent, advancing HWM in this case will take one Oracle block or more? If more, how many more?
I have read somewhere that Oracle move the HWM for a table in increments of five blocks.

Thanks in advance.

Sven

Direct load, since it bypasses the conventional load,
the database has to be backed up if recovery is needed,

Is this correct

I am very much aware how the data flow occurs in conventional path load,
by server process using database blocks in the buffer cache and
then eventually data is written to the disk by DB Writer process.

Could you tell us how the data flow occurs during
direct load. Does the data fills up a special buffer for direct load
and then written to disk bypassing the buffer cache. Do these
buffers are acquired from temporary tablespace ....

I simulated direct write with CTAS and found there
is significant redo like you said, and seems no 
undo.

Could you give a reference to a good white paper
that descibes about direct load comprehensively

SQL> 1 select s.*,
  2  (select name from v$statname n where s.STATISTIC#=n.STATISTIC#
  3  and (name like '%write' or name like '%redo%' or name like '%undo%')
  4   )
  5   from v$mystat s order by 4;     

       SID STATISTIC#      VALUE (SELECTNAMEFROMV$STATNAMENWHERES.STATISTIC#=N.STATISTIC#AND(NAME
---------- ---------- ---------- ----------------------------------------------------------------
         9         51          0 DBWR undo block writes
         9        163          5 data blocks consistent reads - undo records applied
         9        183          0 number of auto extends on undo tablespace
         9        120          0 redo blocks written
         9        116          0 redo buffer allocation retries
         9        114         36 redo entries
         9        122          0 redo log space requests
         9        123          0 redo log space wait time
         9        124          0 redo log switch interrupts
         9        125          0 redo ordering marks
         9        115       9464 redo size
         9         73          0 redo synch time
         9         72          1 redo synch writes
         9        117          0 redo wastage
         9        121          0 redo write time
         9        118          0 redo writer latching time
         9        119          0 redo writes
         9         61          0 remote instance undo block writes
         9         62          0 remote instance undo header writes
         9        171          0 rollback changes - undo records applied
         9        185          0 total number of undo segments dropped
         9        161          0 transaction tables consistent reads - undo records applied

create table tableDR as select * from x$ksmmem where rownum < 20000;

       SID STATISTIC#      VALUE (SELECTNAMEFROMV$STATNAMENWHERES.STATISTIC#=N.STATISTIC#AND(NAME
---------- ---------- ---------- ----------------------------------------------------------------
         9         51          0 DBWR undo block writes
         9        163         15 data blocks consistent reads - undo records applied
         9        183          0 number of auto extends on undo tablespace
         9        120          0 redo blocks written
         9        116          0 redo buffer allocation retries
         9        114        251 redo entries
         9        122          0 redo log space requests
         9        123          0 redo log space wait time
         9        124          0 redo log switch interrupts
         9        125          0 redo ordering marks
         9        115     642024 redo size
         9         73         17 redo synch time
         9         72          2 redo synch writes
         9        117          0 redo wastage
         9        121          0 redo write time
         9        118          0 redo writer latching time
         9        119          0 redo writes
         9         61          0 remote instance undo block writes
         9         62          0 remote instance undo header writes
         9        171          0 rollback changes - undo records applied
         9        185          0 total number of undo segments dropped
         9        161          0 transaction tables consistent reads - undo records applied


SQL> select s.*,
  2  (select name from v$statname n where s.STATISTIC#=n.STATISTIC#
  3   and (name like '%rollback%')
  4  )
  5  from v$mystat s order by 4
  6  /
 
       SID STATISTIC#      VALUE (SELECTNAMEFROMV$STATNAMENWHERES.STATISTIC#=N.STATISTIC#AND(NAME
---------- ---------- ---------- ----------------------------------------------------------------
         9        167          0 cleanouts and rollbacks - consistent read gets
         9        171          0 rollback changes - undo records applied
         9        166         15 rollbacks only - consistent read gets
         9        172          0 transaction rollbacks
         9        162          0 transaction tables consistent read rollbacks
         9          5          0 user rollbacks

create table tabledl2 as select * from x$ksmmem where rownum < 20000;

       SID STATISTIC#      VALUE (SELECTNAMEFROMV$STATNAMENWHERES.STATISTIC#=N.STATISTIC#AND(NAME
---------- ---------- ---------- ----------------------------------------------------------------
         9        167          0 cleanouts and rollbacks - consistent read gets
         9        171          0 rollback changes - undo records applied
         9        166         30 rollbacks only - consistent read gets
         9        172          0 transaction rollbacks
         9        162          0 transaction tables consistent read rollbacks
         9          5          0 user rollbacks 

From the "Oracle 9i Database Concepts"
<>
During direct-path INSERT operations, Oracle appends the inserted data after
existing data in the table. Data is written directly into datafiles, bypassing the buffer cache. Free space in the existing data is not reused, and referential
integrity constraints are ignored.
<>

What is the mechanism that happens during direct load
which causes direct load to bypass refrential constraints;
however conventional load to verify the constraints

CTAS is "inside" database.
"rollbacks only - consistent read gets"
is increased. Is it safe to assume UNDO is
generated during direct writes

If so, does direct writes only affects the freelist
( mecahnism of finding blocks and keeping available
blocks )

Tom,
I would like your expert opinion on running a query with db links. The options I have are listed below. The query below is executed as a job every 15 minutes.

Count of records in table1(database1): 500000
Count of records in table2(database2): 5000000

Option 1
--------

Log into database2 and run the query below
(dbLink1 - from database 2 to database1)

INSERT INTO table1@dbLink1
SELECT * FROM table2
WHERE rundate > (SELECT MAX(s.rundate) from table1@dbLink1 S);

Explain plan on the above query is


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=420 Card=24662 Bytes=12774916)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'table2' (Cost=420 Card=24662 Bytes=12774916)

2 1 INDEX (RANGE SCAN) OF 'table2_N5' (NON-UNIQUE) (Cost=16 Card=4439)
3 2 REMOTE* dbLink1
3 SERIAL_FROM_REMOTE SELECT MAX("A1"."RUNDATE") FROM "table1" "A1"


Option 2
--------

Log into database1 and run the query below
(dbLink2 - from database1 to database2)

INSERT INTO table1
SELECT * FROM table2@dbLink2
WHERE rundate > (SELECT MAX(s.rundate) from table1 S);


Thank you

Explain plan for option 2

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=420 Card=24662 Bytes=12774916)
1 0 FILTER
2 1 REMOTE* (Cost=420 Card=24662 Bytes=12774916) dbLink2
3 1 SORT (AGGREGATE)
4 3 INDEX (FULL SCAN (MIN/MAX)) OF 'table1_I1' (NON-UNIQUE) (Cost=66985 Card=5429090 Bytes=43432720)
2 SERIAL_FROM_REMOTE SELECT "STATUS","RUNDATE","INVOICECREATIONDATE","INVOICECREATEDBY","INVOICEUPDAT



In addition to option 1 and 2, would like your opinion on option 3
Option3
Create trigger on database2.table2 to insert record into database1.table1

Would really appreciate your comments on each of these options.

Thank you

Tom,
I really appreciate your answer. Do you have any reasons in particular or any rule/logic for picking option #1 over option#2, and why is option#3 a "no go under any circumstances".

Thank you

In our data warehouse environment, we have a pretty big table (around 700 million records). The table is partitioned on the week code.
The daily job does delete and insert in this table and the deletes may happen in any partition (based on the primary key value). Daily around 300K records gets deleted.

It was found out a simple insert statement was taking 3-4 hours to insert 200K records. Later I modified it by putting append hint and started running in less than 20 minutes.

My concern is space. If we use append hint oracle will insert above the HWM and all the space released by delete statement goes waste.

Questions: 1.Is there a better way than delete/insert? I can't partition the table in such a way that I can drop or truncate the entire partition. As of now deletes are inevitable.

2. I know by re-org the table the space that are not used can be reclaimed back. Since this is a daily job, there is hardly any window time to do the re-orgs. Is there a better way to approach this problem?

thanks,

sns from austin,tx said:

> In our data warehouse environment, we have a pretty big
> table (around 700 million records). The table is
> partitioned on the week code.
> The daily job does delete and insert in this table and the
> deletes may happen in any partition (based on the primary
> key value). Daily around 300K records gets deleted.

FWIW, I tend to stay away as far as possible from UPDATEs and DELETEs when dealing with VLTs in Warehousing in general. Exactly because of the problems you describe.

What I aim for is a partitioning scheme that allows truncating partitions for aging old data as oppose to deleting the data.

When it comes to updating data, I rather do a CTAS and re-create the entire up-to-date data set for a partition and then swap the contents of that CTAS table with the partition.

The largest fact table I have is a bit larger than yours - but (touch wood) no space issues and long run-time windows because of the goal of dealing only with direct load paths and not UPDATEs and DELETEs.

Partitioning is not only a means to make data volumes to process for querying purposes smaller, but with my DBA hat on, it is equally important to use partitioning to make data management smarter and simpler.

You mean parallalise the insert without using "append" hint? I thought enabling the parallel dml for a session makes sense only if we use "append" hint. I will without using append hint.

About partitioning the table and truncating the partitions instead of delete/insert or update is almost not possible. The table has order_num as primary key and I cannnot partition on that.

When the new data for the same order_number comes, either we have to delete the data and re-insert it or update the data. It was found delete/insert was faster than update since there are more than 50 columns in the table.

The order_number that gets updated today may be created a year ago, two years ago or even last week. So there is no way for us to partition in such a way to truncate the old data and re-insert the new one.

Anyway thanks for Billy for looking into my question.

Hi Tom where is this lost space could you give me some clue about, why using direct path inserts I use more space (after a truncate) and using conventional insert I don't.
Why it takes 220.31 instead of 219.5 in DPI
Thank you in advance.


The size was the not same using the append hint and using the conventional insert
Using Conventional Insert        219.5        MB

Using Append hint        220.3125 MB
 This size don’t change if you change the arraysize from 1 to 5000

Initial Segment         .0625        MB

truncate table fon.hicuentasf_rw2;
SQL>select segment_Name, sum(bytes)/1024/1024 from dba_segments
SQL>  where segment_name in ('HICUENTASF_RW2')
SQL>  group by segment_Name order by 2 desc
           .0625 ( Initial segment size)

Example:

SQL>truncate table fon.hicuentasf_rw2;
SQL> insert /*+ append */ into fon.hicuentasf_rw2
  2  select * from fon.hicuentasf_rw;
4257616 filas creadas.

SQL> select segment_Name, sum(bytes)/1024/1024 from dba_segments
  2  where segment_name in ('HICUENTASF_RW2')
  3  group by segment_Name order by 2 desc
  4  ;
            220.3125 (APPEND)
SQL> truncate table fon.hicuentasf_rw2;
SQL> insert into fon.hicuentasf_rw2
  2  select * from fon.hicuentasf_rw;
4257616 filas creadas.

SQL> select segment_Name, sum(bytes)/1024/1024 from dba_segments
  2  where segment_name in ('HICUENTASF_RW2')
  3  group by segment_Name order by 2 desc;

               219.5 (CONVENTIONAL INSERT)
SQL> SET ARRAYSIZE 5000
SQL> truncate table fon.hicuentasf_rw2;
SQL> insert /*+ append */ into fon.hicuentasf_rw2
  2     select * from fon.hicuentasf_rw;
4257616 filas creadas.
SQL> COMMIT;
SQL> select segment_Name, sum(bytes)/1024/1024 from dba_segments
  2    where segment_name in ('HICUENTASF_RW2')
  3    group by segment_Name order by 2 desc;
            220.3125 (APPEND ARRAYSIZE 5000)

SQL>  SET ARRAYSIZE 1
SQL> truncate table fon.hicuentasf_rw2;
SQL> insert /*+ append */ into fon.hicuentasf_rw2
  2     select * from fon.hicuentasf_rw;
SQL> select segment_Name, sum(bytes)/1024/1024 from dba_segments
  2    where segment_name in ('HICUENTASF_RW2')
  3    group by segment_Name order by 2 desc;
            220.3125 (APPEND ARRAYSIZE 1)
 

Thanks Tom, ok, mean while this is not more than 1MB, i must not worry.

Anyway this is not a dictionary managed database

CREATE DATABASE xxx
CONTROLFILE REUSE
LOGFILE
GROUP 1 ('D:\oraxxx\logfiles\log_1xxx.ora', 'E:\oraxxx\logfiles\log_1xxx.ora') SIZE 10M,
GROUP 2 ('D:\oraxxx\logfiles\log_2xxx.ora', 'E:\oraxxx\logfiles\log_2xxx.ora') SIZE 10M
MAXLOGFILES 32
DATAFILE 'E:\oraxxx\datafiles\dfl_sys_xxx'
size 200M
REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
DEFAULT TEMPORARY TABLESPACE TBL_TEMP TEMPFILE 'D:\oraxxx\datafiles\DFL_TEMP_xxx'
SIZE 50M
REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 640K
UNDO TABLESPACE TBL_UNDO DATAFILE 'E:\oraxxx\datafiles\dfl_undo_xxx'
SIZE 50M
REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
MAXLOGHISTORY 1
MAXLOGFILES 3
MAXLOGMEMBERS 3
MAXDATAFILES 15
MAXINSTANCES 1
ARCHIVELOG
EXCLUSIVE
CHARACTER SET WE8ISO8859P1
NATIONAL CHARACTER SET AL16UTF16
;

-- Tablespace usuario
CREATE TABLESPACE TBL_USERS DATAFILE 'E:\oraxxx\datafiles\DFL_USER_xxx'
SIZE 100M
REUSE AUTOEXTEND ON NEXT 640k MAXSIZE UNLIMITED
SEGMENT SPACE MANAGEMENT AUTO
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K ;

Thanks Again Tom,

0) is better not to use uniform size, why?
1) Are you suggesting to use autoallocate in the data tablespace?,
2) Do I should have the same in the index tablespace

CREATE TABLESPACE TBL_INDX DATAFILE 'D:\oraXXXX\datafiles\DFL_INDX_XXXX'
SIZE 100M
REUSE AUTOEXTEND ON NEXT 640k MAXSIZE UNLIMITED
SEGMENT SPACE MANAGEMENT AUTO
BLOCKSIZE 16K
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 80K ;

Thank you in advance Tom

Thanks a lot Tom.

Create table V_RPT
(
UID VARCHAR2(40),
LID VARCHAR2(10),
SUBMIT_DATE DATE,
SCORE VARCHAR2(4),
STATUS VARCHAR2(1),
RATE NUMBER(7,3)
)
tablespace RPT_TS pctfree 10 pctused 90
storage ( initial 65536k next 65536k pctincrease 0 minextents 24 maxextents 4096 freelists 2 )
nologging nocache;

Insert /*+ APPEND */ into V_RPT
(UID, LID, SUBMIT_DATE, SCORE, STATUS, RATE)
Select a.UID,a.LID,a.SUBMIT_DATE,b.SCORE,v.STATUS,l.RATE
From T1 b,
T2 l,
T3 v,
T4 a
Where v.V_TYPE = 'B'
And a.A_CREATED = 'L'
And b.A_TYPE = 'P'
And a.A_SOURCE is null
And a.UID = v.UID
And a.LID = v.LID
And a.UID = b.UID
And a.LID = b.LID
And a.UID = l.UID(+)
And a.LID = l.LID(+)
And a.SUBMIT_DATE > trunc(SYSDATE - 60)
And a.SUBMIT_DATE < trunc(SYSDATE);

Statistics
------------------------------------------------------
2429 recursive calls
3980 db block gets
96859538 consistent gets
5343862 physical reads
8112420 redo size
750 bytes sent via SQL*Net to client
2417 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
30 sorts (memory)
0 sorts (disk)
8181862 rows processed

The composites indexes on these four tables are the following. These indices are used by different Queries. No new indexes were created specifically for this Insert-Append-Select.

Table Index Column and Index Create Order
----- ----------- --------------------------------
T1 UI_T1_1 UID, A_TYPE, LID
T2 UI_T2_1 LID, UID
T3 NI_T3_1 V_TYPE, UID, LID, STATUS
T4 NI_T4_1 SUBMIT_DATE, LID, A_SOURCE

Table T1 has 12m rows
Table T2 has 7m rows
Table T3 has 14m rows
Table T4 has 9m rows

The no of Selected rows for Insert Append is over 8m rows.
The whole process takes over 3 hours with NOLOGGING on V_RPT.

Question:
The whole process use to take less then 40 minutes two weeks back and
now more then 3 hours.
Nothing has changed as far as the structure of the objects, or the indices.
Checked the system and i/o waits, nothing that should signal a red flag.
Host is Sun 68k with 20 CPU's and 40G of RAM.

1>Is there any way to find out what is taking so long.
2>Is there anything to tune in the Select part?
3>Any method to reduce the "consistent gets".

Apreciate your time and advice on this.

db_block_size integer 4096
db_file_multiblock_read_count integer 8
hash_multiblock_io_count integer 12

object_cache_optimal_size integer 102400
optimizer_features_enable string 8.1.7
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_max_permutations integer 80000
optimizer_mode string CHOOSE
optimizer_percent_parallel integer 0

fast_start_parallel_rollback string LOW
optimizer_percent_parallel integer 0
parallel_adaptive_multi_user boolean FALSE
parallel_automatic_tuning boolean FALSE
parallel_broadcast_enabled boolean FALSE
parallel_execution_message_size integer 2152
parallel_instance_group string
parallel_max_servers integer 10
parallel_min_percent integer 0
parallel_min_servers integer 0
parallel_server boolean FALSE
parallel_server_instances integer 1
parallel_threads_per_cpu integer 2
recovery_parallelism integer 0

************************************************************************
Plan for Insert-Append-Select for 60 days

Execution Plan
----------------------------------------------------------
ERROR:
ORA-12838: cannot read/modify an object after modifying it in parallel


SP2-0612: Error generating AUTOTRACE EXPLAIN report

Statistics
----------------------------------------------------------
1625 recursive calls
721574 db block gets
70283454 consistent gets
31720738 physical reads
227244 redo size
748 bytes sent via SQL*Net to client
2463 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
38 sorts (memory)
11 sorts (disk)
8442709 rows processed

**************************************************************************
Plan for Select count(A.UID) with same predicates as Insert-Append-Select for 60 days......

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=139661 Card=1 Bytes=65)

1 0 SORT (AGGREGATE)
2 1 SORT* (AGGREGATE) :Q41002
3 2 NESTED LOOPS* (OUTER) (Cost=139661 Card=21162 Bytes=13 :Q41002
75530)
4 3 NESTED LOOPS* (Cost=107333 Card=16164 Bytes=824364) :Q41002
5 4 HASH JOIN* (Cost=74259 Card=16537 Bytes=611869) :Q41002
6 5 TABLE ACCESS* (BY INDEX ROWID) OF 'T4' :Q41000
(Cost=38239 Card=144651 Bytes=3326973)

7 6 INDEX (RANGE SCAN) OF 'NI_T4_1' (NON-UNIQUE)
(Cost=1955 Card=144651)

8 5 INDEX* (FAST FULL SCAN) OF 'UI_T1_1' :Q41001
' (UNIQUE) (Cost=24585 Card=3060948 Bytes=42853272)

9 4 INDEX* (UNIQUE SCAN) OF 'UI_T3_1' (UNIQ :Q41002
UE) (Cost=2 Card=6779737 Bytes=94916318)

10 3 INDEX* (UNIQUE SCAN) OF 'UI_T2_1' (UNI :Q41002
QUE) (Cost=2 Card=7014740 Bytes=98206360)



2 PARALLEL_TO_SERIAL SELECT /*+ PIV_SSF */ SYS_OP_MSR(COUNT(*)) F
ROM (SELECT /*+ ORDERED NO_EXPAND US

3 PARALLEL_COMBINED_WITH_PARENT
4 PARALLEL_COMBINED_WITH_PARENT
5 PARALLEL_COMBINED_WITH_PARENT
6 PARALLEL_FROM_SERIAL
8 PARALLEL_TO_PARALLEL SELECT /*+ Q41001 */ A1."UID" C
0,A1."LID" C1 FROM :I."T4

9 PARALLEL_COMBINED_WITH_PARENT
10 PARALLEL_COMBINED_WITH_PARENT


Statistics
----------------------------------------------------------
103 recursive calls
171 db block gets
53489377 consistent gets
35028393 physical reads
2388 redo size
315 bytes sent via SQL*Net to client
358 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
14 sorts (memory)
3 sorts (disk)
1 rows processed


*****************************************************************************
Plan for Select A.UID with same predicates as Insert-Append-Select for 20 days......

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=139661 Card=21162 Bytes=1375530)

1 0 NESTED LOOPS* (OUTER) (Cost=139661 Card=21162 Bytes=137553 :Q430020)

2 1 NESTED LOOPS* (Cost=107333 Card=16164 Bytes=824364) :Q43002
3 2 HASH JOIN* (Cost=74259 Card=16537 Bytes=611869) :Q43002
4 3 TABLE ACCESS* (BY INDEX ROWID) OF 'T4' (Cos :Q43000
t=38239 Card=144651 Bytes=3326973)

5 4 INDEX (RANGE SCAN) OF 'NI_T4_1' (NON-UNIQUE) (Co
st=1955 Card=144651)

6 3 INDEX* (FAST FULL SCAN) OF 'UI_T1_1' (U :Q43001
NIQUE) (Cost=24585 Card=3060948 Bytes=42853272)

7 2 INDEX* (UNIQUE SCAN) OF 'UI_T3_1' (UNIQUE) :Q43002
(Cost=2 Card=6779737 Bytes=94916318)

8 1 INDEX* (UNIQUE SCAN) OF 'UI_T2_1' (UNIQUE) :Q43002
(Cost=2 Card=7014740 Bytes=98206360)



1 PARALLEL_TO_SERIAL SELECT /*+ ORDERED NO_EXPAND USE_NL(A2) INDE
X(A2 "UI_T2_1") */ A1.

2 PARALLEL_COMBINED_WITH_PARENT
3 PARALLEL_COMBINED_WITH_PARENT
4 PARALLEL_FROM_SERIAL
6 PARALLEL_TO_PARALLEL SELECT /*+ Q43001 */ A1."UID" C
0,A1."LID" C1 FROM :I."T4

7 PARALLEL_COMBINED_WITH_PARENT
8 PARALLEL_COMBINED_WITH_PARENT


Statistics
----------------------------------------------------------
133 recursive calls
175 db block gets
16785768 consistent gets
21527731 physical reads
692 redo size
58713453 bytes sent via SQL*Net to client
13946776 bytes received via SQL*Net from client
172180 SQL*Net roundtrips to/from client
11 sorts (memory)
6 sorts (disk)
2582680 rows processed


One more index that is used in the Select is
Table Index Column and Index Create Order
----- ----------- --------------------------------
T3 UI_T3_1 UID,V_TYPE,LID

Many Thanks for sparing time and answering the questions during a weekend.

Appology for wasting the white space.
Here is the Query and the Plan.

Select a.UID,a.LID,a.SUBMIT_DATE,b.SCORE,v.STATUS,l.RATE
From T1 b,
T2 l,
T3 v,
T4 a
Where v.V_TYPE = 'B'
And a.A_CREATED = 'L'
And b.A_TYPE = 'P'
And a.A_SOURCE is null
And a.UID = v.UID
And a.LID = v.LID
And a.UID = b.UID
And a.LID = b.LID
And a.UID = l.UID(+)
And a.LID = l.LID(+)
And a.SUBMIT_DATE > trunc(SYSDATE - 4)
And a.SUBMIT_DATE < trunc(SYSDATE);

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=307912 Card=21162 By
tes=3110814)

1 0 NESTED LOOPS* (OUTER) (Cost=307912 Card=21162 Bytes=311081 :Q45002
4)

2 1 NESTED LOOPS* (Cost=259420 Card=16164 Bytes=2085156) :Q45002
3 2 HASH JOIN* (Cost=209809 Card=16537 Bytes=1554478) :Q45002
4 3 TABLE ACCESS* (BY INDEX ROWID) OF 'T4' (Cos :Q45000
t=38239 Card=144651 Bytes=10848825)

5 4 INDEX (RANGE SCAN) OF 'NI_T4_1' (NON-UNIQUE) (Co
st=1955 Card=144651)

6 3 TABLE ACCESS* (FULL) OF 'T1' (Cost=155057 Car :Q45001
d=3060948 Bytes=58158012)

7 2 TABLE ACCESS* (BY INDEX ROWID) OF 'T3' (Cost=3 Ca :Q45002
rd=6779737 Bytes=237290795)

8 7 INDEX* (UNIQUE SCAN) OF 'UI_T3_1' (UNIQUE :Q45002
) (Cost=2 Card=6779737)

9 1 TABLE ACCESS* (BY INDEX ROWID) OF 'T2' (Cost=3 C :Q45002
ard=7014740 Bytes=126265320)

10 9 INDEX* (UNIQUE SCAN) OF 'UI_T2_1' (UNIQU :Q45002
E) (Cost=2 Card=7014740)



1 PARALLEL_TO_SERIAL SELECT /*+ ORDERED NO_EXPAND USE_NL(A2) INDE
X(A2 "UI_T2_1") */ A1.

2 PARALLEL_COMBINED_WITH_PARENT
3 PARALLEL_COMBINED_WITH_PARENT
4 PARALLEL_FROM_SERIAL
6 PARALLEL_TO_PARALLEL SELECT /*+ Q45001 NO_EXPAND ROWID(A1) */ A1.
"UID" C0,A1."LID"

7 PARALLEL_COMBINED_WITH_PARENT
8 PARALLEL_COMBINED_WITH_PARENT
9 PARALLEL_COMBINED_WITH_PARENT
10 PARALLEL_COMBINED_WITH_PARENT


Statistics
----------------------------------------------------------
195 recursive calls
721531 db block gets
3645206 consistent gets
1443742 physical reads
732 redo size
61148597 bytes sent via SQL*Net to client
1734811 bytes received via SQL*Net from client
21415 SQL*Net roundtrips to/from client
10 sorts (memory)
11 sorts (disk)
321203 rows processed

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=591966 Card=247462 Bytes=18312188)

1 0 HASH JOIN* (OUTER) (Cost=591966 Card=247462 Bytes=18312188) :Q54006
2 1 HASH JOIN* (Cost=424152 Card=189017 Bytes=10584952) :Q54004
3 2 MERGE JOIN* (Cost=326543 Card=193374 Bytes=7928334) :Q54002
4 3 SORT* (JOIN) (Cost=133768 Card=1691428 Bytes=38902844) :Q54002

5 4 TABLE ACCESS* (FULL) OF 'T4' (Cost=108757 :Q54000
Card=1691428 Bytes=38902844)

6 3 SORT* (JOIN) (Cost=192775 Card=3060948 Bytes=55097064) :Q54002

7 6 TABLE ACCESS* (FULL) OF 'T1' (Cost=155057 C :Q54001
ard=3060948 Bytes=55097064)

8 2 TABLE ACCESS* (FULL) OF 'T3' (Cost=71347 Card=677 :Q54003
9737 Bytes=101696055)

9 1 TABLE ACCESS* (FULL) OF 'T2' (Cost=131621 Card=7 :Q54005
014740 Bytes=126265320)

1 PARALLEL_TO_SERIAL SELECT /*+ ORDERED NO_EXPAND USE_HASH(A2) */
A1.C0,A1.C1,A1.C2,A1.C3,A1.C4,A1.C5

2 PARALLEL_TO_PARALLEL SELECT /*+ ORDERED NO_EXPAND USE_HASH(A2) */
A1.C0 C0,A1.C1 C1,A2.C2 C2,A1.C4 C3

3 PARALLEL_TO_PARALLEL SELECT /*+ ORDERED NO_EXPAND USE_MERGE(A2) *
/ A1.C0 C0,A1.C1 C1,A2.C3 C2,A2.C2 C

4 PARALLEL_COMBINED_WITH_PARENT
5 PARALLEL_TO_PARALLEL SELECT /*+ Q54000 NO_EXPAND ROWID(A1) */ A1.
"UID" C0,A1."LID"

6 PARALLEL_COMBINED_WITH_PARENT
7 PARALLEL_TO_PARALLEL SELECT /*+ Q54001 NO_EXPAND ROWID(A1) */ A1.
"UID" C0,A1."LID"

8 PARALLEL_TO_PARALLEL SELECT /*+ Q54003 NO_EXPAND ROWID(A1) */ A1.
"UID" C0,A1."LID"

9 PARALLEL_TO_PARALLEL SELECT /*+ Q54005 NO_EXPAND ROWID(A1) */ A1.
"UID" C0,A1."LID"



Statistics
----------------------------------------------------------
3582 recursive calls
1806616 db block gets
3101384 consistent gets
21688257 physical reads
652 redo size
638218799 bytes sent via SQL*Net to client
43784665 bytes received via SQL*Net from client
540549 SQL*Net roundtrips to/from client
73 sorts (memory)
19 sorts (disk)
8108209 rows processed

The improvement was two fold, in terms of time to obtain a resultset from 'Select'
and the reduction in 'consistent gets'.
Again few Questions.
>Is it not to trick the optimizer with the method you proposed to eliminate the Nested Loops?
Just a brief on your thought process on why you proposed this theorey.
>Inspite of improvement in time to get a result set, why a FTS on all four tables?
>Although the parallel DML was not enabled for the session that did the select, why
do one see many active parallel process.

My gratitude and sincere appreciation for the help.

I generated a flat file using
Select '"'||a.UID ||'","'|| a.LID ||'","'||to_char(a.SUBMIT_DATE, 'YYYYMMDD HH24:MI:SS')||'","'||
b.SCORE ||'","'|| v.STATUS ||'","'||l.RATE||'"|';

"5032191030","DTC","20050326 00:00:01","","N",""|

Create table external_table
(
UID VARCHAR2(40),
LID VARCHAR2(10),
SUBMIT_DATE DATE,
SCORE VARCHAR2(4),
STATUS VARCHAR2(1),
RATE NUMBER(7,3)
)
organization external
( type oracle_loader
default directory data_dir
access parameters (
records delimited by '|'
badfile data_dir:'p_rpt%a_%p.bad'
logfile data_dir:'p_rpt%a_%p.log'
nologfile
fields terminated by ','
optionally enclosed by '"' and '"' lrtrim
missing field values are null
(
UID,
LID,
SUBMIT_DATE char date_format date mask "YYYYMMDD HH24:MI:SS",
SCORE,
STATUS,
RATE
)
)
location ('ff.txt')
)
reject limit unlimited;

Create table P_RPT
(
UID VARCHAR2(40),
LID VARCHAR2(10),
SUBMIT_DATE DATE,
SCORE VARCHAR2(4),
STATUS VARCHAR2(1),
RATE NUMBER(7,3),
CREATE_DATE DATE default SYSDATE,
UPDATE_DATE DATE default SYSDATE
)
partition by range( SUBMIT_DATE )
(
partition MAR2005 values less than ( to_date( '01-apr-2005', 'dd-mon-yyyy') ),
partition APR2005 values less than ( to_date( '01-may-2005', 'dd-mon-yyyy') ),
partition MAY2005 values less than ( to_date( '01-jun-2005', 'dd-mon-yyyy') )
)
parallel ( degree 3 );

A external table created above was used to populate the above partition table using
Insert /*+ APPEND */ into P_RPT Select * From EXTERNAL_TABLE;

Insert /*+ APPEND */ into P_RPT
(UID,LID,SUBMIT_DATE,SCORE,STATUS,RATE) Select * From EXTERNAL_TABLE;

Insert /*+ APPEND */ into P_RPT
(UID,LID,SUBMIT_DATE,SCORE,STATUS,RATE)
Select UID,LID,SUBMIT_DATE,SCORE,STATUS,RATE From EXTERNAL_TABLE;

Insert /*+ APPEND */ into P_RPT Select * From P_RP;

--Where P_RP is a non-partitioned table with same column and order as the partitioned table P_RPT.

All the above failed with the below error.

ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

Data was loaded into non-partitioned table P_RP using the below SQL, and executed without errors.
Insert /*+ APPEND */ into P_RP Select * From EXTERNAL_TABLE;

Tried creating the partitioning table with the below method too for the dates, but still errors

partition MAR2005 values less than ( to_date( '20050401', 'YYYYMMDD') ),
partition APR2005 values less than ( to_date( '20050501', 'YYYYMMDD') ),
partition MAY2005 values less than ( to_date( '20050601', 'YYYYMMDD') )

Could you please let me know, where I am going wrong.

The flat file had similar data

"5055909913","CTY","20050524 00:00:01","0","U","14.5"|
                    --------

SQL> select count(*) from external_table where submit_date >= to_date( '20050601', 'YYYYMMDD');

  COUNT(*)
----------
         0
SQL> select count(*) from p_rp where submit_date >= to_date( '20050601', 'YYYYMMDD');

  COUNT(*)
----------
         0

select count(*) from external_table where submit_date >= to_date( '20050301', 'YYYYMMDD');

  COUNT(*)
----------
    170145

SQL> select count(*) from p_rp where submit_date >= to_date( '20050301', 'YYYYMMDD');

  COUNT(*)
----------
    170145

SQL> select count(*) from external_table where submit_date >= to_date( '20050524', 'YYYYMMDD');

  COUNT(*)
----------
    170145

SQL> select count(*) from p_rp where submit_date >= to_date( '20050524', 'YYYYMMDD');

  COUNT(*)
----------
    170145

SQL> select min(submit_date) from p_rp;

MIN(SUBMIT_
-----------
24-MAY-2005

SQL> select min(submit_date) from external_table;

MIN(SUBMIT_
-----------
24-MAY-2005

SQL> select max(submit_date) from external_table;

MAX(SUBMIT_
-----------
24-MAY-2005

SQL> select max(submit_date) from p_rp;

MAX(SUBMIT_
-----------
24-MAY-2005

SQL> select count(*) from p_rp where submit_date >= to_date( '20050525', 'YYYYMMDD');

  COUNT(*)
----------
         0

select count(*) from external_table where submit_date >= to_date( '20050525', 'YYYYMMDD');

  COUNT(*)
----------
         0 

Many Thanks for the help.

Tom i have a quick question. pl tell me what are all the precautions that i should take when i am inserting huge millions of rows into table .

It's probably just me but I read "direct path" early in this thread and it seems to mean using the /*+ APPEND */ hint. In other threads it seems to refer to the bifurcation of SQL*Loader's conventional vs direct path load.

Which is it? or Both?

When SQLLDR does a conventional path load in APPEND mode, does it use /*+ APPEND */?

The keyword APPEND when used in an INSERT statement hint means Direct Path but in sqlldr the keyword APPEND doesn't.

It would have been less confusing if the sqlldr keyword was 'DONT_DELETE' or 'ADD_TO' OR the INSERT statement hint was 'DIRECT_PATH'

It's kinda like to shutdown windows you have to press the 'START' button.

Tom,

I found that, the extended plan will not show LOAD AS SELECT for APPEND hint when Foreign Key is enabled in 10g. It was not so in 9i. Is this expected behaviour? Can you shed some light?

I have my test case here(First 10G next 9205 database on HP UX ):

20:45:53 sql>drop table t1 cascade constraints;

Table dropped.

20:46:12 sql>drop table t2 cascade constraints;

Table dropped.

20:46:12 sql>drop table t22 cascade constraints;

Table dropped.

20:46:12 sql>
20:46:12 sql>create table t1 ( x int primary key );

Table created.

20:46:13 sql>create table t2 ( y references t1 );

Table created.

20:46:13 sql>create table t22 as (select * from t2 where 1=2);

Table created.

20:46:13 sql>
20:46:13 sql>analyze table t1 compute statistics;

Table analyzed.

20:46:13 sql>analyze table t2 compute statistics;

Table analyzed.

20:46:13 sql>analyze table t22 compute statistics;

Table analyzed.

20:46:13 sql>
20:46:13 sql>exec gen_data('t22', 100000);

PL/SQL procedure successfully completed.

20:46:32 sql>select 'alter table t2 disable constraint ' || constraint_name || ' ;'
20:46:34 2 from user_constraints
20:46:34 3 where table_name = 'T2';

'ALTERTABLET2DISABLECONSTRAINT'||CONSTRAINT_NAME||';'
-------------------------------------------------------------------
alter table t2 disable constraint SYS_C00270466 ;

1 row selected.

20:46:35 sql>
20:46:35 sql>set lines 75
20:46:35 sql>explain plan for
20:46:35 2 insert /*+ APPEND */ into t2 (select * from t22);

Explained.

20:46:35 sql>select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 3800592419

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T22 | 1 | 13 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------

8 rows selected.

20:46:35 sql>alter table t2 disable constraint SYS_C00270466 ;

Table altered.

20:46:44 sql>explain plan for
20:46:56 2 insert /*+ APPEND */ into t2 (select * from t22);

Explained.

20:46:56 sql>select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 4072789352

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
| 1 | LOAD AS SELECT | | | | | |
| 2 | TABLE ACCESS FULL| T22 | 1 | 13 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------

9 rows selected.

20:46:57 sql>select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - 64bi
PL/SQL Release 10.1.0.4.0 - Production
CORE 10.1.0.4.0 Production
TNS for HPUX: Version 10.1.0.4.0 - Production
NLSRTL Version 10.1.0.4.0 - Production

5 rows selected.

20:47:05 sql>spool off
20:52:35 sql>select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
PL/SQL Release 9.2.0.5.0 - Production
CORE 9.2.0.6.0 Production
TNS for HPUX: Version 9.2.0.5.0 - Production
NLSRTL Version 9.2.0.5.0 - Production

5 rows selected.

20:52:43 sql>drop table t1 cascade constraints;

Table dropped.

20:52:53 sql>drop table t2 cascade constraints;

Table dropped.

20:52:53 sql>drop table t22 cascade constraints;

Table dropped.

20:52:54 sql>
20:52:54 sql>create table t1 ( x int primary key );

Table created.

20:52:54 sql>create table t2 ( y references t1 );

Table created.

20:52:54 sql>create table t22 as (select * from t2 where 1=2);

Table created.

20:52:54 sql>
20:52:54 sql>analyze table t1 compute statistics;

Table analyzed.

20:52:54 sql>analyze table t2 compute statistics;

Table analyzed.

20:52:54 sql>analyze table t22 compute statistics;

Table analyzed.

20:52:54 sql>
20:52:54 sql>exec gen_data('t22', 100000);

PL/SQL procedure successfully completed.

20:53:08 sql>set lines 75
20:53:11 sql>explain plan for
20:53:11 2 insert /*+ APPEND */ into t2 (select * from t22);

Explained.

20:53:11 sql>select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------

--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 13 | 1 |
| 1 | LOAD AS SELECT | | | | |
| 2 | TABLE ACCESS FULL | T22 | 1 | 13 | 1 |
--------------------------------------------------------------------

Note: cpu costing is off

10 rows selected.

20:53:12 sql>spool off

Thanks,
Shivaswamy

this was true in 9i, 8i, and before.  9i may well have said "load as select" but it was "not being truthful"

direct path loads using append are not available with foreign keys.

ops$tkyte@ORA9IR2> create table t1 ( x int primary key );

Table created.

ops$tkyte@ORA9IR2> insert into t1 values ( 1 );

1 row created.

ops$tkyte@ORA9IR2> commit;

Commit complete.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create table t2 ( x constraint t2_fk references t1 );

Table created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert /*+ append */ into t2 select 1 from dual;

1 row created.

ops$tkyte@ORA9IR2> select * from t2;

         X
----------
         1

<b>that I could read the table shows it was not DIRECT PATH</b>

ops$tkyte@ORA9IR2> rollback;

Rollback complete.

ops$tkyte@ORA9IR2> alter table t2 disable constraint t2_fk;

Table altered.

ops$tkyte@ORA9IR2> insert /*+ append */ into t2 select 1 from dual;

1 row created.

ops$tkyte@ORA9IR2> select * from t2;
select * from t2
              *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

<b>that I cannot read the table shows it WAS direct path</b>
 

Tom,

The documentation says:

</code> http://oraclesvca2.oracle.com/docs/cd/B14117_01/server.101/b10739/tables.htm#i1009100 <code>
[Oracle® Database Administrator's Guide 10g Release 1 (10.1)
Part Number B10739-01]

During direct-path INSERT operations, the database appends the inserted data after existing data in the table. Data is written directly into datafiles, bypassing the buffer cache. Free space in the existing data is not reused, and referential integrity constraints are ignored. These procedures combined can enhance performance.

Further, the data can be inserted either in serial mode, where one process executes the statement, or parallel mode, where multiple processes work together simultaneously to run a single SQL statement. The latter is referred to as parallel execution.

Tom, Do you mean to say from the illustrated case above that, direct insert can ONLY take place in parallel mode?
Can you please clarify?

Thanks,
Shivaswamy

Okay.. then why can not I conclude it did Direct insert serially.. after all the plan told LOAD AS SELECT.

Not able to read table (ORA-12838) comes when inserted in PARALLEL and trying to read before commit is it not?
Not necessarily only for for Direct Insert. Is this not right? We will get ORA-12838 for direct insert IF done in parallel. If done serially why should I expect to get 12838?

I'm not sure what you are saying... but let me just say:


DIRECT PATH loads via insert /*+ append */ are available to both PARALLEL and SERIAL execution plans.


the ora-12838 results from serial plans as well:


ops$tkyte@ORA9IR2> create table t  ( x int );

Table created.

ops$tkyte@ORA9IR2> insert /*+ APPEND */ into t select 1 from dual;

1 row created.

ops$tkyte@ORA9IR2> select * from t;
select * from t
              *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel



I will agree with you in advance that the error message is a little obscure. 

Tom,
can you tell me how I can conclusively say, if I have used Direct path insert or not? Here is the 10046 trace snip:

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

insert /*+ append */ into t2 select 1 from dual


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

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

Rows Row Source Operation
------- ---------------------------------------------------
1 FAST DUAL (cr=0 pr=0 pw=0 time=5 us)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.04 0.04
********************************************************************************
Thanks.

Tom,

I ran two identical (insert...select) statements with the only difference that one had an APPEND hint and the other did not...
The APPEND hint statement ran SLOWER than the non-APPEND hint.
This behavior is consistent over many tests.
Do you have any idea why this would be?
Here is the tkprof output... sorted by elapsed time.

Note: I truncated the 'test_parallel' table between statements.
Note: The 'test_parallel' table is degree=1 for these tests.

Thanks, Robert.

insert /*+ append */ into test_parallel
select /*+ null */ * from test_large

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 779.30 861.07 822510 829280 5300 30425088
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 779.31 861.08 822510 829280 5300 30425088

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

Rows Row Source Operation
------- ---------------------------------------------------
1 LOAD AS SELECT
30425088 TABLE ACCESS FULL TEST_LARGE

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

insert /*+ null */ into test_parallel t
select /*+ null */ * from test_large s

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 326.30 669.92 775547 1653717 4555971 30425088
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 326.30 669.92 775547 1653717 4555971 30425088

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

Rows Row Source Operation
------- ---------------------------------------------------
30425089 TABLE ACCESS FULL TEST_LARGE

Tom,
If I create a fresh table and do insert /*+ append */ in one case and in second case I take a direct path export and import into this table, which would be faster?
Thanks

Tom,
Your answer did make my head spin like a blender but I got the answer. Looks like you are still in form after the 14 hours technology sesions...:)

We have to migrate large mix of large OLTP and datawarehouse tables across OS platform and from 9i to 10g and looks like export/import or insert (append) are our only options. I recently tested with export/import and it was too slow. Keeping the downtime minimum is top priority. Now I am going to try insert (append).

The approach I am planning to follow is:
a) Put the source database into restricted mode. Export all objects from source database using full=y and rows=n.
b) Import all objects into target database from this export. This will create all users and all database objects. No data will be imported. This is very quick. Takes less than half hour.
c) On the target database, disable all RI constraints and triggers since we have lot of trigger/sequence for generating surrogate primary keys.
d) Do an insert /*+ append */ data load into target database from source database. I can migrate many tables at the same time launching different sessions.
e)Enable RI and triggers on target database.

Please let me know what you think of this approach.

I have one more question. If I use insert /*+ append */ to migrate just the table data, would I need to rebuild the indexes after data migration?

Thanks so much for your help.

Tom,
You touched a raw nerve here. Upgrade to 10g in situ and then do cross platform TTS was what the DBAs had originally suggested. It was promptly shot down by the application management on the grounds that dual testing will be required after the upgrade to 10g and then after migration. Most of it was founded on FUD but what could we do.

I have used the method you described (multiple exports and multiple imports concurrently) successfully. I was just trying to explore other possibilities. I will soon be hitting tables with 600m+ rows. I just wanted to be sure that I use the fastest available method given all constraints.

Thanks...

I logged into 
myuser/myuser
SQL> insert /*+ APPEND */ INTO T1 SELECT * FROM BIGTABLE;

Now because bigtable has a lot of rows it will take 
about half an hour or more to complete.

Now while the above one is in process I logged with 
the same user from another session 
myuser/myuser
SQL>INERRT /*+ APPEND */ INTO T1 SELECT * FROM ANOTHER_BIG_TABLE;


My question is
Will the SECOND insert wait and do no processing until
I complete and commit the first one?

Thanks
 

Can you please explain it why?
It seems that the multiuser/session feature is
not supported in case of /*+append */.

Thanks

When we use /*+ append */ hint, there is no redo and undo
generated. But if we
ALTER DATABASE FORCE LOGGING;
and then use INSERT /*+ APPEND */

Will it generate redo undo both or it will generate redo only.

Thank you very much, very nice explanation.

In data guard (physical standby), when we
ALTER DATABASE FORCE LOGGING, and use /*+APPEND*/
then NOLOGGING is ignored and REDO is generated but UNDO is not generated on the primary database, does this (not generating of UNDO) effect the consistency of stand by
database.

Hi

I have created a table with minextents 256 (pre allocate the extents). Each extent has 25mb. Since the tablespace is LMT it created an INITIAL extent of 6400MB. So far I think my HWM is cero correct?

Before loading the segment size was 6400MB.

So I proceed to load 16 million rows.

After LOAD the segment size was 12800MB???!!!

Why Oracle didnt use my existent extents?

The funny thing is I thought TRUNCATE would reset the HWM, after truncate I still see 12800MB allocated to the segment!

Hmm I cannot agree with this statement

"direct pathing doesn't use any currently allocated space by definition, during a direct path operation - new extents are allocated and used"

My table is a brand new table, no data inserted whatsoever so my HWM is the lowest it can be. According to the doc

high-water mark is the level at which blocks have never been formatted to receive data

So since my table is new my blocks have never been formatted with data.

Your comments rised a new question though, you said that new extents are used, I always thought during direct load data is written above HWM and not ABOVE hwm and a new extent?


Truncate was showing me the wrong values due to bug 4142932 DBA_SEGMENTS.EXTENTS wrong for locally managed segment after TRUNCATE operation... (9.2.0.6)

when doing a parallel direct path load as you are, sqlldr allocates new extents.

Consider:

ops$tkyte@ORA10GR2> create table emp as select * from scott.emp where 1=0;
 
Table created.
 
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> select file_id, block_id, block_id+blocks-1 end_block, extent_id
  2    from dba_extents
  3   where owner = user
  4     and segment_name = 'EMP';
 
   FILE_ID   BLOCK_ID  END_BLOCK  EXTENT_ID
---------- ---------- ---------- ----------
         4       7097       7104          0
 
ops$tkyte@ORA10GR2> !sqlldr / emp direct=true parallel=true
 
SQL*Loader: Release 10.2.0.1.0 - Production on Mon Jun 26 10:36:31 2006
 
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
 
Load completed - logical record count 14.
 
ops$tkyte@ORA10GR2> select file_id, block_id, block_id+blocks-1 end_block, extent_id, dblock
  2    from (select *
  3            from dba_extents
  4                   where owner = user
  5                     and segment_name = 'EMP' ) a left outer join
  6         (select distinct dbms_rowid.rowid_block_number(rowid) dblock
  7                from emp) b
  8          on (b.dblock between a.block_id and a.block_id+a.blocks-1 )
  9   order by extent_id
 10  /
 
   FILE_ID   BLOCK_ID  END_BLOCK  EXTENT_ID     DBLOCK
---------- ---------- ---------- ---------- ----------
         4       7097       7104          0
         4       7105       7112          1       7109
 
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> truncate table emp;
 
Table truncated.
 
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> select file_id, block_id, block_id+blocks-1 end_block, extent_id
  2    from dba_extents
  3   where owner = user
  4     and segment_name = 'EMP';
 
   FILE_ID   BLOCK_ID  END_BLOCK  EXTENT_ID
---------- ---------- ---------- ----------
         4       7097       7104          0
 
ops$tkyte@ORA10GR2> !sqlldr / emp direct=true
 
SQL*Loader: Release 10.2.0.1.0 - Production on Mon Jun 26 10:36:32 2006
 
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
 
Load completed - logical record count 14.
 
ops$tkyte@ORA10GR2> select file_id, block_id, block_id+blocks-1 end_block, extent_id, dblock
  2    from (select *
  3            from dba_extents
  4                   where owner = user
  5                     and segment_name = 'EMP' ) a left outer join
  6         (select distinct dbms_rowid.rowid_block_number(rowid) dblock
  7                from emp) b
  8          on (b.dblock between a.block_id and a.block_id+a.blocks-1 )
  9   order by extent_id
 10  /
 
   FILE_ID   BLOCK_ID  END_BLOCK  EXTENT_ID     DBLOCK
---------- ---------- ---------- ---------- ----------
         4       7097       7104          0       7100
 

Hi

I have a 9.2.0.6 Linux database in noarchivelog mode.

I created a table in nologging mode (quite useless since I am in noarchivelog). I then ran an insert append operation as follows:

create table t1
(
a number,
b varchar2(64),
c varchar2(64),
d varchar2(64),
e varchar2(64)
)
tablespace users
pctused 1
pctfree 90
nologging;

insert /*+ append */ into t1
WITH tdata AS
(SELECT ROWNUM ID
FROM all_objects
WHERE ROWNUM <= 350)
SELECT ROWNUM,
DBMS_RANDOM.STRING ('U', 64),
DBMS_RANDOM.STRING ('U', 64),
DBMS_RANDOM.STRING ('U', 64),
DBMS_RANDOM.STRING ('U', 64)
FROM tdata a, tdata b
WHERE ROWNUM <= 100000;

When observing v$transaction I noticed there are quite a few I/O activity going on in my undo segment,

select log_io, phy_io, cr_get, cr_change, used_urec from v$transaction

LOG_IO PHY_IO CR_GET CR_CHANGE USED_UREC
---------- ---------- ---------- ---------- ----------
102826 50002 43863 6252 1

and 6MB redo was generated.

My question is why this insert operation is performing I/O operation at UNDO all the time?

I am the only user in the database, no other transaction were running.

Thanks

consistent reads - it did not really GENERATE undo (1 used_urec), it read undo.

All queries (reads) may well need undo to respond.  It is one of the primary "features" of Oracle.

Now, that aside - I don't see "6mb" of redo?  I don't know how  you measured that (so, maybe you didn't measure it correctly and it was not 6mb for the insert append...)

log_io is LOGICAL IO, not read writes.

ops$tkyte%ORA9IR2> create table t1
  2  (
  3     a number,
  4     b varchar2(64),
  5     c varchar2(64),
  6     d varchar2(64),
  7     e varchar2(64)
  8  )
  9  tablespace users
 10  pctused 1
 11  pctfree 90
 12  nologging;

Table created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> set autotrace traceonly statistics
ops$tkyte%ORA9IR2> insert /*+ append */ into t1
  2     WITH tdata AS
  3          (SELECT ROWNUM ID
  4             FROM all_objects
  5            WHERE ROWNUM <= 350)
  6     SELECT ROWNUM,
  7            DBMS_RANDOM.STRING ('U', 64),
  8            DBMS_RANDOM.STRING ('U', 64),
  9            DBMS_RANDOM.STRING ('U', 64),
 10            DBMS_RANDOM.STRING ('U', 64)
 11       FROM tdata a, tdata b
 12      WHERE ROWNUM <= 100000;

100000 rows created.


Statistics
----------------------------------------------------------
       5990  recursive calls
       3955  db block gets
       4416  consistent gets
        270  physical reads
     470992  redo size
        764  bytes sent via SQL*Net to client
       1141  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
         31  sorts (memory)
          0  sorts (disk)
     100000  rows processed

ops$tkyte%ORA9IR2> set autotrace off
ops$tkyte%ORA9IR2> select log_io, phy_io, cr_get, cr_change, used_urec from v$transaction;

    LOG_IO     PHY_IO     CR_GET  CR_CHANGE  USED_UREC
---------- ---------- ---------- ---------- ----------
      7122      50252       4231        244          1


for example shows well under 0.5 mb of redo generated on my particular system.  Much of that likely comes from the temporary table (which can and will generate undo and redo) that could be implicitly created under the covers (sql_trace will show you) 

Hi

This is the test case which generates 6mb redo.

I still dont understand why undo is accessed so many times. The only operation which needs to access undo is the INSERT operation. I sql_traced the insert and see nothing suspicious related to undo! What is being read from undo...?

pepe@pnb-esmad01O>create table t1
2 (
3 a number,
4 b varchar2(64),
5 c varchar2(64),
6 d varchar2(64),
7 e varchar2(64)
8 )
9 tablespace users
10 pctused 1
11 pctfree 90
12 nologging;

Table created.

pepe@pnb-esmad01O>set autotrace trace stat
pepe@pnb-esmad01O>insert /*+ append */ into t1
2 WITH tdata AS
3 (SELECT ROWNUM ID
4 FROM all_objects
5 WHERE ROWNUM <= 350)
6 SELECT ROWNUM,
7 DBMS_RANDOM.STRING ('U', 64),
DBMS_RANDOM.STRING ('U', 64),
8 9 DBMS_RANDOM.STRING ('U', 64),
10 DBMS_RANDOM.STRING ('U', 64)
11 FROM tdata a, tdata b
12 WHERE ROWNUM <= 100000;

100000 rows created.


Statistics
----------------------------------------------------------
131335 recursive calls
46836 db block gets
43872 consistent gets
1 physical reads
6116168 redo size
765 bytes sent via SQL*Net to client
1133 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
100000 rows processed

pepe@pnb-esmad01O>set autotrace off
pepe@pnb-esmad01O>select log_io, phy_io, cr_get, cr_change, used_urec from v$transaction;

LOG_IO PHY_IO CR_GET CR_CHANGE USED_UREC
---------- ---------- ---------- ---------- ----------
102815 50002 43863 6251 1

1 row selected.

Hi again

I do agree with you that there are operation reading undo all the time, however it is a test database where I am the only user and I just recreated the table, there is a transaction, mine. Why my own transaction is reading the UNDO... to buld consistent view of what data (my table is empty and there is no recursive SQL modifying data dictionary)?

sql trace didnt show anything

Cheers

Hi Tom. I am on 10gR2 RAC environment here, in noarchive log mode. Can you explain me why is redo generated the other way around when I truncate the table ?

For example

SQL> create table t (
  2     x       int
  3  )
  4  /

Table created.

SQL> create index t_idx on t ( x );

Index created.

SQL>

insert.sql is
insert /*+ append */ into t
select rownum
  from all_objects
 where rownum <= 1000
/

SQL>
SQL> truncate table t;

Table truncated.

SQL> set autotrace traceonly statistics
SQL>
SQL> alter index t_idx nologging;

Index altered.

SQL> @insert

1000 rows created.


Statistics
----------------------------------------------------------
        186  recursive calls
         62  db block gets
       2783  consistent gets
          8  physical reads
      48412  redo size
        820  bytes sent via SQL*Net to client
        781  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          9  sorts (memory)
          0  sorts (disk)
       1000  rows processed

SQL> commit;

Commit complete.

SQL> alter index t_idx logging;

Index altered.

SQL> @insert

1000 rows created.


Statistics
----------------------------------------------------------
        229  recursive calls
        127  db block gets
       2802  consistent gets
          0  physical reads
      88560  redo size
        816  bytes sent via SQL*Net to client
        781  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          9  sorts (memory)
          0  sorts (disk)
       1000  rows processed

SQL> commit;

Commit complete.

SQL> truncate table t;

Table truncated.

SQL> @insert

1000 rows created.


Statistics
----------------------------------------------------------
          8  recursive calls
         63  db block gets
       2758  consistent gets
          8  physical reads
      48404  redo size
        820  bytes sent via SQL*Net to client
        781  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
       1000  rows processed

SQL> commit;

Commit complete.

SQL> alter index t_idx nologging;

Index altered.

SQL> @insert

1000 rows created.


Statistics
----------------------------------------------------------
        229  recursive calls
        125  db block gets
       2802  consistent gets
          0  physical reads
      88560  redo size
        819  bytes sent via SQL*Net to client
        781  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          9  sorts (memory)
          0  sorts (disk)
       1000  rows processed

As you can see, the first operation after I truncate the table generates litle redo, but sub-sequent operation generates a lot, whether the index on the table is marked logged or not. Can you explain why is this happening ? Thanks! 

Hi Tom,

I was trying to use your procedure to simulate parallel insert using many concurrent sessions thru' dbms_job's to remote site. With in the code I am calling the procedure serial_to_parallel listed below:

I am using the pull approach rather than push approach.

Questions:

1. Even if parallel DML is enabled the query doesn't open parallel slaves at the remote site, however it does open at local site.
2. It opens the parallel slaves at both ends if we don't specify the WHERE clause. Is there any limitation surrounding this.
3. We were able to transfer 66 million rows with global index maintenance in 54 mins but still trying to use the parallel inserts using more db links, so that the time to transfer can be reduced.

Create or replace procedure serial_to_parallel(l_JOB NUMBER)
Is
l_rec job_parms%rowtype;
l_error_message VARCHAR2(4000);
Begin
Select * into l_rec from job_parms Where job = l_job;
Begin
Execute immediate 'Alter session enable parallel dml';
Execute immediate 'Alter session force parallel query';
Insert /*+ Parallel(X 4) */ into Imp_Clk_Invrep_Denorm X Select /*+ PArallel(Y 4) */ * from Imp_Clk_V@admw.amazon Y
where rowid between chartorowid(l_rec.lo_rid) and chartorowid(l_rec.hi_rid);
Delete from job_parms Where JOB = l_job;
commit;
EXCEPTION
When NO_DATA_FOUND then
null;
dbms_output.put_line('No Data Found');
When Others then
l_error_message := SQLERRM;
dbms_output.put_line(l_error_message);
End;
End;
/

Appreciate your reply.

Thanks

You probably meant "parallel dml != db-link" ... the remote select can be (and usually is) parallelized.

It's the parallel insert that never happens.

Hi Tom,

I have a question about space usage and INSERT APPEND. I have three tables with the same structure created in a tablespace with ASSM system extent allocation and compression enabled. When I copy SOURCE_TABLE into DESTINATION_TABLE with PDML disabled, DESTINATION_TABLE uses 90MB more space than SOURCE_TABLE. When I copy SOURCE_TABLE into DESTINATION_TABLE_PARALLEL, DESTINATION_TABLE_PARALLEL uses more than 200MB of space than SOURCE_TABLE.

Why are there such differences in space usage?

Also, why are there such differences in the numbers of Full Blocks from show_space procedure?

CREATE TABLE source_table
(
c1 VARCHAR2(20 BYTE) NOT NULL,
c2 VARCHAR2(10 BYTE),
c3 VARCHAR2(5 BYTE) NOT NULL,
c4 NUMBER,
c5 NUMBER,
c6 NUMBER,
c7 NUMBER,
c8 VARCHAR2(5 BYTE),
c9 NUMBER,
c10 NUMBER,
c11 NUMBER,
c12 NUMBER,
c13 VARCHAR2(5 BYTE) NOT NULL,
c14 VARCHAR2(5 BYTE),
c15 NUMBER,
c16 NUMBER,
c17 VARCHAR2(10 BYTE),
c18 DATE,
c19 DATE,
c20 NUMBER,
c21 NUMBER,
c22 NUMBER,
c23 NUMBER,
c24 NUMBER,
c25 DATE           NOT NULL,
c26 DATE,
c27 DATE,
c28 NUMBER,
c29 NUMBER,
c30 NUMBER,
c31 NUMBER,
c32 NUMBER,
c33 NUMBER,
c34 NUMBER,
c35 NUMBER,
c36 NUMBER,
c37 VARCHAR2(5 BYTE),
c38 VARCHAR2(5 BYTE),
c39 VARCHAR2(5 BYTE),
c40 VARCHAR2(5 BYTE),
c41 VARCHAR2(5 BYTE),
c42 VARCHAR2(5 BYTE),
c43 NUMBER,
c44 NUMBER,
c45 NUMBER         NOT NULL,
c46 NUMBER(9,2),
c47 NUMBER(9,2),
c48 NUMBER(9,2),
c49 NUMBER(9,2),
c50 NUMBER(9,2),
c51 NUMBER(9,2),
c52 NUMBER(9,2),
c53 NUMBER(9,2),
c54 NUMBER(9,2),
c55 NUMBER(9,2),
c56 NUMBER(9,2),
c57 NUMBER(9,2),
c58 NUMBER(9,2),
c59 NUMBER(9,2),
c60 NUMBER(9,2),
c61 NUMBER(9,2),
c62 NUMBER(9,2),
c63 NUMBER(9,2),
c64 NUMBER(9,2),
c65 NUMBER(9,2),
c66 NUMBER(9,2),
c67 DATE,
c68 VARCHAR2(50 BYTE),
c69 CHAR(1 BYTE),
c70 NUMBER
)
parallel
nologging
tablespace fact_data
PARTITION BY RANGE (c25) 
(  
PARTITION st_P2006_1 VALUES LESS THAN (TO_DATE('20060201', 'YYYYMMDD')),  
PARTITION st_P2006_2 VALUES LESS THAN (TO_DATE('20060301', 'YYYYMMDD')),  
PARTITION st_P2006_3 VALUES LESS THAN (TO_DATE('20060401', 'YYYYMMDD')),  
PARTITION st_P2006_4 VALUES LESS THAN (TO_DATE('20060501', 'YYYYMMDD')),  
PARTITION st_P2006_5 VALUES LESS THAN (TO_DATE('20060601', 'YYYYMMDD')),  
PARTITION st_P2006_6 VALUES LESS THAN (TO_DATE('20060701', 'YYYYMMDD')),  
PARTITION st_P2006_7 VALUES LESS THAN (TO_DATE('20060801', 'YYYYMMDD')),  
PARTITION st_P2006_8 VALUES LESS THAN (TO_DATE('20060901', 'YYYYMMDD')),  
PARTITION st_P2006_9 VALUES LESS THAN (TO_DATE('20061001', 'YYYYMMDD')),  
PARTITION st_P2006_10 VALUES LESS THAN (TO_DATE('20061101', 'YYYYMMDD')),  
PARTITION st_P2006_11 VALUES LESS THAN (TO_DATE('20061201', 'YYYYMMDD')),  
PARTITION st_P2006_12 VALUES LESS THAN (TO_DATE('20070101', 'YYYYMMDD')),  
PARTITION st_d VALUES LESS THAN (MAXVALUE)
);


CREATE TABLE destination_table
(
c1 VARCHAR2(20 BYTE) NOT NULL,
c2 VARCHAR2(10 BYTE),
c3 VARCHAR2(5 BYTE) NOT NULL,
c4 NUMBER,
c5 NUMBER,
c6 NUMBER,
c7 NUMBER,
c8 VARCHAR2(5 BYTE),
c9 NUMBER,
c10 NUMBER,
c11 NUMBER,
c12 NUMBER,
c13 VARCHAR2(5 BYTE) NOT NULL,
c14 VARCHAR2(5 BYTE),
c15 NUMBER,
c16 NUMBER,
c17 VARCHAR2(10 BYTE),
c18 DATE,
c19 DATE,
c20 NUMBER,
c21 NUMBER,
c22 NUMBER,
c23 NUMBER,
c24 NUMBER,
c25 DATE           NOT NULL,
c26 DATE,
c27 DATE,
c28 NUMBER,
c29 NUMBER,
c30 NUMBER,
c31 NUMBER,
c32 NUMBER,
c33 NUMBER,
c34 NUMBER,
c35 NUMBER,
c36 NUMBER,
c37 VARCHAR2(5 BYTE),
c38 VARCHAR2(5 BYTE),
c39 VARCHAR2(5 BYTE),
c40 VARCHAR2(5 BYTE),
c41 VARCHAR2(5 BYTE),
c42 VARCHAR2(5 BYTE),
c43 NUMBER,
c44 NUMBER,
c45 NUMBER         NOT NULL,
c46 NUMBER(9,2),
c47 NUMBER(9,2),
c48 NUMBER(9,2),
c49 NUMBER(9,2),
c50 NUMBER(9,2),
c51 NUMBER(9,2),
c52 NUMBER(9,2),
c53 NUMBER(9,2),
c54 NUMBER(9,2),
c55 NUMBER(9,2),
c56 NUMBER(9,2),
c57 NUMBER(9,2),
c58 NUMBER(9,2),
c59 NUMBER(9,2),
c60 NUMBER(9,2),
c61 NUMBER(9,2),
c62 NUMBER(9,2),
c63 NUMBER(9,2),
c64 NUMBER(9,2),
c65 NUMBER(9,2),
c66 NUMBER(9,2),
c67 DATE,
c68 VARCHAR2(50 BYTE),
c69 CHAR(1 BYTE),
c70 NUMBER
)
noparallel
nologging
tablespace fact_data
PARTITION BY RANGE (c25) 
(  
PARTITION dt_P2006_1 VALUES LESS THAN (TO_DATE('20060201', 'YYYYMMDD')),  
PARTITION dt_P2006_2 VALUES LESS THAN (TO_DATE('20060301', 'YYYYMMDD')),  
PARTITION dt_P2006_3 VALUES LESS THAN (TO_DATE('20060401', 'YYYYMMDD')),  
PARTITION dt_P2006_4 VALUES LESS THAN (TO_DATE('20060501', 'YYYYMMDD')),  
PARTITION dt_P2006_5 VALUES LESS THAN (TO_DATE('20060601', 'YYYYMMDD')),  
PARTITION dt_P2006_6 VALUES LESS THAN (TO_DATE('20060701', 'YYYYMMDD')),  
PARTITION dt_P2006_7 VALUES LESS THAN (TO_DATE('20060801', 'YYYYMMDD')),  
PARTITION dt_P2006_8 VALUES LESS THAN (TO_DATE('20060901', 'YYYYMMDD')),  
PARTITION dt_P2006_9 VALUES LESS THAN (TO_DATE('20061001', 'YYYYMMDD')),  
PARTITION dt_P2006_10 VALUES LESS THAN (TO_DATE('20061101', 'YYYYMMDD')),  
PARTITION dt_P2006_11 VALUES LESS THAN (TO_DATE('20061201', 'YYYYMMDD')),  
PARTITION dt_P2006_12 VALUES LESS THAN (TO_DATE('20070101', 'YYYYMMDD')),  
PARTITION dt_d VALUES LESS THAN (MAXVALUE)
);


CREATE TABLE destination_table_parallel
(
c1 VARCHAR2(20 BYTE) NOT NULL,
c2 VARCHAR2(10 BYTE),
c3 VARCHAR2(5 BYTE) NOT NULL,
c4 NUMBER,
c5 NUMBER,
c6 NUMBER,
c7 NUMBER,
c8 VARCHAR2(5 BYTE),
c9 NUMBER,
c10 NUMBER,
c11 NUMBER,
c12 NUMBER,
c13 VARCHAR2(5 BYTE) NOT NULL,
c14 VARCHAR2(5 BYTE),
c15 NUMBER,
c16 NUMBER,
c17 VARCHAR2(10 BYTE),
c18 DATE,
c19 DATE,
c20 NUMBER,
c21 NUMBER,
c22 NUMBER,
c23 NUMBER,
c24 NUMBER,
c25 DATE           NOT NULL,
c26 DATE,
c27 DATE,
c28 NUMBER,
c29 NUMBER,
c30 NUMBER,
c31 NUMBER,
c32 NUMBER,
c33 NUMBER,
c34 NUMBER,
c35 NUMBER,
c36 NUMBER,
c37 VARCHAR2(5 BYTE),
c38 VARCHAR2(5 BYTE),
c39 VARCHAR2(5 BYTE),
c40 VARCHAR2(5 BYTE),
c41 VARCHAR2(5 BYTE),
c42 VARCHAR2(5 BYTE),
c43 NUMBER,
c44 NUMBER,
c45 NUMBER         NOT NULL,
c46 NUMBER(9,2),
c47 NUMBER(9,2),
c48 NUMBER(9,2),
c49 NUMBER(9,2),
c50 NUMBER(9,2),
c51 NUMBER(9,2),
c52 NUMBER(9,2),
c53 NUMBER(9,2),
c54 NUMBER(9,2),
c55 NUMBER(9,2),
c56 NUMBER(9,2),
c57 NUMBER(9,2),
c58 NUMBER(9,2),
c59 NUMBER(9,2),
c60 NUMBER(9,2),
c61 NUMBER(9,2),
c62 NUMBER(9,2),
c63 NUMBER(9,2),
c64 NUMBER(9,2),
c65 NUMBER(9,2),
c66 NUMBER(9,2),
c67 DATE,
c68 VARCHAR2(50 BYTE),
c69 CHAR(1 BYTE),
c70 NUMBER
)
parallel
nologging
tablespace fact_data
PARTITION BY RANGE (c25) 
(  
PARTITION dt_P2006_1 VALUES LESS THAN (TO_DATE('20060201', 'YYYYMMDD')),  
PARTITION dt_P2006_2 VALUES LESS THAN (TO_DATE('20060301', 'YYYYMMDD')),  
PARTITION dt_P2006_3 VALUES LESS THAN (TO_DATE('20060401', 'YYYYMMDD')),  
PARTITION dt_P2006_4 VALUES LESS THAN (TO_DATE('20060501', 'YYYYMMDD')),  
PARTITION dt_P2006_5 VALUES LESS THAN (TO_DATE('20060601', 'YYYYMMDD')),  
PARTITION dt_P2006_6 VALUES LESS THAN (TO_DATE('20060701', 'YYYYMMDD')),  
PARTITION dt_P2006_7 VALUES LESS THAN (TO_DATE('20060801', 'YYYYMMDD')),  
PARTITION dt_P2006_8 VALUES LESS THAN (TO_DATE('20060901', 'YYYYMMDD')),  
PARTITION dt_P2006_9 VALUES LESS THAN (TO_DATE('20061001', 'YYYYMMDD')),  
PARTITION dt_P2006_10 VALUES LESS THAN (TO_DATE('20061101', 'YYYYMMDD')),  
PARTITION dt_P2006_11 VALUES LESS THAN (TO_DATE('20061201', 'YYYYMMDD')),  
PARTITION dt_P2006_12 VALUES LESS THAN (TO_DATE('20070101', 'YYYYMMDD')),  
PARTITION dt_d VALUES LESS THAN (MAXVALUE)
);


SQL> alter session enable parallel dml;

Session altered.

Elapsed: 00:00:00.00
SQL>
SQL> truncate table destination_table_parallel;

Table truncated.

Elapsed: 00:00:01.00
SQL>
SQL> insert /*+ append */ into destination_table_parallel
  2  select
  3  *
  4  from source_table s;

5000000 rows created.

Elapsed: 00:02:30.84

Execution Plan
----------------------------------------------------------
ERROR:
ORA-12838: cannot read/modify an object after modifying it in parallel


SP2-0612: Error generating AUTOTRACE EXPLAIN report

Statistics
----------------------------------------------------------
         20  recursive calls
        112  db block gets
         75  consistent gets
         13  physical reads
       2352  redo size
        511  bytes sent via SQL*Net to client
        370  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          7  sorts (memory)
          0  sorts (disk)
    5000000  rows processed

SQL>
SQL> commit;

Commit complete.

Elapsed: 00:00:00.07
SQL> alter session disable parallel dml;

Session altered.

Elapsed: 00:00:00.01
SQL>
SQL> truncate table destination_table;

Table truncated.

Elapsed: 00:00:00.85
SQL>
SQL> insert /*+ append */ into destination_table
  2  select
  3  *
  4  from source_table s;

5000000 rows created.

Elapsed: 00:17:17.07

Execution Plan
----------------------------------------------------------
ERROR:
ORA-12838: cannot read/modify an object after modifying it in parallel


SP2-0612: Error generating AUTOTRACE EXPLAIN report

Statistics
----------------------------------------------------------
      53728  recursive calls
      32851  db block gets
     174159  consistent gets
     103252  physical reads
    1600680  redo size
        515  bytes sent via SQL*Net to client
        361  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          7  sorts (memory)
          0  sorts (disk)
    5000000  rows processed

SQL> commit;

Commit complete.

Elapsed: 00:00:00.04
SQL>

SQL> ed
Wrote file afiedt.buf

  1  select
  2  segment_name,
  3  segment_type,
  4  sum(bytes) bytes,
  5  sum(bytes)/1024/1024 mb,
  6  count(*) extents
  7  from dba_extents
  8  where segment_name in
  9  ('SOURCE_TABLE', 'DESTINATION_TABLE', 'DESTINATION_TABLE_PARALLEL')
 10  and owner='RDMDATA'
 11* group by segment_name,segment_type
SQL> /

SEGMENT_NAME                   SEGMENT_TYPE            BYTES         MB    EXTENTS
------------------------------ ------------------ ---------- ---------- ----------
SOURCE_TABLE                   TABLE PARTITION     886505472   845.4375        539
DESTINATION_TABLE              TABLE PARTITION     981925888   936.4375        550
DESTINATION_TABLE_PARALLEL     TABLE PARTITION    1116143616  1064.4375        566

Elapsed: 00:00:00.39
SQL>

SQL> exec show_space('DESTINATION_TABLE_PARALLEL',p_type=>'TABLE PARTITION',P_PARTITION=>'DT_P2006_8');
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................          24,604
Total Blocks............................          25,600
Total Bytes.............................     209,715,200
Total MBytes............................             200
Unused Blocks...........................             793
Unused Bytes............................       6,496,256
Last Used Ext FileId....................              72
Last Used Ext BlockId...................         509,832
Last Used Block.........................             231

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.10
SQL> exec show_space('SOURCE_TABLE',p_type=>'TABLE PARTITION',P_PARTITION=>'ST_P2006_8');
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................          20,202
Total Blocks............................          21,120
Total Bytes.............................     173,015,040
Total MBytes............................             165
Unused Blocks...........................             732
Unused Bytes............................       5,996,544
Last Used Ext FileId....................              58
Last Used Ext BlockId...................         399,880
Last Used Block.........................             292

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.10
SQL> exec show_space('DESTINATION_TABLE',p_type=>'TABLE PARTITION',P_PARTITION=>'DT_P2006_8');
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................          22,422
Total Blocks............................          23,552
Total Bytes.............................     192,937,984
Total MBytes............................             184
Unused Blocks...........................             935
Unused Bytes............................       7,659,520
Last Used Ext FileId....................              63
Last Used Ext BlockId...................         555,784
Last Used Block.........................              89

PL/SQL procedure successfully completed.

Thanks in advance for your help.

I had read this excerpt from your book before submitting this question. However, I am not sure how it applies to my question. Specifically, DESTINATION_TABLE is being populated in serial NOT in parallel (i.e. PDML is disabled). How can an example partition in SOURCE_TABLE have 20202 full blocks with no partially free blocks and the same partition in DESTINATION_TABLE have 22422 full blocks with no partially free blocks? I have the same question about the table that was populated in parallel DESTINATION_TABLE_PARALLEL -- how can it have even more number of full blocks (24604) with no free/partially free blocks?

The only possible explanation I can think of is that the compression ratio might be lower in destination tables. Even though the order of the records without the ORDER BY is not guaranteed, we are doing a full table scan of SOURCE_TABLE so shouldn't Oracle stream the source records into destination in roughly the same order as they appear in SOURCE_TABLE blocks?

Thanks

Hi Tom,

I am sorry for the confusion about parallel. My confusion was about the output of show_space procedure for an example partition in all three tables (which have exactly the same data). I didn't want waste space by sending the output for all 39 partitions (13 per table).

All three tables have pctfree of 0.

Could you please comment on the show_space results above? Also, could you please explain why are the "Full Blocks" numbers are so different while there are no free blocks?

What additional information do I need to provide for context?

Thanks for your help.

:) I knew we were looking at two different things. I checked my original question and it was about 16k in size... I can see my whole question in the thread just fine. Anyway, let me paste the show_space output again.

These tables are in a tablespace with compression enabled so pctfree gets set to 0 implicitly (if I am not mistaken).

SQL> exec show_space('DESTINATION_TABLE_PARALLEL',p_type=>'TABLE 
PARTITION',P_PARTITION=>'DT_P2006_8');
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................          24,604
Total Blocks............................          25,600
Total Bytes.............................     209,715,200
Total MBytes............................             200
Unused Blocks...........................             793
Unused Bytes............................       6,496,256
Last Used Ext FileId....................              72
Last Used Ext BlockId...................         509,832
Last Used Block.........................             231

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.10
SQL> exec show_space('SOURCE_TABLE',p_type=>'TABLE PARTITION',P_PARTITION=>'ST_P2006_8');
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................          20,202
Total Blocks............................          21,120
Total Bytes.............................     173,015,040
Total MBytes............................             165
Unused Blocks...........................             732
Unused Bytes............................       5,996,544
Last Used Ext FileId....................              58
Last Used Ext BlockId...................         399,880
Last Used Block.........................             292

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.10
SQL> exec show_space('DESTINATION_TABLE',p_type=>'TABLE PARTITION',P_PARTITION=>'DT_P2006_8');
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................          22,422
Total Blocks............................          23,552
Total Bytes.............................     192,937,984
Total MBytes............................             184
Unused Blocks...........................             935
Unused Bytes............................       7,659,520
Last Used Ext FileId....................              63
Last Used Ext BlockId...................         555,784
Last Used Block.........................              89

PL/SQL procedure successfully completed.

Dear Tom!
Hope you fine.
I want to know if a table have a fk ¿when we do insert /*+ append */ into t select ....,does oracle ignore the hint and use traditional insert or not?if not ,please explain how does oracle check the referential integrity.
Regards!
Alan

Hello,

I have some tables partitioned. Every day I execute a procedure with an INSERT /*+APPEND*/. That makes a big partition at the end of every month, so I have to rebuilt it.
I use DELETE to eliminate some rows, because I can't use TRUNCATE.
My question is... Can I use an INSERT without APPEND hint to aboid all the wasted space?

(Sorry for my english, I'm spanish)

Hi Tom,

If the FORCE LOGGING is enforced at the database level, is there any benefit in using APPEND hint? Am I right in saying that it will generate REDO but not UNDO for the table? I want to insert milliions of rows in several tables by extracting data over a database link. The strategy that I have adopted is:

(1) Disable constraints
(2) Mark indexes unusable
(3) Skip unusable indexes
(4) Direct insert using /*+ APPEND */ hint.
(4) Unset skip unusable indexes
(5) Rebuild indexes
(6) Enable constraints

Can the above approach be adopted in case of FORCE LOGGING? Also, can the query be parallelised over a database link? Please let me know.

Thanks

Thanks Tom for your feedback. I am performing a full load of data from source to target that will be used on an as and when required basis. So, your suggestion is not to disable the constraints before the load. Are you also suggesting to leave the indexes in an usable state and not mark them unusable before the load? Please let me know.

Hi Tom,

I am inserting records using 'append' HINTS from view which are ctreated from multiple table on anohter schema. i have created synonyms upon this view and calling this view.
i am facing ora-error.after removing hint its running successfully.


Following error was found in job_log table of event schema:
Error for insert dt range (20090312131159,20090312131159):ORA-12840: cannot access a remote table after parallel/insert direct load txn



Same schenirio running succsfully in another insert statment.
insert /*+ append */ into rp_alert_cdr
select
lv_al_number,
c.*
from
cdr c --DB_LINK
where
c.prof_num = lv_ID_spoofing(i).prof_num and
prof_num is not null
and MO_MT_FLAG='1'
and pp_code in ('POSTPAID','PREPAID')
and disc_dt >=lv_start_time
and disc_dt <lv_end_time;

Tom,
A few reviews above, you said :

if you use insert /*+ append */ (and it works - the append is allowed)

Under what condition(s) is APPEND allowed/not allowed?

Thanks...

Tom,

This is not a question. This is just something I discovered today and would like to know if you are aware about

Suppose you have

1) two databases DB1 and DB2
2) a dblink db1_to_db2
3) and you have table named emp in both DB1 and DB2
What I discovered is that when you issue the following select from DB1
select * from emp@db1_db2
then you are selecting from DB1 and not from DB2.

Are you aware about this

ops$tkyte%ORA10GR2> select * from t;

MSG
----------
i am local

ops$tkyte%ORA10GR2> select * from t@db1_to_db2;

DATA
-----------
i am remote

Hi Tom,

Can you please tell me what are benefits and drawback of using append hint while inserting data on a global temporary table.
Here I don't see benefit of not doing undo. Correct me if I'm wrong.

I have one serious requirement that how to design a table, so that it can have bulk insert,update,delete on real time basis.
This is one of my production system requirement..

How to achieve performance tuning in this case..

Pl Help..