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.
I can’t recall seeing a higher prod plan cost.
Reminds me of Laurent Schneider having fun with collection costs here:
http://laurentschneider.com/wordpress/2009/01/high-cost.html
If we’re talking interesting plans, I did have some prod execution plans with 2800 lines but the pastebin-type place I put them has gone offline. They involved views on views on views, etc.
Also, here’s a snippet from a 10053 trace file that I’ve kept:
***** Virtual column Adjustment ******
Column name SYS_NC00066$
cost_cpu 300.00
cost_io 17976931348623157081452742373170435679807056752584499
6598917476803157260780028538760589558632766878171540458953514
3824642343213268894641827684675467035375169860499105765512820
7624549009038932894407586850845513394230458323690322294816580
8559332123348274797826204144723168738177180919299881250404026
184124858368.00
***** End virtual column Adjustment ******
Cheers,
Dominic
Yeah, Laurent’s example had the same 18E max as well. It’s interesting that the estimated time maxes out at 1000 hours, that’s 41.666 days. Seems like there ought to be a parameter you could set to tell Oracle to just return an error message (WARNING: Danger Will Robinson, this query will probably never finish). But if it were so, then what would my buddy do all day?
On the subject of large SQL text, I had one a couple of weeks ago that wouldn’t fit in my cut and paste buffer (had to scp a file to the server to execute it). It was 2914 lines and tipped the scales at about half a meg. I have seen generated statements that were to big to run in the past (but I can’t remember anymore if the limit was in SQL*Plus or an Oracle).
Kerry
> I have seen generated statements that were to big to run in the past (but I can’t remember anymore if the limit was in SQL*Plus or an Oracle).
I did a test with 8i some years ago, generating and parsing SQLs through DBMS_SQL. The only size limit I could find at the time was the amount of free(able) memory in the shared pool. Things may have changed though…
Cheers,
Flado
I can hardly believe it myself – in fact I could hardly believe it at the time – but I remember Oracle version 4 (the first version I worked with in the mid-1980s) had a 25 line SQL limit. That’s right, not a character limit – 25 lines.
Physically longer queries would fail, but if you removed some carriage returns and made fewer lines, it would run.
Gee, why would anyone ever want anything bigger than that?
Jim
Hey that’s pretty cool – I didn’t know you could get numbers that big!
Have you tested it on different versions to see how the limits have changed? (Wonder what Oracle v6’s limits would have been).
Maybe you should advise your buddy to look at the plan before he runs his queries, or even just look at the sql!
John
the worst plan (and design) i ever saw was this one:-
http://forums.oracle.com/forums/thread.jspa?messageID=2187453�
I still have a demo that will generate a similar table and use it whenever someone starts to talk about generic data models.
John,
My buddy doesn’t run the queries – he’s the DBA so he just kills them. 😉
Chris,
All I can say is Wow!
Kerry
Hey, if we can find even one of these running at a government installation (there’s got to be a bunch, no???), we could resolve the federal budget deficit!