Archive for the ‘Developer Tricks’ Category.

Weird Hint – Open Book Quiz

I ran into a slightly strange hint last week:

insert /*+ append, nologging, parallel(orders_tab, 4) */ …

Anyone know how this hint will behave right off the top of their head?

When I was in school we used to occasionally have quizzes where we were allowed to use our books. So feel free to use any documentation you can find on the topic.

Note: Be sure and see the comments on this one …

UPDATE 01-JUN-11: – Here’s a test case using two easy to verify hints

 
SYS@SANDBOX1> select /*+ monitor, GATHER_PLAN_STATISTICS */ * from dual
  2  ;
 
D
-
X
 
Elapsed: 00:00:00.00
SYS@SANDBOX1> @fsx
Enter value for sql_text: select /*+ monitor, GATHER_PLAN_STATISTICS */ * from dual%
Enter value for sql_id: 
 
SQL_ID         CHILD  PLAN_HASH  EXECS  AVG_ETIME AVG_PX OFFLOAD IO_SAVED_% SQL_TEXT
------------- ------ ---------- ------ ---------- ------ ------- ---------- ----------------------------------------------------------------------
d8yaqqyc0yb9k      0  272002086      1        .00      0 No             .00 select /*+ monitor, GATHER_PLAN_STATISTICS */ * from dual
 
Elapsed: 00:00:00.04
SYS@SANDBOX1> select * from table(dbms_xplan.display_cursor('&sql_id','&child_no','allstats'));
Enter value for sql_id: d8yaqqyc0yb9k
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  d8yaqqyc0yb9k, child number 0
-------------------------------------
select /*+ monitor, GATHER_PLAN_STATISTICS */ * from dual
 
Plan hash value: 272002086
 
---------------------------------------------------
| Id  | Operation                 | Name | E-Rows |
---------------------------------------------------
|   0 | SELECT STATEMENT          |      |        |
|   1 |  TABLE ACCESS STORAGE FULL| DUAL |      1 |
---------------------------------------------------
 
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level
 
 
19 rows selected.
 
Elapsed: 00:00:00.04

As you can see in the Note section, the GATHER_PLAN_STATISTICS hint was not obeyed (actually it wasn’t even evaluated). This is due to the fact that the parser quits evaluating the hint text when it hits the comma. Here’s the same statement with the comma removed.

 
SYS@SANDBOX1> select /*+ monitor GATHER_PLAN_STATISTICS */ * from dual;
 
D
-
X
 
Elapsed: 00:00:00.01
SYS@SANDBOX1> @fsx
Enter value for sql_text: select /*+ monitor GATHER_PLAN_STATISTICS */ * from dual
Enter value for sql_id: 
 
SQL_ID         CHILD  PLAN_HASH  EXECS  AVG_ETIME AVG_PX OFFLOAD IO_SAVED_% SQL_TEXT
------------- ------ ---------- ------ ---------- ------ ------- ---------- ----------------------------------------------------------------------
1scryy04ggv60      0  272002086      1        .00      0 No             .00 select /*+ monitor GATHER_PLAN_STATISTICS */ * from dual
 
Elapsed: 00:00:00.19
SYS@SANDBOX1> select * from table(dbms_xplan.display_cursor('&sql_id','&child_no','allstats'));
Enter value for sql_id: 1scryy04ggv60
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1scryy04ggv60, child number 0
-------------------------------------
select /*+ monitor GATHER_PLAN_STATISTICS */ * from dual
 
Plan hash value: 272002086
 
-----------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |      1 |        |      1 |00:00:00.01 |       2 |      2 |
|   1 |  TABLE ACCESS STORAGE FULL| DUAL |      1 |      1 |      1 |00:00:00.01 |       2 |      2 |
-----------------------------------------------------------------------------------------------------
 
 
13 rows selected.
 
Elapsed: 00:00:00.08

