Insert taking too much of time.

[samdba]

Hi there,

It takes less than 1 second when I run one query which has two UNION ALL.

But, when I use the same Query to insert the rows into a table, it takes too much of time and seems like it hangs. It does not come out. Then, I have kill the session.

I altered the table and made it LOGGING = NO to avoid Roll back segment and Redo operations.

I have to insert the queries rows in one temp. table in Pre-report trigger.

Total rows = 79000.

Is there any way to accelerate Insert operation?

Thanks in Adv.

[Mr.Hanky]

SQL* copy command, you can set the arraysize and copycommit. I'm not sure if this will speed it up.

What about adding a query to an export file and using IMP/EXP, providing this is 8i.

MH

[irehman]

You may have lots of indexes created, also check that.

[samdba]

I forgot to write that we are on 8.0.5.

Mr.Hanky,

I have to run Insert statement from the D2K reports 2.5 in Before-Report trigger to insert the rows as per User inputs and then generate the report.

So, I am not sure whether I can use SQL* copy command.

I cannot use a query to an export file and using IMP/EXP, as I am not on 8i.

irehman,

The table in which I am inserting rows is a temp.table without any Indexes.

Thanks both,

Any more ideas?

[marist89]

Make sure your db is tuned; i/o is distributed, no rbs contention, good buffer hit ratio, etc.

[samdba]

Originally posted by marist89
Make sure your db is tuned; i/o is distributed, no rbs contention, good buffer hit ratio, etc.

Hi,

I think Database and buffer hit ratio are fine. I doubt about RBS.

SELECT CLASS, COUNT FROM V$WAITSTAT WHERE CLASS like '%undo%';

shows foll. result

save undo block 0
save undo header 0
system undo header 0
system undo block 0
undo header 5 <-- Problem?
undo block 0

But the table in which I am inserting rows is defined as LOGGING = NO at the time of creation.

Any suggestion?

Thanks,

[marist89]

NOLOGGING is one of the most misunderstood topics in Oracle Database Administration. Even if you have NOLOGGING turned on, not all operations are NOLOG operations. See http://technet.oracle.com/docs/produ...dlins.htm#4418 for details.

[samdba]

Originally posted by marist89
NOLOGGING is one of the most misunderstood topics in Oracle Database Administration. Even if you have NOLOGGING turned on, not all operations are NOLOG operations. See http://technet.oracle.com/docs/produ...dlins.htm#4418 for details.

Thanks for clearing the subject marist89. I will try Direct-Load Insert with Append hint. I also think that because I am inserting about 80000 rows in one shot / one transaction, it is taking this much time. I commit transaction after insert. Will it make any difference if I divide single 80000 rows insert transaction into 1000 rows (commit) per transaction?

Pl. advise.

Thanks,

[Mr.Hanky]

[[/B][/QUOTE]
Thanks for clearing the subject marist89. I will try Direct-Load Insert with Append hint. I also think that because I am inserting about 80000 rows in one shot / one transaction, it is taking this much time. I commit transaction after insert. Will it make any difference if I divide single 80000 rows insert transaction into 1000 rows (commit) per transaction?

Pl. advise.

Thanks, [/B][/QUOTE]

Setting up a commit will certainly prevent you from blowing up your rollback segment, I don't think it will speed it up though. If anything it will slow it down some, Jeff?

MH

[marist89]

Originally posted by Mr.Hanky
If anything it will slow it down some, Jeff?

Agree.