Today I faced a performance problem caused by a bad cardinality estimation involving a CDB view in a 12.1.0.2 multitenant environment. While solving the problem I did a number of observations that I try to summarize in this blog post.
First of all, when checking the execution plan of a query already running for more than two hours, I noticed that, in the execution plan, neither the referenced CDB view nor one of its underlying objects were referenced. The following query (and its execution plan) executed while connect to the CDB illustrates (I also added the 12.2.0.1 output to show you the difference it that area):
12.1.0.2
SQL> EXPLAIN PLAN FOR SELECT * FROM cdb_tables; SQL> SELECT * FROM table(dbms_xplan.display); Plan hash value: 1439328272 --------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib | --------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 20000 | 16M| 1 (100)| 00:00:01 | | | | | | | 1 | PX COORDINATOR | | | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10000 | 20000 | 16M| 1 (100)| 00:00:01 | | | Q1,00 | P->S | QC (RAND) | | 3 | PX PARTITION LIST ALL| | 20000 | 16M| 1 (100)| 00:00:01 | 1 | 254 | Q1,00 | PCWC | | | 4 | FIXED TABLE FULL | X$CDBVW$ | 20000 | 16M| 1 (100)| 00:00:01 | | | Q1,00 | PCWP | | --------------------------------------------------------------------------------------------------------------------------------- |
12.2.0.1
SQL> EXPLAIN PLAN FOR SELECT * FROM cdb_tables; SQL> SELECT * FROM table(dbms_xplan.display); Plan hash value: 1043806087 ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 20000 | 28M| 1 (100)| 00:00:01 | | | | 1 | PARTITION LIST ALL| | 20000 | 28M| 1 (100)| 00:00:01 | 1 | 3 | | 2 | CONTAINERS FULL | DBA_TABLES | 20000 | 28M| 1 (100)| 00:00:01 | | | ------------------------------------------------------------------------------------------------- |
As you can see, the 12.1.0.2 execution plan doesn’t reference any object related to the CDB_TABLES view or one of its underlying tables. Instead, it uses the generic fixed table X$CDBVW$. Simply put, X$CDBVW$ is a fixed table that gives access to data stored in PDBs. To know more, I advise you to read Laurent Leturgez’s blog post entitled Oracle Database 12c CDB$VIEW function.
In the real query, the CDB view was joined to a number of V$ views. Unfortunately, the query optimizer selected the wrong join method (no surprise, it was a nested loops join instead of a hash join…) and the performance was abysmal. When I saw that the cardinality estimations were wrong, I checked whether the involved objects had statistics. But, because of its particular behavior, the fixed table X$CDBVW$ had no statistics. And, by the way, statistics on it can’t be gathered. If you try, you get the following error:
12.1.0.2
SQL> exec dbms_stats.gather_table_stats('SYS','X$CDBVW$')
BEGIN dbms_stats.gather_table_stats('SYS','X$CDBVW$'); END;
*
ERROR at line 1:
ORA-20000: Unable to analyze TABLE "SYS"."X$CDBVW$", analyzing the table is not supported
ORA-06512: at "SYS.DBMS_STATS", line 35464
ORA-06512: at line 1 |
12.2.0.1
SQL> exec dbms_stats.gather_table_stats('SYS','X$CDBVW$')
BEGIN dbms_stats.gather_table_stats('SYS','X$CDBVW$'); END;
*
ERROR at line 1:
ORA-20000: Unable to analyze TABLE "SYS"."X$CDBVW$", insufficient privileges or does not exist
ORA-06512: at "SYS.DBMS_STATS", line 36873
ORA-06512: at "SYS.DBMS_STATS", line 36496
ORA-06512: at "SYS.DBMS_STATS", line 36716
ORA-06512: at line 1 |
As Laurent mentioned in his blog post, the query optimizer uses a default value instead. However, what I noticed is that the estimation wasn’t 10000 rows as he mentioned. In my case it was 30000 rows. The difference is probably due to the version. In fact, he wrote his blog post when only 12.1.0.1 was available, but my customer is using 12.1.0.2. So, I did a couple of test on my own test environment and found out that as of and including 12.1.0.2 the number of rows increases proportionally as the number of open PDBs increases. The following example illustrates:
12.1.0.2 / 12.2.0.1
SQL> SELECT con_id, name, open_mode FROM v$pdbs WHERE open_mode LIKE 'READ%';
CON_ID NAME OPEN_MODE
---------- ------------------------------ ----------
2 PDB$SEED READ ONLY
SQL> EXPLAIN PLAN SET STATEMENT_ID = 'seed' FOR SELECT * FROM cdb_tables;
SQL> ALTER PLUGGABLE DATABASE test1 OPEN;
SQL> EXPLAIN PLAN SET STATEMENT_ID = 'test1' FOR SELECT * FROM cdb_tables;
SQL> ALTER PLUGGABLE DATABASE test2 OPEN;
SQL> EXPLAIN PLAN SET STATEMENT_ID = 'test2' FOR SELECT * FROM cdb_tables;
SQL> ALTER PLUGGABLE DATABASE test3 OPEN;
SQL> EXPLAIN PLAN SET STATEMENT_ID = 'test3' FOR SELECT * FROM cdb_tables;
SQL> SELECT con_id, name, open_mode FROM v$pdbs WHERE open_mode LIKE 'READ%';
CON_ID NAME OPEN_MODE
---------- ------------------------------ ----------
2 PDB$SEED READ ONLY
4 TEST1 READ WRITE
5 TEST2 READ WRITE
6 TEST3 READ WRITE
SQL> SELECT statement_id, cardinality FROM plan_table WHERE id = 0;
STATEMENT_ID CARDINALITY
------------------------------ -----------
seed 20000
test1 30000
test2 40000
test3 50000 |
Finally, in the real query, because of the join condition based on the CON_ID column, the query optimizer incorrectly adjusted the number of rows returned through the fixed table X$CDBVW$. That led me doing few tests related to the selectivity estimations related to the CON_ID column. As the following example illustrates, the query optimizer uses a default selectivity of 1% for equality predicates, and 5% for range predicates.
12.1.0.2
SQL> EXPLAIN PLAN SET STATEMENT_ID = 'all' FOR SELECT * FROM cdb_tables; Explained. SQL> SELECT * FROM table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 1439328272 --------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib | --------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 50000 | 40M| 2 (100)| 00:00:01 | | | | | | | 1 | PX COORDINATOR | | | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10000 | 50000 | 40M| 2 (100)| 00:00:01 | | | Q1,00 | P->S | QC (RAND) | | 3 | PX PARTITION LIST ALL| | 50000 | 40M| 2 (100)| 00:00:01 | 1 | 254 | Q1,00 | PCWC | | | 4 | FIXED TABLE FULL | X$CDBVW$ | 50000 | 40M| 2 (100)| 00:00:01 | | | Q1,00 | PCWP | | --------------------------------------------------------------------------------------------------------------------------------- SQL> EXPLAIN PLAN SET STATEMENT_ID = 'eq' FOR SELECT * FROM cdb_tables WHERE con_id = 0; SQL> EXPLAIN PLAN SET STATEMENT_ID = 'gt' FOR SELECT * FROM cdb_tables WHERE con_id > 0; SQL> SELECT statement_id, cardinality FROM plan_table WHERE id = 0; STATEMENT_ID CARDINALITY ------------------------------ ----------- all 50000 eq 500 gt 2500 |
12.2.0.1
SQL> EXPLAIN PLAN SET STATEMENT_ID = 'all' FOR SELECT * FROM cdb_tables; Explained. SQL> SELECT * FROM table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 1281079049 ----------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib | ----------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 50000 | 42M| 1 (100)| 00:00:01 | | | | | | | 1 | PX COORDINATOR | | | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10000 | 50000 | 42M| 1 (100)| 00:00:01 | | | Q1,00 | P->S | QC (RAND) | | 3 | PX PARTITION LIST ALL| | 50000 | 42M| 1 (100)| 00:00:01 | 1 | 3 | Q1,00 | PCWC | | | 4 | CONTAINERS FULL | DBA_TABLES | 50000 | 42M| 1 (100)| 00:00:01 | | | Q1,00 | PCWP | | ----------------------------------------------------------------------------------------------------------------------------------- SQL> EXPLAIN PLAN SET STATEMENT_ID = 'eq' FOR SELECT * FROM cdb_tables WHERE con_id = 0; SQL> EXPLAIN PLAN SET STATEMENT_ID = 'gt' FOR SELECT * FROM cdb_tables WHERE con_id > 0; SQL> SELECT statement_id, cardinality FROM plan_table WHERE id = 0; STATEMENT_ID CARDINALITY ------------------------------ ----------- all 50000 eq 500 gt 2500 |
It goes without saying that such estimates are way off. Good estimates should consider the number of open PDBs….
In summary, if you see wrong estimates related to CDB views, don’t be surprised. In fact, the query optimizer bases its estimations on a number of default values.
Thanks for the reference Chris ;)
Thank you for the post