| Help me in tune my database PLZ [message #130770] |
Tue, 02 August 2005 07:01  |
masoud_iesoft
Messages: 3
Registered: August 2005
Location: iran
|
Junior Member |
|
|
I Have large amount of date (about 1,500,000,000)that all of them are necessary and they are in a table white 8 field.(Like ID,A,B,A_Spec,B_Spaec,Start_Time,End_time,Start_date,End date , ...)
I appreciate the help me on following items:
1-whitch hardware do i need?
2-which paramete in init file are more important and should be tuned?
3-in sqlldr my direct option is true.do i need change some other parameters?(my records are in a text file and load to oracle whit sqlldr.each time about 2,000,000 records,are loded.
best regards.
|
|
|
|
|
|
| Re: Help me in tune my database PLZ [message #130779 is a reply to message #130774] |
Tue, 02 August 2005 07:52  |
masoud_iesoft
Messages: 3
Registered: August 2005
Location: iran
|
Junior Member |
|
|
hi
thanks for your attentions.
in fact ,the speed of my reports are very slow(about tow hours).i want to tune my datebase parameters or ... or change my hardware specifications to have a better performance.
Software SPC(oracle 9i,windows 2000 ADVS ,Crystal report 10)
Hardware SPC(CPU Xeon 3.2 Dual,RAM 2 GB,Hard 3 * 300 GB)
yours
masoud
|
|
|
|
|
|
| Re: Help me in tune my database PLZ [message #130862 is a reply to message #130779] |
Tue, 02 August 2005 23:24 |
milind_sri
Messages: 70
Registered: February 2005
Location: Pune
|
Member |
|
|
Hi,
I would suggest after gathering statistics and implementing all your suggested by the forum, concentrate on SQL's written or generated by Crystal reports.
Tuning only the database will not resolve your problem, badly generated SQL or written SQL mainly hampers the performance of reports. Look for conflicting joins and regularly check the explain plan of the SQL's that are executing in the background of reports and tune them.
Tip:- In crystal report there is a facility of creating a formula field which is calculated and then displayed. You can try creating temp tables and store the values in those tables so that the extra overhead for calculation is not required and you can get better performance.
What is the optimiser_mode your are using? I would suggest to use FIRST_ROWS as optimiser_mode as your application looks interactive cause you are generating reports and FIRST_ROWS works better in these scenarios as it concentrates on response time.
HTH
Milind.
|
|
|
|
| Re: Help me in tune my database PLZ [message #131234 is a reply to message #130770] |
Thu, 04 August 2005 22:42 |
joeancell
Messages: 19
Registered: June 2005
|
Junior Member |
|
|
I think in your case large amount of data does not necessary mean you will have a poerformance problem or you will need special hardware to run.
Some suggestions:
1) You may want to consider partition this table based on certain partition key, such as Start_Date
2) The fact you report is slow might be caused by many things, have you check your report provide necessary conditions such as your SQL will use proper index? Are the table statistics and index statistics up to date? Are there any index used at all? Do you try to bring all these data to your report?
You can consider to grab your SQL statement, replace it with the condition your report will provide and run it through SQL*PLUS or other tools such as AgileInfoSoftware (http://www.agileinfollc.com) DataStudio to check the execution plan, and fix whatever issue indicated in the execution plan.
If your SQL cxan run reasonably fast in the raw query tool, then put it back to your report an dgive it a try.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
