I have a problem where the execution of a query is too slow in oracle 12c standard edition.
I figured out, the optimizer estimates wrong cardinalities because it misses the correlation between two columns in two different tables, so I tried to create a minimal example explaining the problem.
Given the following tables:
create table test_articles (id number(10,0), cat varchar2(40), CONSTRAINT test_articles_pk PRIMARY KEY (id));
create table test_sales (id number(10,0), article_id number(10,0), country varchar2(40), CONSTRAINT fk_article FOREIGN KEY (article_id) references test_articles (id));
Lets say this shop sales products of several categorys: Toys, Tools and Books. Most products are sold inside the UK, but products of category 'Toys' are mostly exported to France:
begin
for i in 1..100
loop
insert into test_articles values(i,'Toys');
insert into test_articles values(100 + i,'Toys');
insert into test_articles values(200 + i,'Toys');
insert into test_articles values(300 + i,'Tools');
insert into test_articles values(400 + i,'Books');
end loop;
for i in 1..50
loop
insert into test_sales values(i,1,'UK');
end loop;
for i in 51..1050
loop
insert into test_sales values(i,2,'FR');
end loop;
for i in 1051..5000
loop
insert into test_sales values(i,301,'UK');
end loop;
end;
I gathered statistics, forcing the creation of histograms and then searched for all Toys sold to the UK and checked the execution plan
exec dbms_stats.gather_table_stats(null,'TEST_SALES',method_opt => 'for all columns');
exec dbms_stats.gather_table_stats(null,'TEST_ARTICLES',method_opt => 'for all columns');
select /*+ gather_plan_statistics */ * from TEST_SALES s join TEST_ARTICLES a on s.ARTICLE_ID = a.ID
where a.cat = 'Toys' and s.country = 'UK';
select *
from table(dbms_xplan.display_cursor(format => 'allstats last advanced +adaptive'));
I got the following result, where - unsurprisingly - expected rows are way higher than actual rows.
---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 8 (100)| | 50 |00:00:00.01 | 18 | | | |
|* 1 | HASH JOIN | | 1 | 4000 | 324K| 8 (0)| 00:00:01 | 50 |00:00:00.01 | 18 | 1922K| 1922K| 1680K (0)|
|* 2 | TABLE ACCESS FULL| TEST_ARTICLES | 1 | 300 | 10500 | 3 (0)| 00:00:01 | 300 |00:00:00.01 | 7 | | | |
|* 3 | TABLE ACCESS FULL| TEST_SALES | 1 | 4000 | 187K| 5 (0)| 00:00:01 | 1341 |00:00:00.01 | 8 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------
I thought, this could be fixed by using extended statistics with column groups in both tables that include the article-id-columns, which are linked via the foreign key (I forced the creation of histograms for both created column groups, because they were not calculated automatically, when gathering statistics, after the execution of create_extended_stats):
select dbms_stats.create_extended_stats(ownname=>user, tabname=>'TEST_ARTICLES', extension=>'(ID,CAT)') from dual;
select dbms_stats.create_extended_stats(ownname=>user, tabname=>'TEST_SALES', extension=>'(ARTICLE_ID,COUNTRY)') from dual;
exec DBMS_STATS.GATHER_TABLE_STATS (user,'TEST_ARTICLES', method_opt=>'FOR ALL COLUMNS SIZE AUTO FOR COLUMNS SIZE 254 SYS_STUVDR2B43_Y$B$A85Y33E9BGN');
exec DBMS_STATS.GATHER_TABLE_STATS (user,'TEST_SALES', method_opt=>'FOR ALL COLUMNS SIZE AUTO FOR COLUMNS SIZE 254 SYS_STU5TK8KZP1UO386PUVC2TYHT$');
But when I rerun the query now
select /*+ gather_plan_statistics */ * from TEST_SALES s join TEST_ARTICLES a on s.ARTICLE_ID = a.ID
where a.cat = 'Toys' and s.country = 'UK';
select *
from table(dbms_xplan.display_cursor(format => 'allstats last advanced +adaptive'));
the expected rows have only changed slightly and are still way off:
---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 8 (100)| | 50 |00:00:00.01 | 18 | | | |
|* 1 | HASH JOIN | | 1 | 3993 | 83853 | 8 (0)| 00:00:01 | 50 |00:00:00.01 | 18 | 1922K| 1922K| 1537K (0)|
|* 2 | TABLE ACCESS FULL| TEST_ARTICLES | 1 | 300 | 3000 | 3 (0)| 00:00:01 | 300 |00:00:00.01 | 7 | | | |
|* 3 | TABLE ACCESS FULL| TEST_SALES | 1 | 4000 | 44000 | 5 (0)| 00:00:01 | 1341 |00:00:00.01 | 8 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------
Am I doing something wrong? Is the optimizer not able to resolve correlation between multiple tables or is there a way to fix this? I would be thankful, if someone knows how to get the cardinalities right here.
