Re: Partition selectivity lost in DB - PL/SQL performance issue

Tao Zuo wrote:

> One of our PL/SQL code runs extremely slow lately. The code:
> --------------------
> declare
> v_year_key number:=2002;
> begin
>
> insert into
> table_a
> select
> (...)
> from
> BIG_TABLE, other_tables
> where
> BIG_TABLE.year_key = v_year_key
> and ...;
> end;
> --------------------
> where BIG_TABLE is a partitioned table by year_key (2000,2002,...).
>
> This query will run fast if v_year_key is replaced by a hard-coded
> figure such as 2002.
>
> For some reason, our Oracle DB has lost the partition selectivity
> ability in the PL/SQL above using a parameter in the where-clause, and
> is doing a full table scan.
>
> I know this query ran well before.
>
> THE QUESTION: What would have caused this to happen?
>
> Experts: please help.
>
> Tao

Local or global indexes? Valid? Still there? Current statistics with DBMS_STATS on tables and indexes? How current?

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)