Optimize Oracle’s SQL Statement Cache

When Oracle receives a SQL statement to execute, it first looks to see ifthe execution plan already exists in its cache. If the statement does existin the cache, the previous execution plan is retrieved and used. Thisreduces the cost of recreating the execution plan, thus saving executiontime. The cache can be viewed via the V$SQL table. Looking at the V$SQLtable’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 itplaces in the cache are case-sensitive. Therefore, the following twostatements 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 theV$SQL table in all uppercase letters, whereas the second uses all lowercaseletters. To prove this, run the following query (note that two entriesare 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 lessuseful. To prevent this, always ensure that applications that issue the samequery use the same letter case in the syntax.

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

The Latest

microsoft careers

Top Careers at Microsoft

Microsoft has gained its position as one of the top companies in the world, and Microsoft careers are flourishing. This multinational company is efficiently developing popular software and computers with other consumer electronics. It is a dream come true for so many people to acquire a high paid, high-prestige job

your company's audio

4 Areas of Your Company Where Your Audio Really Matters

Your company probably relies on audio more than you realize. Whether you’re creating a spoken text message to a colleague or giving a speech, you want your audio to shine. Otherwise, you could cause avoidable friction points and potentially hurt your brand reputation. For example, let’s say you create a

chrome os developer mode

How to Turn on Chrome OS Developer Mode

Google’s Chrome OS is a popular operating system that is widely used on Chromebooks and other devices. While it is designed to be simple and user-friendly, there are times when users may want to access additional features and functionality. One way to do this is by turning on Chrome OS