Home » RDBMS Server » Backup & Recovery » DB is very slow
| DB is very slow [message #196347] |
Thu, 05 October 2006 01:49  |
M.Shakeel Azeem
Messages: 226
Registered: September 2006
|
Senior Member |
|
|
Dear Sir,
At end of each month closing when users are running reports frequently ,db is very slow
i have confirmed that hit ratios on shared_pool,buffer_pool,db buffer cache are reasonable
Waits events are occuring not too frequently
There is not an issue of locks
Perhaps there is a problem in oracle server configuration because s/w + db are on the same drive and all of datafiles
may i m wrong it is the problem of i/o occuring on the same disk causing bottleneck
Sir can u please guide me what i should do to diagnose the problem
which task i should perform to identify that where is the problem
i am in some trouble
Can u please help me in this regard ?
|
|
|
|
|
|
| Re: DB is very slow [message #196565 is a reply to message #196347] |
Fri, 06 October 2006 00:14  |
M.Shakeel Azeem
Messages: 226
Registered: September 2006
|
Senior Member |
|
|
I run statpack and generate report
I found the some SQL statments causing too many phy.reads
Physical Reads Executions Reads per Exec % Total Hash Value--------------- ------------ -------------- ------- ------------
2,156,814 2 1,078,407.0 84.5 1088530458
SELECT VCC . CODE_VALUE code_value , VCC . ACCOUNTING_DESC , JD
. FC_AMOUNT_DR , JD . FC_AMOUNT_CR , JD . QUANTITY , JD . JRN_DA
TE , JD . PERIOD_DESC , JD . JOURNAL_short_DESC || DECODE ( JD .
jrn_category_short_desc , NULL , NULL , '-' ) || JD . jrn_categ
ory_short_desc JOURNAL_short_DESC , JD . JRN_SOURCE_DESC , JD .
1,601,618 9 177,957.6 62.7 3082450634
SELECT VCC . CODE_VALUE , VCC . CODE_COMBINATION_ID , VCC . ACCO
UNTING_DESC , JD . FC_AMOUNT_DR , JD . FC_AMOUNT_CR , JD . QUANT
ITY , JD . JRN_DATE , JD . PERIOD_DESC , JD . JOURNAL_TINY_DESC
|| DECODE ( JD . JRN_CATEGORY_TINY_DESC , NULL , NULL , '-' ) ||
JD . JRN_CATEGORY_TINY_DESC JOURNAL_SHORT_DESC , JD . JRN_SOURC
60,360 32 1,886.3 2.4 1892271990
SELECT ITEM_CODE, DECODE(ITEM_DESC,NULL,Demand_item_desc,ITEM_DE
SC) ITEM_DESC, ITEM_DESC ITEM_MT_DESC, CODE_VALUE, CODE_DESC, IT
EM_ID, PRI_UOM_ID, PRI_UOM, PRI_QTY, SEC_UOM_ID, SEC_UOM, SEC_QT
Y, Item_Rate, PRI_QTY * Item_Rate TC_EST_PRICE, STAX_GROUP_ID, S
TAX_GROUP_DESC, STAX_RATE, ROUND(NVL(((PRI_QTY * Item_Rate) * ST
13,726 19 722.4 0.5 1740197904
SELECT DEMAND_MT.Demand_Id ,DEMAND_MT.DOC_NO, DEMAND_MT.Ref_No,D
EMAND_MT.DOC_DATE, FUN_ITEM_DESC_IN_LOV(Demand_Mt.Demand_Id,'DEM
AND') SHORT_DESC , to_char(ss.Total_amount) total_amount ,DEMAND
_MT.REMARKS FROM DEMAND_MT,DEMAND_DETAIL,TEMP_INV_BOOKS_CRITERIA
T, ( SELECT DEMAND_MT.Demand_Id,SUM(DEMAND_DETAIL.TC_EST_PRICE*
The problem is that complete sql statments are not displayed in the report
So it is not clear to pinpoint the tables (other objects) involved in these queries
Can u please guide me what i should do next
Should i check to see whether these sql statements are utilizing indexes or not
If it is not then should we go for indexes
by doing this phy.reads are reduced or not
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Wed May 08 01:04:10 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
