Partition selectivity lost in DB - PL/SQL performance issue

From: Tao Zuo <zuot2000_at_yahoo.com>
Date: 22 Jan 2004 08:48:21 -0800
Message-ID: <7120c31.0401220848.18ceb5e2@posting.google.com>

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 Received on Thu Jan 22 2004 - 10:48:21 CST