[20160215]connect by.txt
https://jonathanlewis.wordpress.com/2016/02/15/connect-by-2/
--重复测试：
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 nologging
as
select
rownum id_p, 10 * rownum id
from
all_objects
where
rownum <= 50000
;
execute dbms_stats.gather_table_stats(user,'t1', method_opt=>'for all columns size 1')
alter system flush shared_pool;
set serveroutput off
alter session set statistics_level = all;
SCOTT@book> SELECT * FROM t1 CONNECT BY id = 20 * PRIOR id_p START WITH id_p = 1;
ID_P ID
---------- ----------
1 10
2 20
4 40
8 80
16 160
32 320
64 640
128 1280
256 2560
512 5120
1024 10240
2048 20480
4096 40960
8192 81920
16384 163840
32768 327680
16 rows selected.
Plan hash value: 2084792490
-------------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | 99 (100)| | 16 |00:00:00.31 | 1785 | | | |
| 1 | CONNECT BY WITH FILTERING| | 1 | | | | | 16 |00:00:00.31 | 1785 | 2048 | 2048 | 2048 (0)|
|* 2 | TABLE ACCESS FULL | T1 | 1 | 1 | 10 | 32 (0)| 00:00:01 | 1 |00:00:00.01 | 105 | | | |
|* 3 | HASH JOIN | | 16 | 1 | 23 | 65 (2)| 00:00:01 | 15 |00:00:00.31 | 1680 | 1969K| 1969K| 631K (0)|
| 4 | CONNECT BY PUMP | | 16 | | | | | 16 |00:00:00.01 | 0 | | | |
| 5 | TABLE ACCESS FULL | T1 | 16 | 50000 | 488K| 32 (0)| 00:00:01 | 800K|00:00:00.07 | 1680 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$4 / T1@SEL$4
3 - SEL$3
5 - SEL$3 / T1@SEL$3
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID_P"=1)
3 - access("ID"=20*"connect$_by$_pump$_002"."PRIOR id_p ")
--如果改写如下：
SELECT /*+ no_connect_by_filtering */ * FROM t1 CONNECT BY id = 20 * PRIOR id_p START WITH id_p = 1;
Plan hash value: 2748547338
------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 235 (100)| | 16 |00:00:00.20 | 105 |
|* 1 | CONNECT BY NO FILTERING WITH START-WITH| | 1 | | | | | 16 |00:00:00.20 | 105 |
| 2 | TABLE ACCESS FULL | T1 | 1 | 50000 | 488K| 32 (0)| 00:00:01 | 50000 |00:00:00.01 | 105 |
------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$2 / T1@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID_P"=1)
--可以发现如果访问是顺着链子走下去,主要id_p唯一。逻辑读很小105.而前面使用connect_by_filtering，逻辑读starts*T1的全表扫描 16*105+105=1785.
select sum(ct)
from (
select
/*+ no_connect_by_filtering */
count(id) ct
from
t1
connect by
id = 20 * prior id_p
start with
id_p = 1
group by
id
)
;
SUM(CT)
----------
16
select * from table(dbms_xplan.display_cursor(null,null,'allstats last cost'));
Plan hash value: 1649295669
-----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 33 (100)| 1 |00:00:00.19 | 105 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:00.19 | 105 | | | |
| 2 | VIEW | | 1 | 2 | 33 (4)| 16 |00:00:00.19 | 105 | | | |
| 3 | HASH GROUP BY | | 1 | 2 | 33 (4)| 16 |00:00:00.19 | 105 | 1519K| 1519K| 1237K (0)|
|* 4 | CONNECT BY NO FILTERING WITH START-WITH| | 1 | | | 16 |00:00:00.19 | 105 | | | |
| 5 | TABLE ACCESS FULL | T1 | 1 | 50000 | 32 (0)| 50000 |00:00:00.01 | 105 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("ID_P"=1)
--时间很短。00:00:00.19
SCOTT@book> update t1 set id_p = 0 where id_p = 1 ;
1 row updated.
SCOTT@book> update t1 set id_p = 1 where id_p > 45000 ;
5000 rows updated.
SCOTT@book> commit ;
Commit complete.
--这样相当于有5000条ID_P=1的记录。
SELECT * FROM t1 CONNECT BY id = 20 * PRIOR id_p START WITH id_p = 1;
....
80000 rows selected.
--相当于5000*16=80000行。
Plan hash value: 2084792490
-------------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | 99 (100)| | 80000 |00:00:00.66 | 1785 | | | |
| 1 | CONNECT BY WITH FILTERING| | 1 | | | | | 80000 |00:00:00.66 | 1785 | 337K| 337K| 299K (0)|
|* 2 | TABLE ACCESS FULL | T1 | 1 | 1 | 10 | 32 (0)| 00:00:01 | 5000 |00:00:00.01 | 105 | | | |
|* 3 | HASH JOIN | | 16 | 1 | 23 | 65 (2)| 00:00:01 | 15 |00:00:00.28 | 1680 | 1969K| 1969K| 698K (0)|
| 4 | CONNECT BY PUMP | | 16 | | | | | 16 |00:00:00.01 | 0 | | | |
| 5 | TABLE ACCESS FULL | T1 | 16 | 50000 | 488K| 32 (0)| 00:00:01 | 800K|00:00:00.07 | 1680 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$4 / T1@SEL$4
3 - SEL$3
5 - SEL$3 / T1@SEL$3
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID_P"=1)
3 - access("ID"=20*"connect$_by$_pump$_002"."PRIOR id_p ")
--可以发现这个时候选择CONNECT BY WITH FILTERING，执行计划是稳定的1785逻辑读。时间相对固定。
--如果改写如下：
SELECT /*+ no_connect_by_filtering */ * FROM t1 CONNECT BY id = 20 * PRIOR id_p START WITH id_p = 1;
......
Plan hash value: 2748547338
------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 235 (100)| | 80000 |00:05:55.08 | 105 |
|* 1 | CONNECT BY NO FILTERING WITH START-WITH| | 1 | | | | | 80000 |00:05:55.08 | 105 |
| 2 | TABLE ACCESS FULL | T1 | 1 | 50000 | 488K| 32 (0)| 00:00:01 | 50000 |00:00:00.01 | 105 |
------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$2 / T1@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID_P"=1)
--可以发现逻辑读仅仅105，但是ID1=1 CONNECT BY NO FILTERING WITH START-WITH,使用了00:05:55.08。
--实际上查询到5000条id_p=1的记录，循环了5000次，时间被放大了5000倍。
select sum(ct)
from (
select
/*+ no_connect_by_filtering */
count(id) ct
from
t1
connect by
id = 20 * prior id_p
start with
id_p = 1
group by
id
)
;
SUM(CT)
----------
80000
Plan hash value: 1649295669
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | 33 (100)| | 1 |00:06:12.50 | 105 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 13 | | | 1 |00:06:12.50 | 105 | | | |
| 2 | VIEW | | 1 | 2 | 26 | 33 (4)| 00:00:01 | 5015 |00:06:12.50 | 105 | | | |
| 3 | HASH GROUP BY | | 1 | 2 | 36 | 33 (4)| 00:00:01 | 5015 |00:06:12.50 | 105 | 5312K| 2025K| 1362K (0)|
|* 4 | CONNECT BY NO FILTERING WITH START-WITH| | 1 | | | | | 80000 |00:06:12.47 | 105 | | | |
| 5 | TABLE ACCESS FULL | T1 | 1 | 50000 | 488K| 32 (0)| 00:00:01 | 50000 |00:00:00.01 | 105 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$2 / from$_subquery$_001@SEL$1
3 - SEL$2
5 - SEL$3 / T1@SEL$3
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("ID_P"=1)
--使用了00:06:12.50,虽然逻辑读很小105.
--使用connect_by_filtering看看：
select sum(ct)
from (
select
/*+ connect_by_filtering */
count(id) ct
from
t1
connect by
id = 20 * prior id_p
start with
id_p = 1
group by
id
)
;
SUM(CT)
----------
80000
Plan hash value: 2838373257
----------------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | 98 (100)| | 1 |00:00:00.49 | 1785 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 13 | | | 1 |00:00:00.49 | 1785 | | | |
| 2 | VIEW | | 1 | 2 | 26 | 98 (3)| 00:00:02 | 5015 |00:00:00.49 | 1785 | | | |
| 3 | HASH GROUP BY | | 1 | 2 | 36 | 98 (3)| 00:00:02 | 5015 |00:00:00.49 | 1785 | 5312K| 2025K| 1362K (0)|
| 4 | CONNECT BY WITH FILTERING| | 1 | | | | | 80000 |00:00:00.47 | 1785 | 337K| 337K| 299K (0)|
|* 5 | TABLE ACCESS FULL | T1 | 1 | 1 | 10 | 32 (0)| 00:00:01 | 5000 |00:00:00.01 | 105 | | | |
|* 6 | HASH JOIN | | 16 | 1 | 23 | 65 (2)| 00:00:01 | 15 |00:00:00.28 | 1680 | 1969K| 1969K| 693K (0)|
| 7 | CONNECT BY PUMP | | 16 | | | | | 16 |00:00:00.01 | 0 | | | |
| 8 | TABLE ACCESS FULL | T1 | 16 | 50000 | 488K| 32 (0)| 00:00:01 | 800K|00:00:00.07 | 1680 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$2 / from$_subquery$_001@SEL$1
3 - SEL$2
5 - SEL$5 / T1@SEL$5
6 - SEL$4
8 - SEL$4 / T1@SEL$4
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("ID_P"=1)
6 - access("ID"=20*"connect$_by$_pump$_003"."prior id_p ")
--很快，逻辑读1785。
--附带存在问题时perf top观察到的情况：
# perf top -k /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
PerfTop: 1175 irqs/sec kernel:12.1% exact: 0.0% [1000Hz cycles], (all, 24 CPUs)
-------------------------------------------------------------------------------------------
samples pcnt function DSO
_______ _____ ______________________________ ____________________________________________________
2894.00 19.4% rworupo /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
2841.00 19.1% smbget /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
2637.00 17.7% lnxmul /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
1671.00 11.2% evamul /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
1312.00 8.8% sorgetqb /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
1019.00 6.8% evareo /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
633.00 4.3% evaopn3 /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
515.00 3.5% qercbiFetch /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
490.00 3.3% expeal /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
394.00 2.6% lmebco /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
279.00 1.9% qercbiUnpackFromSort /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
56.00 0.4% __intel_new_memset /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
17.00 0.1% kewa_sample_session /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
17.00 0.1% kstmhighrestimecntkeeper /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
14.00 0.1% kgghash2 /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
12.00 0.1% ksugsta /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
12.00 0.1% kewa_sampler /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
8.00 0.1% kgsmf_cread_invalidate /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
6.00 0.0% ksdhng_node_init_full /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
6.00 0.0% memcpy /usr/bin/2.6.39-300.26.1.el5uek/perf
5.00 0.0% kghrcdepth /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
5.00 0.0% kss_get_type /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
5.00 0.0% ksdhng_cache_blkrs_update_node /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
5.00 0.0% gettimeofday /lib64/libc-2.5.so
SELECT /*+ no_connect_by_filtering */ * FROM t1 CONNECT BY id = 20 * PRIOR id_p START WITH id_p = 1;
PerfTop: 1157 irqs/sec kernel:13.1% exact: 0.0% [1000Hz cycles], (all, 24 CPUs)
---------------------------------------------------------------------------------------------------------------------
samples pcnt function DSO
_______ _____ ______________________________ ___________________________________________________________
2614.00 22.0% lnxmul /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
2307.00 19.4% rworupo /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
1443.00 12.2% evamul /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
1426.00 12.0% smbget /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
959.00 8.1% sorgetqb /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
844.00 7.1% evareo /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
613.00 5.2% qercbiFetch /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
509.00 4.3% expeal /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
470.00 4.0% evaopn3 /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
322.00 2.7% lmebco /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
213.00 1.8% qercbiUnpackFromSort /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
35.00 0.3% __intel_new_memset /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
15.00 0.1% kewa_sampler /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
12.00 0.1% memcpy /usr/bin/2.6.39-300.26.1.el5uek/perf
10.00 0.1% kstmhighrestimecntkeeper /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
9.00 0.1% kgghash2 /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
8.00 0.1% ksugsta /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
8.00 0.1% kewa_sample_session /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
7.00 0.1% ksdhng_cache_blkrs_update_node /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
5.00 0.0% afifmt /u01/app/oracle/product/11.2.0.4/dbhome_1/lib/libsqlplus.so
5.00 0.0% kgsmf_cread_invalidate /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-1989588/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/267265/viewspace-1989588/
扫一扫
3584
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
