Re: Cursor Invalidation after "alter session" command...

From: Andy Hassall <andy_at_andyh.co.uk>
Date: Mon, 08 Nov 2004 21:57:52 +0000
Message-ID: <ojovo058oe3bo72220rra5l5mur56emgo1@4ax.com>

On Sat, 06 Nov 2004 18:20:03 GMT, Oradba Linux <oradba_linux_at_comcast.net> wrote:

>Matt wrote:
>> Does anyone know whether issuing an 'alter session..' command will
>> invalidate cursors.
>>
>> i.e. if I run
>>
>> SQL> alter session set optimizer_index_caching=90;
>>
>> Will I need to flush the shared pool to see the effects of running the
>> same statement with this new optimizer parameter..?
>>
>> (8.1.7.4.1 Windows and 9.2.0.4 Windows)
>
>This will not invalidate cursors. You can flush shared_pool if you want.
>But if you are testing a query to make sure if the optimizer uses a
>different execution plan, then you can change something in the query to
>force re-parsing the query.

That doesn't seem to match up with what I'm seeing, unless I'm misinterpreting something. In the below I set optimizer_index_cost_adj, mainly because it's easier to get a plan change out of it. The statements are exactly the same both times, but yield two different execution plans. Doesn't this imply it's invalidating the execution plan? Else it'd get the cached plan and have the same plan both times? Or is there a distinction between "invalidates cursors" and "causes existing cached plans to be ignored and causes a new hard parse" that I'm missing? (Since after the below, there are both plans in v$sql_area with the same sql_address/hash_value but differing child_number values)

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.5.0 - Production

SQL> set pagesize 0
SQL> alter session set optimizer_index_cost_adj = 10;

Session altered.

SQL> delete from plan_table;

2 rows deleted.

SQL> explain plan for select x from t where x between 1 and 5000;

Explained.

SQL> select * from table(dbms_xplan.display());

|   0 | SELECT STATEMENT     |             |  4000 | 16000 |     2   (0)|
|*  1 |  INDEX RANGE SCAN    | T_PK        |  4000 | 16000 |    14   (8)|
-------------------------------------------------------------------------

Predicate Information (identified by operation id):

1 - access("T"."X">=1 AND "T"."X"<=5000)

12 rows selected.

SQL> alter session set optimizer_index_cost_adj = 100;

Session altered.

SQL> delete from plan_table;

2 rows deleted.

SQL> explain plan for select x from t where x between 1 and 5000;

Explained.

SQL> select * from table(dbms_xplan.display());

|   0 | SELECT STATEMENT     |             |  4000 | 16000 |     7  (15)|
|*  1 |  TABLE ACCESS FULL   | T           |  4000 | 16000 |     7  (15)|
-------------------------------------------------------------------------

Predicate Information (identified by operation id):

1 - filter("T"."X"<=5000 AND "T"."X">=1)

12 rows selected.

SQL> rollback;

Rollback complete.

-- 
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool

Received on Mon Nov 08 2004 - 15:57:52 CST