Home » RDBMS Server » Performance Tuning » Entries are getting very slow.
Entries are getting very slow. [message #289977] Thu, 27 December 2007 00:10 Go to next message
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
Re: Entries are getting very slow. [message #290002 is a reply to message #289977] Thu, 27 December 2007 01:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
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 Go to previous messageGo to next message
michael_bialik
Messages: 611
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
Re: Entries are getting very slow. [message #290225 is a reply to message #290093] Fri, 28 December 2007 01:35 Go to previous messageGo to next message
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 236 times)
Re: Entries are getting very slow. [message #290228 is a reply to message #290225] Fri, 28 December 2007 01:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
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
Re: Entries are getting very slow. [message #290392 is a reply to message #289977] Fri, 28 December 2007 12:25 Go to previous message
michael_bialik
Messages: 611
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
Previous Topic: EXECUTION TIME
Next Topic: can collections help
Goto Forum:
  


Current Time: Fri Dec 09 15:22:28 CST 2016

Total time taken to generate the page: 0.31555 seconds