Notice that this time the GATHER_PLAN_STATISTICS hint was obeyed and so the xplan output has the A-Rows column and there is no Note section complaining about missing plan statistics. The documentation states that comment text can be interspersed with valid hints though. So why isn’t the comma treated as comment text. Well apparently it is a reserved word. Oddly enough, the word COMMENT is a reserved word as well. Watch this:

 
SYS@SANDBOX1> select /*+ monitor COMMENT GATHER_PLAN_STATISTICS */ * from dual;
 
D
-
X
 
Elapsed: 00:00:00.00
SYS@SANDBOX1> @fsx
Enter value for sql_text: select /*+ monitor COMMENT GATHER_PLAN_STATISTICS */ * from dual
Enter value for sql_id: 
 
SQL_ID         CHILD  PLAN_HASH  EXECS  AVG_ETIME AVG_PX OFFLOAD IO_SAVED_% SQL_TEXT
------------- ------ ---------- ------ ---------- ------ ------- ---------- ----------------------------------------------------------------------
8dxup58bgaxsy      0  272002086      2        .02      0 No             .00 select /*+ monitor COMMENT GATHER_PLAN_STATISTICS */ * from dual
 
Elapsed: 00:00:00.03
SYS@SANDBOX1> @dplan_allstats
Enter value for sql_id: 8dxup58bgaxsy
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  8dxup58bgaxsy, child number 0
-------------------------------------
select /*+ monitor COMMENT GATHER_PLAN_STATISTICS */ * from dual
 
Plan hash value: 272002086
 
---------------------------------------------------
| Id  | Operation                 | Name | E-Rows |
---------------------------------------------------
|   0 | SELECT STATEMENT          |      |        |
|   1 |  TABLE ACCESS STORAGE FULL| DUAL |      1 |
---------------------------------------------------
 
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level
 
 
19 rows selected.
 
Elapsed: 00:00:00.03

So the moral is don’t put comment text in your hints. If you must have a comment in your SQL, put it in a separate comment structure like so:

select /*+ monitor  GATHER_PLAN_STATISTICS */ /* This is a comment */ * from dual;

And if you really feel you need commas between your hints try something like this:

select /*+ monitor"," GATHER_PLAN_STATISTICS */ * from dual;

Most Expensive SQL Statement Ever

You know the cost calculation that the cost based optimizer (CBO) uses to determine which execution plan to choose for a SQL statement, right? If you don’t, you should immediately stop reading this and pick up a good novel instead. Ah, you’re still here? Well I got an interesting email today from one of my co-workers saying he had to kill a query yesterday. Actually that’s a big part of his current job. Killing runaway queries – apparently that job takes most of his time between 8 and 5. Anyway, he sent me this execution plan today, no comments, “just have a look at this”, he said.

---------------------------------------------------------------------------------------------------
| Id  | Operation              | Name             | Rows  | Bytes|TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                  |       |       |      |    65P(100)|          |
|   1 |  SORT ORDER BY         |                  |    18E|    15E|   15E|    65P (78)|999:59:59 |
|   2 |   COUNT                |                  |       |       |      |            |          |
|*  3 |    FILTER              |                  |       |       |      |            |          |
|   4 |     NESTED LOOPS       |                  |    18E|    15E|      |    14P  (3)|999:59:59 |
|   5 |      NESTED LOOPS      |                  |   984G|   216T|      |    14G  (3)|999:59:59 |
|   6 |       TABLE ACCESS FULL| CAT_6000_6001TBL |  7270K|  1074M|      |   176K  (3)| 00:15:46 |
|   7 |       TABLE ACCESS FULL| CAT_6000TBL      |   135K|    11M|      |  1950   (3)| 00:00:11 |
|   8 |      INDEX FULL SCAN   | PK_OBJECTS       |    32M|   306M|      | 15207   (3)| 00:01:22 |
---------------------------------------------------------------------------------------------------

So I had a look. Yes – that’s a 65P in the cost column. I’ve seen worse (but not in a production system). Cost is not always a good indication of run time, by the way. It’s just a sort of normalized estimation after all. But the estimate for the number of rows and bytes (18E and 15E) are very impressive as well. This query ran for several hours before my buddy finally killed it. As you might expect, the query was missing a join condition between a couple of large tables (7M and 32M).

Here’s a test I worked up to see how big a number I could get.

SYS@LAB1024> !cat dplan.sql
set lines 150
select * from table(dbms_xplan.display_cursor('&sql_id','&child_no','typical'))
/
 
SYS@LAB1024> @dplan
Enter value for sql_id: gf5nnx0pyfqq2
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  gf5nnx0pyfqq2, child number 0
-------------------------------------
select a.col2, sum(a.col1) from kso.skew a, kso.skew b group by a.col2
 
Plan hash value: 321450672
 
-----------------------------------------------------------------------------------
| Id  | Operation               | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |         |       |       |   689G(100)|          |
|   1 |  HASH GROUP BY          |         |     1 |    16 |   689G (84)|999:59:59 |
|   2 |   MERGE JOIN CARTESIAN  |         |  1024T|    14P|   145G (22)|999:59:59 |
|   3 |    TABLE ACCESS FULL    | SKEW    |    32M|   488M| 10032  (18)| 00:01:21 |
|   4 |    BUFFER SORT          |         |    32M|       |   689G (84)|999:59:59 |
|   5 |     INDEX FAST FULL SCAN| SKEW_PK |    32M|       |  4558  (22)| 00:00:37 |
-----------------------------------------------------------------------------------
 
 
17 rows selected.
 
SYS@LAB1024> @dplan
Enter value for sql_id: 12p7fuydx3dd5
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  12p7fuydx3dd5, child number 0
-------------------------------------
select a.col2, sum(a.col1) from kso.skew a, kso.skew b, kso.skew c group by
a.col2
 
Plan hash value: 175710540
 
------------------------------------------------------------------------------------
| Id  | Operation                | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |         |       |       |    18E(100)|          |
|   1 |  HASH GROUP BY           |         |     1 |    16 |    18E (81)|999:59:59 |
|   2 |   MERGE JOIN CARTESIAN   |         |    18E|    15E|  4670P (22)|999:59:59 |
|   3 |    MERGE JOIN CARTESIAN  |         |  1024T|    14P|   145G (22)|999:59:59 |
|   4 |     TABLE ACCESS FULL    | SKEW    |    32M|   488M| 10032  (18)| 00:01:21 |
|   5 |     BUFFER SORT          |         |    32M|       |   145G (22)|999:59:59 |
|   6 |      INDEX FAST FULL SCAN| SKEW_PK |    32M|       |  4558  (22)| 00:00:37 |
|   7 |    BUFFER SORT           |         |    32M|       |    18E (81)|999:59:59 |
|   8 |     INDEX FAST FULL SCAN | SKEW_PK |    32M|       |  4558  (22)| 00:00:37 |
------------------------------------------------------------------------------------
 
 
21 rows selected.
 
SYS@LAB1024> @dplan
Enter value for sql_id: 7b53dxh6w6mpj
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  7b53dxh6w6mpj, child number 0
-------------------------------------
select a.col2, sum(a.col1) from kso.skew a, kso.skew b, kso.skew c, kso.skew
d group by a.col2
 
Plan hash value: 3965951819
 
-------------------------------------------------------------------------------------
| Id  | Operation                 | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |         |       |       |    18E(100)|          |
|   1 |  HASH GROUP BY            |         |     1 |    16 |    18E  (0)|999:59:59 |
|   2 |   MERGE JOIN CARTESIAN    |         |    18E|    15E|    18E  (0)|999:59:59 |
|   3 |    MERGE JOIN CARTESIAN   |         |    18E|    15E|  4670P (22)|999:59:59 |
|   4 |     MERGE JOIN CARTESIAN  |         |  1024T|    14P|   145G (22)|999:59:59 |
|   5 |      TABLE ACCESS FULL    | SKEW    |    32M|   488M| 10032  (18)| 00:01:21 |
|   6 |      BUFFER SORT          |         |    32M|       |   145G (22)|999:59:59 |
|   7 |       INDEX FAST FULL SCAN| SKEW_PK |    32M|       |  4558  (22)| 00:00:37 |
|   8 |     BUFFER SORT           |         |    32M|       |  4670P (22)|999:59:59 |
|   9 |      INDEX FAST FULL SCAN | SKEW_PK |    32M|       |  4558  (22)| 00:00:37 |
|  10 |    BUFFER SORT            |         |    32M|       |    18E  (0)|999:59:59 |
|  11 |     INDEX FAST FULL SCAN  | SKEW_PK |    32M|       |  4558  (22)| 00:00:37 |
-------------------------------------------------------------------------------------
 
 
24 rows selected.

So it looks like the cost tops out at 18E as does the estimated number of rows. Oddly the number of bytes appears to top out at 15E. So the production query had maxed out the rows and bytes estimate although the cost was significantly under the max. Still 65P is the biggest cost I’ve seen in a production system. Anyone seen a bigger one?

P.S. I have two categories for SQL related posts. “Developer Tricks” and “Wall of Shame”. This one gets both tags.

Funny Developer Tricks – (substr(cust_id,1,length(:b1))

Ha. This one was a little surprising. I ran across a SQL statement that gets fired off hundreds at a time in rapid succession. Each execution taking several seconds – too long for thousands of executions in a row. The statement looked like this (cleaned up to protect the guilty):

b1 := '10355P034001SGL00066';
b2 := '10355P034001SGL00066';
 
select count(cust_id) 
from customers
where substr(cust_id,1,length(:b1)) = :b2;

What was the developer trying to do? How can we fix it? Your comments are welcomed.

Continue reading ‘Funny Developer Tricks – (substr(cust_id,1,length(:b1))’ »

Funny Developer Tricks – upper(number)

I saw a funny one today. I’ll paraphrase:

select * from table_x
where upper(acct_number) = '876876'
or upper(acct_number) = '826531';

Nice huh?

1. Obviously turns off any indexes on acct_number (unless they had a function based index).
2. Looks like they are probably storing numeric data in a character data type (or implicitly converting a number to character string).
3. Not using bind variables so they are not helping themselves from a parsing standpoint.
4. Finally, they’re making darn sure they take care of any mixed case numbers!

Some fun.

(by the way, those lower case numbers cause me problems all the time)

Check to Make Sure My New Record Doesn’t Already Exist

Here’s another interesting piece of SQL that I ran into last week:

select con_id from xyz_blah where con_id=’BS-002342′;

Actually there was a whole set of them with different literals. My first thought was, “Why would the developers want to select the con_id when they already had the con_id?”, quickly followed by “Ohhhhhhhh, it’s one of those deals where the developers didn’t trust the database or don’t know how to check for an error after executing a SQL statement”.

Presumably they know that ‘BS-002342′ is a valid con_id (it looks pretty specific to me). So the app’s probably doing one of the following three bad things (listed in what I think is the most likely order).

  1. Checking to make sure a record exists with that con_id before doing something (UPDATE or DELETE).
  2. Checking to make sure that a record doesn’t already exist with that con_id, before doing an INSERT.
  3. Checking to make sure a DELETE actually worked.

When I looked at the stats in v$sql, the queries never return any rows. So it’s probably not #1. I didn’t find any DELETEs on the table but I did find INSERT statements with matching con_id’s, so it looks like it’s the check before insert scenario (#2). There is a Primary Key on the con_id field, so the check is of course, totally unnecessary. They should have just done the insert and handled the duplicate key error if one ever happened. By the way, in the 30 days of AWR data we had available, none of these statements ever returned a row. So most likely, they never have this issue in the first place. Of course, they might also consider using a sequence to generate the key instead of having the app manufacture a 9 character key!

Anyway, this is one of a whole set of coding issues where unnecessary work is done as a standard coding practice. Like Cary Millsap always says, “the fastest way to do anything is not to do it at all”.