Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: November 12, 2001 - 4:30 pm UTC

Answered by: Tom Kyte - Last updated: March 15, 2010 - 9:54 am UTC

Category: Database - Version: 817

Viewed 10K+ times! This question is

You Asked

Hi Tom
I have read about the append hint that when its used with the Insert stmt it takes more space than a usual Insert would.
In our case we have a batch process that first truncates tables and then does Inserts with the Append hint.
In such a case would it also take more space than ususal?
From my understanding a truncate resets the HWM , so does the insert reuse all the space from the baseline or still waste space sue to its very nature?

THanks

and we said...

It only takes more space in that is loads above the high water mark, it will NOT use space that is on the freelist for that table.

If you truncate -- nothing will be on the freelist, everything is above the high water mark and the storage will be the same. Using append will not "waste" space in this case.



and you rated our response

  (127 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

Suresh

November 12, 2001 - 10:41 pm UTC

Reviewer: A reader

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

Tom Kyte

Followup  

November 13, 2001 - 8:34 am UTC

that won't waste or NOT waste space -- it just doesn't release the currently allocated space (so in fact, it could "waste space" if the table had 1gig allocated to it and you load it up with 10meg of data -- it'll have almost a gig of alloocated but not used space -- which many people would consider "wasted")

It really doesn't have any affect on the space usage of the direct path insert.

February 09, 2002 - 10:21 pm UTC

Reviewer: Make from Alpharetta, GA

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.

Tom Kyte

Followup  

February 10, 2002 - 1:29 pm UTC

my point is -- it may or may not be "wasted" space.

Suppose you have 10meg on the freelists. You do a direct path load, it'll not touch that data on the freelist instead it will go above the HWM. Now, is that 10meg "wasted"? Not if you also insert into the table as part of your day to day operations. Not if you update the data causing it to grow (and hence migrate or chain rows). It is not "wasted space" in general.

If all you do is direct path load -- all of the time -- you will not have any space on the freelists anyway (nothing to "waste"). In order to get stuff on the freelist you have to be doing conventional inserts/updates and deletes. If you are doing that -- then I would say the space is definitely not wasted, you'll use it later with conventional inserts/updates.

thanks for clearfying

February 10, 2002 - 9:14 pm UTC

Reviewer: Make


November 18, 2002 - 11:15 am UTC

Reviewer: DJ from Newton, MA

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

Will this waste space?

Tom Kyte

Followup  

November 18, 2002 - 1:29 pm UTC

waste is such a RELATIVE term.

The space freed by a DELETE (there is but one kind of DELETE) will never be reused by a direct path insert. So, if you feel the deleted spaced is wasted -- it will be wasted.

Will APPEND cmd extend the datafiles

June 06, 2003 - 7:59 pm UTC

Reviewer: Neeraj Nagpal from Los Angeles, CA

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

Tom Kyte

Followup  

June 06, 2003 - 8:26 pm UTC

if the available space is insufficient to satisfy the next extent, sure.

remember a datafile in a DMT (dictionary managed tablespace) might have 500meg free but be unable to satisfy a request for a 1meg extent (reason 12432 to use LMT's) due to fragmentation.

if there was sufficient, logically contigous space available, the append would use it. else, it'll extend the datafile

HWM after conventional inserts and direct path inserts

July 08, 2003 - 12:38 am UTC

Reviewer: Chuan Sat from VA USA

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

Tom Kyte

Followup  

July 08, 2003 - 7:16 am UTC

if you loaded 300m of data total, it'll be 300m

Does MERGE with APPEND use the HWM or something else

September 16, 2003 - 4:07 pm UTC

Reviewer: Arnoud Otte from Portland, OR, USA

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.

Tom Kyte

Followup  

September 16, 2003 - 6:21 pm UTC

looks like an interesting new question...

the problem with long involved things that I have to look at in a review section is -- well, i don't have any method to "back burner it".

meaning, I see reviews chronologically. I either answer them right there or not.

this will take more then 30/60 seconds to research and I'm hungry right now :)

hence, i'll just look at the rest of the reviews and go eat......

when I'm taking new questions -- feel free to put this that -- too big for right here.

Unable to Extend tablespace SORT

September 28, 2003 - 9:06 pm UTC

Reviewer: Sridhar Reddy from USA

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 Kyte

Followup  

September 29, 2003 - 7:17 am UTC



and what step exactly is throwing this error.

if it is:

insert /*+ append */ into table t select .....

then the SELECT is the culprit and it may well have nothing to do with your indexes at all (unless of course the select itself is selecting from t and due to the lack of indexes.....)


but this is sort of like me asking "hey, my car won't start -- whats wrong". Insufficient data to say anything.

What is the thought process behind not marking the indexes unusable? Why do they feel that is the issue?

Unable To Extend tablespace SORT

September 29, 2003 - 9:43 am UTC

Reviewer: Sridhar Reddy from USA

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 Kyte

Followup  

September 29, 2003 - 12:41 pm UTC

FULL SCANS = GOOD
INDEXES = BAD

that is true as often as the converse.

sizing system to do workload required = good

is a correct statement as well.

Unable to Extend Tablespace SORT

September 29, 2003 - 1:40 pm UTC

Reviewer: Sridhar from USA

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 Kyte

Followup  

September 29, 2003 - 2:49 pm UTC

nope. i meant to say

FULL SCANS = GOOD
INDEXES = BAD

using an index is not always good.
using an index is not always bad
full scanning is not always good.
full scanning is not always bad.


an index full scan just means it is reading the index in order -- every row (so it probably took out a sort). that could be good -- but I cannot really comment -- no data to look at here....


is the query running faster and using less temp? then you have success :)

SYS_OP_MAP_NONNULL

November 21, 2003 - 2:15 pm UTC

Reviewer: M S Reddy from USA

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 Kyte

Followup  

November 21, 2003 - 5:25 pm UTC

just an internal "hint".

"ignore that man behind the curtain" sort of stuff.

partitioned table AND TRY TO INSERT 3Million records

November 25, 2003 - 4:43 pm UTC

Reviewer: Alex smith from UK

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.


Tom Kyte

Followup  

November 25, 2003 - 6:38 pm UTC

what do you mean "if i use a plsql table"

give us a sample of what you are doing (code sample) and if you have the tkprofs -- they would be very telling.

Bulk insert

November 26, 2003 - 4:39 pm UTC

Reviewer: Alex from UK

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

Tom Kyte

Followup  

November 27, 2003 - 10:16 am UTC

i would need a full blown example here -- or at least a much better description of what we are comparing to what.

we've gone from plsql table to proc

not sure the setup -- tab1 and tab2, same server, different server.

100,000 records in proc versus millions in the database?

i don't understand WHAT we are comparing.

Bulk Insert

November 26, 2003 - 4:41 pm UTC

Reviewer: Alex from UK

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



a question from my friend:

December 08, 2003 - 2:19 am UTC

Reviewer: Fenng

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!
 

Tom Kyte

Followup  

December 08, 2003 - 6:38 am UTC

use tkprof. autotrace (using explain plan) doesn't have to tell you "the truth"

sql_trace plus tkprof will tell you the true.

Hints

February 16, 2004 - 12:08 pm UTC

Reviewer: M S Reddy from USA

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

/*+BYPASS_UJVC*/ .

Thanks,
M S Reddy



Tom Kyte

Followup  

February 16, 2004 - 12:52 pm UTC

bypass updateable join view hint.

not for external use. internal only.

BYPASS_UJVC

February 26, 2004 - 4:14 pm UTC

Reviewer: M S Reddy from USA

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.

Tom Kyte

Followup  

February 26, 2004 - 4:25 pm UTC

if it did, it would be a bug.

however, the most likely solution from support would be "don't use that hint, it is not documented"

hence, my hesitance to suggest it.

Append hint

May 21, 2004 - 6:55 am UTC

Reviewer: Reader

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

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

Tom Kyte

Followup  

May 21, 2004 - 10:51 am UTC

sure

Append Hint

June 03, 2004 - 8:10 am UTC

Reviewer: Reader

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.

Tom Kyte

Followup  

June 03, 2004 - 8:30 am UTC

a few thousand records -- leave things intact, just load conventionally.

if you have bitmap indexes, they'll need some special care and feeding.
</code> http://www.dbazine.com/oracle.shtml/#jlewis <code>
read his articles on bitmaps if so.

Append hint

June 03, 2004 - 9:59 am UTC

Reviewer: Reader

Thanks

Fast after index for MTL_MATERIAL_TRANSACTIONS created

June 09, 2004 - 8:02 am UTC

Reviewer: A reader

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 Kyte

Followup  

June 09, 2004 - 9:26 am UTC

i deleted the two whopping huge duplicate traces you put up there.

I can only say you had lots of procedural look ups -- lots and lots of routines that did nothing more than:

select value into variable from table where condition;


they should be erased and the code for them put into the query itself. do not do things like:

for x in ( select * from table )
loop
lookup column1
lookup column2
......


just "join"

June 09, 2004 - 10:23 am UTC

Reviewer: Belinda Bin from NJ

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

Tom Kyte

Followup  

June 09, 2004 - 11:14 am UTC

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
 

Thanks.

June 09, 2004 - 9:52 pm UTC

Reviewer: Goh Seong Hin from Malaysia

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 Kyte

Followup  

June 10, 2004 - 7:38 am UTC

as many times as it needed to?

You are the best

June 10, 2004 - 12:31 pm UTC

Reviewer: Belinda from NJ

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.



Append parallel hint Over DB link

July 08, 2004 - 2:34 pm UTC

Reviewer: A reader

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 Kyte

Followup  

July 08, 2004 - 3:51 pm UTC

you need an alter to enable pdml

but here, what could happen is 4 local processes fed by 8 remote processes

[tkyte@tkyte-pc tkyte]$ ps -auxww | grep p0
ora10g 22383 1.3 0.6 247348 13680 ? S 15:48 0:01 ora_p000_ora10g
ora10g 22385 1.4 0.8 247348 17768 ? D 15:48 0:01 ora_p001_ora10g
ora10g 22387 1.4 0.9 247356 20324 ? S 15:48 0:01 ora_p002_ora10g
ora10g 22389 1.2 0.8 247364 16884 ? D 15:48 0:01 ora_p003_ora10g
ora9ir2 22391 0.7 0.4 423704 10132 ? S 15:48 0:00 ora_p000_ora9ir2
ora9ir2 22393 0.5 0.4 423696 8488 ? S 15:48 0:00 ora_p001_ora9ir2
ora9ir2 22395 0.6 0.4 423696 8496 ? S 15:48 0:00 ora_p002_ora9ir2
ora9ir2 22397 0.6 0.4 423692 8500 ? S 15:48 0:00 ora_p003_ora9ir2
ora9ir2 22399 0.5 0.4 423684 8484 ? S 15:48 0:00 ora_p004_ora9ir2
ora9ir2 22401 0.5 0.4 423696 8496 ? S 15:48 0:00 ora_p005_ora9ir2
ora9ir2 22403 0.6 0.4 423700 8476 ? S 15:48 0:00 ora_p006_ora9ir2
ora9ir2 22405 0.4 0.4 423688 8488 ? S 15:48 0:00 ora_p007_ora9ir2
tkyte 22425 0.0 0.0 3680 660 pts/2 S 15:50 0:00 grep p0


i did exactly that and got 4 local (10g) and 4 'remote' (my 9ir2 instance) going.

get rid of full table scan

July 08, 2004 - 5:12 pm UTC

Reviewer: A reader from NJ USA

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,


Tom Kyte

Followup  

July 08, 2004 - 8:56 pm UTC

look at the predicate:

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')

pc, p, ad, l cannot be driving tables -- because of the outer join -- but since you have:
ad.state=l.state_cd

you can remove the (+) after AD and L -- since if you did outer join to them, well, one or the other would be NULL and NULL is not equal (nor is it not equal) to ANYTHING.

for that fact, of P was outer joined to, P.name could NOT be in (nor not in) that set of values, get rid of (+) after P

Hmm, AND PC.X_FLAG_1 = 'Y' , well, if PC was outer joined to (sounding repitious yet ? :)


Hence, we have a case of over zealous outer joining removing/reducing the available plans -- for no gain.


there is not a single outer join in that query that "makes sense"

After PDML it got slower ??

July 09, 2004 - 5:21 am UTC

Reviewer: A reader

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 Kyte

Followup  

July 09, 2004 - 7:48 am UTC

the first one executed the query in parallel but the insert serially. the second did both -- since you enabled it.


in order for parallel operations, especially a parallel direct path (eg: lots of DIRECT IO -- no buffer cache) to be efficient -- you need lots of resources to abuse and use. did you have sufficient cpu, did you have sufficient io bandwidth -- or were all 4 writers writing to the same disk.

parallel is NOT fast=true, especially for things that already run in seconds. parallel can be used (can be used -- as a tool) to take many minute/hour/day long processes and decrease their run time -- given you have sufficient resources.



Very clear !

July 09, 2004 - 8:47 am UTC

Reviewer: A reader

Tom

Thanks. It's clear to me now.


High Water Mark

July 09, 2004 - 9:12 am UTC

Reviewer: A reader

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 Kyte

Followup  

July 09, 2004 - 9:19 am UTC

append will never reuse space from the freelists.

alter table T move;
alter index I rebuild;

will "reclaim" that space but -- it truly sounds like you want to look at partitioning so you can avoid having to delete at all -- just truncate/drop a partition with "old data"

Get Rid of full table scan

July 09, 2004 - 2:15 pm UTC

Reviewer: A reader from NJ USA

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.



query running slow

July 30, 2004 - 1:17 pm UTC

Reviewer: A reader from NJ, US

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,


Tom Kyte

Followup  

July 30, 2004 - 5:43 pm UTC

sorry -- optimizer = cbo since you hinted it.


26million rows isn't really "large", i commonly test with a 4,000,000 row table on my laptop.


anyway -- what is your hash area size (hint: set it larger for this query perhaps?)

hash joins are *brutally efficient*.
nested loops are deadly with tons of rows.

See:
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:6749454952894 <code>





Parallel Hint

September 01, 2004 - 5:07 pm UTC

Reviewer: A reader

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

Tom Kyte

Followup  

September 01, 2004 - 8:33 pm UTC

in parallel DML -- append is the default, each parallel writer grabs it's own new extent and writes to that.

so....

September 02, 2004 - 4:28 am UTC

Reviewer: A reader

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 Kyte

Followup  

September 02, 2004 - 8:56 am UTC

correct,

as documented:

</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96524/c21dlins.htm#10629 <code>




different explain plan in different env

September 09, 2004 - 4:57 pm UTC

Reviewer: A reader from USA

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 Kyte

Followup  

September 09, 2004 - 6:22 pm UTC

lets see the autotrace traceonly explain from both systems.

different explain plan in different env

September 09, 2004 - 9:50 pm UTC

Reviewer: A reader from USA

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 Kyte

Followup  

September 10, 2004 - 7:52 am UTC

show us the indexes on S_CONTACT

prompt
prompt
Prompt Indexes on &1
column index_name heading "Index|Name"
column Uniqueness heading "Is|Unique" format a6
column columns format a32 word_wrapped

select substr(a.index_name,1,30) index_name,
decode(a.uniqueness,'UNIQUE','Yes','No') uniqueness,
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 )) ||
max(decode( b.column_position, 3, ', '||
substr(b.column_name,1,30), NULL )) ||
max(decode( b.column_position, 4, ', '||
substr(b.column_name,1,30), NULL )) ||
max(decode( b.column_position, 5, ', '||
substr(b.column_name,1,30), NULL )) ||
max(decode( b.column_position, 6, ', '||
substr(b.column_name,1,30), NULL )) ||
max(decode( b.column_position, 7, ', '||
substr(b.column_name,1,30), NULL )) ||
max(decode( b.column_position, 8, ', '||
substr(b.column_name,1,30), NULL )) ||
max(decode( b.column_position, 9, ', '||
substr(b.column_name,1,30), NULL )) ||
max(decode( b.column_position, 10, ', '||
substr(b.column_name,1,30), NULL )) ||
max(decode( b.column_position, 11, ', '||
substr(b.column_name,1,30), NULL )) ||
max(decode( b.column_position, 12, ', '||
substr(b.column_name,1,30), NULL )) ||
max(decode( b.column_position, 13, ', '||
substr(b.column_name,1,30), NULL )) ||
max(decode( b.column_position, 14, ', '||
substr(b.column_name,1,30), NULL )) ||
max(decode( b.column_position, 15, ', '||
substr(b.column_name,1,30), NULL )) ||
max(decode( b.column_position, 16, ', '||
substr(b.column_name,1,30), NULL )) columns
from all_indexes a, all_ind_columns b
where a.owner = user
and a.table_name = upper('&1')
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
/


