Our customer has a problem today.
The very simple queries take very different time :
select min(a.ENTRYDATE) from ORDERS_BASE a where a.ENTRYDATE < to_date('01.01.2015','dd.mm.yyyy');
select min(a.ENTRYDATE) from ORDERS_BASE a where a.ENTRYDATE < trunc(sysdate-1112);
select min(a.ENTRYDATE) from ORDERS_BASE a where a.ENTRYDATE < date '2015-01-01';
First query takes 850 second.
2nd and 3rd queries take .04 second.
Find the difference !
:(
The queries were tested at 17-JAN-2018 so trunc(sysdate-1112) == to_date('01.01.2015','dd.mm.yyyy') that day.
For clarity and for checking the difference in data types i did dump of these values:
SQL> select dump(trunc(sysdate-1112)) from dual;
DUMP(TRUNC(SYSDATE-1112))
--------------------------------------------------------------------------------
Typ=13 Len=8: 223,7,1,1,0,0,0,0
SQL> select dump(to_date('01.01.2015','dd.mm.yyyy')) from dual;
DUMP(TO_DATE('01.01.2015','DD.M
-------------------------------
Typ=13 Len=8: 223,7,1,1,0,0,0,0
SQL> select dump(date '2015-01-01') from dual;
DUMP(DATE'2015-01-01')
-------------------------------
Typ=13 Len=8: 223,7,1,1,0,0,0,0
INVESTIGATION:
Step 0: Gather facts
DB version is 12.1.0.2.170418, Oracle on Linux x64.
Table has 65 columns:
SQL> desc ORDERS_BASE
Name Null? Type
----------------------------------------- -------- ------
...
ENTRYDATE DATE
...
CREATE TABLE "ORDERS_BASE"
( ...
"ENTRYDATE" DATE,
...
)
PARTITION BY RANGE ("ENTRYDATE")
(PARTITION "CURR_ORDERS_BASE_P_20050804" VALUES LESS THAN (TO_DATE(' 2005-08-05 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED ,
PARTITION "CURR_ORDERS_BASE_P_20050805" VALUES LESS THAN (TO_DATE(' 2005-08-06 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED ,
...
PARTITION "CURR_ORDERS_BASE_P_20200310" VALUES LESS THAN (TO_DATE(' 2020-03-11 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED ,
PARTITION "CURR_ORDERS_BASE_P_20200409" VALUES LESS THAN (TO_DATE(' 2020-04-10 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED
) ;Table has about 5000 partitions.
Table has 2 indexes:
CREATE UNIQUE INDEX "ORDERS_CURR_UIDX" ON "ORDERS_BASE" ("ENTRYDATE", "ORDERNO") LOCAL;
All 2014 year is in unusable state :
ALTER INDEX "ORDERS_CURR_UIDX" MODIFY PARTITION "CURR_ORDERS_BASE_P_20140101" UNUSABLE;
...
ALTER INDEX "ORDERS_CURR_UIDX" MODIFY PARTITION "CURR_ORDERS_BASE_P_20141231" UNUSABLE;
CREATE INDEX "ORDER_CURR_ORDNO_IDX" ON "ORDERS_BASE" ("ORDERNO") LOCAL;
All 2014 year is in unusable state :
ALTER INDEX "ORDER_CURR_ORDNO_IDX" MODIFY PARTITION "CURR_ORDERS_BASE_P_20140101" UNUSABLE;
...
ALTER INDEX "ORDER_CURR_ORDNO_IDX" MODIFY PARTITION "CURR_ORDERS_BASE_P_20141231" UNUSABLE;
Step 1: Lets measure
SQL> select min(a.ENTRYDATE) from ORDERS_BASE a where a.ENTRYDATE < trunc(sysdate-1112);
MIN(A.ENTRYDATE)
-------------------
10.01.2006 00:00:00
Elapsed: 00:00:00.04
SQL> select min(a.ENTRYDATE) from ORDERS_BASE a where a.ENTRYDATE < date '2015-01-01';
MIN(A.ENT
---------
10-JAN-06
Elapsed: 00:00:00.04
SQL> select min(a.ENTRYDATE) from ORDERS_BASE a where a.ENTRYDATE < to_date('01.01.2015','dd.mm.yyyy');
MIN(A.ENTRYDATE)
-------------------
10.01.2006 00:00:00
Elapsed: 00:14:10.64
Step 2: Lets look at the execution plan
The good plan at first:
SQL> set autotrace on
SQL> set lines 999 pages 99
SQL> select min(a.ENTRYDATE) from ORDERS_BASE a where a.ENTRYDATE < trunc(sysdate-1112);
MIN(A.ENTRYDATE)
-------------------
10.01.2006 00:00:00
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 2553418393
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 1404K (65)| 00:00:36 | | |
| 1 | PARTITION RANGE ITERATOR MIN/MAX| | 1 | 8 | | | 1 | KEY |
| 2 | SORT AGGREGATE | | 1 | 8 | | | | |
|* 3 | TABLE ACCESS STORAGE FULL | ORDERS_BASE | 9195M| 68G| 1404K (65)| 00:00:36 | 1 | KEY |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - storage("A"."ENTRYDATE"<TRUNC(SYSDATE@!-1112))
filter("A"."ENTRYDATE"<TRUNC(SYSDATE@!-1112))
Statistics
----------------------------------------------------------
0 recursive calls
2 db block gets
19 consistent gets
13 physical reads
0 redo size
555 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Here i added the dummy hint to force hard parsing and generate the new plan:
SQL> select /*+ U */ min(a.ENTRYDATE) from ORDERS_BASE a where a.ENTRYDATE < trunc(sysdate-1112);
MIN(A.ENTRYDATE)
-------------------
10.01.2006 00:00:00
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 2553418393
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 1404K (65)| 00:00:36 | | |
| 1 | PARTITION RANGE ITERATOR MIN/MAX| | 1 | 8 | | | 1 | KEY |
| 2 | SORT AGGREGATE | | 1 | 8 | | | | |
|* 3 | TABLE ACCESS STORAGE FULL | ORDERS_BASE | 9195M| 68G| 1404K (65)| 00:00:36 | 1 | KEY |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - storage("A"."ENTRYDATE"<TRUNC(SYSDATE@!-1112))
filter("A"."ENTRYDATE"<TRUNC(SYSDATE@!-1112))
Statistics
----------------------------------------------------------
1 recursive calls
2 db block gets
19 consistent gets
13 physical reads
0 redo size
555 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
After hard parsing the new plan was generated and we see the no difference.
Oracle strongly keep the best plan.
Let probe the bad query:
SQL> select min(a.ENTRYDATE) from ORDERS_BASE a where a.ENTRYDATE < to_date('01.01.2015','dd.mm.yyyy');
MIN(A.ENTRYDATE)
-------------------
10.01.2006 00:00:00
Elapsed: 00:14:10.64
Execution Plan
----------------------------------------------------------
Plan hash value: 2334607833
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 285K (31)| 00:00:08 | | |
| 1 | SORT AGGREGATE | | 1 | 8 | | | | |
| 2 | VIEW | VW_TE_2 | 9195M| 77G| 285K (31)| 00:00:08 | | |
| 3 | UNION-ALL | | | | | | | |
| 4 | PARTITION RANGE ITERATOR | | 6615M| 49G| 195K (30)| 00:00:05 | 1 | 3072 |
| 5 | TABLE ACCESS STORAGE FULL| ORDERS_BASE | 6615M| 49G| 195K (30)| 00:00:05 | 1 | 3072 |
| 6 | PARTITION RANGE ITERATOR | | 2579M| 19G| 89337 (32)| 00:00:03 | 3073 | 3437 |
| 7 | TABLE ACCESS STORAGE FULL| ORDERS_BASE | 2579M| 19G| 89337 (32)| 00:00:03 | 3073 | 3437 |
-------------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
657 recursive calls
3206 db block gets
17682261 consistent gets
17666575 physical reads
120 redo size
555 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
We see absolutely different plan:
- ONE full table scan has become TWO table scans
- and the view VW_TE_2 appeared
Let find the information about this view. I suppose the chars T and E may be in russian codepage
(russian chars with the T and E image), so I excluded them and left only english chars.
And I supposed that there may be leading and trailing spaces (invisible chars).
Therefore i searched the like '%VW%2%'.
[~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 17 17:21:04 2018
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
Is it the view ?
SQL> select * from dba_views where view_name like '%VW%2%';
no rows selected
Is it the synonym ?
SQL> select * from SYS.DBA_SYNONYMS where synonym_name like '%VW%2%' ;
no rows selected
What is the object ?
SQL> select * from dba_objects where object_name like '%VW%2%';
no rows selected
SQL> select * from sys.obj$ where name like '%VW%2%';
no rows selected
Obvious, there is no such view in the dictionary !
What's the hell ? Where the view VW_TE_2 was taken from ?
Step 3: Let look VW_TE_2 in the support.oracle.com and in Internet
Great! One of my friends have wrote some post with VW_TE_2 view:
https://iusoltsev.wordpress.com/2014/06/25/table-expansion-features/
This is the Table Expansion feature: https://blogs.oracle.com/optimizer/optimizer-transformations:-table-expansion
This feature is applicable for queries on partitioned tables which have local indexes.
Oracle can have access to table rows via index or directly to table using full table/partition scan.
And during parse Optimizer decide which path is better.
In some cases when the query benefits from index access path then Oracle can take the data from the index and not touch the table rows.
Index-only access path, "Fast Full Index Scan" for example.
And what to do if our query benefits from index-only access, but some index partitions are in UNUSABLE state ?
Table expansion take place here !
It is just my case.
Optimizer divide one query to two similar sub-queries.
One sub-query is go to table rows (not use index) and scan particular partitions.
Other sub-query is using index (local partitions which are in USABLE state).
As you can see in my post above.
Look at the last two columns of plan table. These columns show Pstart Pstop partitons of the table:
-----------------
| Pstart| Pstop |
-----------------
| | |
| | |
| | |
| | |
| 1 | 3072 |
| 1 | 3072 |
| 3073 | 3437 |
| 3073 | 3437 |
-----------------
As you can see the one sub-query scans the 1-3072 partitions, and 2nd sub-query scans 3073-3437 partitions.
Step 4: how to disable TE ?
If my diagnose about TE feature is correct, then after disabling this feature the long-running query will go fast.
It will confirm my diagnose.
Internet helped to find two hints:
- /*+ opt_param('_optimizer_table_expansion', 'false') */ - will disable TE
- /*+ EXPAND_TABLE(tab_alias) */ - will force TE
The query has 3 form of predicates:
- date '2015-01-01'
- trunc(sysdate-1112) ( for today 18-JAN-2018 we use trunc(sysdate-1113) and so on...)
- to_date('01.01.2015','dd.mm.yyyy')
In our case we have an issue. The syntax " date '2015-01-01' " and " trunc(sysdate-1112) " prevent the table expansion feature:
- date '2015-01-01' - no TE and fast plan
- trunc(sysdate-1112) - no TE and fast plan
- to_date('01.01.2015','dd.mm.yyyy') - TE take place and wrong plan.
Therefore we can make two experiments:
- disabling TE for bad query will lead to good plan and short time
- forcing TE for good queries will lead to bad plan and long time
SQL> set lines 999 pages 99
SQL> set timing on
SQL> set autot on
SQL> select /*+ opt_param('_optimizer_table_expansion', 'false') */ min(a.ENTRYDATE) from ORDERS_BASE a where a.ENTRYDATE < to_date('01.01.2015','dd.mm.yyyy');
MIN(A.ENT
---------
10-JAN-06
Elapsed: 00:00:00.04
Execution Plan
----------------------------------------------------------
Plan hash value: 2553418393
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 309K (30)| 00:00:08 | | |
| 1 | PARTITION RANGE ITERATOR MIN/MAX| | 1 | 8 | | | 1 | 3437 |
| 2 | SORT AGGREGATE | | 1 | 8 | | | | |
| 3 | TABLE ACCESS STORAGE FULL | ORDERS_BASE | 9195M| 68G| 309K (30)| 00:00:08 | 1 | 3437 |
----------------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
2 db block gets
19 consistent gets
13 physical reads
0 redo size
555 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Great, be obtain the good plan and short time !
Try aggravate the good plan:
SQL> select /*+ EXPAND_TABLE(a) */ min(a.ENTRYDATE) from ORDERS_BASE a where a.ENTRYDATE < date '2015-01-01';
MIN(A.ENT
---------
10-JAN-06
Elapsed: 00:13:29.91
Execution Plan
----------------------------------------------------------
Plan hash value: 2354560326
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 347K (43)| 00:00:09 | | |
| 1 | SORT AGGREGATE | | 1 | 9 | | | | |
| 2 | VIEW | VW_TE_1 | 9195M| 77G| 347K (43)| 00:00:09 | | |
| 3 | UNION-ALL | | | | | | | |
| 4 | PARTITION RANGE ITERATOR | | 6615M| 123G| 240K (43)| 00:00:07 | 1 | 3072 |
| 5 | TABLE ACCESS STORAGE FULL| ORDERS_BASE | 6615M| 123G| 240K (43)| 00:00:07 | 1 | 3072 |
| 6 | PARTITION RANGE ITERATOR | | 2579M| 48G| 106K (43)| 00:00:03 | 3073 | 3437 |
| 7 | TABLE ACCESS STORAGE FULL| ORDERS_BASE | 2579M| 48G| 106K (43)| 00:00:03 | 3073 | 3437 |
-------------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1068 recursive calls
3206 db block gets
17684816 consistent gets
17666603 physical reads
240 redo size
555 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Yes! After we forced the TE the fast query run the very long time !
The very simple queries take very different time :
select min(a.ENTRYDATE) from ORDERS_BASE a where a.ENTRYDATE < to_date('01.01.2015','dd.mm.yyyy');
select min(a.ENTRYDATE) from ORDERS_BASE a where a.ENTRYDATE < trunc(sysdate-1112);
select min(a.ENTRYDATE) from ORDERS_BASE a where a.ENTRYDATE < date '2015-01-01';
First query takes 850 second.
2nd and 3rd queries take .04 second.
Find the difference !
:(
The queries were tested at 17-JAN-2018 so trunc(sysdate-1112) == to_date('01.01.2015','dd.mm.yyyy') that day.
For clarity and for checking the difference in data types i did dump of these values:
SQL> select dump(trunc(sysdate-1112)) from dual;
DUMP(TRUNC(SYSDATE-1112))
--------------------------------------------------------------------------------
Typ=13 Len=8: 223,7,1,1,0,0,0,0
SQL> select dump(to_date('01.01.2015','dd.mm.yyyy')) from dual;
DUMP(TO_DATE('01.01.2015','DD.M
-------------------------------
Typ=13 Len=8: 223,7,1,1,0,0,0,0
SQL> select dump(date '2015-01-01') from dual;
DUMP(DATE'2015-01-01')
-------------------------------
Typ=13 Len=8: 223,7,1,1,0,0,0,0
INVESTIGATION:
Step 0: Gather facts
DB version is 12.1.0.2.170418, Oracle on Linux x64.
Table has 65 columns:
SQL> desc ORDERS_BASE
Name Null? Type
----------------------------------------- -------- ------
...
ENTRYDATE DATE
...
CREATE TABLE "ORDERS_BASE"
( ...
"ENTRYDATE" DATE,
...
)
PARTITION BY RANGE ("ENTRYDATE")
(PARTITION "CURR_ORDERS_BASE_P_20050804" VALUES LESS THAN (TO_DATE(' 2005-08-05 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED ,
PARTITION "CURR_ORDERS_BASE_P_20050805" VALUES LESS THAN (TO_DATE(' 2005-08-06 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED ,
...
PARTITION "CURR_ORDERS_BASE_P_20200310" VALUES LESS THAN (TO_DATE(' 2020-03-11 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED ,
PARTITION "CURR_ORDERS_BASE_P_20200409" VALUES LESS THAN (TO_DATE(' 2020-04-10 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED
) ;Table has about 5000 partitions.
Table has 2 indexes:
CREATE UNIQUE INDEX "ORDERS_CURR_UIDX" ON "ORDERS_BASE" ("ENTRYDATE", "ORDERNO") LOCAL;
All 2014 year is in unusable state :
ALTER INDEX "ORDERS_CURR_UIDX" MODIFY PARTITION "CURR_ORDERS_BASE_P_20140101" UNUSABLE;
...
ALTER INDEX "ORDERS_CURR_UIDX" MODIFY PARTITION "CURR_ORDERS_BASE_P_20141231" UNUSABLE;
CREATE INDEX "ORDER_CURR_ORDNO_IDX" ON "ORDERS_BASE" ("ORDERNO") LOCAL;
All 2014 year is in unusable state :
ALTER INDEX "ORDER_CURR_ORDNO_IDX" MODIFY PARTITION "CURR_ORDERS_BASE_P_20140101" UNUSABLE;
...
ALTER INDEX "ORDER_CURR_ORDNO_IDX" MODIFY PARTITION "CURR_ORDERS_BASE_P_20141231" UNUSABLE;
Step 1: Lets measure
SQL> select min(a.ENTRYDATE) from ORDERS_BASE a where a.ENTRYDATE < trunc(sysdate-1112);
MIN(A.ENTRYDATE)
-------------------
10.01.2006 00:00:00
Elapsed: 00:00:00.04
SQL> select min(a.ENTRYDATE) from ORDERS_BASE a where a.ENTRYDATE < date '2015-01-01';
MIN(A.ENT
---------
10-JAN-06
Elapsed: 00:00:00.04
SQL> select min(a.ENTRYDATE) from ORDERS_BASE a where a.ENTRYDATE < to_date('01.01.2015','dd.mm.yyyy');
MIN(A.ENTRYDATE)
-------------------
10.01.2006 00:00:00
Elapsed: 00:14:10.64
Step 2: Lets look at the execution plan
The good plan at first:
SQL> set autotrace on
SQL> set lines 999 pages 99
SQL> select min(a.ENTRYDATE) from ORDERS_BASE a where a.ENTRYDATE < trunc(sysdate-1112);
MIN(A.ENTRYDATE)
-------------------
10.01.2006 00:00:00
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 2553418393
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 1404K (65)| 00:00:36 | | |
| 1 | PARTITION RANGE ITERATOR MIN/MAX| | 1 | 8 | | | 1 | KEY |
| 2 | SORT AGGREGATE | | 1 | 8 | | | | |
|* 3 | TABLE ACCESS STORAGE FULL | ORDERS_BASE | 9195M| 68G| 1404K (65)| 00:00:36 | 1 | KEY |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - storage("A"."ENTRYDATE"<TRUNC(SYSDATE@!-1112))
filter("A"."ENTRYDATE"<TRUNC(SYSDATE@!-1112))
Statistics
----------------------------------------------------------
0 recursive calls
2 db block gets
19 consistent gets
13 physical reads
0 redo size
555 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Here i added the dummy hint to force hard parsing and generate the new plan:
SQL> select /*+ U */ min(a.ENTRYDATE) from ORDERS_BASE a where a.ENTRYDATE < trunc(sysdate-1112);
MIN(A.ENTRYDATE)
-------------------
10.01.2006 00:00:00
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 2553418393
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 1404K (65)| 00:00:36 | | |
| 1 | PARTITION RANGE ITERATOR MIN/MAX| | 1 | 8 | | | 1 | KEY |
| 2 | SORT AGGREGATE | | 1 | 8 | | | | |
|* 3 | TABLE ACCESS STORAGE FULL | ORDERS_BASE | 9195M| 68G| 1404K (65)| 00:00:36 | 1 | KEY |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - storage("A"."ENTRYDATE"<TRUNC(SYSDATE@!-1112))
filter("A"."ENTRYDATE"<TRUNC(SYSDATE@!-1112))
Statistics
----------------------------------------------------------
1 recursive calls
2 db block gets
19 consistent gets
13 physical reads
0 redo size
555 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
After hard parsing the new plan was generated and we see the no difference.
Oracle strongly keep the best plan.
Let probe the bad query:
SQL> select min(a.ENTRYDATE) from ORDERS_BASE a where a.ENTRYDATE < to_date('01.01.2015','dd.mm.yyyy');
MIN(A.ENTRYDATE)
-------------------
10.01.2006 00:00:00
Elapsed: 00:14:10.64
Execution Plan
----------------------------------------------------------
Plan hash value: 2334607833
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 285K (31)| 00:00:08 | | |
| 1 | SORT AGGREGATE | | 1 | 8 | | | | |
| 2 | VIEW | VW_TE_2 | 9195M| 77G| 285K (31)| 00:00:08 | | |
| 3 | UNION-ALL | | | | | | | |
| 4 | PARTITION RANGE ITERATOR | | 6615M| 49G| 195K (30)| 00:00:05 | 1 | 3072 |
| 5 | TABLE ACCESS STORAGE FULL| ORDERS_BASE | 6615M| 49G| 195K (30)| 00:00:05 | 1 | 3072 |
| 6 | PARTITION RANGE ITERATOR | | 2579M| 19G| 89337 (32)| 00:00:03 | 3073 | 3437 |
| 7 | TABLE ACCESS STORAGE FULL| ORDERS_BASE | 2579M| 19G| 89337 (32)| 00:00:03 | 3073 | 3437 |
-------------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
657 recursive calls
3206 db block gets
17682261 consistent gets
17666575 physical reads
120 redo size
555 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
We see absolutely different plan:
- ONE full table scan has become TWO table scans
- and the view VW_TE_2 appeared
Let find the information about this view. I suppose the chars T and E may be in russian codepage
(russian chars with the T and E image), so I excluded them and left only english chars.
And I supposed that there may be leading and trailing spaces (invisible chars).
Therefore i searched the like '%VW%2%'.
[~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 17 17:21:04 2018
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
Is it the view ?
SQL> select * from dba_views where view_name like '%VW%2%';
no rows selected
Is it the synonym ?
SQL> select * from SYS.DBA_SYNONYMS where synonym_name like '%VW%2%' ;
no rows selected
What is the object ?
SQL> select * from dba_objects where object_name like '%VW%2%';
no rows selected
SQL> select * from sys.obj$ where name like '%VW%2%';
no rows selected
Obvious, there is no such view in the dictionary !
What's the hell ? Where the view VW_TE_2 was taken from ?
Step 3: Let look VW_TE_2 in the support.oracle.com and in Internet
Great! One of my friends have wrote some post with VW_TE_2 view:
https://iusoltsev.wordpress.com/2014/06/25/table-expansion-features/
This is the Table Expansion feature: https://blogs.oracle.com/optimizer/optimizer-transformations:-table-expansion
This feature is applicable for queries on partitioned tables which have local indexes.
Oracle can have access to table rows via index or directly to table using full table/partition scan.
And during parse Optimizer decide which path is better.
In some cases when the query benefits from index access path then Oracle can take the data from the index and not touch the table rows.
Index-only access path, "Fast Full Index Scan" for example.
And what to do if our query benefits from index-only access, but some index partitions are in UNUSABLE state ?
Table expansion take place here !
It is just my case.
Optimizer divide one query to two similar sub-queries.
One sub-query is go to table rows (not use index) and scan particular partitions.
Other sub-query is using index (local partitions which are in USABLE state).
As you can see in my post above.
Look at the last two columns of plan table. These columns show Pstart Pstop partitons of the table:
-----------------
| Pstart| Pstop |
-----------------
| | |
| | |
| | |
| | |
| 1 | 3072 |
| 1 | 3072 |
| 3073 | 3437 |
| 3073 | 3437 |
-----------------
As you can see the one sub-query scans the 1-3072 partitions, and 2nd sub-query scans 3073-3437 partitions.
Step 4: how to disable TE ?
If my diagnose about TE feature is correct, then after disabling this feature the long-running query will go fast.
It will confirm my diagnose.
Internet helped to find two hints:
- /*+ opt_param('_optimizer_table_expansion', 'false') */ - will disable TE
- /*+ EXPAND_TABLE(tab_alias) */ - will force TE
The query has 3 form of predicates:
- date '2015-01-01'
- trunc(sysdate-1112) ( for today 18-JAN-2018 we use trunc(sysdate-1113) and so on...)
- to_date('01.01.2015','dd.mm.yyyy')
In our case we have an issue. The syntax " date '2015-01-01' " and " trunc(sysdate-1112) " prevent the table expansion feature:
- date '2015-01-01' - no TE and fast plan
- trunc(sysdate-1112) - no TE and fast plan
- to_date('01.01.2015','dd.mm.yyyy') - TE take place and wrong plan.
Therefore we can make two experiments:
- disabling TE for bad query will lead to good plan and short time
- forcing TE for good queries will lead to bad plan and long time
SQL> set lines 999 pages 99
SQL> set timing on
SQL> set autot on
SQL> select /*+ opt_param('_optimizer_table_expansion', 'false') */ min(a.ENTRYDATE) from ORDERS_BASE a where a.ENTRYDATE < to_date('01.01.2015','dd.mm.yyyy');
MIN(A.ENT
---------
10-JAN-06
Elapsed: 00:00:00.04
Execution Plan
----------------------------------------------------------
Plan hash value: 2553418393
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 309K (30)| 00:00:08 | | |
| 1 | PARTITION RANGE ITERATOR MIN/MAX| | 1 | 8 | | | 1 | 3437 |
| 2 | SORT AGGREGATE | | 1 | 8 | | | | |
| 3 | TABLE ACCESS STORAGE FULL | ORDERS_BASE | 9195M| 68G| 309K (30)| 00:00:08 | 1 | 3437 |
----------------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
2 db block gets
19 consistent gets
13 physical reads
0 redo size
555 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Great, be obtain the good plan and short time !
Try aggravate the good plan:
SQL> select /*+ EXPAND_TABLE(a) */ min(a.ENTRYDATE) from ORDERS_BASE a where a.ENTRYDATE < date '2015-01-01';
MIN(A.ENT
---------
10-JAN-06
Elapsed: 00:13:29.91
Execution Plan
----------------------------------------------------------
Plan hash value: 2354560326
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 347K (43)| 00:00:09 | | |
| 1 | SORT AGGREGATE | | 1 | 9 | | | | |
| 2 | VIEW | VW_TE_1 | 9195M| 77G| 347K (43)| 00:00:09 | | |
| 3 | UNION-ALL | | | | | | | |
| 4 | PARTITION RANGE ITERATOR | | 6615M| 123G| 240K (43)| 00:00:07 | 1 | 3072 |
| 5 | TABLE ACCESS STORAGE FULL| ORDERS_BASE | 6615M| 123G| 240K (43)| 00:00:07 | 1 | 3072 |
| 6 | PARTITION RANGE ITERATOR | | 2579M| 48G| 106K (43)| 00:00:03 | 3073 | 3437 |
| 7 | TABLE ACCESS STORAGE FULL| ORDERS_BASE | 2579M| 48G| 106K (43)| 00:00:03 | 3073 | 3437 |
-------------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1068 recursive calls
3206 db block gets
17684816 consistent gets
17666603 physical reads
240 redo size
555 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Yes! After we forced the TE the fast query run the very long time !
