Skip to Main Content
Forums

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Roguewave errors with rwtools7_std option

807575Apr 12 2004 — edited May 7 2004
Hi

I am trying to use Roguewave tools with Sunone Studio 8 compiler and the flag rwtools7_std but I get the following errors. Any info on this will be appreciated.

Regards
Balaji
-------------------------------

Compiler: Sunone Studio 8.
Command Used to Compile: CC -library=rwtools7_std stream.cpp
OS: Solaris 8

stream.cpp
========

#include <rw/cstring.h>
#include <rw/tvhdict.h>

int main()
{

RWTValHashDictionary<RWCString, unsigned int> gdmoNameHash(RWCString::hash);


}
==========================================================
Errors

"stream.cpp", line 7: Error: Too few arguments for template RWTValHashMap.
"/opt/external/studio8/SUNWspro/prod/include/CC/rw7/rw/tvhdict.h", line 64: Error: The base "RW_VMapAssoc<rw_hashmap<RWCString, T, H, EQ, std::allocator<RWCString>>, RWTValHashMap<RWCString, T, H, EQ>, RWCString, T>" must be a previously defined class or struct.
"stream.cpp", line 7: Where: While specializing "RWTValHashMap<RWCString>".
"stream.cpp", line 7: Where: Specialized in non-template code.
"/opt/external/studio8/SUNWspro/prod/include/CC/rw7/rw/tvhdict.h", line 99: Error: RW_VMapAssoc<rw_hashmap<RWCString, T, H, EQ, std::allocator<RWCString>>, RWTValHashMap<RWCString, T, H, EQ>, RWCString, T> is not a base class of RWTValHashMap<RWCString>.
"stream.cpp", line 7: Where: While specializing "RWTValHashMap<RWCString>".
"stream.cpp", line 7: Where: Specialized in non-template code.
"/opt/external/studio8/SUNWspro/prod/include/CC/rw7/rw/tvhdict.h", line 99: Error: apply is not a member of RW_VMapAssoc<rw_hashmap<RWCString, T, H, EQ, std::allocator<RWCString>>, RWTValHashMap<RWCString, T, H, EQ>, RWCString, T>.
"stream.cpp", line 7: Where: While specializing "RWTValHashMap<RWCString>".
"stream.cpp", line 7: Where: Specialized in non-template code.
"/opt/external/studio8/SUNWspro/prod/include/CC/rw7/rw/tvhdict.h", line 100: Error: RW_VMapAssoc<rw_hashmap<RWCString, T, H, EQ, std::allocator<RWCString>>, RWTValHashMap<RWCString, T, H, EQ>, RWCString, T> is not a base class of RWTValHashMap<RWCString>.
"stream.cpp", line 7: Where: While specializing "RWTValHashMap<RWCString>".
"stream.cpp", line 7: Where: Specialized in non-template code.
"/opt/external/studio8/SUNWspro/prod/include/CC/rw7/rw/tvhdict.h", line 100: Error: applyToKeyAndValue is not a member of RW_VMapAssoc<rw_hashmap<RWCString, T, H, EQ, std::allocator<RWCString>>, RWTValHashMap<RWCString, T, H, EQ>, RWCString, T>.
"stream.cpp", line 7: Where: While specializing "RWTValHashMap<RWCString>".
"stream.cpp", line 7: Where: Specialized in non-template code.
"/opt/external/studio8/SUNWspro/prod/include/CC/rw7/rw/tvhdict.h", line 101: Error: RW_VMapAssoc<rw_hashmap<RWCString, T, H, EQ, std::allocator<RWCString>>, RWTValHashMap<RWCString, T, H, EQ>, RWCString, T> is not a base class of RWTValHashMap<RWCString>.
"stream.cpp", line 7: Where: While specializing "RWTValHashMap<RWCString>".
"stream.cpp", line 7: Where: Specialized in non-template code.
"/opt/external/studio8/SUNWspro/prod/include/CC/rw7/rw/tvhdict.h", line 101: Error: begin is not a member of RW_VMapAssoc<rw_hashmap<RWCString, T, H, EQ, std::allocator<RWCString>>, RWTValHashMap<RWCString, T, H, EQ>, RWCString, T>.
"stream.cpp", line 7: Where: While specializing "RWTValHashMap<RWCString>".
"stream.cpp", line 7: Where: Specialized in non-template code.
"/opt/external/studio8/SUNWspro/prod/include/CC/rw7/rw/tvhdict.h", line 102: Error: RW_VMapAssoc<rw_hashmap<RWCString, T, H, EQ, std::allocator<RWCString>>, RWTValHashMap<RWCString, T, H, EQ>, RWCString, T> is not a base class of RWTValHashMap<RWCString>.
"stream.cpp", line 7: Where: While specializing "RWTValHashMap<RWCString>".
"stream.cpp", line 7: Where: Specialized in non-template code.
"/opt/external/studio8/SUNWspro/prod/include/CC/rw7/rw/tvhdict.h", line 102: Error: end is not a member of RW_VMapAssoc<rw_hashmap<RWCString, T, H, EQ, std::allocator<RWCString>>, RWTValHashMap<RWCString, T, H, EQ>, RWCString, T>.
"stream.cpp", line 7: Where: While specializing "RWTValHashMap<RWCString>".
"stream.cpp", line 7: Where: Specialized in non-template code.
"/opt/external/studio8/SUNWspro/prod/include/CC/rw7/rw/tvhdict.h", line 103: Error: RW_VMapAssoc<rw_hashmap<RWCString, T, H, EQ, std::allocator<RWCString>>, RWTValHashMap<RWCString, T, H, EQ>, RWCString, T> is not a base class of RWTValHashMap<RWCString>.
"stream.cpp", line 7: Where: While specializing "RWTValHashMap<RWCString>".
"stream.cpp", line 7: Where: Specialized in non-template code.
"/opt/external/studio8/SUNWspro/prod/include/CC/rw7/rw/tvhdict.h", line 103: Error: isEmpty is not a member of RW_VMapAssoc<rw_hashmap<RWCString, T, H, EQ, std::allocator<RWCString>>, RWTValHashMap<RWCString, T, H, EQ>, RWCString, T>.
"stream.cpp", line 7: Where: While specializing "RWTValHashMap<RWCString>".
"stream.cpp", line 7: Where: Specialized in non-template code.
"/opt/external/studio8/SUNWspro/prod/include/CC/rw7/rw/tvhdict.h", line 104: Error: RW_VMapAssoc<rw_hashmap<RWCString, T, H, EQ, std::allocator<RWCString>>, RWTValHashMap<RWCString, T, H, EQ>, RWCString, T> is not a base class of RWTValHashMap<RWCString>.
"stream.cpp", line 7: Where: While specializing "RWTValHashMap<RWCString>".
"stream.cpp", line 7: Where: Specialized in non-template code.
"/opt/external/studio8/SUNWspro/prod/include/CC/rw7/rw/tvhdict.h", line 104: Error: entries is not a member of RW_VMapAssoc<rw_hashmap<RWCString, T, H, EQ, std::allocator<RWCString>>, RWTValHashMap<RWCString, T, H, EQ>, RWCString, T>.
"stream.cpp", line 7: Where: While specializing "RWTValHashMap<RWCString>".
"stream.cpp", line 7: Where: Specialized in non-template code.
"/opt/external/studio8/SUNWspro/prod/include/CC/rw7/rw/tvhdict.h", line 105: Error: RW_VMapAssoc<rw_hashmap<RWCString, T, H, EQ, std::allocator<RWCString>>, RWTValHashMap<RWCString, T, H, EQ>, RWCString, T> is not a base class of RWTValHashMap<RWCString>.
"stream.cpp", line 7: Where: While specializing "RWTValHashMap<RWCString>".
"stream.cpp", line 7: Where: Specialized in non-template code.
"/opt/external/studio8/SUNWspro/prod/include/CC/rw7/rw/tvhdict.h", line 105: Error: operator == is not a member of RW_VMapAssoc<rw_hashmap<RWCString, T, H, EQ, std::allocator<RWCString>>, RWTValHashMap<RWCString, T, H, EQ>, RWCString, T>.
"stream.cpp", line 7: Where: While specializing "RWTValHashMap<RWCString>".
"stream.cpp", line 7: Where: Specialized in non-template code.
"/opt/external/studio8/SUNWspro/prod/include/CC/rw7/rw/tvhdict.h", line 106: Error: RW_VMapAssoc<rw_hashmap<RWCString, T, H, EQ, std::allocator<RWCString>>, RWTValHashMap<RWCString, T, H, EQ>, RWCString, T> is not a base class of RWTValHashMap<RWCString>.
"stream.cpp", line 7: Where: While specializing "RWTValHashMap<RWCString>".
"stream.cpp", line 7: Where: Specialized in non-template code.
"/opt/external/studio8/SUNWspro/prod/include/CC/rw7/rw/tvhdict.h", line 106: Error: operator<is not a member of RW_VMapAssoc<rw_hashmap<RWCString, T, H, EQ, std::allocator<RWCString>>, RWTValHashMap<RWCString, T, H, EQ>, RWCString, T>.
"stream.cpp", line 7: Where: While specializing "RWTValHashMap<RWCString>".
"stream.cpp", line 7: Where: Specialized in non-template code.
"/opt/external/studio8/SUNWspro/prod/include/CC/rw7/rw/tvhdict.h", line 108: Error: RW_VMapAssoc<rw_hashmap<RWCString, T, H, EQ, std::allocator<RWCString>>, RWTValHashMap<RWCString, T, H, EQ>, RWCString, T> is not a base class of RWTValHashMap<RWCString>.
"stream.cpp", line 7: Where: While specializing "RWTValHashMap<RWCString>".
"stream.cpp", line 7: Where: Specialized in non-template code.
"/opt/external/studio8/SUNWspro/prod/include/CC/rw7/rw/tvhdict.h", line 108: Error: clear is not a member of RW_VMapAssoc<rw_hashmap<RWCString, T, H, EQ, std::allocator<RWCString>>, RWTValHashMap<RWCString, T, H, EQ>, RWCString, T>.
"stream.cpp", line 7: Where: While specializing "RWTValHashMap<RWCString>".
"stream.cpp", line 7: Where: Specialized in non-template code.
"/opt/external/studio8/SUNWspro/prod/include/CC/rw7/rw/tvhdict.h", line 109: Error: RW_VMapAssoc<rw_hashmap<RWCString, T, H, EQ, std::allocator<RWCString>>, RWTValHashMap<RWCString, T, H, EQ>, RWCString, T> is not a base class of RWTValHashMap<RWCString>.
"stream.cpp", line 7: Where: While specializing "RWTValHashMap<RWCString>".
"stream.cpp", line 7: Where: Specialized in non-template code.
"/opt/external/studio8/SUNWspro/prod/include/CC/rw7/rw/tvhdict.h", line 109: Error: contains is not a member of RW_VMapAssoc<rw_hashmap<RWCString, T, H, EQ, std::allocator<RWCString>>, RWTValHashMap<RWCString, T, H, EQ>, RWCString, T>.
"stream.cpp", line 7: Where: While specializing "RWTValHashMap<RWCString>".
"stream.cpp", line 7: Where: Specialized in non-template code.
"/opt/external/studio8/SUNWspro/prod/include/CC/rw7/rw/tvhdict.h", line 110: Error: RW_VMapAssoc<rw_hashmap<RWCString, T, H, EQ, std::allocator<RWCString>>, RWTValHashMap<RWCString, T, H, EQ>, RWCString, T> is not a base class of RWTValHashMap<RWCString>.
"stream.cpp", line 7: Where: While specializing "RWTValHashMap<RWCString>".
"stream.cpp", line 7: Where: Specialized in non-template code.
"/opt/external/studio8/SUNWspro/prod/include/CC/rw7/rw/tvhdict.h", line 110: Error: find is not a member of RW_VMapAssoc<rw_hashmap<RWCString, T, H, EQ, std::allocator<RWCString>>, RWTValHashMap<RWCString, T, H, EQ>, RWCString, T>.
"stream.cpp", line 7: Where: While specializing "RWTValHashMap<RWCString>".
"stream.cpp", line 7: Where: Specialized in non-template code.
"/opt/external/studio8/SUNWspro/prod/include/CC/rw7/rw/tvhdict.h", line 111: Error: RW_VMapAssoc<rw_hashmap<RWCString, T, H, EQ, std::allocator<RWCString>>, RWTValHashMap<RWCString, T, H, EQ>, RWCString, T> is not a base class of RWTValHashMap<RWCString>.
"stream.cpp", line 7: Where: While specializing "RWTValHashMap<RWCString>".
"stream.cpp", line 7: Where: Specialized in non-template code.
Compilation aborted, too many Error messages.

