EXPLAIN PLAN, V$SQL_PLAN and DBMS_XPLAN

In some of our previous posts, we have talked about DBMS_XPLAN package in Oracle and have demonstrated it’s usage. One of the recent questions from our readers was: “What is the difference between using “EXPLAIN PLAN command” vs using the dictionary view: V$SQL_PLAN? And can you elaborate a bit on the different options available in dbms_xplan package along with some examples?”

This blog post is to address those questions. The answer to the first question is pretty straight forward. EXPLAIN PLAN command is used to display the execution plan of a SQL statement without actually executing it. On the other hand, v$sql_plan dictionary view is used to show the execution plan of the SQL statement that has been compiled into a cursor in the cache. Besides this difference, another difference is that the EXPLAIN PLAN command does not take the bind variables into consideration whereas v$sql_plan displays the plan that takes the bind variables into account.

The answer to the second question is a little detailed and rather than going into each and every option, we will point you to the Oracle documentation in this regard:

http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_xplan.htm#sthref15004

However, we would like to point out a couple of things that we have seen that people typically miss out on. As you probably already know, dbms_xplan package has several functions that help to display the plan from: a) EXPLAIN PLAN command, b) v$sql_plan, c) AWR (Automatic Workload Repository), and d) STS (SQL Tuning Set). The functions to read up on in the documentation link above are:

a) display
b) display_cursor
c) display_sql_plan_baseline
d) display_awr
e) display_sqlset

One thing that we want to point out is regarding the format argument. This argument allows you to see a lot of details about the SQL command’s plan. There are three high level options that most of you must already be familiar with (if not, the link above is all that you need to read): Basic, Typical and All. The low level options are the ones that are often missed by people. Here is a description of those options:

1) Predicates, cost, bytes, note are some of the options that you might want to use and in order to specify that you wish to include them in the plan, you would need to use the + sign and in order to exclude them from the plan, you can use the – sign. Example:

select plan_table_output
from table(dbms_xplan.display(‘plan_table’,null,’basic +cost’));

or

select plan_table_output
from table(dbms_xplan.display(‘plan_table’,null,’typical -cost’));

2) Another important addition that was made to the low level options was in the 10gR2 release: PEEKED_BINDS. This option is available when you use display_cursor(). This option allows for the display of the bind variables that were used to generate a particular plan. We had blogged about bind variable peeking before and you can read more on it here.

Example:

select plan_table_output
from table(dbms_xplan.display_cursor(null,null,’basic +PEEKED_BINDS’));

Setting the execution order of Triggers in SQL Server

Yesterday, we had seen how one can set the execution order of triggers in Oracle 11g i.e. if there is more than one trigger on a table, how can one set the execution order for those.  In today’s post, we will look at the same functionality in SQL Server.  SQL Server has this support in both SQL Server 2000 as well as SQL Server 2005.  It makes use of the system stored procedure: sp_settriggerorder.

Using sp_Settriggerorder stored procedure, we can define the execution order of the trigger. Here is the syntax for SQL Server 2005, taken from BOL. For complete explanation of syntax, please look at BOL.

sp_settriggerorder [ @triggername = ] ‘[ triggerschema. ] triggername’
, [ @order = ] ‘value’
, [ @stmttype = ] ‘statement_type’
[ , [ @namespace = ] { ‘DATABASE’ | ‘SERVER’ | NULL } ]

We are interested in the second parameter: “order”. It can take three values which means that it can take into account up-to three triggers.

1. First – Trigger is fired first
2. Last  – Trigger is fired last
3. None – Trigger is fired in random order.

The same procedure is available in SQLServer 2000 also but without namespace parameter because it does not support DDL triggers. Since SQL Server 2005, supports DDL trigger, namespace parameter defines the scope of the DDL trigger whether at Database level or at Server level. If value is NULL, trigger is a DML trigger.

We will use the same example as shown in yesterday’s  blog. Connect to database using Management Studio. Create following table..

CREATE TABLE TEST
(
COL1 INT IDENTITY (1,1),
COL2 INT,
COL3 INT,
COL4 DATETIME
)
GO

Now we will create two DML triggers on the table.

CREATE TRIGGER dbo.TRI_TEST_2 ON dbo.TEST
FOR INSERT
AS
DECLARE @ID INT
SELECT @ID = COL1 FROM INSERTED

UPDATE dbo.TEST
SET COL3 = 5 + COL2
WHERE COL1 = @ID
GO

CREATE TRIGGER dbo.TRI_TEST_1 ON dbo.TEST
FOR INSERT
AS
DECLARE @ID INT
SELECT @ID = COL1 FROM INSERTED

