SQL>set autotrace traceonly
SQL> Select distinct Id, Role_Id, Login
2 From tb_test a,(Select Client_Id
3 From Tb_test_Bet_Log y,
4 (Select Distinct a.test_Deal_Log_Id
5 From Tb_test_Bet_Log a,
6 (Select Id
7 From tb_test
8 Where Exists (Select Id
9 From (Select Id
10 From Tb_Admin_Role
11 Connect By Prior Id = Parent_Id
12 Start With Id = 1) x
13 Where Role_Id = x.Id)
14 And Lower(Login) = 'rtest5') b
15 Where a.Client_Id=b.id) b
16 Where y.test_Deal_Log_Id=b.test_Deal_Log_Id
17 ) b
18 Where a.Id=b.client_id
19 ;
189 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=2492 Card=4247 Bytes
=237832)
1 0 SORT (UNIQUE) (Cost=2492 Card=4247 Bytes=237832)
2 1 HASH JOIN (Cost=2450 Card=4247 Bytes=237832)
3 2 TABLE ACCESS (FULL) OF 'tb_test' (Cost=6 Card=3667 B
ytes=62339)
4 2 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'TB_test_BET_L
OG' (Cost=10 Card=36 Bytes=324)
5 4 NESTED LOOPS (Cost=2442 Card=4247 Bytes=165633)
6 5 HASH JOIN (Cost=1262 Card=118 Bytes=3540)
7 6 HASH JOIN (Cost=9 Card=1 Bytes=21)
8 7 VIEW (Cost=2 Card=20 Bytes=80)
9 8 CONNECT BY (WITH FILTERING)
10 9 NESTED LOOPS
11 10 INDEX (UNIQUE SCAN) OF 'PK_TB_ADMIN_ROLE
' (UNIQUE) (Cost=1 Card=1 Bytes=4)
12 10 TABLE ACCESS (BY USER ROWID) OF 'TB_ADMI
N_ROLE'
13 9 NESTED LOOPS
14 13 BUFFER (SORT)
15 14 CONNECT BY PUMP
16 13 INDEX (RANGE SCAN) OF 'INX_ADMIN_ROLE_P'
(NON-UNIQUE) (Cost=2 Card=20 Bytes=160)
17 7 TABLE ACCESS (FULL) OF 'tb_test' (Cost=6 Car
d=37 Bytes=629)
18 6 PARTITION RANGE (ALL)
19 18 TABLE ACCESS (FULL) OF 'TB_test_BET_LOG' (Cost
=1246 Card=2316228 Bytes=20846052)
20 5 INDEX (RANGE SCAN) OF 'IDX_test_DEAL_LOG1' (NON-UN
IQUE) (Cost=2 Card=36)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
39295 consistent gets
33639 physical reads
0 redo size
4667 bytes sent via SQL*Net to client
628 bytes received via SQL*Net from client
14 SQL*Net roundtrips to/from client
16 sorts (memory)
0 sorts (disk)
189 rows processed
通过红色部分可以看出逻辑读和物理读比较大,并且还有两个表全表扫描,
tb_test这个表是小表,全表扫描,但tb_test_bet_log是大表几千万数据,全表
扫描那就不合适了,这个表也没加上任何限制条件,根据业务相结合,可以加上时间限制条件
这个表是分区表,而且是按时间分区索引的,所以加上时间限制条件后
看下面优化后的语句
SQL> Select distinct Id, Role_Id, Login
2 From tb_test a,(Select Client_Id
3 From Tb_test_Bet_Log y,
4 (Select Distinct a.test_Deal_Log_Id,created_date
5 From Tb_test_Bet_Log a, (Select Id
6 From tb_test
7 Where Exists (Select Id
8 From (Select Id
9 From Tb_Admin_Role
10 Connect By Prior Id = Parent_Id
11 Start With Id = 1) x
12 Where Role_Id = x.Id)
13 And Lower(Login) = 'rtest5') b
14 Where a.Client_Id=b.id
15 and a.created_date between
16 to_date('2007-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
17 and to_date('2007-01-03 23:59:59','yyyy-mm-dd hh24:mi:ss'))
b
18 Where y.test_Deal_Log_Id=b.test_Deal_Log_Id
19 ) b
20 Where a.Id=b.client_id;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=36 Card=6 Bytes=384)
1 0 SORT (UNIQUE) (Cost=36 Card=6 Bytes=384)
2 1 NESTED LOOPS (Cost=34 Card=6 Bytes=384)
3 2 NESTED LOOPS (Cost=28 Card=6 Bytes=282)
4 3 NESTED LOOPS (Cost=18 Card=1 Bytes=38)
5 4 HASH JOIN (Cost=9 Card=1 Bytes=21)
6 5 VIEW (Cost=2 Card=20 Bytes=80)
7 6 FILTER
8 7 CONNECT BY (WITH FILTERING)
9 8 NESTED LOOPS
10 9 INDEX (UNIQUE SCAN) OF 'PK_TB_ADMIN_ROLE
' (UNIQUE) (Cost=1 Card=1 Bytes=4)
11 9 TABLE ACCESS (BY USER ROWID) OF 'TB_ADMI
N_ROLE'
12 8 NESTED LOOPS
13 12 BUFFER (SORT)
14 13 CONNECT BY PUMP
15 12 INDEX (RANGE SCAN) OF 'INX_ADMIN_ROLE_P'
(NON-UNIQUE) (Cost=2 Card=20 Bytes=160)
16 5 TABLE ACCESS (FULL) OF 'tb_test' (Cost=6 Card=
37 Bytes=629)
17 4 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'TB_test_BE
T_LOG' (Cost=9 Card=2 Bytes=34)
18 17 INDEX (RANGE SCAN) OF 'IDX_test_BET_LOG_CREATED_
DATE' (NON-UNIQUE) (Cost=1 Card=82)
19 3 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'TB_test_BET
_LOG' (Cost=10 Card=36 Bytes=324)
20 19 INDEX (RANGE SCAN) OF 'IDX_test_DEAL_LOG1' (NON-UN
IQUE) (Cost=2 Card=36)
21 2 TABLE ACCESS (BY INDEX ROWID) OF 'tb_test' (Cost=1 C
ard=1 Bytes=17)
22 21 INDEX (UNIQUE SCAN) OF 'PK_tb_test' (UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1025 consistent gets
0 physical reads
0 redo size
407 bytes sent via SQL*Net to client
496 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
16 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
用上索引,逻辑读和物理读都降下来了
所以在优化过程中,有效的降低逻辑读和物理读那是最直接的方法而且要和业务相结合