Oninit cdi / Archive
Talk to an expert →

XPS: Unusual Exection Plan?

Posted in 1998

Topics: Performance & Tuning

Euan Dempster — — source: Informix-list mailing list archive (1991-1998)

--------------7FA08A392E977A3BB2382092 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Hi, Can anybody help me. I have an AS3AP table of 30000 tuples sitting on one coserver (7) and on one disc of that coserver. I do a max on the 'entier' attribute and it decides to redistribute in a most unusual way. Does anybody know why it redistributes the tuples in this way? The execution plan is: QUERY: ------ SELECT MAX(entier) FROM uniques30z WHERE cle > 0 Estimated Cost: 4 Estimated # of Rows Returned: 1 1) informix.uniques30z: SEQUENTIAL SCAN Filters: informix.uniques30z.cle > 0 # of Secondary Threads = 10 XMP Query Plan oper segid brid width ------------------------------ scan 3 0 1 group 2 0 8 group 1 0 1 XMP Query Statistics Cosvr_ID: 1 Plan_ID: 23 type segid brid information ---- ----- ---- ----------- scan 3 0 inst cosvr rows_prod rows_scan ---- ----- --------- --------- 0 7 30000 30000 -------------------------------- 1 30000 30000 group 2 0 inst cosvr rows_prod rows_cons mem ovfl ---- ----- --------- --------- --- ---- 0 1 1 1 -1 -1 1 2 1 8 -1 -1 2 3 1 1 -1 -1 3 4 1 1 -1 -1 4 5 1 1 -1 -1 5 6 1 1 -1 -1 6 7 1 29986 -1 -1 7 8 1 1 -1 -1 -------------------------------------------------- 8 8 30000 (-1) group 1 0 inst cosvr rows_prod rows_cons mem ovfl ---- ----- --------- --------- --- ---- 0 1 1 8 -1 -1 -------------------------------------------------- 1 1 8 (-1) Any suggestions would be gratefully received. Regards -- Euan W Dempster Dept. of Computing & El. Eng. Tel: +44 131 449 5111 ext. 4191/4194 Heriot-Watt University, Riccarton, Fax: +44 131 451 3431 Edinburgh, Scotland, UK, EH14 4AS Email: euan@cee.hw.ac.uk --------------7FA08A392E977A3BB2382092 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit <HTML> Hi, <BR>&nbsp;&nbsp;&nbsp; Can anybody help me. I have an AS3AP table of 30000 tuples sitting on one coserver (7) and on <BR>one disc of that coserver. I do a max on the 'entier' attribute and it decides to redistribute in a most unusual way. Does anybody know why it redistributes the tuples in this way? <P>The execution plan is: <P><TT>QUERY:</TT> <BR><TT>------</TT> <BR><TT>SELECT MAX(entier)</TT> <BR><TT>FROM uniques30z</TT> <BR><TT>WHERE cle > 0</TT><TT></TT> <P><TT>Estimated Cost: 4</TT> <BR><TT>Estimated # of Rows Returned: 1</TT><TT></TT> <P><TT>1) informix.uniques30z: SEQUENTIAL SCAN</TT><TT></TT> <P><TT>&nbsp;&nbsp;&nbsp; Filters: informix.uniques30z.cle > 0</TT> <BR><TT></TT>&nbsp;<TT></TT> <P><TT># of Secondary Threads = 10</TT><TT></TT> <P><TT>XMP Query Plan</TT><TT></TT> <P><TT>&nbsp; oper&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; segid&nbsp; brid&nbsp;&nbsp; width</TT> <BR><TT>&nbsp; ------------------------------</TT> <BR><TT>&nbsp; scan&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1</TT> <BR><TT>&nbsp; group&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8</TT> <BR><TT>&nbsp; group&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1</TT><TT></TT> <P><TT>XMP Query Statistics</TT><TT></TT> <P><TT>&nbsp;Cosvr_ID: 1</TT> <BR><TT>&nbsp;Plan_ID: 23</TT><TT></TT> <P><TT>&nbsp; type&nbsp; segid brid information</TT> <BR><TT>&nbsp; ----&nbsp; ----- ---- -----------</TT> <BR><TT>&nbsp; scan&nbsp;&nbsp; 3&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp; inst cosvr&nbsp; rows_prod&nbsp; rows_scan</TT> <BR><TT>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ---- -----&nbsp; ---------&nbsp; ---------</TT> <BR><TT>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp; 7&nbsp;&nbsp;&nbsp; 30000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 30000</TT> <BR><TT>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; --------------------------------</TT> <BR><TT>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 30000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 30000</TT><TT></TT> <P><TT>&nbsp; group&nbsp; 2&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp; inst cosvr&nbsp; rows_prod&nbsp; rows_cons&nbsp;&nbsp; mem&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ovfl</TT> <BR><TT>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ---- -----&nbsp; ---------&nbsp; ---------&nbsp;&nbsp; ---&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ----</TT> <BR><TT>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -1</TT> <BR><TT>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -1</TT> <BR><TT>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp; 3&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -1</TT> <BR><TT>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3&nbsp;&nbsp;&nbsp; 4&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -1</TT> <BR><TT>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&