| Low performance - Database [message #197711] |
Thu, 12 October 2006 06:57  |
prashanth_gs
Messages: 67
Registered: November 2005
Location: chennai
|
Member |
|
|
Hello all,
We are facing with the problem like the performance of our database is very slow.
We run the statspack and collected the hit ratios in the peak hour with 1 hour gap. The result is displayed below.
Buffer Nowait %: 100.00
Buffer Hit %: 96.31
Library Hit %: 99.45
Both the Buffer Hit and Library Hit is more than 90%. Still the performance is very less.
We analyzed all the tables in the database.
We created some indexes and partition for some of the tables too.
What else we can do to increase the performance.Where we lag?
Please advice.
DB Details:
db version : 9.2.0.6
OS: solaris 8
Db size: 270 GB
Herewith attaching the statspack report of our database.
Thanks and Regards,
Prashanth
|
|
|
|
| Re: Low performance - Database [message #198485 is a reply to message #197711] |
Tue, 17 October 2006 06:20  |
kimant
Messages: 201
Registered: August 2006
Location: Denmark
|
Senior Member |
|
|
You need to get some details here, like:
WHAT is performing poorly?
Once You know what, You can then answer the next question:
WHY is it performing poorly?
You might find that it is a few selects etc. that are reading and rereading the same data over and over, as it has the wrong execution plan. Then You need to figure out if/how You can change this. Perhaps histograms may help etc. Perhaps a higher sort_area_size.
Usually performance tuning is all about tuning the specific things that are not performing, instead of tuning "the whole database".
Br
Kim
Ps. The stats You mention are a thing of the past. It is very likely that You get a high buffer hit, because of the performance problem (!).
|
|
|
|
| Re: Low performance - Database [message #198721 is a reply to message #197711] |
Wed, 18 October 2006 06:07 |
readytestgo
Messages: 5
Registered: October 2006
Location: Chennai
|
Junior Member |
|
|
1. What type of application are you running? Is it OLTP or OLAP/DSS/DW?
2. How many processors are available in the DB server?
3. LOG BUFFER set to 45MB, which is too high and it will not be utilized fully. This can be reduced.
4. SHARED POOL SIZE was set to 1008 MB, this also too high and only less than 50% of this was found used during the monitoring period. This can be reduced.
5. There are few queries, which are having high number of Buffer-Gets, and Physical-Reads per execution and these are fired either from the application (through thin JDBC client) or from the PL/SQL Developer. Tune these queries to reduce number of buffer-gets/exec or physical-reads/exec.
6. PGA_AGGREGATE_TARGET was set to 1500 MB, this also too high and it can be reduced.
7. The events 'PX Deq Credit: send blkd' and 'PX qref latch' encountered waits and it accounted for 82% of the total wait time. These waits are related to degree of Parallelism. Validate the 'Parallel degree' clause value defined in the tables/hints to reduce these waits.
RTGPerf,
http://groups.google.com/group/database-tuning
|
|
|
|
| Re: Low performance - Database [message #198727 is a reply to message #198721] |
Wed, 18 October 2006 06:28 |
kimant
Messages: 201
Registered: August 2006
Location: Denmark
|
Senior Member |
|
|
Reducing shared pool etc. on the basis of one period is a bit drastic, don't You think ?
If the server has plenty of memory, why not let the database use it? - They *could* use it in other situations....
Again - we need some information about the system, the schema etc. to really comment on the sql and the report.
They really need to go into details with what and why the performance is not as they expect.
They *could* have only 512 MB of RAM in the machine for all I know....
They *could* run 10 other databases on the same server....
They *could* ....
Running statistics *blindly* is not the best starting point, if You have no idea about the state of Your execution plans. If they are perfect, You should not gather statistics.
etc etc etc
Br
Kim
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
