Dontcheff

Archive for the ‘New features’ 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 *‘?

Advertisements

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!