different explain plan in different env

September 10, 2004 - 9:25 am UTC

Reviewer: A reader from USA

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 Kyte

Followup  

September 10, 2004 - 9:39 am UTC

are all of the columns that are NOT NULL on one site NOT NULL on the other?

September 10, 2004 - 9:56 am UTC

Reviewer: A reader from USA

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 Kyte

Followup  

September 10, 2004 - 10:18 am UTC

thats what is doing it..

September 10, 2004 - 10:31 am UTC

Reviewer: A reader from USA

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 Kyte

Followup  

September 10, 2004 - 10:38 am UTC

no, it simply multiplies 32*16 to get the IO size (no different then having a 64*8 or 16*32).


nothing will make the optimizer prefer full tablescans in all cases.

September 10, 2004 - 2:31 pm UTC

Reviewer: A reader from USA

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.

getting back to the post about APPEND

October 20, 2004 - 8:07 am UTC

Reviewer: Connor McDonald from Perth

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 :-)

Tom Kyte

Followup  

October 20, 2004 - 11:19 am UTC

picky picky picky...

;)

Tom,question about append hint

December 26, 2004 - 3:17 pm UTC

Reviewer: Kati

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

Tom Kyte

Followup  

December 26, 2004 - 5:50 pm UTC

yes
no
maybe

