"Sure you won't change your mind?" / "Is there something wrong with the one I have?" - Dr. Gillian Taylor & Mr. Spock - Star Trek IV: The Voyage Home
Not so very long ago, I had one of the subject matter experts come and talk to me about what he considered to be the abysmal performance in a query against the ERP database we host. He was trying to find, via a SQL query, all open work orders from the database.
The WHAT
The query in question used two tables:
MSF600 - The equipment register table. This table contains all the pieces of equipment and is keyed on a column called EQUIP_NO
MSF620 - The work order table. This table contains all work orders, whether raised against equipment or not. It is keyed on WORK_ORDER but one of the many alternative indexes is based on WO_STATUS_M. Another alternative index is based upon the EQUIP_NO as well.
Work Orders have a column called WO_STATUS_M, which is the status of the work order. The valid values for status are:
A: Authorized
C: Closed
O: Open, not Authorized
This is going to address a specific query and the understanding of why it took a while to run. Long-running queries are not necessarily a bad thing, depending on what they are doing, however, this particular query seems like it should have run relatively quickly.
The SQL statement itself seemed fairly inoffensive:
select e.plant_no, e.equip_status, w.work_order, w.wo_desc, w.work_group,
w.raised_date, w.maint_sch_task, w.wo_status_m
from msf600 e
join msf620 w on w.equip_no = e.equip_no
where w.wo_status_m <> 'C'
order by raised_date
/
Why should an apparently simple query perform so badly? Well, let's find out. Sometimes the simplest looking queries can do the weirdest things.
Oracle has a statement EXPLAIN PLAN you can add to the start of any query to generate an ESTIMATED execution plan. This is what the optimizer THINKS it should be doing. The actual plan is something different but we won’t go into that today. Doing that here gives us:
SQL> explain plan for
2 select e.plant_no, e.equip_status, w.work_order, w.wo_desc, w.work_group,
3 w.raised_date, w.maint_sch_task, w.wo_status_m
4 from msf600 e
5 join msf620 w on w.equip_no = e.equip_no
6 where w.wo_status_m <> 'C'
7 order by raised_date
8 /
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Plan hash value: 2559632844
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20366 | 2684K| | 57379 (1)| 00:00:03 |
| 1 | SORT ORDER BY | | 20366 | 2684K| 2976K| 57379 (1)| 00:00:03 |
|* 2 | HASH JOIN | | 20366 | 2684K| | 56764 (1)| 00:00:03 |
|* 3 | TABLE ACCESS FULL| MSF620 | 5006 | 430K| | 55431 (1)| 00:00:03 |
| 4 | TABLE ACCESS FULL| MSF600 | 38297 | 1757K| | 1333 (1)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("W"."EQUIP_NO"="E"."EQUIP_NO")
3 - filter("W"."WO_STATUS_M"<>'C')
17 rows selected.
SQL>
What we can see from this is that with are joining both tables, no indexes and doing full table scans.
As far as reading the plan, in general, the further a line is indented, the earlier in the whole execution of the statement it is executed.
So this query as written is doing a TABLE ACCESS FULL on both MSF600 and MSF620. TABLE ACCESS FULL means start at the first record. Read EVERY record in the table and return it. So this query as written is reading ALL of MSF600, ALL of MSF620, picking up the records they want and matching them to MSF600. Then it matches all those records together, sorting them and returning it to the user.
The FIX
So, I modified the query just a little:
SQL> explain plan for
2 select e.plant_no, e.equip_status, w.work_order, w.wo_desc, w.work_group,
3 w.raised_date, w.maint_sch_task, w.wo_status_m
4 from msf600 e
5 join msf620 w on w.equip_no = e.equip_no
6 where w.wo_status_m in ('A', 'O')
7 order by raised_date
8 /
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
Plan hash value: 2508940585
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20364 | 2684K| | 2954 (1)| 00:00:01 |
| 1 | SORT ORDER BY | | 20364 | 2684K| 2976K| 2954 (1)| 00:00:01 |
|* 2 | HASH JOIN | | 20364 | 2684K| | 2339 (1)| 00:00:01 |
| 3 | INLIST ITERATOR | | | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| MSF620 | 5005 | 430K| | 1007 (1)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | MSF62001I9 | 5005 | | | 16 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | MSF600 | 38297 | 1757K| | 1333 (1)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("W"."EQUIP_NO"="E"."EQUIP_NO")
5 - access("W"."WO_STATUS_M"='A' OR "W"."WO_STATUS_M"='O')
19 rows selected.
SQL>
What happened here?
Look at our output now. There’s an index in there - this is the index I talked about at the top, the one that uses WO_STATUS_M as the access path to the data. An Index range scan is the optimizer telling us that it’s found an index and can search on a distinct range of values.
The Cost column is also a VERY abstract thing (represents the amount of effort the engine has to do to satisfy your query) and you shouldn’t go betting your house on it but notice the huge drop in the Cost of the query too.
So instead of matching reading ALL of MSF620 to find those 8,153 rows, if can go directly to the index, pick up the 8100+ out of the millions, match those to MSF600 (which will always be a full scan for a query like this) and return those. Huge reduction in I/O here.
There are other issues that can affect execution time for things like this as well - are the rows already in the buffer cache or do they need to be fetched from disk ? Has the statement previously been parsed and compiled and it's sitting in memory waiting to run again?
The WHY
The optimizer is both a beautiful thing and demon spawn all at the same time. It can only operate on what it knows and sometimes it makes a bad decision. The design of this particular status field (WO_STATUS_M) has 3 statuses – A & O which represent non-closed WO’s and C for closed. Which means you have a small number of A’s, then a bunch of C’s then the O’s. Which means you have data scattered all through the file.
The optimizer takes into consideration the distribution of the data as well.
SQL> select wo_status_m, count(*) 2 from msf620 3 group by wo_status_m 4 order by 1 5 / W COUNT(*) - ---------- A 4946 C 1175836 O 62For this table we have 99.5% of the values are ‘C’. For whatever reason, the optimizer decided that since the range of values of “NOT C” were small it was more efficient to scan the whole table. I can’t tell you why. This is one of those bad decisions I told you about earlier.
When I changed it to be inclusive it turned around and said “OK, I know I have a few up front and a few at the end, let’s get them separately”.
It also doesn’t mean that this is always the case. There are ALWAYS exceptions.
I don’t have the time to go digging in the Oracle Documentation for this at the moment, but there are several blogs and articles I found elsewhere on the internet that backup these findings along the lines of “The Oracle Optimizer doesn’t necessarily like inequalities”:
http://oracleinaction.com/conditions-based-inequalities-cant-use-indexes-toresolve/
http://oracle.readthedocs.io/en/latest/sql/indexes/predicates-equality-before-inequality.html
http://grokbase.com/t/freelists.org/oracle-l/11bf3r6eky/strategies-for-dealing-with-not-equal-conditions-and-indexes
So bottom line here: if you’re trying to run a query with an inequality and getting bad performance, try an equality instead and see what you get.
