Toad World® Forums

Editor - explain plan "autosize main column in tree mode" not working.

Works as expected in Session Browser but that option has no effect in the Editor explain plan window.

It seems to be working fine for me. When I do an explain plan, the main column takes up all of the available space (whatever’s left after the other columns, if any, auto-size). When the option is off and I do an explain plan, the main column stays at whatever size I set it to. Can you give me some more details?

If I expand the main column so the scroll bar appears, checking autosize does not autosize within my visible window - it appears to do nothing. Regarding the Session Browser, I was mistaken, it acts the same as the Editor. My Editor explain plan window somehow had the main column expanded quite large, but the Session Browser main column was sized within my visible window. For me, switching the option on and off has no effect on what I see in the explain plan window. I’m assuming autosize will make the main column completely visible with no horizontal scroll bar, no matter how large the main column is sized.

It may be that one of the steps of your EP has a big description, or it is indented very far, so that Autosize results in a wide column anyway.

Following your steps, if I do a “select * from dual” and explain that, then resize the main column wider so that I have a scrollbar, then do another explain plan, the main column shrinks down again and the scrollbar disappears.

But If I repeat the process with “select * from all_all_tables”, which has a lot of indention, the scrollbar does not go away if Toad is not wide enough.

I have a particular sql for which autosize won’t work but I can manually shrink the column and it looks fine.

I believe step 4 below is the culprit.

Plan

SELECT STATEMENT ALL_ROWSCost: 171 Bytes: 229 Cardinality: 1 CPU Cost: 1,674,817 IO Cost: 171

22 FILTER Filter Predicates: :B19<=:B18

21 NESTED LOOPS Cost: 171 Bytes: 229 Cardinality: 1 CPU Cost: 1,674,817 IO Cost: 171

18 NESTED LOOPS Cost: 169 Bytes: 214 Cardinality: 1 CPU Cost: 1,653,602 IO Cost: 169

13 NESTED LOOPS Cost: 114 Bytes: 139 Cardinality: 1 CPU Cost: 1,116,693 IO Cost: 114

10 NESTED LOOPS Cost: 113 Bytes: 122 Cardinality: 1 CPU Cost: 1,105,580 IO Cost: 113

7 NESTED LOOPS Cost: 112 Bytes: 104 Cardinality: 1 CPU Cost: 1,094,618 IO Cost: 112

2 TABLE ACCESS BY INDEX ROWID TABLE MARKET_REG.MC_ISSUE_UNIVERSE_TMP Object Instance: 1 Filter Predicates: “IU”.“MARKET_DIRECTION_CODE”=:B14 OR “IU”.“MARKET_DIRECTION_CODE”=:B11 Cost: 2 Bytes: 90 Cardinality: 2 CPU Cost: 20,696 IO Cost: 2

1 INDEX RANGE SCAN INDEX (UNIQUE) MARKET_REG.MC_ISSUE_UNIVERSE_TMP_PK Search Columns: 2 Access Predicates: “IU”.“PROCESS_DATE”=:B20 AND “IU”.“SYMBOL”>=:B19 AND “IU”.“SYMBOL”<=:B18 Cost: 1 Cardinality: 2 CPU Cost: 10,143 IO Cost: 1

6 PARTITION RANGE ITERATOR Cost: 55 Bytes: 59 Cardinality: 1 CPU Cost: 536,961 IO Cost: 55 Partition #: 9 Partitions determined by Key Values

5 PARTITION LIST SINGLE Cost: 55 Bytes: 59 Cardinality: 1 CPU Cost: 536,961 IO Cost: 55 Partition #: 10 Partitions determined by Key Values

4 TABLE ACCESS BY LOCAL INDEX ROWID TABLE ONT.MAIN_ORDER Object Instance: 2 Filter Predicates: (“MO”.“ORDER_CAPACITY_CODE”=:B17 OR “MO”.“ORDER_CAPACITY_CODE”=:B16 OR “MO”.“ORDER_CAPACITY_CODE”=:B15) AND (“MO”.“ORDER_SIDE_CODE”=:B13 OR “MO”.“ORDER_SIDE_CODE”=:B12 OR “MO”.“ORDER_SIDE_CODE”=:B6 OR “MO”.“ORDER_SIDE_CODE”=:B5 OR “MO”.“ORDER_SIDE_CODE”=:B4 OR “MO”.“ORDER_SIDE_CODE”=:B10 OR “MO”.“ORDER_SIDE_CODE”=:B9 OR “MO”.“ORDER_SIDE_CODE”=:B8 OR “MO”.“ORDER_SIDE_CODE”=:B7 OR “MO”.“ORDER_SIDE_CODE”=:B6 OR “MO”.“ORDER_SIDE_CODE”=:B5 OR “MO”.“ORDER_SIDE_CODE”=:B4) AND (“IU”.“MARKET_DIRECTION_CODE”=:B14 AND (“MO”.“ORDER_SIDE_CODE”=:B13 OR “MO”.“ORDER_SIDE_CODE”=:B12 OR “MO”.“ORDER_SIDE_CODE”=:B6 OR “MO”.“ORDER_SIDE_CODE”=:B5 OR “MO”.“ORDER_SIDE_CODE”=:B4) OR “IU”.“MARKET_DIRECTION_CODE”=:B11 AND (“MO”.“ORDER_SIDE_CODE”=:B10 OR “MO”.“ORDER_SIDE_CODE”=:B9 OR “MO”.“ORDER_SIDE_CODE”=:B8 OR “MO”.“ORDER_SIDE_CODE”=:B7 OR “MO”.“ORDER_SIDE_CODE”=:B6 OR “MO”.“ORDER_SIDE_CODE”=:B5 OR “MO”.“ORDER_SIDE_CODE”=:B4)) Cost: 55 Bytes: 59 Cardinality: 1 CPU Cost: 536,961 IO Cost: 55 Partition #: 10 Partitions determined by Key Values

3 INDEX RANGE SCAN INDEX ONT.MAIN_ORDER_IDX4 Search Columns: 3 Access Predicates: “MO”.“FUNCTIONAL_GROUP_CODE”=TO_NUMBER(:B3) AND “MO”.“CREATION_DTTM”>=“IU”.“CLOSING_START_DTTM” AND “MO”.“SYMBOL”=“IU”.“SYMBOL” AND “MO”.“CREATION_DTTM”<=“IU”.“CLOSING_DTTM” Filter Predicates: “MO”.“SYMBOL”>=:B19 AND “MO”.“SYMBOL”<=:B18 AND “MO”.“SYMBOL”=“IU”.“SYMBOL” Cost: 55 Cardinality: 1 CPU Cost: 536,961 IO Cost: 55 Partition #: 10 Partitions determined by Key Values

9 TABLE ACCESS BY INDEX ROWID TABLE (TEMP) CORPORATE_SUPPORT.TRADING_ACCOUNT_GTT Object Instance: 4 Cost: 1 Bytes: 18 Cardinality: 1 CPU Cost: 10,963 IO Cost: 1

8 INDEX RANGE SCAN INDEX CORPORATE_SUPPORT.TRADING_AVVOUNT_GTT_IDX1 Search Columns: 1 Access Predicates: “TA”.“TRADING_ACCOUNT_ID”=“MO”.“SOURCE_TRADING_ACCT_ID” Cost: 0 Cardinality: 1 CPU Cost: 1,050 IO Cost: 0

12 TABLE ACCESS BY INDEX ROWID TABLE (TEMP) CORPORATE_SUPPORT.BUSINESS_UNIT_GTT Object Instance: 5 Filter Predicates: “BU”.“BUSINESS_UNIT_TYPE_CODE”=TO_NUMBER(:B2) Cost: 1 Bytes: 17 Cardinality: 1 CPU Cost: 11,113 IO Cost: 1

11 INDEX RANGE SCAN INDEX CORPORATE_SUPPORT.BUSINESS_UNIT_IDX1 Search Columns: 1 Access Predicates: “BU”.“BUSINESS_UNIT_ID”=“TA”.“BUSINESS_UNIT_ID” Cost: 0 Cardinality: 1 CPU Cost: 1,050 IO Cost: 0

17 PARTITION RANGE ITERATOR Cost: 55 Bytes: 75 Cardinality: 1 CPU Cost: 536,908 IO Cost: 55 Partition #: 17 Partitions determined by Key Values

16 PARTITION LIST SINGLE Cost: 55 Bytes: 75 Cardinality: 1 CPU Cost: 536,908 IO Cost: 55 Partition #: 18 Partitions determined by Key Values

15 TABLE ACCESS BY LOCAL INDEX ROWID TABLE ONT.MAIN_ORDER_ACTIVITY Object Instance: 3 Filter Predicates: “MOA”.“ORDER_ID”=“MO”.“ORDER_ID” Cost: 55 Bytes: 75 Cardinality: 1 CPU Cost: 536,908 IO Cost: 55 Partition #: 18 Partitions determined by Key Values

14 INDEX RANGE SCAN INDEX ONT.MAIN_ORDER_ACTIVITY_IDX3 Search Columns: 2 Access Predicates: “MOA”.“FUNCTIONAL_GROUP_CODE”=TO_NUMBER(:B3) AND “MOA”.“ACTIVITY_DTTM”>=“IU”.“CLOSING_START_DTTM” AND “MOA”.“ACTIVITY_DTTM”<=“IU”.“CLOSING_DTTM” Cost: 55 Cardinality: 1 CPU Cost: 536,908 IO Cost: 55 Partition #: 18 Partitions determined by Key Values

20 TABLE ACCESS BY INDEX ROWID TABLE (TEMP) CHX_COMMON.ACTIVITY_EVENT_GTT Object Instance: 7 Filter Predicates: “AE1”.“MROL_MC_EVENT_PARM”=:B1 Cost: 2 Bytes: 15 Cardinality: 1 CPU Cost: 21,216 IO Cost: 2

19 INDEX RANGE SCAN INDEX (UNIQUE) CHX_COMMON.ACTIVITY_EVENT_GTT_PK Search Columns: 2 Access Predicates: “MOA”.“ACTIVITY_AUTHOR_SVC_TYP_CODE”=“AE1”.“SERVICE_TYPE_CODE” AND “MOA”.“ACTIVITY_EVENT_ID”=“AE1”.“ACTIVITY_EVENT_ID” Cost: 1 Cardinality: 1 CPU Cost: 10,793 IO Cost: 1

Can you send me the Table and Index DDL and query so I can try to reproduce it?

Oh, even better - rt-click in the EP area, choose “Save plan to XML file” and send me the XML file. I don’t need the query or DB objects that way.

Sent SQL and DDL to your email

I simplified the SQL greatly but can still reproduce if I shrink the Editor window so it starts scrolling. Re-executing the explain plan does not autosize, but I can manually adjust and it formats correctly. But re-executing explain plan causes scrolling again.

You should have the original explain plan XML also.

Yeah I can reproduce it now, thanks. I’ll let you know when I have a solution. You’re right, it’s that big Filter Predicate that’s causing it.

OK, it should be better now. Give it a try next beta.

Thanks John, and thanks for the filename in Session Browser waits - has come in handy many times so far.

You’re welcome Dale.