Oracle Database 21c

Better diagnostics for SQL regressions in 19c and beyond #JoelKallmanDay

When diagnosing and correcting a performance regression for a SQL statement, it can often be challenging to find out what is happening during the execution and why your corrective measures are not working.

In today’s blog, I want to share several enhancements introduced in recent Oracle Database releases to help you better understand how to improve a SQL statement and understand why your corrective measures aren’t working as you hoped.

Enhanced Execution Plan

One of the most valuable tools at our disposal is the execution plan. Over the last several years, the Oracle Optimizer team has made a considerable effort to share even more insights and guidance on what might be impacting the performance of a SQL statement via the execution plan.

The Note section under the plan

The note section under the execution plan contains valuable information on what has happened during parse and execution. Over the last several releases, you will find a lot more information appearing in the note section, including details on the following features:

Dynamic Sampling – indicates a non-default value for the parameter OPTIMIZER_DYNAMIC_SAMPLING or that one or more objects referenced in the query are missing statistics.
Plan Stability Features – indicates that a store outline, SQL profile, SQL patch SQL directives, or SQL plan baseline influenced the plan chosen.
Adaptive plan – indicates that the plan shown through the Explain Plan command may be different from the plan used during execution as the Optimizer will use run-time statistics to make the final decision on what join types and aggregation methods to use. It is best to view the actual plan used via v$SQL_PLAN after the initial execution.
Statistics Feedback – indicates the plan was re-evaluated based on previous execution statistics, and a new plan was found.

Hint Usage Reporting in Oracle Database 19c

Starting in 19c, you will see a new hint usage report under the execution plan. The new report gives you a better understanding of what happened to each hint in a SQL statement.

Remember, the database doesn’t issue an error if a hint is invalid or not applicable for a SQL statement. The database will treat the hint as a comment, effectively ignoring it. Previously, the only way to find this information was via a 10053 trace file, and even then, it wasn’t very detailed.

The new hint report shows which hints were used and which were ignored and explains why hints weren’t used. The most common reasons for a hint not to be used are:

Syntax errors – the hint contains a typo or an invalid argument.
Unresolved hints – the hint contains an invalid argument or is not applicable to the SQL statement. For example, you request an index be used, but no index exists on the table.
Unused hints – the hint can’t be used in this specific scenario. For example, you requested a Hash Join for a non-equality join condition.
Conflicting hints – multiple hints provide conflicting directives.

In the example below, the hint NO_QURY_TRANSFORMATION was reported to contain a syntax error. The word query is misspelled in the hint, so the hint can’t be used.

---------------------------------------------------------------------------   
| Id  | Operation                      | Name     | Rows  | Bytes | Cost (%CPU)|   
--------------------------------------------------------------------------------    
|   0 | SELECT STATEMENT               |          |       |       |    47 (100)|    
|   1 |  HASH GROUP BY                 |          |   269 | 37929 |    47   (7)|    
|   2 |   HASH JOIN                    |          |   269 | 37929 |    46   (5)|    
|   3 |     TABLE ACCESS STORAGE FULL  | SALES    | 10000 | 90000 |     5   (0)|   
|*  4 |     TABLE ACCESS STORAGE FULL  | PRODUCTS | 43108 |  4841K|    40   (3)|    
--------------------------------------------------------------------------------    
Predicate Information (identified by operation id):                                            
---------------------------------------------------                                            
   4 - access("ITEM_1"="P"."PROD_ID")                                                          
   
Hint Report (identified by operation id / Query Block Name / Object Alias):                    
Total hints for statement: 1 (E - Syntax error (1))
--------------------------------------------------------------------------
 0 -  SEL$1                                                                                  
         E -  NO_QURY_TRANSFORMATION 
Note                                                                                           
-----
- dynamic statistics used: dynamic sampling (level=2)

In this second example, I provided two hints on how to access the employees table. One hint requested that the primary key index be used, and the other requested that the access leverage parallel execution.

SELECT /*+ index(e empno_pk_ind) parallel(e 8) */ e.empno, ename 
FROM    employees eWHERE   e.empno < 7700;

----------------------------------------------------------------------------
|Id  | Operation			     | Name	    | Rows | Bytes |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT		     |	            |	   |	   |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED | EMPLOYEES    |	 8 |	80 |
|*  2 |   INDEX RANGE SCAN		     | EMPNO_PK_IND |	 8 |	   |
----------------------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
	 U -  parallel(e 8)

The parallel hint is not used, as I’ve supplied an invalid combination of hints. An index range scan can’t be parallelized unless the index is partitioned, which is not true in this example. Therefore, the Optimizer can not honor both hints.

Note Nigel Bayliss, the Optimizer product manager, has also blogged about Optimizer hint reporting and has shared details on some limitations regarding execution hints such as GATHER_OPTIMIZER_STATISTICS, APPEND, etc.

SQL Analysis Report in Oracle Database 23c

In Oracle Database 23c, the execution plan got another new section: a SQL Analysis Report. This handy addition helps you diagnose common problems that can cause suboptimal execution plans. For example, the new report will point out situations where you are:

Missing join conditions
Have a WHERE clause predicate that prevents an index from being used
Have a datatype mismatch in a WHERE clause predicate
Using a UNION instead of a UNION ALL

EXPLAIN PLAN FOR
SELECT * FROM addresses
 WHERE UPPER(state) = 'CA';
 
SELECT * FROM dbms_xplan.display();
 
Plan hash value: 3184888728
 
--------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |   239 | 13384 |    61   (0)| 
|*  1 |  TABLE ACCESS FULL| ADDRESSES |   239 | 13384 |    61   (0)| 
--------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(UPPER("state")='CA')
 
SQL Analysis Report (identified by operation id/Query Block Name/Object Alias):
-----------------------------------------------------------------
 
   1 -  SEL$1 / "ADDRESSES"@"SEL$1" 
-  The following columns have predicates which preclude their use as keys in index range scan. Consider rewriting the predicates. "STATE"

Again, Nigel Bayliss has blogged about this in more detail on the official Optimizer blog.

Note that SQL Monitor active reports also contain SQL Analysis reports. You can find them under the new SQL Analysis tab.

New Optimizer Dictionary Views

Along with enhancements to the execution plan in 23c, we added new data dictionary views to help identify what happens when we parse and execute a SQL statement.

Apps and users frequently change the value for one or more parameters that impact the Optimizer at a session level. As a DBA or performance engineer, you are often unaware of these logon triggers or ALTER SESSION commands. The first hint that the environment may have changed is when you see a plan change.

To check if a change in the optimizer environment caused the plan change, you can check the column OPTIMIZER_ENV_HASH_VALUE in V$SQL, V$SQLAREA, and DBA_HIST_SQLSTAT. However, this column doesn’t tell you what has changed.

Starting in 23c, you can query a new dictionary view, DBA_HIST_OPTIMIZER_ENV_DETAILS, to find out exactly what has changed in the optimizer environment when we parsed and executed a SQL statement.

Using the optimizer_env_hash_value for the original and new plan, you can query DBA_HIST_OPTIMIZER_ENV_DETAILS and get a list of the parameters settings for each scenario, which you can then compare to find the difference.

SELECT DISTINCT optimizer_env_hash_value
FROM  dba_hist_sqlstat
WHERE sql_id = 'bsvavk15n7cra'
ORDER BY 1;

OPTIMIZER_ENV_HASH_VALUE                                                        
------------------------                                                        
              1309615723                                                        
              2369923737 

SELECT name, value
FROM dba_hist_optimizer_env_details
WHERE optimizer_env_hash_value = 1309615723
ORDER BY 1;

NAME                                               VALUE                        
-------------------------------------------------- -------------------------    
BlockChain_ledger_infrastructure                   0                            
CLI_internal_cursor                                0                            
PMO_altidx_rebuild                                 0                            
_adaptive_window_consolidator_enabled              true                         
_add_stale_mv_to_dependency_list                   true  
   .
   .

You can get more information on how to leverage the DBA_HIST_OPTIMIZER_ENV_DETAILS view in MOS NOTE:2953121.1 – Examining the Optimizer Environment within Which a SQL Statement was Parsed in AWR.

How to do a Fuzzy Text Search on JSON #JoelKallmanDay

More and more apps I deal with store data as JSON documents in the Oracle Database. It is exceptionally convenient for the developers but doesn’t always make it easy to know exactly what data we have stored.

The good news is that Oracle offers multiple ways to help you understand precisely what data you have stored in those JSON documents. For example, you can use the built-in JSON Data Guide, which will trawl through all your documents and return a list of all the attributes you have stored.

But suppose you are interested in searching through your documents and only returning those that contain a particular word, value, or a variation thereof. In that case, you will want to take advantage of Oracle’s fuzzy text search or approximate string matching.

Imagine we have a table that stores movie reviews as JSON documents. I’m doing this demo in 19c, so I’m using a VARCHAR2 column, but from 21c onwards, you can use a JSON column.

CREATE TABLE movie_reviews( 
                     title varchar2(200),
                     cust_id NUMBER(26), 
                     cust_reviews varchar2(32000)
                     CONSTRAINT cr_is_json CHECK (cust_reviews IS JSON));

If you really want a JSON column in 19c, you can take advantage of this trick I learned from @Connor_mc_d.

 CREATE TABLE movie_rewiews( title varchar2(200), 
                             cust_id NUMBER(26), 
                             cust_reviews BLOB,
                             CHECK (cust_reviews IS json format oson));

Each review document contains details on the movie id, the star rating, and the review.

{“movie_id”: 5641,
“star_rating”: 1,
"Feedback":" Loved the tv show, but hated the movie. I am so disappointed."
}

We have been asked to find all the reviews that contain the word disappoint or variations of it. To facilitate a fuzzy text search, we need to create a text-based index on the feedback column inside the review documents.

CREATE SEARCH INDEX review_search_ind ON movie_reviews(cust_reviews) FOR JSON;

Once the index is created, we can run a fuzzy text search using the following query:

SELECT m.title, m.cust_reviews.feedback AS customer_review
FROM   movie_reviews m
WHERE  JSON_TEXTCONTAINS(m.cust_reviews, '$.feedback', 'fuzzy((disappoint))');

This results in the following entries being returned.

TITLE COMMENTS -------------------- ---------------------------------------------------------------- Can You Ever Forgive Me? This movie was so disappointing Top Gun Tom Cruise never disappoints. Definitely worth a watch. Vice Perry’s performance in this movie is just so disappointing Baywatch Loved the tv show but hated the movie. I’m so disappointed. La La Land Rent this movie you won’t be disappointed! Batman Complete Disappointment

Alternatively, you can use the abbreviated syntax, which will return the same results as above:

SELECT m.cust_reviews.feedback AS comments
FROM movie_reviews m
WHERE JSON_TEXTCONTAINS(cust_reviews, '$.feedback', '?disapoint');

You can also use the stem search operator $. That will match verb forms sharing the same stem, so $disappoint will match “disappointing,” “disappointed,” and “disappoints,” but not “disappointment.”

SELECT m.cust_reviews.feedback AS comments
FROM movie_reviews m
WHERE JSON_TEXTCONTAINS(cust_reviews, '$.feedback', '$disapoint');

This blog was made possible by the lovely Roger Ford, the product manager for Oracle Text and JSON, who has taught me everything I know about text searches in the Oracle Database.

Why Oracle Implement Blockchain in the Database

The primary focus of conventional data security technologies like passwords, firewalls, and data encryption is to keep criminals out of your company and your data stores.

But what protects your data, especially your essential asset (contracts, property titles, account statements, etc.), from being modified or even deleted by folks who gain access to your systems legitimately or illegitimately (hackers)?

Crypto-secure Data Management

This is where Blockchain can help. Layering Blockchain technologies on top of conventional data security features provide an extra level of protection that prevents illicit modifications or deletes of data.

What is Blockchain?

When we think of Blockchain, many of us instantly think of decentralized peer to peer apps that only permit consensus-based data changes. However, adopting these apps requires new development methodologies, speciality data stores and potentially new business practices, which is complicated and expensive!

But if we take a closer look at Blockchain technologies, we see four critical components; immutability, cryptographic digests, cryptographic signatures, and distributed systems. Each part works to protect against a different aspect of illicit data changes performed using legitimate user credentials or by hackers.

Integrating these Blockchain technologies into the Oracle Database brings the critical security benefits of Blockchain to mainstream applications with minimal or no changes required. Providing the full functionality of the world’s leading database on crypto-protected data.

In the video below, Juan Loaiza explains how Oracle implemented Blockchain technologies in the Oracle Database and how they can be used to protect your essential business data. I’ve also included a brief description of these features under the video.

How do Blockchain technologies work in the Oracle Database?

To protect against illicit data changes made by rogue insiders or malicious actors using insiders’ credentials, Oracle has introduced Immutable tables (insert-only tables) in Oracle Database 21c (21.3).

Immutable Tables

With an Immutable table, it is possible to insert new data, but existing data cannot be changed or deleted by anyone using the database, even the database administrators (SYSDBA). It is also impossible to change an immutable table’s definition or convert it to an updatable table. However, an Immutable table appears like any other table in the database from an application’s point of view. It can store both relational data and JSON documents, and it can be indexed and partitioned or used as the basis of a view.

Blockchain Tables

To protect against illicit changes made by hackers, Oracle has introduced Blockchain tables. Blockchain tables are immutable tables that organize rows into several chains. Each row, except the first row in the chain, is chained to the previous row via a cryptographic digest or hash. The hash is automatically calculated on insert based on that row’s data and the hash value of the previous row in the chain. Timestamps are also recorded for each row on insertion.

Any modification to data in a Blockchain table breaks the cryptographic chain because the hash value of the row will change. You can verify the contents of a blockchain table have not been modified since they were inserted using the DBMS_BLOCKCHAIN_TABLE.VERIFY_ROWS procedure.

DECLARE
actual_rows NUMBER;
verified_rows NUMBER;
 
BEGIN
 
SELECT COUNT(*)
INTO actual_rows
FROM admin.my_bc_tab;
 
dbms_blockchain_table.verify_rows(
schema_name =&gt; 'admin',
table_name =&gt; 'MY_BC_TAB',
number_of_rows_verified =&gt; verified_rows);
 
DBMS_OUTPUT.put_line('Actual_rows='||actual_rows|| ' Verified Rows=' || verified_rows);
END;
/

End-User Data Signing

Even with Immutable or Blockchain tables, data can be falsely inserted in an end user’s name by someone using stolen credentials. To address this vulnerability, Oracle allows end-users to cryptographically sign the data they insert using their private key that is never passed to the database.

Each end-user registers a digital certificate containing their public key with this database. This digital certificate allows the database to validate the end-users signature when new data is inserted. Even if a hacker manages to steal a valid set of credentials without the private key, the data insert signature won’t match and will therefore not be accepted.

It’s also possible for end-users to ensure the database has received their changes by requesting Oracle countersign the newly inserted data. Oracle returns a crypto-receipt to the user, ensuring nothing on the mid-tier can filter specific data to prevent it from being recorded.

Distributing Cryptographic Digest

Even with cryptographically chained rows, sophisticated cyber-criminals or authorities could illicitly change data via a large-scale cover-up, where the entire database is replaced. To detect such a cover-up, Oracle enables schema owners to sign and distribute the cryptographic digest for a blockchain table periodically. Remember, the digest can’t be used to infer the data in the table, but authorized users can use it to validate the chain and confirm their newly inserted data is present. The crypto-digest can be posted to an independent public store or blockchain, like Ethereum or sent out by email or made available via a REST API.

A cover-up can easily be detected by comparing the previously published digests to the current table content. Also, distributing the publicly across multiple independent services prevents an authority or cyber-attacker from deleting all the separate copies.

Getting Started With Blockchain

Both Immutable and Blockchain tables are free features of the Oracle Database. No additional licenses or software is needed to take advantage of these new table types, which are completely transparent to all new and existing applications.

Also, note Oracle has backported Immutable tables and Blockchain tables to Oracle Database 19c (19.11 and 19.10, respectively). Please check My Oracle Support for more details before attempting to use Blockchain tables in 19.10.

For more information on Blockchain check out the Oracle Blockchain blog, Oracle Blockchain LiveLabs or the Oracle Blockchain documentation.