Background Image

BLOG

Votes 0 Comment 0
?

Shortcut

PrevPrev Article

NextNext Article

Larger Font Smaller Font Up Down Go comment Print Attachment

Written by MinJong Kim on 12/09/2021

ABOUT QUERY CACHE 

With the release of CUBRID 11.0, the CUBRID DBMS supports QUERY CACHE hint.

In this article, we will take some time to look at QUERY CACHE. 

 

 

1. What is Query Cache?

Query Cache is a DBMS feature that stores the statements together with the retrieved record set in memory using the SELECT query statement and returns the previously cached values when the identical query statement is requested.

 

The query cache can be useful in an environment where you have tables that do not change very often and for which the server receives many identical queries. Queries using the QUERY_CACHE hint are cached in a dedicated memory area, and the results are also cached in separate disk space.  

 

  • Query Cache Features 

1. The QUERY_CACHE hint only applies to SELECT statements.

2. When a table change (INSERT, UPDATE, DELETE) occurs, the information in the Query Cache related to the table is initialized.

3. When the DB is unloaded, the Query Cache is initialized.

4. The cache size can be adjusted through the max_query_cache_entries and query_cache_size_in_pages setting (The default value is all 0). 

max_query_cache_entries is the setting value for the maximum number of queries that can be cached. If it is set to 1 or more, as many queries as the set number are cached.

query_cache_size_in_pages is the setting value for the maximum cacheable result pages. If it is set to 1 or more, the results for the set page are cached.

 

  • Pros for Query Cache

When the hint is set and a new SELECT query is processed, the query cache is looked up if the query appears in the query cache. If the query is found from the cache, the data stored in the cache will be returned immediately without going through the previous 3 steps (Parsing->Optimizing->Executing). Therefore, the higher the cost of the query and the more repeatedly invoked, the greater the resource benefits.

 

 

2. How to use the Query Cache

  • How to use? 

1. Set the values of max_query_cache_entries and query_cache_size_in_pages  in the cubrid.conf file. (Example: if the number of sql statements to cache is 10, set the max_query_cache_entries value to 10, and set query_cache_size_in_pages to 640 when the result size is 10M.)

2. Add the /*+ QUERY_CACHE */ hint to the select statement.

 

  • Procedure 


      
1.png

 

  1. When the hint is set and a new SELECT query is processed, the query cache is looked up if the query appears in the query cache.
  2. If the cached query is not found, the query will be processed and then cached newly with its result.

 

  • Result Analysis 

      You can check whether a query is cached by entering the session command ;info qcache in CSQL.
      example) 

     2.png

The cached query is shown as query_string in the middle of the result screen. Each of the n_entries and n_pages represents the number of cached queries and the number of pages in the cached results.

 

The n_entries is limited to the value of configuration parameter max_query_cache_entries and the n_pages is limited to the value of query_cache_size_in_pages.
 

If the n_entries is overflown or the n_pages is overflown, some of the cache entries are selected for deletion. The number of caches deleted is about 20% of max_query_cache_entries value and of the query_cache_size_in_pages value.

 

 

3. Cautions

The hint is applied to SELECT query only; However, for the following cases, the hint does not apply to the query and the hint is meaningless:

 

  • a system time or date related attribute in the query as below
              example) SELECT SYSDATE, ADDDATE (SYSDATE, INTERVAL -24 HOUR), ADDDATE (SYSDATE, -1);
  • a SERIAL related attribute is in the query
  • a column-path related attribute is in the query
  • a method is in the query
  • a stored procedure or a stored function is in the query
  • a system table like dual, _db_attribute, and so on, is in the query
  • a system function like sys_guid() is in the query

 

  • Lock waiting situation may occur due to the table change

    If there is any change (ex. INSERT, UPDATE, DELETE) to the target table, the existing cache is always removed. At this time, other transactions will now lock the data to prevent it from grabbing any more invalid data. Until this lock is released, transactions accessing the Query Cache wait in the lock state. Therefore, the more frequently the table is changed, the more SELECT queries that use the Query Cache, the more time is spent waiting for this lock.

4. Conclusion

