Problem With High PJI

Database

Problem With High PJI

Hi,

I am having a tough time as my query is getting aborted due to abnormal PJI values.

The query in question tries to create a volatile table from another volatile table using a subquery

basically its a create table using a subquery , both these table have the same set of PI defined

The select is kind of complex and invloves numerous case staments and about 20-30 columns and derived columns are calculated

however it all comes from a single voaltile table in the from clause there are no joins invovled at all.

I checked on the explain plan from the view point even there it doesnt show any joins let alone product joins but i am still getting

PJI in excess of 30-40 .

Additionaly i tried collecting stats on the base volatile table reduced the volume of data invovled still it shows high PJI

I am failing to understand why its showing a high PJI index when there are joins invovled at all

Does it have to do something with the select ? and why is that the explain is clean ?

Please guide me in overcoming this

Regards

Rajeev

2 REPLIES

Re: Problem With High PJI

PJI is CPU intensive operations indicator.

As you say, you have a lot of CASEs, which may be at the botom of your problems.

Complex operations may also be CPU intensive and show as high PJI.

Others causes may be:

Duplicate row checks in SET tables,

Many duplicate row hashes in NUPIs.

Agregations, functions (SUBSTR, INDEX...) arithmetic operations...

HTH.

Cheers.

Carlos. 

Re: Problem With High PJI

Hi Carlos,
I was under the impression that a high PJI indicates a notorious product join, thanks for pointing out that
a complex select which utilizes lot of resources may also lead to a high PJI
I was not very sure on changing the select as that would undo the current logic, so instead
we reduced the date range and hence the volume of data that it has to process in one go and it did reduce
the PJI it was on the higher side but was under the norms, we manged to get the entire load done

Thanks
R.Rajeev