Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Bhogesh.

Asked: September 06, 2017 - 9:56 am UTC

Last updated: September 06, 2017 - 2:32 pm UTC

Version: 12.1

Viewed 1000+ times

You Asked

we are in the process of performance tuning , the below program is taking time, Please guide us to tune further or can we write any other way to improve the performance,

CREATE OR REPLACE TYPE "RECO_FE" FORCE AS OBJECT
(
    o_o_id                    VARCHAR2(18 CHAR),
    o_gl_no                VARCHAR2(35 CHAR),
    o_p_key               VARCHAR2(5 CHAR),
    o_p_name              VARCHAR2(35 CHAR),
    o_c_b_sum        NUMBER(18,3),
    o_d_b_sum         NUMBER(18,3),
    o_o_b_sum     NUMBER(18,3),
    o_c_sum       NUMBER(18,3),
    o_d_sum        NUMBER(18,3),
    o_de_sum         NUMBER(18,3)
);
/



CREATE OR REPLACE TYPE "RECO_FI_COL" FORCE AS TABLE OF RECO_FE;
/

CREATE OR REPLACE PROCEDURE TA_RE_FILE 
(
    pi_id IN VARCHAR2, 
    pi_no IN NUMBER,
    pi_reco_coll OUT RECO_FI_COL
)
IS 
    V_OBJ_RECONCI           RECO_FI_COL;
    V_OBJ_RECO_COLL       RECO_FI_COL:= RECO_FI_COL();
    
BEGIN


    SELECT RECO_FE(EX_TA_O_ID,TA_g_AC_CRDT, TA_pr_key, TA_pr_na, SUM(TA_CR_BA_LOC),NULL, NULL, NULL, NULL, NULL)BULK COLLECT INTO V_OBJ_RECONCI
    FROM TA_no_AC
    WHERE FK_TARB_BANK_REPORT_ID=pi_no
        AND EX_TA_O_ID=pi_id
        AND TA_g_AC_CRDT IS NOT NULL
    GROUP BY EX_TA_O_ID, TA_g_AC_CRDT, TA_pr_key, TA_pr_na;
    
    V_OBJ_RECO_COLL := V_OBJ_RECO_COLL MULTISET UNION V_OBJ_RECONCI;


    SELECT RECO_FE(EX_TA_O_ID,TANA_GL_ACCT_DBT, TA_pr_key, TA_pr_na,NULL, SUM(TA_DE_BA_LOC), NULL, NULL, NULL, NULL) BULK COLLECT INTO V_OBJ_RECONCI
    FROM TA_no_AC
    WHERE FK_TARB_BANK_REPORT_ID=pi_no
        AND EX_TA_O_ID=pi_id
        AND TANA_GL_ACCT_DBT IS NOT NULL
    GROUP BY EX_TA_O_ID, TANA_GL_ACCT_DBT, TA_pr_key, TA_pr_na;
    
    V_OBJ_RECO_COLL := V_OBJ_RECO_COLL MULTISET UNION V_OBJ_RECONCI;


    SELECT RECO_FE(EX_TA_O_ID, TANA_GL_ACCT_OVERDRFT,TA_pr_key, TA_pr_na, NULL,NULL, SUM(TA_OVE_BAL_LOC), NULL, NULL, NULL)BULK COLLECT INTO V_OBJ_RECONCI
    FROM TA_no_AC
    WHERE FK_TARB_BANK_REPORT_ID=pi_no
        AND EX_TA_O_ID=pi_id
        AND TANA_GL_ACCT_OVERDRFT IS NOT NULL
    GROUP BY EX_TA_O_ID, TANA_GL_ACCT_OVERDRFT, TA_pr_key, TA_pr_na;
    
    V_OBJ_RECO_COLL := V_OBJ_RECO_COLL MULTISET UNION V_OBJ_RECONCI;

 
    pi_reco_coll := V_OBJ_RECO_COLL;
    
END;

and Chris said...

You've got almost the same query run three times. Then unioning the results in PL/SQL.

Can't you have one SQL statement that unions these together instead?

For us to give more meaningful help, we're going to need to see details of what exactly is going on and how long it takes. i.e. execution (not explain!) plans for your SQL, run times for your PL/SQL, etc.

To get the plans, trace your session and run your code. Then parse the trace files witk TKPROF. You can find instructions on how to do this at:

https://blogs.oracle.com/sql/how-to-create-an-execution-plan#tkprof

It may also be worth profiling the PL/SQL to see if those multiset unions are what's taking the time. Read about this at:

https://oracle-base.com/articles/11g/plsql-hierarchical-profiler-11gr1

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Another suggestion

Eduard Turea, September 06, 2017 - 11:00 am UTC

Hi Chris,

Your answer is on the spot. When is about performance, the knowledge of the business is always useful and provides the insights to a good optimizing strategy.

As per provided example, it seems that the right solution would be to use the union all at the query level and not with the objects. From my experience, when the objects are very large the performance takes a hit. If the business logic does not permit the union as you suggested, I would recommend to use something like this:
select *
BULK COLLECT INTO V_OBJ_RECO_COLL 
from table(V_OBJ_RECONCI1) 
UNION ALL
select *
from table(V_OBJ_RECONCI2)
UNION ALL
select *
from table(V_OBJ_RECONCI3)


https://eturea.wordpress.com/2017/07/10/corner-case-of-under-performing-objects-multiset-operation/

Other suggesions using GROUPING SETS

Rajeshwaran, Jeyabal, September 06, 2017 - 1:11 pm UTC

Consider rewriting the above three SQL's into a single SQL using grouping sets.

not sure if that works, since don't see any create table/inserts script available to play with.

select EX_TA_O_ID, TA_g_AC_CRDT,TANA_GL_ACCT_DBT,TANA_GL_ACCT_OVERDRFT,TA_pr_key,TA_pr_na, 
    case when TA_g_AC_CRDT is not null then SUM(TA_CR_BA_LOC)
      when TANA_GL_ACCT_DBT is not null then SUM(TA_DE_BA_LOC)
      when TANA_GL_ACCT_OVERDRFT IS NOT NULL then SUM(TA_OVE_BAL_LOC) end as tot_val
from TA_no_AC
WHERE FK_TARB_BANK_REPORT_ID=pi_no
AND EX_TA_O_ID=pi_id
and ( TA_g_AC_CRDT IS NOT NULL or 
   TANA_GL_ACCT_DBT IS NOT NULL or 
   TANA_GL_ACCT_OVERDRFT IS NOT NULL )
group by grouping sets( (EX_TA_O_ID, TA_g_AC_CRDT, TA_pr_key, TA_pr_na) ,
   (EX_TA_O_ID, TANA_GL_ACCT_DBT, TA_pr_key, TA_pr_na) ,
   (EX_TA_O_ID, TANA_GL_ACCT_OVERDRFT, TA_pr_key, TA_pr_na) )

Chris Saxon
September 06, 2017 - 2:30 pm UTC

I don't think that quite does what you think it does...

Bhogesh waraprasad, September 06, 2017 - 1:54 pm UTC

In the First query the not null constraints checking and second query not constraint checking is difference etc, how to combine the multiple query as one sql query. Please guide us.

1)TA_g_AC_CRDT IS NOT NULL
2)TANA_GL_ACCT_DBT IS NOT NULL
3)TANA_GL_ACCT_OVERDRFT IS NOT NULL

Chris Saxon
September 06, 2017 - 2:32 pm UTC

Just stick union (all) between all your queries:

select first query
union 
select second query
union 
select third query


And bulk collect the results of that. There may be other ways to rewrite this, but I'm not sure without seeing the data and table definitions!

Bhogesh waraprasad, September 06, 2017 - 2:04 pm UTC

In the first query ,second query and third query the where clause is different,how to combine as one query?. Please guide us.

1)TA_g_AC_CRDT IS NOT NULL
2)TANA_GL_ACCT_OVERDRFT IS NOT NULL
3) TANA_GL_ACCT_DBT IS NOT NULL
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database