Hi Tom,I have met a SQL that with the latest statistics and the execution plan is in the wrong estimate-rows ,that's to say,it's a large difference between the estimate and the actual,the sql statement and execution is below:
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
why the table access full of T_IOT_CARD is estimate 1 rows?
and anywhere to tune the sql?
Thanks!
As you add predicates, the optimizer will generally assume that each predicate will *reduce* the number of rows in the sample set. For example, ask the optimizer how many people born in February it will think 1/12th. Ask how many of them are star sign Pisces, it will think 1/12th times 1/12th = 1/144th, or less than one percent, when in fact, most people born in Feb are Pisces.
You can see this with a simple demo
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>
Histograms can potentially help, as can extended statistics
https://blogs.oracle.com/optimizer/extended-statistics If all else fails, you can hint a copy of the SQL to get the plan you want, capture a SQL plan baseline to lock in that plan, and the apply that baseline to the real SQL.
There is a full walkthrough of that process here
https://blogs.oracle.com/optimizer/using-sql-plan-management-to-control-sql-execution-plans