Option on StatsPack report (Is this Reading from DB to much?)

[GARYM]

The following is segment of a statspack report from a test app (the full results are in the attached file).

Does this look like the problem has to do with DB I/O. For extra info running 8.7.1.4 on W2K. The report was run around a Crystal Report generation.

Cache Sizes
~~~~~~~~~~~
db_block_buffers: 5000 log_buffer: 1048576
db_block_size: 16384 shared_pool_size: 350M

Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 77.04 235,676.00
Logical reads: 3,053.77 9,341,468.00
Block changes: 0.05 165.00
Physical reads: 83.36 255,012.00
Physical writes: 2.18 6,657.00
User calls: 15.08 46,124.00
Parses: 8.28 25,338.00
Hard parses: 4.95 15,139.00
Sorts: 22.91 70,096.00
Logons: 0.00 0.00
Executes: 805.21 2,463,134.00
Transactions: 0.00

% Blocks changed per Read: 0.00 Recursive Call %: 99.27
Rollback per transaction %: 0.00 Rows per Sort: 14.15

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 97.27 In-memory Sort %: 99.99
Library Hit %: 98.90 Soft Parse %: 40.25
Execute to Parse %: 98.97 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 99.18 % Non-Parse CPU: 89.23

Shared Pool Statistics Begin End
------ ------
Memory Usage %: 94.82 94.87
% SQL with executions>1: 1.89 1.20
% Memory for SQL w/exec>1: 1.14 0.42

Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total
Event Waits Time (cs) Wt Time
-------------------------------------------- ------------ ------------ -------
db file scattered read 30,965 11,480 76.30
direct path read 950 2,784 18.50
direct path write 604 479 3.18
control file parallel write 994 204 1.36
latch free 11 56 .37
-------------------------------------------------------------
Wait Events for DB: DBAS Instance: dbas Snaps: 17 -18
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> ordered by wait time desc, waits desc (idle events last)

Avg
Total Wait wait Waits
Event Waits Timeouts Time (cs) (ms) /txn
---------------------------- ------------ ---------- ----------- ------ ------
db file scattered read 30,965 0 11,480 4 ######
direct path read 950 0 2,784 29 950.0
direct path write 604 0 479 8 604.0
control file parallel write 994 0 204 2 994.0
latch free 11 9 56 51 11.0
db file sequential read 41 0 17 4 41.0

Gary

[marist89]

From a 2 second glance, it looks like an awful lot of scattered reads (ie. Full Table Scans). Check that your query is using the available indexes...

[GARYM]

I was looking at the same thing. db file scattered reads. This is a Crystal Report and I am not sure we can place hints into the generated query (built by Crystal) and have the thing still work.

Has anyone looked at this with Crystal before?

Gary

[marist89]

I used to work with Crystal a long time ago. Our fix for problems like this involved creating views that had the hints embedded in the CREATE VIEW statement...