Correct execution plan is not considered for a query

Hello Guys,

We are on Oracle database 10.2.0.3.

I was facing an issue with one of queries wherein the query was taking a long time to execute. After analysis, we realized the query was stuck on scanning a job data table named PS_JOB.

I gathered the stats on the job data table using the following command:

exec DBMS_STATS.GATHER_TABLE_STATS('SYSADM', 'PS_JOB', estimate_percent =>100, cascade => TRUE)

There was no impact and the query again got stuck in processing.

I again built the stats using the following command:

exec DBMS_STATS.GATHER_TABLE_STATS('SYSADM', 'PS_JOB', estimate_percent =>100, method_opt => 'FOR ALL COLUMNS SIZE 1', cascade => TRUE)