Skip to Main Content
Forums

Analytics Software

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.

Multiple amount load in data management

Ashima RajputMar 26 2019 — edited Apr 1 2019

Hi All,

I need to load data separately for two columns debit and credit through data management into FCCS.

for account 10-000 need to load 121.

for account 100-00 need to load 23423.

How we can load this debit credit load through csv file.

    10-000       Names         Debit       Credit             

    100-00       x                      121         42342                      

    100-01       yz                    234         23423     

    100-02        zzz                 3568       3255

i have gone through this link but it is for fixed format.

https://docs.oracle.com/en/cloud/saas/enterprise-performance-management-common/erpia/displaying_debit_and_credit_columns…

Thanks

Ashima
This post has been answered by user6692921 on Mar 27 2019
Jump to Answer

Comments

Jonathan Lewis
Answer

The Note section tells you:
- PDML is disabled in current session

So it looks as if you haven't executed: "alter session enable parallel DML" - this may be enough to get the performance you want.

Questions: 

How much time do you think it should take to create and insert your 341M rows ?

What indexes are there on the table receiving the data - and how much of the work is maintaining indexes?

Which version or Oracle are you using?

Are you licensed to use the Diagnostic and Performance packs - if so have you used the SQL Monitor feature to get a report of where the time is being spent?

You have several hints in the query - presumably you did that because you think you know what Oracle should be doing: what do you think the execution plan should be.

In passing, your hints include:

  • LEADING(F_AMIS_WORK_ORDER)
  • USE_HASH(F_AMIS_WORK_ORDER)

These two hints are incompatible - if f_amis_work_order is the first table in the join it can never be in a position to be the probe table of a hash join.

You also have the hint

  • PQ_DISTRIBUTE(REP_WORP_WO_DETAIL HASH,HASH)

But the table is the target of the insert, not a table that will be the second table of a join, so the hint isn't doing whatever you think it ought to be doing. You probably intended to use the  "single-table" version of the hint.

Random Observation:

You've got a call to dbms_lob.substr() - it's possible that that's responsible for a lot of the final time of insert if one process (the coordinator) has to call it 341M times.  Apart from sorting out the optimum plan I'd check to see what impact it had on total time and see if there were any way of bypassing a lot of that time (e.g. if the column is often NULL would wrapping the call in a CASE/END that checked for null help; e.g.2 is a basic substr() more efficient in your version than a call to dbms_lob.substr()).

Regards

Jonathan Lewis

Marked as Answer by user1014019 · Sep 27 2020
1 person found this helpful
EdStevens

Also asked at http://orafaq.com/forum/t/206929/

user1014019

Hello Sir,

Thank you very much for the wonderful suggestions.

I have implemented Enable.Parallel DML and now the load is completing in 12 minutes.

Hence marking the issue as closed.

Regards

user1014019

Hello Sir,

I have also removed all the hints and kept only Parallel Hint so that it execute in Parallel.

This issue is solved now.

Jonathan Lewis 
user1014019 wrote:
I have also removed all the hints and kept only Parallel Hint so that it execute in Parallel.
.

That was a sensible move. Presumably most of the hints were put there by someone else who had a specific plan in mind but didn't describe it fully enough in hints.  You might have noted a couple of places where the optimizer did a serial index full scan and then distributed the resulting rows "serial to parallel" by block address this would have been using excess CPU (and causing contention) in inter-process communication but would have been necessary because of hints that FORCED the optimizer to use an index when a parallell tablescan would have been more efficient.

Regards

Jonathan Lewis

1 - 5

Post Details

Added on Mar 26 2019
#business-intelligence-applications, #financial-data-management, #performance-management-applications
13 comments
1,642 views
1 mention