Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2002
    Posts
    23

    Unanswered: Consistency of results

    Hi,

    As I'm trying to learn more on how to improve somw queries, I have come accross a difficulty in assesing the results of the queries and the improvements on them.

    I ran one query in the test env and it took 9 min. If I run the same query again it takes less than 30 sec. I guess there is a sort of caching done and the second time it gets the results from the cache. But this prevents from understanding if I have a real improvement of the query of is it just the cache.

    For example, now I'm guessing that the statistics are not gathered frequently and I wanted to launch an
    EXEC dbms_stats.gather_table_stats
    but as I cannot quantify the results it will be quite difficult to asses whether there is an improvement.

    The workaround that I apply it to launch other long running queries (that return very many records) and hope that the cache is getting filled in, but , that takes me a long time, and it's not allways working.

    Thank you for your help.
    Jimmyy

    P.S. Unfortunatelly I don't have DBA access but I'm the schema owner. If needed I can ask the DBAs to execute something, but that takes quite some time because of procedures...

  2. #2
    Join Date
    Mar 2002
    Posts
    23
    I have found two ways of clearing the chache:
    1) alter session set events = 'immediate trace name flush_cache';

    2) ALTER SYSTEM FLUSH BUFFER_CACHE;

    First one doesn't work (I assume it clears only the user buffer, and not the SGA, so the queries take very little time) and for the second one I do not have enough prvileges.

    Is there any other way the the second command?

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I suggest the following

    CREATE OR REPLACE PROCEDURE FLUSH_BUFFER_CACHE
    AS
    BEGIN
    ALTER SYSTEM FLUSH BUFFER_CACHE;
    END FLUSH_BUFFER_CACHE;

    have above owned by schema which has needed privilege; say DBADMIN.

    Then do
    GRANT EXECUTE ON DBADMIN.FLUSH_BUFFER_CACHE TO <your_schema>;

    Then you will be able to FLUSH_BUFFER_CACHE any time you desire!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Do it twice.

    Quote Originally Posted by jimmyy View Post
    ...etc...
    Is there any other way the the second command?
    Work-arounds could be:

    1) Execute the query twice before and after changes.
    2) Add a different comment to the query each time you test it.

    These work-arounds would work in 8i, not sure if they will work for 10g.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    This is a perfect example of the benefits of using bind variables

    Anyways, a small way to get around the issue of your testing is to change the variables (slightly) for each run. If you hard-code the variables at execution then usually you will receive a hard-parse for the query and thus get your expected results.

    If you expect the query to be run many times from the app side then using bind variables for your testing and discounting the first run results would be a valid testing procedure.

    ps: use TRACE and TKPROF to determine performance gains/losses
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •