-
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.
Sam
------------------------
To handle yourself, use your head. To handle others, use your heart
-
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
-
You may have lots of indexes created, also check that.
-
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?
Sam
------------------------
To handle yourself, use your head. To handle others, use your heart
-
Make sure your db is tuned; i/o is distributed, no rbs contention, good buffer hit ratio, etc.
Jeff Hunter
-
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,
Sam
------------------------
To handle yourself, use your head. To handle others, use your heart
-
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.
Jeff Hunter
-
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,
Sam
------------------------
To handle yourself, use your head. To handle others, use your heart
-
[[/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
-
Originally posted by Mr.Hanky
If anything it will slow it down some, Jeff?
Agree.
Jeff Hunter
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|