OraFAQ Forum: Performance Tuning » Entries are getting very slow.

Home » RDBMS Server » Performance Tuning » Entries are getting very slow.

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

Entries are getting very slow. [message #289977]

Thu, 27 December 2007 00:10

ksaravin
Messages: 2
Registered: December 2007
Location: India

Junior Member

Dear All,

My users are complaining that their entries are getting very slow. Here with I have attached one day transaction details captured through statspack. Any one can suggest how to fine tune and improve the performance highly appreciated.

Saravanan.K

Attachment: 3861261207.LST
(Size: 63.59KB, Downloaded 1119 times)

Report message to a moderator

Re: Entries are getting very slow. [message #290002 is a reply to message #289977]

Thu, 27 December 2007 01:35

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

If you want a statspack report to be uselful you have to first set timed_statistics to true.
Then you have to take a snapshot every 1/2 hour, a snapshot on 1 day is useless.

Nevertheless you have the heaviest queries, investigate on them.
But you have to know that you must first optimize the application before trying to optimize the queries and finally the database.

Regards
Michel

[Updated on: Thu, 27 December 2007 01:37]

Report message to a moderator

Re: Entries are getting very slow. [message #290093 is a reply to message #289977]

Thu, 27 December 2007 06:40

michael_bialik
Messages: 621
Registered: July 2006

Senior Member

Just for starter:

SELECT SUM(QTYACCT) WT,SUM(LANDCOST)
FROM STORES.PLANTRECEIPTS
WHERE ITEMCODE = :b1  AND
  COMPCODE || UNITCODE  || PLANT  || ITEMCODE  ||
  RECDOC  || BATCHNO  = :b2 || :b3  || :b1  || :b5  || :b6
GROUP BY COMPCODE,UNITCODE,PLANT,ITEMCODE,RECDOC,BATCHNO

1. IMHO there is a BUG:
Suppose you have 2 sets of values:
COMPCODE || UNITCODE || PLANT || ITEMCODE ||
RECDOC || BATCHNO = '1111' || '111' || ...

and

COMPCODE || UNITCODE || PLANT || ITEMCODE ||
RECDOC || BATCHNO = '111' || '1111' || ...

in both cases you will get the same answer.

I recommend to split the WHERE ( anyway the number of parameters does not correspond to the number of concatenated columns) and to rewrite the statement as:

SELECT SUM(QTYACCT) WT,SUM(LANDCOST)
FROM STORES.PLANTRECEIPTS
WHERE ITEMCODE = :b1  AND
  COMPCODE = :p1 AND UNITCODE = :p2 AND PLANT = :p3 AND ITEMCODE = :b1 AND RECDOC = :b5 AND BATCHNO  = :b6
GROUP BY COMPCODE,UNITCODE,PLANT,ITEMCODE,RECDOC,BATCHNO

The statement performs too much IO, so try and define an index(after rewriting the statement):

CREATE INDEX ... ON STORES.PLANTRECEIPTS (
   ITEMCODE, COMPCODE, UNITCODE, PLANT, RECDOC, BATCHNO ) ...

Michael

Report message to a moderator

Re: Entries are getting very slow. [message #290225 is a reply to message #290093]

Fri, 28 December 2007 01:35

ksaravin
Messages: 2
Registered: December 2007
Location: India

Junior Member

Dear Michel,

Thanks for your suggestion. Here I have attached another stats pack report for every 1/2 hour with time_statistics.

Buffer Gets Executions Gets per Exec % Total Hash Value
--------------- ------------ -------------- ------- ------------
219,668,136 1,616,675 135.9 81.1 1130711434

SELECT FLAG FROM COMPANY WHERE CODE = :b1

This query is taking more buffer gets is it ok.

Please suggest me to improve the performance.

Regards,

Saravanan.K

Attachment: 150-161.LST
(Size: 59.48KB, Downloaded 1209 times)

Report message to a moderator

Re: Entries are getting very slow. [message #290228 is a reply to message #290225]

Fri, 28 December 2007 01:51

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

I didn't read the statspack report but the first question I would ask is: "is it necessary that the application calls 1,616,675 this statement?"

As I said:

Quote:

you must first optimize the application before trying to optimize the queries and finally the database

Regards
Michel

Report message to a moderator

Re: Entries are getting very slow. [message #290392 is a reply to message #289977]

Fri, 28 December 2007 12:25

michael_bialik
Messages: 621
Registered: July 2006

Senior Member

As Michel mentioned, you probable missing some JOIN and performing SELECT inside cursor.
1. Use JOIN.
2. You probably missing a lot of indexes in you application. That may account for slowness.

Michael

Report message to a moderator

Previous Topic:	EXECUTION TIME
Next Topic:	can collections help

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jun 28 10:14:41 CDT 2024