[20170620]11G 12c expand sql text.txt
--//原来写的脚本只能分别在11g,12c单独使用.上午花一点点时间.把两者整合起来.
--//讨论链接:http://www.itpub.net/thread-2088981-1-1.html
--//再次感谢solomon_007的指点:
set long 20000
set serveroutput on
declare
L_sqltext clob := null;
l_version varchar2(3) := null;
l_sql clob := null;
l_result clob := null;
begin
select regexp_replace(version,'\..*') into l_version from v$instance;
select sql_fulltext into l_sqltext from v$sqlarea where sql_id='&&1';
if l_version = '11' then
l_sql := 'begin
dbms_sql2.expand_sql_text( :a,:b );
end;';
elsif l_version = '12' then
l_sql := 'begin
dbms_utility.expand_sql_text(:a,:b);
end;';
end if;
execute immediate l_sql using in l_sqltext,out l_result;
dbms_output.put_line(l_result);
end;
/
set serveroutput off
--//继续拿原来的例子测试:
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( 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
;
--//获得sql_id=2v7uzcnf4kj9s.
SCOTT@book> @ &r/expand_sql_text 2v7uzcnf4kj9s
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"
PL/SQL procedure successfully completed.
--//toad格式化看看:
/* Formatted on 2017/6/20 16:10:46 (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";
--//可以看出使用unpivot实际上内部要全表扫描T1 5次.