Tune the application SQL in the database

Performance tuning tip #2 from "30 tips in 30 minutes," brought to you by the IOUG.


The following is performance tuning tip #2 from "30 tips in 30 minutes," brought to you by the IOUG. Return to

the main page for more tips on this topic.


The best way to get your database to run faster is to reduce its workload. To reduce the database workload, look at the load profile in the Oracle supplied Statspack utility. You can get a quick synopsis of the work being performed by the database during the two snapshots.

My experience is that the best way to reduce the overall workload on the machine is to reduce the consistent gets. When SQL has to work very hard to find rows in the database, the consistent gets will really pile up. As you tune SQL with indexes, table statistics via analyze and by review SQL is written well, you'll see the consistent gets decrease. Less consistent gets means that SQL is getting its work done with less database reads. Notice the improvement in these two load profiles:

Load profile Per second Per transaction
Redo size: 10,037.40 1,899.28
Logical reads: 23,740.08 6,599.90
Block changes: 58.40 11.05
Physical reads: 444.21 84.05

After tuning the SQL, the database is accomplishing the same amount of work but is doing this work with less than half the logical database reads.

Load profile Per second Per transaction
Redo size: 8,483.57 1,806.05
Logical reads: 11,868.43 2,526.64
Block changes: 49.20 10.47
Physical reads: 1,241.27 264.25

Less database reads can be also achieved by rethinking the work on the application process. If you have a slow response time or a long running SQL job, review the database process to see if you can cut some corners in your processing.


Get more tips in minutes! Return to the main page.

About the author: Kenny Smith has been working with Oracle technology on HP servers for over a decade. He specializes in Oracle database architecture, database administration and development. He has presented at numerous Oracle conferences on two continents. He has published many articles describing Oracle solutions and has co-authored "Oracle backup and recovery 101" from Oracle Press.

IOUG: Become a member of the IOUG to access the paper referenced here and a repository of technical content created for Oracle users by Oracle users.

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close