OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » slow DB server

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

slow DB server [message #133048]

Wed, 17 August 2005 08:33

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

Report message to a moderator

Re: slow DB server [message #133064 is a reply to message #133048]

Wed, 17 August 2005 09:08

Mahesh Rajendran
Messages: 10708
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.

Report message to a moderator

Re: slow DB server [message #133172 is a reply to message #133064]

Wed, 17 August 2005 23:36

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

Report message to a moderator

Re: slow DB server [message #133181 is a reply to message #133172]

Thu, 18 August 2005 00:20

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?

Report message to a moderator

Re: slow DB server [message #133286 is a reply to message #133181]

Thu, 18 August 2005 07:11

Mahesh Rajendran
Messages: 10708
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.

Report message to a moderator

Re: slow DB server [message #134728 is a reply to message #133286]

Fri, 26 August 2005 08:18

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?

Report message to a moderator

Previous Topic:	Optimizer=Choose Error
Next Topic:	clustering vs. reorganization

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 17:53:05 CST 2024