all of the above. depends on your needs. if you insert /*+ append */ you cannot read the data until you commit, which could be a "no go" operation (with an on commit delete rows, it'd be out of the question for example)

Didn't have what I'm looking for

January 18, 2005 - 4:39 pm UTC

Reviewer: Ryan from Canada

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.

Tom Kyte

Followup  

January 19, 2005 - 10:07 am UTC

unless you use order by you have absolutely no reason to expect the records to be in any order.

relational databases are very finicky that way.


You HAVE to afford that order by -- else your program is wrong, so it would be useless.

You can get ordered data *fast* - there are structures out there to force order of data. Have you looked at IOT's, clusters, etc etc etc (no insert /*+ append */ of course, you cannot have your cake and eat it too -- append writes above the high water mark, to insert ordered implies "write the data where the data belongs")

but even then - you HAVE to (must, gotta, no if and or but's about it) use the ORDER BY to get ordered data reliably.




Thanks for responding so soon :-)

January 19, 2005 - 11:30 am UTC

Reviewer: Ryan from Canada

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).

Tom Kyte

Followup  

January 19, 2005 - 9:09 pm UTC

"Effective Oracle by Design" -- or "Expert one on one Oracle" if you are interested in my writings on them

else, we'll, they are sort of documented in the documentation and that is on the web.


YOU HAVE TO USE ORDER BY TO GET ORDERED DATA -- NO MATTER WHAT.

if the underlying structure allows us to skip the sort, we will but you have to use order by, else you cannot REASONABLY expect to get data ordered, we have no requirement to do so without it.

This is true for all databases really.

Parallel insert append over a db link

January 19, 2005 - 12:57 pm UTC

Reviewer: Rick from UK

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

Tom Kyte

Followup  

January 20, 2005 - 9:42 am UTC

did you try it without playing with any parameters first. ( i would have not used any non-default parameters )

