问题描述
嗨，汤姆，我遇到了一个SQL，它与最新的统计数据和执行计划是在错误的估计-行，也就是说，它是估计和实际之间的很大的差异，sql语句和执行如下:
为什么表访问充满T_IOT_CARD估计1行？
以及任何可以调整sql的地方？
谢谢!
select t1.card_no,
t.card_active_date,
t1.package_deadline,
s.service_name,
t1.channel_id,
t1.interface_id,
t1.id,
t1.sms_package_id
from (select iot_card_id, min(card_sync_date) card_active_date
from (select cmt.*
from (select *
from t_iot_card_monitor_trancation
where iot_card_id in
(select id
from t_iot_card
where card_active_date = create_date
and (card_state = '1' or
(sysdate > Card_Test_Closing_Date and
sysdate < card_silence_closing_date))
and (flow_share = '00' or flow_share is null))
and package_used > 0) cmt
left join t_iot_card ic
on ic.id = cmt.iot_card_id
where to_char(cmt.card_sync_date, 'yyyy-mm') >
to_char(ic.card_test_closing_date, 'yyyy-mm')
or ic.card_test_closing_date is null)
group by iot_card_id) t
left join t_iot_card t1
on t1.id = t.iot_card_id
left join t_iot_interface_new t3
on t3.id = t1.interface_id
left join t_iot_interface_server s
on t3.server_id = s.id
where s.service_name != 'ydszdh';
execution plan:
----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:01.51 | 957K| | | |
| 1 | HASH GROUP BY | | 1 | 5 | 0 |00:00:01.51 | 957K| 757K| 757K| |
|* 2 | FILTER | | 1 | | 0 |00:00:01.51 | 957K| | | |
| 3 | NESTED LOOPS OUTER | | 1 | 5 | 1860 |00:00:01.51 | 957K| | | |
|* 4 | HASH JOIN | | 1 | 5 | 1860 |00:00:01.51 | 955K| 1185K| 1185K| 1017K (0)|
|* 5 | TABLE ACCESS FULL | T_IOT_INTERFACE_SERVER | 1 | 1 | 10 |00:00:00.01 | 6 | | | |
|* 6 | HASH JOIN | | 1 | 101 | 5030 |00:00:01.50 | 955K| 1263K| 1263K| 1255K (0)|
| 7 | TABLE ACCESS FULL | T_IOT_INTERFACE_NEW | 1 | 125 | 125 |00:00:00.01 | 5 | | | |
| 8 | NESTED LOOPS | | 1 | 75 | 5030 |00:00:01.49 | 955K| | | |
| 9 | NESTED LOOPS | | 1 | 76 | 5030 |00:00:01.48 | 950K| | | |
| 10 | NESTED LOOPS | | 1 | 76 | 5030 |00:00:01.47 | 949K| | | |
|* 11 | TABLE ACCESS FULL | T_IOT_CARD | 1 | 1 | 15665 |00:00:00.20 | 14166 | | | |
|* 12 | TABLE ACCESS BY INDEX ROWID| T_IOT_CARD_MONITOR_TRANCATION | 15665 | 163 | 5030 |00:00:01.26 | 935K| | | |
|* 13 | INDEX RANGE SCAN | IOT_CARD_MONITOR_TRAN_CARD_ID | 15665 | 165 | 909K|00:00:00.23 | 25712 | | | |
|* 14 | INDEX UNIQUE SCAN | T_IOT_CARD | 5030 | 1 | 5030 |00:00:00.01 | 552 | | | |
| 15 | TABLE ACCESS BY INDEX ROWID | T_IOT_CARD | 5030 | 1 | 5030 |00:00:00.01 | 5250 | | | |
| 16 | TABLE ACCESS BY INDEX ROWID | T_IOT_CARD | 1860 | 1 | 1860 |00:00:00.01 | 2346 | | | |
|* 17 | INDEX UNIQUE SCAN | T_IOT_CARD | 1860 | 1 | 1860 |00:00:00.01 | 370 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter((TO_CHAR(INTERNAL_FUNCTION("CARD_SYNC_DATE"),'yyyy-mm')>TO_CHAR(INTERNAL_FUNCTION("IC"."CARD_TEST_CLOSING_DATE"),'yyyy-mm')
OR "IC"."CARD_TEST_CLOSING_DATE" IS NULL))
4 - access("T3"."SERVER_ID"="S"."ID")
5 - filter("S"."SERVICE_NAME"<>'ydszdh')
6 - access("T3"."ID"="T1"."INTERFACE_ID")
11 - filter(("CARD_ACTIVE_DATE"="CREATE_DATE" AND ("FLOW_SHARE"='00' OR "FLOW_SHARE" IS NULL) AND ("CARD_STATE"='1' OR
("CARD_SILENCE_CLOSING_DATE">SYSDATE@! AND "CARD_TEST_CLOSING_DATE"0)
13 - access("IOT_CARD_ID"="ID")
14 - access("T1"."ID"="T_IOT_CARD_MONITOR_TRANCATION"."IOT_CARD_ID")
17 - access("IC"."ID"="T_IOT_CARD_MONITOR_TRANCATION"."IOT_CARD_ID")
table statistics:
Table Name Num Rows Sample Perc Last Analyzed Blocks Stale
Size Stats
T_IOT_CARD 387382 387382 100 2018-09-03/16:56:54 14177 NO
T_IOT_CARD_MONITOR_TRANCATION 65201550 6520155 10 2018-09-03/05:32:57 659854 NO
T_IOT_INTERFACE_NEW 125 125 100 2018-09-03/05:34:26 7 NO
T_IOT_INTERFACE_SERVER 11 11 100 2018-09-03/05:34:26 5 NO
为什么表访问充满T_IOT_CARD估计1行？
以及任何可以调整sql的地方？
谢谢!
专家解答
当您添加谓词时，优化器通常会假设每个谓词将 * 减少 * 样本集中的行数。例如，询问优化器有多少人在2月出生，它会认为1/12。问其中有多少是双鱼座星座，它会认为1/12乘以1/12 = 1/144，或者小于1%，而事实上，大多数出生在2月的人都是双鱼座。
你可以通过一个简单的演示来看到这个
直方图可能会有所帮助，扩展统计也可能会有所帮助
https://blogs.oracle.com/optimizer/extended-statistics
如果所有其他方法均失败，则可以提示SQL的副本以获取所需的计划，捕获SQL计划基线以锁定该计划，然后将该基线应用于实际SQL。
这里有一个完整的过程
https://blogs.oracle.com/optimizer/using-sql-plan-management-to-control-sql-execution-plans
你可以通过一个简单的演示来看到这个
SQL> create table t
2 as select * from dba_objects;
Table created.
SQL>
SQL> exec dbms_stats.gather_table_stats('','T');
PL/SQL procedure successfully completed.
SQL>
SQL> select /*+ gather_plan_statistics */ count(*) from t
2 where owner = 'SYS';
COUNT(*)
----------
51766
1 row selected.
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 0dc3bp8v3ddbu, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from t where owner = 'SYS'
Plan hash value: 2966233522
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 1498 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 1498 |
|* 2 | TABLE ACCESS FULL| T | 1 | 2402 | 51766 |00:00:00.01 | 1498 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OWNER"='SYS')
19 rows selected.
SQL>
SQL> select /*+ gather_plan_statistics */ count(*) from t
2 where owner = 'SYS'
3 and object_type = 'JAVA CLASS';
COUNT(*)
----------
34642
1 row selected.
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 418f63yfz4bv1, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from t where owner =
'SYS' and object_type = 'JAVA CLASS'
Plan hash value: 2966233522
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 1498 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 1498 |
|* 2 | TABLE ACCESS FULL| T | 1 | 48 | 34642 |00:00:00.01 | 1498 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("OBJECT_TYPE"='JAVA CLASS' AND "OWNER"='SYS'))
20 rows selected.
SQL>
SQL> select /*+ gather_plan_statistics */ count(*) from t
2 where owner = 'SYS'
3 and object_type = 'JAVA CLASS'
4 and status = 'VALID';
COUNT(*)
----------
34642
1 row selected.
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 77maurtkg4sfj, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from t where owner =
'SYS' and object_type = 'JAVA CLASS' and status = 'VALID'
Plan hash value: 2966233522
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 1498 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 1498 |
|* 2 | TABLE ACCESS FULL| T | 1 | 24 | 34642 |00:00:00.01 | 1498 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("OBJECT_TYPE"='JAVA CLASS' AND "OWNER"='SYS' AND
"STATUS"='VALID'))
21 rows selected.
SQL>
SQL> select /*+ gather_plan_statistics */ count(*) from t
2 where owner = 'SYS'
3 and object_type = 'JAVA CLASS'
4 and status = 'VALID'
5 and temporary = 'N';
COUNT(*)
----------
34642
1 row selected.
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID cnqr8f247z25z, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from t where owner =
'SYS' and object_type = 'JAVA CLASS' and status = 'VALID' and
temporary = 'N'
Plan hash value: 2966233522
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 1498 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 1498 |
|* 2 | TABLE ACCESS FULL| T | 1 | 12 | 34642 |00:00:00.01 | 1498 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("OBJECT_TYPE"='JAVA CLASS' AND "OWNER"='SYS' AND
"STATUS"='VALID' AND "TEMPORARY"='N'))
22 rows selected.
SQL>
SQL> select /*+ gather_plan_statistics */ count(*) from t
2 where owner = 'SYS'
3 and object_type = 'JAVA CLASS'
4 and status = 'VALID'
5 and temporary = 'N'
6 and generated = 'N';
COUNT(*)
----------
34642
1 row selected.
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 5nzjuw2z4w9ya, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from t where owner =
'SYS' and object_type = 'JAVA CLASS' and status = 'VALID' and
temporary = 'N' and generated = 'N'
Plan hash value: 2966233522
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 1498 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 1498 |
|* 2 | TABLE ACCESS FULL| T | 1 | 6 | 34642 |00:00:00.01 | 1498 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("OBJECT_TYPE"='JAVA CLASS' AND "OWNER"='SYS' AND
"STATUS"='VALID' AND "TEMPORARY"='N' AND "GENERATED"='N'))
22 rows selected.
SQL>
SQL>
直方图可能会有所帮助，扩展统计也可能会有所帮助
https://blogs.oracle.com/optimizer/extended-statistics
如果所有其他方法均失败，则可以提示SQL的副本以获取所需的计划，捕获SQL计划基线以锁定该计划，然后将该基线应用于实际SQL。
这里有一个完整的过程
https://blogs.oracle.com/optimizer/using-sql-plan-management-to-control-sql-execution-plans
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
