Skip to content

Added cardinality to SQL on xmltable to avoid excessive estimates.#756

Merged
jgebal merged 4 commits intodevelopfrom
feature/ut_cursor_compare_cardinality
Sep 26, 2018
Merged

Added cardinality to SQL on xmltable to avoid excessive estimates.#756
jgebal merged 4 commits intodevelopfrom
feature/ut_cursor_compare_cardinality

Conversation

@jgebal
Copy link
Copy Markdown
Member

@jgebal jgebal commented Sep 24, 2018

Resolves #752

@jgebal jgebal added this to the v3.1.3 milestone Sep 24, 2018
utplsql-travis
utplsql-travis reviewed Sep 25, 2018
View reviewed changes
Copy link
Copy Markdown

@utplsql-travis utplsql-travis left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

SonarQube analysis found issues:
Bug Bugs: 0
Vulnerability Vulnerabilities: 0
Code Smell Code Smells: 2

See all issues in SonarCloud

utplsql-travis
utplsql-travis reviewed Sep 25, 2018
View reviewed changes
Copy link
Copy Markdown

@utplsql-travis utplsql-travis left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

SonarQube analysis found issues:
Bug Bugs: 0
Vulnerability Vulnerabilities: 0
Code Smell Code Smells: 2

See all issues in SonarCloud

utplsql-travis
utplsql-travis reviewed Sep 25, 2018
View reviewed changes
Copy link
Copy Markdown

@utplsql-travis utplsql-travis left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

SonarQube analysis found issues:
Bug Bugs: 0
Vulnerability Vulnerabilities: 0
Code Smell Code Smells: 2

See all issues in SonarCloud

utplsql-travis
utplsql-travis reviewed Sep 25, 2018
View reviewed changes
Copy link
Copy Markdown

@utplsql-travis utplsql-travis left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

SonarQube analysis found issues:
Bug Bugs: 0
Vulnerability Vulnerabilities: 0
Code Smell Code Smells: 2

See all issues in SonarCloud

utplsql-travis
utplsql-travis reviewed Sep 25, 2018
View reviewed changes
Copy link
Copy Markdown

@utplsql-travis utplsql-travis left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

SonarQube analysis found issues:
Bug Bugs: 0
Vulnerability Vulnerabilities: 0
Code Smell Code Smells: 2

See all issues in SonarCloud

utplsql-travis
utplsql-travis reviewed Sep 25, 2018
View reviewed changes
Copy link
Copy Markdown

@utplsql-travis utplsql-travis left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

SonarQube analysis found issues:
Bug Bugs: 0
Vulnerability Vulnerabilities: 0
Code Smell Code Smells: 2

See all issues in SonarCloud

utplsql-travis
utplsql-travis reviewed Sep 25, 2018
View reviewed changes
Copy link
Copy Markdown

@utplsql-travis utplsql-travis left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

SonarQube analysis found issues:
Bug Bugs: 0
Vulnerability Vulnerabilities: 0
Code Smell Code Smells: 2

See all issues in SonarCloud

utplsql-travis
utplsql-travis reviewed Sep 25, 2018
View reviewed changes
Copy link
Copy Markdown

@utplsql-travis utplsql-travis left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

SonarQube analysis found issues:
Bug Bugs: 0
Vulnerability Vulnerabilities: 0
Code Smell Code Smells: 2

See all issues in SonarCloud

@jgebal
Copy link
Copy Markdown
Member Author

jgebal commented Sep 25, 2018

Before adding cardinality hint (on 12.2) we see estimate of 683K rows and 7863M bytes.
After adding cardinality hint estimate is 300 rows and 3534K bytes.

Old SQL plan (without hints)