also, with loopback, you are reading and writing to/from the same database. Are you sort of slamming the same physical device (append does direct path writes, perhaps the sqlnet wait is really a manifestion of "we are waiting on IO so the insert routine waits on sqlnet"


and do you have at LEAST 5 cpus on your system?? you do not want to use a degree of parallelism that is more than about 2xncpu - and you it doesn't make sense to have 15/5 -- 5/5 or 15/15 -- but 15/5 -- it doesn't make sense to have more readers than writers for such a simple case.

and are you using tcp/ipc for the sqlnet setup.

(eg: is this anything near a realistic simulation)

insert append over a database link

January 20, 2005 - 12:27 pm UTC

Reviewer: Rick Bakker from UK

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 Kyte

Followup  

January 20, 2005 - 7:18 pm UTC

o i was saying "let it default, it would be so rare to have it mean a thing to change it"

o you are reading and writing the same DISKs, how COULDn't it be an IO restriction?!? with two databases on different machines, then I know the writer is not contending with the reader..

there should be N database links - when you run this (it would be easiest actually to test with 2 databases not one) how many sessions do you see established on the remote site?

query performance

January 20, 2005 - 5:54 pm UTC

Reviewer: A reader from USA

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 Kyte

Followup  

January 20, 2005 - 7:42 pm UTC

ok, when I have to hit page down 5 times (i run at 1650x1080, not a small screen) -- it is not a review of a question, it's a new question :)


you answered the question with "i'm using the rbo" -- if you have 9ir2, use optimizer dynamic sampling hint and either all or first rows hint to do what you want.... (eg: use the cbo and let it dynamically sample the first time it hard parses in order to give the cbo a fighting chance)

Query performance

January 20, 2005 - 9:14 pm UTC

Reviewer: A reader from USA

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,

Tom Kyte

Followup  

January 20, 2005 - 11:10 pm UTC

rbo is the rbo is the rbo. the rbo is very very very predicable. if you run that query one billion times, it'll do the same thing.

without dynamic sampling, all_rows or first rows *might* accidently work OK. worth a try.

Parallel insert append over a db link

January 21, 2005 - 8:42 am UTC

Reviewer: Rick from UK

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


Tom Kyte

Followup  

January 21, 2005 - 12:07 pm UTC

Lets just look at serial for now. I cannot reproduce anything nearly your 8/160 split:

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

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.05 0 0 0 0
Execute 1 6.28 10.10 14367 15637 2677 1000000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 6.29 10.15 14367 15637 2677 1000000

Rows Row Source Operation
------- ---------------------------------------------------
1 LOAD AS SELECT (cr=15637 r=14367 w=14376 time=10100827 us)
1000000 COUNT STOPKEY (cr=14378 r=14367 w=0 time=5747962 us)
1000000 TABLE ACCESS FULL BIG_TABLE (cr=14378 r=14367 w=0 time=4383548 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
db file scattered read 1796 0.87 3.01
direct path write 2 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************

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


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.22 0 7 1 0
Execute 1 11.86 15.71 0 0 337 1000000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 11.86 15.93 0 7 338 1000000

Rows Row Source Operation
------- ---------------------------------------------------
1 LOAD AS SELECT (cr=0 r=0 w=14376 time=15712670 us)
1000000 COUNT STOPKEY (cr=0 r=0 w=0 time=12367538 us)
1000000 REMOTE (cr=0 r=0 w=0 time=10984586 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.00 0.11
SQL*Net more data from dblink 33895 0.03 2.93
direct path write 2 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00


can we see a really simple tkprof output like that (with waits and all) for you?



Parallel insert append over a db link

January 22, 2005 - 3:21 pm UTC

Reviewer: Rick from UK

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.

Tom Kyte

Followup  

January 22, 2005 - 4:38 pm UTC

something just isn't sitting right here -- your first one had lots of physical IO but hardly any waits on IO (12k pio's, but only 395 waits.

but you see -- it is not waits on the dblink at all -- it is CPU usage totally here. There were no waits at all of any measurable size udring the second one, it was all about CPU time there.

what does this single serial thing look like over a "real" dblink?

Parallel insert append over a db link

January 23, 2005 - 3:04 pm UTC

Reviewer: Rick from UK

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



Tom Kyte

Followup  

January 23, 2005 - 3:21 pm UTC

but now 20 seconds -- not 12, not 40


and no waits of any significance.


Is this all being done more or less on the same hardware?

I'm not understanding why 12.15 cpu seconds, vs 40, vs 20 -- for basically the same operation.

(this is not showing an 8 to 160 ratio by any means.)

Parallel insert append over a db link

January 23, 2005 - 4:00 pm UTC

Reviewer: Rick from UK

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

Tom Kyte

Followup  

January 23, 2005 - 4:27 pm UTC

cpu time would not result from disk io.


in order to test the theory that it is the single dblink causing great waits do this:

statspack.snap
do your parallel insert
commit;
statspack.snap


do that in isolation -- and we'll see what we see from that. it'll show "system wide" waits - if they are not sqlnet waits -- it is not that.

issue with tkprof on 9.2.0.4

January 23, 2005 - 4:24 pm UTC

Reviewer: Alberto Dell'Era from Milan, Italy

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."

Tom Kyte

Followup  

January 23, 2005 - 5:20 pm UTC

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

January 23, 2005 - 5:22 pm UTC

Reviewer: Alberto Dell'Era from Milan, Italy

> 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".

Tom Kyte

Followup  

January 23, 2005 - 5:34 pm UTC

right, but it shouldn't make it "slower" either (than the serial case)


what we might end up doing here is "do it yourself parallelism" in multiple sessions -- using rowid ranges at the end.

January 23, 2005 - 5:27 pm UTC

Reviewer: Alberto Dell'Era from Milan, Italy

> 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 ...


Parallel insert append over a db link

January 23, 2005 - 5:44 pm UTC

Reviewer: Rick from UK

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...



Tom Kyte

Followup  

January 23, 2005 - 5:51 pm UTC

we can "do it yourself" (DIY) parallelize this. this (not actually run, but gives the gist) piece of code would push the data in parallel:

declare
l_job number;
begin

for x in (
select grp,
dbms_rowid.rowid_create
( 1, data_object_id, lo_fno, lo_block, 0 ) min_rid,
dbms_rowid.rowid_create
( 1, data_object_id, hi_fno, hi_block, 10000 ) max_rid
from (
select distinct grp,
first_value(relative_fno) over
(partition by grp order by relative_fno, block_id
rows between unbounded preceding and unbounded following) lo_fno,
first_value(block_id ) over
(partition by grp order by relative_fno, block_id
rows between unbounded preceding and unbounded following) lo_block,
last_value(relative_fno) over
(partition by grp order by relative_fno, block_id
rows between unbounded preceding and unbounded following) hi_fno,
last_value(block_id+blocks-1) over
(partition by grp order by relative_fno, block_id
rows between unbounded preceding and unbounded following) hi_block,
sum(blocks) over (partition by grp) sum_blocks
from (
select relative_fno,
block_id,
blocks,
trunc( (sum(blocks) over (order by relative_fno, block_id)-0.01) /
(sum(blocks) over ()/&CHUNKS) ) grp
from dba_extents
where segment_name = upper('&TNAME')
and owner = user order by block_id
)
),
(select data_object_id
from user_objects
where object_name = upper('&TNAME') )
)
loop
dbms_job.submit
( l_job,
'insert into t@remote
select * from t
where rowid between chartorowid('''||x.min_rid||''')
and chartorowid('''|| x.max_rid||''');' );
end loop;
commit;

end;
/


(assuming you had job queue processes set high enough on the push site)

Parallel insert over a database link

January 23, 2005 - 6:02 pm UTC

Reviewer: Rick from UK

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

consistent parallel read from db-link

January 23, 2005 - 6:46 pm UTC

Reviewer: Alberto Dell'Era from Milan, Italy

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 ?

Tom Kyte

Followup  

January 23, 2005 - 7:15 pm UTC

that is a good addition if the source data is subject to change, definitely.

Parallel insert append over a dblink

January 24, 2005 - 4:05 am UTC

Reviewer: Rick from UK

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.

Tom Kyte

Followup  

January 24, 2005 - 8:29 am UTC

insert /*+ append */ doesn't avoid redo -- unless the table is NOLOGGING or you are not in archive log mode.

and the redo generation shouldn't be an issue? on a properly configuration system, redo generation should not be a bottleneck.

Parallel insert append over a dblink

January 24, 2005 - 10:10 am UTC

Reviewer: Rick from UK

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

Parallel insert append over a dblink

January 24, 2005 - 1:43 pm UTC

Reviewer: Rick from UK

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

Me again.

February 07, 2005 - 10:40 pm UTC

Reviewer: Ryan from Canada, Ontario

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.


Tom Kyte

Followup  

February 08, 2005 - 1:25 am UTC

there is NO assurance the rows will be in "any sort of order"

None.

first, this statement is totally 100% misleading:

"insert /*+ APPEND */ /*+ PARALLEL */ into
databasename (rowidentifier) values(%d),

append works with insert /*+ append */ into T select ....

append *does NOT work with values, that is documented*

parallel won't kick in there either!!!!!


and rows have no order in heap tables, when you insert into them, we put them where ever we feel

anyone that relies on the "order of rows in a table" is only kidding themselves (and writing really utterly buggy code to boot probably -- as the rows are "not in order")


even with a true append that works - each PQ slave gets a different extent, it would be pretty near impossible for them to "be in order" since they are written to "simultaneously"

thanks again :-)

February 08, 2005 - 8:49 am UTC

Reviewer: Ryan


Cant read until commit

February 17, 2005 - 9:08 am UTC

Reviewer: VA from New Jersey, USA

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

Tom Kyte

Followup  

February 17, 2005 - 10:11 am UTC

do not use append? seems straight forward. You have millions of features, not all can be used simultaneously -- therefore you make choices.

In this case, if you need to read the data before committing, you cannot use a direct path operation.

Why commit?

February 17, 2005 - 1:47 pm UTC

Reviewer: A reader

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?

Tom Kyte

Followup  

February 17, 2005 - 2:10 pm UTC

because you just obliviated our capability to do consistent reads as well, the very underpinning of how queries are done.

it is the way it is. the high water mark is a global thing.

A reader

March 15, 2005 - 5:45 am UTC

Reviewer: sven

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

Tom Kyte

Followup  

March 15, 2005 - 8:20 am UTC

the hwm is advanced about 5 blocks at a time, yes.

reader

April 19, 2005 - 12:27 pm UTC

Reviewer: A reader

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

Is this correct

Tom Kyte

Followup  

April 19, 2005 - 12:49 pm UTC

direct path loads do not bypass REDO generation, unless you explicitly make it do that.

So no, the database typically does not require a backup (you can query v$datafile to see the unrecoverable_time) after a direct path load. the segments loaded into would have to have been "nologging" and the database would have to permit "non logged" operations.

Thanks

April 19, 2005 - 3:01 pm UTC

Reviewer: A reader

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 ....

Tom Kyte

Followup  

April 19, 2005 - 7:37 pm UTC

the buffers are just in "memory", build a block, write a block.

reader

April 20, 2005 - 7:20 am UTC

Reviewer: A reader

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 

Tom Kyte

Followup  

April 20, 2005 - 7:22 am UTC

</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96524/c21dlins.htm#11358 <code>




Reader

April 20, 2005 - 12:59 pm UTC

Reviewer: A reader

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

Tom Kyte

Followup  

April 20, 2005 - 9:10 pm UTC

direct loads in this case are only available "outside" the database (sqlldr) and they just format and write blocks.

Reader

April 22, 2005 - 7:47 am UTC

Reviewer: A reader

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 Kyte

Followup  

April 22, 2005 - 10:37 am UTC

direct writes write ABOVE the high water mark.

they do not use freelists they do not use any existing free space

Thanks

April 22, 2005 - 10:39 am UTC

Reviewer: A reader


Query with db links

May 09, 2005 - 12:49 pm UTC

Reviewer: A reader

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


Tom Kyte

Followup  

May 09, 2005 - 2:39 pm UTC

why no second plan?

Query with db links

May 09, 2005 - 5:54 pm UTC

Reviewer: A reader

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 Kyte

Followup  

May 09, 2005 - 6:04 pm UTC

I probably like query 1 better than 2 and #3 would be a "no go under any circumstances"

Query with db links

May 09, 2005 - 11:07 pm UTC

Reviewer: A reader

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


Tom Kyte

Followup  

May 10, 2005 - 7:47 am UTC

option #3 is the option to make sure I cannot actually do anything as both databases would need to be available always. It would impact my real time performance. It would have me be using dblinks as part of my normal operation. None of these are good things.

Option 1 was a probable -- run remote query to find max, generate the about 24k rows and send them over the dblink:

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

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
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"


I could not see the entire remote query -- but if it was a full scan without a predicate, I would not want it. If it has a predicate and only sends the rows we want back -- then it is equivalent. So, given I knew what the above did and wasn't 100% sure what the following does, I chose the above....

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

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) Card=5429090
2 SERIAL_FROM_REMOTE SELECT "STATUS", "RUNDATE", "INVOICECREATIONDATE",
"INVOICECREATEDBY", "INVOICEUPDAT

question on space wastage,

May 11, 2005 - 2:11 pm UTC

Reviewer: sns from austin,tx

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,

Tom Kyte

Followup  

May 11, 2005 - 7:24 pm UTC

can you parallelize the insert statement?

the append will go above the HWM, might as well not delete the old data, you won't reuse that space.



VLTs

May 12, 2005 - 4:57 am UTC

Reviewer: Billy from Cape Town, ZA

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.

partitioning????

May 12, 2005 - 10:31 am UTC

Reviewer: sns from austin,tx

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.



Tom Kyte

Followup  

May 12, 2005 - 1:16 pm UTC

there is also "do it yourself parallel", you can just run multiple sessions as well.



Lost space I can't find

May 17, 2005 - 11:00 am UTC

Reviewer: A reader

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&#8217;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)
 

Tom Kyte

Followup  

May 17, 2005 - 2:07 pm UTC

we are worried about less than 1mb? data is packed differently, that is all. this is less than "noise" -- 1/3 of 1% of the volume of data.

as far as I'm concerned (this looks to be dictionary managed storage) these two numbers are not even different.

arraysize has zero effect on insert as select.


May 17, 2005 - 2:13 pm UTC

Reviewer: A reader

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 ;


Tom Kyte

Followup  

May 17, 2005 - 2:40 pm UTC

64k for 200+ meg -- might consider using autoallocate....

but it is most likely ASSM (segment space management auto) that is causing the diffs here.

May 17, 2005 - 6:11 pm UTC

Reviewer: A reader

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

Tom Kyte

Followup  

May 17, 2005 - 6:38 pm UTC

0) it is a matter of choice, but I've had no issues whatsoever (no loss of sleep for example) using auto sizing. I don't have to think about it anymore.

in a data warehouse - -where I had a really good handle on the size (or the sheer size) of a segment, uniform would be OK.

if I needed to micro manage space -- uniform would be OK.

otherwise, I don't care to deal with it anymore :)

1) yes.

