Global temporary tables

User_3IM5K

Hi

Can any one help me with the below issue.

Recently we are migrated the oracle EBS application(12.2.9) and Database(19.8) to Latest Releases. we are using Statement of account Report through Global Temporary Table concept, First data is inserted into Global temporary tables and then generating Xml Report.After Upgradation, Insert time into Global tempaorary table is More compared to Previous and Report is taking long time to complete. Please help me to sort this issue.

Thanks in Advance.

evgenyg

Check out where you spend time and for what events.

take a look on awr report (if you licensed for), check out execution plans (before/after) .

You need to provide some more information about your issue to get help/direction.

Post there at least:

1. Insert and execution plan of it (there is insert as select isn't?)
2. Select that generate your report and execution plan of it.

As well, "more" is not time measurement unit.

Regards

User_3IM5K

Thank you so much for your Reply. Here I am attaching trace file for your information.

insert and execution plan of it (there is insert as select isn't?)-- yes. Insert

15Sep2020.rar

as select

evgenyg

Good one, query of 1624 lines.

It would be good idea to rewrite it using subquery factoring, it would give you opportunity to simplify the query.

Regarding performance, I don't see predicates section and the rest of good things we have usually in oracle but.

I would suggest you to start from: HZ_CUSTOMER_PROFILES

seems you retrieve few hundred of millions of rows from this table using index scan.

354205278 354205278 354205278      INDEX RANGE SCAN HZ_CUSTOMER_PROFILES_N1 (cr=67243030 pr=0 pw=0 time=572087644 us starts=354205278 cost=2 size=0 card=1)(object id 1259019)

Meanwhile ignore the cartesian product which is need to be addressed once you manage to simplify the query.

Very impressive system you have there, such amount of I/O in very short time.

Seems you use only three columns and very much repetitive from this table,

HCP.CUST_ACCOUNT_ID , HCP.COLLECTOR_ID , HCP.SITE_USE_ID

Why not materialize it?

What the statistics status? Is it up to date?

User_3IM5K

What the statistics status? Is it up to date?

YES. status are up to date

User_3IM5K

I am trying to get collector name from below table

HZ_CUSTOMER_PROFILES. I don 't have other join with hz_cust_accounts other than cust_account_id

evgenyg

You must simplify the query.

I saw repetitive patterns there, so I would start such work from materialization of repetitive pieces .

User_3IM5K

Can You please guide me How to start and what is meant by repetitive patterns

evgenyg

You have two main blocks , MQ and CUST.

Each one contains multiple UNION's and many "small" selects.

According to trace file you sent the issue seems to be in MQ.

So start with one select a time.

I would start from first,

Can you try to create table with three columns:

cust_account_id, collector_id site_use_id from hz_customer_profiles where site_use_id IS NULL .

and replace in first select of MQ   hz_customer_profiles HCP, using this table?

There:

    FROM  ap_payment_schedules_all PS,

        ap_invoices_all I,

        ap_suppliers V,

        po_vendor_sites_all VS,

        hz_cust_accounts_all HCA,

        hz_customer_profiles HCP,

        ar_collectors AC

    WHERE I.invoice_id = PS.invoice_id

        AND I.vendor_id = V.vendor_id

        AND I.vendor_site_id = VS.vendor_site_id

        AND HCA.account_number = V.segment1

        AND HCP.cust_account_id = HCA.cust_account_id

        AND HCP.collector_id = AC.collector_id

        AND HCP.site_use_id IS NULL

        AND Nvl(AC.collector_id, 1) = Nvl(:B2, Nvl(AC.collector_id, 1))

        AND Nvl(HCA.attribute1, '0') = Nvl(:B3, Nvl(HCA.attribute1, '0'))

        AND Nvl((SELECT Min(accounting_date) FROM  apps.ap_invoice_distributions_all WHERE invoice_id = I.invoice_id), To_date(I.gl_date)) <= To_date(:B7)

        AND ( ( I.cancelled_date IS NOT NULL AND I.cancelled_date > To_date(:B7) ) OR 

( I.cancelled_date IS NULL AND 

( Nvl((SELECT SUM(amount) FROM  apps.ap_invoice_distributions D WHERE invoice_id = I.invoice_id AND D.accounting_date < To_date(:B7)),PS.gross_amount)) - 

 Nvl((SELECT SUM(amount) FROM  apps.ap_invoice_payments P WHERE P.invoice_id = I.invoice_id AND P.accounting_date < To_date(:B7)),0) != 0

) 

)

        AND ( V.segment1 = Nvl(:B1, V.segment1) )

        AND I.invoice_currency_code = Nvl(:B6, I.invoice_currency_code)

        AND Nvl(:B5, 'BOTH') IN ( 'AP', 'BOTH' )

User_3IM5K

Hi,

i need to create one more table with below conditions

cust_account_id, collector_id site_use_id from hz_customer_profiles where site_use_id IS NULL .

evgenyg

Sorry I am not sure I understand your comment.

How many rows in this new table?

You must to know exact numbers and sizes of tables involved in the query.

User_3IM5K

You asked me create new table for hz cust profiles with three coloumns right?

evgenyg

Since I saw

  279783   279783   279783     TABLE ACCESS BY INDEX ROWID BATCHED HZ_CUSTOMER_PROFILES (cr=110032876 pr=0 pw=0 time=1019813237 us starts=354205278 cost=3 size=12 card=1)

 354205278 354205278 354205278      INDEX RANGE SCAN HZ_CUSTOMER_PROFILES_N1 (cr=67243030 pr=0 pw=0 time=572087644 us starts=354205278 cost=2 size=0 card=1)(object id 1259019)

I think that pre-creation of table with only relevant data (and need to check may be make it IOT) may help there.

Later you may decide how to manage it properly, first you need to find proper direction.

So yes, I think you need to create this table

User_3IM5K

Ok. I will create view with relavente data of hz customer profile table

evgenyg

No view, real table which should contain subset of relevant data and columns from HZ_CUSTOMER_PROFILES .

View will not help there, your query already over complicated .

User_3IM5K

hi,

Thank you for valuable support.

You want me to change as below.

evgenyg

No

I want you to create table.

create table test as select cust_account_id, collector_id site_use_id from hz_customer_profiles where site_use_id IS NULL.

and then replace  hz_customer_profiles with test. and run explain plan and the test with trace, you don't need to wait till it finished, first few minutes would be enough

 FROM  ap_payment_schedules_all PS,

        ap_invoices_all I,

        ap_suppliers V,

        po_vendor_sites_all VS,

        hz_cust_accounts_all HCA,

        test HCP,

        ar_collectors AC

User_3IM5K

Ok.I will try.if it is worked, then How can I manage real time Data created in future

evgenyg

if it worked then you may add additional steps to your process, like populate the GTT/regular table with the data, run insert, run report.

Or you may use with clause to materialize it within the query.

User_3IM5K

Hi

I tried, no luck. Same quey in 12.1.3 and 11g database taking 4 min , where as in 12.2.9 and 19c database taking 16 min.

evgenyg

According to stats you provided early the query took well over hour. how it took only 16 minutes this time?

all   count    cpu  elapsed    disk   query  current    rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse    1   0.01    0.02     0     0     0      0

Execute   4  3180.87  5093.66     0 443798000    185     478

Fetch    0   0.00    0.00     0     0     0      0

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total    5  3180.88  5093.68     0 443798000    185     478

No magic there.

1. Compare execution plans.
2. Check out what going on with the run.

What the size of the table that you created from  hz_customer_profiles?

What are run statistics this time?

Show explain pans before and after?

run the select with: /*+ gather_plan_statistics */ 

And show output of: SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format=>'ALLSTATS LAST'));

Start to refactoring and rewrite the query.

You clearly spent the time at "first" MQ session:

  279783   279783   279783     TABLE ACCESS BY INDEX ROWID BATCHED HZ_CUSTOMER_PROFILES (cr=110032876 pr=0 pw=0 time=1019813237 us starts=354205278 cost=3 size=12 card=1)

 354205278 354205278 354205278      INDEX RANGE SCAN HZ_CUSTOMER_PROFILES_N1 (cr=67243030 pr=0 pw=0 time=572087644 us starts=354205278 cost=2 size=0 card=1)(object id 1259019)

So work it out.

Remove cartesian join as well.

Regards

User_3IM5K

Hi there is no cartisian join. Everything has proper joins

User_3IM5K

Iam taking insert statement, which is causing the issue, and trying to execute in old instances through toad,it is taking 4 min where as new instances taking 16 min . What ever I shared last time, it is taking time through report

evgenyg

Insert is no issue. The issue is select.

And there is cartesian join in statistics you provided (see bold).

You send me file that shows that the time of one query is: 5093.68 seconds, this is little bit more then 16 minutes.

Rows (1st) Rows (avg) Rows (max) Row Source Operation

---------- ---------- ---------- ---------------------------------------------------

     0     0     0 LOAD TABLE CONVENTIONAL OIC_ARAP_AGING_REPORT_G_TL (cr=110949500 pr=0 pw=0 time=159473088 us starts=1)

    437    120    437  SORT ORDER BY (cr=110949494 pr=0 pw=0 time=159472366 us starts=1 cost=53076 size=2378 card=1)

    437    120    437  HASH JOIN (cr=110949494 pr=0 pw=0 time=156953991 us starts=1 cost=53075 size=2378 card=1)

     1     1     1   VIEW (cr=110043416 pr=0 pw=0 time=156045958 us starts=1 cost=6933 size=1716 card=4)

     1     1     1   UNION-ALL (cr=110043416 pr=0 pw=0 time=156045957 us starts=1)

     1     1     1    FILTER (cr=110043410 pr=0 pw=0 time=156045746 us starts=1)

  279783   279783   279783    NESTED LOOPS OUTER (cr=110043410 pr=0 pw=0 time=24829240 us starts=1 cost=5458 size=4620 card=33)

  279783   279783   279783     NESTED LOOPS (cr=110043051 pr=0 pw=0 time=24402391 us starts=1 cost=5413 size=1656 card=23)

 354205278 354205278 354205278     MERGE JOIN CARTESIAN (cr=10175 pr=0 pw=0 time=213997116 us starts=1 cost=2630 size=58320 card=972)

   1266    1266    1266      TABLE ACCESS BY INDEX ROWID BATCHED AR_COLLECTORS (cr=510 pr=0 pw=0 time=21080 us starts=1 cost=6 size=14 card=1)

   1266    1266    1266      INDEX SKIP SCAN AR_COLLECTORS_U1 (cr=5 pr=0 pw=0 time=16641 us starts=1 cost=5 size=0 card=1)(object id 1234555)

 354205278 354205278 354205278      BUFFER SORT (cr=9665 pr=0 pw=0 time=139297349 us starts=1266 cost=2624 size=44712 card=972)

  279783   279783   279783      TABLE ACCESS FULL HZ_CUST_ACCOUNTS (cr=9665 pr=0 pw=0 time=192831 us starts=1 cost=2624 size=44712 card=972)

  279783   279783   279783     TABLE ACCESS BY INDEX ROWID BATCHED HZ_CUSTOMER_PROFILES (cr=110032876 pr=0 pw=0 time=1019813237 us starts=354205278 cost=3 size=12 card=1)

 354205278 354205278 354205278      INDEX RANGE SCAN HZ_CUSTOMER_PROFILES_N1 (cr=67243030 pr=0 pw=0 time=572087644 us starts=354205278 cost=2 size=0 card=1)(object id 1259019)

   13203   13203   13203     TABLE ACCESS BY INDEX ROWID BATCHED PER_ALL_PEOPLE_F (cr=359 pr=0 pw=0 time=290734 us starts=279783 cost=3 size=68 card=1)

   13203   13203   13203     INDEX RANGE SCAN PER_PEOPLE_F_PK (cr=321 pr=0 pw=0 time=146639 us starts=279783 cost=1 size=0 card=2)(object id 60190)

     0     0     0    VIEW VW_ORE_780EDBD4 (cr=0 pr=0 pw=0 time=610476 us starts=279782 cost=19 size=0 card=2)

User_3IM5K

Thank you for your analysis

still Iam wondering after ugrade , same select statement is taking 15 min in toad and through report it is taking 80 min

evgenyg

The answer is in explain plans (and runtime explain plans) before and after upgrade

11G to 19C is very much big jump.

The good point that your select is relatively easy to rewrite. Since the issue is well defined.

If I were you I would spend couple of hours to refactor it

User_3IM5K

yes I have seen explain plan in schema browser in toad, cost is less in 19c compared to 11g, even though time taking is more in19c

evgenyg

Coast is function of statistics and another factors. I don't think you can compare cost between 11G and 19C at all. Don't sepnt time on it.

You need to see what the difference between execution plans, see if execution plan is right one (you know your data and tables).

Obviously what I saw in tracefile you uploaded far a way from good execution plan.

what happen when you created table from HZ_CUSTOMER_PROFILES ?