4

I want to know the way to test right sqls before tuned and after tuned. but once I executed the original sql, I got results too fast for tuned sql.

I found below...

How to clear all cached items in Oracle

I did flush data buffer cache and shared pool but it still didn't work. I guess this answer from that question is related to what I want to know more: Keep in mind that the operating system and hardware also do caching which can skew your results.

Oracle's version is 11g and Server is HP-UX 11.31.

If the server was Linux, I could've tried clearing buffer using '/proc/sys/vm/drop_caches'.(I'm not sure it would works)

I'm searching quite long time for this problem. Is there anyone has this kind of problem?

thanks

Community
  • 1
  • 1
seeun
  • 41
  • 3
  • Try querying the V$BH view before and after the flush to check the objects have been flushed. If there is another session query the same blocks you find find them "sticky". "SELECT o.object_name, COUNT(*) number_of_blocks FROM DBA_OBJECTS o, V$BH bh WHERE o.data_object_id = bh.OBJD AND o.owner != 'SYS' GROUP BY o.object_Name ORDER BY COUNT(*);" – TenG Jun 02 '16 at 16:07

1 Answers1

0

If your query is such that the results are being cached in the file system, which your description would suggest, then the query is probably not a "heavy-hitter" overall. But if you were testing in isolation, with not much activity on the database, when the SQL is run in a production environment performance could suffer.

There are several things you can do to determine which version of two queries is better. In fact, entire books have been written on just this topic. But to summarize:

Before you begin, ensure statistics on the tables and indexes are up to date.

  • See how often the SQL will be executed in the grand scheme of things. If it runs once or twice a day, and takes 2 seconds to run, don't bother trying to tune.
  • Do a explain plan on both and look at the estimated costs and number of steps.
  • Turn on tracing for both optimizer steps and execution statistics, and compare.
Mark Stewart
  • 2,046
  • 4
  • 22
  • 32