2) sure, use the autoallocate (64k and 80k are very very very small)

May 17, 2005 - 6:59 pm UTC

Reviewer: A reader

Thanks a lot Tom.

How to Reduce LIO when using Insert Append?

May 20, 2005 - 7:58 pm UTC

Reviewer: Vin from NY-USA

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.

Tom Kyte

Followup  

May 21, 2005 - 8:44 am UTC

I would guess you are using the rbo and it is using indexes (nested loops joins). one would need to see the plan.

Oracle Ver 8174

May 20, 2005 - 7:59 pm UTC

Reviewer: Vin from NY-USA


Plan for Select

May 21, 2005 - 9:24 pm UTC

Reviewer: Vin from NY-USA

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.


Tom Kyte

Followup  

May 22, 2005 - 8:12 am UTC

plans are the same.

but they are 100% not relevant. You cannot count a count() to a real query, not even a little.

Plan from Real Select of Insert-Append

May 22, 2005 - 9:49 am UTC

Reviewer: Vin from NY-USA

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

Tom Kyte

Followup  

May 22, 2005 - 11:01 am UTC

the cardinality is off by more than an order of magnitude. For a big batch query like this, we might want to not use bind variables. What does the plan for:

column DT1 new_val DT1
column DT2 new_val DT2

select to_char(sysdate-4,'yyyymmdd') dt1, to_char(sysdate,'yyyymmdd') dt2 from dual;

