Performance : ROWID join after JI hit

Database
Enthusiast

Performance : ROWID join after JI hit

Hi All,

I am trying to debug a query which is taking a lot of time.

Following objects are involved in the query :

person_mst table :
person_id , first_name , last_name , age , address_id , contact_id , version_nbr , create_dt
PI : person_id
PPI : create_dt (weekly)

JI on person_mst :
first_name , last_name , age , version_nbr

NUSI on person_mst :
first_name , last_name

select a.person_id , b.first_name , a.last_name , a.age , a.create_dt , a.address_id , a.contact_id
from person_mst a inner join names_list_vt b on
a.first_name = b.first_name
and a.create_dt between '2015-01-01' and '2015-02-02';

The names_list_vt contains a list of distinct first names.

In the above query, the PE decides to hit the JI first and retrieve the row_id based on the condition of fisrt_name.

It then hits the base table doing a rowid join to fetch the results leveraging the PPI as well.

However, the rowid join is very slow and consumes a lot of time and the paramater AMP CPU is very high as well.

I am looking for a better way to execute this query. Looks like hitting the JI is not the best option here.

Note: I cannot share the exact the structure and other details due to compliance concerns.

Appreciate your help in this regard.

3 REPLIES
Junior Contributor

Re: Performance : ROWID join after JI hit

Hi Tarun,

did you check statistics?

Any missing stats according to DIAGNOSTIC HELPSTATS ON FOR SESSION?

Enthusiast

Re: Performance : ROWID join after JI hit

Hi ,

The statistics are updated on participating objects.

Enthusiast

Re: Performance : ROWID join after JI hit

Just FYI that Similar behaviour is observed if i hit a SI.

The rowid join takes a long time to complete.