Home » RDBMS Server » Performance Tuning » Database performance issue
Database performance issue [message #127696] Tue, 12 July 2005 23:04 Go to next message
Hina
Messages: 51
Registered: April 2004
Member
Hi xperts,


My firm want me to diagnose an oracle application performance issue and tune afterwards.

I have no enough knowledge on oracle performance and tuning, so could you plz provide some scripts to gather all statistics and information.

e.g CPU utilization, memory utilization, SQL parse, hit ratio etc.

Your help will be appreciated, thanks in advance

database oracle 9i
os Solars


Re: Database performance issue [message #127711 is a reply to message #127696] Wed, 13 July 2005 00:18 Go to previous messageGo to next message
milind_sri
Messages: 70
Registered: February 2005
Location: Pune
Member

Hi,

Oracle performance tuning is a practical phenomena.

I would suggest you implement Statspack tool for gathering statistics of your database which will provide you with everything and it is the best tool as per my opinion.

And, also ensure that statistics of tables database are duly updated for improving performance. You can use DBMS_STATS package for gathering statistics.

Check your SQL's that they are using proper indexes and if required build indexes and check their response.

If your application is interactive type then you can try with FIRST_ROWS optimizer mode which concentrated more on response time.

There are too many things involved in Performance tuning but you have to check which approach fits better.

All the best.

Milind.

Re: Database performance issue [message #127824 is a reply to message #127711] Wed, 13 July 2005 09:51 Go to previous messageGo to next message
Hina
Messages: 51
Registered: April 2004
Member
Thanks Milind,

Can you please provide me a link or steps, so I can install/configure statspack tool.

Thanks
Re: Database performance issue [message #127849 is a reply to message #127696] Wed, 13 July 2005 11:47 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Should be in the performance tuning guide of the standard oracle documentation.
Re: Database performance issue [message #127892 is a reply to message #127824] Wed, 13 July 2005 23:53 Go to previous messageGo to next message
milind_sri
Messages: 70
Registered: February 2005
Location: Pune
Member

Hi,

I am providing you the generic steps for the implementation of Statspack for your database.

1. Create a permanant locally managed tablespace for Statspack. You can use an exisiting tablespace also.

2. You have to specify the temporary tablespace also when prompted.

3. Execute SPCREATE.sql (If during execution you encounter any errors then execute SPDROP.sql and re-execute SPCREATE.sql)

4. For generating the report you have to execute SPREPORT.sql. Before executing SPREPORT.sql execute PL/SQL procedure STATSPACK.snap at different intervals which will generate various SNAP id's like 1,2,3 and so on.

4. Once you have executed STATSPACK.snap when you will execute SPREPORT.sql it will ask you for start snap id and end snap id.

5. Give the start and end snap id and your report will be generated in .lst format. You can open it with notepad and scrutinise the performance bottlenecks of your database like Wait events etc.

You can also try UTLBSTAT and UTLESTAT scripts also which generates Report.txt but Statspack report I believe gives a better idea and output.

The scripts that I have mentioned you can find ORACLE_HOME/Ora92/rdbms/admin folder.

I hope this gives you a better idea how to implement statspack.

Milind.
Re: Database performance issue [message #127893 is a reply to message #127892] Thu, 14 July 2005 00:14 Go to previous message
milind_sri
Messages: 70
Registered: February 2005
Location: Pune
Member

Hi,

I forgot to mention in the previous post that Perfstat user will get created and you have to specify the password for it.

You have to connect to perfstat user in order to generate statspack report.

Milind.
Previous Topic: using statspack .......!
Next Topic: functional desing advise needed
Goto Forum:
  


Current Time: Sun Sep 20 15:53:26 CDT 2020