insert /*+ ........
And a.SUBMIT_DATE > to_date(dt1,'yyyymmdd')
And a.SUBMIT_DATE < to_date(dt2,'yyyymmdd')



look like. those nested loops should probably not be there.

Can't thank you enough!

May 22, 2005 - 10:23 pm UTC

Reviewer: Vin from NY-USA

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.


Tom Kyte

Followup  

May 23, 2005 - 8:25 am UTC

o is it a trick? No, not really. when you have a "big query" that is "infrequently executed" (think data warehouse type stuff for example), you want to give it (the optimizer) as much information as possible.

When you used sysdate -- it has to come up with a plan that works OK today and next month.

When you use to_date( 'some date' ), then the date isn't variable in that query and it "knows" better how much data will be returned.

when doing "big" stuff, bind variables (and sysdate is sort of like a bind, everytime you run the query, sysdate is different) are not always appropriate.


When you run MANY QUERIES PER SECOND -- you must bind.
When you run a query that takes MANY SECONDS -- you might not bind.

o full scans are not evil, full scans are not evil, full scans are not evil (please repeat.... till you believe it).

Why not?


o you most likely have set a non-default degree of parallelism on the table(s) being queried and parallel query was used to run the query itself.

Insert Append and External Table

May 25, 2005 - 2:19 pm UTC

Reviewer: Vin from NY-USA

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.

Tom Kyte

Followup  

May 25, 2005 - 7:28 pm UTC

perhaps the data really doesn't "fit"?

can you

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

?

Data exist only for a day 24-MAY-2005

May 25, 2005 - 8:29 pm UTC

Reviewer: Vin from NY-USA

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 

Changed the Partioning Key, and Works Well Now

May 26, 2005 - 2:37 pm UTC

Reviewer: Vin from NY-USA

Many Thanks for the help.

u r excellent tom

June 28, 2005 - 9:14 pm UTC

Reviewer: shyam

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 .



Direct Path seems to be used loosey goosey

August 24, 2005 - 1:55 pm UTC

Reviewer: Brady from Baltimore, MD

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 */?

Tom Kyte

Followup  

August 24, 2005 - 6:36 pm UTC

a direct path operation is done when you

sqlldr direct=y
create table as select
insert /*+ APPEND */ into t select ....
alter table T move
create index I....

and so on, many things use "direct path" writes to the data file.

My confusion is...

August 29, 2005 - 4:37 pm UTC

Reviewer: A reader

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.

Direct load & FK In 10G

November 11, 2005 - 9:06 pm UTC

Reviewer: Shivaswamy from MD, USA

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


Tom Kyte

Followup  

November 12, 2005 - 10:51 am UTC

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>
 

November 12, 2005 - 3:25 pm UTC

Reviewer: Shivaswamy

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


Tom Kyte

Followup  

November 12, 2005 - 4:39 pm UTC

...
Further, the data can be inserted either in serial mode, where one process
executes the statement, or parallel mode, ...

it seemed to clearly say "EITHER in serial mode OR in parallel mode"?


it can certainly be done without parallel query

November 12, 2005 - 9:29 pm UTC

Reviewer: A reader

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?


Tom Kyte

Followup  

November 13, 2005 - 10:08 am UTC

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. 

November 14, 2005 - 12:45 pm UTC

Reviewer: A reader

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 Kyte

Followup  

November 14, 2005 - 2:13 pm UTC

I would expect to see:

nsert /*+ append */ into t select 1 from dual


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

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

Rows Row Source Operation
------- ---------------------------------------------------
1 LOAD AS SELECT (cr=0 pr=0 pw=1 time=3909 us)
1 FAST DUAL (cr=0 pr=0 pw=0 time=4 us)


load as select.

Why is APPEND hint slower?

November 29, 2005 - 1:26 pm UTC

Reviewer: Robert from Memphis, USA

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 Kyte

Followup  

November 30, 2005 - 10:55 am UTC

insert append writes directly to disk. You had to format a block buffer and then write it to disk yourself.


insert without the append writes to the buffer cache. dbwr was responsible for writing this out in the background.

so it could be that the conventional path insert writing to the buffer cache and dbwr writing out the blocks in the background worked out nicely for you in this case.

Great explanation! and most helpful!!!

November 30, 2005 - 10:57 am UTC

Reviewer: Robert from Memphis, USA


