result cache not enabled for owner SYS and SYSTEM

EDIT: the original title of this blog was "result cache not enabled in SYSTEM and SYSAUX Tablespace".
After a totally correct post from Coskan I had to rework it totally. I mixed up the problem and conclusio so much it was totally messed up.

I hope this one is the better one:

---

During some Tests with result cache in 11gR2 I discovered a small docu bug.

You can still see the bug in the 11gR1 docu:

You cannot cache results when you use the following database objects or functions in your SQL query:

• Dictionary and temporary tables

• Sequence CURRVAL and NEXTVAL pseudo columns

• SQL functions current_date, current_timestamp, local_timestamp, userenv/sys_context (with non-constant variables), sys_guid, sysdate, and sys_timestamp

• Non-deterministic PL/SQL functions

In 11gR2 it sounds slightly different: 7.6.4.2 Additional Requirements for the Result Cache:

You cannot cache results when the following objects or functions are in a query:

• Temporary tables and tables in the SYS or SYSTEM schemas

• Sequence CURRVAL and NEXTVAL pseudo columns

• SQL functions CURRENT_DATE, CURRENT_TIMESTAMP, LOCAL_TIMESTAMP, USERENV/SYS_CONTEXT (with non-constant variables), SYS_GUID, SYSDATE, andSYS_TIMESTAMP

for those of you who want to run the testcase:

create tablespace berx1 datafile '+DG' SIZE 50M;
create user berx1 identified by berx1;
grant connect to berx1;
grant unlimited tablespace to berx1;

create table sys.berx1_1 tablespace system as select * from dual;
create table sys.berx1_2 tablespace sysaux as select * from dual;
create table sys.berx1_3 tablespace berx1 as select * from dual;
create table system.berx1_4 tablespace system as select * from dual;
create table system.berx1_5 tablespace sysaux as select * from dual;
create table system.berx1_6 tablespace berx1 as select * from dual;
create table berx1.berx1_7 tablespace system as select * from dual;
create table berx1.berx1_8 tablespace sysaux as select * from dual;
create table berx1.berx1_9 tablespace berx1 as select * from dual;
grant select on sys.berx1_1 to berx1;
grant select on sys.berx1_2 to berx1;
grant select on sys.berx1_3 to berx1;
grant select on system.berx1_4 to berx1;
grant select on system.berx1_5 to berx1;
grant select on system.berx1_6 to berx1;

set autotrace on

-- run as user sys, system and berx1:

select /*+ result_cache */ * from sys.berx1_1;
select /*+ result_cache */ * from sys.berx1_2;
select /*+ result_cache */ * from sys.berx1_3;
select /*+ result_cache */ * from system.berx1_4;
select /*+ result_cache */ * from system.berx1_5;
select /*+ result_cache */ * from system.berx1_6;
select /*+ result_cache */ * from berx1.berx1_7;
select /*+ result_cache */ * from berx1.berx1_8;
select /*+ result_cache */ * from berx1.berx1_9;

my results are reflected in this matrix:

	berx1_1	berx1_2	berx1_3	berx1_4	berx1_5	berx1_6	berx1_7	berx1_8	berx1_9
sys	N	N	N	N	N	N	Y	Y	Y
system	N	N	N	N	N	N	Y	Y	Y
berx1	N	N	N	N	N	N	Y	Y	Y

So in my testcase it's not dependend of the schema (or the data dichtionary), but the tablespace.
This issue is addressed in BUG:8558309 and fixed in the latest docu :)

I know, I should never create any objects in SYS or SYSTEM schema. Especially if you want this object to be cached in result cache.

Once again thank you Coskan for showing my ridiculous error.

-prelim and direct_access

I recently asked a question about the 'difference' between /prelim and direct_access on oracle-l.
Tanel Poder gave a great answer. Now it's nearly clear, at least for me:

-prelim allows sysdba to start a preliminary connection to db, which
means that a server process is started for you and it attaches to sga
segments, but it does not allocate anything inside sga (no process
state nor session state object and session structures are allocated
for example). This means tha no latch gets are needed in prelim mode
and if you have an instance hang due these latches your session wont
get stuck. You can do very limited things with prelim mode, mostly
just various dumps with oradebug.

Oradebug direct-access allows you to also query few x dollar tables
with sql-like syntax. thats fake sql though its oradebugs hack, not
real sql. The same is doable with oradebug dumptype as well.

Or, to summarise it (just for me):

• -prelim allows me to start a sqlplus binary and connect it to the (correct ≈ right $ORACLE_HOME and $ORACLE_SID) SGA without creating anything within this SGA. So for me it's the cheapest way to get access to the shared memory of the specific instance.
• this prelim state is of no really use in ordinary life. Just for some really rare occasions, it might be of any use. Most of these are oradebug variants. I have not tested any ALTER SYSTEM or ALTER SESSION statement yet. Some of them might be worth testing, but most will fail. Ordinary SQL will fail all the time (I'm sure).
  
• oradebug direct-access is a nice gadget to query some x$ tables without really going through all the sql-parse-pin-heap-whatever engine ;-)
• at last, -prelim is a kind of last resort to access an instance and at least get some informations, until some really hard direct SGA access methods must be considered

The truth about NULL

"Do you want something to eat?" - "NO"
"Do you want nothintg to eat?" - "NO"
"What do you want?" - "I don't know" (off she run crying)

That was a conversation between me and my 3 years old daughter.
As she run away, totally overstrained by the situation, her dad and her world over all, I had pity on her.
And one thought further, I recognised the true answer she gave me:
it's not "YES", it's not "NO" (regarding the 1st question), it's "NULL". Or translated into what I think are the thoughts of a 3 years old girl: "I don't know what to do now, so I'm against anything and run away!"
From now on, if any application designer argues with me, why any column can not be set NOT NULL, I will remember this picture of a overstrained, 3 years old girl and just feel pity.

cbo costing for pl/sql functions

I rarely create a blog entry just to refer to another article, but this time it's worth to do so:
pl/sql functions and cbo costing from Adrian Billington is worth reading and at the end a must know for anyone, who writes pl/sql functions and considers performance.

linesize.sql

For a new set of db-servers I'm currently reworking our sql-script-toolbox.
Many of these scripts are copied and modified from other sources, but this particular is created by myself.
It's target is to set the linesize of sqlplus according the actual size of the unix terminal (yes, it's only for unix systems). So if you change the size of your terminal window, and you have this script in your SQL_PATH, just type @linesize and you use your terminal the best way you can.

It uses a defined mycpid to distinguish different processes at the same time, and I populated this define with a logon.sql which is derived from Tanel Poders init.sql out of his package TPT_public.zip

-- linesize.sql
--
-- mycpid must be defined before - e.g. in logon.sql
--
-- set linesize according to stty
-- handle with care, as stty is not consistent over platform boundaries
--

set termout off

define _linesize_cmd1 = 'echo -n "set linesize " > /tmp/linesize_&mycpid..sql '
-- for solaris:
define _linesize_cmd2 = 'stty -a  | awk -F''[ ;]'' ''/columns/ { print $7 }'' >>/tmp/linesize_&mycpid..sql'
-- for linux:
-- define _linesize_cmd2 = 'stty -a  | awk -F''[ ;]'' ''/columns/ { print $9 }'' >>/tmp/linesize_&mycpid..sql'

HOST &_linesize_cmd1
HOST &_linesize_cmd2

@ '/tmp/linesize_&mycpid..sql'

undef _linesize_cmd1 _linesize_cmd2

set termout on

Unfortunately the output of stty -a is not defined, so every OS provides a slightly different format and I have to change the parsing for every OS and sometomes even major release.

Might it be useful for someone else, too ;)

before select trigger

The whole idea to create a 'before select trigger' is based on a comment Martin Jensen dropped during his Seminar about Advanced Materialized Views in Vienna this week. So this all is not my idea, I just wanted to test the suggestion to get used to it.
The whole functionality utilizes FGAC.
As some others have also noticed, the function is called twice per every execution, so I have to reduce the effective execution of my action.

So let's start with - a log_table:

create table log_table
(datum date,
c varchar2(80)) ;

Now something which can write into this table.

The package is necessary to hold my package variable tot_calls and eliminate every 2nd execution of the function (otherwise it would fire twice, not recommended in most cases, I guess).

The autonomous_transaction might be necessary in most cases, but if someone find business-cases where it's recommended not to be autonomous, just drop this line and have the trigger under full transaction control.

CREATE OR REPLACE PACKAGE my_select_package AS
FUNCTION my_select_trigger (p_schema in varchar2,
                      p_object in varchar2 ) return varchar2;
END my_select_package;
/

CREATE OR REPLACE PACKAGE BODY my_select_package AS
tot_calls NUMBER := 0;
function my_select_trigger( p_schema in varchar2,
                                p_object in varchar2 ) return varchar2
as
PRAGMA AUTONOMOUS_TRANSACTION;
begin
tot_calls := tot_calls + 1;
if mod(tot_calls, 2) > 0
then
insert into log_table values (sysdate, 'function was here: ' ||p_schema ||'.' || p_object || ' tot_calls: ' ||tot_calls);
commit;
end if;
return NULL;
end;
END my_select_package;
/

What are we still missing? A table to connect the trigger to, with some data:

create table my_table
(  data        varchar2(30)
);

insert into my_table ( data ) values ( 'Some Data' );

insert into my_table ( data ) values ( 'Some other Data' );

commit;

Now let's tight these pieces together:

begin
dbms_rls.add_policy
( object_schema   => 'SR3',
object_name     => 'MY_TABLE',
policy_name     => 'MY_SELECT_TRIGGER',
function_schema => 'SR3',
policy_function => 'MY_SELECT_PACKAGE.MY_SELECT_TRIGGER',
statement_types => 'select' ,
update_check    => FALSE );
end;
/

and some trivial tests:

select * from my_table;

DATA                           OWNER
------------------------------ ------------------------------
Some Data                      SR3
Some Data Owned by SCOTT       SCOTT

select * from log_table;

DATUM               C
------------------- --------------------------------------------------
2009-05-18 13:19:09 function was here: SR3.MY_TABLE tot_calls: 1

select * from my_table;
-- ...

select * from log_table;

DATUM               C
------------------- --------------------------------------------------
2009-05-18 13:19:09 function was here: SR3.MY_TABLE tot_calls: 1
2009-05-18 13:19:15 function was here: SR3.MY_TABLE tot_calls: 3

works like a charm.

Anything more to say?
Of course, as allways, this is only a proove of concept.
As allways, most parts are borrowed from several sites.
Don't use this blindly anywhere. I totally left everything out, which could confuse me. Like exception handling, hard testing, theoretical and practical security checks, performance baselines, etc.
...
and at the end, the most global citation from Martin Jensen: 'If it's not tested, it does not work'

odyssey of a session - Part 2

An hour after I finished my work on explaining and supporting one poor session, I got a call from my honoured colleague again.

'The session stopped doing any sorts in v$session_longops and also no more segments on the indices are written. What's going on?'

My first guess was 'Did it just finish?', but that was not the case. The table still seemed to be empty (so no commit yet). The session was still active, but SQL_HASH_VALUE was NULL. Also sampling v$session_wait showed diferent events, so the process was doing anything. We just did not know, what.

Even I didn't know, what's going on, I knew something is going on. I couldn't pin it down with the knowledge I have using plain oracle methods, so I decided to switch to another method. First OStackProf came to my mind, but unfortunately I was sitting on an Apple Computer at this moment, and I didn't want to care about VBS on Apple. Not a big deal at all, because after a short crawling through Tanles box of marvelous miracles, I grabbed os_explain.

Let's have a look what the process is doing.

After running

pstack 11460 | ./os_explain

some times (5 or 6 where enough) it was clear there is no ordinary execution plan running. We got 2 stacks ( with little changes in the end, but this didn't count). Putting these side by side for easier camparison:

ksupop                  ksupop      
ksudlc                  ksudlc
ktcrsp                  ktcrsp
ktursp                  ktursp
ktubko                  ktubko
kcbgcur                 ktundo
kcbzib                  kqrpre
kcfrbd                  kqrpre1
 ksfdread                kslfre
  ksfd_odmio
   odm_io
    ioctl

I highlighted the first diference:

kcbgcur vs. ktundo.

To decipher these names (ok, in this particular case it wasn't too difficult, even for me) we had a look in Note:175982.1 - ORA-600 Lookup Error Categories. kcbgcur will be more or less Kernel Cache Buffer Current (don't know about the g) and ktundo is about undo and rollback management.

This still didn't answer all my questions, but what can a session be doing if it doesn't execute a SQL, (there is no sign for PL/SQL, java or similar currently executed by the session) and there is a lot of work performed in Cache and Undo? The next idea was: Rollback!

v$transaction came into my mind. And a little bit googling led me to this query:

SELECT a.sid, a.username, b.xidusn, b.used_urec, b.used_ublk
FROM v$session a, v$transaction b
WHERE a.saddr = b.ses_addr;

Yes, USED_UREC was decreasing! So the poor session, which worked for mor than 12 hours now, did a rollback.

Some phone calls later, the operations-guy who started the whole script told us 'Yes, about noon there where some hanging situations on my PC, but Toad looks ok again, I'm just waiting for the script to finish'.

Poor guy, and lucky me for enabling Dead connection Detection in sqlnet.ora. Otherwise the whole statement would have run until the end and started the rollback THEN.

This was the last chapter in the odyssey of my little poor session. After the rollback finished, it just closed (as the client connection died before).

Some words at the end?

I assume, the script which caused the problem was never tested properly.

Even with limited knowledge, a more or less systematic aproach is possible.

There is a lot of free information and tools out there. Just try to know about them before you need them.

I should try to learn more about sessions in rollback.

No fancy Oracle-features where needed. No statspack, no AWR (as the DB is 9i - did I mention that?). Not even dtrace.

It was a great day for me, working with a DB again and not only talking about DBs in endless meetings.