UPDATE dbo.TEST
SET COL2 = 5 + @ID
WHERE COL1 = @ID
GO

Now let us insert the record in the table and examine the result.

INSERT INTO TEST(COL4) VALUES(GETDATE());

Following is the result.

SELECT * FROM TEST;

COL1        COL2        COL3        COL4
———– ———– ———– ———————–
1           6           NULL        2008-02-27 23:28:08.500

It is apparent  from the result that TRI_TEST_2 got executed first at which point value of COL2 was null and as a end result, COL3 contains null value.

Let us now define the trigger order so that TRI_TEST_1 executes first and TRI_TEST_2 executes  after TRI_TEST_1.

sp_Settriggerorder ‘TRI_TEST_1′,’FIRST’,’INSERT’
GO
sp_Settriggerorder ‘TRI_TEST_2′,’LAST’,’INSERT’
GO

After executing above mentioned command, insert new record and check the result. We are displaying both the records for comparison.

COL1        COL2        COL3        COL4
———– ———– ———– ———————–
1           6           NULL        2008-02-27 23:37:03.640
2           7           12          2008-02-27 23:45:25.357

With the pre-defined execution order of the trigger, all the columns are populated  correctly.  Similarly we can set the firing order for the DDL triggers as well.

Restrictions:
•    For a single table, we can define only one first and last trigger for each statement.
•    If trigger is modified using ALTER TRIGGER statement, priority of the trigger is set to None so it is very important to re-execute the sp_settriggerorder stored procedure to reset the correct execution order.
•    If ‘FIRST’ or ‘LAST’ trigger already exists for the statement Type, reassigining new trigger to be the ‘FIRST’ one will result into an error.

How do we know whether any ordering is defined for the trigger or not? We can use objectproperty() function to retreive this information. Here is the query and the result.

SELECT OBJECT_NAME(PARENT_OBJ) TABLE_NAME,
NAME AS TRIGGER_NAME,
CASE OBJECTPROPERTY(ID, ‘ExecIsFirstInsertTrigger’)
WHEN 0 THEN ‘YES’
ELSE ‘NO’
END AS First
FROM SYSOBJECTS
WHERE XTYPE = ‘TR’
GO

TABLE_NAME    TRIGGER_NAME    First
———-    ————    —–
TEST        TRI_TEST_2      YES
TEST        TRI_TEST_1      NO

Similarly we can check for ExecIsLastInsertTrigger property to see whether last trigger is defined for insert statement or not.

Setting the execution order of Triggers in Oracle 11g

We are all aware that we can write multiple triggers on a single table based on the business requirement(s). One can have ‘BEFORE INSERT’ trigger on the table to generate a running number and on the same table there can be an ‘AFTER UPDATE’ trigger to calculate new value for another column. We can even define more than one trigger of the same type i.e. multiple ‘AFTER UPDATE’ triggers or multiple ‘BEFORE UPDATE’ triggers.  Normally execution order is determined by the type of the trigger. Oracle executes all triggers of the same type before executing trigger of different type. But what is the order of execution in case when there are multiple triggers of the same type? How do we know which trigger will be executed first?

Prior to 11g, there was no sure answer for this type of situation. Any one trigger (if they are of the same type) can get executed first and there is no guarantee that triggers will be executed again in the same order. This can create issues with the  data. In 11g, Oracle introduced ‘FOLLOWS’ clause to control the execution order of the triggers when they are of the same type.

Connect to SQL*Plus or SQL*Developer and create following table and sequence.

CREATE TABLE TEST
(
COL1 NUMBER(9),
COL2 NUMBER(9),
COL3 DATE
)
/

CREATE SEQUENCE TEST_SEQ START WITH 1234;

Now we will create two ‘BEFORE INSERT’ trigger on the table.

CREATE OR REPLACE TRIGGER TRI_TEST_1
BEFORE INSERT
ON TEST
FOR EACH ROW
BEGIN
:NEW.COL1 := TEST_SEQ.NEXTVAL;
Dbms_output.put_line(‘In Trigger TRI_TEST_1’);
END;
/

CREATE OR REPLACE TRIGGER TRI_TEST_2
BEFORE INSERT
ON TEST
FOR EACH ROW
DECLARE
v_col2 VARCHAR2(10);
BEGIN
SELECT REVERSE(to_char(:NEW.COL1))
INTO v_col2
FROM DUAL;
:NEW.COL2 := to_number(v_col2); –REVERSE(:NEW.COL1);
Dbms_output.put_line(‘In Trigger TRI_TEST_2’);
END;
/

