Different execution plans with same query same database.
Date: 1998/08/25
Message-ID: <35E35857.6FBD010C_at_panbio.com>
Has anyone ever seen anything like this before?
I am having a problem where sometimes, not always I get vastly different query execution plans (QEP's) from the SAME database using the SAME query just varying the tool used to connect to the database. This is consistent and reproducible. It also happens in seperate instances on seperate machines, so I don't believe it is a corruption problem. This problem has been observed with both a 7.3.4.0.1 and 7.3.3.5 databases on sun Solaris 2.6. I believe I was seeing this behavior on oracle 7.3.3.0 as well, but don't have an instance anymore.
Be careful if you try to duplicate this problem. If you send the query from one of the tools that work and then send the same query from one of the tools that don't work, you will get a query plan chache hit, and the bad tool will use the good stored plan. The reverse is also true, if you send a query from one of the working tools, that gets a cache hit for a query sent from one of the bad tools, it will then execute the bad stored query plan. This does not happen for the majority of our queries, but when it does happen, it is devastating. Bad execution plans can consume even a large server very quickly.
Don't pay too much attention to the specific query being run, I am just trying to point out that in some cases vastly different queries are being generated when everything should be the same.
The following desktop (Windows N/T) tools work correctly...
SQLPlus 8.0.4.0.0 (Enerprise Manger 1.5.5 distribution) SQL WorkSheet 1.5.0 (Enterprise Manager 1.5.5 distribution) Sql Analyze Tool (Enterprise Manager 1.5.5 Tuning Pack)
The following Server tools (sun Solaris 2.6) work incorrectly and
generate really bad
execution plans.
Svrmgrl 7.3.4 Svrmgrl 7.3.3.5 sqlplus 3.3.4.0.1 (7.3.4 oracle) sqlplus 3.3.3.0.0 (7.3.3.5 oracle)
My tables are heavily indexed, so the optimizer has lots of choices, but a reasonable plan is consistently chosen by the client tool set. The server tools are choosing to scan a table that has 16 million records, rather then use the primary key index that is available and appropriate to use.
I have also observed that the client tools seem to do the right things for most hints i.e., I can get it to do stupid things that it probably should not do with hints, but when you use the same query with the server tools, I don't get the same behavior, it keeps insisting to doing a table scan. The server tools don't even seem to like the /*+ FIRST_ROWS */ hint to get around the table scan.
Everything has been analyzed I even used compute to make sure we were
getting a good sample size.
I analyzed the table, the index and all the indexed columns asking it to
save the maximum number of values/column (254).
All of the columns involved in the query are indexed. So the optimizer
has collected statistics on each of them. The
query only returns 1 row, even with out the count(Distinct). In fact, I
have run the inner sub select alone, and it
also has exhibited the same problem.
The COMPATIBLE parameter on the database is set to 7.3.3.5 and hash_joins_enabled=false. I did this because it was doing a hash join now it has switched to a standard merge join, still a bad idea.
Any help or insight into the problem would be appreciated.
Feel free to leave answers in the news group, but Please send me email
with any answers,
I will follow-up to the group if/when I track this down.
-Jeff Rule
Pangea Systems
Oakland, CA
jeffr_at_pangeasystems.com
Here are examples of the 2 QEP's being generated.
(Note, the execution costs are requisite with the length of time it is
taking the queries to return)
The GOOD QEP ---------------------------------------------------------
SQLWKS>
SQLWKS> EXPLAIN PLAN SET STATEMENT_ID = 'huh' FOR
2> SELECT COUNT(DISTINCT base.gt_id)
3> FROM (SELECT identlnk.gt_id gt_id
4> FROM identifier_linkage identlnk, identifier ident
5> WHERE ident.identifier_string LIKE 'P53_at__BOVIN%' ESCAPE
'_at_'
6> AND identlnk.identifier_id = ident.identifier_id
7> AND link_type_id IN (31, 33, 35, 36, 37, 39, 40, 42, 52,
55, 801,
8> 805, 700, 701)) base
9> /
Statement processed.
SELECT STATEMENT Cost =
6
SORT GROUP
BY
NESTED
LOOPS
INDEX RANGE SCAN
IDENTIFIER_STRING_IDX
INDEX RANGE SCAN
PK_IDENTIFIER_LINKAGE
The BAD QEP
(note the full scan of the identifier_linkage table, 33+ million
records)
SQL>
SQL> EXPLAIN PLAN SET STATEMENT_ID = 'huh' FOR
2 SELECT COUNT(DISTINCT base.gt_id)
3 FROM (SELECT identlnk.gt_id gt_id 4 FROM identifier_linkage identlnk, identifier ident 5 WHERE ident.identifier_string LIKE 'P53_at__BOVIN%' ESCAPE '@' 6 AND identlnk.identifier_id = ident.identifier_id 7 AND link_type_id IN (31, 33, 35, 36, 37, 39, 40, 42, 52, 55, 801, 8 805, 700, 701)) base
9 /
SELECT STATEMENT Cost = 23942
SORT GROUP BY
MERGE JOIN
SORT JOIN
INDEX RANGE SCAN IDENTIFIER_STRING_IDX
SORT JOIN
TABLE ACCESS FULL IDENTIFIER_LINKAGE
-- http://www.pangeasystems.comReceived on Tue Aug 25 1998 - 00:00:00 CEST