- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: sql loader performance
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-16-2003 12:04 AM
тАО07-16-2003 12:04 AM
Re: sql loader performance
Regards,
Tom Geudens
P.S. You might want to take this up with your management / endusers. We did this and introduced SLA's (Service Level Agreements) that allow both sides (not only the enduser-side) to have a say in how things should run.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-16-2003 01:25 AM
тАО07-16-2003 01:25 AM
Re: sql loader performance
There was no performance improvement after altering the indexes to UNUSABLE! So what I was drop the indexes and the performance is vastly improved.....Will see how long it takes to load an hours worth of data & time taken to create the index.
Thank!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-16-2003 02:41 AM
тАО07-16-2003 02:41 AM
Re: sql loader performance
Concerning the "rebuilding" of indexes:
Note that you can speed up the index rebuild/create via the "parallel" option (In an archive log mode database, the nologging option)
I would not drop the indexes though! Better set them unusable: alter index t_idx2 unusable;
Benchmark to see for yourself..
If I use rebuild (meaning i set them unusable), I cannot lose them. I cannot accidently have one "go missing". People will call and say "hey, I'm getting a strange error about index such and such being unusable -- whats up with that".
If I use DROP / CREATE -- what will happen if the DROP succeeds, but the create fails for whatever reason (script bombs out, out of temp, whatever). Index has gone missing -- no one checks the script log -- and then you spend the next day or two trying to figure out why performance has gone down the tubes ("oh they say the next day, we 'lost' an index").
So, if you use ususable/rebuild -- you'll not lose an index, you'll be told your script bombed, you fix it and the system runs smoothing (except for that minor annoyed person who hit your error)
If you use drop/create -- you'll lose an index someday (maybe many days). You'll not be told your script bombed. Your users will just experience slower response times (if they get the answer back at all), you'll be flooded with calls about "slowness" and then hopefully you can actually track down the index that has gone missing this time.
You may also consider:
- increasing the sort_area_size if not already.
- increasing db_file_multi_block_read_count, if not at OS limits already.
- make sure the data you are reading is spread out across multiple devices (else you might just be introducing massive disk contention).
- speed up your disk access -- db file scattered read is sequential IO (believe it or not).
hope this helps too!
regards
Yogeeraj
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-16-2003 04:52 AM
тАО07-16-2003 04:52 AM
Re: sql loader performance
Another idea could be like this.
Create a smaller database independent from
your production.
Load the data into it. At this point you can do some checks and correct errors.
Then insert the data into production by using
a sql-script.
Rgds
Alexander M. Ermes
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-16-2003 07:52 PM
тАО07-16-2003 07:52 PM
Re: sql loader performance
Yogeeraj,
You will want to be careful with db_multiblock_read_count for data warehouses. We had a large SAP system that had horrible performance after a migration from Informix. We dropped this from 32 to 12, and performance improved considerably. What I didn't realize, is that this parameter deals with the amount of data that can be accessed at a given time, however it has the added effect of forcing the CBO to lower the cost of FTS as well, espcially for small tables. We had always treated it as a "more is better" setting, which probably had detrimental effects in some cases.
Thanks,
Brian
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-16-2003 10:29 PM
тАО07-16-2003 10:29 PM
Re: sql loader performance
I attempted disabling the indexes and loading but there was no improvement in the load. How do you suggest I disable&enable the indexes in a script(syntax),because say I have 3 months worth of data and there are daily partitions with indexes?
Thanks in advance!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-17-2003 02:58 AM
тАО07-17-2003 02:58 AM
Re: sql loader performance
Brian is right! be cautious with the db_multiblock_read_count ...
As for syntax:
If you had a table created as:
create table p (i integer)
partition by range(i)
(partition p1 values less than (10),
partition p2 values less than (20),
partition p3 values less than (maxvalue));
create index l on p(i) local;
alter index l modify partition p1 unusable;
then:
alter index l modify partition p1 unusable;
alter index l rebuild nologging parallel 6;
Are you using "locally partitioned indexes" or "globally partitioned indexes"? could that be one of the sources of the "problem"?
regards
Yogeeraj
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-17-2003 04:12 AM
тАО07-17-2003 04:12 AM
Re: sql loader performance
Thanks for the pointers. The partitions are created as locally. I will test the unusable index suggestion and get back to you.
Regards!
- « Previous
-
- 1
- 2
- Next »