Q
Problem solve Get help with specific problems with your technologies, process and projects.

Reducing sequential reads

I've got the following output. Can you explain how I can tune it?

I've got the following output. Can you explain how I can tune the following?

EVENT
SUM(SE.TOTAL_WAITS) SUM(SE.TOTAL_TIMEOUTS) TIME_WAITED
----------------------------------------------------------------
------------------- ---------------
db file scattered read
16333122 0 2996.17
db file sequential read
5710782 0 431.66
SQL*Net message to client
866243 0 1.48
SQL*Net message from client
866056 0 863529.38
SQL*Net more data to client
105196 0 6.52
pipe get
38586 38308 158864.25
log file sync
8452 125 389.57
queue messages
5484 5379 51814.78
SQL*Net break/reset to client
5106 0 3.6
SQL*Net more data from client
2253 0 59.45
buffer busy waits
1620 0 3.06
latch free
1201 437 5.71
direct path write (lob)
182 0 .24
direct path read (lob)
133 0 .08
library cache load lock
126 11 37.71
library cache pin
126 5 15.42
control file sequential read
115 0 0
log file switch completion
39 2 5.21
undo segment extension
35 35 0
row cache lock
20 0 .11
enqueue
18 0 6.5
buffer deadlock
2 2 0
library cache lock
1 0 0
The results look like you may be IO bound. Check your code for full table scans and if they are not needed, fix them. They are causing the scattered reads. Make sure you have enough memory to cache a working set of your data and that may help reduce the sequential reads. However, this does not really tell enough to analyze what is going on. I assume this is just a select against the events table which is a cumulative statistics table. Look at using either statspack (8i, 9i) or awrrpt (10g) to gather a set of snapshots of valid statistics while you run your problem queries/application. The statspack report will give you a better idea of bad SQL and if your memory configuration is proper.

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close