马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。
您需要 登录 才可以下载或查看，没有账号？立即注册
x
[20250526]SORT GROUP BY与HASH GROUP BY.txt
--//我记忆里大约在10g开始oracle的group by采用hash group by算法，其效果就是其效果集不再按照group by字段排序，也提醒一些
--//开发人员必须显示控制排序方式，可以通过提示USE_HASH_AGGREGATION/NO_USE_HASH_AGGREGATION来控制采用的算法。做一个例子说
--//明一些细节，有时候采用hash group by算法并不是最佳的情况：
1.环境：
SCOTT@book01p> @ ver2
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 21.0.0.0.0
BANNER : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.
SCOTT@book01p> @ sqlhintz "sort_agg|hash_agg"
NAME SQL_FEATURE CLASS INVERSE TARGET_LEVEL PROPERTY VERSION VERSION_OUTLINE CON_ID
----------------------- ----------- -------------------- ----------------------- ------------ -------- -------------- --------------- ------
USE_HASH_AGGREGATION QKSFM_ALL USE_HASH_AGGREGATION NO_USE_HASH_AGGREGATION 2 0 10.2.0.1 10.2.0.5 0
NO_USE_HASH_AGGREGATION QKSFM_ALL USE_HASH_AGGREGATION USE_HASH_AGGREGATION 2 0 10.2.0.1 10.2.0.5 0
2.测试：
SCOTT@book01p> create table t1 as select * from all_objects;
Table created.
--//分析表略。
SCOTT@book01p> @ sl all
alter session set statistics_level = all;
Session altered.
SCOTT@book01p> Select object_type,sum(object_id),avg(object_id),count(*) from t1 group by object_type;
OBJECT_TYPE SUM(OBJECT_ID) AVG(OBJECT_ID) COUNT(*)
------------------------------ -------------- -------------- ----------
INDEX 47963848 20110.6281 2385
TABLE 46586395 19435.292 2397
CLUSTER 3127 312.7 10
EDITION 134 134 1
SYNONYM 451440006 37346.1289 12088
SEQUENCE 5405583 51481.7429 105
DIRECTORY 198343 18031.1818 11
PACKAGE 30372636 37636.4758 807
VIEW 114811847 15025.7619 7641
FUNCTION 23268633 57171.0885 407
PROCEDURE 3958952 43988.3556 90
TYPE 77026974 34775.1576 2215
OPERATOR 3005473 50091.2167 60
TABLE PARTITION 23209585 40224.5841 577
INDEX PARTITION 29357424 79775.6087 368
TYPE BODY 7464132 66054.2655 113
TABLE SUBPARTITION 512080 16002.5 32
PACKAGE BODY 20327004 57583.5807 353
LIBRARY 3731825 52560.9155 71
CONSUMER GROUP 378585 21032.5 18
JOB CLASS 63169 21056.3333 3
DESTINATION 42133 21066.5 2
SCHEDULE 85012 21253 4
WINDOW 190681 21186.7778 9
SCHEDULER GROUP 84768 21192 4
EVALUATION CONTEXT 67978 22659.3333 3
TRIGGER 7458043 51791.9653 144
RULE SET 92298 23074.5 4
XML SCHEMA 1753783 44968.7949 39
INDEXTYPE 654696 59517.8182 11
JAVA CLASS 1701232733 44526.729 38207
JAVA RESOURCE 100771276 58965.0532 1709
JAVA DATA 1168076 68710.3529 17
JAVA SOURCE 264798 88266 3
34 rows selected.
SCOTT@book01p> @ dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 43vzz3p38fqdy, child number 0
-------------------------------------
Select object_type,sum(object_id),avg(object_id),count(*) from t1
group by object_type
Plan hash value: 136660032
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 415 (100)| | 34 |00:00:00.01 | 1479 |
| 1 | HASH GROUP BY | | 1 | 34 | 510 | 415 (2)| 00:00:01 | 34 |00:00:00.01 | 1479 |
| 2 | TABLE ACCESS FULL| T1 | 1 | 69908 | 1024K| 412 (1)| 00:00:01 | 69908 |00:00:00.01 | 1479 |
---------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / "T1"@"SEL$1"
21 rows selected.
--//缺省采用HASH GROUP BY，注意看输出效果并没有按照OBJECT_TYPE排序。
--//给sql语句参加order by 1;后：
SCOTT@book01p> Select object_type,sum(object_id),avg(object_id),count(*) from t1 group by object_type order by 1;
OBJECT_TYPE SUM(OBJECT_ID) AVG(OBJECT_ID) COUNT(*)
------------------------------ -------------- -------------- ----------
CLUSTER 3127 312.7 10
CONSUMER GROUP 378585 21032.5 18
DESTINATION 42133 21066.5 2
DIRECTORY 198343 18031.1818 11
EDITION 134 134 1
EVALUATION CONTEXT 67978 22659.3333 3
FUNCTION 23268633 57171.0885 407
INDEX 47963848 20110.6281 2385
INDEX PARTITION 29357424 79775.6087 368
INDEXTYPE 654696 59517.8182 11
JAVA CLASS 1701232733 44526.729 38207
JAVA DATA 1168076 68710.3529 17
JAVA RESOURCE 100771276 58965.0532 1709
JAVA SOURCE 264798 88266 3
JOB CLASS 63169 21056.3333 3
LIBRARY 3731825 52560.9155 71
OPERATOR 3005473 50091.2167 60
PACKAGE 30372636 37636.4758 807
PACKAGE BODY 20327004 57583.5807 353
PROCEDURE 3958952 43988.3556 90
RULE SET 92298 23074.5 4
SCHEDULE 85012 21253 4
SCHEDULER GROUP 84768 21192 4
SEQUENCE 5405583 51481.7429 105
SYNONYM 451440006 37346.1289 12088
TABLE 46586395 19435.292 2397
TABLE PARTITION 23209585 40224.5841 577
TABLE SUBPARTITION 512080 16002.5 32
TRIGGER 7458043 51791.9653 144
TYPE 77026974 34775.1576 2215
TYPE BODY 7464132 66054.2655 113
VIEW 114811847 15025.7619 7641
WINDOW 190681 21186.7778 9
XML SCHEMA 1753783 44968.7949 39
34 rows selected.
SCOTT@book01p> @ dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID db1k70a7favpv, child number 0
-------------------------------------
Select object_type,sum(object_id),avg(object_id),count(*) from t1
group by object_type order by 1
Plan hash value: 3946799371
------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | 415 (100)| | 34 |00:00:00.02 | 1479 | | | |
| 1 | SORT GROUP BY | | 1 | 34 | 510 | 415 (2)| 00:00:01 | 34 |00:00:00.02 | 1479 | 4096 | 4096 | 4096 (0)|
| 2 | TABLE ACCESS FULL| T1 | 1 | 69908 | 1024K| 412 (1)| 00:00:01 | 69908 |00:00:00.01 | 1479 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / "T1"@"SEL$1"
21 rows selected.
--//执行语句参加order by后，采用SORT GROUP BY算法，注意看输出效果按照OBJECT_TYPE字段排序。
3.尝试参加提示：
--//给有order by 字段的语句参加USE_HASH_AGGREGATION提示
SCOTT@book01p> Select /*+ USE_HASH_AGGREGATION */ object_type,sum(object_id),avg(object_id),count(*) from t1 group by object_type order by 1;
--//输出略。
SCOTT@book01p> @ dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID a6k9ajfah8vr0, child number 0
-------------------------------------
Select /*+ USE_HASH_AGGREGATION */ object_type,sum(object_id),avg(object
_id),count(*) from t1 group by object_type order by 1
Plan hash value: 2808104874
-------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | 415 (100)| | 34 |00:00:00.01 | 1479 | | | |
| 1 | SORT ORDER BY | | 1 | 34 | 510 | 415 (2)| 00:00:01 | 34 |00:00:00.01 | 1479 | 4096 | 4096 | 4096 (0)|
| 2 | HASH GROUP BY | | 1 | 34 | 510 | 415 (2)| 00:00:01 | 34 |00:00:00.01 | 1479 | 1264K| 1264K| |
| 3 | TABLE ACCESS FULL| T1 | 1 | 69908 | 1024K| 412 (1)| 00:00:01 | 69908 |00:00:00.01 | 1479 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / "T1"@"SEL$1"
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
1 - SEL$1
- USE_HASH_AGGREGATION
--//先使用HASH GROUP BY，再使用SORT ORDER BY输出。
--//给没有order by 字段的语句参加NO_USE_HASH_AGGREGATION提示
SCOTT@book01p> Select /*+ NO_USE_HASH_AGGREGATION */ object_type,sum(object_id),avg(object_id),count(*) from t1 group by object_type ;
OBJECT_TYPE SUM(OBJECT_ID) AVG(OBJECT_ID) COUNT(*)
------------------------------ -------------- -------------- ----------
CLUSTER 3127 312.7 10
CONSUMER GROUP 378585 21032.5 18
DESTINATION 42133 21066.5 2
DIRECTORY 198343 18031.1818 11
....
WINDOW 190681 21186.7778 9
XML SCHEMA 1753783 44968.7949 39
34 rows selected.
--//可以发现按照OBJECT_TYPE字段排序输出。
SCOTT@book01p> @ dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 654wq05g36wzj, child number 0
-------------------------------------
Select /*+ NO_USE_HASH_AGGREGATION */
object_type,sum(object_id),avg(object_id),count(*) from t1 group by
object_type
Plan hash value: 3946799371
------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | 415 (100)| | 34 |00:00:00.02 | 1479 | | | |
| 1 | SORT GROUP BY | | 1 | 34 | 510 | 415 (2)| 00:00:01 | 34 |00:00:00.02 | 1479 | 4096 | 4096 | 4096 (0)|
| 2 | TABLE ACCESS FULL| T1 | 1 | 69908 | 1024K| 412 (1)| 00:00:01 | 69908 |00:00:00.01 | 1479 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / "T1"@"SEL$1"
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
1 - SEL$1
- NO_USE_HASH_AGGREGATION
--//采用SORT GROUP BY 算法。
4.继承问题展开：
--//如果将count(*)换成count(distinct object_name)，变成分组后统计有多少个差别object_name的情况。
SCOTT@book01p> Select object_type,sum(object_id),avg(object_id),count(distinct object_name) from t1 group by object_type;
OBJECT_TYPE SUM(OBJECT_ID) AVG(OBJECT_ID) COUNT(DISTINCTOBJECT_NAME)
------------------------------ -------------- -------------- --------------------------
INDEX 47963848 20110.6281 2381
TABLE 46586395 19435.292 2391
SYNONYM 451440006 37346.1289 12077
CLUSTER 3127 312.7 10
SEQUENCE 5405583 51481.7429 105
VIEW 114811847 15025.7619 7638
PACKAGE 30372636 37636.4758 807
TYPE 77026974 34775.1576 2067
INDEX PARTITION 29357424 79775.6087 225
TABLE PARTITION 23209585 40224.5841 228
PROCEDURE 3958952 43988.3556 90
FUNCTION 23268633 57171.0885 407
CONSUMER GROUP 378585 21032.5 18
SCHEDULE 85012 21253 4
WINDOW 190681 21186.7778 9
OPERATOR 3005473 50091.2167 60
LIBRARY 3731825 52560.9155 71
PACKAGE BODY 20327004 57583.5807 353
XML SCHEMA 1753783 44968.7949 39
TRIGGER 7458043 51791.9653 144
RULE SET 92298 23074.5 4
JAVA CLASS 1701232733 44526.729 38207
JAVA RESOURCE 100771276 58965.0532 1708
INDEXTYPE 654696 59517.8182 11
TYPE BODY 7464132 66054.2655 113
JAVA DATA 1168076 68710.3529 17
EDITION 134 134 1
DIRECTORY 198343 18031.1818 11
JOB CLASS 63169 21056.3333 3
SCHEDULER GROUP 84768 21192 4
DESTINATION 42133 21066.5 2
TABLE SUBPARTITION 512080 16002.5 1
JAVA SOURCE 264798 88266 3
EVALUATION CONTEXT 67978 22659.3333 3
34 rows selected.
--//如果对比Select object_type,sum(object_id),avg(object_id),count(*) from t1 group by object_type;的输出可以发现输出顺
--//序差别，看末了一条纪录前面的是JAVA SOURCE.
--//看看执行筹划：
SCOTT@book01p> @ dpc '' 'projection' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 0n5vt0xa0qwk0, child number 1
-------------------------------------
Select object_type,sum(object_id),avg(object_id),count(distinct
object_name) from t1 group by object_type
Plan hash value: 3244420040
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 1296 (100)| | 34 |00:00:00.04 | 1479 | | | |
| 1 | HASH GROUP BY | | 1 | 34 | 3468 | | 1296 (1)| 00:00:01 | 34 |00:00:00.04 | 1479 | 837K| 837K| |
| 2 | VIEW | VW_DAG_0 | 1 | 69212 | 6894K| | 1296 (1)| 00:00:01 | 69212 |00:00:00.04 | 1479 | | | |
| 3 | HASH GROUP BY | | 1 | 69212 | 3447K| 4416K| 1296 (1)| 00:00:01 | 69212 |00:00:00.04 | 1479 | 15M| 2089K| 9M (0)|
| 4 | TABLE ACCESS FULL| T1 | 1 | 69908 | 3481K| | 412 (1)| 00:00:01 | 69908 |00:00:00.01 | 1479 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$C33C846D
2 - SEL$5771D262 / "VW_DAG_0"@"SEL$C33C846D"
3 - SEL$5771D262
4 - SEL$5771D262 / "T1"@"SEL$1"
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=1; rowset=256) "ITEM_2"[VARCHAR2,23], COUNT("ITEM_1")[22], SUM("ITEM_3")[22], SUM("ITEM_5")[22]
2 - (rowset=256) "ITEM_1"[VARCHAR2,128], "ITEM_2"[VARCHAR2,23], "ITEM_3"[NUMBER,22], "ITEM_5"[NUMBER,22]
3 - (#keys=2; rowset=256) "OBJECT_TYPE"[VARCHAR2,23], "OBJECT_NAME"[VARCHAR2,128], COUNT(*)[22], SUM("OBJECT_ID")[22]
4 - (rowset=256) "OBJECT_NAME"[VARCHAR2,128], "OBJECT_ID"[NUMBER,22], "OBJECT_TYPE"[VARCHAR2,23]
Note
-----
- statistics feedback used for this statement
37 rows selected.
--//oracle仍旧采用HASH GROUP BY算法，不过使用2次。
--//仔细看Column Projection Information (identified by operation id):就可以看出一些细节。
--//id=3 的输出字段信息是"OBJECT_TYPE"[VARCHAR2,23], "OBJECT_NAME"[VARCHAR2,128], COUNT(*)[22], SUM("OBJECT_ID")[22]。
--//也就是可以猜测这部门的group by实际上包罗2个字段OBJECT_TYPE，OBJECT_NAME。
--//然后在这个的底子上做了1次group by OBJECT_TYPE。也就是做了查询转换。
SCOTT@book01p> @ expand_sql_text.sql 0n5vt0xa0qwk0
SELECT "A1"."OBJECT_TYPE" "OBJECT_TYPE",
SUM("A1"."OBJECT_ID") "SUM(OBJECT_ID)",
AVG("A1"."OBJECT_ID") "AVG(OBJECT_ID)",
COUNT(DISTINCT "A1"."OBJECT_NAME") "COUNT(DISTINCTOBJECT_NAME)"
FROM "SCOTT"."T1" "A1"
GROUP BY "A1"."OBJECT_TYPE"
PL/SQL procedure successfully completed.
--//注做了格式化处理，oracle的expand sql text看不出细节，搞不懂有时候从输出可以看出执行细节有时候不行!!
--//再做一个10053看看。
SCOTT@book01p> @ 10053x 0n5vt0xa0qwk0 1
/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_3410_a0n5vt0xa0qwk0.trc
SCOTT@book01p> @ 10053y ''
TRCLINE
------------------------------------------------------------------------------------------------------------------------
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "VW_DAG_0"."ITEM_2" "OBJECT_TYPE",SUM("VW_DAG_0"."ITEM_3") "SUM(OBJECT_ID)",DECODE(NVL(SUM("VW_DAG_0"."ITEM_5"),0
),0,TO_NUMBER(NULL),SUM("VW_DAG_0"."ITEM_3")/NVL(SUM("VW_DAG_0"."ITEM_5"),0)) "AVG(OBJECT_ID)",COUNT("VW_DAG_0"."ITEM_1"
) "COUNT(DISTINCTOBJECT_NAME)" FROM (SELECT "T1"."OBJECT_NAME" "ITEM_1","T1"."OBJECT_TYPE" "ITEM_2",SUM("T1"."OBJECT_ID
") "ITEM_3",SUM("T1"."OBJECT_ID") "ITEM_4",COUNT(*) "ITEM_5" FROM "SCOTT"."T1" "T1" GROUP BY "T1"."OBJECT_TYPE","T1"."OB
JECT_NAME") "VW_DAG_0" GROUP BY "VW_DAG_0"."ITEM_2"
--//格式化后读起来更新清晰，oracle做了查询转换，可以很轻易的发现内层的group by "T1"."OBJECT_TYPE", "T1"."OBJECT_NAME"。
SELECT "VW_DAG_0"."ITEM_2" "OBJECT_TYPE"
,SUM ("VW_DAG_0"."ITEM_3") "SUM(OBJECT_ID)"
,DECODE (
NVL (SUM ("VW_DAG_0"."ITEM_5"), 0)
,0, TO_NUMBER (NULL)
,SUM ("VW_DAG_0"."ITEM_3") / NVL (SUM ("VW_DAG_0"."ITEM_5"), 0))
"AVG(OBJECT_ID)"
,COUNT ("VW_DAG_0"."ITEM_1") "COUNT(DISTINCTOBJECT_NAME)"
FROM ( SELECT "T1"."OBJECT_NAME" "ITEM_1"
,"T1"."OBJECT_TYPE" "ITEM_2"
,SUM ("T1"."OBJECT_ID") "ITEM_3"
,SUM ("T1"."OBJECT_ID") "ITEM_4"
,COUNT (*) "ITEM_5"
FROM "SCOTT"."T1" "T1"
GROUP BY "T1"."OBJECT_TYPE", "T1"."OBJECT_NAME") "VW_DAG_0"
GROUP BY "VW_DAG_0"."ITEM_2"
--//如果给如许的语句参加NO_USE_HASH_AGGREGATION提示呢?
SCOTT@book01p> Select /*+ NO_USE_HASH_AGGREGATION */ object_type,sum(object_id),avg(object_id),count(distinct object_name) from t1 group by object_type;
OBJECT_TYPE SUM(OBJECT_ID) AVG(OBJECT_ID) COUNT(DISTINCTOBJECT_NAME)
------------------------------ -------------- -------------- --------------------------
CLUSTER 3127 312.7 10
CONSUMER GROUP 378585 21032.5 18
DESTINATION 42133 21066.5 2
DIRECTORY 198343 18031.1818 11
EDITION 134 134 1
EVALUATION CONTEXT 67978 22659.3333 3
FUNCTION 23268633 57171.0885 407
INDEX 47963848 20110.6281 2381
INDEX PARTITION 29357424 79775.6087 225
INDEXTYPE 654696 59517.8182 11
JAVA CLASS 1701232733 44526.729 38207
JAVA DATA 1168076 68710.3529 17
JAVA RESOURCE 100771276 58965.0532 1708
JAVA SOURCE 264798 88266 3
JOB CLASS 63169 21056.3333 3
LIBRARY 3731825 52560.9155 71
OPERATOR 3005473 50091.2167 60
PACKAGE 30372636 37636.4758 807
PACKAGE BODY 20327004 57583.5807 353
PROCEDURE 3958952 43988.3556 90
RULE SET 92298 23074.5 4
SCHEDULE 85012 21253 4
SCHEDULER GROUP 84768 21192 4
SEQUENCE 5405583 51481.7429 105
SYNONYM 451440006 37346.1289 12077
TABLE 46586395 19435.292 2391
TABLE PARTITION 23209585 40224.5841 228
TABLE SUBPARTITION 512080 16002.5 1
TRIGGER 7458043 51791.9653 144
TYPE 77026974 34775.1576 2067
TYPE BODY 7464132 66054.2655 113
VIEW 114811847 15025.7619 7638
WINDOW 190681 21186.7778 9
XML SCHEMA 1753783 44968.7949 39
34 rows selected.
--//看输出就知道采用sort group by，按照OBJECT_TYPE排序。
SCOTT@book01p> @ dpc '' 'projection' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID f3u6zcjbfx37z, child number 0
-------------------------------------
Select /*+ NO_USE_HASH_AGGREGATION */
object_type,sum(object_id),avg(object_id),count(distinct object_name)
from t1 group by object_type
Plan hash value: 3946799371
------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | 415 (100)| | 34 |00:00:00.12 | 1479 | | | |
| 1 | SORT GROUP BY | | 1 | 34 | 1734 | 415 (2)| 00:00:01 | 34 |00:00:00.12 | 1479 | 7140K| 1416K| 6346K (0)|
| 2 | TABLE ACCESS FULL| T1 | 1 | 69908 | 3481K| 412 (1)| 00:00:01 | 69908 |00:00:00.01 | 1479 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / "T1"@"SEL$1"
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=1; rowset=59) "OBJECT_TYPE"[VARCHAR2,23], COUNT(DISTINCT NLSSORT("OBJECT_NAME",'nls_sort=''BINARY'''))[22],
COUNT("OBJECT_ID")[22], SUM("OBJECT_ID")[22]
2 - (rowset=59) "OBJECT_NAME"[VARCHAR2,128], "OBJECT_ID"[NUMBER,22], "OBJECT_TYPE"[VARCHAR2,23]
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
1 - SEL$1
- NO_USE_HASH_AGGREGATION
--//直接就是1个SORT GROUP BY就可以，没有做查询转换。
--//不过你可以看cost就可以发现oracle有时候在采用最终的执行筹划有点问题，采用SORT GROUP BY的cost成本才415，而2次hash group
--//by的cost成本1296。 oracle最终还是选择hash group by的执行方式，或许oracle默认采用hash group by算法的缘故。
--//或者以为采用hash group by算法最优的缘故。
--//你可以执行如下sql语句，可以发现输出69212行，与前面hash group by 的E-Rows估算一致。
Select object_type,object_name,sum(object_id),avg(object_id) from t1 group by object_type,object_name;
Select object_type,object_name,sum(object_id),avg(object_id) from t1 group by object_type,object_name order by 1,2;
--//换一句话讲在如许的方式上采用SORT GROUP BY实际上执行效率也许更好，以后应该注意出现2次hash group by的情况。
--//oracle在做2个字段的hash group by时没有注意成本就很高。
--//趁便测试参加order by的情况。
SCOTT@book01p> Select object_type,sum(object_id),avg(object_id),count(distinct object_name) from t1 group by object_type order by 1;
OBJECT_TYPE SUM(OBJECT_ID) AVG(OBJECT_ID) COUNT(DISTINCTOBJECT_NAME)
------------------------------ -------------- -------------- --------------------------
CLUSTER 3127 312.7 10
CONSUMER GROUP 378585 21032.5 18
DESTINATION 42133 21066.5 2
...
VIEW 114811847 15025.7619 7638
WINDOW 190681 21186.7778 9
XML SCHEMA 1753783 44968.7949 39
34 rows selected.
SCOTT@book01p> @ dpc '' 'projection' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 0rmrjurdhxrmy, child number 1
-------------------------------------
Select object_type,sum(object_id),avg(object_id),count(distinct
object_name) from t1 group by object_type order by 1
Plan hash value: 2434018851
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 1296 (100)| | 34 |00:00:00.05 | 1479 | | | |
| 1 | SORT GROUP BY | | 1 | 34 | 3468 | | 1296 (1)| 00:00:01 | 34 |00:00:00.05 | 1479 | 4096 | 4096 | 4096 (0)|
| 2 | VIEW | VW_DAG_0 | 1 | 69212 | 6894K| | 1296 (1)| 00:00:01 | 69212 |00:00:00.04 | 1479 | | | |
| 3 | HASH GROUP BY | | 1 | 69212 | 3447K| 4416K| 1296 (1)| 00:00:01 | 69212 |00:00:00.04 | 1479 | 15M| 2089K| 9756K (0)|
| 4 | TABLE ACCESS FULL| T1 | 1 | 69908 | 3481K| | 412 (1)| 00:00:01 | 69908 |00:00:00.01 | 1479 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$C33C846D
2 - SEL$5771D262 / "VW_DAG_0"@"SEL$C33C846D"
3 - SEL$5771D262
4 - SEL$5771D262 / "T1"@"SEL$1"
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=1; rowset=256) "ITEM_2"[VARCHAR2,23], COUNT("ITEM_1")[22], SUM("ITEM_3")[22], SUM("ITEM_5")[22]
2 - (rowset=256) "ITEM_1"[VARCHAR2,128], "ITEM_2"[VARCHAR2,23], "ITEM_3"[NUMBER,22], "ITEM_5"[NUMBER,22]
3 - (#keys=2; rowset=256) "OBJECT_TYPE"[VARCHAR2,23], "OBJECT_NAME"[VARCHAR2,128], COUNT(*)[22], SUM("OBJECT_ID")[22]
4 - (rowset=256) "OBJECT_NAME"[VARCHAR2,128], "OBJECT_ID"[NUMBER,22], "OBJECT_TYPE"[VARCHAR2,23]
Note
-----
- statistics feedback used for this statement
37 rows selected.
--//仍旧在内层采用HASH GROUP BY 算法。
5.附上执行脚本代码：
$ cat expand_sql_text.sql
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
$ cat 10053x.sql
set term off
execute dbms_sqldiag.dump_trace(p_sql_id=>'&1',p_child_number=>&2,p_component=>'Compiler',p_file_id=>'a'||'&&1');
set term on
set head off
@ t
set head on
define 1=&trc
$ cat 10053y.sql
-- Copyright 2023 lfree. All rights reserved.
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms and conditions.
------------------------------------------------------------------------------------------------------------
--
-- File name: 10053y.sql
-- Purpose: display Final query after transformations
--
-- Author: lfree
--
-- Usage:
-- @ 10053y
--
-------------------------------------------------------------------------------------------------------------
SET TERM OFF
COLUMN trc_file NEW_VALUE v_trc_file
--DEFINE trc_file = &1
SELECT NVL('&1','&TRC') trc_file FROM DUAL ;
SELECT SUBSTR ('&v_trc_file', INSTR ('&v_trc_file', '/', -1) + 1) trc_file FROM DUAL;
SET TERM ON
DEFINE trc_file = &v_trc_file
--DEFINE trc_file = &1
--COL trace_filename FOR A45
--COL adr_home FOR A45
--SELECT trace_filename, to_char(change_time, 'dd-mm-yyyy hh24:mi:ss') AS change_time, to_char(modify_time, 'dd-mm-yyyy hh24:mi:ss') AS modify_time, adr_home, con_id
--FROM gv$diag_trace_file
--WHERE lower(trace_filename) LIKE lower('%&v_trc_file%')
--ORDER BY modify_time;
column trcline format a120
SELECT trcline
FROM gv$diag_trace_file_contents
MATCH_RECOGNIZE
(
PARTITION BY trace_filename
ORDER BY line_number
MEASURES payload AS trcline
ALL ROWS PER MATCH
PATTERN (a | b nc * | c | f n)
DEFINE a AS (payload LIKE 'qksptfSQM_GetTxt(): Anonymous Block%')
,b AS (payload LIKE 'qksptfSQM_GetTxt(): Macro Text%')
,nc AS (payload NOT LIKE 'qksptfSQM_Template(): Template Text%')
,c AS (payload LIKE 'qksptfSQM_Template(): Template Text%')
,f AS (payload LIKE 'Final query after%')
)
x
WHERE trace_filename = '&v_trc_file';
$ cat t.sql
SELECT value tracefile FROM v$diag_info WHERE name = 'Default Trace File';
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。 |
