[20170619]11G expand sql text.txt

[20170619]11G expand sql text.txt

--//12G下存在dbms_utility包.expand_sql_text擴充套件sql語句,查詢到轉換後執行的sql語句.
--//昨天看jonathanlewis.wordpress.com/2017/06/14/unpivot/,發現11g上也有.使用的是dbms_sql2.expand_sql_text.

--//作者的例子使用Unpivot可以發現存在5次全表掃描.自己也重複測試,我想透過另外的例子演示dbms_sql2.expand_sql_text.
--//一般要想看轉換sql語句,要透過10053事件或者dbms_sqldiag.dump_trace.
--//參考連結:http://blog.itpub.net/267265/viewspace-2140767/

1.環境:
SCOTT@book> @ &r/ver1
PORT_STRING         VERSION    BANNER
------------------- ---------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

--//大家可以參考連結:http://blog.itpub.net/267265/viewspace-1593068/

create table t1 as select rownum id,rownum||'t1' data from dual connect by level<=5;
create table t2 as select rownum+1 id,rownum||'t2' data from dual connect by level<=5;

--//分析表略.

2.測試:
SCOTT@book>  select * from t1;
ID DATA
-- -----
1 1t1
2 2t1
3 3t1
4 4t1
5 5t1

SCOTT@book>  select * from t2;
ID DATA
-- -----
2 1t2
3 2t2
4 3t2
5 4t2
6 5t2

SCOTT@book> set null NULL
SCOTT@book> select * from t1 left join t2 on t1.id=t2.id and t1.id=2;
ID DATA ID   DATA
-- ---- ---- ----
2 2t1     2 1t2
4 4t1  NULL NULL
3 3t1  NULL NULL
1 1t1  NULL NULL
5 5t1  NULL NULL

SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  5katgcygcphpc, child number 0
-------------------------------------
select * from t1 left join t2 on t1.id=t2.id and t1.id=2
Plan hash value: 1823443478
------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |       |     6 (100)|          |      5 |00:00:00.01 |       5 |       |       |          |
|*  1 |  HASH JOIN OUTER   |      |      1 |      5 |    70 |     6   (0)| 00:00:01 |      5 |00:00:00.01 |       5 |  1645K|  1645K|  900K (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |      5 |    35 |     3   (0)| 00:00:01 |      5 |00:00:00.01 |       2 |       |       |          |
|   3 |   TABLE ACCESS FULL| T2   |      1 |      5 |    35 |     3   (0)| 00:00:01 |      5 |00:00:00.01 |       3 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$9E43CB6E
   2 - SEL$9E43CB6E / T1@SEL$2
   3 - SEL$9E43CB6E / T2@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."ID"="T2"."ID" AND "T1"."ID"=CASE  WHEN ("T2"."ID" IS NOT NULL) THEN 2 ELSE 2 END )

--//注意看access條件,如果你不看內部,很難想像執行的語句條件是這樣....

3.使用dbms_sql2.expand_sql_text看看.

$ cat expand_sql_text11g.txt
variable m_sql_out clob

declare
        m_sql_in    clob :=
                'select * from t1 left join t2 on t1.id=t2.id and t1.id=2 ' ;
begin

        dbms_sql2.expand_sql_text(        -- 11g
--      dbms_utility.expand_sql_text(     -- 12c
                m_sql_in,
                :m_sql_out
        );

end;
/

set long 20000
column m_sql_out format a160
print m_sql_out

--//執行如下:
SCOTT@book> @ expand_sql_text11g.txt
PL/SQL procedure successfully completed.

M_SQL_OUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT "A1"."ID_0" "ID","A1"."DATA_1" "DATA","A1"."ID_2" "ID","A1"."DATA_3" "DATA" FROM  (SELECT "A3"."ID" "ID_0","A3"."DATA" "DATA_1","A2"."ID_0" "ID_2","A2"."
DATA_1" "DATA_3" FROM "SCOTT"."T1" "A3", LATERAL( (SELECT "A4"."ID" "ID_0","A4"."DATA" "DATA_1" FROM "SCOTT"."T2" "A4" WHERE "A3"."ID"="A4"."ID" AND "A3"."ID"=2
))(+) "A2") "A1"

--//在toad下格式化如下:
SELECT "A1"."ID_0" "ID"
      ,"A1"."DATA_1" "DATA"
      ,"A1"."ID_2" "ID"
      ,"A1"."DATA_3" "DATA"
  FROM (SELECT "A3"."ID" "ID_0"
              ,"A3"."DATA" "DATA_1"
              ,"A2"."ID_0" "ID_2"
              ,"A2"."DATA_1" "DATA_3"
          FROM "SCOTT"."T1" "A3"
              ,LATERAL
               (
                  (SELECT "A4"."ID" "ID_0", "A4"."DATA" "DATA_1"
                     FROM "SCOTT"."T2" "A4"
                    WHERE "A3"."ID" = "A4"."ID" AND "A3"."ID" = 2)
               )(+) "A2") "A1";
--//不過你自己執行拿上面的sql語句執行會報錯,提示
ORA-00907: missing right parenthesis

4.與使用dbms_sqldiag.dump_trace對比看看.
$ cat 10053x.sql
execute dbms_sqldiag.dump_trace(p_sql_id=>'&1',p_child_number=>&2,p_component=>'Compiler',p_file_id=>'a'||'&&1');

SCOTT@book> @ &r/10053x 5katgcygcphpc 0
PL/SQL procedure successfully completed.

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T1"."ID" "ID","T1"."DATA" "DATA","T2"."ID" "ID","T2"."DATA" "DATA" FROM "SCOTT"."T1" "T1","SCOTT"."T2" "T2"
WHERE "T1"."ID"="T2"."ID"(+) AND "T1"."ID"=CASE  WHEN ("T2"."ID"(+) IS NOT NULL) THEN 2 ELSE 2 END

--//格式化如下:
SELECT "T1"."ID" "ID"
      ,"T1"."DATA" "DATA"
      ,"T2"."ID" "ID"
      ,"T2"."DATA" "DATA"
  FROM "SCOTT"."T1" "T1", "SCOTT"."T2" "T2"
WHERE     "T1"."ID" = "T2"."ID"(+)
       AND "T1"."ID" = CASE WHEN ("T2"."ID"(+) IS NOT NULL) THEN 2 ELSE 2 END;

SCOTT@book> set null NULL
SCOTT@book> /
ID DATA ID   DATA
-- ---- ---- -----
2 2t1     2 1t2
4 4t1  NULL NULL
3 3t1  NULL NULL
1 1t1  NULL NULL
5 5t1  NULL NULL

--//兩者不一致,而且dbms_sql2.expand_sql_text顯示的不能執行.
--//使用dbms_sqldiag.dump_trace看到的是正確的.

--//不過我測試作者的例子:
create table t1( id, col1, col2, col3, col4, col5, padding )
cache
pctfree 95 pctused 5
-- compress for query low
as
select
        1, 100 , 200 , 300 , 400 , 500,rpad('x',100)
from
        all_objects
where
        rownum <= 50000 ;
       
execute dbms_stats.gather_table_stats(user,'t1',method_opt=>'for all columns size 1')
 
select
        /*+ gather_plan_statistics find this */
        id, max(val) as high_val
from
        t1
unpivot include nulls (
        val for source in (col1, col2, col3, col4, col5)
)
group by id
order by id
;

SCOTT@78> @ expand_sql_text11g
PL/SQL procedure successfully completed.

M_SQL_OUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT "A1"."ID" "ID",MAX("A1"."VAL") "HIGH_VAL" FROM  ( (SELECT "A3"."ID" "ID","A3"."PADDING" "PADDING",'COL1' "SOURCE","A3"."COL1" "VAL" FROM "SCOTT"."T1" "A3
") UNION ALL  (SELECT "A4"."ID" "ID","A4"."PADDING" "PADDING",'COL2' "SOURCE","A4"."COL2" "VAL" FROM "SCOTT"."T1" "A4") UNION ALL  (SELECT "A5"."ID" "ID","A5"."
PADDING" "PADDING",'COL3' "SOURCE","A5"."COL3" "VAL" FROM "SCOTT"."T1" "A5") UNION ALL  (SELECT "A6"."ID" "ID","A6"."PADDING" "PADDING",'COL4' "SOURCE","A6"."CO
L4" "VAL" FROM "SCOTT"."T1" "A6") UNION ALL  (SELECT "A7"."ID" "ID","A7"."PADDING" "PADDING",'COL5' "SOURCE","A7"."COL5" "VAL" FROM "SCOTT"."T1" "A7")) "A1" GRO
UP BY "A1"."ID" ORDER BY "A1"."ID"

--//格式化如下:
/* Formatted on 2017/6/19 9:19:09 (QP5 v5.269.14213.34769) */
  SELECT "A1"."ID" "ID", MAX ("A1"."VAL") "HIGH_VAL"
    FROM ( (SELECT "A3"."ID" "ID"
                  ,"A3"."PADDING" "PADDING"
                  ,'COL1' "SOURCE"
                  ,"A3"."COL1" "VAL"
              FROM "SCOTT"."T1" "A3")
          UNION ALL
          (SELECT "A4"."ID" "ID"
                 ,"A4"."PADDING" "PADDING"
                 ,'COL2' "SOURCE"
                 ,"A4"."COL2" "VAL"
             FROM "SCOTT"."T1" "A4")
          UNION ALL
          (SELECT "A5"."ID" "ID"
                 ,"A5"."PADDING" "PADDING"
                 ,'COL3' "SOURCE"
                 ,"A5"."COL3" "VAL"
             FROM "SCOTT"."T1" "A5")
          UNION ALL
          (SELECT "A6"."ID" "ID"
                 ,"A6"."PADDING" "PADDING"
                 ,'COL4' "SOURCE"
                 ,"A6"."COL4" "VAL"
             FROM "SCOTT"."T1" "A6")
          UNION ALL
          (SELECT "A7"."ID" "ID"
                 ,"A7"."PADDING" "PADDING"
                 ,'COL5' "SOURCE"
                 ,"A7"."COL5" "VAL"
             FROM "SCOTT"."T1" "A7")) "A1"
GROUP BY "A1"."ID"
ORDER BY "A1"."ID"

--//執行計劃如下:
Plan hash value: 2087176859
--------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |      1 |        |       | 22834 (100)|          |      1 |00:00:00.35 |   83385 |       |       |          |
|   1 |  SORT GROUP BY       |      |      1 |      1 |    16 | 22834   (1)| 00:04:35 |      1 |00:00:00.35 |   83385 |  2048 |  2048 | 2048  (0)|
|   2 |   VIEW               |      |      1 |    250K|  3906K| 22828   (1)| 00:04:34 |    250K|00:00:00.28 |   83385 |       |       |          |
|   3 |    UNION-ALL         |      |      1 |        |       |            |          |    250K|00:00:00.24 |   83385 |       |       |          |
|   4 |     TABLE ACCESS FULL| T1   |      1 |  50000 |   292K|  4565   (1)| 00:00:55 |  50000 |00:00:00.05 |   16677 |       |       |          |
|   5 |     TABLE ACCESS FULL| T1   |      1 |  50000 |   292K|  4566   (1)| 00:00:55 |  50000 |00:00:00.04 |   16677 |       |       |          |
|   6 |     TABLE ACCESS FULL| T1   |      1 |  50000 |   292K|  4566   (1)| 00:00:55 |  50000 |00:00:00.03 |   16677 |       |       |          |
|   7 |     TABLE ACCESS FULL| T1   |      1 |  50000 |   292K|  4566   (1)| 00:00:55 |  50000 |00:00:00.03 |   16677 |       |       |          |
|   8 |     TABLE ACCESS FULL| T1   |      1 |  50000 |   292K|  4566   (1)| 00:00:55 |  50000 |00:00:00.03 |   16677 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SET$1 / A1@SEL$1
   3 - SET$1
   4 - SEL$2 / A3@SEL$2
   5 - SEL$3 / A4@SEL$3
   6 - SEL$4 / A5@SEL$4
   7 - SEL$5 / A6@SEL$5
   8 - SEL$6 / A7@SEL$6

--//你可以發現這樣邏輯讀翻了5倍.