Home » RDBMS Server » Performance Tuning » Help me in tune my database PLZ
Help me in tune my database PLZ [message #130770] Tue, 02 August 2005 07:01 Go to next message
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 #130774 is a reply to message #130770] Tue, 02 August 2005 07:21 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>Help me in tune my database PLZ
What exactly are we looking tune here?
Performance? loading of data? maintainence? security? backup/recovery?
And have not provided the oracle version, OS,your hardware, any specific issues / errors, logs.
Re: Help me in tune my database PLZ [message #130779 is a reply to message #130774] Tue, 02 August 2005 07:52 Go to previous messageGo to next message
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 #130782 is a reply to message #130779] Tue, 02 August 2005 08:06 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> i want to tune my datebase parameters or ... or change my
>>hardware specifications to have a better performance

Based on what?
Without even diagnozing the issue, how can you fix the problem?

>>,the speed of my reports are very slow(about tow hours)
Before doing anything else,
we need to indentify the offending objects/sql.

Your reports use sql. right?
the sqls typically query tables/ indexes.
Are the tables undergo frequent changes (inserts/updates/deletes)?

Do you gather statistics for these tables/indexes frequently?
1. Post the offending sql
2. Make sure you are using good sql coding standards
3. Make sure you have indexed the right columns
4. analyze the tables/indexes.
Ideally you are supposed to use DBMS_STATS.
to start with just use analyze (google / search this forum for more examples)
analyze table table_name compute statistics;
analyze index index_name compute statistics;
5. run your query.
6. enable autotrace/statspack or tracing and post the output.
(FOr doing all these google/ search this forum / refer docs).
-- build the plan table
sql>@$ORACLE_HOME/rdbms/admin/utlxplan
-- enable autotrace.
sql>set autotrace traceonly exp stat
-- run your sql
sql>mysql.sql

post the results


Re: Help me in tune my database PLZ [message #130862 is a reply to message #130779] Tue, 02 August 2005 23:24 Go to previous messageGo to next message
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 Go to previous message
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.
Previous Topic: Application Tuning
Next Topic: tuning SUM reproting statement
Goto Forum:
  


Current Time: Thu Mar 28 16:00:02 CDT 2024