Overall Performance--still poor

[engiri]

One of our production server,
with below details runs fine with 100 or less users.
When more than 200 users come,
we feel a lot of buffer busy waits, when any one of the table
goes in db_file_sequential_read for more than 5 seconds.

And also reading in undo is very poor.

Attaced a statspack report for your reference.

Os level, the datafiles are with RAID 5 (Though I told the sysadmin
that raid 5 will not suit, he told this will not be a big problem).
Redo log file s are in RAID 1.
SOlaris 8.
Oracle 9i.

Thanks for all the help provided.

Regards

[pando]

You have some nasty SQL statements, massive buffer gets which leads to buffer busy waits

[hrishy]

Hi

Whats your parameter for optmizer_index_adj_cost

what does the plan for the following statement look like

[code]

UPDATE SA_TEMP_IRR_WORKING SET CURRENT_BALANCE = :B5 , PREVIOUS_
BALANCE = :B4 , CHARGES = :B3 WHERE INFLOW_OUTFLOW = :B2 AND DOC
UMENT_DATE = :B1


SELECT REFERENCE_VALUE FROM SA_CONSTANT_REFERENCE_DIR WHERE REFE
RENCE_NAME = :B1

SELECT DISTINCT SYADPKG00004.DF_V_GET_STRING_POSITION(AMD_ASSET_
LEVEL_STRING,ADE_ASSET_LEVEL_NO) FROM SA_ASSET_DEFINITION_DETAIL
S, SA_ASSET_MASTER_DETAILS WHERE ADE_ASSET_DEF_CODE = AMD_ASSET_
DEF_CODE AND AMD_ASSET_MASTER_CODE = :B1 AND ADE_ASSET_ATTRIB_CO
DE = DF_V_CONST_REF_DIR('CAP_ASSET_CLASS_ATTRIB_CODE')

is the distinct necessary ?

regards
Hrishy

[pando]

forget

UPDATE SA_TEMP_IRR_WORKING SET CURRENT_BALANCE = :B5 , PREVIOUS_
BALANCE = :B4 , CHARGES = :B3 WHERE INFLOW_OUTFLOW = :B2 AND DOC
UMENT_DATE = :B1


SELECT REFERENCE_VALUE FROM SA_CONSTANT_REFERENCE_DIR WHERE REFE
RENCE_NAME = :B1

those are good enough, we are not gonna question SQL with 2 or 3 buffer gets atm!

[pando]

I have a question, why you disable smon transaction recovery with the event?

[engiri]

Thanks for your valuable time and analysis.

Pando I think you are referring
10513 trace name context forever

I asked my collegue who is incharge of this database, (On leave),
it is due to one request from oracle during their analysis.

After that he forgot to unset that.

I will unset that tonight.

Regards

[engiri]

After unsetting the value shutdown of the database hangs
and alerts keep on going with MARKING UNDO XX AS AVAILABLE
AND MARKING XXX AS AVAILABLE... AND SO ON.

What could be the reason.

We have to raise a tar.

What could be the reason..??

Are we going suffering due to any bugs.?

Regards