Home » RDBMS Server » Performance Tuning » testing performance of queries
- testing performance of queries [message #65577] Mon, 01 November 2004 21:31 Go to next message
Milind Deshpande
Messages: 93
Registered: May 2004
Member
Hi,

I would like to compare the performance of 2 queries (namely how long it takes to query large sets of data). Now, when I run the first query, it takes a considerable amount of time to run, but subsequent runs will be instantaneous. I believe this is due to the db caching the results. Then when I run the second query, it would be fast as well.

How do yo test performance when the db uses caching?

ALTER SYSTEM FLUSH BUFFER_CACHE doesnt work. Can SHARED_POOL be used instead of this or there is any other command. The version is Oracle 9.2.0.

Thanks

Milind

Report message to a moderator

- Re: testing performance of queries [message #65580 is a reply to message #65577] Tue, 02 November 2004 01:00 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
You have to keep in mind the way these queries will be run in production environment.
If this query is run on a regular basis, touching and needing data that will be in memory anyway, then testing a clean buffer cache is useless.

If this query runs once a month and needs data that never is touched, then it has to load all data itself.

You see, it all depends. Performance tuning seldom means 'this is always right, that is always wrong'

hth

Report message to a moderator

- Re: testing performance of queries [message #65582 is a reply to message #65577] Tue, 02 November 2004 03:55 Go to previous message
Bruce McCartney
Messages: 7
Registered: September 2004
Junior Member
milind,
you can compare logical IO to see relative performance characteristics without disk access. logical IO is reported in sql_trace data or using sqlplus autotrace

Report message to a moderator

Previous Topic: Oracle Data Block Size
Next Topic: Why not use analyze?
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu May 01 17:17:30 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.