do not touch if you do not know for sure

Oracle provides and documents a huge load of possibilities and functions for nearly every purpose. For me it is impossible to know all of them. Even to know such an area exists is hard.
But still sometimes these functions Oracle does not document for customers purpose seems to be more attractive than those officially available.
One of these attractive packages is DBMS_SYSTEM. You will not find any description of this package in the official Oracle documentation. There are some small traces available, but nothing really useful.
Oracle also have quite clear words about using such unofficial, and hidden, packages:
In How To Edit, Read, and Query the Alert.Log [ID 1072547.1] you can read:

NOTE about DBMS_SYSTEM:
This package should in fact only be installed when requested by Oracle Support.
It is not documented in the server documentation.
As such the package functionality may change without notice.
It is to be used only as directed by Oracle Support and its use otherwise is not supported.

Per internal Note 153324.1:
Generally, if a package is not in the Oracle documentation at all, it is intentional, as it is not for end user use. Non-documented packages should be avoided by customers unless specifically instructed to use them by either Oracle Support or Oracle Development.

For some reasons I'm one of those which likes to play with forbidden toys like these. I found a procedure in DBMS_SYSTEM which changed behavior slightly in 11gR2 (I've tested with 11.2.0.3 patchset - so maybe other patchsets behave quite different!)

I'm talking about DBMS_SYSTEM.READ_EV. This procedure more or less calls directly the internal C-routine READ_EV_ICD. Common sense is, it should return the level of an event given. This is also quite true, just for one exception: the probably most known event in Oracle world: 10046 - or sql_trace.

My test-script here

VARIABLE lev number
SET AUTOPRINT ON
EXECUTE sys.dbms_system.read_ev(10046, :lev)

ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';

SELECT sql_trace, sql_trace_waits, sql_trace_binds FROM v$session WHERE sid=userenv('sid')


EXECUTE sys.dbms_system.read_ev(10046,:lev)

oradebug setmypid
oradebug eventdump session

gives the expected result in one of my 10g test DBs:

@test_read_ev.sql

PL/SQL procedure successfully completed.

       LEV
----------
         0

Session altered.

PL/SQL procedure successfully completed.

       LEV
----------
         8

Statement processed.
10046 trace name CONTEXT level 8, forever

but an unexpected result in my 11.2.0.3 test DB:

@test_read_ev.sql

PL/SQL procedure successfully completed.

       LEV
----------
         0

Session altered.

PL/SQL procedure successfully completed.

       LEV
----------
         0

Statement processed.
sql_trace level=8

I guessed events with an ALIAS might be excluded somehow, but other tests with DEADLOCK==60 or DB_FILES==10222 shows this special behavior only with sql_trace.

My todays conclusion is easy:
If it's not there for you, don't guess you can play with it without any consequences.

restore DBMS_SCHEDULER.CREATE_CREDENTIAL cleartext password

If you want to use Oracle file watcher, you need to Create a Credential. As there a password needs to be stored in the database, Oracle tries to save it in a secure way. But as the password must be decrypted for the purpose to login on the file watchers agent side, it is not safe at all:
The credentials are stored with DBMS_SCHEDULER.CREATE_CREDENTIAL. Here an example:

exec DBMS_SCHEDULER.CREATE_CREDENTIAL(
  credential_name => 'local_credential',
  username => 'oracle',  password => 'welcome1');
exec DBMS_SCHEDULER.CREATE_CREDENTIAL(
  credential_name => 'local_credential2',
  username => 'oracle2', password => 'welcome1');


It's quite easy to see the values again:

select o.object_name credential_name, username, password
 FROM SYS.SCHEDULER$_CREDENTIAL c, DBA_OBJECTS o
 WHERE c.obj# = o.object_id;

CREDENTIAL_NAME    USERNAME PASSWORD
------------------ -------- ------------------------------------
LOCAL_CREDENTIAL   oracle   BWVYxxK0fiEGAmtiKXULyfXXgjULdvHNLg==
LOCAL_CREDENTIAL2  oracle2  BWyCCRtd8F0zAVYl44IhvVcJ2i8wNUniDQ==


At least the password is somehow encrypted, and even the password was welcome1 for both credentials, the encrypted string is not identical.

Nothing to blame here, but I mentioned, the password can be decrypted. So let's do so:

SELECT u.name CREDENTIAL_OWNER, O.NAME CREDENTIAL_NAME, C.USERNAME,
  DBMS_ISCHED.GET_CREDENTIAL_PASSWORD(O.NAME, u.name) pwd
FROM SYS.SCHEDULER$_CREDENTIAL C, SYS.OBJ$ O, SYS.USER$ U
WHERE U.USER# = O.OWNER#
  AND C.OBJ#  = O.OBJ# ;

