Home » RDBMS Server » Performance Tuning » slow DB server
slow DB server [message #133048] Wed, 17 August 2005 08:33 Go to next message
manu_jariwala
Messages: 20
Registered: August 2005
Location: Surat
Junior Member

Hi
I get some problem in Oracle 8.1.5.0.0.

There is too much problem regarding performance from server.
Its too much slow.

Here some details about it:

Buffer cache hit rate: 83.6277 may need to increase db_block_buffers

chained fetch ratio: 2.5974 PCTFREE
too low for a table

parse/execute ratio: 60.5699 high parse to execute ratio

DBWR avg scan depth: 22074.4000 #DB_FILES too high

Details of INIT.ORA file:-
------------------------------

db_files = 1024 # INITIAL
db_file_multiblock_read_count = 8 # INITIAL
db_block_buffers = 100000
shared_pool_size = 52428800 # INITIAL
java_pool_size = 20971520
log_checkpoint_interval = 10000
log_checkpoint_timeout = 1800
processes = 150 # INITIAL
parallel_max_servers = 5 # SMALL
log_buffer = 32768 # INITIAL
Re: slow DB server [message #133064 is a reply to message #133048] Wed, 17 August 2005 09:08 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10706
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Ignore buffer cache hit ratio.
Identify the sql/application module that is slowing down.
Use bind variables in your sql.
Are you analyzing the tables/indexes regularly? if not do it first.
And upgrade to 10g!. Oracle 8.1.5 is long been depreciated.
Re: slow DB server [message #133172 is a reply to message #133064] Wed, 17 August 2005 23:36 Go to previous messageGo to next message
manu_jariwala
Messages: 20
Registered: August 2005
Location: Surat
Junior Member

Analyzing means what and How?

I think u said right to analyze DB. But how it possible
& what type of steps to conduct?


Is it OK to PCTFREE too low?

And INIT.ORA file is OK?

regards,
MJ
Re: slow DB server [message #133181 is a reply to message #133172] Thu, 18 August 2005 00:20 Go to previous messageGo to next message
manu_jariwala
Messages: 20
Registered: August 2005
Location: Surat
Junior Member

Hi,

Record Delete from the [with index] table. Is it require to rebuild index on that table? So performance can improve?
Re: slow DB server [message #133286 is a reply to message #133181] Thu, 18 August 2005 07:11 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10706
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
So you have never analyzed / gathered statistics before in this database?!!


First Read documentation / any decent book on oracle database administration.
Gather the statistics for tables and indexes ( except for system objects).
This statistics is used by oracle CBO to fetch the data in an efficient manner.
The simplest method to collect statistics is

analyze table emp compute statistics;
analyze index myindex compute statistics;

from 9i and above Oracle strongly recomends to use DBMS_STATS builtin to do the same.

NO need to rebuild the table/index ( IF you do, make sure you gather stats again).
Collect statistics regularly. After every big data change, collect statistics again.

>>is it OK to PCTFREE too low?
I cannot comment on that, without knowledge of your application.
If you are using DMT ( dictionary managed tablespace) make sure
PCTINCREASE=0 and INITIAL=NEXT.
If you are using LMT ( Local Managed Tablespace) make sure you have uniform extents.
Re: slow DB server [message #134728 is a reply to message #133286] Fri, 26 August 2005 08:18 Go to previous message
manu_jariwala
Messages: 20
Registered: August 2005
Location: Surat
Junior Member

chained fetch ratio = 3.0029 PCTFREE too low for a table

parse/execute ratio = 60.0194 high parse to execute ratio

so how can solve this alert?
Previous Topic: Optimizer=Choose Error
Next Topic: clustering vs. reorganization
Goto Forum:
  


Current Time: Tue Dec 06 21:18:25 CST 2022