Query Cache is useful for data that does not change frequently but needs to be accessed frequently, such as street name, address, organization information, department information, etc. In this case, Query Cache can help reduce system resource usage and improve performance. 


  1. CUBRID INTERNAL: CUBRID Double Write Buffer

    Written by MyungGyu Kim on 03/08/2022 INTRODUCTION Data in the database is allocated from disk to memory, some data is read and then modified, and some data is newly created and allocated to memory. Such data should eventually be stored on disk to ensure that it is permanently stored. In this article, we will introduce one of the methods of storing data on disk in CUBRID to help you understand the CUBRID database. The current version at the time of writing is CUBRID 11.2. DOUBLE WRITE BUFFER First of all, I would like to give a general description of the definition, purpose, and mechanism of Double Write Buffer. What is Double Write Buffer? By default, CUBRID stores data on disk through Double Write Buffer. Double Write Buffer is a buffer area composed of both memory and disk. By default, t...
    Read More
  2. Monitoring CUBRID through Scouter

    Written by TaeHwan Seo on 01/18/2022 CUBRID users can monitor items in CUBRID through the Scouter. It was developed based on CUBRID 11.0 version. Full features are available from CUBRID 10.2.1 Version. Scouter (Server, Client) is available from version 2.15.0, bug fixes and features will be added by participating in Scouter GitHub in the future. The latest version of Scouter (as in 2022.01.18) is Scouter 2.15.0, Multi Agent support and bug fixes are currently in the PR stage. 1. What is Scouter? Scouter is an Open Source Application Performance Management (APM), it provides monitoring function for applications and OS. Scouter Basic Configuration Scouter-provided Information ​- WAS Basic Information Response speed/profiling information for each request, number of server requests/number of re...
    Read More
  3. QUERY CACHE Hint

    Written by MinJong Kim on 12/09/2021 ABOUT QUERY CACHE With the release of CUBRID 11.0, the CUBRID DBMS supports QUERY CACHE hint. In this article, we will take some time to look at QUERY CACHE. 1. What is Query Cache? Query Cache is a DBMS feature that stores the statements together with the retrieved record set in memory using the SELECT query statement and returns the previously cached values when the identical query statement is requested. The query cache can be useful in an environment where you have tables that do not change very often and for which the server receives many identical queries. Queries using the QUERY_CACHE hint are cached in a dedicated memory area, and the results are also cached in separate disk space. Query Cache Features 1. The QUERY_CACHE hint only applies to SELE...
    Read More
  4. CUBRID INSIDE: HASH SCAN Method

    Written by SeHun Park on 11/09/2021 - HASH SCAN Hash Scan is a scan method for hash join. Hash Scan is applied in view or hierarchical query. When a subquery such as view is joined as inner, index scan cannot be used. In this case, performance degradation occurs due to repeated inquiry of a lot of data. In this situation, Hash Scan is used. The picture above shows the difference between Nested Loop join and Hash Scan in the absence of an index. In the case of NL join, the entire data of INNER is scanned as many as the number of rows of OUTER. In contrast, Hash Scan scans INNER data once when building a hash data structure and scans OUTER once when searching. Therefore, you can search for the desired data relatively very quickly. Here, the internal structure of Hash Scan is written as the fl...
    Read More
  5. Converting PL/SQL to CUBRID Java SP using ANTLR and StringTemplate

    Written by Youngjin Joo on 09/30/2021 CUBRID DBMS (hereinafter 'CUBRID') does not support PL/SQL. If you want to continue your project by creating functions or subprograms with PL/SQL syntax in CUBRID, you need to convert them to Java Stored Function/Procedure (hereinafter 'Java SP'). Database developers, administrators, and engineers are often familiar with PL/SQL syntax but not with programming languages. In addition, application development depends very little on the DBMS used, but converting PL/SQL to Java SP seems difficult because it feels like you're developing a new system. Therefore, while I am looking for an easy way to convert PL/SQL to Java SP, I found out about ANTLR. ANTLR is a tool for generating parsers. With the help of contributors around the world, ANT...
    Read More
Board Pagination Prev 1 2 3 4 5 Next
/ 5

Join the CUBRID Project on