SPEXP

Enjoy spexp tool http://valentinnikotin.com/spexp/


Some words on result cache dependencies tracking in 11.2.

Uncategorized
| Feb 7, 2012

First, every result of a function or query for which result cache is enabled has its own dependencies.
Second, all dependencies are tracked while the function/query is being executed.
Third,  the PL/SQL function result cache and the SQL result cache internals are the same.
Fourth, if you have a function within a query, for which result cache hint is used, the dependencies for the query result will include the dependencies on the objects accessed by the function.

This has several consequences.

1. One function may have different dependencies for different input arguments. Let’s look at the following example:

--drop table  (more...)

Comments in hint

Uncategorized
| Jan 31, 2012

 

It seems that CBO can work with the line comment inside sql hints.

Let’s see. I create a table with 3 indexes:

--drop table ttt purge;
create table ttt
(
  a number not null,
  b number not null,
  c number not null
);

insert into ttt
  select 1,1,1
    from dual connect by level <= 10000;
commit;

create index iiia on ttt(a);
create index iiib on ttt(b);
create index iiic on ttt(c);

And then I’m able to switch access paths by commenting:

explain plan for
select /*+
         --full(ttt)
         --index(ttt(c))
         index(ttt(a))
         --index(ttt(b))
       */
       count(*)
from ttt;
select * from table(dbms_xplan.display);

 (more...)