Speed Oracle SQL with Temporary Tables

Using temporary tables with Dictionary Views

The prudent use of temporary tables can dramatically improve Oracle SQL performance. To illustrate the concept, consider the following example from the DBA world. In the query that follows, we want to identify all users who exist within Oracle who have not been granted a role. We could formulate the query as an anti-join with a noncorrelated subquery (against a complex view) as shown here:

select

username

from

dba_users

where

username NOT IN

(select grantee from dba_role_privs);

This query runs in 18 seconds. As you may remember from Chapter 12, these anti-joins can often be replaced with an outer join. However, we have another option by using CTAS. Now, we rewrite the same query to utilize temporary tables by selecting the distinct values from each table.

create table

temp1

as

select

username

from

dba_users;

create table

temp2

as

select distinct

grantee

from

dba_role_privs;

select

username

from

temp1

where

username not in

(select grantee from temp2);

With the addition of temporary tables to hold the intermediate results, this query runs in less than three seconds, a 6× performance increase. Again, it is not easy to quantify the reason for this speed increase, since the DBA views do not map directly to Oracle tables, but it is clear that temporary table show promise for improving the execution speed of certain types of Oracle SQL queries.

Using Temporary Tables

If the amount of data to be processed or utilized from your PL/SQL procedure is too large to fit comfortably in a PL/SQL table, use a GLOBAL TEMPORARY table rather than a normal table. A GLOBAL TEMPORARY table has a persistent definition but data is not persistent and the global temporary table generates no redo or rollback information. For example if you are processing a large number of rows, the results of which are not needed when the current session has ended, you should create the table as a temporary table instead:

create global temporary table results_temp (...) on commit preserve rows;

 

The “on commit preserve rows” clause tells the SQL engine that when a transaction is committed the table should not be cleared.

The global temporary table will be created in the users temporary tablespace when the procedure populates it with data and the DIRECT_IO_COUNT will be used to govern the IO throughput (this usually defaults to 64 blocks).

 

The Top 10 Reasons for Poor Oracle Performance

What are the most common root-causes of poor Oracle performance?  Every expert will give you a different opinion, but I’ve prepared my list based on our hundreds of Oracle tuning engagements:

1. Bad Design – The number one offender to poor performance is over-normalization of Oracle tables, excessive (unused indexes) and 15-way table joins for what should be a simple fetch.

2. Poor server optimization – Setting the server kernel parameters and I/O configuration (e.g. direct I/O) has a profound impact on Oracle performance

3. Bad disk I/O configuration – Inappropriate use of RAID5, disk channel bottlenecks and poor disk striping.

4. Poor Optimizer Statistics – Prior to Oracle 10g (automatic statistics), a common cause of poor SQL performance was missing/stale CBO statistics and missing histograms.

5. Object contention – Failing to set ASSM, freelists or freelist_groups for DML-active tables and indexes can cause very slow DML performance.

6. Under-allocated RAM regions – Not allocating enough RAM for shared_pool_size, pga_aggregate_target and db_cache_size can cause an I/O-bound database.

7. Non-reentrant SQL – All SQL should use host variables/cursor_sharing=force to make SQL reusable within the library cache.

8. Un-set initialization parameters – Many of the initialization parameters are made to be set by the DBA (db_file_multiblock_read_count, optimizer_index_caching) and failing to set these parameters properly results in poorly optimized execution plans.

9. Excessive nested loop joins – In 64-bit Oracle systems we have gigabytes available for RAM sorts and hash joins.  Failing to set pga_aggregate_target to allow the CBO to choose hash joins can result in very slow SQL performance.

10. Human Misfeasance – The DBA’s failure to monitor their database (STATSPACK/AWR), set-up exception reporting alerts (OEM) and adjusting their instance to match changing workloads is a major cause of poor performance.

The Oracle Documentation lists these ten reasons for poor performance.  The BC list is similar, but our top-10 list is based on what we see with our clients:

1. Bad Connection Management

The application connects and disconnects for each database interaction. This problem is common with stateless middleware in application servers. It has over two orders of magnitude impact on performance, and it is totally unscalable.

2. Bad Use of Cursors and the Shared Pool

Not using cursors results in repeated parses. If bind variables are not used, then there is hard parsing of all SQL statements. This has an order of magnitude impact in performance, and it is totally unscalable. Use cursors with bind variables that open the cursor and execute it many times. Be suspicious of applications generating dynamic SQL.

3. Getting Database I/O Wrong

Many sites lay out their databases poorly over the available disks. Other sites specify the number of disks incorrectly, because they configure disks by disk space and not I/O bandwidth.

4. Redo Log Setup Problems

Many sites run with too few redo logs that are too small. Small redo logs cause system checkpoints to continuously put a high load on the buffer cache and I/O system. If there are too few redo logs, then the archive cannot keep up, and the database will wait for the archive process to catch up.

5. Serialization of data blocks in the buffer cache due to lack of free lists, free list groups, transaction slots (INITRANS), or shortage of rollback segments.

This is particularly common on INSERT-heavy applications, in applications that have raised the block size to 8K or 16K, or in applications with large numbers of active users and few rollback segments.

6. Long Full Table Scans

Long full table scans for high-volume or interactive online operations could indicate poor transaction design, missing indexes, or poor SQL optimization. Long table scans, by nature, are I/O intensive and unscalable.

7. In Disk Sorting

In disk sorts for online operations could indicate poor transaction design, missing indexes, or poor SQL optimization. Disk sorts, by nature, are I/O-intensive and unscalable.

8. High Amounts of Recursive (SYS) SQL

Large amounts of recursive SQL executed by SYS could indicate space management activities, such as extent allocations, taking place. This is unscalable and impacts user response time. Recursive SQL executed under another user ID is probably SQL and PL/SQL, and this is not a problem.

9. Schema Errors and Optimizer Problems

In many cases, an application uses too many resources because the schema owning the tables has not been successfully migrated from the development environment or from an older implementation. Examples of this are missing indexes or incorrect statistics. These errors can lead to sub-optimal execution plans and poor interactive user performance. When migrating applications of known performance, export the schema statistics to maintain plan stability using the DBMS_STATS package.

Likewise, optimizer parameters set in the initialization parameter file can override proven optimal execution plans. For these reasons, schemas, schema statistics, and optimizer settings should be managed together as a group to ensure consistency of performance.

10. Use of Nonstandard Initialization Parameters

These might have been implemented based on poor advice or incorrect assumptions. In particular, parameters associated with SPIN_COUNT on latches and undocumented optimizer features can cause a great deal of problems that can require considerable investigation.

SQL tuning with views

The relational database model gave us the concept of views to simplify relational algebra, providing a way to “name” a complex query and treat it as-if it were a discrete table:
create view
myview
as
select   stuff from   tableA natural join  tableB
natiral join  tableC;
Now, we can logically simplify the query:
select stuff from myview where xxx=yyy;
The benefits to views
In sum, views provide a benefit in these areas:
·    Code reuse – Views ensure that everyone uses the exact same SQL to access their tables
·    Column access security – Using the “grant” security model, views can be used for column-level security, such that some columns in a table may be “hidden” by not specifying them in a view.
All benefits come at a cost, and one downside to using views is that it adds complexity to the SQL and makes it harder for the optimizer to service a query with the minimum amount of resources (either I/O or CPU resources, depending on your optimizer goal).
The problem of tuning SQL that contains views
While it’s clear that views are useful for end-user ad-hoc queries and cases where we want to simplify the syntax of complex SQL queries, we have a serious problem when queries contain views.
·    Predicate pushing – The downside to re-usable code is that where clause predicates must be “pushed” down the hierarchy of views, down to the base query.  This adds processing overhead to the optimizer and increases the chances of a bad execution plan.
·    Non mergeable views – Because a view is an encapsulation of a complex query, it is used as-if it were a discrete relational table.  Hence, Oracle must pre-materialize a view whenever it is used in a query.  This create a hidden sub-plan that must be exposed for SQL tuning.
·    Unnecessary overhead – Views are abused when they introduce unnecessary complexity.  For example, you may call a view that is composed of 10 tables where the result set only requires data from two tables.
·    Excessive hard parsing – Predicate pushing may result in a hard parse of the underlying SQL that is executed. Hence, it is important to make sure you use bind variables instead of literals in SQL code calling views. Thus, our SQL should look something like this instead for best performance:
SELECT *
FROM
vw_layer_two_dept_100
WHERE
empid=:b100;
To understand why views make it difficult to tune your SQL, let’s see what happens when a views is used with a where clause.  To function, Oracle must “push” the where clause predicate to the underlying view.
Predicate pushing with views
The Oracle SQL tuning problem becomes a nightmare when views are nested within other views.  Oracle supports pushing of predicates into a given view. Assume we have a set of nested views, like this, where view1 id referenced inside view2:
– View One
CREATE VIEW
vw_layer_one
AS
SELECT * FROM emp;
– view two
CREATE VIEW
vw_layer_two_dept_100
AS
SELECT * FROM vw_layer_one
WHERE deptno=100;
Then assume we issued this query:
SELECT *
FROM
vw_layer_two_dept_100
WHERE empid=100;
The predicate in this SQL is the where empid=100 statement. You may have one of tens or even hundreds of predicates. The Oracle optimizer is written to “push” predicates down into the views that are being referenced in the SQL. Thus, Oracle will transform the vw_layer_one view into a SQL statement that looks like this:
CREATE VIEW
vw_layer_one
AS
SELECT * FROM emp
WHERE
deptno=100
AND
empid=100;
Note that both the predicate from view two (where deptno=100) and the predicate from the SQL statement being executed (where empid=100) are pushed down into the final view that is executed. This can have significant performance benefits because now the bottom view can possibly use an index if one exists on deptno and/or empid.
Predicate pushing can get very complex and predicate pushing also has several performance gotchas:
·    Unnecessary overhead – Views are abused when they introduce unnecessary complexity.  For example, you may call a view that is composed of 10 tables where the result set only requires data from two tables.
·    Hard parsing – Predicate pushing may result in a hard parse of the underlying SQL that is executed. Hence, it is important to make sure you use bind variables instead of literals in SQL code calling views. Thus, our SQL should look something like this instead for best performance:
SELECT *
FROM
vw_layer_two_dept_100
WHERE
empid=:b100;

Abusing views in Oracle SQL
Views are very handy but they get badly abused, which is a shame. I’ve seen views that return 50 columns, and have 40 predicates used to return just two or three columns that could easily have been retrieved from a simple SQL query. This is clearly a case of view abuse, and can lead to badly performing views.
Stacked views can also mask performance problems. Again, they can result in innumerable columns being returned when all you really need are a few of those columns. Also, predicate pushing tends to break down as you stack more views on top of more views.
If you are going to start stacking views, carefully review the rules for predicate pushing in the Oracle documentation. But beware, the predicate pushing rules are long and involved and may change between each release of Oracle!
Merging views and SQL tuning
As we have seen, at optimization time, the CBO will attempt to “flatten out” the views by building and optimizing one large query against the base tables that comprise the view.  This is known as “merging” views, and a merge is critical to avoid a hidden sub-plan from being introduced into your SQL.
Whenever you are tuning SQL and you see the VIEW notation in  the explain plan, you may have a non-mergeable view:
select
count(1) from(
select distinct pat_id from
(
select
p.pat_id,
p.pat_last_name,
p.pat_first_name,
v.visit_id,
v.arrive_dt_tm,
v.depart_dt_tm,
r.test_id,
r.test_name,
r.result_dt_tm,
r.result_val
from
patient p,
pat_visit v,
pat_result r
where
p.pat_id=1
and
p.pat_id=v.pat_id
and
v.pat_id=r.pat_id)
);
Here we see the VIEW in the execution plan, indicating a sub-plan:

ID    PID    Operation Name                           Rows    Bytes    Cost    CPU Cost    IO Cost
0        SELECT STATEMENT                                1        2     8     25M          6
1    0      SORT AGGREGATE                               1        2
2    1        VIEW                                       1        2     8     25M          6
3    2          SORT UNIQUE NOSORT                       1       24     8     25M          6
4    3            HASH JOIN                            125     3000     7     8484914      6
5    4              NESTED LOOPS                         5       90     3     42157        3
6    5                INDEX UNIQUE SCAN    XPKPATIENT    1        9     0     1050         0
7    5                TABLE ACCESS FULL    PAT_VISIT     5       45     3     41107        3
8    4              TABLE ACCESS FULL     PAT_RESULT    25      150     3     63107        3

Note that the VIEW notation in an explain plan means that the view is not mergeable and Oracle must run a sub-plan (which is hidden).  Now, let’s revue some tips for ensuring that you get the fastest possible execution time when using views:
Tips for tuning SQL with views
It’s ironic that views make life simple for the developers but make life complex for the DBA’s who must tune the execution!  Here are some tricks for tuning SQL with views:
·    Use plan stability and SQL profiles – SQL profiles and optimizer plan stability )stored outlines) can swap-out a bad view plan with a correct plan using the base tables.
·    Use a stored procedure instead – Instead of a view, encapsulate the complex SQL inside a stored procedure.  This way, the SQL optimizer will not see any views because you manage the SQL within your own application.
·    Train your developers – If possible, teach the developers to write SQL using the base tables instead of relying on the cosmetic simplicity of views.
·    Optimize to use views – The all-powerful Oracle optimizer parameters can be used for holistic tuning of your entire workload.  For example, using optimizer_secure_view_merging causes Oracle to materialize the view results, resulting in faster query performance.
In sum, Oracle views are a necessary evil, but nesting of views with complex queries are a frequent cause of poor SQL performance.

Tracking & auditing changes to your init.ora parameters

A very important auditing task for any production database is the ability to track changes to the powerful initialization parameters.  Many of these parameters are “silver bullets”, single parameters that have a profound impact on system-wide behavior.  This is especially true for SQL optimizer changes.

A single change to an optimizer parameter might effect thousands of SQL statements, a disaster in a controlled production environment.

Also see these important notes on Oracle best practices.

Auditing changes to init.ora parameters (via pfile or spfile) is an important DBA task.  Sometimes, users which have “alter system” privilege can make unauthorized changes to the initialization parameters in the spfile on a production database.  Hence, auditing changes to parameters is a critical DBA task.  Fortunately, it’s quite simple to audit these changes by implementing the audit_sys_operations=true.

Here is a method to track changes to the initialization parameters.  In order to track all changes to parameters we can use audit for the alter system statement for any specific user

We should follow below steps to track changes to init.ora parms:

1.       ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;

2.       SHUTDOWN IMMEDIATE

3.       STARTUP

4.       CREATE USER TEST IDENTIFIED BY TEST;

5.       GRANT DBA TO TEST;

6.       AUDIT ALTER SYSTEM BY test;

7.       CONN TEST/TEST

8.       ALTER SYSTEM SET AUDIT_TRAIL=db SCOPE=SPFILE;

9.    Create an alert script to notify the DBA when a parameter has changed.

Let’s start by finding the action_name in the dba_audit_trail view for the alter system command:

SQL> select username, timestamp, action_name from dba_audit_trail;
USERNAME                       TIMESTAMP ACTION_NAME
—————————— ——— —————————-
TEST                           29-MAY-09 ALTER SYSTEM

STEP 1 – We can track changes made by SYS user by setting audit_sys_operations parameter to TRUE.

SQL> alter system set audit_sys_operations=true scope=spfile;
System altered.

STEP 2 – Next, we bounce the instance to make the change take effect:
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              92276304 bytes
Database Buffers          188743680 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.

Here we see our auditing parameters:

SQL> show parameter audit

NAME                                 TYPE        VALUE
———————————— ———– ——————————
audit_file_dest                      string      /home/oracle/oracle/product/10
.2.0/db_1/admin/kam/adump
audit_sys_operations                 boolean     TRUE
audit_syslog_level                   string
audit_trail                          string      DB
SQL> alter system set audit_trail=db scope=spfile;

System altered.

STEP 3 – Here we go to the adump directory and examine the audit files:
SQL> host
[oracle@localhost bin]$ cd /home/oracle/oracle/product/10.2.0/db_1/admin/kam/adump/
[oracle@localhost adump]$ ls
ora_5449.aud  ora_5476.aud  ora_5477.aud  ora_5548.aud  ora_5575.aud  ora_5576.aud

[oracle@localhost adump]$ cat ora_5576.aud
Audit file /home/oracle/oracle/product/10.2.0/db_1/admin/kam/adump/ora_5576.aud
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_1/
System name:    Linux
Node name:      localhost.localdomain
Release:        2.6.18-92.el5
Version:        #1 SMP Tue Jun 10 18:49:47 EDT 2008
Machine:        i686
Instance name: TESTDB
Redo thread mounted by this instance: 1
Oracle process number: 15
Unix process pid: 5576, image: oracle@localhost.localdomain (TNS V1-V3)

Fri May 29 02:38:30 2009
ACTION : ‘alter system set audit_trail=db scope=spfile’
DATABASE USER: ‘/’
PRIVILEGE : SYSDBA
CLIENT USER: oracle
CLIENT TERMINAL: pts/2
STATUS: 0

Please beware that using the auditing command imposes additional work on the production database.

How to tune Oracle SQL remotely

The central idea behind “swapping” SQL profiles is simple.  You define a SQL profile that specifies the SQL statement that you want to tune, and an alternative execution plan, in the form of hints.  When this SQL is executed and hits the library cache, Oracle detects that a SQL profile exists for this statement, and automatically applies the hints to change the execution plan.

Hence, we can tune SQL statements without ever touching the SQL statement itself.  To do this we use the DBMS_SQLTUNE package which has an import_sql_profile procedure which allows you to swap hints from one SQL profile into another SQL profile.

