Category Archives: documentation

Laterals: is (+) documented for laterals?

Posted on by Posted in bug, CBO, documentation, oracle 1,539 Page views Leave a comment

I know this syntax for a long time, since when lateral() was not documented yet, but recently I found a bug: the following query successfully returns 1 row:

with a as (select level a from dual connect by level<10)
    ,b as (select 0 b from dual)
    ,c as (select 0 c from dual)
select
  *
from a,
     lateral(select * from b where a.a=b.b)(+) bb
     --left outer join c on c.c=bb.b
where a=1;

         A          B
---------- ----------
         1

But doesn’t if we uncomment “left join”:

with a as (select level a from dual connect by level<10)
    ,b as (select 0 b from dual)
    ,c as (select 0 c from dual)
select
  *
from a,
     lateral(select * from b where a.a=b.b)(+) bb
     left outer join c on c.c=bb.b
where a=1;

no rows selected

And outer apply works fine:

with a as (select level a from dual connect by level<10)
    ,b as (select 0 b from dual)
    ,c as (select 0 c from dual)
select
  *
from a
     outer apply (select * from b where a.a=b.b) bb
     left outer join c on c.c=bb.b
where a=1;

         A          B          C
---------- ---------- ----------
         1

RESULT_CACHE: run-time dependency tracking

Posted on by Posted in documentation, oracle, result_cache 1,892 Page views Leave a comment

As you know, since 11.2 “relies_on” clause was deprecated and oracle tracks dependencies at runtime now.

Test function and tables

[sourcecode language=”sql”]
create or replace function f_without_deps(p_tab varchar2) return varchar2
as
res varchar2(30);
begin
execute immediate ‘select ”’||p_tab||”’ from ‘||p_tab||’ where rownum=1′ into res;
return res;
end;
/
create table a as select ‘a’ a from dual;
create table b as select ‘b’ b from dual;
create view v_ab as select a,b from a,b;
[/sourcecode]

[collapse]

And it works fine with normal tables:
v_ab

[sourcecode language=”sql”]
SQL> exec :p_tab:=’v_ab’;

PL/SQL procedure successfully completed.

SQL> call DBMS_RESULT_CACHE.flush();

Call completed.

SQL> select/*+ result_cache */ f_without_deps(:p_tab) result from dual;

RESULT
———-
v_ab

1 row selected.

SQL> select name,bucket_no, id,type,status,pin_count,scan_count,invalidations from v$result_cache_objects o order by type,id;

NAME BUCKET_NO ID TYPE STATUS PIN_COUNT SCAN_COUNT INVALID
—————————————————————– ———- — ———- ——— ———- ———- ——-
XTENDER.F_WITHOUT_DEPS 1579 0 Dependency Published 0 0 0
XTENDER.V_AB 3127 2 Dependency Published 0 0 0
XTENDER.B 778 3 Dependency Published 0 0 0
XTENDER.A 464 4 Dependency Published 0 0 0
select/*+ result_cache */ f_without_deps(:p_tab) result from dual 1749 1 Result Published 0 0 0
[/sourcecode]

[collapse]

But don’t forget that the result_cache also caches such functions with the objects, that usually should not be cached, and such objects will not be listed in the result_cache dependencies list:
v$database

[sourcecode language=”sql”]
SQL> exec :p_tab:=’v$database’;

PL/SQL procedure successfully completed.

SQL> call DBMS_RESULT_CACHE.flush();

Call completed.

SQL> select/*+ result_cache */ f_without_deps(:p_tab) result from dual;

RESULT
———-
v$database

1 row selected.

SQL> select name,bucket_no, id,type,status,pin_count,scan_count,invalidations from v$result_cache_objects o order by type,id;

NAME BUCKET_NO ID TYPE STATUS PIN_COUNT SCAN_COUNT INVALID
—————————————————————– ———- — ———- ——— ———- ———- ——-
XTENDER.F_WITHOUT_DEPS 772 0 Dependency Published 0 0 0
PUBLIC.V$DATABASE 1363 2 Dependency Published 0 0 0
select/*+ result_cache */ f_without_deps(:p_tab) result from dual 2283 1 Result Published 0 0 0

