Category: Oracle Database 18c

How to determine which view is the right view to use in Oracle?

Database views are a handy tool to help obfuscate complex joins from developers and analysts. However, knowing which view to use can be tricky, especially when faced with multiple views with similar-sounding names. After all, you don’t want to join various tables only to discover that all the information you needed was in just a single table. But how do you know which view has the data you are after and will allow you to get it with the minimum number of joins?

The DBMS_UTILITY.EXPAND_SQL_TEXT procedure expands any references to a view within a query, turning it into a subquery in the original statement that displays the full query text behind that view. This trick lets you see where and how the needed data will be retrieved.

Let’s look at an elementary example of this procedure in action, using a SELECT * statement on a view called SALES_V.

SET serveroutput ON 
DECLARE 
    l_clob CLOB; 
BEGIN 
    dbms_utility.Expand_sql_text(input_sql_text => 'SELECT * FROM sales_v', output_sql_text => l_clob); 
    dbms_output.Put_line(l_clob); 
END; 
/

The result of this procedure call is the following output.

SELECT "A1"."order_id" "ORDER_ID", 
       "A1"."time_id"  "TIME_ID", 
       "A1"."cust_id"  "CUST_ID", 
       "A1"."prod_id"  "PROD_ID" 
FROM   (SELECT "A3"."order_id" "ORDER_ID", 
               "A3"."time_id"  "TIME_ID", 
               "A3"."cust_id"  "CUST_ID", 
               "A3"."prod_id"  "PROD_ID" 
        FROM   "SH"."sales" "A3", 
               "SH"."products" "A2" 
        WHERE  "A3"."prod_id" = "A2"."prod_id") "A1"

The subquery with the alias A1 above is the view definition for SALES_V.

It’s a simple two-table join between SALES (alias A3) and PRODUCTS (alias A2). Although the view only returns columns from the SALES table (A3), it does come with the overhead of a join. The execution plan for our simple SELECT * query below shows that.

PLAN_TABLE_OUTPUT                                                                          
__________________________________________________________________________________________ 
Plan hash VALUE: 2857462611                                                                
 
---------------------------------------------------------------------------------------    
| Id  | Operation                  | Name     | ROWS  | Bytes | Cost (%CPU)| TIME     |    
---------------------------------------------------------------------------------------    
|   0 | SELECT STATEMENT           |          |  9773 |   314K|     7   (0)| 00:00:01 |    
|*  1 |  HASH JOIN                 |          |  9773 |   314K|     7   (0)| 00:00:01 |    
|   2 |   TABLE ACCESS STORAGE FULL| PRODUCTS |  4999 | 29994 |     2   (0)| 00:00:01 |    
|   3 |   TABLE ACCESS STORAGE FULL| SALES    | 10000 |   263K|     5   (0)| 00:00:01 |    
---------------------------------------------------------------------------------------    
 
Predicate Information (IDENTIFIED BY operation id):                                        
---------------------------------------------------                                        
 
   1 - access("S"."PROD_ID"="P"."PROD_ID")

In this case, it would be best to find an alternative view that only accesses the SALES table or access the SALES table directly. Below is the plan for a direct select statement from the SALES table, and as you can see, the cost of this plan is lower.

EXPLAIN PLAN FOR
SELECT s.order_id, s.date_id, s.cust_id, s.product_id 
FROM sales s;
 
Explained.
 
SELECT * FROM TABLE(dbms_xplan.display());
 
PLAN_TABLE_OUTPUT                                                                      
______________________________________________________________________________________ 
Plan hash VALUE: 781590677                                                             
 
-----------------------------------------------------------------------------------    
| Id  | Operation                 | Name  | ROWS  | Bytes | Cost (%CPU)| TIME     |    
-----------------------------------------------------------------------------------    
|   0 | SELECT STATEMENT          |       | 10000 |   263K|     5   (0)| 00:00:01 |    
|   1 |  TABLE ACCESS STORAGE FULL| SALES | 10000 |   263K|     5   (0)| 00:00:01 |    
-----------------------------------------------------------------------------------

Over the last couple of releases, Oracle has added several handy PL/SQL packages and procedures you might not know about. So, I put together a short blog series highlighting some of my favorites. This blog post is part of that series. Other blogs in this series include How to add a SLEEP COMMAND to your Oracle PL/SQL code and How diff optimizer statistics.

How do I add a SLEEP to a PL/SQL Stored Procedure?

Over the last couple of releases, Oracle has added several handy PL/SQL packages and procedures you might not know about. So, I put together a short blog series highlighting some of my favorites. First up, DBMS_SESSION.SLEEP().

Oracle has always enabled you to add a sleep command to your stored procedures to suspend a session for a specified number of seconds, as shown in the code below.

    DECLARE
       v_start TIMESTAMP;
       v_end   TIMESTAMP; 
    BEGIN
       v_start := SYSTIMESTAMP;
       -- Sleep for 10 seconds
       DBMS_LOCK.SLEEP(10);
       v_end   := SYSTIMESTAMP;
       DBMS_OUTPUT.PUT_LINE('This procedure started at ' ||v_start);
       DBMS_OUTPUT.PUT_LINE('This procedure ended   at ' ||v_end);
    END;
   /
 
This PROCEDURE started AT 10-SEP-22 12.39.40.587041 AM
This PROCEDURE ended   AT 10-SEP-22 12.39.50.637738 AM
 
PL/SQL PROCEDURE successfully completed.
Elapsed: 00:00:10.02

However, the sleep function was part of the DBMS_LOCK package, which is not granted to PUBLIC, by default, due to the other more powerful functions inside that package. That means you had to beg the DBA or the security team to give you access to this package just to put your session to sleep for a few minutes.

DBMS_SESSION.SLEEP()

Things got a lot easier starting in Oracle Database 18c, as the sleep function is now available in the DBMS_SESSION package, which is granted to PUBLIC by default. That means you can call the function without any additional privileges. Plus, the function code in DBMS_SESSION.SLEEP is identical to DBMS_LOCK.SLEEP, so you can do a simple find and replace in your code!

DECLARE
       v_start TIMESTAMP;
       v_end   TIMESTAMP; 
    BEGIN
       v_start := SYSTIMESTAMP;
       -- Sleep for 10 seconds
       DBMS_SESSION.SLEEP(10);
       v_end   := SYSTIMESTAMP;
       DBMS_OUTPUT.PUT_LINE('This procedure started at ' ||v_start);
       DBMS_OUTPUT.PUT_LINE('This procedure ended   at ' ||v_end);
   END;
   /
This PROCEDURE started AT 10-SEP-22 12.39.40.587041 AM
This PROCEDURE ended   AT 10-SEP-22 12.39.50.637738 AM
PL/SQL PROCEDURE successfully completed.
Elapsed: 00:00:10.02

SQL Tuning: How to fix a SQL statement that picks a Nested Loop instead of a Hash Join

At one point or another during your career, you will face a situation where the optimizer picks the wrong join method. As tempting as it is to brute-force the plan you want via a hint, it’s always better to understand why the Optimizer made a decision and provide the necessary information to allow the Optimizer to select the plan you want by default.

In this short video below, I explain how the Optimizer determines the join method based on the cardinality of the table on the left-hand side of the join. I also provide a simple set of steps to help you identify the common problems that can cause an incorrect join method to be used and guidance on how to supply the necessary information, so the Optimizer will automatically select the appropriate join method.

Adaptive joins, introduced in 12c, can help address some cases where the Optimizer selects the wrong join method but not in all cases. It’s always better to know how to save yourself rather than relying on the built-in safety net.

Happy Tuning!

What are Query Block Names and how to find them

I got a lot of follow-up questions on what Query Block names are and how to find them, after my recent post about using SQL Patches to influence execution plans. Rather than burying my responses in the comment section under that post, I thought it would be more useful to do a quick post on it.

What are query blocks?

A query block is a basic unit of SQL. For example, any inline view or subquery of a SQL statement are considered separate query blocks to the outer query.

The simple query below has just one sub-query, but it has two Query Blocks—one for the outer SELECT and one for the subquery SELECT.

Oracle automatically names each query block in a SQL statement based on the keyword using the following sort of name; sel$1, ins$2, upd$3, del$4, cri$5, mrg$6, set$7, misc$8, etc.

Given there are two SELECT statements in our query, the query block names will begin with SEL. The outer query will be SEL$1 and the inner query SEL$2.

How do I find the name of a query block?

To find the Query Block name, you can set the FORMAT parameter to ‘+alias’ in the DBMS_XPLAN.DISPLAY_CURSOR command. This will display the contents of the OBJECT_ALIAS column in the PLAN_TABLE, as a new section under the execution plan.

The new section will list the Query Block name for each of the lines in the plan.

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format=\>'+alias'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
SQL_ID 4c8bfsduxhyht, child NUMBER 0
-------------------------------------
SELECT e.ename, e.deptno FROM emp e WHERE e.deptno IN (SELECT d.deptno 
FROM dept d WHERE d.loc='DALLAS')
Plan hash VALUE: 2484013818
---------------------------------------------------------------------------
| Id  | Operation	   | Name | ROWS  | Bytes | Cost (%CPU)| TIME	  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |	  |	  |	5 (100)|	  |
|*  1 |  HASH JOIN SEMI    |	  |	5 |   205 |	5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   280 |	2   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| DEPT |	1 |    21 |	2   (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (IDENTIFIED BY operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
2 - SEL$5DA710D3 / E@SEL$1
3 - SEL$5DA710D3 / D@SEL$2
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
1 - access("E"."DEPTNO"="D"."DEPTNO")
3 - FILTER("D"."LOC"='DALLAS')

As you can see, @SEL1 is the Query Block name for the outer query, where the EMP table is used, and @SEL2 is the Query Block name for the sub-query, where the DEPT tables is used.

Continue reading “What are Query Block Names and how to find them”

SQL Tuning Workshop

Last week I had the pleasure of delivering a five-part SQL Tuning Workshop for my local Oracle User Group –  Northern California Oracle User Group. The workshop explains the fundamentals of the cost-based Optimizer, the statistics that feed it, the hints that influence it, and the key tools you need to examine execution plans.

The workshop also provides a methodology for diagnosing and resolving the most common SQL execution performance problems. Given the volume of interest in this content, I want to share all of the material from the workshop here and give you links to additional material on each of the five topics.

Part 1 Understanding the Oracle Optimizer

The first part of the workshop covers the history of the Oracle Optimizer and explains the first thing the Optimizer does when it begins to optimize a query – query transformation.

Query transformations or the rewriting of the SQL statement into a semantically equivalent statement allows the Optimizer to consider alternative methods of processing or executing that query, which are often more efficient than the original SQL statement would allow. Most of Oracle’s query transactions are now cost-based, which means the Optimizer will cost the plan with and with the query transformation and pick the plan with the lowest cost. With the help of the Optimizer development team, I’ve already blogged about a number of these transformations, including:

You can also download the slides here.

Part 2 Best Practices for Managing Optimizer Statistics

Part 2 of the workshop focuses on Optimizer Statistics and the best practices for managing them, including when and how to gather statistics, including fixed object statistics.
Continue reading “SQL Tuning Workshop”

Can I use an Autonomous Database to develop new applications?

Yes, Oracle Autonomous Database (ADB) is the ideal platform for new application development.

With this family of cloud services, developers no longer have to wait on others to provision hardware, install software, and create a database for them. With ADB, developers can easily and instantly deploy an Oracle database without worrying about having to manual tune it or capacity planning. This allows developers to start developing in minutes and concentrate on solving business problems without all of the usual distractions.

ADB has the most advanced SQL and PL/SQL support accelerating developer productivity by minimizing the amount of application code required to implement complex business logic. It also has a complete set of integrated Machine Learning algorithms, simplifying the development of applications that perform real-time predictions such as personalized shopping recommendations, customer churn rates, and fraud detection.

What Development Tools should I use with ATP?

Continue reading “Can I use an Autonomous Database to develop new applications?”

Getting started with Oracle Autonomous Transaction Processing

Getting started with Oracle Autonomous Transaction Processing is actually much easier than you might think. In fact, with Oracle’s $300 in free cloud credits you can probably get your first 30 days on the service for free. Please note, you will require an active email address and credit card in order to sign up for a trial account. Of course, if you have existing cloud credits you can skip this step.

Once you sign up for trail account you’ll get an email with your tenancy, username and password. Armed with this information, head on over to https://cloud.oracle.com to sign in. The video below explains in detailed the simple steps needed to provision a new Autonomous Transaction Processing database. I’ve also listed these steps below the video, for  easy reference.

Continue reading “Getting started with Oracle Autonomous Transaction Processing”

What you can expect from Oracle Autonomous Transaction Processing

Today Larry Ellison announced the general availability of Oracle Autonomous Transaction Processing (ATP), the newest member of the Oracle Autonomous Database family, combining the flexibility of cloud with the power of machine learning to deliver data management as a service.

Traditionally, creating a database management system required a team of experts to custom build and manually maintain a complex hardware and software stack. With each system being unique, this approach led to poor economies of scale and a lack of the agility typically needed to give the business a competitive edge.

ATP enables businesses to safely run a complex mix of high-performance transactions, reporting, and batch processing using the most secure, available, performant, and proven platform – Oracle Database on Exadata in the cloud. Unlike manually managed transaction processing databases, ATP provides instant, elastic compute and storage, so only the required resources are provisioned at any given time, decreasing runtime costs.

But what does the Autonomous in Autonomous Transaction Processing really mean?

Self-Driving

ATP is a self-driving database, meaning it eliminates the human labor needed to provision, secure, update, monitor, backup, and troubleshooting a database.  This reduction in database maintenance tasks, reducing costs and freeing scarce administrator resources to work on higher value tasks.

Continue reading “What you can expect from Oracle Autonomous Transaction Processing”

How does Autonomous Transaction Processing differ from the Autonomous Data Warehouse?

In my previous post, I explained that  Oracle Autonomous Transaction Processing has three main attributes: Self-Driving, Self-Securing and Self-Repairing. All of the functionality I described in that post is shared between both the Autonomous Data Warehouse (ADW) and ATP.

Where the two services differ is actually inside the database itself. Although both services use Oracle Database 19c, they have been optimized differently to support two very different but complementary workloads. The primary goal of ADW is to achieve fast complex analytics, while ATP has been designed to efficiently execute a high volume of simple transactions.

Configuration

The differences in the two services begin with how we configure them. Continue reading “How does Autonomous Transaction Processing differ from the Autonomous Data Warehouse?”

Oracle Database 18c is now available for Download!

Today Oracle officially released Oracle Database 18c for download on Linux x86-64.

As you may recall, we originally released 18c on the Oracle Public Cloud and Oracle Engineered Systems back in February.

So, when will you be able to get your hands on 18c on-premises for other platforms?

You can check the Oracle Support document 742060.1 for more details!

18c is the first version of the database to follow the new yearly release model and you can find more details on the release model change in the Oracle Support Document 2285040.1.

Before you freak out about the fact you haven’t even upgraded to 12.2, so how on earth are you ever going to get to 18c – Don’t Panic!

Oracle Database 18c is in fact “Oracle Database 12c Release 2 12.2.0.2”, the name has simply been changed to reflect the year in which the product is released.

So, what can you expect?

As you’d imagine a patchset doesn’t contain any seismic changes in functionality but there are lots of small but extremely useful incremental improvements, most of which focus on the three key marquee features in Oracle Database 12c Release2:

More details on what has changed in each of these areas and other improvements can be found in the Oracle Database blog post published by Dominic Giles or in the video below with Penny Avril, VP of Database Product Management.

You can also read all about the new features in the 18c documentation and you can try out Oracle Database 18c on LiveSQL.

 

%d bloggers like this: