The Disk_Reads for a particular update statement is 3071090. This has slowed down my activity. What are the solutions to decrease the Disk_Reads?
This is a large amount of disk reads for your UPDATE statement. When you issue an update statement you can include a WHERE clause, similar to the following:
UPDATE emp SET sal=3000 WHERE empid=1001;Tuning this statement is similar to tuning a similar SELECT statement like:
SELECT * FROM emp WHERE empid=1001;The most common cause of a high number of disk reads is by the statement performing a full table scan (FTS). A FTS means that the SQL statement has to read every single block in the table. Indexes typically reduce the number of reads (but not always), so you might want to investigate employing an index. In my case, I'd create an index on the EMPID column of the EMP table.
Statement tuning is quite a lengthy topic. There is lots to learn and many resources out there. The Oracle documentss touch on this subject. And Guy Harrison has a nice Oracle SQL performance tuning book on the market as well.
For More Information
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our applications, PL/SQL, database administration and data warehousing gurus are waiting to answer your toughest questions.