Different query execution plan

Posted in 2003

Topics: Performance & Tuning, SQL Development & Query Writing, Server Administration

rkusenet — Thu, 19 Jun 2003 17:47:33 — source: IIUG Forums & Mailing Lists

If I run the same query on QA box and our PRODUCTION box, I see different query execution plan. Following settings are used in both QA and PRODUCTION box. OPTCOMPIND 0 # To hint the optimizer OPT_GOAL -1 The table structure, including fragmentation is same. There is only 1 difference between QA and PRODUCTION box. QA runs 9.21.UC4XE server PRODUCTION runs 9.21.UC4 server In QA it is using a sequential scan, whereas in production it is not. in QA there are 300 rows, and in production 50 rows. Ravi QA BOX QUERY: ------------- SELECT r.id, r.index FROM flight_req f, request r, request_change c WHERE (f.index > 0) AND c.avail_done = 'N' AND r.avail_done IN ('Y', 'I', 'O','0') AND r.index_oj = 0 AND r.id = f.id AND r.index = f.index and r.id =c.id AND r.index = c.index Estimated Cost: 18 Estimated # of Rows Returned: 1 1) dba.c: SEQUENTIAL SCAN Filters: (dba.c.avail_done = 'N' AND dba.c.index > 0 ) 2) dba.f: INDEX PATH Filters: (dba.f.index > 0 AND dba.f.index = dba.c.index ) (1) Index Keys: id (Serial, fragments: ALL) Lower Index Filter: dba.f.id = dba.c.id NESTED LOOP JOIN 3) dba.r: INDEX PATH Filters: dba.r.avail_done IN ('Y' , 'I' , 'O' , '0' ) (1) Index Keys: id index index_oj (Serial, fragments: ALL) Lower Index Filter: ((dba.r.id = dba.f.id AND dba.r.index = dba.f.index ) AND dba.r.index_oj = 0 ) NESTED LOOP JOIN PRODUCTION BOX QUERY: --------------------- SELECT r.id, r.index FROM flight_req f, request r, request_change c WHERE (f.index > 0) AND c.avail_done = 'N' AND r.avail_done IN ('Y', 'I', 'O','0') AND r.index_oj = 0 AND r.id = f.id AND r.index = f.index and r.id =c.id AND r.index = c.index Estimated Cost: 5 Estimated # of Rows Returned: 1 1) dba.c: INDEX PATH Filters: dba.c.index > 0 (1) Index Keys: avail_done (Serial, fragments: ALL) Lower Index Filter: dba.c.avail_done = 'N' 2) dba.r: INDEX PATH Filters: dba.r.avail_done IN ('Y' , 'I' , 'O' , '0' ) (1) Index Keys: id index index_oj (Serial, fragments: ALL) Lower Index Filter: ((dba.r.id = dba.c.id AND dba.r.index = dba.c.index ) AND dba.r.index_oj = 0 ) NESTED LOOP JOIN 3) dba.f: INDEX PATH Filters: (dba.r.index = dba.f.index AND dba.f.index > 0 ) (1) Index Keys: id (Serial, fragments: ALL) Lower Index Filter: dba.r.id = dba.f.id NESTED LOOP JOIN

↪ replying to rkusenet

Gary Andrus — Thu, 19 Jun 2003 20:24:01 — source: IIUG Forums & Mailing Lists

If it's not a difference in your server build, then check your data sets. Try running the query on development with the same data set as in production. If that fixes the problem, then I'd suggest that the query plan may be different because of the cardinality of the data.

↪ replying to rkusenet

Murray Wood \\\\(IList\\\\) — Thu, 19 Jun 2003 21:01:12 — source: IIUG Forums & Mailing Lists

Ravi If the number of rows are different and the distribution pattern of the relevant columns are different then it is certainly possible that the optimiser will chose different paths on different databases. Assuming update stats has been run recently on both databases. MW > -----Original Message----- > From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]On > Behalf Of rkusenet > Sent: Friday, 20 June 2003 5:48 a.m. > To: ids@iiug.org > Subject: Different query execution plan [1400] > > > If I run the same query on QA box and our PRODUCTION box, I see > different query execution plan. > > Following settings are used in both QA and PRODUCTION box. > > OPTCOMPIND 0 # To hint the optimizer > OPT_GOAL -1> > The table structure, including fragmentation is same. > > There is only 1 difference between QA and PRODUCTION box. > > QA runs 9.21.UC4XE server > PRODUCTION runs 9.21.UC4 server > > In QA it is using a sequential scan, whereas in production it is not. > in QA there are 300 rows, and in production 50 rows. > > Ravi > > > > QA BOX QUERY: > ------------- > > SELECT r.id, r.index FROM flight_req f, request r,> request_change c WHERE > (f.index > 0) AND c.avail_done = 'N' AND r.avail_done IN > ('Y', 'I', 'O','0') > AND r.index_oj = 0 AND r.id = f.id AND r.index = f.index and r.id = > c.id AND r.index = c.index > > > Estimated Cost: 18 > Estimated # of Rows Returned: 1 > > 1) dba.c: SEQUENTIAL SCAN > > Filters: (dba.c.avail_done = 'N' AND dba.c.index > 0 ) > > 2) dba.f: INDEX PATH > > Filters: (dba.f.index > 0 AND dba.f.index = dba.c.index ) > > (1) Index Keys: id (Serial, fragments: ALL) > Lower Index Filter: dba.f.id = dba.c.id > NESTED LOOP JOIN > > 3) dba.r: INDEX PATH > > Filters: dba.r.avail_done IN ('Y' , 'I' , 'O' , '0' ) > > (1) Index Keys: id index index_oj (Serial, fragments: ALL) > Lower Index Filter: ((dba.r.id = dba.f.id AND > dba.r.index = dba.f.index ) AND dba.r.index_oj > = 0 ) > NESTED LOOP JOIN > > > PRODUCTION BOX QUERY: > --------------------- > > SELECT r.id, r.index FROM flight_req f, request r,> request_change c WHERE > (f.index > 0) AND c.avail_done = 'N' AND r.avail_done IN > ('Y', 'I', 'O','0') > AND r.index_oj = 0 AND r.id = f.id AND r.index = f.index and r.id = > c.id AND r.index = c.index > > > > Estimated Cost: 5 > Estimated # of Rows Returned: 1 > > 1) dba.c: INDEX PATH > > Filters: dba.c.index > 0 > > (1) Index Keys: avail_done (Serial, fragments: ALL) > Lower Index Filter: dba.c.avail_done = 'N' > > 2) dba.r: INDEX PATH > > Filters: dba.r.avail_done IN ('Y' , 'I' , 'O' , '0' ) > > (1) Index Keys: id index index_oj (Serial, fragments: ALL) > Lower Index Filter: ((dba.r.id = dba.c.id AND > dba.r.index = dba.c.index ) AND dba.r.index_oj > = 0 ) > NESTED LOOP JOIN > > 3) dba.f: INDEX PATH > > Filters: (dba.r.index = dba.f.index AND dba.f.index > 0 ) > > (1) Index Keys: id (Serial, fragments: ALL) > Lower Index Filter: dba.r.id = dba.f.id > NESTED LOOP JOIN > >

Glossary

fragmentation