Hi all,
possible someone could help me on following issue:
I'm working for a software vendor and one of our customers is reporting that especially 2 of the sql statements of our application are " executed ineffective" on their database environment.
They are especially saying that "These statements are consuming a lot of CPU and doing a lot of buffer gets in relation to the number of executions."
They provided following extracts out of the statspack report.
SQL1:
SQL Statistics
~~~~~~~~~~~~~~
-> CPU and Elapsed Time are in seconds (s) for Statement Total and in
milliseconds (ms) for Per Execute
% Snap
Statement Total Per Execute Total
--------------- --------------- ------
Buffer Gets: 322,101 16.6 .89
Disk Reads: 631 0.0 .48
Rows processed: 19,444 1.0
CPU Time(s/ms): 19 1.0
Elapsed Time(s/ms): 26 1.3
Sorts: 0 .0
Parse Calls: -2 -.0
Invalidations: 0
Version count: 1
Sharable Mem(K): 43
Executions: 19,444
SQL2:
SQL Statistics
~~~~~~~~~~~~~~
-> CPU and Elapsed Time are in seconds (s) for Statement Total and in
milliseconds (ms) for Per Execute
% Snap
Statement Total Per Execute Total
--------------- --------------- ------
Buffer Gets: 628,517 22.9 3.26
Disk Reads: 128 0.0 .18
Rows processed: 27,492 1.0
CPU Time(s/ms): 27 1.0
Elapsed Time(s/ms): 30 1.1
Sorts: 0 .0
Parse Calls: 0 .0
Invalidations: 0
Version count: 1
Sharable Mem(K): 39
Executions: 27,492
The SQL1 is an update and SQL2 an Insert on the same table.
The accessed table has 6 indexes and a primary key column. On SQL1, the update, the where condition is reffering to the primary key column.
Both statements are using bind variables.
From my point, I would say, that the customer should provide the execution plan of both statements to verify that SQL1 is using the primary key.
As far as I understand "buffer gets" this issn't an issue because it's only saying that the data coumes out of the cache (which would be good) instead of reading the data from disk.
But I don't really see there any bottleneck.
Could you please give me some suggestions?
Many Thanks
Joerg