Home » RDBMS Server » Backup & Recovery » DB is very slow
DB is very slow [message #196347] Thu, 05 October 2006 01:49 Go to next message
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 #196361 is a reply to message #196347] Thu, 05 October 2006 02:16 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Generate "STATPACK" report and analyze it.
It is better way to sort ur problem.
Re: DB is very slow [message #196565 is a reply to message #196347] Fri, 06 October 2006 00:14 Go to previous messageGo to next message
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
Re: DB is very slow [message #196616 is a reply to message #196565] Fri, 06 October 2006 04:19 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
A very broad question with very little information.
http://www.orafaq.com/forum/t/51267/42800/

[Updated on: Fri, 06 October 2006 11:54]

Report message to a moderator

Re: DB is very slow [message #197526 is a reply to message #196347] Wed, 11 October 2006 11:26 Go to previous message
aladdha
Messages: 4
Registered: October 2006
Location: Mumbai
Junior Member

Hi,
To View Whole SQL query which is in statspack.....u can see by using Hash value from Statspack and quering to V$SQL or V$SQLTEXT using that hash value.
but i doubt if the queries are part of Batch/procedure whether o/p will be full or not...
Previous Topic: Control file recovery
Next Topic: Restore 10g database
Goto Forum:
  


Current Time: Fri Dec 09 22:53:43 CST 2016

Total time taken to generate the page: 0.10978 seconds