Oracle performance issue with Prod Env!

You attached a package that has 5 procedures and one function.  So, what exactly are you doing?

Like everything else, it looks like there is a lot of parallel processing.  Likely you are running a lot more parallel processing than your system can handle.

Yes, the parallel hints may be making performance worse, rather than better.  But,  it is very difficult for us to give you specific suggestions when you haven't given us specific information including:
1. Which procedure in the package you posted is the slowest?
2. What are the table definitions?
3. Which columns are indexed?
4. What are the data volumes in the tables?
5. How many CPUs does your database server have?  And, is this a single-node server, or RAC?
6. How much RAM does the server have and what is the SGA_MAX_SIZE?
7. Do your tables have up-to-date statistics?
8. Have you run this procedure with "trace" on to collect performance-related information?

When I look at the SQL code in the PL\SQL package you posted here, I see lots of things that make performance slower:
1. Applying operators to database column values referenced in "where" clauses like this one: UPPER (ilc.parm_nm (+)) = UPPER (e.parm_nm);
2. Using "does not equal" comparisons in "where" clauses, like this one: "pr.rank_nbr <> 0"
3. Outer joins like this one: WHERE ilc.process_nm (+) = 'IPM_TS_TP'
4.  Removed APPEND hint from all the INSERT statements which load data into STGMGR.GTT_VNDR_ORD table

These are all things to avoid if you want the best performance.  I listed them in the approximate order of their likely impact on performance.

I hope you have approval to post your code on a public website.

I agree with the above comments that we cannot look at a few hundred lines of code and point "here is your problem".

"faster" isn't a tuning goal.  Has this always taken a 3 hours?  If not, what changed?  If so, what is your goal?

I've not had good luck using the MATERIALIZE hint on CTEs.  I would also look at the use of collections.  I saw one that extended one row at a time in a loop.  Extending a collection is pretty expensive.

You need to figure out what specific functions/procedures are taking the most time and focus there.

This is fixed !

Are you willing to tell us how this problem was solved?  That information could help others.

Hi @Mark initially i have worked with followed steps in order to fix the performance bottleneck

1) Procedure PG_IDRP_TS_DOMAIN_PARAM.P_UPD_TS_DOMAIN_PARAM;
It was around 136M records inside superuser.wrk_all_item_loc earlier than 2013. i have archived the records till 2017.

2) Procedure Name : P_LOAD_TS_DOMAIN_PARAM
The INSERT ALL used to loads around 593M Records . Also Archived Unused records from  table stgmgr.ext_ipm_tp_temp_store e and  IPM_LOAD_COLUMNS ilc.

3) Stats are gathered for the table which took around 25 min now it's reduced to 10 minutes.

Please find the below logs for  execution time for this job

*  INFO: (07/31/18-23:24:13): STARTING: DPRPRMI_UPD_TS_DOMAIN_PARAM
*  INFO: (07/31/18-23:24:13): LOGFILE: /dprp/idrppsb/log/DPRPRMI_UPD_TS_DOMAIN_PARAM.20180809232413.log
*  INFO: (07/31/18-23:24:13): Executing /dprp/idrppsb/sql/IDRP_START_LOG.sql
*  INFO: (07/31/18-23:24:14): /dprp/idrppsb/sql/IDRP_START_LOG.sql FINISHED SUCCESSFULLY: 0
*  INFO: (07/31/18-23:24:14): Successfully completed writing to UDT_BATCH_LOG
*  INFO: (07/31/18-23:24:14): --------------------------------------------------------------------------------
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
'DPRPRMI_UPD_TS_DOMAIN_PARAM.sql Started'
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
*  INFO: (08/01/18-02:20:09):                   2901381 rows created
*  INFO: (08/01/18-02:20:09):     Done.
PL/SQL procedure successfully completed.
Elapsed: 02:55:55.01
'DPRPRMI_UPD_TS_DOMAIN_PARAM.sql Completed'
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
*  INFO: (08/01/18-02:20:10): Executing /dprp/idrppsb/sql/IDRP_STOP_LOG.sql
*  INFO: (08/01/18-02:20:10): /dprp/idrppsb/sql/IDRP_STOP_LOG.sql FINISHED SUCCESSFULLY: 0
*  INFO: (08/01/18-02:20:10): Executing /dprp/idrppsb/sql/IDRP_INT_JOB_DURATION.sql
*  INFO: (08/01/18-02:20:10): /dprp/idrppsb/sql/IDRP_INT_JOB_DURATION.sql FINISHED SUCCESSFULLY: 0
******************************************************************
*  SCRIPT TERMINATING
*  SCRIPT:   /dprp/idrppsb/bin/DPRPRMI_UPD_TS_DOMAIN_PARAM.ksh
*  DATE:     08/01/18-02:20:10
*  ENV:      /dprp/idrppsb
* [b] DURATION: 0 Days, 2 Hours, 55 Minutes,00 Seconds[/b]
*  STATUS:   SUCCESS
*****************************************************************





*  INFO: (08/08/18-23:29:10): STARTING: DPRPRMI_UPD_TS_DOMAIN_PARAM
*  INFO: (08/08/18-23:29:10): LOGFILE: /dprp/idrppsb/log/DPRPRMI_UPD_TS_DOMAIN_PARAM.20180809232413.log
*  INFO: (08/08/18-23:29:10): Executing /dprp/idrppsb/sql/IDRP_START_LOG.sql
*  INFO: (08/08/18-23:29:10): /dprp/idrppsb/sql/IDRP_START_LOG.sql FINISHED SUCCESSFULLY: 0
*  INFO: (08/08/18-23:29:10): Successfully completed writing to UDT_BATCH_LOG
*  INFO: (08/08/18-23:29:10): --------------------------------------------------------------------------------
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
'DPRPRMI_UPD_TS_DOMAIN_PARAM.sql Started'
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
*  INFO: (08/09/18-02:20:09):                   2901288 rows created
*  INFO: (08/09/18-02:20:09):     Done.
PL/SQL procedure successfully completed.
Elapsed: 02:20:00:09
'DPRPRMI_UPD_TS_DOMAIN_PARAM.sql Completed'
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
*  INFO: (08/09/18-02:20:10): Executing /dprp/idrppsb/sql/IDRP_STOP_LOG.sql
*  INFO: (08/09/18-02:20:10): /dprp/idrppsb/sql/IDRP_STOP_LOG.sql FINISHED SUCCESSFULLY: 0
*  INFO: (08/09/18-02:20:10): Executing /dprp/idrppsb/sql/IDRP_INT_JOB_DURATION.sql
*  INFO: (08/09/18-02:20:10): /dprp/idrppsb/sql/IDRP_INT_JOB_DURATION.sql FINISHED SUCCESSFULLY: 0
******************************************************************
*  SCRIPT TERMINATING
*  SCRIPT:   /dprp/idrppsb/bin/DPRPRMI_UPD_TS_DOMAIN_PARAM.ksh
*  DATE:     08/09/18-02:20:10
*  ENV:      /dprp/idrppsb
* [b] DURATION: 0 Days, 2 Hours, 20Minutes 39 Seconds[/b]
*  STATUS:   SUCCESS
*****************************************************************

Select allOpen in new window

So Overall i have achieved around 20-30 minutes run-time of the procedure . Still i'm working on to reduce the time as much possible.

Thanks!

Thank you.  Yes, moving older records out of active Oracle tables to archive tables or to an archive system will always help Oracle processes go faster that need to read or process all of the records in the active tables (assuming that you also re-organize the active tables to remove the unused space in them, and rebuild the indexes to also shrink the size of the indexes).

And yes, whenever the number of records in a table is adjusted significantly either up or down, new statistics should be collected.  

On a separate note, I still don't like seeing this "Indian English" word order: "Please find the below logs ...".  Outside of India in other English-speaking countries, the words "above" and "below" are considered adverbs not adjectives.  So English speakers from England, Canada, Australia, USA, etc. do not put those words between "the" and a noun.  We put it them after the noun, like this: "...the logs below...".  The English words "upper" and "lower" are adjectives. They are commonly used between "a" or "the" and a noun.  For example, in a multi-story building, I could say: "He is on an upper floor right now" or "He is on a lower floor".  To use an adverb, I would say: "He is on a floor above me" or "He is on a floor below me".  I would never say: "He is on a below floor".

Thanks a lot @Mark for the guidance, Really Appreciate!

Sometimes i feel hard to translate the" Indian English" to Other "English Speaking Countries", and it sounds weird. Definitely will take care of this.

Thank you once again Everyone for your support and guidance!