Comments

Luís Gustavo Lira

Hi,

check if the filters related to the join are not generating a Cartesian product.

if you have diagnostic pack try to run a SQL Tuning Advisor for this query, which will give you general recommendations related to indexes, statistics, etc.

If it is possible to optimize by creating indexes, I don't see why not create it.

User_OCZ1T

Thank You . My thought was before going for considering new indexes here in this query may be optimizer is choosing some wrong Order + JOIN mechanism causing this to run longer. And if we can tweak the query to help the optimizer by simplifying the query.

And yes again considering the base transaction tables are experiencing heavy DML and are big in size(~5TB+ but are partitioned though), so we need to evaluate those additionally indexes carefully.

Luís Gustavo Lira

OK, but anyway SQL Tuning Advisor can recommend you a profile for example that will give you a better plan than the current one (be careful that it also recommends profiles to use parallelism that is not always recommended).

Is this a new query or was it already executed and did you notice a performance degradation?

If it is old you can check if the degradation was caused by a change in plan.

evgenyg

Hi

Please correct me if I am wrong:

Currently, in where clause you have: STAGE.PART_DT =TAB1.PART_DT  AND  TAB1.PART_DT = TAB2.PART_DT so you may add STAGE.PART_DT = TAB2.PART_DT

Now to be able to remove TAB1 at all (you saying you not use its data) you need to consider if the following condition is necessary: TAB1.DID = REF2.DID

Then you may remove TAB1 from the query seems it can help..

Regards

User_OCZ1T

Yes that condition is required and apart from those partition key column joins i.e. PART_DT the other condition i.e. "AND TAB1.TXN_ID = TAB2.TXN_ID " is required to filter out records from TAB2. So it look like TAB1 cant be bypassed.

evgenyg

Then, maybe, you can prepare the subset from tab1 that you need for the join and then join only on subset? and/or introduce HASH instead of NL

User_OCZ1T

I think the cause of putting table TAB1 in a nested loop is because the plan_line_id -8 i.e. "MERGE JOIN CARTESIAN" between table STAGE and REF1 , oracle is expecting ~1 rows and that way its putting the FTS of TAB1 in a NESTED LOOP join.

Now i am trying to understand why the merge join Cartesian is yielding 1 row, might be because of some filter on REF1 making it to result ~0 rows which gets rounded up to ~1 by optimizer. And the way table REF1 is used in three different places in the same query might be causing the underestimation of cardinality. Is there a way , i can place table REF1 much better way in this query by tweaking someway which will help optimizer to have correct estimation. REF1 is  s small reference data table holding only ~600 rows.

1 person found this helpful
User_OCZ1T

I tried tweaking the query by evaluating table REF1, REF2 separately using WITH clause + Added Materialize hint to restrict it getting merged with the main query and i am seeing its finishing considerably faster time as compared to earlier even with more data volume.

So my next thought is , as its clear that the way table REF1 and REF2 is used/joined in the query causing optimizer estimating the cardinality wrongly and thus a bad plan(mainly as it puts FTS of table TAB1 in a nested loop), so can i achieve same result without using the hints 'MATERIALIZE' and by some additional tweak or will it require any fixing of statistics by anyway rather simplifying/tweaking the query?

WITH tab as
(   select /*+materialize*/REF1.VAL,REF2.DID
 from REF1, REF2   where REF1.PUSER = 'ZZZ'
      AND REF1.NAME = 'CODE' AND TRUNC (SYSDATE) BETWEEN REF1.SDATE AND REF1.EDATE
       AND ( (   REF1.VAL NOT IN
                (SELECT VAL   FROM REF1      WHERE     PUSER = 'ZZZ' AND NAME = 'YYY' AND TRUNC (SYSDATE) BETWEEN SDATE AND EDATE)
                              OR REF2.CUST_ID IN
                                    (SELECT CUST_ID       FROM CUST_ORG WHERE CUST_ORG.INDICATOR = 'Y'
                                     MINUS
                                     SELECT TO_NUMBER (VAL)   FROM REF1  WHERE     PUSER = 'ZZZ' AND TRUNC (SYSDATE) BETWEEN SDATE AND EDATE
                                     )
             )
            )
)
SELECT
  ....
  FROM STAGE,       TAB1,       TAB2, TAB
 WHERE     STAGE.SID = TAB1.SID  AND STAGE.SBMT_DT =TAB1.PART_DT  AND STAGE.STAT = 'I'   AND STAGE.NAME = 'XXX'
       AND TAB1.TXN_ID = TAB2.TXN_ID  AND TAB1.PART_DT = TAB2.PART_DT   AND TAB2.CODE = TAB.VAL AND TAB1.DID = TAB.DID
       AND EXISTS  (SELECT 1      FROM CONTROLTAB     WHERE     CONTROLTAB.CNTRLID = 999   AND NVL (CONTROLTAB.STATUS, 'F') = 'F')

Global Information
------------------------------
 Status              :  DONE (ALL ROWS)          
 Instance ID         :  4                        
 SQL Execution ID    :  67108864                 
 Execution Started   :  07/09/2020 03:54:05      
 First Refresh Time  :  07/09/2020 03:54:11      
 Last Refresh Time   :  07/09/2020 03:56:02      
 Duration            :  117s                     
 Module/Action       :  SQL*Plus/-               
 Program             :  sqlplus.exe              
 Fetch Calls         :  186                      
Global Stats
===================================================================================================
| Elapsed |   Cpu   |    IO    | Application | Cluster  | Fetch | Buffer | Read | Read  |  Cell   |
| Time(s) | Time(s) | Waits(s) |  Waits(s)   | Waits(s) | Calls |  Gets  | Reqs | Bytes | Offload |
===================================================================================================
|     105 |      78 |       27 |        0.00 |     0.47 |   186 |    17M |   1M |  92GB |  80.58% |
===================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=39601017)
============================================================================================================================================================================================================================
| Id |               Operation                |            Name             |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Read  | Read  |  Cell   |  Mem  | Activity |            Activity Detail             |
|    |                                        |                             | (Estim) |      | Active(s) | Active |       | (Actual) | Reqs  | Bytes | Offload | (Max) |   (%)    |              (# samples)               |
============================================================================================================================================================================================================================
|  0 | SELECT STATEMENT                       |                             |         |      |        94 |    +24 |     1 |     924K |       |       |         |       |     3.96 | Cpu (1)                                |
|    |                                        |                             |         |      |           |        |       |          |       |       |         |       |          | SQL*Net more data to client (3)        |
|  1 |   TEMP TABLE TRANSFORMATION            |                             |         |      |        94 |    +24 |     1 |     924K |       |       |         |       |          |                                        |
|  2 |    LOAD AS SELECT                      |                             |         |      |         1 |     +6 |     1 |        1 |       |       |         |  276K |          |                                        |
|  3 |     CONCATENATION                      |                             |         |      |         1 |     +6 |     1 |      845 |       |       |         |       |          |                                        |
|  4 |      FILTER                            |                             |         |      |         3 |     +4 |     1 |      845 |       |       |         |       |     0.99 | Cpu (1)                                |
|  5 |       MERGE JOIN CARTESIAN             |                             |   12361 | 1225 |         1 |     +6 |     1 |       2M |       |       |         |       |          |                                        |
|  6 |        TABLE ACCESS BY INDEX ROWID     | REF1                        |       1 |    3 |         1 |     +6 |     1 |        1 |       |       |         |       |          |                                        |
|  7 |         INDEX RANGE SCAN               | REF1_PK                     |       1 |    2 |         1 |     +6 |     1 |        1 |       |       |         |       |          |                                        |
|  8 |        BUFFER SORT                     |                             |      2M | 1222 |         1 |     +6 |     1 |       2M |       |       |         |   64M |          |                                        |
|  9 |         INDEX STORAGE FAST FULL SCAN   | MTD_PK                      |      2M | 1222 |         1 |     +6 |     1 |       2M |       |       |         |       |          |                                        |
| 10 |       MINUS                            |                             |         |      |         1 |     +6 |  861K |       17 |       |       |         |       |          |                                        |
| 11 |        TABLE ACCESS BY INDEX ROWID     | CUST_ORG                    |       1 |    3 |         1 |     +6 |  861K |       36 |       |       |         |       |          |                                        |
| 12 |         INDEX UNIQUE SCAN              | MC_PK                       |       1 |    2 |         1 |     +6 |  861K |     861K |       |       |         |       |          |                                        |
| 13 |        SORT UNIQUE NOSORT              |                             |       1 |    4 |         1 |     +6 |  861K |       23 |       |       |         |       |          |                                        |
| 14 |         TABLE ACCESS BY INDEX ROWID    | REF1                        |       1 |    3 |         1 |     +6 |  861K |       23 |       |       |         |       |          |                                        |
| 15 |          INDEX RANGE SCAN              | REF1_PK                     |       1 |    2 |         6 |     +1 |  861K |       23 |       |       |         |       |     3.96 | Cpu (4)                                |
| 16 |      FILTER                            |                             |         |      |           |        |     1 |          |       |       |         |       |          |                                        |
| 17 |       MERGE JOIN CARTESIAN             |                             |     618 | 1225 |           |        |     1 |          |       |       |         |       |          |                                        |
| 18 |        TABLE ACCESS BY INDEX ROWID     | REF1                        |       1 |    3 |           |        |     1 |          |       |       |         |       |          |                                        |
| 19 |         INDEX RANGE SCAN               | REF1_PK                     |       1 |    2 |           |        |     1 |          |       |       |         |       |          |                                        |
| 20 |          TABLE ACCESS BY INDEX ROWID   | REF1                        |       1 |    3 |         1 |     +6 |     1 |        1 |       |       |         |       |          |                                        |
| 21 |           INDEX RANGE SCAN             | REF1_PK                     |       1 |    2 |         1 |     +6 |     1 |        1 |       |       |         |       |          |                                        |
| 22 |        BUFFER SORT                     |                             |      2M | 1222 |           |        |       |          |       |       |         |       |          |                                        |
| 23 |         INDEX STORAGE FAST FULL SCAN   | MTD_PK                      |      2M | 1222 |           |        |       |          |       |       |         |       |          |                                        |
| 24 |       MINUS                            |                             |         |      |         1 |     +6 |  861K |       17 |       |       |         |       |          |                                        |
| 25 |        TABLE ACCESS BY INDEX ROWID     | CUST_ORG                    |       1 |    3 |         1 |     +6 |  861K |       36 |       |       |         |       |          |                                        |
| 26 |         INDEX UNIQUE SCAN              | MC_PK                       |       1 |    2 |         1 |     +6 |  861K |     861K |       |       |         |       |          |                                        |
| 27 |        SORT UNIQUE NOSORT              |                             |       1 |    4 |         1 |     +6 |  861K |       23 |       |       |         |       |          |                                        |
| 28 |         TABLE ACCESS BY INDEX ROWID    | REF1                        |       1 |    3 |         1 |     +6 |  861K |       23 |       |       |         |       |          |                                        |
| 29 |          INDEX RANGE SCAN              | REF1_PK                     |       1 |    2 |         1 |     +6 |  861K |       23 |       |       |         |       |          |                                        |
| 30 |    FILTER                              |                             |         |      |        94 |    +24 |     1 |     924K |       |       |         |       |          |                                        |
| 31 |     NESTED LOOPS                       |                             |       1 |   6M |        94 |    +24 |     1 |     924K |       |       |         |       |     0.99 | Cpu (1)                                |
| 32 |      NESTED LOOPS                      |                             |    1826 |   6M |        94 |    +24 |     1 |     924K |       |       |         |       |     0.99 | Cpu (1)                                |
| 33 |       HASH JOIN                        |                             |      11 |   6M |       112 |     +6 |     1 |       1M |       |       |         |  104M |    22.77 | Cpu (23)                               |
| 34 |        JOIN FILTER CREATE              | :BF0001                     |     500 |  659 |         1 |     +6 |     1 |     845K |       |       |         |       |          |                                        |
| 35 |         PART JOIN FILTER CREATE        | :BF0000                     |     500 |  659 |         1 |     +6 |     1 |     845K |       |       |         |       |          |                                        |
| 36 |          MERGE JOIN CARTESIAN          |                             |     500 |  659 |         1 |     +6 |     1 |     845K |       |       |         |       |          |                                        |
| 37 |           VIEW                         |                             |       1 |    2 |         1 |     +6 |     1 |      845 |       |       |         |       |          |                                        |
| 38 |            TABLE ACCESS STORAGE FULL   | SYS_TEMP_0FDA48F5B_58EE133C |       1 |    2 |         1 |     +6 |     1 |      845 |       |       |         |       |          |                                        |
| 39 |           BUFFER SORT                  |                             |     500 |  659 |         1 |     +6 |   845 |     845K |       |       |         |  104K |          |                                        |
| 40 |            TABLE ACCESS BY INDEX ROWID | STAGE                       |     500 |  657 |         1 |     +6 |     1 |     1000 |       |       |         |       |          |                                        |
| 41 |             INDEX RANGE SCAN           | STAGE_IDX1                  |    2025 |   28 |         1 |     +6 |     1 |     1000 |       |       |         |       |          |                                        |
| 42 |        JOIN FILTER USE                 | :BF0001                     |    802M |   6M |       112 |     +6 |     1 |     249M |       |       |         |       |          |                                        |
| 43 |         PARTITION RANGE JOIN-FILTER    |                             |    802M |   6M |       112 |     +6 |     1 |     249M |       |       |         |       |          |                                        |
| 44 |          TABLE ACCESS STORAGE FULL     | TAB1                        |    802M |   6M |       112 |     +6 |     1 |     249M | 96540 |  85GB |  87.94% |    7M |     4.95 | Cpu (5)                                |
| 45 |       PARTITION RANGE ITERATOR         |                             |     166 |    3 |        94 |    +24 |    2M |     924K |       |       |         |       |     1.98 | Cpu (2)                                |
| 46 |        INDEX RANGE SCAN                | TAB2_IX1                    |     166 |    3 |        94 |    +24 |    2M |     924K |  862K |   7GB |         |       |    36.63 | gc current block 2-way (1)             |
|    |                                        |                             |         |      |           |        |       |          |       |       |         |       |          | gc current grant busy (1)              |
|    |                                        |                             |         |      |           |        |       |          |       |       |         |       |          | Cpu (20)                               |
|    |                                        |                             |         |      |           |        |       |          |       |       |         |       |          | cell list of blocks physical read (14) |
|    |                                        |                             |         |      |           |        |       |          |       |       |         |       |          | cell single block physical read (1)    |
| 47 |      TABLE ACCESS BY LOCAL INDEX ROWID | TAB2                        |       1 |   12 |        94 |    +24 |    1M |     924K |  150K |   1GB |         |       |    21.78 | Cpu (11)                               |
|    |                                        |                             |         |      |           |        |       |          |       |       |         |       |          | cell list of blocks physical read (4)  |
|    |                                        |                             |         |      |           |        |       |          |       |       |         |       |          | cell single block physical read (7)    |
| 48 |     TABLE ACCESS BY INDEX ROWID        | CONTROLTAB                  |       1 |    1 |         1 |     +6 |     1 |        1 |       |       |         |       |          |                                        |
| 49 |      INDEX UNIQUE SCAN                 | CONTROLTAB_PK               |       1 |      |         1 |     +6 |     1 |        1 |       |       |         |       |          |                                        |
============================================================================================================================================================================================================================
924077 rows selected.
Elapsed: 00:01:57.22
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter( EXISTS ( (SELECT "CUST_ID" FROM "USER1"."CUST_ORG" "CUST_ORG" WHERE "CUST_ID"=:B1 AND "CUST_ORG"."INDICATOR"='Y')MINUS (SELECT TO_NUMBER("VAL") FROM "USER1"."REF1" "REF1" WHERE "PUSER"='ZZZ' AND "EDATE">=TRUNC(SYSDATE@!) AND   TO_NUMBER("VAL")=:B2 AND "SDATE"<=TRUNC(SYSDATE@!))))
   6 - filter("REF1"."EDATE">=TRUNC(SYSDATE@!))
   7 - access("REF1"."PUSER"='ZZZ' AND  "REF1"."NAME"='CODE' AND "REF1"."SDATE"<=TRUNC(SYSDATE@!))
  11 - filter("CUST_ORG"."INDICATOR"='Y')
  12 - access("CUST_ID"=:B1)
  14 - filter("EDATE">=TRUNC(SYSDATE@!))
  15 - access("PUSER"='ZZZ' AND "SDATE"<=TRUNC(SYSDATE@!))
       filter(TO_NUMBER("VAL")=:B1 AND "SDATE"<=TRUNC(SYSDATE@!))
  16 - filter(LNNVL( EXISTS ( (SELECT "CUST_ID" FROM "USER1"."CUST_ORG" "CUST_ORG" WHERE "CUST_ID"=:B1 AND "CUST_ORG"."INDICATOR"='Y')MINUS (SELECT TO_NUMBER("VAL") FROM "USER1"."REF1"   "REF1" WHERE "PUSER"='ZZZ' AND "EDATE">=TRUNC(SYSDATE@!) AND    TO_NUMBER("VAL")=:B2 AND "SDATE"<=TRUNC(SYSDATE@!)))))
  18 - filter("REF1"."EDATE">=TRUNC(SYSDATE@!))
  19 - access("REF1"."PUSER"='ZZZ' AND  "REF1"."NAME"='CODE' AND "REF1"."SDATE"<=TRUNC(SYSDATE@!))
       filter( NOT EXISTS (SELECT 0 FROM "USER1"."REF1" "REF1" WHERE "SDATE"<=TRUNC(SYSDATE@!) AND "NAME"='YYY' AND "PUSER"='ZZZ'   AND "EDATE">=TRUNC(SYSDATE@!) AND "VAL"=:B1))
  20 - filter("EDATE">=TRUNC(SYSDATE@!))
  21 - access("PUSER"='ZZZ' AND "NAME"='YYY' AND "VAL"=:B1        AND "SDATE"<=TRUNC(SYSDATE@!))
       filter("VAL"=:B1)
  25 - filter("CUST_ORG"."INDICATOR"='Y')
  26 - access("CUST_ID"=:B1)
  28 - filter("EDATE">=TRUNC(SYSDATE@!))
  29 - access("PUSER"='ZZZ' AND "SDATE"<=TRUNC(SYSDATE@!))
       filter(TO_NUMBER("VAL")=:B1 AND "SDATE"<=TRUNC(SYSDATE@!))
  30 - filter( EXISTS (SELECT 0 FROM "TH_STG_PROD"."CONTROLTAB" "CONTROLTAB" WHERE    "CONTROLTAB"."CNTRLID"=999 AND NVL("CONTROLTAB"."STATUS",'F')='F'))
  33 - access("STAGE"."SBMT_DT"="TAB1"."PART_DT" AND "STAGE"."SID"="TAB1"."SID" AND "TAB1"."DID"="TAB"."DID")
  41 - access("STAGE"."NAME"='ZZZ' AND  "STAGE"."STAT"='I')
  44 - storage(SYS_OP_BLOOM_FILTER(:BF0001,"TAB1"."SID","TAB1"."PART_DT","TAB1"."DID"))
       filter(SYS_OP_BLOOM_FILTER(:BF0001,"TAB1"."SID","TAB1"."PART_DT","TAB1"."DID"))
  46 - access("TAB1"."TXN_ID"="TAB2"."TXN_ID" AND  "TAB2"."CODE"="TAB"."VAL")
  47 - filter("TAB1"."PART_DT"="TAB2"."PART_DT")
  48 - filter(NVL("CONTROLTAB"."STATUS",'F')='F')
  49 - access("CONTROLTAB"."CNTRLID"=999)
JohnWatson2

If you add an ORDER BY to your CTE, that should force Oracle to materialize it.

Jonathan Lewis
Answer 
933257 wrote:
I think the cause of putting table TAB1 in a nested loop is because the plan_line_id -8 i.e. "MERGE JOIN CARTESIAN" between table STAGE and REF1 , oracle is expecting ~1 rows and that way its putting the FTS of TAB1 in a NESTED LOOP join.
Now i am trying to understand why the merge join Cartesian is yielding 1 row, might be because of some filter on REF1 making it to result ~0 rows which gets rounded up to ~1 by optimizer. And the way table REF1 is used in three different places in the same query might be causing the underestimation of cardinality. Is there a way , i can place table REF1 much better way in this query by tweaking someway which will help optimizer to have correct estimation. REF1 is s small reference data table holding only ~600 rows.

Good inference.

Note the predicates on the two objects feeding that MJC - we can compare actuals and estimates and see that the STAGE estimate as out by a factor of about 12, and the REF1 estimate must be very much less than 1 to give you a product where (rounded) 1 * 84 = 1.

  1.   10 - access("REF1"."PUSER"='XXX' AND  "REF1"."NAME"='CODE' AND "REF1"."SDATE"<=TRUNC(SYSDATE@!)) 
  2.   13 - access("STAGE"."NAME"='XXX' AND "STAGE"."STAT"='I')

I suggest you create extended stats on ref1 (puser, name) - but make sure that 'XXX' and 'CODE' are NOT out of range for the individual columns - this may address the estimate < 1.

Also (name, stat) on stage may give you a better estimate closer to 1,000.  Possibly, though, you may need a  histogram on STAT (and also on (name, stat)) - that's a guess based on the column name. ("State/Status"? => small number of distinct values with extreme skew).

Regards

Jonathan Lewis

Marked as Answer by User_OCZ1T · Sep 27 2020
User_OCZ1T

You are spot on.

There does exists frequency histogram on column NAME(having 14 distinct value) and STAT(having 7 distinct values) of table STAGE. Also there already exists a frequency histogram on column PUSER and height balanced histogram on column NAME of table REF1. But still they were not helping the existing query.

By creating a column group on both on ref1 (puser, name) and stage(name, stat)  with histogram for both i.e. 'FOR COLUMNS SIZE 254'. The merge Cartesian removed automatically from the path by optimizer and its doing a hash join now with TAB1 finishing the query in quick time.

Jonathan Lewis

I've written up a blog note giving some explanation of the suggestions I made: https://jonathanlewis.wordpress.com/2020/07/09/execution-plans-6/

Regards

Jonathan Lewis

1 person found this helpful
1 - 12
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jun 4 2004
Added on Apr 12 2004
#developer-studio, #developer-studio-c-c-fortran-compilers
5 comments
305 views