3 rows selected.
[/sourcecode]

[collapse]
As you can see, there is only dependency on public synonym V$DATABASE, but not real base fixed X$-tables.
SYS.OBJ$

[sourcecode language=”sql”]
SQL> exec :p_tab:=’sys.obj$’;

PL/SQL procedure successfully completed.

SQL> call DBMS_RESULT_CACHE.flush();

Call completed.

SQL> select/*+ result_cache */ f_without_deps(:p_tab) result from dual;

RESULT
———-
sys.obj$

1 row selected.

SQL> select name,bucket_no, id,type,status,pin_count,scan_count,invalidations from v$result_cache_objects o order by type,id;

NAME BUCKET_NO ID TYPE STATUS PIN_COUNT SCAN_COUNT INVALID
—————————————————————– ———- — ———- ——— ———- ———- ——-
XTENDER.F_WITHOUT_DEPS 3922 0 Dependency Published 0 0 0
select/*+ result_cache */ f_without_deps(:p_tab) result from dual 3753 1 Result Published 0 0 0

2 rows selected.
[/sourcecode]

[collapse]
The results were cached and the dependencies do not include system objects.
We easily check that the queries with any table in SYS schema or with sysdate,systimestamp,current_date,current_timestamp,dbms_random will not be cached:
SYS tables

[sourcecode language=”sql”]
SQL> select/*+ result_cache */ current_scn result from v$database;

RESULT
———-
##########

1 row selected.

SQL> select name,bucket_no, id,type,status,pin_count,scan_count,invalidations from v$result_cache_objects o order by type,id;

no rows selected

SQL> explain plan for select/*+ result_cache */ * from sys.obj$;

PLAN_TABLE_OUTPUT
————————————————————————–
Plan hash value: 2311451600

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 87256 | 7328K| 296 (1)| 00:00:04 |
| 1 | TABLE ACCESS FULL| OBJ$ | 87256 | 7328K| 296 (1)| 00:00:04 |
————————————————————————–
[/sourcecode]

[collapse]
Note that there is no “RESULT CACHE” line.
And even if create own tables in SYS schema(don’t do it 🙂), they will not be cached :
SYS.V_AB

[sourcecode language=”sql”]
SYS> create table a as select ‘a’ a from dual;
SYS> create table b as select ‘b’ b from dual;
SYS> create view v_ab as select a,b from a,b;
SYS> grant select on v_ab to xtender;

XTENDER> explain plan for select/*+ result_cache */ * from sys.v_ab;
PLAN_TABLE_OUTPUT
—————————————————————————–
Plan hash value: 215283502

—————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 6 | 4 (0)| 00:00:01 |
| 1 | MERGE JOIN CARTESIAN| | 1 | 6 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | A | 1 | 3 | 2 (0)| 00:00:01 |
| 3 | BUFFER SORT | | 1 | 3 | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | B | 1 | 3 | 2 (0)| 00:00:01 |
—————————————————————————–

Note
—–
– dynamic sampling used for this statement (level=2)
[/sourcecode]

[collapse]

But sys_context and userenv will be cached successbully:
sys_context

[sourcecode language=”sql”]
SQL> explain plan for select/*+ result_cache */ sys_context(‘userenv’,’os_user’) from dual;

PLAN_TABLE_OUTPUT
—————————————————————————————
Plan hash value: 1388734953

—————————————————————————————
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
—————————————————————————————
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | RESULT CACHE | 267m2hcwj08nq5kwxcb0nb2ka8 | | | |
| 2 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
—————————————————————————————

Result Cache Information (identified by operation id):
——————————————————

1 – column-count=1; attributes=(single-row); parameters=(sys_context);
name="select/*+ result_cache */ sys_context(‘userenv’,’os_user’) from dual"

14 rows selected.
[/sourcecode]

[collapse]
userenv

[sourcecode language=”sql”]
SQL> explain plan for select/*+ result_cache */ userenv(‘instance’) from dual;

PLAN_TABLE_OUTPUT
—————————————————————————————
Plan hash value: 1388734953

—————————————————————————————
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
—————————————————————————————
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | RESULT CACHE | dxzj3fks1sqfy35shbbst4332h | | | |
| 2 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
—————————————————————————————

Result Cache Information (identified by operation id):
——————————————————

1 – column-count=1; attributes=(single-row); parameters=(sys_context);
name="select/*+ result_cache */ userenv(‘instance’) from dual"
[/sourcecode]

[collapse]

SYS_OP_MAP_NONNULL is in the documentation now

Posted on by Posted in 12c, documentation, oracle, undocumented 4,315 Page views Leave a comment

Interesting, that SYS_OP_MAP_NONNULL appeared in the Oracle 12c documentation: Choosing Indexes for Materialized Views

Lazy tip: By the way, with length limitations, we can also use documented dump function:

SQL> with
  2    t(a,b) as (
  3               select *
  4               from table(ku$_vcnt(null,'FF','A'))
  5                   ,table(ku$_vcnt(null,'FF','B'))
  6              )
  7  select
  8      a,b
  9     ,case when sys_op_map_nonnull(a) = sys_op_map_nonnull(b) then '=' else '!=' end comp1
 10     ,case when dump(a,1017)          = dump(b,1017)          then '=' else '!=' end comp2
 11     ,sys_op_map_nonnull(a) s_o_m_n_a
 12     ,sys_op_map_nonnull(b) s_o_m_n_b
 13     ,dump(a,  17) dump_a
 14     ,dump(b,  17) dump_b -- it is preferably sometimes to use 1017 - for charset showing
 15  from t;

A     B     COMP1 COMP2 S_O_M_N_A  S_O_M_N_B  DUMP_A                DUMP_B
----- ----- ----- ----- ---------- ---------- --------------------- ---------------------
            =     =     FF         FF         NULL                  NULL
      FF    !=    !=    FF         464600     NULL                  Typ=1 Len=2: F,F
      B     !=    !=    FF         4200       NULL                  Typ=1 Len=1: B
FF          !=    !=    464600     FF         Typ=1 Len=2: F,F      NULL
FF    FF    =     =     464600     464600     Typ=1 Len=2: F,F      Typ=1 Len=2: F,F
FF    B     !=    !=    464600     4200       Typ=1 Len=2: F,F      Typ=1 Len=1: B
A           !=    !=    4100       FF         Typ=1 Len=1: A        NULL
A     FF    !=    !=    4100       464600     Typ=1 Len=1: A        Typ=1 Len=2: F,F
A     B     !=    !=    4100       4200       Typ=1 Len=1: A        Typ=1 Len=1: B

9 rows selected.

Bug in documentation about dbms_stats.gather_schema_stats or in the dbms_stats itself

Posted on by Posted in bug, CBO, documentation, oracle, statistics, undocumented 2,232 Page views 2 Comments

Recently I had to gather pending stats with Object Filter List by several objects, so I copied example from documentation and was surprised: instead of gather stats by specified filter list, oracle started to gather stats for all tables in current_schema! And ‘filter list’ applies only with dbms_stats.GATHER_DATABASE_STATS

UPD: Jared Still gave link to registered bug id, which i couldn’t find in MOS before:

Bug 12754926 – DBMS_STATS.gather_schema_stats does not work with an obj_filter_list parameter specified [ID 12754926.8]
Modified:04-Jan-2012 Type:PATCH Status:PUBLISHED
This issue is fixed in 11.2.0.4 (Future Patch Set)

Little example

[sourcecode language=”sql”]
SQL> exec dbms_stats.delete_schema_stats(‘HR’);

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.31
SQL> begin
2 for r in (select table_name
3 from dba_tables t
4 where t.owner=’XTENDER’
5 and table_name like ‘TMP%’
6 )loop
7 dbms_stats.delete_table_stats(‘XTENDER’,r.TABLE_NAME);
8 end loop;
9 end;
10 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.31
SQL> select
2 owner
3 ,table_name
4 ,num_rows
5 ,last_analyzed
6 from dba_tab_statistics t
7 where owner=’XTENDER’ and table_name like ‘TMP%’
8 or owner=’HR’;

