Long inserts with high cost

[Chucks_k]

Hi all,

Is there a way of speeding up inserts in a long table (current cost is 32,000!)? I think it scans for all empty blocks then inserts in the end. The inserts are sequential and always at the end. Was just wondering if there was a faster way of doing this.

Thanks,
Chucks.

[marist89]

Explain plan & maybe a trace file??

[Chucks_k]

Hi Jeff,

Here is a snippet of the trace file:

insert into page_requests (timestamp,session_id,cust_id,store_id,domain,
path_info,query_string,page_request_time,ip_address)
values
(SYSDATE,:SYS_B_0,:SYS_B_1,:SYS_B_2,:SYS_B_3,:SYS_B_4,:SYS_B_5,:SYS_B_6,
:SYS_B_7)


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 6 0.05 0.11 0 98 0 0
Execute 6 0.07 0.04 0 6 42 6
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 12 0.12 0.15 0 104 42 6

Misses in library cache during parse: 6
Optimizer goal: CHOOSE
Parsing user id: 24 (BURT)

Rows Execution Plan
------- ---------------------------------------------------
0 INSERT STATEMENT GOAL: CHOOSE

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

Any clues?

Cheers,
Chucks

[Chucks_k]

Hi all,

Are there any hints that can be used with the INSERT INTO.. VALUES statment to reduce cost of the statement?. I tried using the /*APPEND*/ hint with nologging unfortunately the cost of the query remained the same.High!!.

[marist89]

/* APPEND */ is not a hint, /*+ APPEND */ is.

You may want to look at why you're parsing every INSERT statement. From the stats, you have parsed this statement 6 times and executed it 6 times. You should be parsing once and executing many times.

[slimdave]

APPEND is not valid with the INSERT INTO ... VALUES syntax, only with the INSERT INTO ... SELECT ... syntax.

[tamilselvan]

=========
Is there a way of speeding up inserts in a long table (current cost is 32,000!)?
========
Does this table have a column with LONG DATA TYPE?

Tamil

[robertbalmer]

do you have any triggers enabled that relate to this table(s).
If so,...alter trigger your_table_trigger disable;
execute insert and enable trigger again after commit.