Peformance tuning Query taking more time

Database
Enthusiast

Peformance tuning Query taking more time

Hi Experts ,

I aplogize for such a big para but i thought more info might help .

Could you please help me I am Struggling to reduce a time taken by query .

I have one table which has 2 Billion record . Finally only 26k gets deleted . Query is taking 3 hours to completed  :(.

Query I am using is ( PI of both the tables TICN and TIC is  Column A  )   ( 50% SKewness on column B I am not sure whether this could be the reason if it is please comment on this too )

DEL FROM TIC

WHERE EXISTS

( SEL 1 FROM TICN

WHERE      TICN.A = TIC.A

AND TICN.B = TIC.B

AND TICN.C <> TIC.C

AND TICN.B = 36

)

AND TIC.B = 36

;

Explain plan looks like -:

We do an all-AMPs JOIN step from TIC by way of an

     all-rows scan with a condition of ("(B

     (FLOAT, FORMAT '-9.99999999999999E-999'))= 3.60000000000000E 001"),

     which is joined to TICN by way of an all-rows

     scan with a condition of ("(TICN.B (FLOAT,

     FORMAT '-9.99999999999999E-999'))= 3.60000000000000E 001").

     TIC and TICN are joined using an

     inclusion merge join, with a join condition of (

     "(TICN.C <>

     TIC.C) AND

     ((TICN.B = TIC.B) AND

     ((TICN.A = TIC.A) AND

     ((NOT (TIC.A ISNULL )) AND

     ((TIC.B (FLOAT, FORMAT

     '-9.99999999999999E-999'))= 3.60000000000000E 001 ))))").  The

     input table TICN will not be cached in memory.

     The result goes into Spool 2 (all_amps), which is built locally on

     the AMPs.  Then we do a SORT to order Spool 2 by the hash code of

     (TIC.ROWID) the sort key in spool field1 eliminating

     duplicate rows.  The size of Spool 2 is estimated with no

     confidence to be 19 rows (342 bytes).  The estimated time for this

     step is 0.01 seconds.

  5) We do an all-AMPs MERGE DELETE to TIC from Spool 2

     (Last Use) via the row id.  The size is estimated with no

     confidence to be 19 rows.  The estimated time for this step is

     2 Hour 36 Minutes .

Now I changed my query to

DEL FROM TIC

WHERE

TICN.A = TIC.A

AND

TICN.B = TIC.B

AND

TICN.C <> TIC.C

AND

TICN.B= 36 ;

Now my explain plan

2) Next, we lock a distinct DB_REPRISE."pseudo table" for write on a

RowHash to prevent global deadlock for DB_REPRISE.TIC.

3) We lockTICN for read, and we lock

TIC for write.

4) We do an all-AMPs MERGE DELETE to TIC from

TICN by way of a RowHash match scan.

The size is estimated with low confidence to be 56 rows. The

estimated time for this step is 10.11 seconds.

 

Although Second plan looks better to me but still it takes 3.5  Hour and When I took stats on PI column A time in explain plan increased to 5 hours . Please comments which plan looks better to you .

Although join on PI with stats its showing more time in explain . Could you please help me reducing the query time .

Thanks in advance .

 Monk@Work ;)

4 REPLIES
Junior Contributor

Re: Peformance tuning Query taking more time

Hi Vivek,

what's the PI/partitioning of both tables?

Are there any Secondary Indexes on TIC?

And you're using the wrong datatype for column B: according to the explain it's a (VAR)CHAR, but you use a numeric value in WHERE. Should probably be B= '36' instead.

Dieter

Enthusiast

Re: Peformance tuning Query taking more time

Hi Dieter ,

Thanks for your reply .

I changed the datatype and now it's less time 1.5 hour :) .

Do you think that query taking more time just because of this internal casting ?

What are your comments on second explain plan i posted . Isnt that look better ?

PI for both the tables is Column A .There is no partitioning on any of the table or any secondry index .

Thanks in advance for your help .




Enthusiast

Re: Peformance tuning Query taking more time

This is OBIEE generated query as per business joins; it was running for long time, I did some kind of initial tuning but still it runs for a long time, would you please help me to tune this query

select 
sum(case when T66199."PER_NAME_MONTH" = '2014 / 03' then T298388."NUM_JC_CLS" end ) ,
sum(case when T66199."PER_NAME_MONTH" = '2014 / 03' then A."NUM_JC_CLS" end )
from
"W_DAY_D" T66199 ,
"WC_JOBCARD_A" T298388,
"WC_JOBCARD_A" A
where ( T66199."YEAR_AGO_WID" = T298388."DATE_WID"
and T66199."TWO_YR_AGO_WID" = A."DATE_WID"

INDEX's and STATS are gathered as we've mentined below.

CREATE  INDEX  W_DAY_D_SI ( YEAR_AGO_WID) ON DEV_OLAP.W_DAY_D;
COLLECT STATS ON DEV_OLAP.W_DAY_D INDEX (YEAR_AGO_WID);

CREATE INDEX W_DAY_D_SI_1 ( TWO_YR_AGO_WID) ON DEV_OLAP.W_DAY_D;
COLLECT STATS ON DEV_OLAP.W_DAY_D INDEX (TWO_YR_AGO_WID);

COLLECT STATISTICS WC_JOBCARD_A COLUMN DATE_WID;
COLLECT STATISTICS W_DAY_D COLUMN TWO_YR_AGO_WID;
Enthusiast

Re: Peformance tuning Query taking more time

OBIEE has generated two queries, this was one of them which would not give good result. They refreshed same report with content level changes at RPD, Now it refreshing report for a seconds.