Re: Oracle optimizer problem

Sybrand mentioned the missing data, which is running an explain plan for the two queries. The documentation has clear examples and the necessary scripts for creating, filling, and querying a plan_table. Since the literal value and the variable are the effectively the same, it would be interesting to see how Oracle cached the two queries.

If you need the parameterized version of the query, I would recommend doing the obvious, which is expanding your temp tablespace. If your temp space is not locally managed, I would recommend switching over. However, this would mean rebuilding your database.

A write to disk means the query is probably doing sorting in one query but not in the other. If you haven't updated statistics recently, I would recommend analyzing all the tables and indexes for the tables involved. I would also recommend running a utlbstat and a utlestat to see where you database is choked. It sounds like you have some init.ora tuning to do as well.

Good Luck!

--

~~~~~~~~~~~~~~~~
Chris Weiss
mailto:chris_at_hpdbe.com
www.hpdbe.com
High Performance Database Engineering
Available for long and short term contracts
~~~~~~~~~~~~~~~~


"Don Del Grande" <delgrandedp_at_navsea.navy.mil> wrote in message
news:d210e76a.0205020735.626b7c54_at_posting.google.com...


> When I run the following query:

>

> DECLARE

>   vd_data_dt  DATE;

> BEGIN

>     vd_data_dt = TO_DATE('05/02/2002', 'MM/DD/YYYY');

>     INSERT INTO mytable

>       (<fields>)

>     SELECT <fieldlist>

>     FROM table1 t1, table2 t2,...

>     WHERE ...

>     AND t1.data_dt > vd_data_dt;

>

> it returns with a "max extents exceeded on temp tablespace" error.

> However, if I replace the last line with:

>

>     AND t1.data_dt > TO_DATE('05/02/2002', 'MM/DD/YYYY')

>

> then there is no problem.

> Any ideas why using a local variable rather than something returned

> from TO_DATE would cause the query to change so dramatically?  (Note

> that removing the entire last line works as well.)