Is insert /*+ append */ faster than import?

February 10, 2006 - 10:27 am UTC

Reviewer: A reader

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 Kyte

Followup  

February 10, 2006 - 1:09 pm UTC

insert append would be faster

no wait, import would be faster

no wait, they will run at the same speed.


All three are possible.

Import will write to the "buffer cache". It will array insert and write to the buffer cache and dbwr will flush the blocks to disk in the background. Import will generate UNDO (which insert append will not). So import will generate more blocks than insert append will write, but import will generate them in the buffer cache. In noarchive log mode, import will generate REDO that insert append will not (but in archive log mode BOTH will generate redo!!!) - but since redo is also written out in the background by lgwr as the import is taking place - it typically should not affect it too much (and would have the same effect for both in archivelog mode)

insert append will not write to the buffer cache, append will write directly to the datafiles. Therefore you will wait while it does, dbwr is not flushing this in the background as with import, you are doing the IO directly and waiting for it.


Therefore, import could be faster.

append could be faster.

they could run the same.



In "general", to load a huge amount of data (so that the fact we buffer is not really relevant as we blow away the cache anyway and end up waiting for IO), a parallel direct path load can be brutally efficient - really fast.

To load a moderate or small amount of data, import with a nice array insert size, can be very nice indeed.


So, "it depends" :)

Is insert /*+ append */ faster than import?

February 11, 2006 - 1:25 pm UTC

Reviewer: A reader

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 Kyte

Followup  

February 11, 2006 - 2:44 pm UTC

how about considering:
a) upgrade the 9i to 10g on old OS
b) user RMAN to cross platform transport



to me, if you cannot already do (A and B) in your list without an export, that is a problem (well, OK maybe not the users - but the schema??)



I've used exp/imp in "parallel" (export many times, in many sessions, many different bits of data) quite successfully as well. You might find that easier.

Is insert /*+ append */ faster than import?

February 12, 2006 - 1:23 pm UTC

Reviewer: A reader

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...

Tom Kyte

Followup  

February 12, 2006 - 5:13 pm UTC

dual testing? Not really, you have to test that the cross platform will work - but you don't have to test your application on the old platform (it'll never be running there).


dblinks are generally not the speediest things (no parallel direct paths with them).

You might find that dump to flatfile (custom program, I have a pro*c one I use from time to time) and reload using external tables and parallel create table as select to be something to consider.

APPEND with multiple Sessions

March 06, 2006 - 4:52 am UTC

Reviewer: A reader

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
 

Tom Kyte

Followup  

March 08, 2006 - 3:49 pm UTC

yes, it will.

can you please explain it

March 10, 2006 - 11:57 am UTC

Reviewer: A reader

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

Thanks


Tom Kyte

Followup  

March 10, 2006 - 8:23 pm UTC

because you are doing a direct path operation, writing above the high water mark. You are doing something "very special", something "unusual".

If you need parallel, we can certainly do parallel, you just cannot "do it yourself"

FORCE LOGGING

May 15, 2006 - 2:32 am UTC

Reviewer: A reader

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.



Tom Kyte

Followup  

May 15, 2006 - 6:51 am UTC

wrong,

when you use /*+ append */ Oracle can skip undo generation for the TABLE data - but not on the indexes. Additionally, when the database is in archivelog mode, all REDO for the table is generated. REDO is normally generated with /*+ APPEND */, it is UNDO that is skipped and then only for the table itself.


Now, if you place the table into "nologging" mode, then redo for the table as well as undo can be skipped. But again, only for the table - not for any indexes on the table itself.

And if you put the database into force logging mode, then NOLOGGING is ignored and all redo is generated again (but undo is not generated)

redo and undo

May 15, 2006 - 8:32 am UTC

Reviewer: A reader

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.


Tom Kyte

Followup  

May 15, 2006 - 9:40 am UTC

the lack of undo generation for the table data only does not affect anything regarding consistency.

The undo would normally be used to un-insert the rows in the event of a failure or rollback - with APPEND this undo is not necessary since the new rows are added entirely above the high water mark for the table - hence the act of rolling back just needs to encompass "undo the dictionary update that advanced the high water mark". So, every bit of undo we need to have present is in fact present.

minextents and direct load

June 26, 2006 - 7:41 am UTC

Reviewer: A reader

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!



Tom Kyte

Followup  

June 26, 2006 - 7:54 am UTC

you did a direct path load didn't you....

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

truncate does (did) reset the tables high water mark. If you full scan that table after you truncate - how much IO was performed.

If you want to deallocate the allocated extents, that would be something you can specify in the truncate.


</code> http://docs.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10006.htm#sthref9983 <code>
"drop storage"

minextents and direct load

June 26, 2006 - 9:34 am UTC

Reviewer: A reader

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)

Tom Kyte

Followup  

June 26, 2006 - 10:50 am UTC

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
 

undo generation when using insert append

October 08, 2006 - 3:37 pm UTC

Reviewer: A reader

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

Tom Kyte

Followup  

October 08, 2006 - 4:53 pm UTC

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) 

October 10, 2006 - 3:25 am UTC

Reviewer: A reader

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.


Tom Kyte

Followup  

October 10, 2006 - 8:06 am UTC

... The only operation
which needs to access undo is the INSERT operation.
....

absolutely FALSE, 100% false. ora-1555's - means we could not read undo we needed and is only signalled for ..... READS. This is called "consistent read" in Oracle, we constantly read the undo tablespace during the processing of YOUR QUERIES. It is the fundemental way in which Oracle works!

You might want to peek at the concepts guide and give the chapters on concurrency/consistency a read through.

do this:

select log_mode, force_logging from v$database;

that is the only way I'm able to reproduce anything close to your findings - to have force logging be true.

undo generation when using insert append

October 12, 2006 - 5:55 pm UTC

Reviewer: A reader

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

Tom Kyte

Followup  

October 13, 2006 - 7:00 am UTC

sure there is, all of the time - you are never the only game in town. the database is constantly and forever doing things.

Redo being generated

October 18, 2006 - 6:07 pm UTC

Reviewer: A reader from Earth

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! 

Tom Kyte

Followup  

October 18, 2006 - 7:59 pm UTC

the difference between 48k and 88k is so small here, you are talking a couple of blocks!

when you do the insert append - indexes are ALWAYS LOGGED - period. the insert append:

a) skips undo generation on the table (and since undo generates redo, this can lessen the amount of redo generated)

b) can skip REDO for the TABLE (but not indexes!!!) if the table is nologging


The way an insert append works is to write the table data above the high water mark for the table and to create "mini indexes" of the newly inserted data. These indexes are then merged with the "real" indexes after the insert append is done writing to the table. This reduces the amount of redo generated by the insert operation on the index (because big bulk operations like the merge of the index do that).

So, the first time you did that - the merge was "empty index plus new index" - little work to do.

The second time you merged "full index plus new index data" - quite different from merging an empty index.