OWNER TABLE_NAME NUM_ROWS LAST_ANALYZED
—————————— —————————— ———- ——————-
HR COUNTRIES
HR DEPARTMENTS
HR EMPLOYEES
HR JOBS
HR JOB_HISTORY
HR LOCATIONS
HR REGIONS
XTENDER TMP
XTENDER TMP1
XTENDER TMP_CONTENT
XTENDER TMP_DATA
XTENDER TMP_DOCUMENT
XTENDER TMP_EXCH

13 rows selected.

Elapsed: 00:00:00.11
SQL> col dt new_value dt
SQL> select sysdate dt from dual;

DT
——————-
2013-01-27 00:30:21

1 row selected.

Elapsed: 00:00:00.00
SQL> DECLARE
2 filter_lst DBMS_STATS.OBJECTTAB := DBMS_STATS.OBJECTTAB();
3 BEGIN
4 filter_lst.extend(2);
5 filter_lst(1).ownname := ‘XTENDER’;
6 filter_lst(1).objname := ‘TMP%’;
7 filter_lst(2).ownname := ‘HR’;
8 DBMS_STATS.GATHER_SCHEMA_STATS(NULL, obj_filter_list => filter_lst,
9 options => ‘gather’);
10 END;
11 /

PL/SQL procedure successfully completed.

Elapsed: 00:03:16.89
SQL> select owner,table_name
2 from dba_tables t
3 where t.LAST_ANALYZED>timestamp’&dt’+0;

OWNER TABLE_NAME
—————————— ——————————
XTENDER TT
XTENDER TR_LOG
XTENDER IOT1
…[skipped 171 rows]…
XTENDER DEPARTMENTS

175 rows selected.

Elapsed: 00:00:01.04
[/sourcecode]

[collapse]

PS. Also there is a typo in the example from oracle documentation: ‘gather_stale’ instead of ‘gather stale’ – underline instead of blank space.

Deceptive commit after select from dblink.

Posted on by Posted in commit, documentation, oracle, remote statements, trace, transactions 3,389 Page views Leave a comment

Recently on our russian forum we discussed about distributed transaction: is “insert /*+ append */ into x select * from t@dblink” a distributed transaction or not?
According to the documentation – no:

A distributed transaction includes one or more statements that, individually or as a group, update data on two or more distinct nodes of a distributed database

And Tom Kyte said the same:

In the following, we do not have a distributed transaction when we just insert /*+ append */ into local_table select * from remote@table; – we just have a single site transaction.

But v$global_transaction(but v$transaction), another part of documentation and v$lock(type=’DX’) assure that it is.

Distributed statement: A statement that accesses data on two or more distinct nodes/instances of a distributed database.
A remote statement accesses data on one remote node of a distributed database.

Who is right?

But it not so interesting as my another finding: commit after simple “select * from dblink” would be “read-only” commit on local db, but on remote it became “read-only” rollback! Except cases when there was any dml in that transaction(with no difference local or not). But how Oracle determining: read-only or not, execute commit or rollback?

Yet another interesting thing: If we do a few times “commit” after “select from dblink”, then, as expected, rollback will be executed only once on remote. But when we closing our session, there is another commit on remote(real read-only commit).

Tests below: Continue reading→

Link to the online unwrapper in the official Oracle documentation

Posted on by Posted in curious, documentation, oracle 5,887 Page views 3 Comments

Of course, everyone knows about “unwrappers” and I guess many of you even tried to make an unwrapper of your own (I also tried to make one after Pete Finnigan’s presentation:). But nevertheless its funny that Oracle in its documentation for 11.2 “loudly” states that unwrap does not protect anything from viewing, and even gives a direct link to the online unwrapper 🙂

11.2 PL/SQL Source Text Wrapping:

Note:

Wrapping text does not prevent anyone from displaying it with a utility such as:
http://www.codecheck.info/UnwrapIt/

For high-assurance security, use Oracle Database Vault, described in Oracle Database Vault Administrator’s Guide.

Here is a comparison of unwrap capabilities in documentations for different versions:

  • In 9.2 everything is safely hidden, except for literals, names of variables, tables and columns:

    String literals, number literals, and names of variables, tables, and columns remain in plain text within the wrapped file. Wrapping a procedure helps to hide the algorithm and prevent reverse-engineering, but it is not a way to hide passwords or table names that you want to be secret.

  • In 10.2 the data is hidden only from “most users”, but at least it makes reverse-engineering difficult!

    Although wrapping a compilation unit helps to hide the algorithm and makes reverse-engineering difficult, Oracle Corporation does not recommend it as a secure method for hiding passwords or table names. Obfuscating a PL/SQL unit prevents most users from examining the source code, but might not stop all attempts.

  • In 11.1 everything looks humble and boring:

    Wrapping is not a secure method for hiding passwords or table names. Wrapping a PL/SQL unit prevents most users from examining the source code, but might not stop all of them.

When oracle invalidates result_cache function results without any changes in objects on which depends

Posted on by Posted in documentation, oracle, result_cache 2,080 Page views Leave a comment

On our production servers we have simple function with result_cache, like this:

create or replace function f_rc(p_id number) return number result_cache
is
  ret number;
begin
  select t.val into ret from rc_table t where t.id=p_id;
  return ret;
exception 
  when no_data_found then 
     return null;
end;
/

And oracle invalidates its results very frequently without any changes in table or function. I found only 2 cases when oracle invalidates result_cache results without any changes in table:
1. “select for update” from this table with commit; (strictly speaking, select-for-update is DML and it changes blocks, so it’s ok)
2. deleting rows from parent table that has no child records if there is unindexed foreign key with “on delete cascade”.
I test it on 11.2.0.1, 11.2.0.3, on solaris x64 and windows. Test cases for this i will show below.
But none of them can be the cause of our situation: we have no unindexed fk, and even if i lock all rows with “select for update”, it still does not stop invalidating.
In what other cases this happens? Am I right that the oracle does not track any changes, but the captures of the locks and “commits”?
Continue reading→

dbms_random in parallel

Posted on by Posted in documentation, oracle, parallel 1,811 Page views Leave a comment

The documentation for dbms_random states:

It will automatically initialize with the date, user ID, and process ID if no explicit initialization is performed.

This phrase does not answer the question, which “process id” is going to be used for initialization in case of parallel execution. That’s why I decided to give a vivid example that shows independence of “dbms_random” generator from “process id” of slave, that is generating identical values in parallel:

with
 t  as ( select/*+ materialize */ level n from dual connect by level<=4000)
,t1 as (
         select--+ materialize parallel(t 4)
            dbms_random.string('x',4)
            ||';'
            ||(select sid||';'||process||';'||pid
               from v$session, v$process
               where sid=sys_context('USERENV','SID')
                 and PADDR=ADDR
                 and n>0
              ) f
         from t
)
,t2 as (
         select
            t1.f
           ,count(*) over(partition by regexp_substr(f,'^[^;]+')) cnt
         from t1
)
select f
      ,regexp_substr(f,'[^;]+') rnd
      ,regexp_substr(f,'[^;]+',1,2) sid
      ,regexp_substr(f,'[^;]+',1,3) process
      ,regexp_substr(f,'[^;]+',1,4) pid
from t2 
where cnt>1
order by f

Result:


F RND SID PROCESS PID
AARV;130;5472;30 AARV 130 5472 30
AARV;68;2228;29 AARV 68 2228 29
AC2R;130;5472;30 AC2R 130 5472 30
AC2R;68;2228;29 AC2R 68 2228 29
AC8O;130;5472;30 AC8O 130 5472 30
AC8O;68;2228;29 AC8O 68 2228 29
AKVZ;130;5472;30 AKVZ 130 5472 30
AKVZ;68;2228;29 AKVZ 68 2228 29
ALTQ;130;5472;30 ALTQ 130 5472 30
ALTQ;68;2228;29 ALTQ 68 2228 29
… … … … …