Now let us insert the record in the table and examine the result.

SQL> INSERT INTO TEST(COL3) VALUES(SYSDATE);

Following is the result.

In Trigger TRI_TEST_2
In Trigger TRI_TEST_1

1 row created.

SQL> SELECT * FROM TEST;

COL1  COL2  COL3
—– —– ———
1234        26-FEB-08

It is very clear from the result that trigger TRI_TEST_2 got fired first. As a result COL2 value remained null because value  for COL1 is not populated at this time. Thus unordered execution of the trigger can lead to wrong results. To avoid this we can use ‘FOLLOWS’ clause.

Let us rewrite the TRI_TEST_2 trigger so that it gets executed after execution of trigger TRI_TEST_1. Here is the revised code.

CREATE OR REPLACE TRIGGER TRI_TEST_2
BEFORE INSERT
ON TEST
FOR EACH ROW
FOLLOWS TRI_TEST_1
DECLARE
v_col2 VARCHAR2(10);
BEGIN
SELECT REVERSE(to_char(:NEW.COL1))
INTO v_col2
FROM DUAL;
:NEW.COL2 := to_number(v_col2);
Dbms_output.put_line(‘In Trigger TRI_TEST_2’);
END;
/

After re-creating trigger, again insert record into the table using previously used insert statement and check result.

SQL> SELECT * FROM TEST;

COL1  COL2  COL3
—– —– ———
1235    5321    26-FEB-08

Now, we see that COL2 is correctly populated as execution of  trigger TRI_TEST_2 followed the execution of trigger TRI_TEST_1.

It is very clear from the example above that if we have multiple triggers of the same type on the same table, Oracle executes it randomly unless ‘FOLLOWS’ clause is specified for ordered execution of triggers.  Same functionality is available in SQL Server as well and we will blog that tomorrow.

Getting current SQL statements using ::fn_get_sql()

In SQL Server, getting the current SQL statements that are being executed by the active sessions is quite easy. In prior versions, one either had to make use Enterprise Manager or had to use DBCC INPUTBUFFER (<spid>) in order to get that information. In newer versions, one can just make use of the ::fn_get_sql() function. This was first introduced in SQL Server 2000 and is much better than the DBCC command since there are no text limitations.

Using the cross apply operator that we had discussed in one of our previous blog posts, one can write up a simple script to show all the SQL commands that are being fired by the active sessions on an instance:

SELECT spid, db_name(s.dbid), text
FROM master.dbo.sysprocesses as s
cross apply ::fn_get_sql(s.sql_handle)

Disabling Trigger at Creation time in Oracle 11g

Prior to Oracle 11g, disabling triggers used to be a two step process. Triggers were automatically created in an ‘enabled’ state. To disable it we had to disable it using the DISABLE clause of the ‘ALTER TRIGGER’ or ‘ALTER TABLE ‘ commands.

In Oracle 11g, we can specify ‘DISABLE’ clause in trigger definition it self.  In the absence of the ‘DISABLE’ clause, the trigger is created in an ‘ENABLED’ state. Following is the script to create table and then create trigger in disabled state.

CREATE TABLE TEST
(
COL1 VARCHAR(5) NOT NULL,
COL2 NUMBER(5),
COL3 DATE
);

Let us create a trigger now.

CREATE OR REPLACE TRIGGER TRI_TEST
BEFORE INSERT ON TEST
FOR EACH ROW
DISABLE
BEGIN
:NEW.COL3 := SYSDATE;
END;

Trigger will be created successfully.  We can verify the status of the trigger using data dictionary view user_triggers. Even though trigger is created in a disabled status,  Oracle will make sure that it does not have any compilation errors. Trigger will be compiled with errors if we try to reference non-existent column in the trigger.

This feature can be very useful when we want to enable trigger at later stage for a specific events and as mentioned earlier, we can avoid extra step of altering the trigger after creation.

Simple Copy/Paste to load data from Excel in SQL Server

We have blogged in the past about the data extraction and loading (exports and imports) methods in Oracle, SQL Server and DB2 LUW and the pros and cons of those different methods in terms of performance as well as maintenance etc.. At times during the QA or Development process, there is a need to load some data quickly for test cases. Under such circumstances, going through the scripts or the wizards could be a time consuming thing for users who are not that technical. Here is an alternative option that is available in SQL Server Management Studio (SSMS) in SQL Server 2005.

Say that we have a table called T1 with this structure:

USE [DECIPHER_TEST]
GO
/****** Object: Table [dbo].[T1] Script Date: 02/24/2008 12:00:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[T1](
[COL1] [int] IDENTITY(1,1) NOT NULL,
[COL2] [int] NULL
) ON [PRIMARY]

And in this table, the end user wants to load up 10 records which he/she has already put in an excel spreadsheet:

You can just highlight that data and hit Ctrl-C to copy that data into the buffer. After that, open up SQL Server Management Studio and right click the table and select open:

Now, click on the top left corner most tab to select the data and hit Ctrl-V to paste the records or just right click on that tab and select paste:

And it will paste the data from the buffer into the table. Once done, you can just exit out and it will save the data into the table:

This is just a quick copy/paste method of getting the data into the table in SQL Server 2005.

Grouping same sets together

Last night, at one of the client sites, a quick piece of code needed to be written which would take care of grouping the same cases (physical boxes) with the exact same set of SKUs together. It was a simple bit of SQL in the overall complex algorithm which decides how the cases are packed and shipped. Below is a small explanation of the issue and a short snippet of code to resolve it.

Let’s assume that there is a table called TEMP_CASE which has two columns: CASE_NBR and SKU_ID. And the data distribution is as shown below:

SET NOCOUNT ON
GO

CREATE TABLE TEMP_CASE (CASE_NBR VARCHAR(10), SKU_ID VARCHAR(10));
INSERT INTO TEMP_CASE VALUES (‘C1’, ‘S1’);
INSERT INTO TEMP_CASE VALUES (‘C1’, ‘S2’);
INSERT INTO TEMP_CASE VALUES (‘C2’, ‘S1’);
INSERT INTO TEMP_CASE VALUES (‘C2’, ‘S2’);
INSERT INTO TEMP_CASE VALUES (‘C3’, ‘S1’);
INSERT INTO TEMP_CASE VALUES (‘C3’, ‘S4’);
INSERT INTO TEMP_CASE VALUES (‘C4’, ‘S2’);
INSERT INTO TEMP_CASE VALUES (‘C4’, ‘S3’);
INSERT INTO TEMP_CASE VALUES (‘C4’, ‘S4’);
INSERT INTO TEMP_CASE VALUES (‘C4’, ‘S5’);
INSERT INTO TEMP_CASE VALUES (‘C5’, ‘S1’);
INSERT INTO TEMP_CASE VALUES (‘C5’, ‘S4’);
INSERT INTO TEMP_CASE VALUES (‘C6’, ‘S1’);
INSERT INTO TEMP_CASE VALUES (‘C6’, ‘S4’);
INSERT INTO TEMP_CASE VALUES (‘C7’, ‘S7’);
INSERT INTO TEMP_CASE VALUES (‘C7’, ‘S8’);

The intent of the code was to group C1 and C2 together since both of them had S1 and S2 as the SKUs in them. Likewise, C3, C5 and C6 would be grouped together since they have S1 and S4 as the SKUs. While C4 and C7 will be totally separate since the SKUs that the “EXACT” combination of SKUs that exist in them do not exist in any other case. There are many ways of doing that – key thing to keep in mind is that not only the number of SKUs need to be the same, the SKUs should match up exactly.

SELECT DISTINCT
DENSE_RANK() OVER (ORDER BY IV1.COMP_SKU) AS GRP_NBR,
IV1.CASE_NBR,
IV1.COMP_SKU
FROM
(SELECT
A.CASE_NBR, STUFF((SELECT ‘,’ + SKU_ID FROM TEMP_CASE WHERE CASE_NBR = A.CASE_NBR ORDER BY SKU_ID FOR XML PATH(”)), 1, 1, ”) AS COMP_SKU
FROM TEMP_CASE AS A) AS IV1
LEFT OUTER JOIN
(SELECT
A.CASE_NBR, STUFF((SELECT ‘,’ + SKU_ID FROM TEMP_CASE WHERE CASE_NBR = A.CASE_NBR ORDER BY SKU_ID FOR XML PATH(”)), 1, 1, ”) AS COMP_SKU
FROM TEMP_CASE AS A) AS IV2
ON IV1.COMP_SKU = IV2.COMP_SKU
ORDER BY GRP_NBR
GO
GRP_NBR CASE_NBR COMP_SKU
——————– ———- ————
1 C1 S1,S2
1 C2 S1,S2
2 C3 S1,S4
2 C5 S1,S4
2 C6 S1,S4
3 C4 S2,S3,S4,S5
4 C7 S7,S8

There are other ways to accomplish this as well but the above was performant enough for the requirements. Needless to state, there was additional filter criteria and conditions in the SQL that was used.

COPY Command in SQL *PLUS to copy data across Oracle databases

The COPY command is a SQL *PLUS command and not SQL or PL/SQL. It is facilitated by the fact that SQL *PLUS can connect to different databases simultaneously. In one of our previous posts, we had covered database links as a means of copying over or querying data across databases. Another method to do so is using the COPY command. Here is an example:

SET ARRAYSIZE 50

SET LONG 1000

SET COPYCOMMIT 100 /* To set commit size */

COPY FROM USERA/USERA@SID1 TO USERB/USERB@SID2 INSERT TABLEA USING SELECT * FROM TABLEA where …;

Here, the USERA and USERB are two separate users on two separate databases and the copy command is being used to copy over the data in TABLEA from USERA on one database to USERB on another database. It assumes that the structure of the tables is exactly the same. One can specify the filter conditions that they want and whatever form or shape of query they want to specify when copying over the data from the remote database.

In 10g and above, one should make use of the data pump commands: expdp/impdp since COPY command has been deprecated.

The IN-List Iterator Issue

We have seen this issue time and again in many client applications. Many applications are designed to just plug in different values into the IN Clause of the filter criteria for a given column (or if the RDBMS supports row value constructors, then the set of values). That is not a scalable design. You should be specifying a criteria that qualifies for those say 1000 values (region id values or the company ids) i.e. a status code or a date criteria or something that made you select those 1000 values/records to begin with.

If you do that, then the SQL:

X IN (1, 2, 3, 4, 5, 20, 30, 40, 50….)

will just be

X.Stat_code = :1
AND X.date_field >= :2 AND X.date_field <= :3

where :1, :2 and :3 are bind variables. The idea is that there must have been some reason (some criteria) due to which those 1000 values were selected to begin with…instead of preparing an IN list, parsing it and dividing it up into multiple sets, you should use that criteria itself…what if tomorrow, instead of say 1000, a client has a requirement where you have over a million such qualifying values? Your overhead will increase…internally, these COL1 IN (x, y, z) get translated to COL1 = x OR COL1 = y OR COL1 = z…you get the picture now.

Let’s talk about the IN-List iterator for a second. As you know, the queries that use the IN clause (with values) are internally converted (by the optimizer) into a series of queries using OR statements. The optimizer calculates the estimation of cardinality and looks for a long continguous sequence where expressions are all column=constant, such as (a=1 or a=10 or a=11, …). Those tests are run against every row in the table that matches the other filter criterias and join conditions (if those are present). RDBMS short-circuit the OR comparisons (meaning if it made a match on the 3rd item in the list it will stop testing instead of testing the other 96 items in the list), but it’s messy even if most of the values are caught by the first 20% of the list. Non-matching values will still be tested against every item in the IN clause. If you use an IN clause with many values to filter a large table, the amount of overhead can be brutal compared to simply lining two ordered lists up next to each other and plucking out the matches based on the filter criteria. Performance will dip even more when there are multiple users firing off similar queries that do these things.

IMHO (can be proven by comparing logical/physical reads, CPU usage and of course execution times), the best approach on any platform with an IN list of large size is two-fold :

The first option below is the best option…

1) The criteria that was used to qualify those N number of IN values should be used directly while forming the SQL –> that way the joins & meaningful filter criteria (and thus their indexes) can be used for faster retrieval of the data.

Either that, or if the above is not feasible for whatever reason, then the next best approach is :

2) To put the IN data into a work table (by using a DB function) and use a join. The DB function is in the FROM clause and hence acts as a table and pivots the data and joins it like a derived table/inline view.

Example:

SELECT
EmpName
FROM Emp e
INNER JOIN Split(‘100,101,102,103,104,105’) d
ON e.EmpID = d.Value
WHERE e.salary > 50000

One of the optimizer improvements in DB2 version 8.2 and SQL Server 2005 is that it will flatten large IN lists and will at times turn them into worktables (simulating what I have mentioned above in option #2) but this is not always guaranteed. In Oracle as well, at times, it can opt to use USE_CONCAT or NO_EXPAND (depending upon a couple of other parameter settings) if it sees the cost of “OR Expansion” for the IN-LIST to be high but again that is not always the case since the optimizer has to consider the permutations before giving up the OR expansion and that itself can take a longer parse time.

SQL Server 2008 CTP6

CTP6 (Community Technical Preview) has been released for SQL Server 2008. You can download it from:

http://www.microsoft.com/sql/2008/prodinfo/download.mspx

And you can download the BOL from here:

http://www.microsoft.com/downloads/details.aspx?FamilyID=19db0b42-a5b2-456f-9c5c-f295cdd58d7a&DisplayLang=en