Dontcheff

Archive for the ‘SQL’ Category

ORA-56955: quarantined plan used

In DBA, New features, Oracle database, Security and auditing, SQL on May 29, 2019 at 13:15

“The way that worms and viruses spread on the Internet is not that different from the way they spread in the real world, and the way you quarantine them is not that different, either” – David Ulevitch

And now, in Oracle 19c, you can do the same with SQL:

 SQL> SELECT client, COUNT(*) OVER (PARTITION BY price) CLIENT_COUNT 
 FROM sales WHERE price IN (2, 91984);
  *
 ERROR at line 1:
 ORA-56955: quarantined plan used
 Elapsed: 00:00:00.00

Error “ORA-56955: quarantined plan used” is new in the Oracle database, it comes when the SQL run fulfills the quarantine conditions.

It is important to differentiate Oracle SQL Quarantines in 19c from Oracle Object Quarantines in 18c. There is also the concept of Offload Quarantines.

1. A good way to start understanding what SQL quarantines are about is to watch the following short video from Rich Niemiec:

2. Check also page 23 of the Optimizer In Oracle Database 19c white paper. “The new Oracle Database 19c feature SQL Quarantine can be used to eliminate the overhead of runaway queries. When DBRM detects a SQL statement is exceeding a resource or run-time limit, the SQL execution plan used by the statement is quarantined. If the SQL statement is executed again and it is using the same SQL execution plan then it will be terminated immediately. This can significantly reduce the amount of system resource that would otherwise be wasted.”

Think of SQL Quarantines as a way to prevent unnecessary SQL being run in the database, of course based on your own definition of unnecessary SQL. You can prevent the use of “bad” execution plans and exhausting the databases from resources.

In the database, there might be SQL statements with high utilization of CPU and IO: you can prevent them from being started so once they are quarantined they no longer consume system resources because they are terminated prior to their execution.

Note that SQL quarantines work only in 19c on Exadata and DBCS/ExaCS. Check out the Database Licensing Information User Manual:

3. You can quarantine a statement based on:

– SQL_ID and one of its execution plan
– SQL_ID and all of its executions plans
– specific SQL_TEXT

You quarantine a statement in 2 steps:

(A) create a SQL Quarantine by using (for example) DBMS_SQLQ.CREATE_QUARANTINE_BY_SQL_ID
(B) add thresholds by using DBMS_SQLQ.ALTER_QUARANTINE

Here are some examples and some more.

4. For some interesting and non-documented stuff check the article by Mahmoud Hatem entitled Oracle 19c : The QUARANTINE hint.

For instance, it shows how you can test by setting “_exadata_feature_on”=true in order to get SQL QUARANTINE feature to work on a non-Exadata box.

The following parameters can affect a quarantine kick off:

CPU_TIME
ELAPSED_TIME
IO_MEGABYTES
IO_REQUESTS
IO_LOGICAL
PHV

There is also the special value called ALWAYS_QUARANTINE.

5. All details can be of course found in the SQL Quarantine documentation.

The following columns of the V$SQL and GV$SQL views show the quarantine information of execution plans of SQL statements:

– SQL_QUARANTINE: This column shows the name of the quarantine configuration for an execution plan of a SQL statement.
– AVOIDED_EXECUTIONS: This column shows the number of times an execution plan of a SQL statement was prevented from running after it was quarantined.

There is a new view in 19c called DBA_SQL_QUARANTINE which displays information about quarantine configurations.

Good news also for admin users of the Autonomous Database: you have full access to the feature:

And note that a DBA can also transfer quarantine configurations from one database to another database using the DBMS_SQLQ package subprograms: CREATE_STGTAB_QUARANTINE, PACK_STGTAB_QUARANTINE, and UNPACK_STGTAB_QUARANTINE.

If you plan to visit Oracle OpenWorld this year (September 16-19, 2019), as of now, there are a couple of presentations on SQL Quarantine:

– Oracle Database 19c: SQL Tuning Using Plan Stability Methods SPM/SQL Quarantine: by Soumendra Paik, Senior Principal Technical Support Engineer, Oracle
– What’s New in Oracle Optimizer, by Nigel Bayliss, Senior Principal Product Manager, Oracle

Question: how do you quarantine a statement based on a sub-string of the query? Like, how can you quarantine statements starting with ‘select *‘?

What else besides Automatic Indexing is new in Oracle Database 19c?

In DBA, Init.ora, New features, Oracle database, Security and auditing, SQL on April 23, 2019 at 10:53

Automatic Indexing (AI) is probably the most important new feature of Oracle Database 19c and AI is arguably one of the best example of AI in the IT industry. But there is much more that came along with 19c.

Here is my choice of the top 10 least known (for now at least) new features of Oracle Database 19.2 (in no order whatsoever):

1. Security: if the database password file name or location has been changed and the metadata cache needs to be refreshed with the details of the updated database password file, you can do it simply with the following command:

SQL> alter system flush passwordfile_metadata_cache;

System altered.

In 19c, most of the Oracle Database supplied schema-only accounts now have their passwords removed to prevent users from authenticating to these accounts. But DBAs can still assign passwords to the default schema-only accounts.

2. SQL: the LISTAGG aggregate function now supports duplicate elimination by using the new DISTINCT keyword.

There are also a new bitvector SQL operators can be used to speed up COUNT DISTINCT operations within a SQL query.

3. Active Data Guard DML redirection: You can now run DML on the Active Data Guard standby database. When run on the standby side, the update is passed to the Primary database where it is executed and the resulting redo of the transaction will update the standby after which control will be returned to the application.

4. There are 6 new init.ora parameters in 19c. DBAs can now tune in DataGuard the amount of wait time by using DATA_GUARD_MAX_IO_TIME and DATA_GUARD_MAX_LONGIO_TIME. You can check details for all six:

ADG_REDIRECT_DML
DATA_GUARD_MAX_IO_TIME
DATA_GUARD_MAX_LONGIO_TIME
ENABLE_IMC_WITH_MIRA
LOB_SIGNATURE_ENABLE
MAX_DATAPUMP_PARALLEL_PER_JOB

5. You can now create Materialized Views containing bitmap-based COUNT(DISTINCT) functions. This means that the MVs are based on SQL aggregate functions that use bitmap representation to express the computation of COUNT(DISTINCT) operations.

SQL> create materialized view MV_SALES as
select CLIENT, 
BITMAP_CONSTRUCT_AGG(BITMAP_BIT_POSITION(PRICE),'RAW') bm_price
from SALES
group by CLIENT,BITMAP_BUCKET_NUMBER(PRICE);  

Materialized view created.

6. Looks like there is now automatic resolution of SQL plan regressions: “SQL plan management searches for SQL statements in the Automatic Workload Repository (AWR). Prioritizing by highest load, it looks for alternative plans in all available sources, adding better-performing plans to the SQL plan baseline. Oracle Database also provides a plan comparison facility and improved hint reporting.”

7. Real-Time Statistics is also a new cool feature. “Oracle automatically gathers online statistics during conventional DML operations. Statistics can go stale between execution of DBMS_STATS statistics gathering jobs. By gathering some statistics automatically during DML operations, the database augments the statistics gathered by DBMS_STATS. Fresh statistics enable the optimizer to produce more optimal plans.”

8. Hybrid Partitioned Tables: now large portions of a table can reside in external partitions, for example in the Oracle Cloud. With this new feature, you can also easily move non-active partitions to external files, such as Oracle Data Pump files, for a cheaper storage solution. Hybrid partitioned tables support all existing external table types for external partitions: ORACLE_DATAPUMP, ORACLE_LOADER, ORACLE_HDFS, ORACLE_HIVE.

9. Data Pump:

– Oracle Data Pump allows tablespaces to stay read-only during TTS import.
– Oracle Data Pump can work in test mode for transportable tablespaces.
– Oracle Data Pump supports resource usage limitations with the introduction of two new parameters: MAX_DATAPUMP_JOBS_PER_PDB and MAX_DATAPUMP_PARALLEL_PER_JOB.
– Data Pump no longer enables secure, password-protected roles by default. Beginning with 19c, you must explicitly enable password-protected roles for an individual export or import job. A new command-line parameter has been added, ENABLE_SECURE_ROLES=YES | NO that can be used to explicitly enable or disable these types of roles for an individual export or import job.
– The new Data Pump command-line parameter CREDENTIAL enables secure import into a managed service from dump files in the Oracle Object Store Service.

10. SQL Quarantine: “SQL statements that are terminated by Oracle Database Resource Manager due to their excessive consumption of CPU and I/O resources can be automatically quarantined. The execution plans associated with the terminated SQL statements are quarantined to prevent them from being executed again.”

Check out the new Oracle package DBMS_SQLQ – cool stuff!

DBA Statements

In DBA, Oracle database, PL/SQL, SQL on September 5, 2017 at 11:51

“A statement is persuasive and credible either because it is directly self-evident or because it appears to be proved from other statements that are so.” Aristotle

In Oracle 12.2, there is a new view called DBA_STATEMENTS. It can helps us understand better what SQL we have within our PL/SQL functions, procedures and packages.

There is too little on the Internet and nothing on Metalink about this new view:

PL/Scope was introduced with Oracle 11.1 and covered only PL/SQL. In 12.2, PL/Scope was enhanced by Oracle in order to report on the occurrences of static and dynamic SQL call sites in PL/SQL units.

PL/Scope can help you answer questions such as:
– Where and how a column x in table y is used in the PL/SQL code?
– Is the SQL in my application PL/SQL code compatible with TimesTen?
– What are the constants, variables and exceptions in my application that are declared but never used?
– Is my code at risk for SQL injection and what are the SQL statements with an optimizer hint coded in the application?
– Which SQL has a BULK COLLECT or EXECUTE IMMEDIATE clause?

Details can be found in the PL/Scope Database Development Guide or at Philipp Salvisberg’s blog.

Here is an example: how to find all “execute immediate” statements and all hints used in my PL/SQL units? If needed, you can limit the query to only RULE hints (for example).

1. You need to set the PLSCOPE_SETTINGS parameter and ensure SYSAUX has enough space:


SQL> SELECT SPACE_USAGE_KBYTES FROM V$SYSAUX_OCCUPANTS 
WHERE OCCUPANT_NAME='PL/SCOPE';

SPACE_USAGE_KBYTES
------------------
              1984

SQL> show parameter PLSCOPE_SETTINGS

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------
plscope_settings                     string      IDENTIFIERS:NONE

SQL> alter system set plscope_settings='STATEMENTS:ALL' scope=both;

System altered.

SQL> show parameter PLSCOPE_SETTINGS

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------
plscope_settings                     string      STATEMENTS:ALL

2. You must compile the PL/SQL units with the PLSCOPE_SETTINGS=’STATEMENTS:ALL’ to collect the metadata. SQL statement types that PL/Scope collects are: SELECT, UPDATE, INSERT, DELETE, MERGE, EXECUTE IMMEDIATE, SET TRANSACTION, LOCK TABLE, COMMIT, SAVEPOINT, ROLLBACK, OPEN, CLOSE and FETCH.


-- start
SQL> select TYPE, OBJECT_NAME, OBJECT_TYPE, HAS_HINT, 
SUBSTR(TEXT,1,LENGTH(TEXT)-INSTR(REVERSE(TEXT), '/*') +2 ) as "HINT" 
from DBA_STATEMENTS
where TYPE='EXECUTE IMMEDIATE' or HAS_HINT='YES';

TYPE              OBJECT_NAME   OBJECT_TYPE  HAS HINT
----------------- ------------- ------------ --- ------------------
EXECUTE IMMEDIATE LASKE_KAIKKI  PROCEDURE    NO  
SELECT            LASKE_KAIKKI  PROCEDURE    YES SELECT /*+ RULE */

-- end

Check also the DBA_STATEMENTS and ALL_STATEMENTS documentation. And the blog post by Jeff Smith entitled PL/Scope in Oracle Database 12c Release 2 and Oracle SQL Developer.

But finally, here is a way how to regenerate the SQL statements without the hints:


-- start
SQL> select TEXT from DBA_STATEMENTS where HAS_HINT='YES';

TEXT
------------------------------------------------------------
SELECT /*+ RULE */ NULL FROM DUAL WHERE SYSDATE = SYSDATE

SQL> select 'SELECT '||
TRIM(SUBSTR(TEXT, LENGTH(TEXT) - INSTR(REVERSE(TEXT), '/*') + 2))
as "SQL without HINT"
from DBA_STATEMENTS where HAS_HINT='YES';

SQL without HINT
-------------------------------------------------------------
SELECT NULL FROM DUAL WHERE SYSDATE = SYSDATE

-- end

Reading Data in Oracle Database 12c

In Cloud, Consolidation, Database options, DBA, Security and auditing, SQL on December 1, 2014 at 18:02

1. For DBAs and Developers, the words READ and SELECT have been for years somehow synonyms. In 12c, is there now any difference?

2. Before pluggable databases, selecting data from the SALES table for instance meant selecting data from a table called SALES in a certain SCHEMA within the database. How about if a table called SALES belongs to several pluggable databases under the same schema name?

The aim of this blog post is to shed some light on these new concepts.

lock_read

1. New READ privilege.

Until Oracle 12.1.0.2. the SELECT object privilege allowed users to perform the following two operations in addition to just reading data from the SALES table:

LOCK TABLE sales IN EXCLUSIVE MODE;
SELECT ... FROM sales FOR UPDATE;

These 2 commands enabled the users to lock the rows of the SALES table.

The READ object privilege does not provide these additional privileges. For better security, grant users the READ object privilege if you want to restrict them to performing queries only.

In addition to the READ object privilege, you can grant users the READ ANY TABLE privilege to enable them to query any table in the database.

When a user who has been granted the READ object privilege wants to perform a query, the user still must use the SELECT statement. There is no accompanying READ SQL statement for the READ object privilege.

The GRANT ALL PRIVILEGES TO user SQL statement includes the READ ANY TABLE system privilege. The GRANT ALL PRIVILEGES ON object TO user statement includes the READ object privilege.

If you want the user only to be able to query tables, views, materialized views, or synonyms, then grant the READ object privilege. For example:

GRANT READ ON SALES TO julian;

CDB_PDB

2. Querying a table owned by a common user across all PDBs.

Consider the following scenario:

– The container database has several pluggable databases, i.e., it has a separate PDB for each different office location of the company.
– Each PDB has a SALES table that tracks the sales of the office, i.e., the SALES table in each PDB contains different sales information.
– The root container also has an empty SALES table.
– The SALES table in each container is owned by the same common user.

To run a query that returns all of the sales across the company connect to each PDB as a common user, and create a view with the following statement:

CREATE OR REPLACE VIEW sales AS SELECT * FROM sales;

The common user that owns the view must be the same common user that owns the sales table in the root. After you run this statement in each PDB, the common user has a view named sales in each PDB.

With the root as the current container and the common user as the current user, run the following query with the CONTAINERS clause to return all of the sales in the sales table in all PDBs:

SELECT * FROM CONTAINERS(sales);

You can also query the view in specific containers. For example, the following SQL statement queries the view in the containers with a CON_ID of 3 and 4:

SELECT * FROM CONTAINERS(sales) WHERE CON_ID IN (3,4);

3. Delegate.

Something else: staring 12.1.0.2, when granting a role to a user, you can specify the WITH DELEGATE OPTION clause. Then the grantee can do the following two things:

A) Grant the role to a program unit in the grantee’s schema
B) Revoke the role from a program unit in the grantee’s schema

delegate

Oracle in-database archiving and invisible columns in 12c

In Database tuning, DBA, Oracle database, Oracle internals, SQL on September 30, 2013 at 01:15

“The true mystery of the world is the visible, not the invisible.” Oscar Wilde

invisible_woman

In-Database archiving is new to Oracle 12c. The idea behind it is to make rows invisible. This is very similar to the invisible column concept.

The two features, combined together, can make big data in the table look not that big to the application.

Check the demonstration from orafaq to see how it works. It is simple and rather trivial. Another example is given by Contract Oracle Limited.

What is really interesting is John Watson’s comment and example at the very end of the orafaq article. He shows the need for filters that cannot be accomplished with access. John suggest that “all indexes need to include the ora_archive_state column, perhaps appended to primary and unique key indexes and prepended to other indexes. If you don’t do this, you will find that many operations that could previously be completed without touching the table will require table scans”.

The AMIS Techonology blog pays attention to the very same thing:

The Oracle Documentations says the following: “In-Database Archiving enables you to archive rows within a table by marking them as inactive. These inactive rows are in the database and can be optimized using compression, but are not visible to an application. The data in these rows is available for compliance purposes if needed by setting a session parameter.

With In-Database Archiving you can store more data for a longer period of time within a single database, without compromising application performance. Archived data can be compressed to help improve backup performance, and updates to archived data can be deferred during application upgrades to improve the performance of upgrades.”

The AMIS Technology Blog says: Note the emphasis on ‘without compromising performance’. It would seem that such in line archived records are skipped in a smart way during full table scans and other records retrieval operations.

Yong Woo, shows in his short note 12c: In-Database Archival …good, but not good with an example how a ‘TABLE ACCESS BY INDEX ROWID’ can be brought to the execution plan because of the new pseudo column.

What I would like to show, is in a way also the opposite, that performance can be even better with Oracle in-database archiving. I have a table called clients and visibility is set to ACTIVE. Almost all rows of the table are “invisible”. Check how bytes and cost increase, once I set the visibility to ALL. And of course, the full table scan!


SQL> select * from clients;

ep1


SQL> alter session set row archival visibility = all;

Session altered.

SQL> select * from clients;

ep2

It is interesting to note that virtual columns can be made invisible:


SQL> alter table clients add (vip_status number 
     generated always as (power(status,status)) virtual);

Table altered.

SQL> alter table clients modify (vip_status invisible);

Table altered.

INVISIBLE columns are not supported in external tables, cluster tables, or temporary tables and you cannot make a system-generated hidden column visible.

You can not yet make a table invisible. If you start marking all columns from the table as invisible, at the very end you will get:

SQL> alter table clients modify (client_id invisible);
alter table clients modify (client_id invisible)
            *
ERROR at line 1:
ORA-54039: table must have at least one column that is not invisible

On a final note, I wonder what developers will do if they cannot insert a row into a table if a DBA adds an invisible column without a default value declared as NOT NULL 🙂

Pseudo cursors and invisible SQL

In DBA, Oracle database, Oracle internals, SQL on February 17, 2013 at 11:39

Do you ever wonder why Oracle Enterprise Manager and AWR reports show the SQL text for some SQL IDs as not available?

SQL_text_not_available

The DBA forums and discussion groups mention that it might be because the SQLs have already been flushed from the shared pool or wonder how do you get and confirm the SQL ID for a query without seeing the text of the query?

The truth is however slightly different. These are Oracle internal or so called pseudo cursors which are just structures providing direct access database objects by avoiding the overhead of writing an explicit SELECT statement. These structures access directly most of the time the data dictionary tables, CLOBs and BLOBs. Sometimes even NCHAR and NVARCHAR2.

This is the reason why these internal cursors show in OEM and AWR with the legend “SQL Text Not Available”. There is just no SQL.

They are used internally within the database and cannot be described. At least not completely. However, here is what you can do as suggested by MOS 1298471.1:

create or replace view h$pseudo_cursor as
select Pseudo_cursor, sql_id,obj_id hex_obj_id
     ,obj# object_id, u.name owner, o.name object_name
from (select distinct 
             KGLNAOBJ Pseudo_cursor,kglobt03 sql_id
        ,substr(KGLNAOBJ
               ,instr(KGLNAOBJ,'_',1,3)+1
               ,instr(KGLNAOBJ,'_',1,4)-instr(KGLNAOBJ,'_',1,3)-1) obj_id 
       ,(case when 
         replace(translate(substr(upper(KGLNAOBJ)
                                 ,instr(KGLNAOBJ,'_',1,3)+1
                                 ,instr(KGLNAOBJ,'_',1,4)
                                  -instr(KGLNAOBJ,'_',1,3)-1)
                          ,'0123456789ABCDEF','................')
                ,'.') is null then 'Y' else 'N' end) is_safe_to_compare
            from x$kglob) k
   , obj$ o, user$ u
where obj#=decode(is_safe_to_compare,'Y',to_number(obj_id,'xxxxxxxxxx'),0)
   and o.owner#=u.user#

Now, we can try to find out more about our invisible SQL:

col hex_obj_id  format a10
col pseudo_cursor format a30
col owner format a20
col object_name for A22
select * from h$pseudo_cursor where sql_id='grv54uyqkw2yc';

pseudo_cursors

The second query shows us the pseudo PL/SQL used by Oracle internally. Notice the missing value for SQL ID!

Now, here is a question: how to tune pseudo cursors? If you run STA (SQL Tuning Advisor), the error message you get will be “The SQL has aged out of the cursor cache”. OEM will notify you with “The SQL statement is not available”:

sql_text_not_available2

So the bottom line is: if you see messages about SQL Text not being available, it is most likely due to Oracle trying to access internally LOBs or its own tables.

SQL Tuning Advisor Internals

In Database tuning, DBA, Oracle database, Oracle internals, SQL on January 27, 2013 at 19:07

Douglas Adams, the author of “The Hitchhiker’s Guide to the Galaxy” said: “A common mistake that people make when trying to design something completely foolproof is to underestimate the ingenuity of complete fools”.

I am talking here about Oracle’s Cost Based Optimizer.

STA_01

The optimizer needs to make decisions about execution plans in a very short time: not all execution plans can be scanned. Staring Oracle 10g, Oracle allows the optimizer to run in tuning mode where it can gather additional information and make recommendations about how specific statements can be tuned further.

Funny thing here is that we rely on the same component (CBO) to improve the SQL statement that could not generate an optimal plan in the first place.

In this post, I would like to show something about Oracle 11gR2 that cannot be found either on Google or on Metalink. It is about tracing the SQL Tuning Advisor.

A very good review of what STA does was written by Tim Hall. Worth checking is also what Kerry Osborne writes about SQL Profiles. Cool stuff!

Christian Antognini’s article on SQL Profiles and how to trace it is probably the best written on that subject. Using the undocumented parameter _STN_TRACE, it is possible to trace what is happening during the SQL Tuning Advisor performance analysis. I find slight amusing the choice of the name of the domain used for Oracle FAQ but I guess this is not intentional.

However, if you check Jonathan Lewis’s article on SQL Profiles and the comments after it, you will notice that: _stn_trace does not seem to be available anymore in 11gR2″. That is visible from the _STN_TRACE documentation available on Oracle FAQ.

Starting 11gR2, there is an event trace[sql_manage.*] disk=low. Disk can be set to one of these 3 values: low, medium, or highest. The ora_debug_table is no longer needed or used.

You can set the event either at session or system level. If using OEM, then set it at system level, just like this:

alter system set events 'trace[sql_manage.*] disk=highest';

Once you run the SQL Tuning Advisor, go to either user_dump_dest or diagnostic_dest/diag/rdbms/SID/trace. You will find a long text file containing all analysis performed by STA.

The screenshot on the top of this post shows you the alternative execution plan offered by the SQL Tuning Advisor, the one below shows you the recommendations:

STA_02

The trace file generated because of enabling STA tracing on the highest level is way too big (20MB) but I am anyway posting it here.

This is twenty megabytes of text fitting into 8670 pages for what Oracle does for a single SQL statement!

STA_03

Tuning Exadata with parallel profiles and SPM baselines

In Database tuning, DBA, Exadata, SQL on October 29, 2012 at 06:34

Harold Geneen used to say: “It is much more difficult to measure nonperformance than performance”.

Without automation database management becomes increasingly difficult. An Exadata database can strongly benefit performance-wise from fully enabled Automatic SQL Tuning:

BEGIN
  DBMS_SQLTUNE.set_tuning_task_parameter(
    task_name => 'SYS_AUTO_SQL_TUNING_TASK',
    parameter => 'ACCEPT_SQL_PROFILES',
    value     => 'TRUE');
END;
/

I will explain why.

The SQL management base (SMB) is a part of the data dictionary that resides in the SYSAUX tablespace. It stores statement logs, plan histories, SQL plan baselines, and SQL profiles.

The difference between SQL Profiles and SQL Plan Management Baselines is very well explained by Maxym Kharchenko and Maria Colgan.

The process of automatic evolution of SQL Plan Baselines is described with a perfect example by Christian Antognini.

Converting baselines into profiles is almost straightforward but not simple, Sumit Bhatia explains how.

Now, for complex and long running SQL statements in Exadata, it is quite important to reduce the amount of data being transferred from the storage nodes to the database and to have the right degree of parallelism.

The former is achieved very much by smart scans which take place only when there are full scans or direct path reads. The blocks are not cached but they are returned to the PGA. The latter is even trickier. How in fact to determine the right degree of parallelism for an SQL statement?

Most often parallel_degree_policy is set to manual (default) and parallel_max_servers is sort of guessed. In my opinion, the right way is to use Oracle’s Auto DOP solution by enabling parallel_degree_policy to a non-default value. Let me quote Gwen Shapira:

There are two levels to Auto DOP:
Limited: when accessing tables and indexes that have been declared with parallel clause, Oracle will decide on the degree of parallelism based on the query and system resources
Auto: Oracle will decide on degree of parallelism for every query. In addition two new features are enabled: parallel statement queuing and in-memory parallel execution.

Even doing all that, because of Oracle’s unpredictable behavior at times, some SQL statements might not get the right number of parallel slaves or might not go for smart scans. For such statements creating an SPM baseline is a good thing to start from. Then, during the Automatic SQL Tuning maintenance window, the SQL Tuning Advisor might notice that there is a non-accepted plan which is better than the accepted one. The recommended SQL profile will simple do nothing else than to evolve the SPM baseline execution plan into an accepted plan.

All that happens automatically due to the fact that we have advised Oracle to automatically accept SQL Profiles.

But there is more to that: how to get a good degree of parallelism for the SQL statement? Oracle will automatically do so if needed by creating a parallel SQL Profile. Note that there are two types of profiles:

– REGULAR_PROFILE – profile without a change to parallel execution
– PX_PROFILE – regular profile with a change to parallel execution

Here is an example:

Automatically accepted parallel profiles can really boost the Exadata performance. But as PX_Profiles can consume also more resources than expected, it is important to have Instance Caging enabled.

The most important step for configuring instance caging is to determine the value of CPU_COUNT for each database instance and the sum of CPU_COUNTs across the server. There are two approaches:

– Partitioned approach for mission-critical pools sum(CPU_COUNT) < 75% x Total CPUs
– Over-subscribed approach for non-critical pools sum (CPU_COUNT) <= up to 3 x Total CPUs

Second step for instance caging: ensure resource_manager_plan is enabled.

Bottom line: tuning an Exadata database is not exactly the same things as tuning a traditional database. Actual tuning of an Exadata database is in fact a lot easier once understood.

And a lot of that tuning work can be really automated!

What is new with Automatic SQL Tuning in 11.2.0.2?

In DBA, Oracle database, SQL on April 24, 2011 at 14:32

When learning PL/1 and Fortran 4 in the very beginning of the 80s, I remember how the teacher was telling us that perhaps some day software will be able to create software. Something like that, I am paraphrasing. At that time software development was called programming.

With Oracle11, this became somehow true. What happens behind the curtain is that Oracle’s CBO is able to correct/change some bad execution plans without human intervention. Which actually turns out to be one of the best new features in 11g and for sure the best 11g new feature for Data Center DBAs.

But even in 11.2.0.2, the complete automation of Automatic SQL Tuning is not turned on by default. You have to manually enable it, just like this:

BEGIN
DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER(
parameter => 'ACCEPT_SQL_PROFILES', value => 'TRUE');
END;
/

After creating an 11g database, that is all you have to do in order to automate the process! For all the details, check the 11gR2 documentation on Automatic SQL Tuning.

The first time I did this in a mission critical production database, was in November 2007, as soon as Oracle 11g was available for Solaris. However, I did spend 100s of hours before that working/testing on 11.1.0.3 as an alpha tester.

In my opinion, starting 12g, accepting SQL profiles should be enabled by default. However, in order to use automatic SQL tuning and in particular SQL profiles the Tuning and the Diagnostics packs need to be licensed! Automatic SQL tuning via profiles is the most efficient way to massively tune SQL statements: CBO learns from its mistakes!

Back to 11.2.0.2: there are 3 new and updated performance tuning features:

• Resource Manager enhancements for parallel statement queuing and CPU utilization limit
• The Oracle Orion I/O Calibration Tool
• The new DBMS_AUTO_SQLTUNE package for Automatic SQL Tuning which is the new interface for manually managing the automatic SQL tuning task

Let us have a look at the 3 main subprograms of DBMS_AUTO_SQLTUNE:

Here are example of how you should use them:

1. Execute the Automatic SQL Tuning task immediately: EXEC DBMS_AUTO_SQLTUNE.EXECUTE_AUTO_TUNING_TASK; Note: must be run as SYS!

2. Display a text report of the automatic tuning task’s history: SELECT DBMS_AUTO_SQLTUNE.REPORT_AUTO_TUNING_TASK FROM DUAL;

3. Change a task parameter value for the daily automatic runs: see in the beginning how to enable automatic acceptance of SQL profiles. You can view all parameters as follows:

SELECT PARAMETER_NAME, PARAMETER_VALUE, IS_DEFAULT, DESCRIPTION
FROM DBA_ADVISOR_PARAMETERS
WHERE task_name = 'SYS_AUTO_SQL_TUNING_TASK'
/

I never understood why IE and Mozilla show me a mixture of Finnish and English in the description fields:

Back to the beginning of my post, have a look at how Oracle fixes its own errors, the Optimizer is optimizing its own execution plans! If you have not yet enabled Automatic SQL Tuning, do not heziate to do it, ask me for help or assistance if needed:

As of today when posting this, 24 April 2011, Metalink returns only 1 article (from all sources) on DBMS_AUTO_SQLTUNE, while I get almost 500 on DBMS_SQLTUNE:

If you are still interested in the topic check additionally:

DBMS_AUTO_SQLTUNE: ORA-01748 and Documentation Bugs
Morgan’s Library Oracle DBMS_AUTO_SQLTUNE Example Sample Code

Table Annotations in 11.2.0.2

In DBA, Oracle database, PL/SQL, SQL on April 22, 2011 at 10:12

11.2.0.2 is not just a patchset of 11gR2. It is more like 11gR3. Last month, I presented my favorite top 10 new features for DBAs. However, the new table annotation feature deserves special attention.

After a new release of Oracle, it is always worthwhile taking a look into DBA_TABLES (or USER_TABLES). Now, the last new column is called RESULT_CACHE. It has for now two possible values: DEFAULT and FORCE.

FORCE means that the table is annotated. That is, if all table names in a given SQL query have this setting, then the result set returned by the query is always considered for caching unless the NO_RESULT_CACHE hint is specified for the query. If one or more tables named in the query are set to DEFAULT, then the effective table annotation for that query is DEFAULT. Which means the result set will not be cached.

Let us see how it works by first creating a table with 15000 rows and run a full table scan query:

If you want to flush the result cache, just run: exec DBMS_RESULT_CACHE.FLUSH;

Here are the objects related to the topic, it is worth checking them:

Caching is not guaranteed by Oracle even if all tables in the query are annotated. The actual caching depends on internal restrictions for client and server cache, query cache worthiness and space available in the cache. Try to run in a row for instance sql with SYSDATE in it and have a look:

Queries that are not Cached:

• Remote objects
• Complex types in the select list
• Snapshot-based or flashback queries
• Queries executed in a serializable, read-only transaction, or inside a flashback session
• Queries that have PL/SQL functions in them
• Queries that have VPD policies enabled on the tables

How many records can Oracle store? All depends on two init.ora parameters: RESULT_CACHE_MAX_SIZE & RESULT_CACHE_MAX_RESULT:

RESULT_CACHE_MAX_SIZE specifies the maximum amount of SGA memory that can be used by the result cache. Values of this parameter greater than 0 are rounded up to the next multiple of 32 KB. If the value of this parameter is 0, then the feature is disabled. The default value is derived from the values of SHARED_POOL_SIZE, SGA_TARGET, and MEMORY_TARGET. By default, the cache takes 0.025% of the memory_target parameter’s value. However, the max allocation cannot exceed 75% of the size of the shared pool. With my current memory_target of 384M, the result cache max size does not even reach 1M. For production systems, I would recommend that you set it to at least 32M.

RESULT_CACHE_MAX_RESULT specifies the percentage of RESULT_CACHE_MAX_SIZE that any single result can use. The default value is 5% of the RESULT_CACHE_MAX_SIZE. This should be OK in most cases.

And finally, look at the execution plan when annotated tables are involved:

A good question is: so which tables from the database should I annotate? I would probably go through few AWR reports and try to find the repetitive queries run at least 100s of times. Then force annotate the tables used in the queries. And test first plus follow on regular basis what comes from V$RESULT_CACHE_OBJECTS! With Oracle at times you get the opposite of what is expected.

P.S. I often see in AWR reports how WebLogic runs 1000s of times per hour “select * from dual”. In a recent report, I saw the DUAL table as number one segment in terms of logical reads. You can annotate even system tables:

SQL> alter table dual result_cache (mode force);

Table altered.