Skip to Main Content
Forums

Oracle Database Discussions

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!

Can I delete $ORACLE_HOME/md/property_graph/lib/log4j-core-2.9.0.jar file?

We have a bit of a predicament, we are on 18c on Oracle Linux 7 and seems that Oracle will not be releasing any new patches for this release. We have a plan to upgrade to 19c, but that will take a few months to accomplish. Security scans flagged $ORACLE_HOME/md/property_graph/lib/log4j-core-2.9.0.jar file as a vulnerability with the recent announcement from apache. $ORACLE_HOME/md/... directory seems to be related to spatial or locator options, we don't use either one. Can we just delete this file? I've got a SR open with Oracle support, but they are just pointing to the "Apache Log4j Security Alert CVE-2021-44228 Products and Versions ( Doc ID 2827611.1 )" document. It states that db is not affected by this vulnerability, but security tools don't like the presence of that file.
This post has been answered by user13297735 on Dec 16 2021
Jump to Answer

Comments

Dom Brooks

You're doing a large amount of work filtering data from GL_DAILY_RATES presumably largely because you're applying a function to the date column.

The name of the column suggests it's already a date.

TO_DATE usage on a DATE would be wrong.

TO_DATE usage without a format mask would also be wrong.

AND             to_date(gdr.conversion_date) = to_date(rctd.gl_date)

|  41 |                TABLE ACCESS BY INDEX ROWID BATCHED         | GL_DAILY_RATES               |  33459 |      1 |  21808 |00:12:44.59 |    1838K|  
|* 42 |                 INDEX RANGE SCAN                           | GL_DAILY_RATES_U1            |  33459 |      1 |  21808 |00:13:08.16 |    1837K| 

  42 - access("GDR"."FROM_CURRENCY"="RCT"."INVOICE_CURRENCY_CODE" AND "GDR"."TO_CURRENCY"="GLL"."CURRENCY_CODE" AND   
              "GDR"."CONVERSION_TYPE"='Corporate')  
       filter(("GDR"."CONVERSION_TYPE"='Corporate' AND TO_DATE(INTERNAL_FUNCTION("GDR"."CONVERSION_DATE"))=TO_DATE(INTERNAL_FUNCTION("RCTD"."  
              GL_DATE")))) 

So, you use access predicates on the index to get all these dates matching from_currency, to_currency and conversion_type - I imagine that could be a lot - and then you have to throw away all those dates you're not interested in AFTER you've applied a function to the date.

I presume the index is on the unadulterated CONVERSION_DATE field therefore you have to modify your SQL logic to make the most of that and question why that TO_DATE function is erroneously being applied there.

Assuming that both these columns are both dates then do either of them have a non-"zero" time portion.

If not then you don't need any function.

AND             gdr.conversion_date = rctd.gl_date

If they do have times then you're probably after either BETWEEN or >= and <

  AND             gdr.conversion_date >= trunc(rctd.gl_date)
  AND             gdr.conversion_date < trunc(rctd.gl_date)+1
3 people found this helpful
Rajesh123

Thank you,  let me try without using to_date and let you know.

Mark D Powell

Rajesh123, I see another potential logic problem in the SQL.  If the author is trying to compare against all days in January then I think the BETWEEN is written wrong:

- -

" rctd.gl_date BETWEEN To_date ('01-JAN-2018', 'DD-MON-YYYY') AND  To_date ('31-JAN-2018', 'DD-MON-YYYY')  "

- -

The end date would default to midnight on the 31 and what is really desired is < 1-Feb-2018.  Either the time 23:59:59 needs to be provided as part of the end date test or as is my preference the code should be rewritten as

  rctd.gl_date >= To_date ('01-JAN-2018', 'DD-MON-YYYY') and rctd.gl_date < to_date(01-FEB-2018','DD-MON-YYYY')

- -

HTH -- Mark D Powell --

1 person found this helpful
JohnWatson2

You are projecting six (six!) scalar subqueries. You could rewrite them to a join to just one subquery. Something like

select

sum(case line_type='LINE' then extended_amount else 0 end) inv_net_amount,

sum(case line_type='TAX' then extended_amount else 0 end) inv_tax_amount,

and so on.

1 person found this helpful
AndrewSayer 
JohnWatson2 wrote:
You are projecting six (six!) scalar subqueries. You could rewrite them to a join to just one subquery. Something like
select
sum(case line_type='LINE' then extended_amount else 0 end) inv_net_amount,
sum(case line_type='TAX' then extended_amount else 0 end) inv_tax_amount,
and so on.

You missed the union all, there's 12!

But we can see from the row source execution statistics that Oracle spent a grand total of 4 seconds doing this. Also, since 12c Oracle has been able to unnest scalar subqueries itself, it has in this circumstance (which is why we see the VW_SSQ_ "views" in the plan) but this is quite limited and obviously didn't manage to see that they all share similar filters with slight modifications.

I think Dom is pointing in the right direction.

2 people found this helpful
Jonathan Lewis

I agree;

And while we can't always trust the timing information on rowsource execution stats when the number of times a line runs is high and it's workload is small, there is some confirmation in line 42 itself that the work done is high:  33,000 starts to do a range scan leading to 1.8M buffer gets means the average range scan is about 60 blocks - which could be a very large number of index entries examined (with an expensive double-conversion of a data column) to find an average of less than one row.

Regards

Jonathan Lewis

Jonathan Lewis

Rajesh,

Your code and plan were long enough to offer a few interesting points, but short enough to make it possible to say something fairly constructive in a blog note - so I've written about it on my blog: https://jonathanlewis.wordpress.com/2018/09/30/case-study-2/

You might be particularly interested in the closing warning - the result you get from your query may depend on the nls_date_format set by the user.

Regards

Jonathan Lewis

1 person found this helpful
Rajesh123

Thank you Andrew,

I removed to_date from date columns still same plan it is showing.

AndrewSayer 
Rajesh123 wrote:
Thank you Andrew,
I removed to_date from date columns still same plan it is showing.

Hard to help without seeing the new query and full execution plan

Rajesh123

Thanks a lot excellent explanation.

Jonathan Lewis

Rajesh123,

Thanks.

A point I failed to make in the note (which I have now added) is that getting rid of the double conversion on the gdr.conversion rate may allow Oracle to do a much more precise index range scan - and if the performance is still not good enough the next step would be to see if there is a more precise index (perhaps very similar to the one Oracle is using with the columns in a different order) so that none of the access predicates end up also being filter predicates.

Regards

Jonathan Lewis

Rajesh123

Hi John,

Thanks, i will get wrong output.

for Line Amount is 100, TAX has 0.

pastedImage_0.png

Populating 0 values for LINE type records , but it should be 100

pastedImage_1.png

Thank you

Rajesh123

Hi ,

Here is the latest statistics , taking 7.33 minutes for 5693 rows.

Could you please help me on this?

SELECT /*+ gather_plan_statistics*/ DISTINCT 2,
                rct.org_id, 
                hzp.party_name, 
                hca.account_number, 
                rct.interface_header_attribute1 order_number, 
                rct.customer_trx_id, 
                rct.trx_number, 
                rct.trx_date, 
                rctd.gl_date, 
                rct.creation_date, 
                rctl.line_number, 
                rct.invoice_currency_code inv_currency, 
                sum(case when rctl.line_type='TAX' then rctl.extended_amount else 0 end) inv_tax_amount,  
                gll.currency_code                                    func_currency, 
                Round(sum(case when rctl.line_type='TAX' then rctl.extended_amount else 0 end)*gdr.conversion_rate,2) func_tax_amount,
                glcc.segment1                                                                 company,
                glcc.segment2                                                                 account,
                hg.geography_name                                                             billing_country,
                gdr.conversion_rate 
FROM            apps.hz_parties hzp, 
                apps.hz_cust_accounts hca, 
                apps.ra_customer_trx_all rct, 
                apps.ra_customer_trx_lines_all rctl, 
                apps.ra_cust_trx_line_gl_dist_all rctd, 
                apps.gl_code_combinations_kfv glcc, 
                apps.hz_cust_site_uses_all hcsua, 
                apps.hz_cust_acct_sites_all hcasa, 
                apps.hz_party_sites hps, 
                apps.hz_locations hl, 
                apps.hz_geographies hg, 
                apps.gl_ledgers gll, 
                apps.gl_daily_rates gdr 
WHERE           hzp.party_id = hca.party_id 
AND             hca.cust_account_id = rct.bill_to_customer_id
AND             hca.cust_account_id = hcasa.cust_account_id
AND             rct.customer_trx_id = rctl.customer_trx_id 
AND             rctl.customer_trx_line_id = rctd.customer_trx_line_id 
AND             glcc.code_combination_id = rctd.code_combination_id 
AND             rct.bill_to_site_use_id = hcsua.site_use_id 
AND             hcsua.cust_acct_site_id = hcasa.cust_acct_site_id 
AND             hcasa.party_site_id = hps.party_site_id 
AND             hps.location_id = hl.location_id 
AND             hl.country = hg.country_code 
AND             hg.geography_type = 'COUNTRY' 
AND             rctl.line_type = 'TAX'
AND             gll.ledger_id = rct.set_of_books_id 
AND             gdr.from_currency = rct.invoice_currency_code 
AND             gdr.to_currency = gll.currency_code 
AND             (gdr.conversion_date) = (rctd.gl_date) 
AND             gdr.conversion_type = 'Corporate' 
AND             rctd.gl_date BETWEEN To_date ('01-JAN-2018', 'DD-MON-YYYY') AND  To_date ('10-JAN-2018', 'DD-MON-YYYY')
AND             glcc.segment1 = '2600'
AND             glcc.segment2 = '206911'
GROUP BY        hzp.party_name, 
                hca.account_number, 
                rct.interface_header_attribute1, 
                rct.trx_number, 
                rct.trx_date, 
                rct.creation_date, 
                rctl.line_number, 
                rctl.unit_selling_price, 
                rct.org_id, 
                rctd.gl_date, 
                rct.customer_trx_id, 
                glcc.segment1,
                glcc.segment2, 
                hg.geography_name, 
                rct.invoice_currency_code, 
                gll.currency_code, 
                gdr.conversion_rate;
PLAN_TABLE_OUTPUT
SQL_ID  7pz8x347z52k6, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics*/ DISTINCT 2,                 
rct.org_id,                  hzp.party_name,                  
hca.account_number,                  rct.interface_header_attribute1 
order_number,                  rct.customer_trx_id,                  
rct.trx_number,                  rct.trx_date,                  
rctd.gl_date,                  rct.creation_date,                  
rctl.line_number,                  rct.invoice_currency_code 
inv_currency,                  sum(case when rctl.line_type='TAX' then 
rctl.extended_amount else 0 end) inv_tax_amount,                   
gll.currency_code                                    func_currency,     
             Round(sum(case when rctl.line_type='TAX' then 
rctl.extended_amount else 0 end)*gdr.conversion_rate,2) 
func_tax_amount,                 glcc.segment1                          
                                       company,                 
glcc.segment2                                                           
      accou
Plan hash value: 1975678822
------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                          | Name                         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                   |                              |      1 |        |   5693 |00:07:33.87 |      37M|  20179 |
|   1 |  HASH UNIQUE                                       |                              |      1 |      1 |   5693 |00:07:33.87 |      37M|  20179 |
|   2 |   HASH GROUP BY                                    |                              |      1 |      1 |   5693 |00:07:33.85 |      37M|  20179 |
|   3 |    NESTED LOOPS                                    |                              |      1 |        |   6104 |00:07:33.80 |      37M|  20179 |
|   4 |     NESTED LOOPS                                   |                              |      1 |      1 |   6104 |00:07:31.11 |      37M|  16384 |
|*  5 |      HASH JOIN                                     |                              |      1 |      1 |   6104 |00:07:29.13 |      37M|  13648 |
|   6 |       NESTED LOOPS                                 |                              |      1 |      1 |   6104 |00:07:29.05 |      37M|  13638 |
|   7 |        NESTED LOOPS                                |                              |      1 |      1 |   6104 |00:07:26.75 |      37M|  10466 |
|   8 |         NESTED LOOPS                               |                              |      1 |      1 |   6104 |00:07:24.71 |      37M|   7667 |
|   9 |          NESTED LOOPS                              |                              |      1 |      1 |   6104 |00:07:22.76 |      37M|   5007 |
|  10 |           NESTED LOOPS                             |                              |      1 |      1 |   6104 |00:07:20.58 |      37M|   2003 |
|* 11 |            HASH JOIN                               |                              |      1 |      1 |   6104 |00:07:19.09 |      37M|      2 |
|  12 |             NESTED LOOPS                           |                              |      1 |      1 |    411K|00:07:16.44 |      37M|      2 |
|  13 |              NESTED LOOPS                          |                              |      1 |      2 |     18M|00:04:09.60 |      18M|      2 |
|* 14 |               HASH JOIN                            |                              |      1 |      3 |     18M|00:00:22.50 |     156K|      2 |
|  15 |                TABLE ACCESS BY INDEX ROWID BATCHED | GL_DAILY_RATES               |      1 |    129 |  19800 |00:00:00.15 |   14981 |      0 |
|* 16 |                 INDEX SKIP SCAN                    | GL_DAILY_RATES_U1            |      1 |    129 |  19800 |00:00:00.10 |    5117 |      0 |
|* 17 |                HASH JOIN                           |                              |      1 |    138 |   9354 |00:00:02.34 |     141K|      2 |
|* 18 |                 TABLE ACCESS BY INDEX ROWID BATCHED| GL_CODE_COMBINATIONS         |      1 |     35 |      1 |00:00:00.01 |     108 |      0 |
|* 19 |                  INDEX RANGE SCAN                  | GL_CODE_COMBINATIONS_N2      |      1 |    499 |     77 |00:00:00.01 |       3 |      0 |
|* 20 |                 TABLE ACCESS BY INDEX ROWID BATCHED| RA_CUST_TRX_LINE_GL_DIST_ALL |      1 |    229K|    370K|00:00:01.48 |     141K|      2 |
|* 21 |                  INDEX RANGE SCAN                  | RA_CUST_TRX_LINE_GL_DIST_N2  |      1 |    256K|    451K|00:00:00.47 |    2763 |      2 |
|* 22 |               TABLE ACCESS BY INDEX ROWID          | RA_CUSTOMER_TRX_LINES_ALL    |     18M|      1 |     18M|00:02:37.71 |      18M|      0 |
|* 23 |                INDEX UNIQUE SCAN                   | RA_CUSTOMER_TRX_LINES_U1     |     18M|      1 |     18M|00:00:53.52 |   58940 |      0 |
|* 24 |              TABLE ACCESS BY INDEX ROWID           | RA_CUSTOMER_TRX_ALL          |     18M|      1 |    411K|00:02:23.39 |      18M|      0 |
|* 25 |               INDEX UNIQUE SCAN                    | RA_CUSTOMER_TRX_U1           |     18M|      1 |     18M|00:00:52.92 |   28380 |      0 |
|  26 |             TABLE ACCESS FULL                      | GL_LEDGERS                   |      1 |      1 |     39 |00:00:00.01 |      15 |      0 |
|  27 |            TABLE ACCESS BY INDEX ROWID             | HZ_CUST_ACCOUNTS             |   6104 |      1 |   6104 |00:00:01.45 |   18161 |   2001 |
|* 28 |             INDEX UNIQUE SCAN                      | HZ_CUST_ACCOUNTS_U1          |   6104 |      1 |   6104 |00:00:00.19 |   12057 |    197 |
|  29 |           TABLE ACCESS BY INDEX ROWID              | HZ_CUST_SITE_USES_ALL        |   6104 |      1 |   6104 |00:00:02.12 |   18196 |   3004 |
|* 30 |            INDEX UNIQUE SCAN                       | HZ_CUST_SITE_USES_U1         |   6104 |      1 |   6104 |00:00:00.56 |   12092 |    759 |
|* 31 |          TABLE ACCESS BY INDEX ROWID               | HZ_CUST_ACCT_SITES_ALL       |   6104 |      1 |   6104 |00:00:01.93 |   17285 |   2660 |
|* 32 |           INDEX UNIQUE SCAN                        | HZ_CUST_ACCT_SITES_U1        |   6104 |      1 |   6104 |00:00:00.56 |   12059 |    767 |
|  33 |         TABLE ACCESS BY INDEX ROWID                | HZ_PARTY_SITES               |   6104 |      1 |   6104 |00:00:02.01 |   18177 |   2799 |
|* 34 |          INDEX UNIQUE SCAN                         | HZ_PARTY_SITES_U1            |   6104 |      1 |   6104 |00:00:00.60 |   12073 |    772 |
|  35 |        TABLE ACCESS BY INDEX ROWID                 | HZ_LOCATIONS                 |   6104 |      1 |   6104 |00:00:02.27 |   18182 |   3172 |
|* 36 |         INDEX UNIQUE SCAN                          | HZ_LOCATIONS_U1              |   6104 |      1 |   6104 |00:00:00.57 |   12078 |    765 |
|  37 |       TABLE ACCESS BY INDEX ROWID BATCHED          | HZ_GEOGRAPHIES               |      1 |    168 |    257 |00:00:00.01 |      19 |     10 |
|* 38 |        INDEX SKIP SCAN                             | HZ_GEOGRAPHIES_N9            |      1 |   5812 |    257 |00:00:00.01 |       8 |      8 |
|* 39 |      INDEX UNIQUE SCAN                             | HZ_PARTIES_U1                |   6104 |      1 |   6104 |00:00:01.96 |   12104 |   2736 |
|  40 |     TABLE ACCESS BY INDEX ROWID                    | HZ_PARTIES                   |   6104 |      1 |   6104 |00:00:02.66 |    6124 |   3795 |
------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("HL"."COUNTRY"="HG"."COUNTRY_CODE")
  11 - access("GLL"."LEDGER_ID"="RCT"."SET_OF_BOOKS_ID" AND "GDR"."TO_CURRENCY"="GLL"."CURRENCY_CODE")
  14 - access("GDR"."CONVERSION_DATE"="RCTD"."GL_DATE" AND TRUNC(INTERNAL_FUNCTION("GL_DATE"))=TRUNC(INTERNAL_FUNCTION("GDR"."CONVERSION_DATE"
              )))
  16 - access("GDR"."CONVERSION_DATE">=TO_DATE(' 2018-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "GDR"."CONVERSION_TYPE"='Corporate' AND 
              "GDR"."CONVERSION_DATE"<=TO_DATE(' 2018-01-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
       filter(("GDR"."CONVERSION_TYPE"='Corporate' AND "GDR"."CONVERSION_DATE">=TO_DATE(' 2018-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND 
              "GDR"."CONVERSION_DATE"<=TO_DATE(' 2018-01-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
  17 - access("CODE_COMBINATION_ID"="RCTD"."CODE_COMBINATION_ID")
  18 - filter("SEGMENT1"='2600')
  19 - access("SEGMENT2"='206911')
  20 - filter("RCTD"."CUSTOMER_TRX_LINE_ID" IS NOT NULL)
  21 - access("RCTD"."GL_DATE">=TO_DATE(' 2018-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "RCTD"."GL_DATE"<=TO_DATE(' 2018-01-10 
              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
  22 - filter("RCTL"."LINE_TYPE"='TAX')
  23 - access("RCTL"."CUSTOMER_TRX_LINE_ID"="RCTD"."CUSTOMER_TRX_LINE_ID")
  24 - filter("GDR"."FROM_CURRENCY"="RCT"."INVOICE_CURRENCY_CODE")
  25 - access("RCT"."CUSTOMER_TRX_ID"="RCTL"."CUSTOMER_TRX_ID")
  28 - access("HCA"."CUST_ACCOUNT_ID"="RCT"."BILL_TO_CUSTOMER_ID")
  30 - access("RCT"."BILL_TO_SITE_USE_ID"="HCSUA"."SITE_USE_ID")
  31 - filter("HCA"."CUST_ACCOUNT_ID"="HCASA"."CUST_ACCOUNT_ID")
  32 - access("HCSUA"."CUST_ACCT_SITE_ID"="HCASA"."CUST_ACCT_SITE_ID")
  34 - access("HCASA"."PARTY_SITE_ID"="HPS"."PARTY_SITE_ID")
  36 - access("HPS"."LOCATION_ID"="HL"."LOCATION_ID")
  38 - access("HG"."GEOGRAPHY_TYPE"='COUNTRY')
       filter("HG"."GEOGRAPHY_TYPE"='COUNTRY')
  39 - access("HZP"."PARTY_ID"="HCA"."PARTY_ID")
Note
-----
   - this is an adaptive plan
Jonathan Lewis

Ignoring the scalar subqueries, your previous plan followed a reasonable join order and method for addressing the query with just the unfortunate problem that the nested loop join into the GL_DAILY_RATES table was using what look liked a highly appropriate index to find exactly one row in the table in a very expensive way thanks to a badly written predicate.

As a first step you need to understand why the index use was so expensive, and see if you can find a way of using that index (or another index which you may have to created) to get to that one row very efficiently.

What is the index definition for gl_daily_rates_u1, and what are the column types of the columns that are (directly, or indirectly) trying to join to the date column in gl_daily_rates. In particularly is there anything that might be worth mentioning about apps.ra_cust_trx_line_gl_dist_all.gl_date, and where did the trunc() function come from in the predicates for operation 14 since there's no sign of a trunc() in the query.

Regards

Jonathan Lewis

Rajesh123 
Jonathan Lewis wrote:
Ignoring the scalar subqueries, your previous plan followed a reasonable join order and method for addressing the query with just the unfortunate problem that the nested loop join into the GL_DAILY_RATES table was using what look liked a highly appropriate index to find exactly one row in the table in a very expensive way thanks to a badly written predicate.
As a first step you need to understand why the index use was so expensive, and see if you can find a way of using that index (or another index which you may have to created) to get to that one row very efficiently.
What is the index definition for gl_daily_rates_u1, and what are the column types of the columns that are (directly, or indirectly) trying to join to the date column in gl_daily_rates. In particularly is there anything that might be worth mentioning about apps.ra_cust_trx_line_gl_dist_all.gl_date, and where did the trunc() function come from in the predicates for operation 14 since there's no sign of a trunc() in the query.
Regards
Jonathan Lewis

(1)

CREATE UNIQUE INDEX GL.GL_DAILY_RATES_U1 ON GL.GL_DAILY_RATES

(FROM_CURRENCY, TO_CURRENCY, CONVERSION_DATE, CONVERSION_TYPE)

(2)Varchar2

FROM_CURRENCY

TO_CURRENCY

CONVERSION_TYPE

CONVERSION_DATE --> Date

(3) is there anything that might be worth mentioning about apps.ra_cust_trx_line_gl_dist_all

Yes gl_date , invoices which are created in a particular month , i should use this column as parameter as i don't have another option.

(4)where did the trunc() function come from in the predicates for operation 14 since there's no sign of a trunc() in the query.

CREATE INDEX AR.RA_CUST_TRX_LINE_GL_DIST_UPG1 ON AR.RA_CUST_TRX_LINE_GL_DIST_ALL

(CUSTOMER_TRX_ID, TRUNC("GL_DATE"), ACCOUNT_SET_FLAG)

CREATE INDEX AR.XXC_CUST_TRX__GL_DIST_N1 ON AR.RA_CUST_TRX_LINE_GL_DIST_ALL

(GL_DATE, NVL("LATEST_REC_FLAG",'Y'), CODE_COMBINATION_ID)

CREATE INDEX AR.RA_CUST_TRX_LINE_GL_DIST_N1 ON AR.RA_CUST_TRX_LINE_GL_DIST_ALL

(CUSTOMER_TRX_LINE_ID, ACCOUNT_SET_FLAG, GL_DATE, ACCOUNT_CLASS)

CREATE INDEX AR.RA_CUST_TRX_LINE_GL_DIST_N5 ON AR.RA_CUST_TRX_LINE_GL_DIST_ALL

(SET_OF_BOOKS_ID, POSTING_CONTROL_ID, GL_DATE)

Jonathan Lewis

Three points

1) In posting number 5 you said that you removed the to_date() and the plan was still the same.

How carefully did you check ?  Did you actually try running it with rowsource execution stats enabled or did you just note that the plan looked the same of the plan hash value was the same.  Did you check the predicate section - because the plan_hash_value and the "plan" could stay the same while the use of predicates changes and makes the plan more efficient.  Please show us the plan with captured with rowsource excution stats enabled after the first 500 rows have been returned.

2) The index does not appear (from an intuitive viewpoint) to be the sensible option. It looks as if it ought to be "(from_currency, to_currency, conversion_type, conversion_date) compress 3"  (And if the current ordering is actually ideal for some important queries it's possible that you still could do with another index with the same 4 columns in a different order with conversion_date at the end. In principle your original index shouldn't need this change as the current index looks as if it ought to be okay except Oracle has introduced that "to_date(internal_function(column_name)))" effect - the re-arranged index would at least minimise the overhead of that wierdness.

3) The trunc(gl_date) in the upg1 index shouldn't echo into the join condition. However, it has occurred to me that perhaps someone has added a couple of constraints to the tables of the form:  (check gl_date = trunc(gl_date)), and the corresponding (check (conversion_date = trunc(conversion_date)).  Can you check the two table definitions for check constraints on the columns.

Regards

Jonathan Lewis

Dom Brooks

In the current execution, the estimates are out quite badly.

So most of the time goes on NESTED LOOP row-by-row lookups on respectively 18 million and 411K rows before the join to GL_LEDGERS brings that back down to a few thousand.

At this point it would help to be more familiar with these tables to know how the query might be written differently (i.e. avoiding any significant manual hinting).

It's just heavier work if you don't know the tables you're dealing with...

i.e. we should be thinking about what the driving rowsources for this query should be and which are just boilerplate - i.e. the superfluous bits of additional information we need later once we have our final resultset.

RCTD + RCTL is presumably what really drives this query?

i.e. get me all the tax lines between these two dates.

Dom Brooks

This might be a giant waste of time but just following on from what I said before about driving data set, I've rearranged the query section you posted a couple of messages back.

There is about 0% chance that I haven't made a mistake somewhere as I've butchered it.

Also the formatting and rearranging is not a reflection on the original style, just a method for going through and double checking what it's all doing ( https://orastory.wordpress.com/2010/03/24/tuning_by_formatting/ ) which includes switching sides of some of the predicates - it's a bit mental I know...

Unfortunately though that comes with a good risk of messing something up along the way.

WITH subq_driving_data AS
     (SELECT /*+ no_merge */
             rctd.gl_date 
      ,      rctd.code_combination_id
      ,      rctl.line_number
      ,      rctl.extended_amount
      ,      rctl.line_type
      ,      rct.org_id,
      ,      rct.interface_header_attribute1
      ,      rct.customer_trx_id
      ,      rct.trx_number
      ,      rct.trx_date  
      ,      rct.creation_date
      ,      rct.invoice_currency_code
      ,      rct.bill_to_customer_id
      ,      rct.bill_to_site_use_id
      ,      gll.currency_code
      ,      (SELECT gdr.conversion_rate
              FROM   apps.gl_daily_rates gdr
              WHERE  gdr.from_currency     = rct.invoice_currency_code   
              AND    gdr.to_currency       = gll.currency_code   
              AND    gdr.conversion_date   = rctd.gl_date
              AND    gdr.conversion_type   = 'Corporate') conversion_rate
      FROM   apps.ra_cust_trx_line_gl_dist_all rctd
      ,      apps.ra_customer_trx_lines_all    rctl
      ,      apps.ra_customer_trx_all          rct
      ,      apps.gl_ledgers          gll
      WHERE  rctd.gl_date              BETWEEN To_date ('01-JAN-2018', 'DD-MON-YYYY') AND  To_date ('10-JAN-2018', 'DD-MON-YYYY')
      AND    rctl.customer_trx_line_id       = rctd.customer_trx_line_id  
      AND    rctl.line_type                  = 'TAX'
      AND    rct.customer_trx_id             = rctl.customer_trx_id
      AND    gll.ledger_id                   = rct.set_of_books_id)  
SELECT /*+ gather_plan_statistics*/ 
       DISTINCT 2  
,      dd.org_id
,      hzp.party_name
,      hca.account_number
,      dd.interface_header_attribute1 order_number
,      dd.customer_trx_id
,      dd.trx_number
,      dd.trx_date
,      dd.gl_date
,      dd.creation_date
,      dd.line_number
,      dd.invoice_currency_code       inv_currency
,      sum(case when dd.line_type='TAX' then dd.extended_amount else 0 end) inv_tax_amount
,      dd.currency_code               func_currency 
,      Round(sum(case when dd.line_type='TAX' then dd.extended_amount else 0 end)*dd.conversion_rate,2) func_tax_amount
,      glcc.segment1                  company
,      glcc.segment2                  account,
,      hg.geography_name              billing_country
,      dd.conversion_rate
FROM   subq_driving_data             dd         
,      apps.hz_parties               hzp
,      apps.hz_cust_accounts         hca
,      apps.gl_code_combinations_kfv glcc
,      apps.hz_cust_site_uses_all    hcsua   
,      apps.hz_cust_acct_sites_all   hcasa
,      apps.hz_party_sites           hps
,      apps.hz_locations             hl  
,      apps.hz_geographies           hg
WHERE  hca.cust_account_id         = dd.bill_to_customer_id
AND    hzp.party_id                = hca.party_id   
AND    hcsua.site_use_id           = dd.bill_to_site_use_id
AND    hcasa.cust_account_id       = hca.cust_account_id     
AND    hcasa.cust_acct_site_id     = hcsua.cust_acct_site_id   
AND    hps.party_site_id           = hcasa.party_site_id
AND    hl.location_id              = hps.location_id   
AND    hg.country_code             = hl.country   
AND    hg.geography_type           = 'COUNTRY'
AND    glcc.code_combination_id    = dd.code_combination_id
AND    glcc.segment1               = '2600'  
AND    glcc.segment2               = '206911'  
GROUP BY   
       hzp.party_name,   
       hca.account_number,   
       dd.interface_header_attribute1,   
       dd.trx_number,   
       dd.trx_date,   
       dd.creation_date,   
       dd.line_number,   
       dd.unit_selling_price,   
       dd.org_id,   
       dd.gl_date,   
       dd.customer_trx_id,   
       glcc.segment1,  
       glcc.segment2,   
       hg.geography_name,   
       dd.invoice_currency_code,   
       dd.currency_code,   
       dd.conversion_rate; 
1 person found this helpful
Rajesh123

Hi ,

I ran the query which was modified by you taking 9 minutes time to complete.

PLAN_TABLE_OUTPUT
SQL_ID  3fmqn33ytdvj7, child number 0
-------------------------------------
WITH subq_driving_data AS        (SELECT /*+ no_merge */                
rctd.gl_date          ,      rctd.code_combination_id         ,      
rctl.line_number         ,      rctl.extended_amount         ,      
rctl.line_type         ,      rct.org_id       ,      
rct.interface_header_attribute1         ,      rct.customer_trx_id      
   ,      rct.trx_number         ,      rct.trx_date           ,      
rct.creation_date         ,      rct.invoice_currency_code         ,    
  rct.bill_to_customer_id         ,      rct.bill_to_site_use_id        
 ,      gll.currency_code         ,      (SELECT gdr.conversion_rate    
             FROM   apps.gl_daily_rates gdr                 WHERE  
gdr.from_currency     = rct.invoice_currency_code                    
AND    gdr.to_currency       = gll.currency_code                    AND 
   gdr.conversion_date   = rctd.gl_date                 AND    
gdr.conversion_type   = 'Corporate') conversion_rate         FROM   
apps.ra_cust_trx_line_gl_dist_all rctd
Plan hash value: 357138763
-----------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                         | Name                         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                  |                              |      1 |        |    501 |00:09:16.40 |    9306K|   8194K|
|   1 |  TABLE ACCESS BY INDEX ROWID                      | GL_DAILY_RATES               |  17504 |      1 |   7109 |00:00:00.13 |   38391 |      0 |
|*  2 |   INDEX UNIQUE SCAN                               | GL_DAILY_RATES_U1            |  17504 |      1 |   7109 |00:00:00.07 |   31282 |      0 |
|   3 |  HASH GROUP BY                                    |                              |      1 |      1 |    501 |00:09:16.40 |    9306K|   8194K|
|   4 |   NESTED LOOPS                                    |                              |      1 |        |  31898 |00:09:16.31 |    9306K|   8194K|
|   5 |    NESTED LOOPS                                   |                              |      1 |      1 |  31898 |00:09:16.11 |    9274K|   8194K|
|*  6 |     HASH JOIN                                     |                              |      1 |      1 |  31898 |00:09:15.83 |    9212K|   8194K|
|   7 |      NESTED LOOPS                                 |                              |      1 |      1 |  31898 |00:09:15.61 |    9211K|   8194K|
|   8 |       NESTED LOOPS                                |                              |      1 |      1 |  31898 |00:09:15.22 |    9118K|   8194K|
|   9 |        NESTED LOOPS                               |                              |      1 |      1 |  31898 |00:09:14.83 |    9025K|   8194K|
|  10 |         NESTED LOOPS                              |                              |      1 |    341 |  31898 |00:09:14.41 |    8938K|   8194K|
|  11 |          NESTED LOOPS                             |                              |      1 |    341 |  31898 |00:09:13.93 |    8844K|   8194K|
|* 12 |           HASH JOIN                               |                              |      1 |    341 |  31898 |00:09:13.49 |    8751K|   8194K|
|* 13 |            TABLE ACCESS BY INDEX ROWID BATCHED    | GL_CODE_COMBINATIONS         |      1 |     35 |      1 |00:00:00.01 |     108 |      0 |
|* 14 |             INDEX RANGE SCAN                      | GL_CODE_COMBINATIONS_N2      |      1 |    499 |     77 |00:00:00.01 |       3 |      0 |
|  15 |            VIEW                                   |                              |      1 |    575K|    578K|00:09:12.41 |    8751K|   8194K|
|* 16 |             HASH JOIN                             |                              |      1 |    575K|    578K|00:09:11.21 |    8713K|   8194K|
|  17 |              TABLE ACCESS FULL                    | GL_LEDGERS                   |      1 |     38 |     39 |00:00:00.01 |      15 |      0 |
|* 18 |              HASH JOIN                            |                              |      1 |    575K|    578K|00:09:09.75 |    8713K|   8194K|
|* 19 |               HASH JOIN                           |                              |      1 |    575K|    578K|00:06:49.08 |    6527K|   6011K|
|* 20 |                TABLE ACCESS BY INDEX ROWID BATCHED| RA_CUST_TRX_LINE_GL_DIST_ALL |      1 |    630K|   1388K|00:00:04.30 |     507K|      0 |
|* 21 |                 INDEX RANGE SCAN                  | RA_CUST_TRX_LINE_GL_DIST_N2  |      1 |    706K|   1738K|00:00:01.33 |   10653 |      0 |
|* 22 |                TABLE ACCESS FULL                  | RA_CUSTOMER_TRX_LINES_ALL    |      1 |     63M|     79M|00:03:50.21 |    6020K|   6011K|
|  23 |               TABLE ACCESS FULL                   | RA_CUSTOMER_TRX_ALL          |      1 |     16M|     16M|00:01:41.26 |    2185K|   2182K|
|  24 |           TABLE ACCESS BY INDEX ROWID             | HZ_CUST_ACCOUNTS             |  31898 |      1 |  31898 |00:00:00.31 |   93142 |      0 |
|* 25 |            INDEX UNIQUE SCAN                      | HZ_CUST_ACCOUNTS_U1          |  31898 |      1 |  31898 |00:00:00.14 |   61244 |      0 |
|  26 |          TABLE ACCESS BY INDEX ROWID              | HZ_CUST_SITE_USES_ALL        |  31898 |      1 |  31898 |00:00:00.30 |   93417 |      0 |
|* 27 |           INDEX UNIQUE SCAN                       | HZ_CUST_SITE_USES_U1         |  31898 |      1 |  31898 |00:00:00.16 |   61519 |      0 |
|* 28 |         TABLE ACCESS BY INDEX ROWID               | HZ_CUST_ACCT_SITES_ALL       |  31898 |      1 |  31898 |00:00:00.32 |   87027 |      0 |
|* 29 |          INDEX UNIQUE SCAN                        | HZ_CUST_ACCT_SITES_U1        |  31898 |      1 |  31898 |00:00:00.15 |   61546 |      0 |
|  30 |        TABLE ACCESS BY INDEX ROWID                | HZ_PARTY_SITES               |  31898 |      1 |  31898 |00:00:00.30 |   93397 |      0 |
|* 31 |         INDEX UNIQUE SCAN                         | HZ_PARTY_SITES_U1            |  31898 |      1 |  31898 |00:00:00.15 |   61499 |      0 |
|  32 |       TABLE ACCESS BY INDEX ROWID                 | HZ_LOCATIONS                 |  31898 |      1 |  31898 |00:00:00.30 |   93416 |      0 |
|* 33 |        INDEX UNIQUE SCAN                          | HZ_LOCATIONS_U1              |  31898 |      1 |  31898 |00:00:00.15 |   61518 |      0 |
|  34 |      TABLE ACCESS BY INDEX ROWID BATCHED          | HZ_GEOGRAPHIES               |      1 |    168 |    257 |00:00:00.01 |      19 |      0 |
|* 35 |       INDEX SKIP SCAN                             | HZ_GEOGRAPHIES_N9            |      1 |   5812 |    257 |00:00:00.01 |       8 |      0 |
|* 36 |     INDEX UNIQUE SCAN                             | HZ_PARTIES_U1                |  31898 |      1 |  31898 |00:00:00.18 |   62126 |      0 |
|  37 |    TABLE ACCESS BY INDEX ROWID                    | HZ_PARTIES                   |  31898 |      1 |  31898 |00:00:00.10 |   32043 |      0 |
-----------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("GDR"."FROM_CURRENCY"=:B1 AND "GDR"."TO_CURRENCY"=:B2 AND "GDR"."CONVERSION_DATE"=:B3 AND "GDR"."CONVERSION_TYPE"='Corporate')
   6 - access("HG"."COUNTRY_CODE"="HL"."COUNTRY")
  12 - access("CODE_COMBINATION_ID"="DD"."CODE_COMBINATION_ID")
  13 - filter("SEGMENT1"='2600')
  14 - access("SEGMENT2"='206911')
  16 - access("GLL"."LEDGER_ID"="RCT"."SET_OF_BOOKS_ID")
  18 - access("RCT"."CUSTOMER_TRX_ID"="RCTL"."CUSTOMER_TRX_ID")
  19 - access("RCTL"."CUSTOMER_TRX_LINE_ID"="RCTD"."CUSTOMER_TRX_LINE_ID")
  20 - filter("RCTD"."CUSTOMER_TRX_LINE_ID" IS NOT NULL)
  21 - access("RCTD"."GL_DATE">=TO_DATE(' 2018-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "RCTD"."GL_DATE"<=TO_DATE(' 2018-01-30 
              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
  22 - filter("RCTL"."LINE_TYPE"='TAX')
  25 - access("HCA"."CUST_ACCOUNT_ID"="DD"."BILL_TO_CUSTOMER_ID")
  27 - access("HCSUA"."SITE_USE_ID"="DD"."BILL_TO_SITE_USE_ID")
  28 - filter("HCASA"."CUST_ACCOUNT_ID"="HCA"."CUST_ACCOUNT_ID")
  29 - access("HCASA"."CUST_ACCT_SITE_ID"="HCSUA"."CUST_ACCT_SITE_ID")
  31 - access("HPS"."PARTY_SITE_ID"="HCASA"."PARTY_SITE_ID")
  33 - access("HL"."LOCATION_ID"="HPS"."LOCATION_ID")
  35 - access("HG"."GEOGRAPHY_TYPE"='COUNTRY')
       filter("HG"."GEOGRAPHY_TYPE"='COUNTRY')
  36 - access("HZP"."PARTY_ID"="HCA"."PARTY_ID")
Note
-----
   - this is an adaptive plan
JohnWatson2

You do need to use format=>'adaptive' if you want to see the plan that is actually used.

Rajesh123

Point# 3

conversation_date (gl_daily_rates) and its constraints.

pastedImage_0.png

RA_CUST_TRX_LINE_GL_DIST_ALL and its constraints.

pastedImage_1.png

Please let me know if anything missing from my side.

Dom Brooks

So, the GLCC join is quite selective as well so perhaps we need to bring that earlier in the query filtering so that driving source is the date selections on RCTD + the join to GLCC for company/segment?

Rajesh123

Yes for company segments

Rajesh123

Hi ,

Could you please help me?

Jonathan Lewis 
Rajesh123 wrote:
Hi ,
Could you please help me?

You haven't answered any of the questions in point 1 of my previous post.

I've already said that the first important point to address seems to be the need to make a critical index access access in the original plan more efficient - point 2 suggests re-ordering the column (so you might want to test with an invisible additional index and optimizer_use_invisible_indexes set to true), and we need to work out why that internal_function() is appearing and make it go away.  In the interim you've been trying to produce a completely different plan and have apparently been ignoring my primary suggestion - if you want help from me you have to answer the questions I ask and supply me with the specific bits of information I ask for.

Regards

Jonathan Lewis

Rajesh123

1# first important point to address seems to be the need to make a critical index access in the original plan more efficient

Could you please explain little bit more, i am completely new to this.

2# re-ordering the column (so you might want to test with an invisible additional index and optimizer_use_invisible_indexes set to true), and we need to work out why that internal_function() is appearing and make it go away

Could you please explain little bit more, i am completely new to this.

Thank you

Jonathan Lewis 
Rajesh123 wrote:
1# first important point to address seems to be the need to make a critical index access in the original plan more efficient 
Could you please explain little bit more, i am completely new to this.
2# re-ordering the column (so you might want to test with an invisible additional index and optimizer_use_invisible_indexes set to true), and we need to work out why that internal_function() is appearing and make it go away
Could you please explain little bit more, i am completely new to this.
Thank you

1# - re-read the comments Dom Brooks and I made in the early stages of this thread.

2# - might be irrelevant, misleading, and a total waste of time, so I won't do it until you've answered the questions I reminded you about in my previous posting.  It's possible that the answers you give (especially the plan) may mean that the suggestion is irrelevant, on the other hand they may make it much easier to give you an explanation you understand of what you need to do and how to do it.

Regards

Jonathan Lewis

Rajesh123 
Dom Brooks wrote:
So, the GLCC join is quite selective as well so perhaps we need to bring that earlier in the query filtering so that driving source is the date selections on RCTD + the join to GLCC for company/segment?

Hi Dom,

Could you please explain little more ? in technical level

Rajesh123

I did rowsource plan using with GATHER PLAN hint in Select statement

SELECT *

FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT

SQL_ID  103z7pvxjhwgn, child number 0

begin dbms_output.get_line(line => :line, status => :status); end;

NOTE: cannot fetch plan for SQL_ID: 103z7pvxjhwgn, CHILD_NUMBER: 0

      Please verify value of SQL_ID and CHILD_NUMBER;

      It could also be that the plan is no longer in cursor cache (check v$sql_plan)

AndrewSayer 
Rajesh123 wrote:
I did rowsource plan using with GATHER PLAN hint in Select statement 
SELECT * 
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
SQL_ID 103z7pvxjhwgn, child number 0
begin dbms_output.get_line(line => :line, status => :status); end;
NOTE: cannot fetch plan for SQL_ID: 103z7pvxjhwgn, CHILD_NUMBER: 0
 Please verify value of SQL_ID and CHILD_NUMBER; 
 It could also be that the plan is no longer in cursor cache (check v$sql_plan)

Did you read the output you copied and pasted here? You tried to get the row source execution statistics for the statement:

begin dbms_output.get_line(line => :line, status => :status); end;

That is not the statement you care about. A google would have shown you that this statement is due to your client program trying to fetch data from the dbms_output buffer and can be turned off with

set serverout off


I suggest you turn off serveroutput, run the statement and run the dbms_xplan.display_cursor query again. Always take a moment to read over what you are copying and pasting first.

1 person found this helpful
Rajesh123

Hi,

I ran the rowsource plan.

SELECT /*+ GATHER_PLAN_STATISTICS */ DISTINCT dd.org_id  
,      hzp.party_name  
,      hca.account_number  
,      dd.interface_header_attribute1 order_number  
,      dd.customer_trx_id  
,      dd.trx_number  
,      dd.trx_date  
,      dd.gl_date  
,      dd.creation_date  
,      dd.line_number  
,      dd.invoice_currency_code       inv_currency  
,      (SELECT SUM (extended_amount)
          FROM apps.ra_customer_trx_lines_all
         WHERE customer_trx_id =dd.customer_trx_id
           AND line_type = 'LINE') inv_net_amount
,      sum(case when dd.line_type='TAX' then dd.extended_amount else 0 end) inv_tax_amount 
,      (SELECT SUM (extended_amount)
          FROM apps.ra_customer_trx_lines_all
          WHERE customer_trx_id =dd.customer_trx_id) inv_gross_amount
,      dd.currency_code               func_currency
,      Round(sum(case when dd.line_type='TAX' then dd.extended_amount else 0 end)*dd.conversion_rate,2) func_tax_amount  
,      glcc.segment1                  company  
,      glcc.segment2                  account
,      hg.geography_name              billing_country  
,      dd.conversion_rate  
FROM (SELECT /*+ no_merge */  
             rctd.gl_date   
      ,      rctd.code_combination_id  
      ,      rctl.line_number  
      ,      rctl.extended_amount  
      ,      rctl.line_type  
      ,      rct.org_id  
      ,      rct.interface_header_attribute1  
      ,      rct.customer_trx_id  
      ,      rct.trx_number  
      ,      rct.trx_date    
      ,      rct.creation_date  
      ,      rct.invoice_currency_code  
      ,      rct.bill_to_customer_id  
      ,      rct.bill_to_site_use_id  
      ,      gll.currency_code  
      ,      (SELECT gdr.conversion_rate  
              FROM   apps.gl_daily_rates gdr  
              WHERE  gdr.from_currency     = rct.invoice_currency_code     
              AND    gdr.to_currency       = gll.currency_code     
              AND    gdr.conversion_date   = rctd.gl_date  
              AND    gdr.conversion_type   = 'Corporate') conversion_rate  
      FROM   apps.ra_cust_trx_line_gl_dist_all rctd  
      ,      apps.ra_customer_trx_lines_all    rctl  
      ,      apps.ra_customer_trx_all          rct  
      ,      apps.gl_ledgers          gll  
      WHERE  rctl.customer_trx_line_id       = rctd.customer_trx_line_id    
      AND    rctl.line_type                  = 'TAX'  
      AND    rct.customer_trx_id             = rctl.customer_trx_id  
      AND    gll.ledger_id                   = rct.set_of_books_id) dd           
,      apps.hz_parties               hzp  
,      apps.hz_cust_accounts         hca  
,      apps.gl_code_combinations_kfv glcc  
,      apps.hz_cust_site_uses_all    hcsua     
,      apps.hz_cust_acct_sites_all   hcasa  
,      apps.hz_party_sites           hps  
,      apps.hz_locations             hl    
,      apps.hz_geographies           hg  
WHERE  hca.cust_account_id         = dd.bill_to_customer_id  
AND    hzp.party_id                = hca.party_id     
AND    hcsua.site_use_id           = dd.bill_to_site_use_id  
AND    hcasa.cust_account_id       = hca.cust_account_id       
AND    hcasa.cust_acct_site_id     = hcsua.cust_acct_site_id     
AND    hps.party_site_id           = hcasa.party_site_id  
AND    hl.location_id              = hps.location_id     
AND    hg.country_code             = hl.country     
AND    hg.geography_type           = 'COUNTRY'  
AND    glcc.code_combination_id    = dd.code_combination_id  
AND    glcc.segment1               = '2600'    
AND    glcc.segment2               = '206911'    
AND dd.gl_date              BETWEEN '01-JAN-2018' and '30-JAN-2018'
GROUP BY     
       hzp.party_name,     
       hca.account_number,     
       dd.interface_header_attribute1,     
       dd.trx_number,     
       dd.trx_date,     
       dd.creation_date,     
       dd.line_number,   
       dd.org_id,     
       dd.gl_date,     
       dd.customer_trx_id,     
       glcc.segment1,    
       glcc.segment2,     
       hg.geography_name,     
       dd.invoice_currency_code,     
       dd.currency_code,     
       dd.conversion_rate;
SELECT * FROM TABLE(dbms_xplan.display_cursor('4gw9h15ttptrp', null, 'ALLSTATS LAST'));
SQL_ID  4gw9h15ttptrp, child number 7
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */ DISTINCT dd.org_id   ,      
hzp.party_name   ,      hca.account_number   ,      
dd.interface_header_attribute1 order_number   ,      dd.customer_trx_id 
  ,      dd.trx_number   ,      dd.trx_date   ,      dd.gl_date   ,     
 dd.creation_date   ,      dd.line_number   ,      
dd.invoice_currency_code       inv_currency   ,      (SELECT SUM 
(extended_amount)           FROM apps.ra_customer_trx_lines_all         
 WHERE customer_trx_id =dd.customer_trx_id            AND line_type = 
'LINE') inv_net_amount ,      sum(case when dd.line_type='TAX' then 
dd.extended_amount else 0 end) inv_tax_amount  ,      (SELECT SUM 
(extended_amount)           FROM apps.ra_customer_trx_lines_all         
  WHERE customer_trx_id =dd.customer_trx_id) inv_gross_amount ,      
dd.currency_code               func_currency ,      Round(sum(case when 
dd.line_type='TAX' then dd.extended_amount else 0 
end)*dd.conversion_rate,2) func_tax_amount   ,      glcc.segment1       
           c
Plan hash value: 1832523212
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                           | Name                         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                    |                              |      1 |        |    501 |00:10:09.14 |    9539K|   8194K|       |       |          |
|   1 |  TABLE ACCESS BY INDEX ROWID                        | GL_DAILY_RATES               |  17504 |      1 |   7109 |00:00:00.13 |   38393 |      0 |       |       |          |
|*  2 |   INDEX UNIQUE SCAN                                 | GL_DAILY_RATES_U1            |  17504 |      1 |   7109 |00:00:00.07 |   31284 |      0 |       |       |          |
|   3 |  HASH GROUP BY                                      |                              |      1 |      1 |    501 |00:10:09.14 |    9539K|   8194K|   727K|   727K|          |
|   4 |   NESTED LOOPS                                      |                              |      1 |        |  31898 |00:10:09.02 |    9539K|   8194K|       |       |          |
|   5 |    NESTED LOOPS                                     |                              |      1 |      1 |  31898 |00:10:08.76 |    9507K|   8194K|       |       |          |
|*  6 |     HASH JOIN                                       |                              |      1 |      1 |  31898 |00:10:08.41 |    9445K|   8194K|  6772K|  2147K| 8154K (0)|
|   7 |      NESTED LOOPS                                   |                              |      1 |      1 |  31898 |00:10:08.17 |    9445K|   8194K|       |       |          |
|   8 |       NESTED LOOPS                                  |                              |      1 |      1 |  31898 |00:10:07.77 |    9352K|   8194K|       |       |          |
|   9 |        NESTED LOOPS OUTER                           |                              |      1 |      1 |  31898 |00:10:07.37 |    9258K|   8194K|       |       |          |
|  10 |         NESTED LOOPS OUTER                          |                              |      1 |      1 |  31898 |00:10:06.13 |    9125K|   8194K|       |       |          |
|  11 |          NESTED LOOPS                               |                              |      1 |      1 |  31898 |00:10:05.10 |    9024K|   8194K|       |       |          |
|  12 |           NESTED LOOPS                              |                              |      1 |    341 |  31898 |00:10:04.67 |    8937K|   8194K|       |       |          |
|  13 |            NESTED LOOPS                             |                              |      1 |    341 |  31898 |00:10:04.19 |    8844K|   8194K|       |       |          |
|* 14 |             HASH JOIN                               |                              |      1 |    341 |  31898 |00:10:03.72 |    8751K|   8194K|  1344K|  1344K|  730K (0)|
|* 15 |              TABLE ACCESS BY INDEX ROWID BATCHED    | GL_CODE_COMBINATIONS         |      1 |     35 |      1 |00:00:00.01 |     108 |      0 |       |       |          |
|* 16 |               INDEX RANGE SCAN                      | GL_CODE_COMBINATIONS_N2      |      1 |    499 |     77 |00:00:00.01 |       3 |      0 |       |       |          |
|  17 |              VIEW                                   |                              |      1 |    575K|    578K|00:10:02.59 |    8751K|   8194K|       |       |          |
|* 18 |               HASH JOIN                             |                              |      1 |    575K|    578K|00:10:01.34 |    8712K|   8194K|  1817K|  1817K| 1604K (0)|
|  19 |                TABLE ACCESS FULL                    | GL_LEDGERS                   |      1 |     38 |     39 |00:00:00.01 |      15 |      0 |       |       |          |
|* 20 |                HASH JOIN                            |                              |      1 |    575K|    578K|00:09:59.83 |    8712K|   8194K|    63M|  4888K|   77M (0)|
|* 21 |                 HASH JOIN                           |                              |      1 |    575K|    578K|00:07:25.19 |    6527K|   6011K|    94M|  6318K|   99M (0)|
|* 22 |                  TABLE ACCESS BY INDEX ROWID BATCHED| RA_CUST_TRX_LINE_GL_DIST_ALL |      1 |    630K|   1388K|00:00:04.27 |     507K|      0 |       |       |          |
|* 23 |                   INDEX RANGE SCAN                  | RA_CUST_TRX_LINE_GL_DIST_N2  |      1 |    706K|   1738K|00:00:01.32 |   10653 |      0 |       |       |          |
|* 24 |                  TABLE ACCESS FULL                  | RA_CUSTOMER_TRX_LINES_ALL    |      1 |     63M|     79M|00:04:24.98 |    6020K|   6011K|       |       |          |
|  25 |                 TABLE ACCESS FULL                   | RA_CUSTOMER_TRX_ALL          |      1 |     16M|     16M|00:01:54.31 |    2185K|   2182K|       |       |          |
|  26 |             TABLE ACCESS BY INDEX ROWID             | HZ_CUST_ACCOUNTS             |  31898 |      1 |  31898 |00:00:00.30 |   93126 |      0 |       |       |          |
|* 27 |              INDEX UNIQUE SCAN                      | HZ_CUST_ACCOUNTS_U1          |  31898 |      1 |  31898 |00:00:00.14 |   61228 |      0 |       |       |          |
|  28 |            TABLE ACCESS BY INDEX ROWID              | HZ_CUST_SITE_USES_ALL        |  31898 |      1 |  31898 |00:00:00.30 |   93397 |      0 |       |       |          |
|* 29 |             INDEX UNIQUE SCAN                       | HZ_CUST_SITE_USES_U1         |  31898 |      1 |  31898 |00:00:00.16 |   61499 |      0 |       |       |          |
|* 30 |           TABLE ACCESS BY INDEX ROWID               | HZ_CUST_ACCT_SITES_ALL       |  31898 |      1 |  31898 |00:00:00.33 |   87019 |      0 |       |       |          |
|* 31 |            INDEX UNIQUE SCAN                        | HZ_CUST_ACCT_SITES_U1        |  31898 |      1 |  31898 |00:00:00.15 |   61541 |      0 |       |       |          |
|  32 |          VIEW PUSHED PREDICATE                      | VW_SSQ_2                     |  31898 |      1 |  31898 |00:00:00.91 |     100K|      0 |       |       |          |
|  33 |           SORT GROUP BY                             |                              |  31898 |      1 |  31898 |00:00:00.75 |     100K|      0 |  2048 |  2048 | 2048  (0)|
|  34 |            TABLE ACCESS BY INDEX ROWID BATCHED      | RA_CUSTOMER_TRX_LINES_ALL    |  31898 |      8 |  85280 |00:00:00.46 |     100K|      0 |       |       |          |
|* 35 |             INDEX RANGE SCAN                        | XXC_CUSTOMER_GETPAID         |  31898 |     12 |  85280 |00:00:00.21 |   79858 |      0 |       |       |          |
|  36 |         VIEW PUSHED PREDICATE                       | VW_SSQ_1                     |  31898 |      1 |  31898 |00:00:01.14 |     132K|      0 |       |       |          |
|  37 |          SORT GROUP BY                              |                              |  31898 |      1 |  31898 |00:00:01.02 |     132K|      0 |  2048 |  2048 | 2048  (0)|
|  38 |           TABLE ACCESS BY INDEX ROWID BATCHED       | RA_CUSTOMER_TRX_LINES_ALL    |  31898 |     16 |    203K|00:00:00.71 |     132K|      0 |       |       |          |
|* 39 |            INDEX RANGE SCAN                         | XXC_CUSTOMER_GETPAID         |  31898 |     16 |    203K|00:00:00.27 |   80742 |      0 |       |       |          |
|  40 |        TABLE ACCESS BY INDEX ROWID                  | HZ_PARTY_SITES               |  31898 |      1 |  31898 |00:00:00.31 |   93390 |      0 |       |       |          |
|* 41 |         INDEX UNIQUE SCAN                           | HZ_PARTY_SITES_U1            |  31898 |      1 |  31898 |00:00:00.16 |   61492 |      0 |       |       |          |
|  42 |       TABLE ACCESS BY INDEX ROWID                   | HZ_LOCATIONS                 |  31898 |      1 |  31898 |00:00:00.30 |   93424 |      0 |       |       |          |
|* 43 |        INDEX UNIQUE SCAN                            | HZ_LOCATIONS_U1              |  31898 |      1 |  31898 |00:00:00.15 |   61526 |      0 |       |       |          |
|  44 |      TABLE ACCESS BY INDEX ROWID BATCHED            | HZ_GEOGRAPHIES               |      1 |    168 |    257 |00:00:00.01 |      19 |      0 |       |       |          |
|* 45 |       INDEX SKIP SCAN                               | HZ_GEOGRAPHIES_N9            |      1 |   5812 |    257 |00:00:00.01 |       8 |      0 |       |       |          |
|* 46 |     INDEX UNIQUE SCAN                               | HZ_PARTIES_U1                |  31898 |      1 |  31898 |00:00:00.22 |   62126 |      0 |       |       |          |
|  47 |    TABLE ACCESS BY INDEX ROWID                      | HZ_PARTIES                   |  31898 |      1 |  31898 |00:00:00.13 |   32043 |      0 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("GDR"."FROM_CURRENCY"=:B1 AND "GDR"."TO_CURRENCY"=:B2 AND "GDR"."CONVERSION_DATE"=:B3 AND "GDR"."CONVERSION_TYPE"='Corporate')
   6 - access("HG"."COUNTRY_CODE"="HL"."COUNTRY")
  14 - access("CODE_COMBINATION_ID"="DD"."CODE_COMBINATION_ID")
  15 - filter("SEGMENT1"='2600')
  16 - access("SEGMENT2"='206911')
  18 - access("GLL"."LEDGER_ID"="RCT"."SET_OF_BOOKS_ID")
  20 - access("RCT"."CUSTOMER_TRX_ID"="RCTL"."CUSTOMER_TRX_ID")
  21 - access("RCTL"."CUSTOMER_TRX_LINE_ID"="RCTD"."CUSTOMER_TRX_LINE_ID")
  22 - filter("RCTD"."CUSTOMER_TRX_LINE_ID" IS NOT NULL)
  23 - access("RCTD"."GL_DATE">=TO_DATE(' 2018-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "RCTD"."GL_DATE"<=TO_DATE(' 2018-01-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
  24 - filter("RCTL"."LINE_TYPE"='TAX')
  27 - access("HCA"."CUST_ACCOUNT_ID"="DD"."BILL_TO_CUSTOMER_ID")
  29 - access("HCSUA"."SITE_USE_ID"="DD"."BILL_TO_SITE_USE_ID")
  30 - filter("HCASA"."CUST_ACCOUNT_ID"="HCA"."CUST_ACCOUNT_ID")
  31 - access("HCASA"."CUST_ACCT_SITE_ID"="HCSUA"."CUST_ACCT_SITE_ID")
  35 - access("CUSTOMER_TRX_ID"="DD"."CUSTOMER_TRX_ID" AND "LINE_TYPE"='LINE')
  39 - access("CUSTOMER_TRX_ID"="DD"."CUSTOMER_TRX_ID")
  41 - access("HPS"."PARTY_SITE_ID"="HCASA"."PARTY_SITE_ID")
  43 - access("HL"."LOCATION_ID"="HPS"."LOCATION_ID")
  45 - access("HG"."GEOGRAPHY_TYPE"='COUNTRY')
       filter("HG"."GEOGRAPHY_TYPE"='COUNTRY')
  46 - access("HZP"."PARTY_ID"="HCA"."PARTY_ID")
Note
-----
   - this is an adaptive plan
Jonathan Lewis

Rajesh123,

That's not the original query with the to_date() functions removed. It's a completely different query.

This is what I asked for:

1) In posting number 5 you said that you removed the to_date() and the plan was still the same.
How carefully did you check ?  Did you actually try running it with rowsource execution stats enabled or did you just note that the plan looked the same of the plan hash value was the same.  Did you check the predicate section - because the plan_hash_value and the "plan" could stay the same while the use of predicates changes and makes the plan more efficient.  Please show us the plan with captured with rowsource excution stats enabled after the first 500 rows have been returned.
Regards
Jonathan Lewis
Rajesh123

Hi,

Sorry , i will run the original query removing with to_date from conversation_date and gl_date from where clause and let you know.

1 - 33

Post Details

Added on Dec 15 2021
#general-database-discussions, #spatial
8 comments
13,827 views