Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: Enterprise
Expertise: Advanced
Aug 11, 2000

Optimize Oracle's SQL Statement Cache

When Oracle receives a SQL statement to execute, it first looks to see if the execution plan already exists in its cache. If the statement does exist in the cache, the previous execution plan is retrieved and used. This reduces the cost of recreating the execution plan, thus saving execution time. The cache can be viewed via the V$SQL table. Looking at the V$SQL table's SQL_TEXT column shows what statements are currently in cache.

One thing to note about how the cache works is that the SQL statements it places in the cache are case-sensitive. Therefore, the following two statements perform the same query, but are seen as two different queries:

  1. SELECT * FROM V$SQL
  2. SELECT * FROM v$sql
They are considered different because the first query references the V$SQL table in all uppercase letters, whereas the second uses all lowercase letters. To prove this, run the following query (note that two entries are returned):
 SELECT * FROM V$SQL WHERE SQL_TEXT = 
'select * from V$SQL'
or
SQL_TEXT = 'select * from v$sql'
This can flood the cache with the same query, thus making the cache less useful. To prevent this, always ensure that applications that issue the same query use the same letter case in the syntax.
Andrew Holliday
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap