Category: Syntax

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”

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”

JEFF Talks From Kscope18

The first day of the ODTUG Kscope conference is always symposium Sunday. This year’s Database symposium, organized by @ThatJeffSmith, consisted of multiple, short, rapid  sessions, covering a wide variety of database and database tool topics, similar to Ted Talks but we called then JEFF Talks!

I was lucky enough to present 3 of this year’s JEFF Talks that I thought I would share on my blog since there wasn’t a way to uploaded to the conference site.

In the first session I covered  5 useful tips for getting the most out of your Indexes, including topics like reverse key indexes, partial indexes, and invisible indexes.

Next up was my session on JSON and the Oracle Database. In this session, I covered topics like what data type you should use to store JSON documents (varchar2, clob or blob) the pros and cons of using an IS JSON check constraint, and how to load, index, and query JSON documents.

In my finally JEFF talk I covered some of the useful PL/SQL packages that are automatically supplied with the Oracle Database. Since the talk was only 15 minutes I only touched on 4 of the 300 supplied packages you get with Oracle Database 18c but hopefully it will give you enough of a taste to get you interested in investigating some of the others!

 

 

Using DBMS_XPLAN.DISPLAY_CURSOR to examine Execution Plans

In last week’s post I described how to use SQL Monitor to determine what is happening during the execution of long-running SQL statements. Shortly after the post went up, I got some requests on both social media and via the blog comments asking, “If it is possible to get the same information from a traditional text-based execution plan, as not everyone has access to SQL Monitor?”.

The answer is yes, it is possible to see a lot of the information shown in SQL Monitor by viewing the execution plan via the DBMS_XPLAN.DISPLAY_CURSOR function. In order to call this function, you will need SELECT or READ privilege on the fixed views V$SQL_PLAN_STATISTICS_ALL, V$SQL, and V$SQL_PLAN, otherwise, you’ll get an error message.

The DBMS_XPLAN.DISPLAY_CURSOR function takes three parameters:

  1. SQL ID – default null, means the last SQL statement executed in this session
  2. CURSOR_CHILD_NO – default 0
  3. FORMAT – Controls the level of details that will be displayed in the execution plan, default TYPICAL.

The video below demonstrates how you can use the FORMAT parameter within the DBMS_XPLAN.DISPLAY_CURSOR function to show you information about what’s happened during an execution plan including the bind variable values used, the actual number of rows returned by each step, and how much time was spent on each step.

Under the video you will find all of the commands used, so you can cut and paste them easily.

How do I see the actual number of rows and elapse time for each step in the plan?

Continue reading “Using DBMS_XPLAN.DISPLAY_CURSOR to examine Execution Plans”

Extremely useful Partitioning enhancements in Oracle Database 12c

Back in January, I promised I would write a series of blogs on some of my favorite new 12c enhancements, designed to make your life easier. I’m finally getting around to keeping that promise with this weeks blog post on my favorite partitioning enhancements.

Imagine you have a large SALES table that contains information on all of the sales we have had in our chain of department stores.

DESC sales
 Name	          NULL?    TYPE
 ----------     -------- ------------------------
 PROD_ID        NOT NULL NUMBER(6)
 CUST_ID        NOT NULL NUMBER
 TIME_ID        NOT NULL DATE
 CHANNEL_ID     NOT NULL CHAR(1)
 PROMO_ID	NOT NULL NUMBER(6)
 QUANTITY_SOLD	NOT NULL NUMBER(3)
 AMOUNT_SOLD	NOT NULL NUMBER(10,2)
 REGION 	NOT NULL CHAR(3)

There are also 4 indexes on our SALES table to help with our analytic queries.

Continue reading “Extremely useful Partitioning enhancements in Oracle Database 12c”

Optimizer Histograms

At the recent OUG Ireland conference I had the privilege of participating in a panel discussion on the Oracle Database. During the course of the session the topic of Optimizer histograms came up. As always, a heated discussion ensued among the members of the panel, as we each had very different views on the subject.

Why so many different opinions when it comes to histograms?

The problem arises from the fact that some folks have been burnt by histograms in the past. In Oracle Database 9i and 10g, histograms in combination with bind-peeking lead to some unpredictable performance problems, which is explained in detail in this post on the  Optimizer blog.  This has resulted in a number of folks becoming histogram shy. In fact, I reckon if you were to put 3 Oracle experts on a panel, you would get at least 5 different opinions on when and how you should gather histograms!

So I thought it would be a good idea to explain some of the common misconceptions that surround histograms and the impact of adopting them.

This is a long post, so you might want to grab a coffee before you get into it!

Continue reading “Optimizer Histograms”

New Top-N Queries and Pagination Syntax

At the RMOUG Training Days, a couple of weeks, ago @GeraldVenzl and I delivered a session demonstrating what it would take to get a REST enabled, web-based application up and running with Oracle Database 12c. During the session Gerald asked me to write a query to calculate the top 10 customers we had in terms of their total spend. Below is the query I came up with.

SELECT c.c_name, 
       c.c_custid loyaltyCardNo, 
       t.city, 
       t.state,
       SUM(salesAmount) total
FROM   customers c,
       t
WHERE  c.c_custid = t.loyaltyCardNo
GROUP BY c.c_name,
         c.c_custid,
         t.city,
         t.state
ORDER BY total DESC
FETCH FIRST 10 ROWS ONLY;

After the session I got a number of questions regarding the “FETCH FIRST 10 ROWS ONLY” syntax that I used, so I thought it would be worth explaining what it is and what happens under the covers when you execute it.

What is FETCH FIRST X ROWS ONLY?

Continue reading “New Top-N Queries and Pagination Syntax”

%d bloggers like this: