In another blog out there in blogspace a fellow expert has called me on this tip. I can only report what we saw during our testing. First we saw a reduction in full table scans as indicated in the V$SQL_PLAN with a reduction in the indicated bytes processed. Allowing that this may be just an artifact of Oracle reporting it would use a FTS if it actually tried to resolve the query but really used predicate elimination to realize there was nothing to do, how does one expalin away the drastic reduction in physical IO and logical IO we noticed in enterprise manager screens after the changes? Anyway, don't take my word for it, no one has the absolute last word on anything Oracle in less they are peering at the souce code for your release on your OS while making their pronouncements. Test the results on your machine and for your version and go with what works in your environment. It's just that simple.
Found out some interesting things yesterday. First, while reviewing a clients Oracle application which had been ported from SQL Server, I noticed they were using the constructs “WHERE 1=1” and “WHERE 1=2” in order to build dynamic SQL for dynamic queries. In the first case of “WHERE 1=1” it was always followed by a group of one or more “AND” clauses so the optimizer would of course practice predicate elimination and remove it. However in the second case, “WHERE 1=2” it was being used to merely return the column headers to avoid error handling.
It is this second case, “WHERE 1=2” I want to talk about. Of course since 1 never equals 2 you never get a result back, however it forces a full table scan! In testing with 10gR1 version 10.1.0.4 and on 9iR2, 9.2.0.4 if there was no bitmap index on the table using the “WHERE 1=2” construct resulted in a full table scan. I tried using a unique and a non-unique B-Tree and they were ignored, seems only a bitmap index would allow the optimizer to determine that 1 would never equal 2, why this is so is a mystery since obviously the bitmap had nothing to do with whether 1=1 or 1=2.
In SQL Server, the optimizer is smart enough to realize 1 will never equal 2 and there, it simply returns the column list without any other action. Oracle, you paying attention? Make a note!
What we ended up doing in this clients case was to replace the “WHERE 1=2” with a clause that equated the primary key of the table with an impossible value for that key, in this case the ID was being passed in as a GUID (a hexadecimal value) so we use a “WHERE KEY=HEX(00)” and got a low cost unique index lookup instead of a costly full table scan.
A second item of note, one of the tables in a SQL involved in a match against values passed into a global table temporary table had a PARALLEL setting of 4, but the instance had a parallel min servers setting of 0 so there was no way that a parallel scan could be performed. However, the fun part in this situation comes up when you get an explain plan. Using the command line explain plan you get a perfectly reasonable plan using an index scan on the permanent table and a full scan on the global temp. However, when you check on this SQL in the V$SQL_PLAN table it is attempted a parallel query with a single query slave doing a full table scan on both the permanent and temporary table!
The proper plan for this non-parallel query being forced into a non-optimal parallel plan was also shown in the EM database control session SQL screen (since it is probably pulled from the DBA_HIST_SQL_PLAN which is a child of the V$SQL_PLAN table, this makes sense.) However, the developer, using the PL/SQL Developer tool, was getting the bad explain plan and couldn’t see the problem. We changed the PARALLEL to 1 on the table and the plans then matched between the online explain plan and the V$SQL_PLAN explain plan.
A second note for Oracle: If the PARALLEL is set on a table and the MIN_PARALLEL_SERVERS is set to 0, completely disregard the PARALLEL setting. In testing a setting of 0 resulted in no P000 processes starting so even with the resulting parallel plan the query was serialized.
And a final note: The plan generated by the EXPLAIN PLAN and AUTOTRACE commands should match the one generated into the V$SQL_PLAN table.
Mike
Subscribe to:
Post Comments (Atom)
4 comments:
Doug,
Thats what the docs say, however as the test I sent you shows, it appears no P000 processes wee started and it serialized the parallel plan. In addition the performance went from 5 seconds per batch of operations to 40 seconds when this plan was used.
Mike
I ran the following script on Oracle 8.1.7.4. On my machine, the query without 1=2 takes 25 seconds and has 2 full scans of BLAH; the query with 1=2 takes 0.07 seconds and has 2 full scans of BLAH.
-- Create a table with 3200 rows in it
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE BLAH AS SELECT * FROM ALL_OBJECTS WHERE ROWNUM <= 100';
FOR c_idx IN 1 .. 5
LOOP
EXECUTE IMMEDIATE 'INSERT INTO BLAH SELECT * FROM BLAH';
END LOOP;
END;
-- Takes 25 seconds
SELECT b1.OWNER, b2.OWNER, COUNT(*)
FROM BLAH b1, BLAH b2
GROUP BY b1.OWNER, b2.OWNER;
-- Takes 0.07 seconds
SELECT b1.OWNER, b2.OWNER, COUNT(*)
FROM BLAH b1, BLAH b2
WHERE 1 = 2
GROUP BY b1.OWNER, b2.OWNER;
Please look at the next entry. In addition your SQL is not equivilent to what I described in that you are not comparing to a non-existant index entry in the first query. SInce the queries are not functionally equivilent I would expect the second to run much faster.
Post a Comment