query tuning

$ sqlplus
username :
password :*****
sql> set autotrace on
sql>set timing on
sql>

gave u the output here.(used copy & paste)
is there anything wrong.

Hi

I want to know what SCRIPT do you use to gather table and index statistics


Please paste the exact command here

regards
Hrishy

used analyze command to table and index(compute statistics).

Code:

---

analyze table table_name compute statistics.
analyze index index_name compute statistics.

---

Hi

Can you please use dbms_stats to runs the stats on all the tables and indexes
and then post the execution plan using dbms_xplan here

regards
Hrishy

hmm., there is change in cost,etc

Code:

---

Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1459 Card=228198 Bytes=6389544)
  1    0  HASH JOIN (Cost=1459 Card=228198 Bytes=6389544)
  2    1    HASH JOIN (Cost=1010 Card=41099 Bytes=945277)
  3    2      TABLE ACCESS (FULL) OF 'PERSON' (Cost=337 Card=34834 Bytes=348340)
  4    2      TABLE ACCESS (FULL) OF 'JOB_QUEUE' (Cost=654 Card=41100 Bytes=534300)
  5    1    INDEX (FAST FULL SCAN) OF 'REGIMEN_PRESC_QX28' (NON-UNIQUE) (Cost=293 Card=515269 Bytes=2576345)

---

but ur expectation ..?

Hi

I want to see the complete dbms_stats statements

Which you are running against all the tables

Please post it

regards
Hrishy

I have analyzed entire schema,here is the command i used.

exec dbms_stats.gather_schema_stats('schema_name');

you need cascade=> true as well

Hi

You need to use something like

Code:

---

exec dbms_stats.gather_table_stats ( -
ownname          => 'OWN_NAME', -
tabname        => 'TABLE_NAME', -
estimate_percent => 20, -
cascade          => TRUE , -
method_opt      => 'FOR ALL  COLUMNS SIZE AUTO' -
)

---

you have badly written query.. and ofcourse with tables used unncessarily.. it can be as simple as below

Code:

---

SELECT
  DISTINCT
        rp.person_nr,
        FIRST_VALUE(rp.regimen_sequence) OVER (PARTITION BY rp.person_nr, rp.regimen_type_code) regimen_sequence,
        rp.regimen_type_code
FROM    REGIMEN_PRES rp,
        PERSON p
WHERE  regimen_type_code = 'CD'
    AND prescription_status_code= 'FNSH'
    AND rp.person_nr = p.person_nr
    and p.email not like '%test%'
    AND p.person_nr IN    (SELECT  a.person_nr
                            FROM    JOB_QUEUE b
                            WHERE  b.job_queue_type_nr = 4
                                AND b.status_code = 'FNSH'
                                AND (a.status_code = 'ACTV' or
                                    a.status_code = 'CHNG'))
ORDER BY rp.person_nr;

---

Rgds
Abhay