and I would hardly call 44k more "a lot"

Parallel Insert over db link

April 13, 2007 - 3:41 pm UTC

Reviewer: Vikas from INDIA

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
Tom Kyte

Followup  

April 13, 2007 - 7:19 pm UTC

parallel != dblink

it is not going to be parallel over a dblink, it cannot work that way.


April 15, 2007 - 2:46 pm UTC

Reviewer: Alberto Dell'Era from Milan, Italy

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

It's the parallel insert that never happens.
Tom Kyte

Followup  

April 16, 2007 - 1:10 pm UTC

yes, correct, no operations OVER a dblink done in parallel. remote operations invoked via a dblink - can be.

DML is inclusive of SELECT by the way :)

PARALLEL INSERT APPEND

June 12, 2007 - 3:36 pm UTC

Reviewer: A reader

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.

Re: PARALLEL INSERT APPEND

June 13, 2007 - 10:00 am UTC

Reviewer: A reader

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

Tom Kyte

Followup  

June 13, 2007 - 2:15 pm UTC


you yourself introduced parallel:

. When I copy SOURCE_TABLE into DESTINATION_TABLE_PARALLEL, DESTINATION_TABLE_PARALLEL uses more than 200MB of space than SOURCE_TABLE.

it explains extent trimming and other stuff that happens with parallel direct path loads.

so I'm confused by your attitude? You are "a reader", I have no clue what you do and do not know. Most people I find ask without bothering to try to find out themselves. Do you see any hard and fast numbers to look at in your example? I see you saying "i have such and such 100% full blocks", but I don't see the code that produced those numbers - so I question what those numbers are and how you got them.

this area is for review followup, yours was so large as to exceed the 32k limit we have - there was nothing 'meaty' to look at.



If you take a fully packed table (eg: every block is 100% full - up past pctfree and all) and you copy it and reintroduce pctfree (so now each block is say 10% free) - it'll GROW of course. That is one possible reason.

Another is that by definition ASSM wastes space to increase concurrency - it spreads data out. It uses blocks inside of the table (bitmap blocks) to manage the space as well.

Re: INSERT APPEND PARALLEL

June 13, 2007 - 3:23 pm UTC

Reviewer: A reader

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.


Tom Kyte

Followup  

June 13, 2007 - 3:36 pm UTC

I see no show_space output - your stuff was too large.

and I now see that is a bug in my user interface :) I cannot see it but you all can.

why do you say pctfree 0? I don't see you specify one.

Re: INSERT APPEND PARALLEL

June 13, 2007 - 3:57 pm UTC

Reviewer: A reader

:) 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.


Tom Kyte

Followup  

June 13, 2007 - 4:04 pm UTC

ahh, compression (you, well, left that bit out)

compression works via factoring out repeated things on a block. So the parallel query - easy - the data was organized way differently, different compression ratio (you had N execution servers reading and writing data - it just got compressed differently)

and ASSM - by spreading stuff out - would tend to have the rows end up in different blocks as well - so the serial table is a different size as well.



Thanks Tom!

June 14, 2007 - 10:34 am UTC

Reviewer: A reader


direct insert and referential integrity constraints

October 05, 2007 - 8:14 pm UTC

Reviewer: alan

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

APPEND hint

November 30, 2007 - 6:23 am UTC

Reviewer: A reader

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)
Tom Kyte

Followup  

November 30, 2007 - 1:35 pm UTC

if you use insert /*+ append */ (and it works - the append is allowed), you bypass the buffer cache, you write above the high water mark of the table (never reuse existing space). You do a direct path write. Indexes are maintained in the most efficient manner possible (a new index for the newly loaded data is created and after the data is loaded, it is merged in bulk into the existing index - rather than lots of single row entries all over the place)

if you do not, you will modify the blocks in the buffer cache, you will reuse existing free space before going above the high water mark.

You can use the insert without the append, it will reuse the space - however, it will work very differently - so expect that.

it might go faster (not likely if this is a large amount of data), it might run about the same (same comment), it might run slower and tend to end up with larger index structures (somewhat likely)

Force Logging

March 11, 2009 - 7:42 pm UTC

Reviewer: A Reader

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
Tom Kyte

Followup  

March 12, 2009 - 12:41 pm UTC

direct path loads skip undo generation on the table. direct path loads bypass the cache and write formatted blocks directly to disk. They do not spend time looking for free space below the high water mark, they just do it above the high water mark.

You might rethink the indexing strategy. A direct path load will create mini-indexes for the newly loaded data as it loads it - and then merge in bulk these mini indexes into the larger existing indexes after the load. Many times that is a lot more efficient than full scanning the table N times AFTER the load to build (read, sort, write) the entire index.

And just leave constraints on - again, why scan the table over and over and over after the fact when you could just do it as it streams in and ONLY for the newly loaded data.

Force Logging

March 19, 2009 - 2:48 am UTC

Reviewer: A Reader

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.


Tom Kyte

Followup  

March 19, 2009 - 10:34 am UTC

yes, that is what I'm saying.

If you skip indexes and constraints during the load, you will have to full scan the table N times (once for each index, once for each constraint you enable) afterward. You may well find the time to load and then index and then constrain exceeds the time to load while maintaining indexes and validating constraints.

March 21, 2009 - 9:31 am UTC

Reviewer: Sachin from india

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 Kyte

Followup  

March 24, 2009 - 10:52 am UTC

that just means you did something specified in this error message:

ops$tkyte%ORA10GR2> !oerr ora 12840
12840, 00000, "cannot access a remote table after parallel/insert direct load txn"
// *Cause: Within a transaction, an attempt was made to perform distributed
// access after a PDML or insert direct statement had been issued.
// *Action: Commit/rollback the PDML transaction first, and then perform
// the distributed access, or perform the distributed access before the
// first PDML statement in the transaction.


you do not show your logic, but you must be accessing a remote table after you did the direct path.

and that is not allowed.

When is APPEND allowed?

April 20, 2009 - 10:19 am UTC

Reviewer: A reader

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...

select from table1@mydblink

June 26, 2009 - 10:47 am UTC

Reviewer: Mohamed from France

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
Tom Kyte

Followup  

June 26, 2009 - 11:25 am UTC

no, you are not, you are entirely mistaken in your belief.

In other words "you are wrong"


ops$tkyte%ORA10GR2> select * from t;

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

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

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



you have done something to confuse yourself.

Append on Global Temporary Table

March 13, 2010 - 2:30 am UTC

Reviewer: Avi from India

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.
Tom Kyte

Followup  

March 15, 2010 - 9:54 am UTC

ok, why don't you see any benefit of not doing undo - once you tell us that, we can answer you.

a pro: undo generation that would normally be done is skipped.
a con: you cannot query that table until you commit



Batch Upload

September 13, 2010 - 2:41 am UTC

Reviewer: Raghavendra Jha from INDIA

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..