dbms_sqltune.import_sql_profile(

sql_text => ’select * from emp’,

profile => sqlprof_attr(‘ALL_ROWS’,'IGNORE_OPTIM_EMBEDDED_HINTS’)

category => ‘DEFAULT’,

name => ‘change_emp’,

force_match => &&6

);

To see how you can swap-out SQL profiles, let’s start by examining the dbms_sqltune package and the import_sql_profile procedure.

In the example below, he forces a query that specifies first_rows_10 to change to all_rows.  He does this trick when he executes dbms_sqltune.import_sql_profile with the profile sqlprof_attr(’ALL_ROWS’,’IGNORE_OPTIM_EMBEDDED_HINTS’):

 

1 – First, he executes the query and display the execution plan which shows an index full scan:

SQL> select /*+ first_rows(10) */ * from sh.customers order by cust_id;

—————————————————-

| Id  | Operation                   | Name         |

—————————————————-

|   0 | SELECT STATEMENT            |              |

|   1 |  TABLE ACCESS BY INDEX ROWID| CUSTOMERS    |

|   2 |   INDEX FULL SCAN           | CUSTOMERS_PK |

—————————————————-

 

2 – Next, he imports the all_rows hint into any query that matches the original query:

begin

dbms_sqltune.import_sql_profile(

name     => ‘test’,

category => ‘DEFAULT’,

sql_text => ’select /*+ first_rows(10) */ * from sh.customers order by cust_id’,

profile  => sqlprof_attr(‘ALL_ROWS’,'IGNORE_OPTIM_EMBEDDED_HINTS’)

);

end;

/

3 – Finally, we re-execute the original query and see that the plan has changed:

SQL> select /*+ first_rows(10) */ * from sh.customers order by cust_id;

—————————————-

| Id  | Operation          | Name      |

—————————————-

|   0 | SELECT STATEMENT   |           |

|   1 |  SORT ORDER BY     |           |

|   2 |   TABLE ACCESS FULL| CUSTOMERS |

—————————————-

 

Internally, SQL profiles are stored in the data dictionary with the SQL profile name, an attribute name, and the attribute value (the hint that is to be applied to the SQL).  Here is a query to display the hints within a SQL profile:

select
attr_val         hint_name

from
dba_sql_profiles prof,
sqlprof$attr     hnt

where
prof.signature = hnt.signature

and
name like (‘&profile_name’)

order by
attr#;

SQL tuning tips – How to find the fastest execution plans

Oracle SQL tuning experts use an endless number of techniques, but there are some common tricks that can be used to optimize all SQL.  Here is a short list of common SQL tuning tips and tricks.  For a more complete list, and details on these SQL tuning tricks, see my bestselling book “Oracle Silver Bullets“:

• Tune the workload first – Always tune your workload as a whole, optimizing the optimizer parameters (optimizer_mode, optimizer_index_cost_adj, db_file_multiblock_read_count) before tuning individual SQL statements.
• Avoid re-parsing of SQL statements – The library cache is intended to make SQL re-entrant, and you can improve response time by cutting-down on redundant SQL parsing.  Be sure to use cursor_sharing when appropriate.
• Use materialized views - Materialized views can pre-summarize aggregations and pre-join tables, making SQL run super fast in systems with low volume update activity.
• Never assume that CBO statistics are correct – Using the GIGO principle (garbage-in, garbage-out), don’t hesitate to re-analyze tables and indexes with dbms_stats.
• Use histograms for tuning – Many common SQL problems (e.g. sub-optimal table join order) are caused by poor cardinality estimates.  Apply histograms providently (only when required) to help the optimizer estimate the size of intermediate rowset operations.
• Use function-based indexes – In almost all cases, the use of a built-in function like to_char, decode, substr, etc. in an SQL query may cause a full-table scan of the target table. To avoid this problem, many Oracle DBAs will create corresponding indexes that make use of function-based indexes. If a corresponding function-based index matches the built-in function of the query, Oracle will be able to service the query with an index range scan thereby avoiding a potentially expensive full-table scan.
• Decompose complex SQL – You can use the with clause and global temporary tables to flatten-out complex subqueries and make execution times faster.
• Avoid subqueries – May types of subqueries (exists, in, not in) can be re-written  as a standard join with faster performance.
• Watch out for counterintuitive tips – tricks such as using where rownum=1 can be dangerous.
• Use views sparingly – Views were designed to assist end-users, and views make complex queries “appear” as-if they were a discrete table.  Hence, running production queries against views can cause a host of optimization problems.
• Watch out for the having clause – You can often decompose a complex query using the with clause to avoid the use of the expensive having clause.
• Use union all when possible – The union clause removes duplicates, and it must perform an expensive sort to remove duplicate rows.  Instead, the union all clause is faster because it does not sort to remove duplicate rows.
• Always reference an indexed column – SQL with a where clause that does not reference any indexed columns can result in an unnecessary large-table full-table scan.
• Avoid using BIF’s in where clause predicates – Don’t invalidate columns by changing the left-hand side of a where clause predicate (where substr(last_name,1,3) = ’Jon’;  where trunc(my_date) = trunc(sysdate)).
• Test with the RULE hint – The RULE hint is fantastic for testing whether a sub-optimal SQL query is failing because of a missing index, or bad CBO statistics.  In many cases, the RULE hints simplicity can help tune SQL statements faster.

Again, these are just a few of the common SQL tuning tricks, and there are many, many more.

Oracle flash_cache tips

Oracle 11g release 2 include a feature called  flash_cache, a table and index argument that resembles the assignment of high-use objects to the KEEP pool.

However, unlike the KEEP pool which uses volatile RAM disk, the flash_cache is used for tertiary storage on solid-state disk (SSD).

The docs suggest that the flash_cache refers to “flash” SSD memory, but it appears that this approach is using SSD as a temporary cache and not as permanent hone for the data, as we see in SSD flash memory.

In traditional SSD, the flash memory is an external rack mount device and it looks like just a bunch of disks (JBOD), and the Oracle data files are allocated directly onto the flash media.  Conversely, in Oracle’s approach, the flash_cache refers to internal flash SSD, and the flash_cache syntax treats it like “temporary” caching instead of permanent storage.
Flash_cache buffer changes

The 11g r2 docs note that the flash_cache is also used to segregate objects, away from the “main memory buffer”:

A flash cache is an extension of the database buffer cache that lives on a flash disk, which is a solid state storage device that uses flash memory.

* Without flash cache, the database re-uses each clean buffer in main memory as needed, overwriting it. If the overwritten buffer is needed later, then the database must read it from magnetic disk.

* With flash cache, the database can write the body of a clean buffer to the flash cache, enabling reuse of its main memory buffer. The database keeps the buffer header in an LRU list in main memory to track the state and location of the buffer body in the flash cache. If this buffer is needed later, then the database can read it from the flash cache instead of from magnetic disk.

Oracle’s Exadata whitepaper also notes that there is a substantial difference in the way that Oracle manages SSD inside the new flash_cache buffer area:

The Exadata Smart Flash Cache manages active data from regular disks in the Exadata cell – but it is not managed in a simple Least Recently Used (LRU) fashion.

The Exadata Storage Server Software in cooperation with the Oracle Database keeps track of data access patterns and knows what and how to cache data and avoid polluting the cache.

This functionality is all managed automatically and does not require manual tuning.

If there are specific tables or indexes that are known to be key to the performance of a database application they can optionally be identified and pinned in cache.
Speed improvement using flash_cache

On Oracle, SSD is up to 600 times faster than platter disks, and at only $2,000 per gigabyte, SSD technology is going to replace platter disks for most systems by 2015, and the 2009 Sun server have on-board SSD flash memory.  Here are current Oracle SSD vendors, and here is a good book on using SSD with Oracle:

Oracle solid state disk tuning

Oracle’s Exadata whitepaper notes that the flash cache SSD is several orders of magnitude faster than platter based disk.

Each Exadata cell comes with 384 GB of Exadata Smart Flash Cache. This solid state storage delivers dramatic performance advantages with Exadata storage. It provides a ten-fold improvement in response time for reads over regular disk; a hundred-fold improvement in IOPS for reads over regular disk; and is a less expensive higher capacity alternative to memory. Overall it delivers a ten-fold increase performing a blended average of read and write operations.
When to use the flash_cache feature

The Oracle documentation suggests enabling the flash_cache when the data buffer advisor suggests that Oracle wants more RAM (a case which is almost always true!), when you are disk I/O bound and when you have spare CPU cycles.  They mention CPU because moving to solid-state flash storage removes disk I/O, but changes the workload bottleneck to CPU in many cases.  Here is a benchmark of SSD showing how flash differs from platter storage.

In other words, flash_cache is for every system that is not already running an SSD back end!

* The Buffer Pool Advisory section of your Automatic Workload Repository (AWR) report or STATSPACK report indicates that doubling the size of the buffer cache would be beneficial.

* db file sequential read is a top wait event.

* You have spare CPU.

Of course, many Oracle shops have been running solid-state disks for many years now, it’s not really a new technology.  In traditional SSD, the SSD looks just like a platter disk, except that the I/O can be hundreds of times faster!
KEEP pool vs. flash_cache

It’s important to note that flash_cache is different from the KEEP pool in both the media type (RAM vs. flash SSD) as well as the arguments.  When using the KEEP pool, you can write automated scripts to detect popular object and cache them:

Oracle Automating KEEP Pool Assignment

The flash_cache is a new storage clause argument, very similar to the KEEP pool:

alter|create table|index
objectname
storage
(
buffer_pool { keep | recycle | default }
flash_cache { keep | none    | default }
);

One confusing aspect of the flash_cache is that Oracle is treating the onboard flash memory as if it were RAM, and not disk!  For example, note that the flash_cache syntax also include a KEEP argument, just like the buffer_pool assignment:

alter table
fred
storage (flash_cache keep);
alter table
fred
storage (buffer_pool keep);

Enabling the flash_cache

Obviously, your server must be equipped with onboard flash SSD, but you must also specify the flash_cache in the init.ora deck.

Unlike the KEEP pool, the new flash_cache option requires setting two new flash_cache parameters, db_flash_cache_file=/dev/miountpoint and db_flash_cache_size=32G, to specify the total size of the onboard flash cache:

SQL> show parameter flash

NAME                                 TYPE         VALUE
————————————                         ———–         ——————————
db_flash_cache_file                  string       /dev/mountpoint
db_flash_cache_size                  big integer  1048576000

In sum, the flash_cache is not much more than yet another method for segregating high-impact objects, and it’s not clear how the flash_cache differs from traditional SSD flash system where the SSD is mounted just like a disk.

Note:  If you do not have the onboard flash_cache Sun server and you try to enable the flash_cache, you get this error message:

ORA-00439: feature not enabled: Server Flash Cache
ORA-01078: failure in processing system parameters

In addition to object segregation, the flash_cache may also be beneficial to RAC systems, since RAC databases love to run on flash drives because it removes bandwidth saturation at the cache fusion layer as a source of contention:

Oracle RAC tuning with solid-state disk

The Oracle documentation says that mounting a platter disk as a flash_cache device will make performance suffer, but they do not say how this flash_cache feature is optimized for SSD flash storage.

Note: As of September 2009 the flash_cache features is not available for Linux.

Important tips for tuning SQL joins

Most Oracle professionals are aware of the logical ways to join tables, such as equi-joins, outer joins and semi-joins; however, Oracle also has internal ways of joining tables, including physical join methods that are critical to understanding SQL tuning.

Built into Oracle is a vast array of internal algorithms, including several types of nested loops and merge joins as well as six types of hash joins.

There are also many data access methods, such as a variety of internal sorts and table access methods.

Oracle nested loops and hash join internals

When we examine an execution plan for a SQL statement, we may see the physical join implementations with names like nested loops, sort merge and hash join.

Hash joins – In a hash join, the Oracle database does a full-scan of the driving table, builds a RAM hash table, and then probes for matching rows in the other table.  For certain types of SQL, the hash join will execute faster than a nested loop join, but the hash join uses more RAM resources.

Nested loops join – The nested loops table join is one of the original table join plans and it remains the most common.  In a nested loops join, we have two tables a driving table and a secondary table.  The rows are usually accessed from a driving table index range scan, and the driving table result set is then nested within a probe of the second table, normally using an index range scan method.

The propensity of the SQL optimizer to invoke a hash join is heavily controlled by the setting for the hash_area_size and pga_aggregate_target Oracle parameters. The larger the value for hash_area_size, the more hash joins the optimizer will invoke. In some releases of Oracle, the hash_area_size defaults to double the value of the sort_area_size parameter, but it highly dependent upon parameter settings and the Oracle release level.

When tuning SQL, we must always remember that it is possible for the optimizer to fail to choose the best table join method. This is especially true for cases where a hash join is wrongly chosen over nested loops. This is frequently the case when we have sub-optimal schema statistics, especially column histograms, which can lead to cases where the optimizer makes an incorrect guess about the cardinality of a result set and wrongly invokes a join that requires a full-table scan rather than choosing nested loops.

This is frequently the case when we have sub-optimal schema statistics (especially column histograms) can lead to cases where the optimizer makes an incorrect guess about the cardinality of a result set and wrongly invokes a join that requires a full-table scan, not choosing nested loops.

For example, consider a zillion rows sales table with this query:

select
sales_stuff
from
sales
where
state = ‘idaho’;

If we assume that Idaho has less than 1% of the rows in the sales table, we would want to force the optimizer to invoke a nested loops join, so that we may use an index for the join.  This can be accomplished in several ways:

Analyze a column histogram on the state column – This will address the root cause of the issue since the optimizer will now recognize that Idaho is a low cardinality query.
Use a hint – Hints are a last resort because of unintended side-effects, but you can use the use_nl_with_index hint to force the query to choose a nested loops join method.
Invoke dynamic sampling – You can use the opt_estimate hint or the dynamic_sampling hint to force the nested loops join:

Here are equivalent ways to force a nested loops join:

********************************************
Nested loops hint
*******************************************

select /*+ use_nl_with_index */
sales_stuff
from
sales
where
state = ‘idaho’;

********************************************
Dynamic sampling
*******************************************

select /*+ dynamic_sampling(sales,10)
sales_stuff
from
sales
where
state = ‘idaho’;

********************************************
opt_estimate hint
*******************************************

select /*+ opt_estimate(table,sales,scale_rows=.001)
sales_stuff
from
sales
where
state = ‘idaho’;

Beware of the use_nl hint!

Using the use_nl hint can force a query to use a nested loops join, but the use of join hints can create a problem, especially with nested loops indexes which reply on indexes.
If the index were dropped, you would get a horrific join plan.

To alleviate this issue, you can use the use_nl_with_index hint.  This hint will only direct a nested loops join if the suitable index is present.

Global environment variables

To view the global environment variables, use the printenv command:
$ printenv
HOSTNAME=testbox.localdomain
TERM=xterm
SHELL=/bin/bash
HISTSIZE=1000
SSH CLIENT=192.168.1.2 1358 22
OLDPWD=/home/rich/test/test1
SSH TTY=/dev/pts/0
USER=rich
LS COLORS=no=00:fi=00:di=00;34:ln=00;36:pi=40;33:so=00;35:
bd=40;33;01:cd=40;33;01:or=01;05;37;41:mi=01;05;37;41:ex=00;32:
*.cmd=00;32:*.exe=00;32:*.com=00;32:*.btm=00;32:*.bat=00;32:
*.sh=00;32:*.csh=00;32:*.tar=00;31:*.tgz=00;31:*.arj=00;31:
*.taz=00;31:*.lzh=00;31:*.zip=00;31:*.z=00;31:*.Z=00;31:
*.gz=00;31:*.bz2=00;31:*.bz=00;31:*.tz=00;31:*.rpm=00;31:
*.cpio=00;31:*.jpg=00;35:*.gif=00;35:*.bmp=00;35:*.xbm=00;35:
*.xpm=00;35:*.png=00;35:*.tif=00;35:
MAIL=/var/spool/mail/rich
PATH=/usr/kerberos/bin:/usr/lib/ccache:/usr/local/bin:/bin:/usr/bin:
/home/rich/bin
INPUTRC=/etc/inputrc
PWD=/home/rich
LANG=en US.UTF-8
SSH ASKPASS=/usr/libexec/openssh/gnome-ssh-askpass
SHLVL=1
HOME=/home/rich
LOGNAME=rich
CVS RSH=ssh

SSH CONNECTION=192.168.1.2 1358 192.168.1.4 22
LESSOPEN=|/usr/bin/lesspipe.sh %s
G BROKEN FILENAMES=1
=/usr/bin/printenv

Function Digit To Word

Following the function for convert from digit to Word :

Create or Replace Function Terbilang(Digit varchar2) Return Varchar2 is
Tampung99 varchar2(100);

# (Author : Ida Bagus Enderajana)
kel_1    varchar2(30);
kel_2    varchar2(30);
kel_3    varchar2(30);
kel_4    varchar2(30);
kel_5    varchar2(30);
hasil   varchar2(3000);
cekdigit number;
sisabagi number;
Begin
tampung99 :=”;
kel_1:=”;
kel_2:=”;
kel_3:=”;
kel_4:=”;
kel_5:=”;
cekdigit :=length(digit);
sisabagi :=mod(cekdigit,3);

if sisabagi=0 then
tampung99:=digit;
elsif sisabagi=1 then
tampung99:=’00′||digit;
elsif sisabagi=2 then
tampung99:=’0′||digit;
end if;

if length(tampung99)=3 then
kel_1:=substr(tampung99,1,3);
hasil:=basecurr_triple_number_words(kel_1);

elsif length(tampung99)=6 then
kel_1:=substr(tampung99,1,3);
kel_2:=substr(tampung99,4,3);
if kel_1=’001′ and kel_2=’000′ then
hasil:=’seribu’;
elsif kel_1=(‘001′) and kel_2 <>(‘000′) then
hasil:=’seribu ‘||basecurr_triple_number_words(to_number(kel_2));
elsif kel_1 <>(‘001′) and kel_2 <>(‘000′) then
hasil:=basecurr_triple_number_words(to_number(kel_1))||’ ribu ‘||basecurr_triple_number_words(to_number(kel_2));
elsif kel_1 <>’001′ and kel_2=’000′ then
hasil:=basecurr_triple_number_words(to_number(kel_1))||’ ribu ‘;
end if;

elsif length(tampung99)=9 then
begin
kel_1:=substr(tampung99,1,3);
kel_2:=substr(tampung99,4,3);
kel_3:=substr(tampung99,7,3);
if kel_1 <>’000′ then
begin
if kel_2 <>’000′ and kel_2 <>’001′ and kel_3 <>’000′ then
hasil:=basecurr_triple_number_words(kel_1)||’ juta ‘||basecurr_triple_number_words(kel_2)||’ ribu ‘||basecurr_triple_number_words(kel_3);
elsif kel_2 =’000′ and kel_3=’000′  then
hasil:=basecurr_triple_number_words(kel_1)||’ juta ‘||basecurr_triple_number_words(kel_3);
elsif kel_2=’000′ and kel_3 <>’000′ then
hasil:=basecurr_triple_number_words(kel_1)||’ juta ‘||basecurr_triple_number_words(kel_3);
elsif kel_2=’001′ and kel_3=’000′ then
hasil:=basecurr_triple_number_words(to_number(kel_1))||’ juta ‘||’ seribu’;
elsif kel_2=’001′ and kel_3 <>’000′ then
hasil:=basecurr_triple_number_words(to_number(kel_1))||’ juta ‘||’ seribu ‘||basecurr_triple_number_words(to_number(kel_3));
elsif kel_2 =’001′ and kel_3 <>’000′ then
hasil:=basecurr_triple_number_words(kel_1)||’ juta ‘||’ seribu ‘||basecurr_triple_number_words(kel_3);
elsif kel_2 <>’000′ and kel_2 <>’001′ and kel_3 <>’000′ or kel_3=’000′   then
hasil:=basecurr_triple_number_words(kel_1)||’ juta ‘||basecurr_triple_number_words(kel_2)||’ ribu ‘||basecurr_triple_number_words(kel_3);
end if;
end;
end if;
end;

elsif length(tampung99)=12 then
begin
kel_1:=substr(tampung99,1,3);
kel_2:=substr(tampung99,4,3);
kel_3:=substr(tampung99,7,3);
kel_4:=substr(tampung99,10,3);
if kel_1 <>’000′ and kel_2 <>’000′ then
begin
if kel_3 <>’000′ and kel_4 <>’000′ then
hasil:=basecurr_triple_number_words(kel_1)||’ milyar ‘||basecurr_triple_number_words(kel_2)||’ juta ‘||basecurr_triple_number_words(kel_3)||’ ribu ‘||basecurr_triple_number_words(kel_4);
elsif kel_3 =’000′ and kel_4 <>’000′ then
hasil:=basecurr_triple_number_words(kel_1)||’ milyar ‘||basecurr_triple_number_words(kel_2)||’ juta ‘||basecurr_triple_number_words(kel_4);
elsif kel_3 =’000′ and kel_4=’000′  then
hasil:=basecurr_triple_number_words(kel_1)||’ milyar ‘||basecurr_triple_number_words(kel_2)||’ juta ‘||basecurr_triple_number_words(kel_4);
elsif kel_3=’001′ and kel_4=’000′ then
hasil:=basecurr_triple_number_words(kel_1)||’ milyar ‘||basecurr_triple_number_words(kel_2)||’ juta ‘||’ seribu ‘;
elsif kel_3=’001′ and kel_4 <>’000′ then
hasil :=basecurr_triple_number_words(kel_1)||’ milyar ‘||basecurr_triple_number_words(kel_2)||’ juta ‘||’ seribu ‘||basecurr_triple_number_words(kel_4);
elsif kel_3 <>’000′ and kel_3 =’001′ and kel_4 <>’000′ or kel_4=’000′   then
hasil :=basecurr_triple_number_words(kel_1)||’ milyar ‘||basecurr_triple_number_words(kel_2)||’ juta ‘||basecurr_triple_number_words(kel_3)||’ ribu ‘||basecurr_triple_number_words(kel_4);
end if;
end;
elsif kel_1 <>’000′ and kel_2=’000′ then
begin
if kel_3 =’000′ and kel_4=’000′ or kel_4 <>’000′ then
hasil:=basecurr_triple_number_words(kel_1)||’ milyar ‘||basecurr_triple_number_words(kel_4);
elsif kel_3 <>’001′ or kel_3 <>’000′ and kel_4 =’000′ or kel_4 <>’000′ then
hasil:=basecurr_triple_number_words(kel_1)||’ milyar ‘||basecurr_triple_number_words(kel_3)||’ ribu ‘||basecurr_triple_number_words(kel_4);
end if;
end;
end if;
end;
elsif length(tampung99)=15 then
begin
kel_1:=substr(tampung99,1,3);
kel_2:=substr(tampung99,4,3);
kel_3:=substr(tampung99,7,3);
kel_4:=substr(tampung99,10,3);
kel_5:=substr(tampung99,13,3);
if kel_1 <>’000′ and kel_2 <>’000′ and kel_3 <>’000′ then
begin
if kel_4 <>’000′ and kel_5 <>’000′ then
hasil:=basecurr_triple_number_words(kel_1)||’ trilyun ‘||basecurr_triple_number_words(kel_2)||’ milyar ‘||basecurr_triple_number_words(kel_3)||’ juta ‘||basecurr_triple_number_words(kel_4)||’ ribu ‘||basecurr_triple_number_words(kel_5);
elsif kel_4 =’000′ and kel_5 <>’000′ then
hasil:=basecurr_triple_number_words(kel_1)||’ trilyun ‘||basecurr_triple_number_words(kel_2)||’ milyar ‘||basecurr_triple_number_words(kel_3)||’ juta ‘||basecurr_triple_number_words(kel_5);
elsif kel_4 =’000′ and kel_5=’000′  then
hasil:=basecurr_triple_number_words(kel_1)||’ trilyun ‘||basecurr_triple_number_words(kel_2)||’ milyar ‘||basecurr_triple_number_words(kel_3)||’ juta ‘||basecurr_triple_number_words(kel_5);
elsif kel_4=’001′ and kel_5=’000′ then
hasil:=basecurr_triple_number_words(kel_1)||’ trilyun ‘||basecurr_triple_number_words(kel_2)||’ milyar ‘||basecurr_triple_number_words(kel_3)||’ juta ‘||’ seribu ‘;
elsif kel_4=’001′ and kel_5 <>’000′ then
hasil :=basecurr_triple_number_words(kel_1)||’ trilyun ‘||basecurr_triple_number_words(kel_2)||’ milyar ‘||basecurr_triple_number_words(kel_3)||’ juta ‘||’ seribu ‘||basecurr_triple_number_words(kel_5);
elsif kel_4 <>’000′ and kel_4 =’001′ and kel_5 <>’000′ or kel_5=’000′   then
hasil :=basecurr_triple_number_words(kel_1)||’ trilyun ‘||basecurr_triple_number_words(kel_2)||’ milyar ‘||basecurr_triple_number_words(kel_3)||’ juta ‘||basecurr_triple_number_words(kel_4)||’ ribu ‘||basecurr_triple_number_words(kel_5);
end if;
end;
elsif kel_1 <>’000′ and kel_2 <>’000′ and kel_3 =’000′ then
begin
if kel_4 <>’000′ and kel_5 <>’000′ then
hasil:=basecurr_triple_number_words(kel_1)||’ trilyun ‘||basecurr_triple_number_words(kel_2)||’ milyar ‘||basecurr_triple_number_words(kel_4)||’ ribu ‘||basecurr_triple_number_words(kel_5);
elsif kel_4 =’000′ and kel_5 <>’000′ then
hasil:=basecurr_triple_number_words(kel_1)||’ trilyun ‘||basecurr_triple_number_words(kel_2)||’ milyar ‘||basecurr_triple_number_words(kel_5);
elsif kel_4 =’000′ and kel_5=’000′  then
hasil:=basecurr_triple_number_words(kel_1)||’ trilyun ‘||basecurr_triple_number_words(kel_2)||’ milyar ‘||basecurr_triple_number_words(kel_5);
elsif kel_4=’001′ and kel_5=’000′ then
hasil:=basecurr_triple_number_words(kel_1)||’ trilyun ‘||basecurr_triple_number_words(kel_2)||’ milyar ‘||’ seribu ‘;
elsif kel_4=’001′ and kel_5 <>’000′ then
hasil :=basecurr_triple_number_words(kel_1)||’ trilyun ‘||basecurr_triple_number_words(kel_2)||’ milyar ‘||’ seribu ‘||basecurr_triple_number_words(kel_5);
elsif kel_4 <>’000′ and kel_4 =’001′ and kel_5 <>’000′ or kel_5=’000′   then
hasil :=basecurr_triple_number_words(kel_1)||’ trilyun ‘||basecurr_triple_number_words(kel_2)||’ milyar ‘||basecurr_triple_number_words(kel_4)||’ ribu ‘||basecurr_triple_number_words(kel_5);
end if;
end;
elsif kel_1 <>’000′ and kel_2 =’000′ and kel_3 <>’000′ then
begin
if kel_4 <>’000′ and kel_5 <>’000′ then
hasil:=basecurr_triple_number_words(kel_1)||’ trilyun ‘||basecurr_triple_number_words(kel_3)||’ juta ‘||basecurr_triple_number_words(kel_4)||’ ribu ‘||basecurr_triple_number_words(kel_5);
elsif kel_4 =’000′ and kel_5 <>’000′ then
hasil:=basecurr_triple_number_words(kel_1)||’ trilyun ‘||basecurr_triple_number_words(kel_3)||’ juta ‘||basecurr_triple_number_words(kel_5);
elsif kel_4 =’000′ and kel_5=’000′  then
hasil:=basecurr_triple_number_words(kel_1)||’ trilyun ‘||basecurr_triple_number_words(kel_3)||’ juta ‘||basecurr_triple_number_words(kel_5);
elsif kel_4=’001′ and kel_5=’000′ then
hasil:=basecurr_triple_number_words(kel_1)||’ trilyun ‘||basecurr_triple_number_words(kel_3)||’ juta ‘||’ seribu ‘;
elsif kel_4=’001′ and kel_5 <>’000′ then
hasil :=basecurr_triple_number_words(kel_1)||’ trilyun ‘||basecurr_triple_number_words(kel_3)||’ juta ‘||’ seribu ‘||basecurr_triple_number_words(kel_5);
elsif kel_4 <>’000′ and kel_4 =’001′ and kel_5 <>’000′ or kel_5=’000′   then
hasil :=basecurr_triple_number_words(kel_1)||’ trilyun ‘||basecurr_triple_number_words(kel_3)||’ juta ‘||basecurr_triple_number_words(kel_4)||’ ribu ‘||basecurr_triple_number_words(kel_5);
end if;
end;
elsif kel_1 <>’000′ and kel_2=’000′ and kel_3=’000′ then
begin
if kel_3 =’000′ and kel_4=’000′ or kel_4 <>’000′ then
hasil:=basecurr_triple_number_words(kel_1)||’ trilyun ‘||basecurr_triple_number_words(kel_5);
elsif kel_3 <>’001′ or kel_3 <>’000′ and kel_4 =’000′ or kel_4 <>’000′ then
hasil:=basecurr_triple_number_words(kel_1)||’ trilyun ‘||basecurr_triple_number_words(kel_4)||’ ribu ‘||basecurr_triple_number_words(kel_5);
end if;
end;
end if;
end;
end if;
return(hasil);
end;
/