CREDENTIAL_OWNER CREDENTIAL_NAME      USERNAME PWD
---------------- -------------------- -------- --------
SYS              LOCAL_CREDENTIAL     oracle   welcome1
SYS              LOCAL_CREDENTIAL2    oracle2  welcome1


Can you see it? It's there. Try it at home!
I don't blame anyone here. It's hard to store anything really safe in case you need to decrypt it also.
But don't expect your password save, if you store it with DBMS_SCHEDULER.CREATE_CREDENTIAL.
Maybe it's slightly to easy to use DBMS_ISCHED.GET_CREDENTIAL_PASSWORD (ok, only SYS can do so) but even it might be slightly more difficult in the future, the basic problem will still exist.

some tracing events in DBMS_SCHEDULER

I currently have the fun to review DBMS_SCHEDULER. As I'm always interested in ways to trace anything, to dig deeper in case of problems, I searched for ways to trace it.
As I did not find a collected list of events anywhere, I start them here. It's by far not a complete list, so feel free to discuss and contribute, if you want!

event 10862

resolve default queue owner to current user in enqueue/dequeue

Cause: resolve default queue owner to current user in enqueue/dequeue.

Action: turn on if client wish to resolve the default queue owner to the current user. If not turned on, the default queue owner will be resolved to the login user.

This event is not checked the way you might imagine. Just in the area of REMOVE_JOB_EMAIL_NOTIFICATION if it's 0, it's set to 1 for a call of DBMS_AQADM.REMOVE_SUBSCRIBER and set to 0 afterwards.

27401

scheduler tracing event

• bit 0x10000 - Logging e-mail to table and returning
  bitand( ,65536)
  logs informations about sending emails into table sys.scheduler$_sent_emails
  
• bit 0x20000 - start DBMS_DEBUG_JDWP.CONNECT_TCP in file watcher
  bitand( ,131072)
  starts DBMS_DEBUG_JDWP.CONNECT_TCP on localhost, port 4444
  I'm not sure if I like this event. In general I don't want any software opening connections without my knowing. And I could not find this documented anywhere.
  Is it fair to call this a backdoor?
  
• bit 0x40000 - starts tracing in file watcher
  bitand( ,262144)
  logs informations about file watcher into trace file
  

27402

scheduler tracing event

• bit 0x40 - starts tracing about emails
  bitand( ,64)
  similar to event 27401 bit 0x10000, but tracefile instead of table
  
• bit 0x80 - starts tracing about emails
  bitand( ,128)
  logs information about email jobs into trace file
  
• bit 0x100 - starts tracing in chains
  bitand( ,256)
  logs information about chains into trace file

I guess there is at least also a bit 0x200, but could not prove it right now.

27403

scheduler stop job event

I did not find anything about it yet. comments are most welcome!

bzip2 twice?

To check the performance of RMAN backup I recently started to trace it a little bit. As most of the time was not spent in any reading from disk or writing to media manager library event, it was on CPU. It's good to know the CPUs are of any good, but as I still want to know what's going on I tried to dig any deeper. CPU cycles are not just a magic black box where we put in a problem and the answer comes out after some times. At an abstraction layer it's a chain of functions where one is called by another, and only the last is the one doing anything. There is not much information in that fact per se, but developers are humans also, and they are giving the functions they code meaningful names.


So I had just to find these names (and where most of the time is spent) to figure out what's going on. To save my time I remembered Tanel Poders Advanced Oracle Troubleshooting Guide, Part 9 – Process stack profiling from sqlplus using OStackProf. There he described his tool ostackprof. This did all the job for me, I just had to find a rman session.

Here's the shortstack where most of the time was spent:
(This backup was done with COMPRESSION ALGORITHM ‘BASIC’)
->__libc_start_main()->main()->ssthrdmain()->opimai_real()->sou2o()->opidrv()->opiodr()->opiino()->opitsk()->ttcpip()->opiodr()->kporpc()->kkxrpc()->prient()->prient2()->pricbr()->pricar()->plsql_run()->pfrrun()->pfrrun_no_tool()->pfrinstr_ICAL()->pevm_icd_call_common()->krbibpc()->krbbpc()->krbb3crw()->krbbcdo()->kgccdo()->kgccbz2pseudodo()->kgccbz2do()->kgccm()->kgccbuf()->kgccgmtf()->__sighandler()->->

The naming convention for functions is not public documented by oracle, but for some reasons I'm sure functions starting with krb are related to backup, whereas kgcc is used for compression. Especially the working function kgccgmtf reads like generate Move To Front.

At that point I had a lot more information than before, still I had no way how to improve the backup speed. As we have licensed advanced compression for that particular node, we tested with different other compression methods. LOW and MEDIUM where faster, with less compression than our previous BASIC. But HIGH was even slower!

