Category: Top_Tip

How to use DBMS_STATS DIFF_TABLE_STATS functions

In 11g, Oracle introduced the DBMS_STAT.DIFF_TABLE_STATS functions to help you compare two sets of statistics for a table along with all its dependent objects (indexes, columns, partitions).

There are three versions of this function depending on where the statistics being compared are located:

  • DBMS_STAT.DIFF_TABLE_STATS_IN_HISTORY (compares statistics for a table from two timestamps in the past)
  • DBMS_STAT.DIFF_TABLE_STATS_IN_PENDING (compares pending statistics and statistics as of a timestamp or statistics from the data dictionary)
  • DBMS_STAT.DIFF_TABLE_STATS_IN_STATTAB (compares statistics from a user statistics table and the data dictionary, from two different user statistics tables, or a single user statistics table using two different STATSIDs)

The functions return a report that has three sections:

  1. Basic table statistics
    The report compares the basic table statistics (number of rows, blocks, etc.).
  2. Column statistics
    The second section of the report examines column statistics, including histograms.
  3. Index Statistics
    The final section of the report covers differences in index statistics.

Statistics will only be displayed in the report if the difference in the statistics exceeds a certain threshold (%). The threshold can be specified as an argument to the functions (PCTTHRESHOLD); the default value is 10%. The statistics corresponding to the first source, typically the current table stats in the data dictionary, will be used to compute the differential percentage.

The functions also return the MAXDIFFPCT (a number) along with the report. This is the maximum percentage difference between the statistics. These differences can come from the table, column, or index statistics.

Let’s look at an example.
Continue reading “How to use DBMS_STATS DIFF_TABLE_STATS functions”

SQL Tuning: How to determine why the Optimizer is picking the wrong index

One of the most common SQL Tuning challenges you will encounter with enterprise applications is a SQL statement where the Optimizer picks the wrong index. As tempting as it is to brute-force the plan you want via an index hint, it’s always better to understand why the Optimizer made the decision and provide the necessary information and access structures to allow the Optimizer to select the plan you want by default.

In the short video below, I explain how the Optimizer costs each of the index accesses available to it and provide you with a simple set of steps to help you identify these types of problems and guidance on how to create indexes so the Optimizer will automatically select them.

This post is part two of a series of blog posts on SQL Tuning. In part one, I shared some simple steps to help you tune a SQL Statement using the wrong Join Type.

SQL Tuning: How to tune a SQL Statement with the wrong Join Type

When it comes to SQL Tuning, I don’t typically recommend folks add one-off hints or look for magical underscore parameters to help improve their query performance.

Instead, I like to share some simple steps to

  1. Accurately characterise the problem
  2. Determine and apply a solution
  3. Measure the effectiveness of that solution

But instead of talking about my approach, I thought it would be more beneficial to show you how I do it. So in the video below, you will see the exact steps I used to determine what was causing a Sales Report to run slowly and the two alternative approaches you can take to resolve similar problems.

SQL Tuning Tips You Can’t Do Without

Last week, I enjoyed presenting at the aioug Sangam 20 on one of my favorite topics, SQL Tuning.

Often, we are led to believe you need a degree in wizardry to tune sub-optimal SQL statement, but in reality, you usually need to know where to look.

In the session, I look at four different SQL statements with sub-optimal plans and share details on where I look for information to help me understand why. Once I know the root cause of a problem, it’s easy to apply the appropriate solution.

Those who couldn’t make the session in person can download the presentation file from here.

Explain the Explain Plan: Cardinality Estimates

In last week’s post, I began a series on how to read and interpret Oracle execution plans by explaining what an execution plan is and how to generate one. This week I’m going to tackle the most important piece of information the Optimizer shares with you via the execution plan, it’s cardinality estimates.

What is a Cardinality Estimate?

A cardinality estimate is the estimated number of rows, the optimizer believes will be returned by a specific operation in the execution plan. The Optimizer determines the cardinality for each operation based on a complex set of formulas that use table and column level statistics as input (or the statistics derived by dynamic sampling). It’s considered the most important aspect of an execution plan because it strongly influences all of the other decisions the optimizer makes.

In part 4 of our series, I share some of the formulas used by the optimizer to estimate cardinalities, as well as showing you how to identify cardinalities in a plan. I also demonstrate multiple ways to determine if the cardinality estimates are accurate.

What can cause a Cardinality Misestimate and how do I fix it?

Several factors can lead to incorrect cardinality estimates even when the basic table and column statistics are up to date. In part 5 of our series, I explain the leading causes of cardinality misestimates and how you can address them.

Next weeks, instalment will be all about the different access methods available to the Optimizer and what you can do to encourage the optimizer to select the access method you want!

Don’t forget this series also covers, how to read an explain plan as well as the different join methods and join orders.

Don’t forget more information on the Oracle Optimizer can always be found on the Optimizer blog.

How to read a Parallel Execution Plan in Oracle

The volume of data being stored in databases has grown exponentially in recent years. So too has the need to rapidly generate value or business insights from that data.

Parallel execution is the key to processing large volumes of diverse data quickly, as it subdivides complex tasks into a number of small tasks allowing multiple processes to accomplish a single complex task.

However, the use of parallelism can complicate the execution plan displayed. Oracle not only displays the operations needed to complete the SQL statement in the plan but all of the communication steps between the parallel server processes.

So, how should you go about interpreting a parallel execution plan?

In the video below, I give you a step by step guide on how to read parallel plans and what additional information you can glean from them!

How to setup VNCServer on an OCI VM?

Now we are all working from home, I’ve noticed that my MAC laptop is severely overloaded when I do live demos during webinars. After all, it’s running my camera, Zoom, PowerPoint, my Java app, and monitoring tools.

So, I decided it was time to move my demo environment to the Oracle Cloud, where I quickly provisioned a 2-OCPU VM running Linux following the instructions in my previous blog post.

Screen Shot 2020-08-11 at 12.56.23 PM

Once I had my VM up and running, I wanted a proper desktop experience, so I needed VNC.

After a quick google search, I found the video below, which provides a very easy to follow, step by step guide to installing and configuring TigerVNC VNCServer on OCI infrastructure. I followed all of these steps except for the final stage where they describe adding the VNCServer to your firewall.

I’m married to a security expert, who strongly advised against this approach. He told me it would be far more secure to use an SSH tunnel instead of opening the firewall for the VNC port.

Below the video are the full set of commands I used in my setup, including how to establish the ssh tunnel, so you can quickly cut and paste them.

Continue reading “How to setup VNCServer on an OCI VM?”

How to Provision a VM in the Oracle Cloud?

This blog post outlines the 10 simple steps necessary to provision, and connect to, a VM on Oracle Cloud Infrastructure (OCI).

          1. Confirm you have an SSH Public Key on your laptop or localhost.
            $ cd ~/.ssh
            — check if you have an existing if you have a key already
            $ ls
            id_rsa    id_rsa.pub    known_hosts

            You’re looking for a file named either id_dsa or id_rsa and a matching file with a .pub extension. The .pub file is your public key, and the other file is the corresponding private key. If you don’t have these files or you don’t remember your passphrase, you will need to complete the steps outlined here.NOTE: You can’t move on unless you have your SSH Public key.
          2. Connect to the OCI console to begin the provisioning process.
            From the hamburger menu in the upper left-hand corner select, the Compute menu item followed by the Instances option.
          3. On the Instances page, click the Create Instance button.
            Step3_begin_Create_instance_2
          4. Specify a unique name for your instance and accept the default Oracle Linux image.
            Step4_name_instance
          5. Scroll down and click on the Change Shape button.
            Step5_change_shape
          6. To run my demos I typically use Swingbench, which needs a minimum of 2 OCPUs (4 OCPUs if you use the JSON workload). So, I select a Virtual Machine with Intel Skylake processors and  2 OCPUs. Then click the Select Shape button.
            Step6_pick_shape
          7. I use the automatic defaults in the Configure Networking and Boot Volume sections and move on to the SSH Key section. Here I select Paste SSH Keys and cut and paste my public key from my .ssh directory into the window provided.
            Picture7_ssh_key
          8. Finally hit the Create button at the end of the page.
          9. Instantly you will see a new VM is being provisioned for you. Once available, you can connect to the machine using the public IP address and the user OPC. You will find the IP address, on the main Instance console page.Step9_connect
          10. Simply ssh into the machine from your laptop using the supplied OPC user.

            $ ssh opc@XXX.XXX.XX.XXX
            Enter passphrase for key '/Users/sqlmaria/.ssh/id_rsa':
            Last login: Tue Jul 28 16:39:35 2020 from XXXXX

Generating an AWR report for Autonomous Databases

Oracle Autonomous Database automates the lifecycle management of a database, everything from provisioning, scaling, backups and patching, but what it doesn’t do yet is fully tune your application.

You are still on the hook to make sure your app doesn’t have any concurrency bottlenecks or poorly written SQL that Auto Indexing can’t address.

So, what can you do to monitor your app while it’s running on an Autonomous Database?

The first place you can start is the Performance Hub tab on the cloud console. Here you’ll find both real-time and historical performance data in the form of Active Session History (ASH) information for the last seven days, and SQL Monitor reports for the high-load SQL. You can aggregate the ASH data several different ways including by wait class, database service, resource group or SQL ID. The same information is also available in Oracle Management Cloud (OMC), and SQL Developer Web.

But if you are trying to get a holistic view of how your app is behaving on a database, nothing beats an Automatic Workload Repository report or AWR report.

Continue reading “Generating an AWR report for Autonomous Databases”

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”

%d bloggers like this: