| Database performance issue [message #127696] |
Tue, 12 July 2005 23:04  |
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  |
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 #127892 is a reply to message #127824] |
Wed, 13 July 2005 23:53 |
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 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