So again I used ostackprof and that's the topmost stack trace - for HIGH:
->__libc_start_main()->main()->ssthrdmain()->opimai_real()->sou2o()->opidrv()->opiodr()->opiino()->opitsk()->ttcpip()->opiodr()->kporpc()->kkxrpc()->prient()->prient2()->pricbr()->pricar()->plsql_run()->pfrrun()->pfrrun_no_tool()->pfrinstr_ICAL()->pevm_icd_call_common()->krbibpc()->krbbpc()->krbb3crw()->krbbcdo()->kgccdo()->__PGOSF209_kgccbzip2pseudodo()->kgccbzip2do()->BZ2_bzCompress()->handle_compress()->BZ2_compressBlock()->generateMTFValues()->__sighandler()->->


Do you see the difference? Until kgccdo there is no! And even afterwards, the functions are somewhat similar. One more thing is worth to mention: the bzip2 implementation for HIGH does not use oracle internal naming convention. So it's worth to search for these names on the internet. one of my best hits was a compress.c File Reference.

Did Oracle reinvent the wheel? No. For me it looks as if they tried their best first (by doing their own kgcc implementation) and afterwards preferred simple copy&paste. Maybe they should just skip either of these 2 - they still can use parameters to achieve different compression quality. 


If someone is interested in our results:
for a single datafile of 30GB (with 100% usage) we achieved on a production system - with all it ongoing tasks:

Type	min	backup-size
BASIC	13:32	5.8
LOW	5:17	8
MEDIUM	8:52	6.14
HIGH	65:29	4.25

We decided to choose MEDIUM.

who owns that directory?

I'm talking here about the DIRECTORY object in Oracle, not the thing you might know from several OSes.

Most objects in a current database can be created for any user, but a DIRECTORY can't. Please check the CREATE DIRECTORY definition, there you can see

No optional schema. anywhere, as you can see with tables, indices and similar objects.

But that's not the end of the story. There is a view which shows you all the objects in a database: DBA_OBJECTS. And there is a owner. allways!

So let's check all the owners of directory objects there:

select distinct owner from DBA_OBJECTS where object_type ='DIRECTORY';

OWNER
------------------------------
SYS

As there is a owner defined somewhere, we also can change it. It's only a question of effort.

Unfortunately there is no ALTER DIRECTORY between ALTER DIMENSION and ALTER DISKGROUP. But it is still defined somewhere, so it can be changed!

Now I'm leaving the well prepared area of Oracle Documentation, but that just increases the fun.

Let's start with the definition of DBA_DIRECTORIES. It's somewhat easier as DBA_OBJECTS and stil shows an owner(!) What a nice sense of humor.

CREATE OR replace FORCE VIEW "SYS"."DBA_DIRECTORIES"
("OWNER", "DIRECTORY_NAME", "DIRECTORY_PATH")
AS
SELECT u.NAME,
o.NAME,
d.os_path
FROM   sys.user$ u,
sys.obj$ o,
sys.dir$ d
WHERE  u.user# = o.owner#
AND o.obj# = d.obj#

For the following tests I created a DIRECTORY D1 with the os-path /tmp/d1.

Checking sys.dir$ I found obj# 42985 for this directory.

Just to make sure, jet's check the owner of that object. I don't trust oracle, there is too much hardcoded magic inside. But not at this point:

select owner# from sys.obj$ where obj#=42985;

OWNER#
----------
  0

Now I create another user and try to give him the ownership of this particular object - without any grants. The owner of an object should be able to access the object anyhow.

The user berx is equiped with CONNECT and CREATE PROCEDURE:

With user berx let's try to access that DIRECTORY:

declare
f utl_file.file_type;
begin
f := utl_file.fopen('D1', 'something.txt', 'w');
utl_file.put_line(f, 'line one: some text');
utl_file.fclose(f);
end;
/

You will expect something like

ERROR at line 1:
ORA-29289: directory access denied
ORA-06512: at "SYS.UTL_FILE", line 41
ORA-06512: at "SYS.UTL_FILE", line 478
ORA-06512: at line 5

Now let's give user berx the ownership of that directory.

In my case user berx has user$ 41.

So I can (as sys)

update sys.obj$ set owner#=41 where  obj#=42985;

back as user berx - same testcase ....
-- still ORA-29289

Ok. Now we know Oracle does not check the owner of that particular object to check permissions. One of their shortcuts in the code.

So let's give poor user berx the GRANTs the ordinary way:

SQL> grant read, write on directory D1 to berx;

grant read, write on directory D1 to berx
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error

Not the effect you expected? Well, maybe one of the side-effects of my unkind interaction with the data dictionary and Oracles shortcuts.

But as I know what I did before, I can revert all the things:

SQL> update sys.obj$ set owner#=0 where  obj#=42985;

1 row updated.

SQL> commit;

Commit complete.

SQL> grant read, write on directory D1 to berx;

Grant succeeded.

And with a last test as user berx:

PL/SQL procedure successfully completed.

Everything is fine again!

Comment:

This post was created out of some older documents and my holey brain. But the main theme should be clear.

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.