You Asked
Hi Tom,
Can you explain why the execution plan for a query changes after truncating and reloading tables with exactly the same data ?
Here's my example :
--
-- Create Tables
--
CREATE TABLE CUSTOMER_DIM
( MONTH_KEY VARCHAR2 (6) NOT NULL ,
CUSTOMER_KEY NUMBER (12) NOT NULL ,
DATE_OPENED DATE NULL ,
DATE_CLOSED DATE NULL ,
ACTIVE_IND VARCHAR2 (1) NULL )
PARTITION BY RANGE (MONTH_KEY)
(PARTITION CUS_200507 VALUES LESS THAN ('200508'),
PARTITION CUS_200508 VALUES LESS THAN ('200509'),
PARTITION CUS_200509 VALUES LESS THAN ('200510'));
CREATE UNIQUE INDEX CUS_PK ON CUSTOMER_DIM (MONTH_KEY, CUSTOMER_KEY);
ALTER TABLE CUSTOMER_DIM ADD (CONSTRAINT CUS_PK PRIMARY KEY (MONTH_KEY, CUSTOMER_KEY));
CREATE TABLE ACCOUNT_PERFORMANCE_FACT
( MONTH_KEY VARCHAR2 (6) NOT NULL ,
ACCOUNT_KEY NUMBER (12) NOT NULL ,
CUSTOMER_KEY NUMBER (12) NOT NULL ,
DATE_LAST_ACTIVITY DATE NULL ,
GOOD_BAD_IND VARCHAR2 (1) NULL )
PARTITION BY RANGE (MONTH_KEY)
(PARTITION APF_200507 VALUES LESS THAN ('200508'),
PARTITION APF_200508 VALUES LESS THAN ('200509'),
PARTITION APF_200509 VALUES LESS THAN ('200510'));
CREATE UNIQUE INDEX APF_PK ON ACCOUNT_PERFORMANCE_FACT (MONTH_KEY, ACCOUNT_KEY) LOCAL;
ALTER TABLE ACCOUNT_PERFORMANCE_FACT ADD (CONSTRAINT APF_PK PRIMARY KEY (MONTH_KEY, ACCOUNT_KEY) USING INDEX LOCAL);
CREATE TABLE CARD_PERFORMANCE_FACT
( MONTH_KEY VARCHAR2 (6) NOT NULL ,
ACCOUNT_KEY NUMBER (12) NOT NULL ,
NUM_OF_PAYMENTS NUMBER (8) NULL ,
NUM_OF_REFUNDS NUMBER (8) NULL ,
NUM_OF_CREDITS NUMBER (8) NULL )
PARTITION BY RANGE (MONTH_KEY)
(PARTITION CPF_200507 VALUES LESS THAN ('200508'),
PARTITION CPF_200508 VALUES LESS THAN ('200509'),
PARTITION CPF_200509 VALUES LESS THAN ('200510'));
CREATE UNIQUE INDEX CPF_PK ON CARD_PERFORMANCE_FACT (MONTH_KEY, ACCOUNT_KEY) LOCAL;
ALTER TABLE CARD_PERFORMANCE_FACT ADD (CONSTRAINT CPF_PK PRIMARY KEY (MONTH_KEY, ACCOUNT_KEY) USING INDEX LOCAL);
--
-- Insert data
--
truncate table CUSTOMER_DIM;
truncate table ACCOUNT_PERFORMANCE_FACT;
truncate table CARD_PERFORMANCE_FACT;
insert into ACCOUNT_PERFORMANCE_FACT
select '200507',
rownum,
mod(rownum, 25) + 1,
to_date('20041231', 'yyyymmdd') + mod(rownum, 30),
'Y'
from (select 1
from dba_objects
where rownum < 1001),
(select 1
from dba_objects
where rownum < 1001);
insert into ACCOUNT_PERFORMANCE_FACT
select '200508',
account_key,
customer_key,
date_last_activity,
good_bad_ind
from ACCOUNT_PERFORMANCE_FACT
where month_key = '200507';
insert into ACCOUNT_PERFORMANCE_FACT
select '200509',
account_key,
customer_key,
date_last_activity,
good_bad_ind
from ACCOUNT_PERFORMANCE_FACT
where month_key = '200507';
insert into CUSTOMER_DIM
select month_key,
account_key customer_key,
sysdate,
null,
case when account_key < 30 then 'A' else 'B' end
from ACCOUNT_PERFORMANCE_FACT
where account_key < 5001;
insert into CARD_PERFORMANCE_FACT
select month_key,
account_key,
mod(rownum, 10),
mod(rownum, 15),
mod(rownum, 25)
from ACCOUNT_PERFORMANCE_FACT
where mod(account_key, 10) = 0;
commit;
exec dbms_stats.gather_table_stats(tabname=>'CUSTOMER_DIM', ownname=>user, cascade=>true);
exec dbms_stats.gather_table_stats(tabname=>'ACCOUNT_PERFORMANCE_FACT', ownname=>user, cascade=>true);
exec dbms_stats.gather_table_stats(tabname=>'CARD_PERFORMANCE_FACT', ownname=>user, cascade=>true);
--
-- End of Inserts
--
The explain plans for my two queries now look like this :
Query 1
=======
explain plan for
select a.*,
b.*
from CUSTOMER_DIM a,
ACCOUNT_PERFORMANCE_FACT b
where a.CUSTOMER_KEY = b.CUSTOMER_KEY
and a.MONTH_KEY = b.MONTH_KEY
and a.ACTIVE_IND = 'A'
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1498K| 64M| 2521 (15)| 00:00:31 | | |
| 1 | PARTITION RANGE ALL| | 1498K| 64M| 2521 (15)| 00:00:31 | 1 | 3 |
|* 2 | HASH JOIN | | 1498K| 64M| 2521 (15)| 00:00:31 | | |
|* 3 | TABLE ACCESS FULL| CUSTOMER_DIM | 7500 | 153K| 14 (15)| 00:00:01 | 1 | 3 |
| 4 | TABLE ACCESS FULL| ACCOUNT_PERFORMANCE_FACT | 2997K| 68M| 2368 (10)| 00:00:29 | 1 | 3 |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."MONTH_KEY"="B"."MONTH_KEY" AND "A"."CUSTOMER_KEY"="B"."CUSTOMER_KEY")
3 - filter("A"."ACTIVE_IND"='A')
Query 2
=======
explain plan for
select a.*,
b.*,
c.*
from CUSTOMER_DIM a,
ACCOUNT_PERFORMANCE_FACT b,
CARD_PERFORMANCE_FACT c
where a.CUSTOMER_KEY = b.CUSTOMER_KEY
and a.MONTH_KEY = b.MONTH_KEY
and a.ACTIVE_IND = 'A'
and b.ACCOUNT_KEY = c.ACCOUNT_KEY
and b.MONTH_KEY = c.MONTH_KEY
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 158K| 9M| | 9544 (6)| 00:01:55 | | |
| 1 | PARTITION RANGE ALL | | 158K| 9M| | 9544 (6)| 00:01:55 | 1 | 3 |
|* 2 | HASH JOIN | | 158K| 9M| 3144K| 9544 (6)| 00:01:55 | | |
| 3 | TABLE ACCESS FULL | CARD_PERFORMANCE_FACT | 301K| 5889K| | 207 (11)| 00:00:03 | 1 | 3 |
|* 4 | HASH JOIN | | 1498K| 64M| | 2521 (15)| 00:00:31 | | |
|* 5 | TABLE ACCESS FULL| CUSTOMER_DIM | 7500 | 153K| | 14 (15)| 00:00:01 | 1 | 3 |
| 6 | TABLE ACCESS FULL| ACCOUNT_PERFORMANCE_FACT | 2997K| 68M| | 2368 (10)| 00:00:29 | 1 | 3 |
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("B"."MONTH_KEY"="C"."MONTH_KEY" AND "B"."ACCOUNT_KEY"="C"."ACCOUNT_KEY")
4 - access("A"."MONTH_KEY"="B"."MONTH_KEY" AND "A"."CUSTOMER_KEY"="B"."CUSTOMER_KEY")
5 - filter("A"."ACTIVE_IND"='A')
Now re-run the inserts script. This time the explain plans look like this :
Query 1
=======
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17380 | 763K| 2520 (15)| 00:00:31 | | |
| 1 | PARTITION RANGE ALL| | 17380 | 763K| 2520 (15)| 00:00:31 | 1 | 3 |
|* 2 | HASH JOIN | | 17380 | 763K| 2520 (15)| 00:00:31 | | |
|* 3 | TABLE ACCESS FULL| CUSTOMER_DIM | 87 | 1827 | 14 (15)| 00:00:01 | 1 | 3 |
| 4 | TABLE ACCESS FULL| ACCOUNT_PERFORMANCE_FACT | 2996K| 68M| 2368 (10)| 00:00:29 | 1 | 3 |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."MONTH_KEY"="B"."MONTH_KEY" AND "A"."CUSTOMER_KEY"="B"."CUSTOMER_KEY")
3 - filter("A"."ACTIVE_IND"='A')
Query 2
=======
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1816 | 115K| 4330 (10)| 00:00:52 | | |
| 1 | NESTED LOOPS | | 1816 | 115K| 4330 (10)| 00:00:52 | | |
| 2 | PARTITION RANGE ALL | | 17380 | 763K| 2520 (15)| 00:00:31 | 1 | 3 |
|* 3 | HASH JOIN | | 17380 | 763K| 2520 (15)| 00:00:31 | | |
|* 4 | TABLE ACCESS FULL | CUSTOMER_DIM | 87 | 1827 | 14 (15)| 00:00:01 | 1 | 3 |
| 5 | TABLE ACCESS FULL | ACCOUNT_PERFORMANCE_FACT | 2996K| 68M| 2368 (10)| 00:00:29 | 1 | 3 |
| 6 | PARTITION RANGE ITERATOR | | 1 | 20 | 1 (0)| 00:00:01 | KEY | KEY |
| 7 | TABLE ACCESS BY LOCAL INDEX ROWID| CARD_PERFORMANCE_FACT | 1 | 20 | 1 (0)| 00:00:01 | KEY | KEY |
|* 8 | INDEX UNIQUE SCAN | CPF_PK | 1 | | 0 (0)| 00:00:01 | KEY | KEY |
--------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."MONTH_KEY"="B"."MONTH_KEY" AND "A"."CUSTOMER_KEY"="B"."CUSTOMER_KEY")
4 - filter("A"."ACTIVE_IND"='A')
8 - access("B"."MONTH_KEY"="C"."MONTH_KEY" AND "B"."ACCOUNT_KEY"="C"."ACCOUNT_KEY")
Notice how for Query 1, the 'Rows' has dropped from 1.5 million to 17,000 - this presumably explains why Query 2 has changed to use Nested Loops (because the lower number of rows returned by the first join makes a NL join more attractive).
Following the reload, Query 2 takes about twice as long to execute as it did on the first run.
Any help would be much appreciated.
Thanks,
Simon.
and Tom said...
you don't even need the truncate, all you need to do is regather statistics.
In 10g, the "method_opt" parameter defaults to a "size auto". After you ran a query - the database remembered the predicates and updated a dictionary table sys.col_usage$. Then, the next time you ran dbms_stats, it queried that table to find out what columns should have histograms collected automagically based on "past query workload". It looked at your predicates and said "hmm, these columns should have histograms".
I can reproduce your experience without the truncate. If you do not want this behaviour, you would not use AUTO as the method_opt default - you would be more explicit.
ops$tkyte@ORA10GR1> exec dbms_stats.gather_table_stats(tabname=>'CUSTOMER_DIM', ownname=>user, cascade=>true );
PL/SQL procedure successfully completed.
ops$tkyte@ORA10GR1> exec dbms_stats.gather_table_stats(tabname=>'ACCOUNT_PERFORMANCE_FACT', ownname=>user, cascade=>true );
PL/SQL procedure successfully completed.
ops$tkyte@ORA10GR1> exec dbms_stats.gather_table_stats(tabname=>'CARD_PERFORMANCE_FACT', ownname=>user, cascade=>true );
PL/SQL procedure successfully completed.
ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> --
ops$tkyte@ORA10GR1> -- End of Inserts
ops$tkyte@ORA10GR1> --
ops$tkyte@ORA10GR1> delete from plan_table;
7 rows deleted.
ops$tkyte@ORA10GR1> explain plan for
2 select a.*,
3 b.*
4 from CUSTOMER_DIM a,
5 ACCOUNT_PERFORMANCE_FACT b
6 where a.CUSTOMER_KEY = b.CUSTOMER_KEY
7 and a.MONTH_KEY = b.MONTH_KEY
8 and a.ACTIVE_IND = 'A';
Explained.
ops$tkyte@ORA10GR1> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1977428117
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1500K| 64M| 2924
| 1 | PARTITION RANGE ALL| | 1500K| 64M| 2924
|* 2 | HASH JOIN | | 1500K| 64M| 2924
|* 3 | TABLE ACCESS FULL| CUSTOMER_DIM | 7500 | 153K| 16
| 4 | TABLE ACCESS FULL| ACCOUNT_PERFORMANCE_FACT | 3000K| 68M| 2836
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."MONTH_KEY"="B"."MONTH_KEY" AND "A"."CUSTOMER_KEY"="B"."CUSTOMER_KEY")
3 - filter("A"."ACTIVE_IND"='A')
17 rows selected.
ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> delete from plan_table;
5 rows deleted.
ops$tkyte@ORA10GR1> explain plan for
2 select a.*,
3 b.*,
4 c.*
5 from CUSTOMER_DIM a,
6 ACCOUNT_PERFORMANCE_FACT b,
7 CARD_PERFORMANCE_FACT c
8 where a.CUSTOMER_KEY = b.CUSTOMER_KEY
9 and a.MONTH_KEY = b.MONTH_KEY
10 and a.ACTIVE_IND = 'A'
11 and b.ACCOUNT_KEY = c.ACCOUNT_KEY
12 and b.MONTH_KEY = c.MONTH_KEY;
Explained.
ops$tkyte@ORA10GR1> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1330301989
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 147K| 9344K|
| 1 | PARTITION RANGE ALL | | 147K| 9344K|
|* 2 | HASH JOIN | | 147K| 9344K|
| 3 | TABLE ACCESS FULL | CARD_PERFORMANCE_FACT | 300K| 5859K|
|* 4 | HASH JOIN | | 1500K| 64M|
|* 5 | TABLE ACCESS FULL| CUSTOMER_DIM | 7500 | 153K|
| 6 | TABLE ACCESS FULL| ACCOUNT_PERFORMANCE_FACT | 3000K| 68M|
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("B"."MONTH_KEY"="C"."MONTH_KEY" AND "B"."ACCOUNT_KEY"="C"."ACCOUNT_KEY")
4 - access("A"."MONTH_KEY"="B"."MONTH_KEY" AND "A"."CUSTOMER_KEY"="B"."CUSTOMER_KEY")
5 - filter("A"."ACTIVE_IND"='A')
20 rows selected.
ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> exec dbms_stats.gather_table_stats(tabname=>'CUSTOMER_DIM', ownname=>user, cascade=>true );
PL/SQL procedure successfully completed.
ops$tkyte@ORA10GR1> exec dbms_stats.gather_table_stats(tabname=>'ACCOUNT_PERFORMANCE_FACT', ownname=>user, cascade=>true );
PL/SQL procedure successfully completed.
ops$tkyte@ORA10GR1> exec dbms_stats.gather_table_stats(tabname=>'CARD_PERFORMANCE_FACT', ownname=>user, cascade=>true );
PL/SQL procedure successfully completed.
ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> --
ops$tkyte@ORA10GR1> -- End of Inserts
ops$tkyte@ORA10GR1> --
ops$tkyte@ORA10GR1> delete from plan_table;
7 rows deleted.
ops$tkyte@ORA10GR1> explain plan for
2 select a.*,
3 b.*
4 from CUSTOMER_DIM a,
5 ACCOUNT_PERFORMANCE_FACT b
6 where a.CUSTOMER_KEY = b.CUSTOMER_KEY
7 and a.MONTH_KEY = b.MONTH_KEY
8 and a.ACTIVE_IND = 'A';
Explained.
ops$tkyte@ORA10GR1> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1977428117
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17400 | 764K| 2924
| 1 | PARTITION RANGE ALL| | 17400 | 764K| 2924
|* 2 | HASH JOIN | | 17400 | 764K| 2924
|* 3 | TABLE ACCESS FULL| CUSTOMER_DIM | 87 | 1827 | 16
| 4 | TABLE ACCESS FULL| ACCOUNT_PERFORMANCE_FACT | 3000K| 68M| 2836
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."MONTH_KEY"="B"."MONTH_KEY" AND "A"."CUSTOMER_KEY"="B"."CUSTOMER_KEY")
3 - filter("A"."ACTIVE_IND"='A')
17 rows selected.
ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> delete from plan_table;
5 rows deleted.
ops$tkyte@ORA10GR1> explain plan for
2 select a.*,
3 b.*,
4 c.*
5 from CUSTOMER_DIM a,
6 ACCOUNT_PERFORMANCE_FACT b,
7 CARD_PERFORMANCE_FACT c
8 where a.CUSTOMER_KEY = b.CUSTOMER_KEY
9 and a.MONTH_KEY = b.MONTH_KEY
10 and a.ACTIVE_IND = 'A'
11 and b.ACCOUNT_KEY = c.ACCOUNT_KEY
12 and b.MONTH_KEY = c.MONTH_KEY;
Explained.
ops$tkyte@ORA10GR1> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1329693311
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1817 | 115K| 3189
| 1 | PARTITION RANGE ALL | | 1817 | 115K| 3189
|* 2 | HASH JOIN | | 1817 | 115K| 3189
|* 3 | HASH JOIN | | 17400 | 764K| 2924
|* 4 | TABLE ACCESS FULL| CUSTOMER_DIM | 87 | 1827 | 16
| 5 | TABLE ACCESS FULL| ACCOUNT_PERFORMANCE_FACT | 3000K| 68M| 2836
| 6 | TABLE ACCESS FULL | CARD_PERFORMANCE_FACT | 300K| 5859K| 256 -----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("B"."MONTH_KEY"="C"."MONTH_KEY" AND "B"."ACCOUNT_KEY"="C"."ACCOUNT_KEY")
3 - access("A"."MONTH_KEY"="B"."MONTH_KEY" AND "A"."CUSTOMER_KEY"="B"."CUSTOMER_KEY")
4 - filter("A"."ACTIVE_IND"='A')
20 rows selected.
ops$tkyte@ORA10GR1>
Rating
(8 ratings)
Is this answer out of date? If it is, please let us know via a Comment