Re: tempspace usage

From: JTommaney <cdos_jtommaney_at_comcast.net>
Date: 6 Oct 2005 07:26:24 -0700
Message-ID: <1128608784.271028.182090@g44g2000cwa.googlegroups.com>

There are a number of factors including version that impact whether a literal vs a bind will generate the same explain-plan. Under both 8i and 9i, one example where you can get different explain with bind-vs-literal when histograms are in play. In that context Oracle can discover whether the actual variable (literal) used is more selective than 'any variable' (bind). Without histograms, there is less information for the optimizer to work with although it still has the high and low values. The literal value may still provide information to the optimizer even without histograms, for example. Below is an example where a literal value above the high_value in dba_tab_columns will (appropriately) generate a fts. Using either a low valued literal, or a bind variable will do an index operation.

So, sometimes literal explain = bind variable explain, sometimes it does not. Assuming this will be run repeatedly, you're going to want to test and evaluate with the bind variable version. Search for runstats on asktom.oracle.com for a good test harness.

Yes, in 9i you can get explain plan behaviour where the optimizer will peek at the actual variable value to compare with histogram info. But the general bind vs literal thing precedes that.

JT(18)@JTDB9I>select utl_raw.cast_to_number(high_value) hv   2 from dba_tab_columns where table_name = 'T10'   3 and column_name = 'C1';

           HV

---

        42314

1 row selected.

JT(18)@JTDB9I>
JT(18)@JTDB9I>delete from plan_table
  2
JT(18)@JTDB9I>explain plan set statement_id = 'EXPLN TEST434' into PLAN_TABLE FOR
  2 select owner, count(*) from t10
  3 where c1 < 50000
  4 group by owner;

Explained.

JT(18)@JTDB9I>
JT(18)@JTDB9I>@explain
JT(18)@JTDB9I>set echo off



------------------------------------------------------------------------------------------------------------

|     |     |     |  OPERATION  OBJECT   [(Temp Space:  temp_space )]
|     |     |     |  [--(ind_col1,ind_col2 ...)]

| Cost| Card|Bytes| [--AP=Access Predicate, FP=Filter Predicate, AFP=both ]

---

|   4K|  34 | 408 | select statement
|   4K|  34 | 408 |  sort  group by
|   1K|   1M|  13M|   table access   **FULL**  T10
|     |     |     |   --FP:T10.C1<50000


------------------------------------------------------------------------------------------------------------

JT(18)@JTDB9I>
JT(18)@JTDB9I>explain plan set statement_id = 'EXPLN TEST434' into PLAN_TABLE FOR
  2 select owner, count(*) from t10
  3 where c1 < 2
  4 group by owner;

Explained.

JT(18)@JTDB9I>
JT(18)@JTDB9I>@explain
JT(18)@JTDB9I>set echo off



------------------------------------------------------------------------------------------------------------

|     |     |     |  OPERATION  OBJECT   [(Temp Space:  temp_space )]
|     |     |     |  [--(ind_col1,ind_col2 ...)]

| Cost| Card|Bytes| [--AP=Access Predicate, FP=Filter Predicate, AFP=both ]

---

|  33 |  19 | 228 | select statement
|  33 |  19 | 228 |  sort  group by
|  31 |  29 | 348 |   table access  by index rowid T10
|   3 |  29 |     |    index  range scan T10_C1
|     |     |     |    --(c1)
|     |     |     |    --AP:T10.C1<2


------------------------------------------------------------------------------------------------------------

JT(18)@JTDB9I>
JT(18)@JTDB9I>explain plan set statement_id = 'EXPLN TEST434' into PLAN_TABLE FOR
  2 select owner, count(*) from t10
  3 where c1 < :bind_var
  4 group by owner;

Explained.

JT(18)@JTDB9I>
JT(18)@JTDB9I>@explain
JT(18)@JTDB9I>set echo off



------------------------------------------------------------------------------------------------------------

|     |     |     |  OPERATION  OBJECT   [(Temp Space:  temp_space )]
|     |     |     |  [--(ind_col1,ind_col2 ...)]

| Cost| Card|Bytes| [--AP=Access Predicate, FP=Filter Predicate, AFP=both ]

---

|   1K|  34 | 408 | select statement
|   1K|  34 | 408 |  sort  group by
|   1K|  53K| 631K|   table access  by index rowid T10
|  23 |   9K|     |    index  range scan T10_C1
|     |     |     |    --(c1)
|     |     |     |    --AP:T10.C1<TO_NUMBER(:Z)


------------------------------------------------------------------------------------------------------------

Received on Thu Oct 06 2005 - 09:26:24 CDT