How to disable cache/buffer when running the same
A user saw a query take ~10 minutes on first run but only ~1 minute on repeats, assumed IDS was caching the result set, and asked how to turn that off so he could time tuning changes fairly. Respondents clarified that IDS caches data and index pages, not result sets, and that there's no simple way to disable it. Suggested workarounds: always compare second-run times, start IDS with a very small buffer pool, run a huge query on other tables to flush the cache, or bounce the instance — while noting OS and disk-subsystem caching also interferes, so using SET EXPLAIN/xtree to study the plan is more useful.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello , When I run a query , for the 1st time the query will take xx minute (ex. 10 minute) but if I run this query again , it will take only y minute (ex. 1 minute). I think IDS keep the result set in buffer/cache . When I run again , IDS map this query to the buffer/cache and retrieve the pre-run result set to me. My question is "how to disable this feature or to disable buffer/cache" ? Because I 'd like to tune query and want to get the actual time using by this query (after changing something such as index, statistics) but I cannot get the actually time if I run this query again (not first time). Best regards, Jakkrit A.
Hi, IDS doesn't cache a result set but it caches the data/index pages needed to create the result (if the buffer cache is reasonable large). If you run a query for the first time (and no other query has requested that data too) IDS has to read the tables/indexes from disk. The following query executions will find all or some of the needed tables/indexes in the buffer cache (memory). There are two solutions: 1) Run all of your test queries at least two times and compare only the second run times. 2) Start IDS with a very small buffer cache. Regards, Andreas ------------------------------------------- SPAR Österreichische Warenhandels-AG Hauptzentrale A - 5015 Salzburg, Europastrasse 3 FN 34170 a Tel: +43 662 4470 24223 Mobile: +43 664 6259575 E-Mail: Andreas.KUTSCHE@spar.at Internet: http://www.spar.at Wichtiger Hinweis: Der Inhalt dieser E-Mail kann vertrauliche und rechtlich geschützte Informationen, insbesondere Betriebs- oder Geschäftsgeheimnisse, enthalten, zu deren Geheimhaltung der Empfänger verpflichtet ist. Die Informationen in dieser E-Mail sind ausschließlich für den Adressaten bestimmt. Sollten Sie die E-Mail irrtümlich erhalten haben so ersuchen wir Sie, die Nachricht von Ihrem System zu löschen und sich mit uns in Verbindung zu setzen. Über das Internet versandte E-Mails können leicht manipuliert oder unter fremdem Namen erstellt werden. Daher schließen wir die rechtliche Verbindlichkeit der in dieser Nachricht enthaltenen Informationen aus. Der Inhalt der E-Mail ist nur rechtsverbindlich, wenn er von uns schriftlich bestätigt und gezeichnet wird. Sollte trotz der von uns verwendeten Virus-Schutzprogramme durch die Zusendung von E-Mails ein Virus in Ihre Systeme gelangen, haften wir nicht für evtl. hieraus entstehende Schäden. Wir danken für Ihr Verständnis. Important notice: The contents of this e-mail may contain confidential and legally protected information that is in particular related to operational and trade secrets, which the recipient is obliged to treat as confidential. The information in this e-mail is made available exclusively for use by the addressee. In the event that the e-mail may have been sent to you in error, we would ask you to kindly delete this communication from your system and to contact us. E-mails sent via the Internet can be easily manipulated or sent out under someone else's name. We therefore do not accept legal liability for the information contained in this communication. The contents of the e-mail are only legally binding if they have been confirmed and signed by us in writing. If, in spite of our using Antivirus protection software, a virus may have penetrated your system through the sending of this e-mail, we do not accept liability for any damage that may possibly arise as a result of this. We trust that you appreciate our position. ------------------------------------------- -----Ursprüngliche Nachricht----- Von: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Im Auftrag von jak aka Gesendet: Mittwoch, 8. April 2009 11:19 An: ids@iiug.org Betreff: How to disable cache/buffer when running the s.... [15478] Hello , When I run a query , for the 1st time the query will take xx minute (ex. 10 minute) but if I run this query again , it will take only y minute (ex. 1 minute). I think IDS keep the result set in buffer/cache . When I run again , IDS map this query to the buffer/cache and retrieve the pre-run result set to me. My question is "how to disable this feature or to disable buffer/cache" ? Because I 'd like to tune query and want to get the actual time using by this query (after changing something such as index, statistics) but I cannot get the actually time if I run this query again (not first time). Best regards, Jakkrit A. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Hi, This is quite difficult to do. True, ids does cache your data, and you can reduce the amount of information that can be cached by playing with your BUFFERPOOL configuration and read ahead configuration. However, I wouldn't recommend it. This can affect how much data is cached by IDS, but what about data cached by your OS? What about data cached by your disk sub system? Rather run the query twice as Anreas mentioned to cache the data, then run the query to gather performance. Use tools like 'set explain' and xtree to see what the query is doing. Look at the joins and filters etc -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of jak aka Sent: 08 April 2009 11:19 AM To: ids@iiug.org Subject: How to disable cache/buffer when running the s.... [15478] Hello , When I run a query , for the 1st time the query will take xx minute (ex. 10 minute) but if I run this query again , it will take only y minute (ex. 1 minute). I think IDS keep the result set in buffer/cache . When I run again , IDS map this query to the buffer/cache and retrieve the pre-run result set to me. My question is "how to disable this feature or to disable buffer/cache" ? Because I 'd like to tune query and want to get the actual time using by this query (after changing something such as index, statistics) but I cannot get the actually time if I run this query again (not first time). Best regards, Jakkrit A. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. ================== Please read our Email Disclaimer : http://www.thefuelgroup.com/disclaimer.html
To add a third solution: Run a MASSIVE select on other tables before your test query - that way the buffer cache will fill with other tables' data first. And your query will be facing the same up-hill battle every time you run it! -ScottM -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Andreas.KUTSCHE@spar.at Sent: Wednesday, April 08, 2009 3:28 AM To: ids@iiug.org Subject: AW: How to disable cache/buffer when running t.... [15479] Hi, IDS doesn't cache a result set but it caches the data/index pages needed to create the result (if the buffer cache is reasonable large). If you run a query for the first time (and no other query has requested that data too) IDS has to read the tables/indexes from disk. The following query executions will find all or some of the needed tables/indexes in the buffer cache (memory). There are two solutions: 1) Run all of your test queries at least two times and compare only the second run times. 2) Start IDS with a very small buffer cache. Regards, Andreas ------------------------------------------- SPAR Österreichische Warenhandels-AG Hauptzentrale A - 5015 Salzburg, Europastrasse 3 FN 34170 a Tel: +43 662 4470 24223 Mobile: +43 664 6259575 E-Mail: Andreas.KUTSCHE@spar.at Internet: http://www.spar.at Wichtiger Hinweis: Der Inhalt dieser E-Mail kann vertrauliche und rechtlich geschützte Informationen, insbesondere Betriebs- oder Geschäftsgeheimnisse, enthalten, zu deren Geheimhaltung der Empfänger verpflichtet ist. Die Informationen in dieser E-Mail sind ausschließlich für den Adressaten bestimmt. Sollten Sie die E-Mail irrtümlich erhalten haben so ersuchen wir Sie, die Nachricht von Ihrem System zu löschen und sich mit uns in Verbindung zu setzen. Über das Internet versandte E-Mails können leicht manipuliert oder unter fremdem Namen erstellt werden. Daher schließen wir die rechtliche Verbindlichkeit der in dieser Nachricht enthaltenen Informationen aus. Der Inhalt der E-Mail ist nur rechtsverbindlich, wenn er von uns schriftlich bestätigt und gezeichnet wird. Sollte trotz der von uns verwendeten Virus-Schutzprogramme durch die Zusendung von E-Mails ein Virus in Ihre Systeme gelangen, haften wir nicht für evtl. hieraus entstehende Schäden. Wir danken für Ihr Verständnis. Important notice: The contents of this e-mail may contain confidential and legally protected information that is in particular related to operational and trade secrets, which the recipient is obliged to treat as confidential. The information in this e-mail is made available exclusively for use by the addressee. In the event that the e-mail may have been sent to you in error, we would ask you to kindly delete this communication from your system and to contact us. E-mails sent via the Internet can be easily manipulated or sent out under someone else's name. We therefore do not accept legal liability for the information contained in this communication. The contents of the e-mail are only legally binding if they have been confirmed and signed by us in writing. If, in spite of our using Antivirus protection software, a virus may have penetrated your system through the sending of this e-mail, we do not accept liability for any damage that may possibly arise as a result of this. We trust that you appreciate our position. ------------------------------------------- -----Ursprüngliche Nachricht----- Von: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Im Auftrag von jak aka Gesendet: Mittwoch, 8. April 2009 11:19 An: ids@iiug.org Betreff: How to disable cache/buffer when running the s.... [15478] Hello , When I run a query , for the 1st time the query will take xx minute (ex. 10 minute) but if I run this query again , it will take only y minute (ex. 1 minute). I think IDS keep the result set in buffer/cache . When I run again , IDS map this query to the buffer/cache and retrieve the pre-run result set to me. My question is "how to disable this feature or to disable buffer/cache" ? Because I 'd like to tune query and want to get the actual time using by this query (after changing something such as index, statistics) but I cannot get the actually time if I run this query again (not first time). Best regards, Jakkrit A. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
IDS does not cache the query results, but it does cache the data and index pages that were accessed to fulfill the query so that it is not necessary to perform the physical IOs again. This is the difference in the timings. There is no easy way to force a flush of the data cache exept to shutdown and restart the instance or to process another query accessing different data that is large enough to force the existing data out of the cache. Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Apr 8, 2009 at 5:18 AM, jak aka <jakkritakeng@yahoo.com> wrote: > Hello , > > When I run a query , for the 1st time the query will take xx minute (ex. 10 > minute) but if I run this query again , it will take only y minute (ex. 1 > minute). I think IDS keep the result set in buffer/cache . When I run again > , > IDS map this query to the buffer/cache and retrieve the pre-run result set > to > me. > > My question is "how to disable this feature or to disable buffer/cache" ? > > Because I 'd like to tune query and want to get the actual time using by > this > query (after changing something such as index, statistics) but I cannot get > the actually time if I run this query again (not first time). > > Best regards, > Jakkrit A. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0016369fa374e23d3204670ce585
Just like a mathematical proof in college: "assume the opposite is true. to prove it wrong......" Bob ----- Original Message ----- From: "Scott MacKenzie" <scottm@dinecollege.edu> To: ids@iiug.org Sent: Wednesday, April 8, 2009 10:15:21 AM GMT -05:00 US/Canada Eastern Subject: RE: How to disable cache/buffer when running t.... [15483] To add a third solution: Run a MASSIVE select on other tables before your test query - that way the buffer cache will fill with other tables' data first. And your query will be facing the same up-hill battle every time you run it! -ScottM -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Andreas.KUTSCHE@spar.at Sent: Wednesday, April 08, 2009 3:28 AM To: ids@iiug.org Subject: AW: How to disable cache/buffer when running t.... [15479] Hi, IDS doesn't cache a result set but it caches the data/index pages needed to create the result (if the buffer cache is reasonable large). If you run a query for the first time (and no other query has requested that data too) IDS has to read the tables/indexes from disk. The following query executions will find all or some of the needed tables/indexes in the buffer cache (memory). There are two solutions: 1) Run all of your test queries at least two times and compare only the second run times. 2) Start IDS with a very small buffer cache. Regards, Andreas ------------------------------------------- SPAR sterreichische Warenhandels-AG Hauptzentrale A - 5015 Salzburg, Europastrasse 3 FN 34170 a Tel: +43 662 4470 24223 Mobile: +43 664 6259575 E-Mail: Andreas.KUTSCHE@spar.at Internet: http://www.spar.at Wichtiger Hinweis: Der Inhalt dieser E-Mail kann vertrauliche und rechtlich geschtzte Informationen, insbesondere Betriebs- oder Geschftsgeheimnisse, enthalten, zu deren Geheimhaltung der Empfnger verpflichtet ist. Die Informationen in dieser E-Mail sind ausschlielich fr den Adressaten bestimmt. Sollten Sie die E-Mail irrtmlich erhalten haben so ersuchen wir Sie, die Nachricht von Ihrem System zu lschen und sich mit uns in Verbindung zu setzen. ber das Internet versandte E-Mails knnen leicht manipuliert oder unter fremdem Namen erstellt werden. Daher schlieen wir die rechtliche Verbindlichkeit der in dieser Nachricht enthaltenen Informationen aus. Der Inhalt der E-Mail ist nur rechtsverbindlich, wenn er von uns schriftlich besttigt und gezeichnet wird. Sollte trotz der von uns verwendeten Virus-Schutzprogramme durch die Zusendung von E-Mails ein Virus in Ihre Systeme gelangen, haften wir nicht fr evtl. hieraus entstehende Schden. Wir danken fr Ihr Verstndnis. Important notice: The contents of this e-mail may contain confidential and legally protected information that is in particular related to operational and trade secrets, which the recipient is obliged to treat as confidential. The information in this e-mail is made available exclusively for use by the addressee. In the event that the e-mail may have been sent to you in error, we would ask you to kindly delete this communication from your system and to contact us. E-mails sent via the Internet can be easily manipulated or sent out under someone else's name. We therefore do not accept legal liability for the information contained in this communication. The contents of the e-mail are only legally binding if they have been confirmed and signed by us in writing. If, in spite of our using Antivirus protection software, a virus may have penetrated your system through the sending of this e-mail, we do not accept liability for any damage that may possibly arise as a result of this. We trust that you appreciate our position. ------------------------------------------- -----Ursprngliche Nachricht----- Von: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Im Auftrag von jak aka Gesendet: Mittwoch, 8. April 2009 11:19 An: ids@iiug.org Betreff: How to disable cache/buffer when running the s.... [15478] Hello , When I run a query , for the 1st time the query will take xx minute (ex. 10 minute) but if I run this query again , it will take only y minute (ex. 1 minute). I think IDS keep the result set in buffer/cache . When I run again , IDS map this query to the buffer/cache and retrieve the pre-run result set to me. My question is "how to disable this feature or to disable buffer/cache" ? Because I 'd like to tune query and want to get the actual time using by this query (after changing something such as index, statistics) but I cannot get the actually time if I run this query again (not first time). Best regards, Jakkrit A.