SQL_ID  by9zrp3ss2vy8, child number 0
-------------------------------------
with expected_cols as ( select xmltype(:a) as item_data from dual ),    
     actual_cols as ( select xmltype(:b) as item_data from dual ),      
   expected_cols_info as (           select e.*,                  
replace(expected_type,'VARCHAR2','CHAR') expected_type_compare          
   from (                   select rownum expected_pos,                 
         xt.name expected_name,                          xt.type 
expected_type                     from (select ucd.item_data as 
item_data from expected_cols ucd) x,                          xmltable( 
                           '/ROW/*'                            passing 
x.item_data                            columns                          
    name     varchar2(4000)  PATH '@xml_valid_name',                    
          type     varchar2(4000) PATH '/'                          ) 
xt                  ) e         ),         actual_cols_info as (        
   select a.*,                  replace(actual_type,'VARCHAR2','CHAR') 
actual_type_compa
 
Plan hash value: 3270454536
 
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | A-Rows |   A-Time   |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |      1 |        |       |       |  5139K(100)|          |      6 |00:00:00.01 |       |       |          |
|   1 |  SORT ORDER BY                |          |      1 |    683K|  7863M|  5339M|  5139K  (1)| 00:03:21 |      6 |00:00:00.01 |  2048 |  2048 | 2048  (0)|
|*  2 |   VIEW                        |          |      1 |    683K|  7863M|       |  3425K  (1)| 00:02:14 |      6 |00:00:00.01 |       |       |          |
|   3 |    WINDOW SORT                |          |      1 |    683K|  7846M|  5339M|  3425K  (1)| 00:02:14 |     80 |00:00:00.01 | 13312 | 13312 |12288  (0)|
|   4 |     WINDOW SORT               |          |      1 |    683K|  7846M|  5339M|  3425K  (1)| 00:02:14 |     80 |00:00:00.01 | 90112 | 90112 |79872  (0)|
|   5 |      VIEW                     | VW_FOJ_0 |      1 |    683K|  7846M|       |  4718   (1)| 00:00:01 |     80 |00:00:00.01 |       |       |          |
|*  6 |       HASH JOIN FULL OUTER    |          |      1 |    683K|  7846M|    46M|  4718   (1)| 00:00:01 |     80 |00:00:00.01 |  1298K|  1298K| 5815K (0)|
|   7 |        VIEW                   |          |      1 |   8168 |    46M|       |    29   (0)| 00:00:01 |     74 |00:00:00.01 |       |       |          |
|   8 |         VIEW                  |          |      1 |   8168 |    31M|       |    29   (0)| 00:00:01 |     74 |00:00:00.01 |       |       |          |
|   9 |          COUNT                |          |      1 |        |       |       |            |          |     74 |00:00:00.01 |       |       |          |
|  10 |           XMLTABLE EVALUATION |          |      1 |        |       |       |            |          |     74 |00:00:00.01 |       |       |          |
|  11 |        VIEW                   |          |      1 |   8168 |    46M|       |    29   (0)| 00:00:01 |     80 |00:00:00.01 |       |       |          |
|  12 |         VIEW                  |          |      1 |   8168 |    31M|       |    29   (0)| 00:00:01 |     80 |00:00:00.01 |       |       |          |
|  13 |          COUNT                |          |      1 |        |       |       |            |          |     80 |00:00:00.01 |       |       |          |
|  14 |           XMLTABLE EVALUATION |          |      1 |        |       |       |            |          |     80 |00:00:00.01 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$13      
   2 - SEL$12       / JOINED_COLS@SEL$13
   3 - SEL$12      
   5 - SEL$1        / from$_subquery$_013@SEL$12
   6 - SEL$1       
   7 - SEL$4        / E@SEL$1
   8 - SEL$F3C6CAD4 / E@SEL$4
   9 - SEL$F3C6CAD4
  11 - SEL$8        / A@SEL$1
  12 - SEL$FA3E51C0 / A@SEL$8
  13 - SEL$FA3E51C0
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(("ACTUAL_POS" IS NULL OR "EXPECTED_POS" IS NULL OR "ACTUAL_TYPE_COMPARE"<>"EXPECTED_TYPE_COMPARE" OR ("A_POS_NN"<>"E_POS_NN" AND 
              "EXPECTED_POS"<>"ACTUAL_POS")))
   6 - access("E"."EXPECTED_NAME"="A"."ACTUAL_NAME")

New plan (with hints):

SQL_ID  1r981zttqtzhw, child number 0
-------------------------------------
with expected_cols as ( select xmltype(:a) as item_data from dual ),    
     actual_cols as ( select xmltype(:b) as item_data from dual ),      
   expected_cols_info as (           select /*+ cardinality(e 100) */ 
e.*,                  replace(expected_type,'VARCHAR2','CHAR') 
expected_type_compare             from (                   select 
rownum expected_pos,                          xt.name expected_name,    
                      xt.type expected_type                     from 
(select ucd.item_data as item_data from expected_cols ucd) x,           
               xmltable(                            '/ROW/*'            
                passing x.item_data                            columns  
                            name     varchar2(4000)  PATH 
'@xml_valid_name',                              type     varchar2(4000) 
PATH '/'                          ) xt                  ) e         ),  
       actual_cols_info as (           select /*+ cardinality(a 100) */ 
a.*,                  repl
 
Plan hash value: 3270454536
 
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | A-Rows |   A-Time   |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |      1 |        |       |       |  2322 (100)|          |      6 |00:00:00.01 |       |       |          |
|   1 |  SORT ORDER BY                |          |      1 |    300 |  3534K|  2408K|  2322   (1)| 00:00:01 |      6 |00:00:00.01 |  2048 |  2048 | 2048  (0)|
|*  2 |   VIEW                        |          |      1 |    300 |  3534K|       |  1567   (1)| 00:00:01 |      6 |00:00:00.01 |       |       |          |
|   3 |    WINDOW SORT                |          |      1 |    300 |  3526K|  2408K|  1567   (1)| 00:00:01 |     80 |00:00:00.01 | 13312 | 13312 |12288  (0)|
|   4 |     WINDOW SORT               |          |      1 |    300 |  3526K|  2408K|  1567   (1)| 00:00:01 |     80 |00:00:00.01 | 90112 | 90112 |79872  (0)|
|   5 |      VIEW                     | VW_FOJ_0 |      1 |    300 |  3526K|       |    58   (0)| 00:00:01 |     80 |00:00:00.01 |       |       |          |
|*  6 |       HASH JOIN FULL OUTER    |          |      1 |    300 |  3526K|       |    58   (0)| 00:00:01 |     80 |00:00:00.01 |  1298K|  1298K| 1352K (0)|
|   7 |        VIEW                   |          |      1 |    100 |   587K|       |    29   (0)| 00:00:01 |     74 |00:00:00.01 |       |       |          |
|   8 |         VIEW                  |          |      1 |    100 |   392K|       |    29   (0)| 00:00:01 |     74 |00:00:00.01 |       |       |          |
|   9 |          COUNT                |          |      1 |        |       |       |            |          |     74 |00:00:00.01 |       |       |          |
|  10 |           XMLTABLE EVALUATION |          |      1 |        |       |       |            |          |     74 |00:00:00.01 |       |       |          |
|  11 |        VIEW                   |          |      1 |    100 |   587K|       |    29   (0)| 00:00:01 |     80 |00:00:00.01 |       |       |          |
|  12 |         VIEW                  |          |      1 |    100 |   392K|       |    29   (0)| 00:00:01 |     80 |00:00:00.01 |       |       |          |
|  13 |          COUNT                |          |      1 |        |       |       |            |          |     80 |00:00:00.01 |       |       |          |
|  14 |           XMLTABLE EVALUATION |          |      1 |        |       |       |            |          |     80 |00:00:00.01 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$13      
   2 - SEL$12       / JOINED_COLS@SEL$13
   3 - SEL$12      
   5 - SEL$1        / from$_subquery$_013@SEL$12
   6 - SEL$1       
   7 - SEL$4        / E@SEL$1
   8 - SEL$F3C6CAD4 / E@SEL$4
   9 - SEL$F3C6CAD4
  11 - SEL$8        / A@SEL$1
  12 - SEL$FA3E51C0 / A@SEL$8
  13 - SEL$FA3E51C0
 

Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(("ACTUAL_POS" IS NULL OR "EXPECTED_POS" IS NULL OR "ACTUAL_TYPE_COMPARE"<>"EXPECTED_TYPE_COMPARE" OR ("A_POS_NN"<>"E_POS_NN" AND 
              "EXPECTED_POS"<>"ACTUAL_POS")))
   6 - access("E"."EXPECTED_NAME"="A"."ACTUAL_NAME")

@jgebal jgebal merged commit 0a95836 into develop Sep 26, 2018
@jgebal jgebal deleted the feature/ut_cursor_compare_cardinality branch October 19, 2018 09:36
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

1 more reviewer

@utplsql-travis utplsql-travis utplsql-travis left review comments

Reviewers whose approvals may not affect merge requirements

Assignees

No one assigned

Labels

None yet

Projects

None yet

Milestone

v3.1.3

Development

Successfully merging this pull request may close these issues.

2 participants

@jgebal @utplsql-travis