[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.

--//参考链接:

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

--//大家可以参考链接:

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

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

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

| 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

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

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

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

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

| 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倍.