Insert with a select, Insert with a select Options

[avdba_22]

We are using 10g. I have to load about 15million records or more from staging table into another table. It is a OLTP environment. And it is taking about 1 hrs to do this.

I am doing :

insert into t_so(SO_ID,
FIRST_NAME,
LAST_NAME,
ADDRESS1,
ADDRESS2,
MIDDLE_INITIAL,
CITY_STATE_ZIP_ID)
SELECT seq_So.nextval,
PCFIRST,
PCLAST,
substr(PCMINIT, 1,1) PCMINIT,
PCPADDR,
PCSADDR,
pkg_so.fn_get_city_state_zip_id ( PCCITY , PCSTATE, PCZIP5)
FROM t_stage;

I optimized the function and there is lot of improvement . Could any of you suggest anything more I could do to improve performance in 10g. Please let me know .

Thanks

[edwardstoever]

Optimization is a funny thing. Sometimes we spend hours to "optimize" something to save time. So, we get it to work faster, say we cut it from 2 hours down to 1 hour. But it takes us 3 hours to "fix" it, and it will only be run twice, then never again.

So, we trade our own time for server time.

I would rather drink coffee than worry about that kind of thing.

It really comes down to this: how often are you going to insert 15 million rows? Just one time? Lots of times? Every day for the next 10 years?

Does it really have to go faster?

Thanks,

Edward

[hpuxrac3]

The scientific way to approach this is to determine what is slowing down the load time. This is known as the wait event interface approach.

I would guess ... without knowing anything for sure ... that your call that invokes the plsql function is what is slowing down the load.

So without real evidence I would guess that eliminating the calsl to the plsql function would help you out.

Puttting on a 10046 trace and performing your analysis of what shows up as slowing down the load is the most reliable approach. That way you ( and I ) are not guessing.

If the results of a 10046 trace show mostly CPU then that would to me appear to confirm that the plsql function is part of your bottleneck.

[avdba_22]

We will using this insert many many times. Function had a problem and that is fixed. In addition can some one give me some tips on how to improve such inserts?

for ex) using append hint .. looking for this kind of other tips in 10g.

Thank you.

[dave]

if not using the table while inserting

try dropping the indexes insert /*+append*/ then rebuild the indexes nologging (then take a backup!)

[mike9]

I would try to remove the function and to replace it with a join with a table containing (PCCITY , PCSTATE, PCZIP5, state_zip_id).

For large inserts I would recommand to set the CACHE of you sequence to something like 100.

[burleson]

Hi,

Here are some general tips from Davce Moore's book "Oracle Utilities"

http://www.dba-oracle.com/oracle_tips_load_speed.htm

- Edited to remove potential errors -

[mbobak]

Couple of quick points:
Regarding "a" in Don's first post in this thread, using parallel programs and each using APPEND
will not work. APPEND takes an exclusive TM enqueue. This will cause the other parallel processes
to serialize, and you won't gain any benefit. If you want to comine parallel processing and the
APPEND hint, use Oracle's parallel DML. (See the PARALLEL hint).

Also, if you're going w/ APPEND, consider putting the table into NOLOGGING mode, which will
allow Oracle to avoid almost all redo logging. (I assume the database is in archive log mode.
In noarchivelog mode, the redo is automatically avoided when using APPEND, and the NOLOGGING
setting is not needed.) If you are considering NOLOGGING, consider the backup and recovery
implications before proceeding.

Hope that helps,

-Mark

[Laks]

Hi Mark,
As of Oracle 9i i think parallel DML would work only on partitioned tables and it would be really beneficial if the table is partitioned else created a CPU bottleneck..
But not sure of how parallel DML works in 10G?
Can you Please clarify here?

Thanks
Laks

[mike9]

How long does it take to execute the following 2 inserts?

insert /*+ append */ into t_so(SO_ID,
FIRST_NAME,
LAST_NAME,
ADDRESS1,
ADDRESS2,
MIDDLE_INITIAL,
CITY_STATE_ZIP_ID)
SELECT seq_So.nextval,
PCFIRST,
PCLAST,
substr(PCMINIT, 1,1) PCMINIT,
PCPADDR,
PCSADDR,
pkg_so.fn_get_city_state_zip_id ( PCCITY , PCSTATE, PCZIP5)
FROM t_stage;


insert /*+ append */ into t_so(SO_ID,
FIRST_NAME,
LAST_NAME,
ADDRESS1,
ADDRESS2,
MIDDLE_INITIAL,
CITY_STATE_ZIP_ID)
SELECT seq_So.nextval,
PCFIRST,
PCLAST,
substr(PCMINIT, 1,1) PCMINIT,
PCPADDR,
PCSADDR,
1
FROM t_stage;

[mbobak]

Hmm...actually, parallel DELETE and UPDATE don't work on non-partitioned tables.
Parallel INSERT is ok. However, there are other restrictions. No referential integrity, for
example.

See: http://download-west.oracle.com/docs/cd/B1...ns.htm#CNCPT328

for more info.

[burleson]

Hi,

Question for Mark,

You noted:

using parallel programs and each using APPEND will not work. APPEND takes an exclusive TM enqueue. This will cause the other parallel processes
to serialize, and you won't gain any benefit.

Thanks! I have updated my notes.

It was my undersanding that APPEND will increase the HWM on each freelist independently, and the TM enqueue would only happen if you forgot to define multiple freelists for the table/indexes.

- Does this "APPEND" TM serialization apply when using multiple freelists or freelist groups?

- What about Automatic Segment Space Management (bitmap freelists)? The documentation implies that there will not be serialization for free space lookups.

Can you educate us, please? TIA!

[mbobak]

Hi Don,

Blocks which have been allocated to a segment can be in one of three situations:

- Above the HWM. They have never been used, or at least, not been used since the
segment was last truncated or moved.

- Below the HWM, on a free list. These are blocks which have (or recently had) some
data in them, but based on PCTFREE/PCTUSED settings, have space still available. These blocks
will be used for conventional (non-direct) inserts.

- Below the HWM, not on a freelist. These are blocks which have data in them, and based
on PCTFREE/PCTUSED settings, do not have enough space available to support other inserts.

Now, when you do a direct-path INSERT, it will allocate blocks *above* the HWM. Therefore,
none of those blocks are on a free list. The free list(s) is (are) out of the picture. So, it really
doesn't matter how many free lists you have. In the direct-path INSERT case, free lists do not
play a role.

As to the TM enqueue, well, any DML or DDL will take a TM enqueue. This is to protect the
object's data dictionary definition. Conventional DML will take a TM enqueue in shared mode.
DDL takes a TM enqueue in exclusive mode. So, a table that's undergoing DML cannot have
DDL executed. (The exclusive mode required for DDL is not compatible with the shared mode
the DML session already holds.) However, other conventional DML will be able to proceed,
cause a shared mode enqueue is comatible with the shared mode already held. In this way,
Oracle allows concurrent DML, while preventing DDL on tables undergoing DML. Now, the direct-
path INSERT is a special case. A direct-path INSERT takes a TM enqueue in exclusive mode,
the same as DDL does. This prevents all other DML from occurring. Honestly, I don't know
enough about the details of the mechanism to clearly explain why this is required. I would
speculate it's because otherwise, it would be possible to have direct-path INSERT modifying
the HWM, and then (if it were permitted) conventional DML could fill all the blocks on the freelists,
causing that session to want to raise the HWM. That would be a problem, cause you'd have two
different sessions wanting to raise the HWM at the same time. So, seems to me, you either
serialize on the TM enqueue, or, you'd serialize on ST enqueue. (ST=space management
transaction enqueue)

As to ASSM, I really don't have much experience w/ that feature yet, however, from the
point of view of why a direct-path INSERT requires an exclusive TM enqueue, I think the
argument is largely the same. you don't want to concurrent DML sessions trying to concurrently
access the HWM.

Hope that's clear and answers your questions,

-Mark

[mbobak]

Whoops, David beat me to it....;-)

[mbobak]

Oh, by the way, Don, in point 'c' in that tip, the example insert statement you give:

CODE

insert /*+ append */ into customer values ('hello',';there');

is not valid. APPEND only works with Insert into table1 select * from table2;

It really doesn't make sense for a single-row insert.

-Mark