berxblog

Setting Up Oracle Connection Manager (without SOURCE_ROUTE)

2012-01-31T19:07:00.002+01:00

This post must be seen as a direct follow up to Arup Nandas Setting Up Oracle Connection Manager.
As there are many references to this post, please read it first. Problem and Solution are quite similar, only the architecture is a little bit different:

The Architecture

The network diagram of the three machines is slightly different:

There is a new needed connection: from the instance on dbhost1 to the connection manager on cmhost1.

After changing the setup, you will need to rewrite the TNSNAMES.ORA in the following way:

TNS_CM = 
  (DESCRIPTION = 
    (ADDRESS = 
      (PROTOCOL = TCP)(HOST = cmhost1)(PORT = 1950)
    )
    (CONNECT_DATA = 
      (SERVICE_NAME=srv1)
    )
  )

You see, the (SOURCE_ROUTE = YES) disappeared as well as the ADDRESS of the listener on dbhost1.

How it Works

Note, all the special parameters and settings on the clients TNSNAMES.ORA disappeared. But the cman must know about the SERVICE_NAME it has to serve. As the cman can be seen as a special kind of listener, there is a common way a listener gets informed about a SERVICE_NAME: the Instance has to register the services to the listener. In general this is done by pmon at registering to logal_listener and remote_listener. In this case, remote_listener is the magic parameter.

Setting Up

You can follow step (1) to (9) as in Arups blog.
But before (10) an additional step is required:

(x) on the instance add the cman to remote_listener:

Alter System Set remote_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=cmhost1)(PORT=1950))))' scope=both;

If there is already an entry in remote_listener, e.g. in a RAC, you can separate the different connection strings by comma. An example can be

Alter System Set remote_listener='SCAN-IP:1521,(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=cmhost1)(PORT=1950))))' scope=both;

(For more details about SCAN I'd recommend this PDF)

CMCTL Primer

As we have now the services registered also on cman, we can see it there. The SHOW command has a 2nd parameter services. Here an example

Services Summary...
Proxy service "cmgw" has 1 instance(s).
  Instance "cman", status READY, has 2 handler(s) for this service...
    Handler(s):
      "cmgw001" established:1 refused:0 current:0 max:256 state:ready
         <machine: 127.0.0.1, pid: 16786 >
         (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=44391))
      "cmgw000" established:1 refused:0 current:0 max:256 state:ready
         <machine: 127.0.0.1,pid: 16784>
         (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=44390))
Service "INSTANCE1" has 1 instance(s).
  Instance "INSTANCE1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=dbhost1)(PORT=1521))
Service "cmon" has 1 instance(s).
  Instance "cman", status READY, has 1 handler(s) for this service...
    Handler(s):
      "cmon" established:3 refused:0 current:1 max:4 state:ready
         <machine: 127.0.0.1, pid: 16759>
         (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=44374))
The command completed successfully.

Fine Tuning

I try to create a dedicated service for all (or a well known set of) connections via the connection manager. By doing so it's sometimes easier to separate or identify different kinds of sessions.

Who created that process?

2011-12-09T18:37:00.001+01:00

Figure 2-7
Connection to
a Dedicated
Server Process

For some reason I was really curios who created that process. It's not about a particular process in detail, mir a well known kind of processes. At least well known for DBAs.

Which process?

It's one of these:

oracle   13096     1  0 20:05 ?        00:00:00 oracleTTT071 (LOCAL=NO)

Yes, it's a simple server process, nothing spectacular. Nevertheless, the Concepts guide is not very specific, who created that process. So I tried to find out in more detail.
On my linux sandbox the first column of ps -ef shows the UID, the second is the PID, the third is the PPID. Unfortunately it's 1 here, and I'm quite sure, this process was not created by init. So this proces is somewhat orphaned, as the direct parent disappeared. Very sad!
I decided to follow Figure 2-7 from the concepts guide. I used strace -f -p <PID_of_listener> to see what's going on. -f follows all forks, so also their actions are traced.
The first 3 lines are

Process 2979 attached with 3 threads - interrupt to quit
[pid  2981] futex(0xae8dee4, FUTEX_WAIT_PRIVATE, 1, NULL <unfinished ...>
[pid  2980] restart_syscall(<... resuming interrupted call ...> <unfinished ...>

So we have 3 listener processes - it's good to know and probably worth to investigating this segregation of duties - but not in this post. There are so many interesting lines, but I'm searching for a process, so let's continue with

[pid 2979] clone(Process 27028 attached
child_stack=0, flags=CLONE_CHILD_CLEARTID|CLONE_CHILD_SETTID|SIGCHLD, child_tidptr=0x2aedd9914b80) = 27028
[pid 2979] wait4(27028, Process 2979 suspended
<unfinished ...>
[pid 27028] clone(Process 27029 attached (waiting for parent)
Process 27029 resumed (parent 27028 ready)
child_stack=0, flags=CLONE_CHILD_CLEARTID|CLONE_CHILD_SETTID|SIGCHLD, child_tidptr=0x2aedd9914b80) = 27029
[pid 27028] exit_group(0) = ?
Process 2979 resumed
Process 27028 detached
[pid 2979] <... wait4 resumed> [{WIFEXITED(s) && WEXITSTATUS(s) == 0}], 0, NULL) = 27028
[pid 27029] close(15 <unfinished ...>
[pid 2979] --- SIGCHLD (Child exited) @ 0 (0) ---
[pid 27029] <... close resumed> ) = 0
[pid 2979] close(14 <unfinished ...>
[pid 27029] close(16 <unfinished ...>
[pid 2979] <... close resumed> ) = 0
[pid 27029] <... close resumed> ) = 0
[pid 2979] close(17) = 0

Here the listener ([pid 2979]) creates a new process by the first clone call. This new Process has the PID 27028. This new process has only one purpose: again clone a new Process: PID 27029 and use exit_group(0) to terminate directly afterwards. By this trick the listener is not shown as parent process for PID 27029. Directly after it's creation PID 27029 closes some file handles. As by the sequence of clone calls the new process inherited a table of all open file (and network) handles it seems it tries to get rid of any it does not need as early as possible. The next part
[pid 2979] fcntl(16, F_SETFD, FD_CLOEXEC) = 0
[pid 27029] setsid( <unfinished ...>
[pid 2979] fcntl(15, F_SETFD, FD_CLOEXEC <unfinished ...>
[pid 27029] <... setsid resumed> ) = 27029
[pid 2979] <... fcntl resumed> ) = 0
[pid 27029] geteuid() = 5831
[pid 2979] fcntl(13, F_SETFD, FD_CLOEXEC) = 0
[pid 27029] setsid() = -1 EPERM (Operation not permitted)

[pid  2979] poll([{fd=8, events=POLLIN|POLLRDNORM}, {fd=11, events=POLLIN|POLLRDNORM}, {fd=12, events=POLLIN|POLLRDNORM}, {fd=16, events=POLLIN|POLLRDNORM}, {fd=15, events=0}], 5, -1 <unfinished ...>

makes sure the file descriptos 16, 15 and 13 will remain after an execve(2) call.
And here it goes:

[pid 27029] execve("/appl/oracle/product/rdbms_112022_a/bin/oracle", ["oracleTTT051", "(LOCAL=NO)"], [/* 109 vars */]) = 0

from the man page if execve:

execve() executes the program pointed to by filename.
...
execve() does not return on success, and the text, data, bss, and stack of the calling process are overwritten by that of the program loaded. The program invoked inherits the calling process’s PID, and any open file descriptors that are not set to close-on-exec. Signals pending on the calling process are cleared. Any signals set to be caught by the calling process are reset to their default behaviour. The SIGCHLD signal (when set to SIG_IGN) may or may not be reset to SIG_DFL.
If the current program is being ptraced, a SIGTRAP is sent to it after a successful execve().
If the set-user-ID bit is set on the program file pointed to by filename, and the calling process is not being ptraced, then the effective user ID of the calling process is changed to that of the owner of the program file. i Similarly, when the set-group-ID bit of the program file is set the effective group ID of the calling process is set to the group of the program file.

From that point on there you can see how the server process comes to life. It's very interesting in some details, but not scope of this post. After some conversation between listener and server process using file descriptors 15 and 16 (I assume these are just sockets) both close these file descriptors. The listener also closes file descriptor 13 which seems to be the TCP connection to the client. From that point the 2 processes seems to be independent.

Well, now I know (at least on my test-system) the simplest way, the listener creates the process - and it uses execve to do so. There still are many questions open, like what's going on at this redirection as shown in Figure 2-8.

bzip2 twice?

2011-11-28T15:51:00.001+01:00

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 circles 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.

non-Exadata HCC

2011-10-05T22:43:00.000+02:00

Oracles Hybrid Columnar Compression was one of the big new features of Oracle 11.2. Unfortunately someone decided this feature should only be available in Exadata. Even Oracle tried to explain it with technical limitations, it was more or less obvious that's just wrong. There are some reasons for this:

The Database is doing the HCC compression all the time
The Database must be able doing the HCC decompression in case the storage cell can not or want not.
Beta-testers where very sure, HCC worked there without any special hardware.
Jonathan Lewis shows there are situations, where also an ordinary database creates tables with HCC

But fact is: Oracle decided to disable HCC for general usage. As there is no different database software in Exadata database servers, the decision whether process the statement or throw a ORA-64307: hybrid columnar compression is only supported in tablespaces residing on Exadata storage must be done by any switch within the software.

Here my collection of informations I have about these switch:

Kerry Osborne described in the book Expert Oracle Exadata in pages 46 to 48 how the ASM DiskGroup attribute cell.smart_scan_capable=TRUE is only possible on Exadata, and necessary for any kind of smart scan - so also for HCC.
Cern has published a paper about Compression in Oracle - in the Appendix (pages 42 to 44) they show how to change this attribute. Not so easy and it corrupts the ASM DG.
Jonathan Lewis mentioned there might be a switch in DBMS_COMPRESSION.GET_COMPRESSION_RATIO which disables the switch for the purpose of the temporary compressed tables. He did not go into details, but I decided to investigate into that direction.

The package DBMS_COMPRESSION uses prvt_compression, and there in GET_COMPRESSION_RATIO it calls PRVT_COMPRESSION.CHECK_HLI(1); to disable this switch and PRVT_COMPRESSION.CHECK_HLI(0); to enables it at the end again. CHECK_HLI just calls the kernel function KDZCHECKHI with it's parameter, nothing more. Unfortunately it can not be called from outside of PRVT_COMPRESSION. That's the way I started to investigate: I removed the line PROCEDURE CHECK_HLI (HLID IN NUMBER); from the package body and inserted it into the package header. (by doing so, I left the path of supported system - don't do this if you care your system!). Now I can call CHECK_HLI:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production 
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,Data Mining and Real Application Testing options

SQL> exec sys.prvt_compression.CHECK_HLI(1);

PL/SQL procedure successfully completed.

SQL> create table bx_dba_objects Compress For Archive Low as select * from dba_objects;

Table created.

prvt_compression.CHECK_HLI works per session; so a logon-trigger comes to my mind.
To enable the check again, the parameter is 0 instead of 1.

Update:
parallel processes does not inherit this feature:
(a slightly different testcase, but same setup in general)

SQL> select /*+ PARALLEL 8 */ count(*) from test_user.DBMS_TABCOMP_TEMP_CMP; 
select /*+ PARALLEL 8 */ count(*) from test_user.DBMS_TABCOMP_TEMP_CMP
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server P008, instance av2l904t:VAX1
(1)
ORA-64307: hybrid columnar compression is only supported in tablespaces
residing on Exadata storage

when does PMON register to remote listeners

2011-09-07T15:30:00.000+02:00

I had a complex problem today: I tried to setup a connection manager, but unlike Arup, I did not like to to use SOURCE_ROUTE. So I had to make the pmon register itself to the cman. As we have already an entry in spfile for remote_listener=REMOTE, I just enhanced this alias in tnsnames.ora by the additional line for the cmans HOST and PORT.
Unfortunately the services did not show up in the cmans show services. Not even an alter system register; did any good, still no service.
After checking with tcpdump (there where really no communication to the cman) and oradebug event 10246 I had still no clue how to find out why my pmon does not like to contact the cman. At a short ask for help on twitter, Martin Nash pointed me to the Note How to Trace Dynamic Registration from PMON ? [ID 787055.1]. There I found the event

alter system set events='immediate trace name listener_registration level 3';

With this, (beside a lot of other useful information) I found the pmon just not knowing about the new entries.
As a solution I had to tell it about the new entries in tnsnames.ora by

alter system set remote_listener=REMOTE;

This made pmon to re-read the tnsnames.ora and accept the new values. All my services shows up in cman now.
Yong Huang has some more Informations about the different trace levels here:

Actually, trc_level 4 (user) is enough to show info about load stats. Other levels are:
0,1: off
2,3: err
4,5: user
6-14:admin
15: dev
16-: support

incomplete list of SRVCTL commands

2011-08-19T11:23:00.003+02:00

As I have to dig into srvctl more than I liked to do, I figured the documentation is not complete (at least for my installation of 11.2.0.2):
the Documentation for srvctl upgrade claims

The srvctl upgrade database command upgrades the configuration of a database and all of its services to the version of the database home from where this command is run.

But there is a 2nd option missing totally:

Usage: srvctl upgrade model -s <source-version> -d <destination-version> -p {first|last} [-e <name>=<value>[,<name>=<value>, ...]

in more detail:

srvctl upgrade model -h



Upgrade the Oracle Clusterware resource types and resources.



Usage: srvctl upgrade model -s <source-version> -d <destination-version> -p {first|last} [-e <name>=<value>[,<name>=<value>, ...]

   -s <source-version>      Version from which to upgrade

   -d <destination-version> Version to which to upgrade

   -p {first|last}          Whether the command is called from the first upgraded node or the last upgraded node

   -e <name>=<value>[,<name>=<value>, ...] Additional name value pairs for upgrade

   -h                       Print usage

In general thsi should only be needed during an CRS upgrade, as part of root.sh script. Nevertheless, as it's there it should be documented. Especially the -e parameter seems to be worth more information than the -h docu provides.

estimated plan stability

2011-07-18T22:36:00.001+02:00

Sometimes I am searching for any method to solve a problem. And after some investigations, mailing lists, direct contact of much smarter people, I come to the conclusion:

It's just not possible!

(Or at least not within reasonable effort).

One of these problems, or more precise questions is:

How likely is the current explain plan for a given SQL statement to change?

I call this

estimated plan stability

Unfortunately there is currently no such feature but at least I can give some examples, what I would like to have:

E-rows vs. A-rows
If they differ a lot (in any line of the execution plan) it might be a hint the plan is far away from reality, or in risk to change?
Of course for A-rows gather_plan_statistics or similar is needed.

Best so far in 10053 trace

If you ever have analysed a 10053 trace, you might know the line starting with Best so far ....
If the 2nd best is not far from the 1st, I assume small changes in the data might lead to a different execution plan.

Binds outside histogram boundaries

If a bind variable is outside of the min/max values of a histogram, the optimiser tries to guess how many rows it will get from this predicate/filter. Of course this can be horrible wrong, and should be also shown by my 1st suggestion.

These are only 3 possibilities. They should show some areas of information where I'd like Oracle to collect and provide more data than they do at the moment. Probably they would also be valuable for others? Any other suggestions out there?

avoid implicit COMMIT at DDL

2011-07-13T22:10:00.003+02:00

Some times, I'd like to have a transaction open, even there is a DDL somewhere in between.
Oracle might have some reasons for the implicit COMMIT, but I have mine to avoid them in my transactions.

Here a small example, how I do it (don't just copy it, it's really small, just an example)!

Let's prepare a package and two tables


CREATE OR REPLACE PACKAGE my_ddl_package AS
FUNCTION do_ddl(p_ddl varchar2)  return varchar2;
END my_ddl_package;
/

CREATE OR REPLACE PACKAGE BODY my_ddl_package AS
function do_ddl(p_ddl varchar2) return varchar2
as
PRAGMA AUTONOMOUS_TRANSACTION;
begin

execute immediate p_ddl;
return NULL;
end;
END my_ddl_package;
/

create table t1 as select * from dual;

create table t2 as select * from dual;

And now the testcase

I try to update t1, drop t2 and afterwards rollback the update on t1.
Let's give it a try.


SQL> update t1 set dummy ='U';

1 row updated.

SQL> select my_ddl_package.do_ddl('drop table t2') from dual;

MY_DDL_PACKAGE.DO_DDL('DROPTABLET2')
--------------------------------------------------------------------------------


SQL> select * from t1;

D
-
U

SQL> rollback;

Rollback complete.

SQL> select * from t1;

D
-
X

SQL> select * from t2;
select * from t2
              *
ERROR at line 1:
ORA-00942: table or view does not exist

Everything works as expected: update - DDL - rollback

Did I miss something?

If you did not want to read (or think) too much, just the shortcut:
What saved my day? It's PRAGMA AUTONOMOUS_TRANSACTION!

ORA-02393 on purpose

2011-06-20T19:39:00.002+02:00

Today I got an email by a developer about an error on a test database.
(I shortened the conversation, but tried not to change the content):

Dev:

We get an ORA-02393: exceeded call limit on CPU usage almost immediatedly after executing the following stmt: ...Could you pls adjust the limits?

After some hours of meetings I replied:
Me:

These limits where introduced in test to find exactly those statements which are running too slow for an OLTP application. Can we help you in tuning the statement?

Dev:

I already exchanged details on the issue with [another DBA] and found the cause of the 'longer than usual' execution time (index missing ;-)
Nonetheless 100ms max exec time is a bit too strict for a dev platform imho - but as the ... is going to be replaced soon we won't request any changes to a system/db in the last chapter of its life cycle.

I did not reply yet. Just try to explain my world.

Is a CPU_PER_CALL limit really needed on a test system?

In theory: no.

If the developers implement a perfect code instrumentation, they would know the runtime of every statement. In test it would be evaluated for a proper per-statement performance as well as for an over all performance.

But if they would do so, I would never have received this email. So the world is not perfect. The code isn't, also.

Are 100ms enough time? seems really tough!

Yes, they are.

Even on an 8 years old UltraSparc III+ you can do a lot of CPU cycles within 100ms. And only CPU is count there, no time for IO or other WAITs.

But the biggest argument for this limit: I never got any complaint about it by a good statement. Only by those which needed urgent suport, anyhow.

Access to the database

2011-06-06T11:47:00.004+02:00

There is a kind of discussion ongoing who should have access to a database, and under what circumstances.
I have never done either of Jeffs or Chets job, so I cannot write anything reasonable about these. But I am sometimes a kind of DBA. This makes me my best source of knowledge about this job, and the attitude I created during the years.

I developed a certain expectation about the differences between an operation guy and others, who are not responsible for productive systems. I even have my private opinion about a sales related job, but that's not my topic today.

From my point of view it's all about control.

For an operational focused person, my work is about control. If there is uncontrolled behaviour somewhere, this will lead to an incident. And that will causes a call. As I like to sleep at least 8 hours per day, that means one out of 3 calls will disturb this sleep (on average).

Of course every incident potentially costs my company money. Or reputation. This will come down to me again. Writing reports. Doing management presentations. Nonproductive paperwork.

After all this additional work, I will do my best to avoid this situation. Analyzing what went out of bounds, led to this uncontrolled behaviour. At the end I even try to change the environment so this will not happen again.

It's all about control. Freak!

So who should get access to my systems? Only those whose I can control. Or at least I can trust?

It's hard to decide whom to trust. At least to which specific level.

Jeffs driving license seems to be a try to formalise this need. But similar to a driving license, it only shows you have the allowance to drive. It rarely tells anything about the skills.

Talking about skills brings one more dimension here: People living in an ecosystem where every action is controlled by a QualityAssurance team are used to go to the limits and beyond. That's great, it's what is expected from them. Otherwise they would do their job bad.

It's just not what I want in a productive environment. There boundaries are, to never be reached.

I put a more pragmatic approach: If someone takes responsibility for the work, it's fine for me. As an example: if the person who added a big bug during a small hotfix at 5pm is called at 2am the next morning to fix this bug, I'm fine.

And just one more dimension: I favour to grant access to people who know what they can break with this: Just ask me for any access to v$ views in my DB; you will get it, just after you show and explain me how you can halt the application if you abuse it.

Need a short summary?

I like to control access to my database, so I limit it to people who I trust.

To gain it, show me your responsibility and knowledge.

Artist of Farmer?

2011-06-01T06:53:00.009+02:00

This morning I read a twitter message by Jeff Smith (aka @hillibillitoad):

OH: "Once we get our system running, we don't touch it." Yeah, that generally works pretty good.

I like Jeffs tweets, blogs and comments as he is a very smart guy and still keeps his mind open for other ideas.

In this particular case I have to contradict, but I was not able to condense it into 140 chars.

Most people in the IT business seems to follow the sentence "never touch a running system" like a commandment. For me this often sounds like the "please don't touch" in an art museum.

This brings me to an interesting question: Do these 'most people' see themselves as artists, and their work as art?

Let me give you another picture:

Imagine a farmer which seeds the crops in spring, did really everything right and then sits down and does not touch his running system. You might guess his harvest?

So what's the big difference here?

Artwork most of the time is first created for a dedicated purpose. As long as the purpose does not change, it's expected to satisfy this purpose.

The purpose of an artwork slowly changes, I'm quite sure most of the time it can be measured in decades or centuries.

Also plants are seeded for a dedicated purpose. But their purpose is the rapid change. To live, to growth, to get harvested after some time. So the farmer has to look after his crops all the time. In the best case, he even can improve and steer the change to his advance.

For me the big difference between an artist ('don't touch') and a farmer ('care and steer') is the timescale of the changes. If you expect your work to be never changed (and in a definition of 'life' things which do not change are just dead) prevent them from any interaction.

I prefer living systems. So I take the duty and care for them.

who owns that directory?

2011-03-16T21:11:00.007+01:00

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.

2 spfiles - some confusion

2011-03-16T13:26:00.008+01:00

At the moment I'm in a big migration project from 9i to 11gR2.
Most of the migrations are done like

create new DB

exp/imp

delete old DB

Even we have templates for all these steps, sometimes something goes wrong.

Today I hit such a something:
As we use Clusters for most of our DBs, cluster_database=TRUE should be set, otherwise it's generating problems. At the Creation of one DB, the 2nd instance did not come up. After some research I found out cluster_database was set to FALSE. No big deal, let's change it:

alter database set cluster_database=TRUE scope=spfile;

But after a

srvctl stop instance -d DB_UNIQUE_NAME -i INSTANCE_NAME

srvctl start instance -d DB_UNIQUE_NAME -i INSTANCE_NAME

a show parameter cluster_database still gave me FALSE. This confused me!
So I checked $ORACLE_HOME/dbs/initINSTANCE_NAME and there I found

spfile=+ASM_DG/DB_UNIQUE_NAME/PARAMETERFILE/spfile.268.745343891 +ASM_DG/DB_UNIQUE_NAME/PARAMETERFILE/spfile.269.745406153

there where 2 spfiles in the pfile-definition!
After a


create pfile='/tmp/pfile1' from spfile='+ASM_DG/DB_UNIQUE_NAME/PARAMETERFILE/spfile.268.745343891';
create pfile='/tmp/pfile2' from spfile='+ASM_DG/DB_UNIQUE_NAME/PARAMETERFILE/spfile.269.745406153';

and a diff /tmp/pfile1 /tmp/pfile2 I decided to use only the 1st spfile in the $ORACLE_HOME/dbs/initINSTANCE_NAME and apply all the differences manually.
Now both instances are up and fine - I just have to delete the correct spfile in ASM.

Invalid SQL if hint is missing?

2010-12-30T16:11:00.004+01:00

Recently Charles Hooper posted a blog An Invalid, or Do You Just Not Want to Work. There he asked under which circumstances a SQL should be considered invalid? I tried to discuss this question in the comments there and I am still thinking about an other kind of answer. But today I'd like to show a statement, which is invalid without a hint!

here we go:
(everything 11.2.0.1EE 64-bit on Linux)
I was asked to provide a list of all views which have a hint (how ironic) in it. After some test I provided this one:


select /*+ no_merge(vv) */ owner, view_name
from (select v.owner, v.view_name
from dba_views v, dba_objects o
where v.owner = o.owner
AND  v.view_name = o.object_name
AND o.object_type='VIEW'
AND o.status='VALID'
AND v.owner ='PSFT'  ) vv
where    dbms_metadata.get_ddl('VIEW', vv.view_name, vv.owner) like '%/*+%'

without the no_merge hint I get this error stack:


ERROR at line 9:
ORA-31603: object "AAB_TEST" of type VIEW not found in schema "PSFT"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 3912
ORA-06512: at "SYS.DBMS_METADATA", line 5678
ORA-06512: at line 1

I did some explain plan with and without hints, so I got these 2 outputs:


SQL_ID bwsx4fryyu6u9, child number 0
-------------------------------------
select /*+ no_merge(vv) */ owner, view_name from (select v.owner,
v.view_name    from dba_views v, dba_objects o   where v.owner =
o.owner    AND v.view_name = o.object_name    AND o.object_type='VIEW'
   AND o.status='VALID'    AND v.owner ='PSFT' ) vv where
dbms_metadata.get_ddl('VIEW', vv.view_name, vv.owner) like '%/*+%'

Plan hash value: 3436948868

------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |    1 |       |     14 |00:01:33.65 |  1088K|       |       |   |
|*  1 |  VIEW          |      |    1 |     1 |     14 |00:01:33.65 |  1088K|       |       |   |
|*  2 |   FILTER         |      |    1 |       |   8802 |00:00:00.28 |  7432 |       |       |   |
|   3 |    NESTED LOOPS         |      |    1 |     1 |   8802 |00:00:00.25 |  7432 |       |       |   |
|   4 |     NESTED LOOPS        |      |    1 |     1 |   8802 |00:00:00.21 |  2469 |       |       |   |
|*  5 |      HASH JOIN         |      |    1 |     1 |   8802 |00:00:00.15 |  2464 |  1152K|  1152K| 1616K (0)|
|   6 |       VIEW         | DBA_OBJECTS |    1 |    26 |   8802 |00:00:00.07 |  2239 |       |       |   |
|   7 |        UNION-ALL        |      |    1 |       |   8802 |00:00:00.07 |  2239 |       |       |   |
|*  8 |  TABLE ACCESS BY INDEX ROWID   | SUM$     |    0 |     1 |      0 |00:00:00.01 |     0 |       |       |   |
|*  9 |   INDEX UNIQUE SCAN       | I_SUM$_1    |    0 |     1 |      0 |00:00:00.01 |     0 |       |       |   |
|* 10 |  FILTER         |      |    1 |       |   8802 |00:00:00.06 |  2239 |       |       |   |
|  11 |   NESTED LOOPS        |      |    1 |     1 |   8802 |00:00:00.05 |  2239 |       |       |   |
|  12 |    NESTED LOOPS        |      |    1 |     1 |   8802 |00:00:00.03 |  2236 |       |       |   |
|  13 |     TABLE ACCESS BY INDEX ROWID| USER$     |    1 |     1 |      1 |00:00:00.01 |     2 |       |       |   |
|* 14 |      INDEX UNIQUE SCAN       | I_USER1     |    1 |     1 |      1 |00:00:00.01 |     1 |       |       |   |
|* 15 |     TABLE ACCESS BY INDEX ROWID| OBJ$     |    1 |     1 |   8802 |00:00:00.03 |  2234 |       |       |   |
|* 16 |      INDEX RANGE SCAN       | I_OBJ5     |    1 |    25 |   8803 |00:00:00.01 |   222 |       |       |   |
|* 17 |    INDEX RANGE SCAN       | I_USER2     | 8802 |     1 |   8802 |00:00:00.02 |     3 |       |       |   |
|* 18 |   TABLE ACCESS BY INDEX ROWID  | IND$     |    0 |     1 |      0 |00:00:00.01 |     0 |       |       |   |
|* 19 |    INDEX UNIQUE SCAN       | I_IND1     |    0 |     1 |      0 |00:00:00.01 |     0 |       |       |   |
|  20 |   NESTED LOOPS        |      |    0 |     1 |      0 |00:00:00.01 |     0 |       |       |   |
|* 21 |    INDEX FULL SCAN       | I_USER2     |    0 |     1 |      0 |00:00:00.01 |     0 |       |       |   |
|* 22 |    INDEX RANGE SCAN       | I_OBJ4     |    0 |     1 |      0 |00:00:00.01 |     0 |       |       |   |
|* 23 |  FILTER         |      |    1 |       |      0 |00:00:00.01 |     0 |       |       |   |
|  24 |   NESTED LOOPS        |      |    0 |     2 |      0 |00:00:00.01 |     0 |       |       |   |
|  25 |    TABLE ACCESS BY INDEX ROWID | USER$     |    0 |     1 |      0 |00:00:00.01 |     0 |       |       |   |
|* 26 |     INDEX UNIQUE SCAN       | I_USER1     |    0 |     1 |      0 |00:00:00.01 |     0 |       |       |   |
|* 27 |    INDEX RANGE SCAN       | I_LINK1     |    0 |     2 |      0 |00:00:00.01 |     0 |       |       |   |
|  28 |       NESTED LOOPS        |      |    1 |  2546 |  25853 |00:00:00.03 |   225 |       |       |   |
|  29 |        TABLE ACCESS BY INDEX ROWID    | USER$     |    1 |     1 |      1 |00:00:00.01 |     2 |       |       |   |
|* 30 |  INDEX UNIQUE SCAN       | I_USER1     |    1 |     1 |      1 |00:00:00.01 |     1 |       |       |   |
|* 31 |        INDEX RANGE SCAN        | I_OBJ5     |    1 |  2546 |  25853 |00:00:00.01 |   223 |       |       |   |
|* 32 |      INDEX RANGE SCAN        | I_USER2     | 8802 |     1 |   8802 |00:00:00.05 |     5 |       |       |   |
|* 33 |     INDEX UNIQUE SCAN        | I_VIEW1     | 8802 |     1 |   8802 |00:00:00.03 |  4963 |       |       |   |
|  34 |    NESTED LOOPS         |      |    0 |     1 |      0 |00:00:00.01 |     0 |       |       |   |
|* 35 |     INDEX FULL SCAN        | I_USER2     |    0 |     1 |      0 |00:00:00.01 |     0 |       |       |   |
|* 36 |     INDEX RANGE SCAN        | I_OBJ4     |    0 |     1 |      0 |00:00:00.01 |     0 |       |       |   |
------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DBMS_METADATA"."GET_DDL"('VIEW',"VV"."VIEW_NAME","VV"."OWNER") LIKE '%/*+%')
   2 - filter((("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND "O"."TYPE#"<>9 AND "O"."TYPE#"<>10
       AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND
       "O"."TYPE#"<>88) OR BITAND("U"."SPARE1",16)=0 OR (INTERNAL_FUNCTION("O"."TYPE#") AND
       ((SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2) OR ("U"."TYPE#"=2 AND
       "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) OR  IS NOT NULL))))
   5 - access("O"."NAME"="O"."OBJECT_NAME")
   8 - filter(BITAND("S"."XPFLAGS",8388608)=8388608)
   9 - access("S"."OBJ#"=:B1)
  10 - filter(((("O"."TYPE#"<>1 AND "O"."TYPE#"<>10) OR ("O"."TYPE#"=1 AND =1)) AND (("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND
       "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND "O"."TYPE#"<>9 AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND
       "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND "O"."TYPE#"<>88) OR BITAND("U"."SPARE1",16)=0 OR
       (INTERNAL_FUNCTION("O"."TYPE#") AND ((SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2) OR
       ("U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) OR  IS NOT NULL)))))
  14 - access("U"."NAME"='PSFT')
  15 - filter((DECODE("O"."STATUS",0,'N/A',1,'VALID','INVALID')='VALID' AND BITAND("O"."FLAGS",128)=0))
  16 - access("O"."SPARE3"="U"."USER#" AND "O"."LINKNAME" IS NULL)
       filter(("O"."NAME"<>'_NEXT_OBJECT' AND "O"."NAME"<>'_default_auditing_options_' AND "O"."LINKNAME" IS NULL AND
       DECODE("O"."TYPE#",0,'NEXT OBJECT',1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,'SYNONYM',6,'SEQUENCE',7,'PROCEDURE',8,'FUNCTION',9,
       'PACKAGE',11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE BODY',19,'TABLE PARTITION',20,'INDEX
       PARTITION',21,'LOB',22,'LIBRARY',23,'DIRECTORY',24,'QUEUE',28,'JAVA SOURCE',29,'JAVA CLASS',30,'JAVA
       RESOURCE',32,'INDEXTYPE',33,'OPERATOR',34,'TABLE SUBPARTITION',35,'INDEX SUBPARTITION',40,'LOB PARTITION',41,'LOB
       SUBPARTITION',42,NVL(,'MATERIALIZED VIEW'),43,'DIMENSION',44,'CONTEXT',46,'RULE SET',47,'RESOURCE PLAN',48,'CONSUMER
       GROUP',51,'SUBSCRIPTION',52,'LOCATION',55,'XML SCHEMA',56,'JAVA DATA',57,'EDITION',59,'RULE',60,'CAPTURE',61,'APPLY',62,'EVALUATIO
       N CONTEXT',66,'JOB',67,'PROGRAM',68,'JOB CLASS',69,'WINDOW',72,'SCHEDULER GROUP',74,'SCHEDULE',79,'CHAIN',81,'FILE
       GROUP',82,'MINING MODEL',87,'ASSEMBLY',90,'CREDENTIAL',92,'CUBE DIMENSION',93,'CUBE',94,'MEASURE FOLDER',95,'CUBE BUILD
       PROCESS',100,'FILE WATCHER',101,'DESTINATION','UNDEFINED')='VIEW'))
  17 - access("O"."OWNER#"="U"."USER#")
  18 - filter(("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR
       "I"."TYPE#"=9))
  19 - access("I"."OBJ#"=:B1)
  21 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
       filter(("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))
  22 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
  23 - filter(NULL IS NOT NULL)
  26 - access("U"."NAME"='PSFT')
  27 - access("L"."OWNER#"="U"."USER#")
  30 - access("U"."NAME"='PSFT')
  31 - access("O"."SPARE3"="U"."USER#")
  32 - access("O"."OWNER#"="U"."USER#")
  33 - access("O"."OBJ#"="V"."OBJ#")
  35 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
       filter(("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))
  36 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")


100 rows selected.

and


Plan hash value: 739349040

----------------------------------------------------------------------------------------------------
| Id  | Operation        | Name    | Rows  | Bytes | Cost (%CPU)| Time    |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |     |  1 |   107 | 66   (2)| 00:00:02 |
|*  1 |  FILTER         |     |    |    |  |    |
|   2 |   NESTED LOOPS        |     |  1 |   107 | 66   (2)| 00:00:02 |
|   3 |    NESTED LOOPS        |     |  1 |   102 | 66   (2)| 00:00:02 |
|*  4 |     HASH JOIN        |     |  1 | 80 | 65   (2)| 00:00:01 |
|   5 |      VIEW        | DBA_OBJECTS | 26 |   624 | 40   (0)| 00:00:01 |
|   6 |       UNION-ALL        |     |    |    |  |    |
|*  7 |        TABLE ACCESS BY INDEX ROWID   | SUM$    |  1 | 26 |  0   (0)| 00:00:01 |
|*  8 |  INDEX UNIQUE SCAN      | I_SUM$_1    |  1 |    |  0   (0)| 00:00:01 |
|*  9 |        FILTER        |     |    |    |  |    |
|  10 |  NESTED LOOPS       |     |  1 |   121 | 40   (0)| 00:00:01 |
|  11 |   NESTED LOOPS       |     |  1 | 99 | 39   (0)| 00:00:01 |
|  12 |    TABLE ACCESS BY INDEX ROWID| USER$    |  1 | 17 |  1   (0)| 00:00:01 |
|* 13 |     INDEX UNIQUE SCAN      | I_USER1    |  1 |    |  0   (0)| 00:00:01 |
|* 14 |    TABLE ACCESS BY INDEX ROWID| OBJ$    |  1 | 82 | 38   (0)| 00:00:01 |
|* 15 |     INDEX RANGE SCAN      | I_OBJ5    | 25 |    | 23   (0)| 00:00:01 |
|* 16 |   INDEX RANGE SCAN      | I_USER2    |  1 | 22 |  1   (0)| 00:00:01 |
|* 17 |  TABLE ACCESS BY INDEX ROWID  | IND$    |  1 |  8 |  2   (0)| 00:00:01 |
|* 18 |   INDEX UNIQUE SCAN      | I_IND1    |  1 |    |  1   (0)| 00:00:01 |
|  19 |  NESTED LOOPS       |     |  1 | 29 |  2   (0)| 00:00:01 |
|* 20 |   INDEX FULL SCAN      | I_USER2    |  1 | 20 |  1   (0)| 00:00:01 |
|* 21 |   INDEX RANGE SCAN      | I_OBJ4    |  1 |  9 |  1   (0)| 00:00:01 |
|* 22 |        FILTER        |     |    |    |  |    |
|  23 |  NESTED LOOPS       |     |  2 | 54 |  2   (0)| 00:00:01 |
|  24 |   TABLE ACCESS BY INDEX ROWID | USER$    |  1 | 17 |  1   (0)| 00:00:01 |
|* 25 |    INDEX UNIQUE SCAN      | I_USER1    |  1 |    |  0   (0)| 00:00:01 |
|* 26 |   INDEX RANGE SCAN      | I_LINK1    |  2 | 20 |  1   (0)| 00:00:01 |
|  27 |      NESTED LOOPS       |     |   127 |  7112 | 24   (0)| 00:00:01 |
|  28 |       TABLE ACCESS BY INDEX ROWID    | USER$    |  1 | 17 |  1   (0)| 00:00:01 |
|* 29 |        INDEX UNIQUE SCAN      | I_USER1    |  1 |    |  0   (0)| 00:00:01 |
|* 30 |       INDEX RANGE SCAN       | I_OBJ5    |   127 |  4953 | 23   (0)| 00:00:01 |
|* 31 |     INDEX RANGE SCAN       | I_USER2    |  1 | 22 |  1   (0)| 00:00:01 |
|* 32 |    INDEX UNIQUE SCAN       | I_VIEW1    |  1 |  5 |  0   (0)| 00:00:01 |
|  33 |   NESTED LOOPS        |     |  1 | 29 |  2   (0)| 00:00:01 |
|* 34 |    INDEX FULL SCAN       | I_USER2    |  1 | 20 |  1   (0)| 00:00:01 |
|* 35 |    INDEX RANGE SCAN       | I_OBJ4    |  1 |  9 |  1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$5947ACE7
   5 - SET$1     / O@SEL$2
   6 - SET$1
   7 - SEL$8     / S@SEL$8
   8 - SEL$8     / S@SEL$8
   9 - SEL$DA86A24D
  12 - SEL$DA86A24D / U@SEL$7
  13 - SEL$DA86A24D / U@SEL$7
  14 - SEL$DA86A24D / O@SEL$9
  15 - SEL$DA86A24D / O@SEL$9
  16 - SEL$DA86A24D / U@SEL$9
  17 - SEL$12     / I@SEL$12
  18 - SEL$12     / I@SEL$12
  19 - SEL$11
  20 - SEL$11     / U2@SEL$11
  21 - SEL$11     / O2@SEL$11
  22 - SEL$13
  24 - SEL$13     / U@SEL$13
  25 - SEL$13     / U@SEL$13
  26 - SEL$13     / L@SEL$13
  28 - SEL$5947ACE7 / U@SEL$3
  29 - SEL$5947ACE7 / U@SEL$3
  30 - SEL$5947ACE7 / O@SEL$4
  31 - SEL$5947ACE7 / U@SEL$4
  32 - SEL$5947ACE7 / V@SEL$3
  33 - SEL$6
  34 - SEL$6     / U2@SEL$6
  35 - SEL$6     / O2@SEL$6

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND
       "O"."TYPE#"<>9 AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND
       "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND
       "O"."TYPE#"<>88 OR BITAND("U"."SPARE1",16)=0 OR ("O"."TYPE#"=4 OR "O"."TYPE#"=5 OR
       "O"."TYPE#"=7 OR "O"."TYPE#"=8 OR "O"."TYPE#"=9 OR "O"."TYPE#"=10 OR "O"."TYPE#"=11 OR
       "O"."TYPE#"=12 OR "O"."TYPE#"=13 OR "O"."TYPE#"=14 OR "O"."TYPE#"=22 OR "O"."TYPE#"=87) AND
       (SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2 OR
       "U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR
       EXISTS (SELECT 0 FROM SYS."USER$" "U2",SYS."OBJ$" "O2" WHERE "O2"."OWNER#"="U2"."USER#" AND
       "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"=:B1 AND "U2"."TYPE#"=2 AND
       "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))))
   4 - access("O"."NAME"="O"."OBJECT_NAME")
   7 - filter(BITAND("S"."XPFLAGS",8388608)=8388608)
   8 - access("S"."OBJ#"=:B1)
   9 - filter(("O"."TYPE#"<>1 AND "O"."TYPE#"<>10 OR "O"."TYPE#"=1 AND (SELECT 1 FROM
       "SYS"."IND$" "I" WHERE "I"."OBJ#"=:B1 AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3
       OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))=1) AND
       ("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND "O"."TYPE#"<>9
       AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND
       "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND "O"."TYPE#"<>88 OR
       BITAND("U"."SPARE1",16)=0 OR ("O"."TYPE#"=4 OR "O"."TYPE#"=5 OR "O"."TYPE#"=7 OR
       "O"."TYPE#"=8 OR "O"."TYPE#"=9 OR "O"."TYPE#"=10 OR "O"."TYPE#"=11 OR "O"."TYPE#"=12 OR
       "O"."TYPE#"=13 OR "O"."TYPE#"=14 OR "O"."TYPE#"=22 OR "O"."TYPE#"=87) AND
       (SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2 OR
       "U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR
       EXISTS (SELECT 0 FROM SYS."USER$" "U2",SYS."OBJ$" "O2" WHERE "O2"."OWNER#"="U2"."USER#" AND
       "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"=:B2 AND "U2"."TYPE#"=2 AND
       "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))))
  13 - access("U"."NAME"='PSFT')
  14 - filter(DECODE("O"."STATUS",0,'N/A',1,'VALID','INVALID')='VALID' AND
       BITAND("O"."FLAGS",128)=0)
  15 - access("O"."SPARE3"="U"."USER#" AND "O"."LINKNAME" IS NULL)
       filter("O"."NAME"<>'_NEXT_OBJECT' AND "O"."NAME"<>'_default_auditing_options_' AND
       "O"."LINKNAME" IS NULL AND DECODE("O"."TYPE#",0,'NEXT
       OBJECT',1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,'SYNONYM',6,'SEQUENCE',7,'PROCEDURE',8,'F
       UNCTION',9,'PACKAGE',11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE BODY',19,'TABLE
       PARTITION',20,'INDEX PARTITION',21,'LOB',22,'LIBRARY',23,'DIRECTORY',24,'QUEUE',28,'JAVA
       SOURCE',29,'JAVA CLASS',30,'JAVA RESOURCE',32,'INDEXTYPE',33,'OPERATOR',34,'TABLE
       SUBPARTITION',35,'INDEX SUBPARTITION',40,'LOB PARTITION',41,'LOB SUBPARTITION',42,NVL(
       (SELECT 'REWRITE EQUIVALENCE' FROM SYS."SUM$" "S" WHERE "S"."OBJ#"=:B1 AND
       BITAND("S"."XPFLAGS",8388608)=8388608),'MATERIALIZED
       VIEW'),43,'DIMENSION',44,'CONTEXT',46,'RULE SET',47,'RESOURCE PLAN',48,'CONSUMER
       GROUP',51,'SUBSCRIPTION',52,'LOCATION',55,'XML SCHEMA',56,'JAVA
       DATA',57,'EDITION',59,'RULE',60,'CAPTURE',61,'APPLY',62,'EVALUATION
       CONTEXT',66,'JOB',67,'PROGRAM',68,'JOB CLASS',69,'WINDOW',72,'SCHEDULER
       GROUP',74,'SCHEDULE',79,'CHAIN',81,'FILE GROUP',82,'MINING
       MODEL',87,'ASSEMBLY',90,'CREDENTIAL',92,'CUBE DIMENSION',93,'CUBE',94,'MEASURE
       FOLDER',95,'CUBE BUILD PROCESS',100,'FILE WATCHER',101,'DESTINATION','UNDEFINED')='VIEW')
  16 - access("O"."OWNER#"="U"."USER#")
  17 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR
       "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9)
  18 - access("I"."OBJ#"=:B1)
  20 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edit
       ion_id')))
       filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edit
       ion_id')))
  21 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
  22 - filter(NULL IS NOT NULL)
  25 - access("U"."NAME"='PSFT')
  26 - access("L"."OWNER#"="U"."USER#")
  29 - access("U"."NAME"='PSFT')
  30 - access("O"."SPARE3"="U"."USER#")
       filter("DBMS_METADATA"."GET_DDL"('VIEW',"O"."NAME","U"."NAME") LIKE '%/*+%')
  31 - access("O"."OWNER#"="U"."USER#")
  32 - access("O"."OBJ#"="V"."OBJ#")
  34 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edit
       ion_id')))
       filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edit
       ion_id')))
  35 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "O"."NAME"[VARCHAR2,30], "U"."NAME"[VARCHAR2,30]
   2 - (#keys=0) "O"."NAME"[VARCHAR2,30], "U"."NAME"[VARCHAR2,30], "O"."OBJ#"[NUMBER,22],
       "O"."TYPE#"[NUMBER,22], "U"."TYPE#"[NUMBER,22], "U"."SPARE1"[NUMBER,22],
       "U"."SPARE2"[NUMBER,22]
   3 - (#keys=0) "O"."NAME"[VARCHAR2,30], "U"."NAME"[VARCHAR2,30], "O"."OBJ#"[NUMBER,22],
       "O"."TYPE#"[NUMBER,22], "U"."TYPE#"[NUMBER,22], "U"."SPARE1"[NUMBER,22],
       "U"."SPARE2"[NUMBER,22]
   4 - (#keys=1) "O"."NAME"[VARCHAR2,30], "U"."NAME"[VARCHAR2,30], "O"."OBJ#"[NUMBER,22],
       "O"."OWNER#"[NUMBER,22], "O"."TYPE#"[NUMBER,22]
   5 - "O"."OBJECT_NAME"[VARCHAR2,128]
   6 - STRDEF[30], STRDEF[128], STRDEF[19], STRDEF[7]
   8 - "S".ROWID[ROWID,10]
   9 - "U"."NAME"[VARCHAR2,30], "O"."OBJ#"[NUMBER,22], "O"."NAME"[VARCHAR2,30],
       "O"."TYPE#"[NUMBER,22], "O"."STATUS"[NUMBER,22]
  10 - (#keys=0) "U"."NAME"[VARCHAR2,30], "O"."OBJ#"[NUMBER,22], "O"."NAME"[VARCHAR2,30],
       "O"."TYPE#"[NUMBER,22], "O"."STATUS"[NUMBER,22], "U"."TYPE#"[NUMBER,22],
       "U"."SPARE1"[NUMBER,22], "U"."SPARE2"[NUMBER,22]
  11 - (#keys=0) "U"."NAME"[VARCHAR2,30], "O"."OBJ#"[NUMBER,22], "O"."OWNER#"[NUMBER,22],
       "O"."NAME"[VARCHAR2,30], "O"."TYPE#"[NUMBER,22], "O"."STATUS"[NUMBER,22]
  12 - "U"."USER#"[NUMBER,22], "U"."NAME"[VARCHAR2,30]
  13 - "U".ROWID[ROWID,10], "U"."NAME"[VARCHAR2,30]
  14 - "O"."OBJ#"[NUMBER,22], "O"."OWNER#"[NUMBER,22], "O"."NAME"[VARCHAR2,30],
       "O"."TYPE#"[NUMBER,22], "O"."STATUS"[NUMBER,22]
  15 - "SYS_ALIAS_4".ROWID[ROWID,10], "O"."NAME"[VARCHAR2,30], "O"."TYPE#"[NUMBER,22],
       "O"."OWNER#"[NUMBER,22], "O"."OBJ#"[NUMBER,22]
  16 - "U"."TYPE#"[NUMBER,22], "U"."SPARE1"[NUMBER,22], "U"."SPARE2"[NUMBER,22]
  18 - "I".ROWID[ROWID,10]
  19 - (#keys=0)
  20 - "U2"."USER#"[NUMBER,22]
  22 - "U"."NAME"[VARCHAR2,30], "L"."NAME"[VARCHAR2,128]
  23 - (#keys=0) "U"."NAME"[VARCHAR2,30], "L"."NAME"[VARCHAR2,128]
  24 - "U"."USER#"[NUMBER,22], "U"."NAME"[VARCHAR2,30]
  25 - "U".ROWID[ROWID,10], "U"."NAME"[VARCHAR2,30]
  26 - "L"."NAME"[VARCHAR2,128]
  27 - (#keys=0) "U"."NAME"[VARCHAR2,30], "O"."OBJ#"[NUMBER,22], "O"."OWNER#"[NUMBER,22],
       "O"."NAME"[VARCHAR2,30], "O"."TYPE#"[NUMBER,22]
  28 - "U"."USER#"[NUMBER,22], "U"."NAME"[VARCHAR2,30]
  29 - "U".ROWID[ROWID,10], "U"."NAME"[VARCHAR2,30]
  30 - "O"."OBJ#"[NUMBER,22], "O"."OWNER#"[NUMBER,22], "O"."NAME"[VARCHAR2,30],
       "O"."TYPE#"[NUMBER,22]
  31 - "U"."TYPE#"[NUMBER,22], "U"."SPARE1"[NUMBER,22], "U"."SPARE2"[NUMBER,22]
  33 - (#keys=0)
  34 - "U2"."USER#"[NUMBER,22]

195 rows selected.

I did not came down what's really wrong in the 2nd case. But it is some of these effects where I'd blame the database more than the SQL for invalidity.

GUI criticism

2010-12-23T12:44:00.007+01:00

As a DBA, I'm not a big fan of Graphical User Interfaces.
From my point of view they too often hiding more information than they visualize.
As I am no GUI developer, please excuse me if I name some things not correct.

I'll show an example. Please take this screenshot. I do not own the tool myself but got this via email from a developer.

What can I see?
In the top left corner I see 3 tabs, the active one is Sessions. This shows me a table with one line highlighted (sid 160). Below that table, there is something I would assume as a sub-tab. Especially the borders of that tab makes me think so, as it's 'above' the 'Sessions' tab.
The sub-tab Waits is the active on. In it I see 2 Tables, Current Waits and Total Waits.
At the bottom I can find a line Last refresh. I'm glad I have any timestamp anywhere.

This is all really fine. But I'm still missing something:

In general, I'd like to know the statement which generates the data I can see here. In the topmost table, I assume it will query v$session, v$process and some others to get the informations. But what's the query exactly? Even small changes can mean something different. Do you see the column CPU in the first table? I can not even imagine it's origin.

Many tools provide the ability to spool all SQLs they generate into a file. But as they do not associate the SQL to the visual representation, it's still guesswork.

I can not see which columns I do not see! Is the AUDSID important? Most of the time I'd say no. Until I have to deal with auditing

I can not adapt the query according to my needs. In the Current Waits table - I assume based on v$session_wait I am often interested in the P1, P2 and P3 values (sometimes even raw - it depends). Sometimes also the change of SEQ# is of some interest. This rapidly forces me to go to the command line, or SQL-window if you want.

But there are also clever examples of some GUIs. The one I like most (as it helps both blind people and others) is Providing Textual Descriptions of Charts. It's still not the SQL, but at least the content of a graph in re-useable form.

I have only considered of any kind of query, yet. no DML or DDL operations at the moment. Maybe it's worth another post in the future ;-)

create database might fail with 11.2.0.2

2010-12-20T20:19:00.005+01:00

I hit a nice bug at a test migration from 10.2.0.5 to 11.2.0.2.
As it's easier to convince Oracle Support of a problem if the testcase is just simple, a colleague reduced the testcase to

cretate a small pfile:


db_name=test
db_block_size=2048
db_create_file_dest=/tmp

and run

create database character set al32utf8;

the result is:


create database character set al32utf8
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-01501: CREATE DATABASE failed
ORA-01519: error while processing file '?/rdbms/admin/doptim.bsq' near line 416
ORA-00604: error occurred at recursive SQL level 1
ORA-01429: Index-Organized Table: no data segment to store overflow row-pieces
Process ID: 16723
Session ID: 47 Serial number: 1

For Orale Support this really was enough to file Bug:10410249 towards development.

If you have a Database with blocksize of 2k and a multibyte characterset, beware!

So far enough for Oracle Support. Just to provide some more insights:
the upgrade failed at
@catupgrd.sql
with


SQL> begin
 2    dbms_stats.delete_table_stats('SYS', 'OBJ$MIG');
 3    dbms_stats.delete_table_stats('SYS', 'USER$MIG');
 4    dbms_Stats.gather_table_stats('SYS', 'OBJ$MIG',  estimate_percent => 100,
 5                                   method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
 6    dbms_Stats.gather_table_stats('SYS', 'USER$MIG', estimate_percent => 100,
 7                                   method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
 8  end;
 9  /
begin
*
ERROR at line 1:
ORA-04063: package body "SYS.DBMS_STATS" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_STATS"
ORA-06512: at line 2

I did not know why dbms_stats seems to be broken. So I tried to run catupgrd.sql again - same problem.

Next step: recompile dbms_stats manually.
at running


@prvtstas.plb

it showed me there was an error at line 2175.
If you are curious what's going on there:


2175 CURSOR GET_COL_GROUP_USAGE(OWNER VARCHAR2, TABNAME VARCHAR2) IS
2176 SELECT CU.OBJ# OBJN, CU.COLS,
2177    (CASE WHEN BITAND(CU.FLAGS, 1) = 1 THEN 'FILTER ' ELSE '' END) ||
2178    (CASE WHEN BITAND(CU.FLAGS, 2) = 2 THEN 'JOIN ' ELSE '' END) ||
2179    (CASE WHEN BITAND(CU.FLAGS, 4) = 4 THEN 'GROUP_BY ' ELSE '' END) USAGE,
2180    CU.FLAGS USAGEFLG
2181 FROM SYS.COL_GROUP_USAGE$ CU
2182 WHERE CU.OBJ# = (SELECT O.OBJ# FROM SYS.OBJ$ O, SYS.USER$ U
2183                     WHERE O.OWNER# = U.USER#
2184                       AND U.NAME = OWNER
2185                       AND O.NAMESPACE = 1
2186                       AND O.REMOTEOWNER IS NULL
2187                       AND O.LINKNAME IS NULL
2188                       AND O.SUBNAME IS NULL
2189                       AND O.TYPE# = 2
2190                       AND O.NAME = TABNAME)
2191   ORDER BY ...

I checked COL_GROUP_USAGE$ - but it did not exist!

Some grep in $ORACLE_HOME/rdbms/admin brought me to c1102000.sql
There I can read:


-- #(9577300) Column group usage
create table col_group_usage$
(
 obj#              number,                                 /* object number */
 /*
  * We store intcol# separated by comma in the following column.
  * We allow upto 32 (CKYMAX) columns in the group. intcol# can be
  * upto 1000 (or can be 64K in future or with some xml virtual columns?).
  * Assume 5 digits for intcol# and one byte for comma.
  * So max length would be 32 * (5+1) = 192
  */
 cols              varchar2(192 char),              /* columns in the group */
 timestamp         date,     /* timestamp of last time this row was changed */
 flags             number,                                 /* various flags */
 constraint        pk_col_group_usage$
 primary key       (obj#, cols))
 organization index
 storage (initial 200K next 100k maxextents unlimited pctincrease 0)
/

As I tried to create that table manually, I recieved my ORA-01429.

We discussed some workarounds internally.
I created the IOT (without approval from Oracle Support) with cols varchar2(192 byte). Guess what, it worked! Also @catupgrd.sql was fine afterwards.
As I'm sure in this particular DB there are only column names with plain ASCII, this will not be an issue in this particular DB. But some other suggestions like creating a heap table or IOT without the cols as part of the index definition. This might result in performance problems of dbms_stats, but does not affect any logic (as my WA does).

UPDATE:
Patch:10410249 is provided for this issue now. It's labeled generic, as it contains only some sql files for $ORACLE_HOME/rdbms/admin and the proper patch (and rollback) scripts.

11.2.0.2 multicast fails totally

2010-11-12T09:08:00.013+01:00

Recently Oracle released a new Patch Set: 11g Release 2 (11.2.0.2) Patch Set 1

In its README there is no word about multicast or the IP 230.0.1.0. It just contains a link to the Known Issues specific to the 11.2.0.2 Patch Set. Still no word there about multicast or the IP 230.0.1.0.
But why is it so important for me, more than it seems to be for Oracle?

Oracle has introduced a new feature in 11.2.0.2 Grid Infrastructure: It uses multicast communication on the cluster interlink for some purposes.
To be more precise, it uses the IP 230.0.1.0 by default for this purpose.

Multicast per se is not a bad thing at all, but it should be done right, not the Oracle way:

The IANA has defined a set of IP adresses to be used for multicast communication. All together this is the range from 224.0.0.0 to 239.255.255.255.
As 230.0.1.0 is within this range - everything fine? NO!
The block 225.0.0.0-231.255.255.255 is marked as RESERVED. And the IETF has a clear wording about these addresses:

Applications MUST NOT use addressing in the IANA reserved blocks.

It seems I'm not the first person who found out this issue. If you read
11.2.0.2 Grid Infrastructure Install or Upgrade may fail due to Multicasting Requirement [ID 1212703.1] you see the

Cause

The cause of this issue is CSSD being unable to establish network communication on the private interconnect network. Assuming that Cluster Verify (cluvfy) has succeeded on all network checks or you are upgrading to 11.2.0.2 whereas the previous release was not experiencing communication issues, multicast not being enabled on the private network is a potential cause.

Oracle Grid Infrastructure 11.2.0.2 introduces new feature called Redundant Interconnect allowing for Oracle Supplied redundancy for the cluster interconnect. With this new feature, multicast network communication on the private interconnect network is utilized on bootstrap to establish communication with peer nodes in the cluster, once communication is established network communication is then switched to unicast. This mulitcast communication utilizes the 230.0.1.0 address (port 42424) on the private interconnect network. Therefore multicast on the private interconnect network must be enabled and properly functioning on all cluster nodes for the mulitcast address 230.0.1.0 (port 42424). Should multicast communication fail, the end result will be the inability for the node to join the cluster (as shown above in the symptoms).

and a suggested

Solution

It has been found that the functionality of multicast on the 230.0.1.0 (port 42424) network address has been problematic with some network environments resulting in the issue stated above. To address this issue Oracle has released Patch: 9974223 on top of 11.2.0.2 . This patch makes use of the 224.0.0.251 (port 42424) multicast network address in addition to the 230.0.1.0 (port 42424) multicast address. Multicast must be enabled on one of these two addresses to allow for Oracle Grid Infrastructure to successfully start on all cluster nodes.

Patch 9974223 has a size of 153M for Linux x86-64. but that's not a problem per se.

The real problem is the spirit of the patch: Instead of making the multicast IP configureable (by that it would be possible to use a dedicated IP address which was assigned for exactly that purpose) Oracle decided to use another IP: 224.0.0.251. So let's look for that IP in more detail: In the IANAs Document for multicast IPs 224.0.0.251 is defined for the use with mDNS - Multicast DNS.
Ok. Now we have at least an IP which is defined to be used with a protocol to announce available resources within a local network. But still, Oracle failed:
Now 224.0.0.251 (port 42424) is used. But this conflicts with the IETFs Document for Multicast DNS: There you can read:

When this document uses the term "Multicast DNS", it should be taken
to mean: "Clients performing DNS-like queries for DNS-like resource
records by sending DNS-like UDP query and response packets over IP
Multicast to UDP port 5353."

5353 != 42424

At the end my plea to Oracle is: Make the Multicast IP of a Cluster configurable by the customer! Follow existing standards! Many companies has different requirements regarding the usage of IPs - so any hard coded value will have disadvantages.

Update: I've just created SR:3-2347539801 for this - just out of curiosity.

Oracle joke

2010-10-29T12:34:00.004+02:00

Sometimes it's really funny to read logfiles (and yes, some sarcasm helps a lot!)

my todays joke is this one:


ALTER DATABASE ENABLE BLOCK CHANGE TRACKING
Fri Oct 29 11:28:04 2010
Block change tracking file is current.
Fri Oct 29 11:28:30 2010
Starting background process CTWR
Fri Oct 29 11:28:30 2010
CTWR started with pid=58, OS id=9457
Fri Oct 29 11:28:55 2010
ORA-1013 signalled during: ALTER DATABASE ENABLE BLOCK CHANGE TRACKING...
Stopping background process CTWR
Fri Oct 29 11:29:26 2010
Background process CTWR not dead after 30 seconds
Killing background process CTWR
Fri Oct 29 11:29:26 2010
PMON (ospid: 4322): terminating the instance due to error 487
Fri Oct 29 11:29:26 2010
ORA-1092 : opitsk aborting process
Fri Oct 29 11:29:26 2010

What was going here?

Someone tried to enable Block Change Tracking.

The process CTWR started.

(for some reason) the DBA hit ^C before the operation finished.

The instance tried to stop the CTWR.

CTWR did not stop within 30 seconds - therefore the instance tried to kill it.

PMON found CTWR dead (ORA-487 "CTWR process terminated with error"), considered this a critical issue and terminated the instance. (ORA-1092 "ORACLE instance terminated. Disconnection forced")

For me this is one if these bugs which are not worth investigating.

back to the future

2010-08-05T10:18:00.004+02:00

Last month I walked through some boxes full of my old books at my parents house. There I also found my first school book about information technology. It was printed in 1990. So for me it covers almost the beginning of my life with IT.

To my surprise, the first page is not about bits, bytes or ICs. It's about communication:

With my todays background I scanned through the book and searched if there is anything about SQL or at least databases:
3 types of databases where mentioned there:

hierarchical databases
network databases
relational databases

Especially relational databases where described as slow, but flexible ;-)

SQL was not mentioned within this book, but available database systems where listed:

dBASE III PLUS, Everyman, MS-R:Base, Informix, Dataflex (no Oracle or DB2, sorry).

As the first pichture in the book is still valueable, so is also the last:

Sometimes it's worth to look into his own history, and books I've read seem to be a valuable method. Many things have changed, some expectations did not come true. But it's worth to know about the own foundation.

debugging MOS

2010-05-13T13:44:00.003+02:00

If you ever wanted to know a little bit more about the Flash Client of My Oracle Support, you can enable it's debug mode:

Hold down the CTRL key and click to Oracle logo.

I have limited use of these informations, but maybe someone with some more knowledge of Flash can use them?

are my resources in favored place?

2010-04-16T08:21:00.002+02:00

On a 11gR1 Oracle Cluster I had an issue with some resources not in their favored place.
While investigating, I had to get the affected resources fast (at least faster than reading a huge list of resources manually and think if they match the rules).

This brought me to a small script:


crs_stat -f | awk ' BEGIN { FS="=" 
                            c=0}
/^NAME=/{n=$2}
/TYPE=/{t=$2}
/HOSTING_MEMBERS=/{hm=$2}
/PLACEMENT=/{p=$2}
/TARGET=/{g=$2}
/STATE=/{s=$2; 
  ch=s
  gsub("ONLINE on ", "",ch)
  if( (p=="favored") && !( index(hm,ch)>0 ) ) {
    printf("%-30s%-20s%-17s%-15s%-30s\n", n, " should be located on ", hm, " but has STATE: ", s) ;
    c+=1 ;
    }
} 
END { exit c }'

It prints out a line for every resource which has a favored placement, but currently is not on any HOSTING_MEMBERS.

I'm sure it's not the best awk-code, but it's fine for me.

To use it in other scripts (e.g. monitoring) I can use | wc -l to get the number of suboptimal located resources or just get the return value $?

developers joke

2010-03-22T21:58:00.003+01:00

Yesterday evening I had to deal with the product of a really funny Oracle developer:
I had requested a CRS patch for Bug:8328904 under really high preassure (Management involved). With a target date of 3rd week of March 2010, I got the patch on 19-Mar-2010 about 2pm local time. Just in time!
So I had the fun to patch a test system on Sunday evening. As allways, it's not that easy as it sounds:
after a slow, but successful preparation of one node and a really easy patch, at restart of the CRS I got

/etc/init.d/init.crs: [[: not found

Ok. I knew this Patch was knitted with really hot needles, so after some checking we applied this diff:

 72c72
< if [[ "$CMD" = "init.crs" && "$PARENT_CMD" != "startpar" ]]; then
---
> if [ "$CMD" = "init.crs" ] && [ "$PARENT_CMD" != "startpar" ]; then

All the patching ended at Monday, 2am.

Of course, I updated my SR on Monday morning with this slightly detail.
The answer was overwhelming:


/etc/init.d/init.crs: [[: not found encountered while startup is a known issue and bug is already filed.( bug# 9131555).

The problem is with the 11.1.0.7. bundle 2 . Since the patch was created on top of 11.1.0.7 bundle 2 hence the same error got passed on. The workaround is correct and is same as mentioned in the above mentioned bug. So no problem it is a supported workaround.

What a funny developer!
delivering a patch for a escalated but just in time, delivering another known within it.

Who can tell me why i was not laughing?

Job, hobby or passion?

2010-03-17T01:01:00.001+01:00

In my last post I summarized the answers I got on a mailing list for the short question:

what ist the fastest SQL you can create?

maybe the most remarkable answer was

I think y'all need a hobby- and one that doesn't include oracle!

Well, maybe I have to answer. Which is not that easy.
Let's try to confine a little bit: Let's start with Job. I see a job as a work which I get paid for. So i need a job to make a living.
At first view a hobby is a totally contradiction to a Job: I do it at my own will and possible even spend money to do it. A hobby is something I do to have fun.
But sometimes it's not that easy.
Even if job and hobby are a contradiction in money and choice, they are not imperatively a contradiction in fun.
Assume, you have a job which at the same time is fun. Why not enjoy this: instead of paying for fun, you get paid for! Maybe it's ok if I call it an avocation?
Of course, it's not that easy all the time, but nothing is that easy, not even a hobby ;-)
I see one more bonus in this situation: I assume I'm quite good in the tasks I get paid for. And at least at the moment I get a fair salary. I hope, both will at least stay at this level for a long time.

This all leads me to an answer:

I have a job in IT, especially Oracle. Why do I need a hobby?

fastest SQL?

2010-03-16T09:33:00.003+01:00

Recently I asked what ist the fastest SQL you can create? on oracle-l mailing list.
It was a kind of challenge. But not a real clever one.
I was more interrested in the way people think and react to this question than in a particular SQL or result.

Here are some summaries for those who are interrested:
There where 33 replies (until now), these can be grouped by some categories (some mails belong to more than one):

select 1 from dual; was suggested by Niall Litchfield and Kevin Lidh.
Don Granaman showed an imprived version with a view on sys.v_dual, but Niall and Norman Dunbar argued, that's what fast_dual was introduced for.

Niall and Wolfgang Breitling asked for better specifications for the quest, which I left open by intention.

Another approach started with exit; by Daniel Fink. Robert Freeman called this not a SQL, but Grant Allen (--) and Jaromir D.B. Nemec ("empty statement") tried the same direction.
Job Miller suggested the qhole quest as a kind of trick question With the answer You look at the statement/business logic and decide that you don't need to run that statement at all. and Daniel gave a real world example for this effect.

Toon Koppelaars and Andre van Winssen both suggested query result cache. I tried to test this but could not find a suficient measurement. Brandon Allen raised the method by checking elapsed_time down to microseconds from v$sql/v$sqlstats.

Wolfgang did one step further with his client side cache.

A discussion about other products like purple and Mauve by Jack C. Applewhite, Christopher Boyle, John Piwowar and Jason Heinrich.

I think y'all need a hobby- and one that doesn't include oracle! by Kellyn Pedersen and Kathy Duret seems to be worth a seperate blog!

NOTE:Unident of disk

2010-03-12T10:23:00.003+01:00

I just wondered where the lines


*** 2010-03-12 10:25:13.862
NOTE:Unident of disk:/appl/oracle/asm_disks/c3t60014380024D39280000A000039B0000d0s0_eva
NOTE:Unident of disk:/appl/oracle/asm_disks/c3t60014380024D39280000A00003A10000d0s0_eva

came from in my +ASMS744_rbal_3119.trc trace file every minute.

As i did not find any matches for NOTE:Unident of disk in MOS, I tried to open a SR there. Just in the preperation, I checked the status of this particular Disk:


select header_status, path 
from v$asm_disk 
where path like '%c3t60014380024D39280000A000039B0000d0s0%';

HEADER_STATUS PATH
------------- ------------------------------------------------------------------
CANDIDATE     /appl/oracle/asm_disks/c3t60014380024D39280000A000039B0000d0s0_eva

So I added these disks to a DiskGroup and the trace file did not grow any further.

I didn't found anything about Unident of disk in the docu, MOS or google. sadly.

USL in excel - without the need to read

2009-12-31T01:01:00.001+01:00

Neil Gunther created a nice small excel spreadsheet to calculate the key values sigma and kappa for his Universal Scalability Law - primarily for the use in his GCaP class. Beside some other problems with excels numerical precision and Negative Scalability Coefficients in Excel I dislike the ugly 'create a graphic, let it show a trendline and it's coefficients, add these into some cells and continue' step. Fortunately, Scott Roberts has created some google spreadsheets where he implemented a real formula (as google spreadsheets does not support trendlines and their coefficients). These are mentioned in Neils blog entry Scalability in a Spreadsheet - google style.

As this method is also available in excel, I decided to extend Neils excel by some functions to avoud the read and insert part.

It is not just easier to apply, it's also more accurate as the coefficients shown in the graph are shown with small rounding.

With the formula shown here:

Don't be confused by the semicolons in the formula, depending on your language settings, excel sometimes use colon or semicolon to separate fields of the formula.

Here the detailed fields I've added:

Field H11:	R²
Field I8:	=INDEX(LINEST($G8:$G14;$F8:$F14^{1,2}; FALSE); 1)
Field I9:	=INDEX(INDEX(LINEST($G8:$G14;$F8:$F14^{1,2}; FALSE;TRUE); 1);2)
Field I10:	=INDEX(INDEX(LINEST($G8:$G14;$F8:$F14^{1,2}; FALSE;TRUE); 1);3)
Field I11:	=INDEX(INDEX(LINEST($G8:$G14;$F8:$F14^{1,2}; FALSE;TRUE); 3);1)

I hope i used the LINEST function correct, and this might help others to reduce one step while playing with USL.

MOS can't count

2009-12-30T14:07:00.004+01:00

MOS (the flash version) can not even count up to 3:

Can someone show me the hidden element?

Guerrilla Capacity Planning arrived - let's prepare the underground resistance!

2009-12-30T11:30:00.001+01:00

Some weeks ago one of my X-mas gifts to myself arrived. (One of those I'm always allowed to buy as none of my relatives would ever imagine to buy me such books for Christmas): Guerrilla Capacity Planning by Neil J. Gunther. In fact, I was only interested in his Universal Scalability Law, which did me a good service in a performance review some weeks ago.

I really like this book and I'm sure it will go with me for some more time; at least every day I go to work by public transport, but also in many other situations.

The idea which gave the book it's name is the Guerrilla Capacity Planning. This is described in the first 2 chapters.

I read it as another iteration of KISS, applied to Capacity Planning. The main goal is to achieve fast and just good enough predictions by the usage of minimal resources and time. It just leads one step further and advises to be well prepared in lean tools and flexible methods to reach the goal when needed. In my current situation, I also see it as a hint to do Capacity Planning in every project, even if it's not calculated within the project plan. But keep it small enough to hide it within the jitter every project contains. The following chapters try to provide some simple weapons to be prepared for all tactical situations which can occur.

Chapter 3 is about significant digits, rounding rules and errors.

These 13 pages where worth reading for me, because I never had a full qualified mathematical or statistical education. It might be just enough to estimate the errors I carry through all my work and provide them (and their meanings to the results) in any discussion, presentation and so on.

Chapters 4 to 6 are the real reason why I ordered this book: the Universal Scalability Model (or Law?). Neil leads in a very consistent way, why scalability is not only limited by contention, as Amdahls law implies, but also by coherency, which leads to a retrograde in scalability beyond p*.

Not only the Model itself is described, but - important for all Guerrilleros - an easy method to gain the parameters for σ and κ out of some measured data with excel is provided. Not mentioned in the book directly but easily to find is the spreadsheet which contains exactly the method Neil provides.

I would like to have also other methods provided within the book to circumvent the problem with excels numerical precision, but Neil provided (and discussed it's difficulties) a glimpse of a implementation in R somewhere else.

In chapter 7 the main focus is on virtualization across all scales, from in-CPU (Hyperthreading) up to Grids and P2P on the other end of the scale. Here it's more about queues, schedules and polling cycles. I had the feeling I should have read Analyzing Computer System Performance with Perl::PDQ before. But I have not. (I will, after the 2nd ed. is published). Also there is not too much ammunition for my underground resistance. More a rough description of that area and some major snares.

Currently I cannot say much about chapters 8 to 11 as I'm in the middle of 8 at the moment. they will also be worth to write about them - in the future.

There is also some criticism outside there which laments, USL does only provide limited practical use for the forecast, until a set of data is measured and σ and κ are derived from these. In comparison, Amdahls s can be measured so much easier as the single threaded phase of a program. This criticism provides its own error inside: If s can be measured, this mean the setup is well instrumented and known and the target hardware is chosen (otherwise, the measurement must be somehow translated to the target hardware) at least for a single process. But, with a well instrumented and know setup, also all code parts which will account to coherency can be spotted and therefore not only Amdahls s leads to σ, but the coherency part leads to κ.

Unfortunately, even in a well instrumented Software like Oracle, nobody sorted all the wait events where they account to.

PRKO-2007 is not always correct

2009-11-22T21:12:00.001+01:00

This week I had to stop and start an instance in a CRS-cluster. Nothing special so far, I just hit a PRKO-2007 error. For those which does not know it by heart, here the short description:

PRKO-2007: "Invalid instance name: "
Cause: An invalid instance name was entered.
Action: Use the correct instance name for the database. Run 'srvctl config database
-d ' command to find out all instances of a database in the
Cluster Database Configuration.

and here the copy of my commands (solution included):

oracle@aves742p:~/ [EDWP031] srvctl stop instance -d EDWP03 -i EDWP031
PRKO-2007 : Invalid instance name: EDWP031
oracle@aves742p:~/ [EDWP031] srvctl stop instance -d EDWP03_SITE1 -i EDWP031

I'm sure, you see the difference, and it's not the instance!
Just once again: don't trust the error message 100%, sometimes it's still worth to use the brain ;-)

hardware lasts longer than software

2009-11-11T22:07:00.004+01:00

A week ago, Oracle has replaced its good old support platform MetaLink with MyOracleSupport (MOS).
The thing I like most about MOS is the non Flash-based HTML-Version.
Many discussions about the need, target and failures during the last week of transition are ongoing in blogs, mailing lists, forums; I also had to suffer and had to communicate it. But that's not what's this blog is about.

I got into touch with MetaLink in 2000, as Oracle tried to move the customers from calling the Support to a self-service platform. They promoted MetaLink on many events, with everything you can imagine. The transition was not forced, but within a short period of time most customers liked the possibility to get many answers faster than on the phone. A real win-win situation.
Also I learned how to use MetaLink effective and therefore like it.

On one of these events, I got the promo-mug. I like it, because I like MetaLink.
Nowadays, everytime I feel without any Support from Oracle, I cling to the mug, hopefully filled with good, strong, hot coffee. This makes me feel a little bit better.
And sometimes, hardware really lasts longer than software, especially if you have the hardware in your hands and take care of.

rollback in PL/SQL

2009-11-10T01:01:00.003+01:00

I recently came across a slightly, but possible dangerous mismatch between transactions and the PL/SQL code based on it.
Here a cut down example (not very realistic, but easy to follow):


create table mytab (myval number);

create or replace procedure tuwas (
    varA in number,
    varB in number,
    varC OUT number)
is
begin
 varC := varA;
 insert into mytab values (varA);
 if varB = 0 then
   commit;
 else
   rollback;
 end if;
end;
/

The Idea behind the code is, to get varA and varB as input parameters, do some calculations (missing here), insert the result into a table and return the calculated value for next steps.
There might be reasons to rollback the DML within the procedure, which is not seen as an error.

And here the result of the procedure:


set serverout on

declare
 vA number;
 vB number;
 vC number;
begin
 vA := 1;
 vB := 0; -- commit;
 tuwas(vA, vB, vC);
 dbms_output.put_line('vC: ' ||vC);
 vA := 2;
 vB := 1; -- rollback;
 tuwas(vA, vB, vC);
 dbms_output.put_line('vC: ' ||vC);
END; 
/

vC: 1
vC: 2

select * from mytab;

    MYVAL
----------
        1

What's dangerous here? Even with


 vA := 2;
 vB := 1;

the return value


vC: 2

is given. But t's not visible in the table, as the procedure did a rollback.
I learned from this example never to populate return-values until you are sure it's really stored (if this is a requirement, of course).

Oracle Security Options

2009-11-09T09:04:00.003+01:00

Last week I attended a 1 day Seminar about Oracle Security by Dominique Jeunot.
Dominique did a great job to squeeze everything she knows into one day, and it was allways obvious there is a lot more she could tell us in the script and even more in her brain. Nevertheless she created a very good overview with the right level on all topics. It was obvious she could only make a kind of teaser, but it was a very tasty one!
Maybe the most important picture she draw was the first overview about all available features, options and products. I did the effort to draw it for my own joy and will share it here, as it might be helpful for others as well. All options are colored red, so you can see where you have to take extra money.

In general you can say: for nearly every issue there are possibilities to do it without extra money (but extra effort) or you can buy a solution.

If someone wants the visio behind this jpeg, just contact me :)

ML-fun

2009-10-27T11:31:00.003+01:00

sometimes it's really funny to see error messages.
This ony I got today:

I think, I'm missing my workspace (or is it the application, who knows ...)

Maybe this is only because of the ongoing transition phase and within the next days everything will be better...

xplan 2.1.3 is out

2009-10-02T10:21:00.005+02:00

Alberto Dell'Era released a ne version of his xplan utility. Version 2.1.3 is out now.
Why is it's worth to mention?
1st he fixed a problem I faced within extraordinary speed (< 4 hrs!):


declare /* xplan_exec_marker */  *** error before main block (
processing XPLAN_OPTIONS ) *** -- main block
                                *
ERROR at line 1:
ORA-06550: line 1, column 34:
PLS-00103: Encountered the symbol "*" when expecting one of the following:
begin function pragma procedure subtype type <an identifier>
<a double-quoted delimited-identifier> current cursor delete
exists prior

--in setup.lst it's this error:

declare /* xplan_exec_marker */ -- process options
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 28

and 2nd: he didn't even announce it himself, so someone must do it :)

result cache not enabled for owner SYS and SYSTEM

2009-09-14T08:10:00.005+02:00

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

2009-08-26T21:44:00.003+02:00

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

2009-08-18T20:19:00.002+02:00

"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

2009-08-04T20:55:00.002+02:00

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

2009-07-15T11:27:00.004+02:00

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

2009-05-18T22:00:00.000+02:00

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

2009-05-09T01:01:00.003+02:00

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.

odyssey of a session - Part 1

2009-05-07T20:48:00.008+02:00

Today a single session made my day!
In the later morning I got a call form a colleague. I would call him a kind of ApplicationDBA, even there is no such definition in our company. He asked my, why a particular session is doing a lot of sorts in gv$session_longops. He also tole me, what the session was doing:
this script was started in the early morning by an operations-guy:


drop materialized view MV;

CREATE MATERIALIZED VIEW MV
TABLESPACE MVIEW_DATA
NOCACHE
NOLOGGING
NOPARALLEL
REFRESH FAST WITH ROWID
AS
select * from USER.TABLE@LINK;

CREATE INDEX I1 ON MV
(...)
LOGGING;

CREATE INDEX I2 ON MV
(...);

CREATE INDEX I3 ON MV
(...)
;

CREATE INDEX I4 ON MV
(...)
;

CREATE INDEX I4 ON MV
(...)
;

CREATE INDEX I5 ON MV
(...)
;

CREATE INDEX I6 ON MV
(...)
;

CREATE INDEX I7 ON MV
(...)
;

CREATE INDEX I8 ON MV
(...)
;

CREATE INDEX I9 ON MV
(...)
;

CREATE INDEX I10 ON MV
(...)
;

CREATE INDEX I11 ON MV
(...)
;

CREATE INDEX I12 ON MV
(...)
;

CREATE INDEX I13 ON MV
(...)
;

GRANT SELECT ON  MV TO USER1;

--currently running as snapadmin:

exec dbms_snapshot.refresh('USER.MV ','c');

In fact, I did not know what's going on. But that's enough teason to start digging.
And as the session did run for some time now, I was pretty sure it would continue to do so and therefore give me enough time to search and think. (Yes, some Services where unavailable, but he agreed not to kill any session or reboot an instance without knowing what's going on).

My first search was the actual SQL (v$session sql_hash_value => v$sql_text).
It was something like

INSERT /*+ APPEND */ INTO MV AS
SELECT .... FROM
USER.TABLE@LINK;

I told my colleague about this, but he replied 'Yes, but I checked the MV, it's empty'.
Ok, this even I could explain by a simpe 'ASIC' - or in this phone call 'you will see the rows after the commit, and this can last'.
This answer was acceptable, but nevertheless, the legitimate question was 'how long will it run; and wehre is it standing right now?'
How to answer this?

v$session_longops

Does only give informations about the 'current' longop, not about the total transaction.

But as we assumed, there might be indices written right now (vsession_longops showed sort and sort merges), the next step was to check anything we can get about the indices.
Even we could not access them directly, we could check for some footprints:

select seg.owner, seg.segment_name, seg.segment_type, (sum(blocks)*8192)/(1024*1024) MB
from dba_segments seg, dba_indexes ind
where ind.table_owner ='OWNER and ind.table_NAME='MV'
and ind.owner = seg.owner
and ind.index_name = seg.segment_name
group by  seg.owner, seg.segment_name, seg.segment_type
order by MB desc
/

By sampling this query we saw some full populated indices, and one just growing. So even for us it was clear what's going on: the indices are calculated and populated.

The next question I had to face was 'why is the index-creation so slow?'
Hmmm. Who can answer this?
manual sampling vsession_wait gave some hints:
some gc... events and latches on the buffer where top.
(Did I mention, this is on a 2-node RAC? ;-) )

It was time to switch to the other node and check for running sessions which has also cache fusion waits, and could interfer with our session of the day.

After some sampling and searching (vsession, vsession_wait, later on vsql_text) and additional clicking in Grid control (yes, it can be useful!) I found a possible culprit:
USER1 run a lot of queries on a view which contains USER.MV.
I didn't proove, but I guess, Instance2 held the master for MV as there where 100 sessions hanging on the View. But I didn't care of - GV$GCSPFMASTER_INFO - I just wanted to get rid of all the cache-fusion waits until the MV is created.
Some might have seen the solution for this yet, it's not hidden.
Our Silver Bullet of The Day was a simple:

REVOKE SELECT ON MV FROM USER1;

(had to be run for several times, as there where DDL-timeouts...))

Afterwards, the index-creation run quite fast - fast enough for us.

It's not the end of the story, but enough for now,
just to leave enough space for Part 2 ;-)

Some infos at the end:

Many parts I can not explain in more detail, simply as I didn't log everything I did and therefore have no exact queries, output or whatever. (Maybe this is the most important area to improve myselve!)

I also used Tanel Poders latchprofx and snapper to collect well sampled data about the session.

New Blogger in Bloggosphere

2009-05-07T20:39:00.002+02:00

There is a new Blogger: Mathias Zarick wrote hist first blog entry.
He is a clever guy, with a lot of knowledge and passion and a very systematic aproach.
I'm very glad he shares his findings with us now.

'symbolic link' for Windows ODBC drivers

2009-03-02T21:12:00.005+01:00

Maybe you know this situation: (nearly) everyone in your Company has local Admin grants on 'his' (and her) local PC. So they install everything they want, also ODBC-Drivers with any name on any local location.

No problem so far, but if they want to share something with an ODBC connection string, they are doomed. Everyone has another version in another ODBC-location.

What else than to 'fake' any ODBC name to any directory you want?

Here is my solution (I don't know, if it's supported. It just works for me!)

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\<INSERT_ODBC_NAME_HERE>]
"APILevel"="1"
"CPTimeout"="60"
"ConnectFunctions"="YYY"
"Driver"="C:\\PATH\\TO\\YOUR\\DRIVER\\SQORA32.DLL"
"DriverODBCVer"="03.51"
"FileUsage"="0"
"Setup"="C:\\PATH\\TO\\YOUR\\DRIVER\\SQORAS32.DLL"
"SQLLevel"="1"

Just use the Driver and Setup files from an existing ODBC-Source.

Might it work for you also ;-)

please increase my TEMP Tablespace

2009-02-28T19:29:00.002+01:00

Today I recieved a call. I was asked to increase a TEMP Tablespace as it was 100% used. I checked the TS and it was correct - 100% full. So I added additional 10% to the tempfile.
So what?
It's only fighting symptoms.
After some minutes I got the same call again.
I suggested the Apps responsible to check the statements or response time. After some minutes he agreed. Because of some new analyses some plans flipped. This caused some more scans and in memory sorts than necessary, which landed in Temp TS.
The solution was obvious: change the objects stats back to 'good' values.
The learnings: once again: don't fight symptoms. It will bring you to no end. Find the initial cause and fix it.

pipelined function vs. dbms_output

2009-01-20T14:26:00.000+01:00

I don't like dbms_output.
So I came to pipelined functions.
here a small example:

create or replace
function dummy
return DBMS_DEBUG_VC2COLL
PIPELINED -- NOTE the pipelined keyword
is
begin
pipe row (to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS' ));
dbms_lock.sleep(15);
pipe row (to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS' ));
return;
end;
/

Make sure you set the arraysize of SQL*PLUS (or whatever) small enough, otherwise you will get a bunch of results at once, not when they occure.

set arraysize 1

select * from table(dummy());

gives

COLUMN_VALUE
--------------------
20-JAN-2009 14:24:17

and after 15 sec.

20-JAN-2009 14:24:32

It might not be a big advantage for anyone, just a thing I like.

multiple ASM instances on one node (11gR1)

2009-01-13T22:45:00.001+01:00

I just searched a way to circumvent the limit of 63 ASM DiskGroups in a storage system.
My first thought was to create a 2nd (or multiple) ASM instances on the same node which manage seperated DGs. This is quite easily possible: Just create a spfile for the 2nd ASM, just make sure to add *.DB_UNIQUE_NAME to avoid ORA-15150. I also recommend to use different ASM_DISKSTRINGs, to avoid multiple mount-attempts of the same DG on all ASM instances.
So I could create different DGs in different ASM-instances.
But now the troubles starts: In my test-RDBMS I could only see the DGs of the first ASM.
So I created a SR(7275580.994) at MetaLink to ask how to do it and wether or not it's supported in single instance or RAC.
To summarize the answers:

It is possible to run multiple ASMs on a node
One RDBMS can onlybe served by one ASM
all the GUIs are not aware of multiple ASMs
it's not supported in RAC

Even these answers are worth some forther testcases.
I have 2 ASMs: +ASM (default) and +ASM2 (2nd, with changed DB_UNIQUE_NAME and ASM_DISKSTRING) and one RDBMS. DG ASM1_DG created in +ASM, DG ASM2_DG created in +ASM2.

Test 1
+ASM up
+ASM2 up
=> only ASM1_DG1 visible in RDBMS.
Test 2
+ASM down
+ASM2 up
=> ASM2_DG1 visible
! create tablespace in ASM2_DG1
Test 3
+ASM up
+ASM2 up
- restart RDBMS
=> only ASM2_DG1 visible in RDBMS
Test 4
+ASM up
+ASM2 up
=> DBCA only shows ASM1_DG1:

Test 5

shutdown +ASM
switch ASM1_DG to +ASM2 (some fiddling with ASM_DISKSTRING and symlinks)
mount ASM1_DG in +ASM2
create TS in ASM1_DG
shutdown RDBMS and +ASM2
switch ASM1_DG back to +ASM
startup +ASM, +ASM2 and rdbms

this constelation (ASM1_DG mounted on +ASM, ASM2_DG mounted on +ASM2) led to this error-message:

ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-01110: data file 8: '+ASM1_DG/berx2/datafile/asm1_dg.256.676065339'
ORA-17503: ksfdopn:2 Failed to open file +ASM1_DG/berx2/datafile/asm1_dg.256.67
6065339
ORA-15001: diskgroup "ASM1_DG" does not exist or is not mounted
ORA-15001: diskgroup "ASM1_DG" does not exist or is not mounted

Test5 led me to one guess: At startup a RDBMS can potentially access all ASMs, but the first attempt to access a DG pins the RDBMS to the ASM. Unfortunately I do not know how to prove this.
Within the SR Bug:6991236 was mentioned.
This Bug is not visible right now, but interpreting the header I can guess it's an enhancement request to allow or support multiple ASM-instances with differen OS-users in a cluster-environment. It's shown to be fixed (implemented) in 11gR2 and has status 98 (Suggestion Implemented). We will see when 11gR2 is out.

avoid ORA-1555 in long running queries

2009-01-09T21:57:00.002+01:00

11.1.0.7 brought an interresting new Feature: Allow ORA-1555 to be ignored during table scan.
From the description for Bug 6688108:

ORA-1555 can occur during long running queries involving a ROWID Range or full table scan if the undo has recycled.
This error is normal and expected.

This fix is a special enhancement to allow an SCN_ASCENDING hint to be supplied in the SQL statement. With this hint the select will take a new snapshot SCN and continue the scan rather than reporting an ORA-1555 to the caller. This can lead to inconsistent results as the SELECT is allowed to see data which is not all consistent to one point in time.

It is somewhat amazing Oracle allows inconsistent results, or it's the 'other end' of READ UNCOMMITTED isolation Level.

I'm not sure where this feature makes any sense, but there will be environments where it's better to get false data than no data.

buffer cache poisioning

2009-01-08T14:52:00.003+01:00

Out of some tests and discussions I wanted to create a way to pollute the buffercache with blocks not existing there. As a result the BCHR also goes down, so I might produce a contradiction to Connor McDonalds choose_a_hit_ratio. There might be easier ways doing so, feel free to promote, if you want.

How i test the BCHR:


column name format A32;
SELECT id, name, block_size,
 (1-(PHYSICAL_READS/(DB_BLOCK_GETS+CONSISTENT_GETS))) HIT
FROM V$BUFFER_POOL_STATISTICS
ORDER BY NAME, BLOCK_SIZE;

On my test system the script is really slow and I'm not sure about side effects it will have on other systems than the buffer cache.

and my results (before the first run):


ID NAME       BLOCK_SIZE        HIT
-- ---------- ---------- ----------
3 DEFAULT          8192 .920069518
1 KEEP             8192 .158558559

The scripts functionality explained:

a Cursor c_tab provides all Tables of some quality (e.g. not owned by SYS)
for every entry in this cursor, all rowids are collected into a TABLE OF ROWID
every rowid is checked if it's block exists in x$bh, if not, it's directly selected and therefore bushed into bufer cache.

and here the script itselve (a big part just copied with permission from Reinhard Krämer)


DECLARE
counter  number;
obj_id   number;
rel_fno  number;
block_id number;
bh_count number;
bh_stmt  varchar2(200);
rid_stmt varchar2(200);
v_stt    varchar2(50);
v_rid    ROWID;
CURSOR c_tab
IS
  SELECT owner,
         table_name,
            owner
         || '.'
         || table_name owntab
  FROM DBA_tables
  WHERE owner NOT IN ('SYS')
         -- comment next line, if you want to have all tables
         --AND table_name = 'T_KEEP'
         AND BUFFER_POOL NOT IN ('KEEP')
  --       AND owner = UPPER ('TEST')
  ;

g_string VARCHAR2 (4000) := NULL;
-- memory-array
TYPE t_rowid IS TABLE OF ROWID
  INDEX BY PLS_INTEGER;

r_rowid t_rowid;

-- print-procedure (mini-version of MSG)
PROCEDURE PRINT (
  p_text IN VARCHAR2 := NULL
)
IS
BEGIN
-- remove comment for debug
--      DBMS_OUTPUT.put_line (p_text);
 NULL;
END;
BEGIN
-- main
EXECUTE IMMEDIATE 'select b.ksppstvl InstValue
                 from x$ksppi a, x$ksppcv b
                 where a.indx = b.indx
                   and a.ksppinm=''_small_table_threshold''' into v_stt;
EXECUTE IMMEDIATE 'alter session set "_small_table_threshold" = 10';
counter := 0;
bh_stmt := 'select count(*)
          from x$bh
          where obj=:obj_id and dbablk=:block_id and dbarfil=:rel_fno';

FOR crec IN c_tab
LOOP
 PRINT(' tab: ' ||crec.owntab);
  g_string :=    'select rowid the_rowid from '
              || crec.owntab;

  BEGIN
     PRINT (   'using command: '
            || g_string);

     EXECUTE IMMEDIATE (g_string)
     BULK COLLECT INTO r_rowid;
  EXCEPTION
     WHEN OTHERS
     THEN
        PRINT (   SQLERRM
               || ' on '
               || g_string);
  END;

  IF r_rowid.COUNT > 0
  THEN
     PRINT (   'showing rowids for: '
            || crec.owntab);

     FOR i IN r_rowid.FIRST .. r_rowid.LAST
     LOOP
        PRINT (r_rowid (i));
        -- load block if not in X$bh
   
        obj_id   := dbms_rowid.rowid_object(r_rowid(i));
        rel_fno  := dbms_rowid.rowid_relative_fno(r_rowid(i));
        block_id := dbms_rowid.rowid_block_number(r_rowid(i));
        rid_stmt := 'SELECT rowid the_rowid FROM '
              || crec.owntab || ' WHERE rowid = :rid';
        execute immediate bh_stmt into bh_count using obj_id, block_id, rel_fno;
   
        IF bh_count = 0 THEN -- block curently not in buffer
          BEGIN
            execute immediate rid_stmt into v_rid using r_rowid(i);
            counter := counter + 1;
          END;
        END IF;
     END LOOP;
  END IF;
END LOOP;
EXECUTE IMMEDIATE 'alter session set "_small_table_threshold" = :stt' using v_stt;
DBMS_OUTPUT.put_line ('counter: ' || counter);
END;
/

After a 2nd check the BCHR is really lower than before.


ID NAME       BLOCK_SIZE        HIT
-- ---------- ---------- ----------
3 DEFAULT          8192 .856567413
1 KEEP             8192 .158558559

One question might be why I didn't run just a lot of full table scans? I can not be sure how many blocks of this tables exist in buffer cache, and every existing buffer would increse the BCHR. Also in Oracle 11g this might result in direct path reads which will not affect the buffer cache directly (in fact, it will increase the BCHR, as all internal selects in SEG$, TS$ and all the other data dictionary tables needed to access blocks).
One last purpose: If ever anyone argues about the need of ratios to judge the health of a database, just ask if you get additional ressources if the BCHR is above/below any limit - and then grab these resources :-)

Instance parameters derived from cpu_count

2009-01-07T21:20:00.002+01:00

Today I have a lot of pictures!
We got a brand new SUN T5140, and I managed to get my hands on it to run some tests.

Glenn Fawcett wrote some interresting entries in his blog about these so called chip multithreading CPUs. 2 should be mentioned here, as they had influence to this test-case:
Oracle's vision of multi-core processors on Solaris and Virtual CPUs effect on Oracle SGA allocations.
I wanted to know which parameters are derived from cpu_count.
A fresh installed and patched RDBMS 11.1.0.7.0 was my victim.
I used this script to get my data:

#!/usr/bin/ksh  -x

integer i=1
while ((i <= 128));
do
  print " $i ";

  echo "*.compatible='11.1.0.0.0'
*.control_files='/appl/oracle/oradata/BERX2/control01.ctl','/appl/oracle/oradata/BERX2/control02.ctl','/appl/oracle/oradata/BERX2/control03
.ctl'
*.db_block_size=8192
*.db_name='BERX2'
*.MEMORY_MAX_TARGET=16G
*.SGA_TARGET=13G
*.cpu_count=$i"> initBERX2.ora

  echo "startup pfile='/appl/oracle/product/11.1.0.7/dbs/initBERX2.ora' ;

insert into init_parameters
select $i         ,
       a.ksppinm  ,
       b.ksppstvl ,
       c.ksppstvl
from x\$ksppi a, x\$ksppcv b, x\$ksppsv c
where a.indx = b.indx and a.indx = c.indx;

commit;

select distinct(\"CPUs\") from init_parameters;

shutdown;

exit;"> cpus/doit$i.sql

sqlplus "/ as sysdba" @cpus/doit$i.sql >cpus/log$i.log

sleep 5

  (( i = i + 1));
done

Afterwards I just had to get the interresting data out:

select /*+ PARALLEL */ i."CPUs" || ',' || i."Parameter" || ',' || i."InstanceValue"  
from init_parameters i  
where "Parameter" in (select distinct(i1."Parameter")
    from init_parameters i1,
       init_parameters i2
    where i1."Parameter" = i2."Parameter"
       and i1."CPUs" <> i2."CPUs"
      and i1."InstanceValue" <> i2."InstanceValue" )  
order by i."CPUs", i."Parameter";

and waste my time in excel creating the graphs:
(the whole spool output can be found here.)

I hope everyone enjoys these graphs. I'm sure there can be done much more tests, so if you have an interresting testcase whic hcan be automated such as mine, feel free to contact me.

KEEP and DEFAULT buffer really seperated (at least for LRU lists)

2009-01-05T12:52:00.003+01:00

I had to answer a really simple question:

If I manage to put a table totally into KEEP buffer (and it's the only table which will ever go there), will it be affected by other operations which are going into DEFAULT buffer cache?

At least if you disable Automated Memory Management the answer is yes (even if some points has to be considered, but that's not part of the primary question).
Here my little testcase:
I have a table t_keep which should go and stay in keep buffer cache (and fits into it).
I have 2 other tables, t and t2, where t is mich bigger than the default pool, t2 also fits into default-pool but together they kick each other out (at least partially).
And now for the tests:
(I had to make _small_table_threshold big enough to load all tables in buffer cache and avoiding direct path reads)

about the tables:

select table_name, buffer_pool, blocks from dba_tables where owner ='TEST';TABLE_NAME                     BUFFER_     BLOCKS
------------------------------ ------- ----------
T2                             DEFAULT        401
T_KEEP                         KEEP           496
T                              DEFAULT      26289

select OBJECT_NAME, DATA_OBJECT_ID 
from dba_objects
 where owner ='TEST';
OBJ_NAME DATA_OBJECT_ID
-------- --------------
T2                64351
T_KEEP            64143
T                 64142

the test itselve:

SELECT ROWID FROM TEST.T_KEEP;

... ROWIDs ...

select obj, count(*) from x$bh where obj in (64142, 64143,64351) group by obj;

OBJ   COUNT(*)
---------- ----------
64143        452

SELECT ROWID FROM TEST.T;

... ROWIDs ...

select obj, count(*) from x$bh where obj in (64142, 64143,64351) group by obj;

OBJ   COUNT(*)
---------- ----------
64143        452
64142       1080

select obj, count(*) from x$bh where obj in (64142, 64143,64351) group by obj; 

OBJ   COUNT(*)
---------- ----------
64351        386
64143        452
64142          1

So ObjectID:64143 == T_KEEP is still in KEEP buffer cache.

Now the same test with Automated Memory Management:
(only the results)

select obj, count(*) from x$bh where obj in (64142, 64143,64351) group by obj;
       OBJ   COUNT(*)
---------- ----------
     64143        452

select obj, count(*) from x$bh where obj in (64142, 64143,64351) group by obj;
       OBJ   COUNT(*)
---------- ----------
     64143        452
     64142      20111

select obj, count(*) from x$bh where obj in (64142, 64143,64351) group by obj;
       OBJ   COUNT(*)
---------- ----------
     64351        386
     64143        452
     64142      19725

So also in this case 64143 survived, whereas the other 2 were fighting for the same blocks in DEFAULT buffer cache.

Windows NTP client - allow large time offsets

2008-12-31T09:00:00.002+01:00

During the christmas holidays, I 'm doing my own christmas ralley to meet a rather huge family. At these visits I'm often asked to solve some smal or great PC-Problems. Even I'm no PC-pro, I'm something like the one-eyed among blindes. Ad of course it makes more sense to solve problems onsite than remote using just the telephone.
One of the problems was an old laptop with an decrepit BIOS-battery. This led to an totally insane system date at every startup. Even with a proper system time and NTP-Server setup it never set the time correctly as the time offset was to large.
After some searching within the registry I changed under HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\W32Time\Config the parameters MaxNegPhaseCorrection and MaxPosPhaseCorrection toFFFFFFFF, which is the maximum value (approx 136 years). Now at every connection, or at least at every manual time adjustment, the time is really set (and no boring error message is shown).

no row information in data dictionary

2008-12-28T19:05:00.000+01:00

It all began with a simple question how to get all ROWIDs from a table (without selecting it)? I posted on oracle-l. After some private forks of conversations I got an email by Jared Still where he mentioned:
"If you constructed a rowid, and did a select on the row, it probably doesn't
matter if the row exists or not.

Oracle has to read the block from disk to read the row directory.
The block would get cached whether or not the row exists.

I haven't tested this, but I imagine you will. :)"
Of course, he was right:
My testcase was very simple:

preparation

 create table jared as select * from dual;

testcase

ALTER SESSION SET events '10046 trace name context forever, level 12';

Session altered.

select * from jared where rowid='AAAPa5AABAAATHhAAB';

no rows selected

/

no rows selected

select * from jared where rowid='AAAPa5AABAAATHhAAA';

D
-
X

which created a tracefile.

summary
the first statement hase one related wait: 'db file sequential read' the second and the third have no such waits!
I crosschecked the situation by asking the buffer:
```
select tch from x$bh where obj=63161 and dbablk=78305 and dbarfil=1;
```
and - as expected - it just counted up from no rows selected (before the first run) counting 1, 2, 3.
conclusio
Disclaimer: Even I hope it's well coated, I never can be 100% sure. A modern RDBMS has so many ways to do it's job I would have to ask the developers to prove. (which I can't).
- the buffer_cache caches blocks, not rows. (obvious?)
- even if a row does not exist, it's including block is cached, if it's available.
- as the smallest granularity the RDBMS takes care at IO is a block, and it even stores blocks if the row asked for does not exist.
this brings me to my own answer to my initial question:
There is no ROWID information for a given table stored in data dictionary.

Frohe Weihnachten!

2008-12-24T18:00:00.001+01:00

Ich wünsche allen frohe Weihnachten!

Möge eurer Leben so friedvoll und zufrieden verlaufen, dass ihr jeden Tag das Gefühl habt, es sei Weihnachten.

(translation available if required )

Doing it better ... (Part 2)

2008-12-18T09:01:00.003+01:00

Based on my Doing it better ... (Part 1), several days after the first approach and a lot of testcases (for other parts of the application) the performance of our so well tuned statement decreased dramatically.
What happened? The index on RESOURCE_POOL.FREED_TIME became soomething unbalanced. Not the kind you might argue 'B* trees can not be unbalanced per definition', but more of the kind 'based on the code there often remain some blocks nearly unused, which makes the selectivity of the index apear less and less usable. In fact, the statment is not really nice to the CBO, as an AND ROWNUM=1 is hard to estimate.

This brings me to one of the highlights in this project: Based on good arguments and a test-case we could argue to make a slightly change in the code and the logic. To understand the change some words must be said to the logic: the part v_time>FREED_TIME was used to introduce a kind of 'cold down period' where a resource was not used but neverthleless NOT reused again. This period was ever planned to be several minutes (about 5 was the asumption for the project, but it could also be 2 or 15, but it was agreed NEVER to be less than 1 or more than 20 minutes).

This led us to these changes:
we could change the table to make FREED_TIME of type NUMBER and changed the statement to:


UPDATE RESOURCE_POOL SET
           STATUS='INUSE',
        FREED_TIME=NULL,
        SESSION_START_TIME=:B7
WHERE
     FREED_TIME NOT IN
       (select (to_char(sysdate, 'MI')-(rownum - 1)) - 60 * FLOOR((to_char(sysdate, 'MI')-(rownum - 1))/60) safe_minutes
        from dual
        connect by level <= :MINUTES_GRACE)
 AND STATUS='FREE'
 AND ADDRESS_POOL=:B1
 AND ROWNUM=1 
RETURNING IP_ADDR INTO :O0

and the statement which 'frees' the resource:


UPDATE RESOURCE_POOL SET
...
STATUS='FREE' FREED_TIME=to_char(sysdate, 'MI')
...

This leads to a logic, where every time the status is freed the current minute is stored in FREED_TIME. There are only 60 different values which come again and again and again. (so The selectivity is quite stable.
The select ... from dual ... connect by ... is a simple way to have a eaily changable list of 'the last x minutes' which is aware the break every hour; the more or less strange formula is a kind of MOD().

Doing it better ... (Part 1)

2008-12-17T21:48:00.000+01:00

I never thought something like this could be worth a blog entry, but as Tom Kyte can do this, I can also! ;-)

I had the pleasure to assist in a little project as a DBA.
The pleasure was mine, as the target (in response-time) was outstanding (in my current working circumstances) and the setup was done to achieve this in a more or less professional manner (which much more outstanding ...), so we had testcases, a kind of change-management, really load-tests, totally cooperative designers/developers. Great!

The goal of the whole thing was to 'lock' and 'free' available resources out of a pool (according to some additional 'quality'). The example I'm writing is about the process 'provide the next free ressource out of the given pool'. It should come back in about 10ms.

Here the Iterations we came through:

1) primary code:


SELECT resource into v_resource
FROM RESOURCE_POOL
WHERE v_time>FREED_TIME
AND STATUS='FREE'
AND POOL=v_pool
AND ROWNUM=1
FOR UPDATE;
UPDATE RESOURCE_POOL set
     STATUS='INUSE',
     FREED_TIME=null,
     START_TIME=current_timestamp,
WHERE RESOURCE=v_resource;

with this comment beside these statements:

-- FOR UPDATE statement ensures that the selected row is locked until the transition is completed --
--    open 2 different sqlplus commands, issue the command and watch the last command. it waits until update is completed (or commit) on the first session
-- FOR UPDATE NOWAIT would report an error if the row cant be locked immediately --
-- FOR UPDATE SKIP LOCKED pauses other db queries - this could be the perf bottleneck ...

BRRR! Even with an index in RESOURCE_POOL.RESOURCE it's not really optimal. And, the hard fact is, it's to slow.

2) after some iterations we came to:

UPDATE RESOURCE_POOL set
            STATUS='INUSE',
            FREED_TIME=null,
            SESSION_START_TIME=current_timestamp,
WHERE v_time>FREED_TIME
           AND STATUS='FREE'
           AND RESOURCE_POOL=v_pool
           AND ROWNUM=1
           AND v_time>FREED_TIME
RETURNING RESOURCE  INTO v_resource;

As we are counting miliseconds, this WAS an advantage. And for me personally, it's more beautiful, even this is not really a tuning-goal.

Beside some tests on the physical layout (we came to the conclusion to use partitions by resource pools and local indexes on them) this saved us enough time so we could stop tuning.
Maybe I also have to tell all the data is small enough to fit into the buffer_pool.
And for the Records, it's 10.2.0.4 EE on HP-UX (Itanium), Failover ServiceGuard.

Detuning soft parses

2008-12-16T22:19:00.003+01:00

During our seminar Oracle 10g Performance analysis with Doug Burns the question arises how to force the optimizer to do a hard-parse all the teme, even when prepared statements and bind variables are used. The question was about a 10.2.0.4 DWH env.

For 11g and some backports I found DBMS_SHARED_POOL.PURGE which just purges the cursor (of course, you have to know it first; but that's no problem for one who complain about bind peeking in 10g).
And just for the records there are some Notes
Note:457309.1 - How To Flush an Object out the Library Cache [SGA]
Note:751876.1 - DBMS_SHARED_POOL.PURGE Is Not Working On 10.2.0.4
and other blogs also: Flushing a single cursor

But my initial idea was tha classic art or de-tuning:
Sometimes we complain about situatiions, where the statement is literally the same, but the optimizer creates several childs by hard-parsing. So let's use this bahaviour this time: Just change one of the many related optimizer environments.
1og Optimizer Environment Views will be a good start, but I assume (not tested!) even a NLS_SORT might help for the start.
Just grab one environment parameter which provides a wide range of values with little to no affect. So first bitmap_merge_area_size came into my mind, but I'm not sure if it will have no side effects, at least in a DWH env.

At the end, the common disclaimer: I have not tested any of the methods described here, so be warned ;-)

Oracle 10g Performance Analysis with Doug Burns in Vienna

2008-12-16T20:45:00.001+01:00

Doug Burns hold a 2 day class in Vienna. The topic was "Oracle 10g Performance Analysis with Doug Burns".
I had the pleasure to attend this class and saw (and of course listen to) Doug.
The course had 3 major parts (from my point of view):

General introduction to Tuning like:
- It's all about time (not costs, not ratios, not beauty, ...)
- the customer is the only who can tell what's good (and must do this, before tuning begins)
- beware Compulsive tuning disorder
the history of the tools:
- bstat/estat
- wait interface
- statspack
current tools (10g/11g) and new features (11g) - (or why it's all the same, just better)
- ASH
- AWR
- ADDM
- improvements in 11g

That's all I remember (high level) - so if I miss anything substantial, sorry Doug, I must have drifted off (which I have to account my little daughters growing teeth much more than Dough) .

It was the first time to see Dough and it was great. It was full of good examples (many of them known if you follow his blog or others, but that's good!), but he never lost the red line through all his slides.
As he started with some history, he picked me up in a well known and familiar area and guided me to the brave new world, their strenghts and pitfalls.

During all the coffee breaks (and there where enough, even for the smokers) quite refreshing debates arose. Very convenient.

At the end, Doug just finished before time, but I had no feeling he missed something during the past 2 days. So I was happy to use the time for some more general, not so technical chat.

At all, these 2 days where the perfect recreation from ordinary labour work as a DBA for a geek like me!

dynamic SQL with DMLs

2008-11-26T10:07:00.003+01:00

Once again, Christian Antognini is the reason for this entry (beside my curiosity of course).
In Chapter 8 - Parsing of his book Troubleshooting Oracle Performance he showed the advantage of explicit cursor handling. The package dbms_sql shows the most flexibility. Unfortunately he only showed SELECT statements, no DML (which I'm interrested for some reasons). So I had to do the tests myselve:

CREATE TABLE dyn_sql (id number, val varchar2(32));

DECLARE   v_cur_hdl         INTEGER;
  v_stmt_str        VARCHAR2(200);
  v_rows_processed  BINARY_INTEGER;
  gid               INTEGER;
  gval              VARCHAR2(32);
BEGIN   v_stmt_str := 'INSERT INTO dyn_sql VALUES (:gid, :gval)';
  v_cur_hdl := DBMS_SQL.OPEN_CURSOR(2); -- to avoid ORA-29471 in 11g  
  DBMS_SQL.PARSE(v_cur_hdl, v_stmt_str, DBMS_SQL.NATIVE);
  DBMS_SQL.BIND_VARIABLE (v_cur_hdl, ':gid',  1);
  DBMS_SQL.BIND_VARIABLE (v_cur_hdl, ':gval', 'V01C01');
  
  v_rows_processed := DBMS_SQL.EXECUTE(v_cur_hdl);
  
  DBMS_SQL.BIND_VARIABLE (v_cur_hdl, ':gid',  2);
  DBMS_SQL.BIND_VARIABLE (v_cur_hdl, ':gval', 'V02C01');
  v_rows_processed := DBMS_SQL.EXECUTE(v_cur_hdl);
  COMMIT;

  DBMS_SQL.BIND_VARIABLE (v_cur_hdl, ':gid',  3);
  DBMS_SQL.BIND_VARIABLE (v_cur_hdl, ':gval', 'V03C02');

  v_rows_processed := DBMS_SQL.EXECUTE(v_cur_hdl);
  DBMS_SQL.BIND_VARIABLE (v_cur_hdl, ':gid',  4);
  DBMS_SQL.BIND_VARIABLE (v_cur_hdl, ':gval', 'V04C02');
  
  v_rows_processed := DBMS_SQL.EXECUTE(v_cur_hdl);
  ROLLBACK;
  
  DBMS_SQL.BIND_VARIABLE (v_cur_hdl, ':gid',  5);
  DBMS_SQL.BIND_VARIABLE (v_cur_hdl, ':gval', 'V05C03');
  
  v_rows_processed := DBMS_SQL.EXECUTE(v_cur_hdl);
  
  DBMS_SQL.CLOSE_CURSOR(v_cur_hdl);
END;
 /

SQL> select * from dyn_sql;
        ID VAL
---------- ------
         1 V01C01
         2 V02C01
         5 V05C03

blubb
what are my findings?

DBMS_SQL works fine also for DMLs.
commit/rollback within an open cursor works fine.
~~closing a cursor implicite commits (direct sql-trace sequence: EXEC(insert) - CLOSE(insert) - EXEC(pl/sql) - CLOSE (pl/sql)~~Deleted after Chris commentar

There is an explicite warning in the documentation: Native Dynamic SQL is faster than DBMS_SQL. I did not test this. But be warned. (and test it yourselve, If you fear performance!)

minimal init.ora

2008-11-25T20:05:00.004+01:00

my minimal init.ora has only 4 lines:

*.compatible='11.1.0.0.0'
*.control_files='/appl/oracle/oradata/BERX2/control01.ctl','/appl/oracle/oradata/BERX2/control02.ctl','/appl/oracle/oradata/BERX2/control03.ctl'
*.db_block_size=8192
*.db_name='BERX2'

where I have to check if I can omit the compatible. So it would be down to 3 lines. nice!
Without setting compatible, I get

ORA-00201: control file version 11.1.0.0.0 incompatible with ORACLE version
11.0.0.0.0
ORA-00202: control file: '/appl/oracle/oradata/BERX2/control01.ctl'

and a show parameter compatible gives

compatible                           string      11.0.0

strange, as the instance identifies itselve as

SQL> select version from v$instance;

VERSION
-----------------
11.1.0.7.0

So it seems the automatic value for compatible is derived only from the first Version number of v$instance.

sysstat_sample.sql

2008-11-25T08:45:00.002+01:00

Based on Tanel Poders sample.sql and ses.sql I was interrested to get some informations about the System at all.
There are various discussions wether or not it is useful to have a look on the system when hunting a problem within a session. Nevertheless I decided I need such a tool. On the one hand to generate a 'good' baseline, on the other to have a quick, zentralised view. It's also of some use to compare the system-statistics to the values of one dedicated sessions to see if this session is the major reason for any changes or submit only a small amount, but might suffer from others in the system.

So here is the code:


select name,
  min(value)               "MIN",
  max(value)               "MAX",
  max(value) - min(value)  "overall_delta",
  max(delta)               "max_single_delta",
  sum(decode(delta,0,0,1)) "#changes"   -- count distinct 
from
  (with c as
     (select &1 counter from dual)
  select     rn,
 name,
 value,
     decode(rn,1,0,value-prev) delta  
from    ( select /*+ ordered use_nl(t) */
         r.rn,
 NAME,
 Value,
         LAG(VALUE) OVER (Partition BY NAME order by r.rn) prev
      from
        (select /*+ no_unnest */
 rownum rn,
 1 sample_dummy
          from dual
            connect by level <= (select counter from c)        ) r,        v$sysstat t      order by name, r.rn     )  ) syss
where   value     > 0
  and delta > 0
group by name
order by name
/

This piece of code samples through v$sysstat and generates for every entry (where the value > 0, to reduce the lines) a line with the minimal value, the maximal value, the delta between them (this could also be done by DBMS_LOCK.SLEEP) but also the greatest single step between 2 sequent samples.
There might also be other statistical functions of some interrest, but I have too little knowledge on statistics and how to use them (comments/suggestions welcome!).

The script can also be found here, where I will keep the most recent version all the time.

Composite Bitmap Indexes

2008-11-07T12:08:00.002+01:00

In hist Book Troubleshoot Oracle Performance Christian Antognini wrote also about Composite Bitmap Indexes. (p400)
Unfortunately he only proclaims

Composite bitmap indexes are rarely created. This is because several indexes can be combined efficiently in order to apply a restriction. To see how powerful bitmap indexes are, let’s look at several queries. (p400)

without givin gany evidence. So I grabbed his scripts (thank you for providing them) and run some testcases (on 11.1.0.6):
I just created this additional bitmap index:

CREATE BITMAP INDEX bx_i_n456 on t (n4, n5, n6);

bitmap AND:

index_combine:

SELECT /*+ index_combine(t i_n4 i_n5 i_n6) */ *
FROM t
WHERE n4 = 6 AND n5 = 42 AND n6 = 11

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID | T    |      1 |      1 |      1 |00:00:00.01 |       7 |
|   2 |   BITMAP CONVERSION TO ROWIDS|      |      1 |        |      1 |00:00:00.01 |       6 |
|   3 |    BITMAP AND                |      |      1 |        |      1 |00:00:00.01 |       6 |
|*  4 |     BITMAP INDEX SINGLE VALUE| I_N5 |      1 |        |      1 |00:00:00.01 |       2 |
|*  5 |     BITMAP INDEX SINGLE VALUE| I_N6 |      1 |        |      1 |00:00:00.01 |       2 |
|*  6 |     BITMAP INDEX SINGLE VALUE| I_N4 |      1 |        |      1 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------

   4 - access("N5"=42)
   5 - access("N6"=11)
   6 - access("N4"=6)

Cost: 3

composite bitmap index(CIB):

SELECT *
FROM t
WHERE n4 = 6 AND n5 = 42 AND n6 = 11

----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID | T         |      1 |      1 |      1 |00:00:00.01 |       4 |
|   2 |   BITMAP CONVERSION TO ROWIDS|           |      1 |        |      1 |00:00:00.01 |       3 |
|*  3 |    BITMAP INDEX SINGLE VALUE | BX_I_N456 |      1 |        |      1 |00:00:00.01 |       3 |
----------------------------------------------------------------------------------------------------

   3 - access("N4"=6 AND "N5"=42 AND "N6"=11)

Cost:1

In this case the CBI wins.

bitmap OR:

index_combine:

SELECT /*+ index_combine(t i_n4 i_n5 i_n6) */ *
FROM t
WHERE n4 = 6 OR n5 = 42 OR n6 = 11

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID | T    |      1 |    797 |    767 |00:00:00.01 |     419 |
|   2 |   BITMAP CONVERSION TO ROWIDS|      |      1 |        |    767 |00:00:00.01 |       7 |
|   3 |    BITMAP OR                 |      |      1 |        |      1 |00:00:00.01 |       7 |
|*  4 |     BITMAP INDEX SINGLE VALUE| I_N4 |      1 |        |      1 |00:00:00.01 |       3 |
|*  5 |     BITMAP INDEX SINGLE VALUE| I_N6 |      1 |        |      1 |00:00:00.01 |       2 |
|*  6 |     BITMAP INDEX SINGLE VALUE| I_N5 |      1 |        |      1 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------

   4 - access("N4"=6)
   5 - access("N6"=11)
   6 - access("N5"=42)

Cost: 135

no hints:

SELECT *
FROM t
WHERE n4 = 6 OR n5 = 42 OR n6 = 11

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID | T    |      1 |    797 |    767 |00:00:00.01 |     419 |
|   2 |   BITMAP CONVERSION TO ROWIDS|      |      1 |        |    767 |00:00:00.01 |       7 |
|   3 |    BITMAP OR                 |      |      1 |        |      1 |00:00:00.01 |       7 |
|*  4 |     BITMAP INDEX SINGLE VALUE| I_N4 |      1 |        |      1 |00:00:00.01 |       3 |
|*  5 |     BITMAP INDEX SINGLE VALUE| I_N6 |      1 |        |      1 |00:00:00.01 |       2 |
|*  6 |     BITMAP INDEX SINGLE VALUE| I_N5 |      1 |        |      1 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------

   4 - access("N4"=6)
   5 - access("N6"=11)
   6 - access("N5"=42)

Cost:135

index_combine with BX_I_N456:

SELECT /*+ index_combine(t BX_I_N456 i_n5 i_n6) */ *
FROM t
WHERE n4 = 6 OR n5 = 42 OR n6 = 11   

----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID | T         |      1 |    797 |    767 |00:00:00.04 |     420 |
|   2 |   BITMAP CONVERSION TO ROWIDS|           |      1 |        |    767 |00:00:00.03 |       8 |
|   3 |    BITMAP OR                 |           |      1 |        |      1 |00:00:00.03 |       8 |
|   4 |     BITMAP MERGE             |           |      1 |        |      1 |00:00:00.03 |       4 |
|*  5 |      BITMAP INDEX RANGE SCAN | BX_I_N456 |      1 |        |    527 |00:00:00.01 |       4 |
|*  6 |     BITMAP INDEX SINGLE VALUE| I_N6      |      1 |        |      1 |00:00:00.01 |       2 |
|*  7 |     BITMAP INDEX SINGLE VALUE| I_N5      |      1 |        |      1 |00:00:00.01 |       2 |
----------------------------------------------------------------------------------------------------

   5 - access("N4"=6)
       filter("N4"=6)
   6 - access("N6"=11)
   7 - access("N5"=42)

Cost: 138

index_combine with BX_I_N456 on 2nd place:

SELECT /*+ index_combine(t i_n4 BX_I_N456 i_n6) */ *
FROM t
WHERE n4 = 6 OR n5 = 42 OR n6 = 11   

----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID | T         |      1 |    797 |    767 |00:00:00.01 |     420 |
|   2 |   BITMAP CONVERSION TO ROWIDS|           |      1 |        |    767 |00:00:00.01 |       8 |
|   3 |    BITMAP OR                 |           |      1 |        |      1 |00:00:00.01 |       8 |
|   4 |     BITMAP MERGE             |           |      1 |        |      1 |00:00:00.01 |       4 |
|*  5 |      BITMAP INDEX RANGE SCAN | BX_I_N456 |      1 |        |    527 |00:00:00.01 |       4 |
|*  6 |     BITMAP INDEX SINGLE VALUE| I_N6      |      1 |        |      1 |00:00:00.01 |       2 |
|*  7 |     BITMAP INDEX SINGLE VALUE| I_N5      |      1 |        |      1 |00:00:00.01 |       2 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("N4"=6)
       filter("N4"=6)
   6 - access("N6"=11)
   7 - access("N5"=42)

Costs: 138

in this case, the 3 seperated bitmap indices wins.

NOT EQUAL AND:

index_combine:

SELECT /*+ index_combine(t i_n4 i_n5 i_n6) */ *
FROM t
WHERE n4 != 6 AND n5 = 42 AND n6 = 11   

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS BY INDEX ROWID | T    |      1 |      1 |      1 |00:00:00.01 |       6 |
|   2 |   BITMAP CONVERSION TO ROWIDS|      |      1 |        |      2 |00:00:00.01 |       4 |
|   3 |    BITMAP AND                |      |      1 |        |      1 |00:00:00.01 |       4 |
|*  4 |     BITMAP INDEX SINGLE VALUE| I_N5 |      1 |        |      1 |00:00:00.01 |       2 |
|*  5 |     BITMAP INDEX SINGLE VALUE| I_N6 |      1 |        |      1 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------

   1 - filter("N4"<>6)
   4 - access("N5"=42)
   5 - access("N6"=11)

Costs: 2

a different execution plan from Chris' Book, there a BITMAP MINUS was shown in the execution plan.
Maybe a question for a seperated session, where the BITMAP MINUS disappeared.

for some reason, this hint generated the BITMAP MINUS

SELECT /*+ index(t  BX_I_N456) */ *
FROM t
WHERE n4 != 6 AND n5 = 42 AND n6 = 11   

------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID   | T         |      1 |      1 |      1 |00:00:00.01 |      13 |
|   2 |   BITMAP CONVERSION TO ROWIDS  |           |      1 |        |      1 |00:00:00.01 |      12 |
|   3 |    BITMAP MINUS                |           |      1 |        |      1 |00:00:00.01 |      12 |
|   4 |     BITMAP MINUS               |           |      1 |        |      1 |00:00:00.01 |       8 |
|   5 |      BITMAP AND                |           |      1 |        |      1 |00:00:00.01 |       4 |
|*  6 |       BITMAP INDEX SINGLE VALUE| I_N5      |      1 |        |      1 |00:00:00.01 |       2 |
|*  7 |       BITMAP INDEX SINGLE VALUE| I_N6      |      1 |        |      1 |00:00:00.01 |       2 |
|   8 |      BITMAP MERGE              |           |      1 |        |      1 |00:00:00.01 |       4 |
|*  9 |       BITMAP INDEX RANGE SCAN  | BX_I_N456 |      1 |        |    526 |00:00:00.01 |       4 |
|  10 |     BITMAP MERGE               |           |      1 |        |      1 |00:00:00.01 |       4 |
|* 11 |      BITMAP INDEX RANGE SCAN   | BX_I_N456 |      1 |        |    526 |00:00:00.01 |       4 |
------------------------------------------------------------------------------------------------------

   6 - access("N5"=42)
   7 - access("N6"=11)
   9 - access("N4"=6)
  11 - access("N4" IS NULL)

Cost: 4

So I decided to do a slightly different testcase:

index_combine:

SELECT /*+ index_combine(t i_n4 i_n5 i_n6) */ * 
FROM t 
WHERE n4 = 6 and n5 != 42 and n6 = 11

-------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID   | T    |      1 |      8 |      7 |00:00:00.01 |      15 |
|   2 |   BITMAP CONVERSION TO ROWIDS  |      |      1 |        |      7 |00:00:00.01 |       8 |
|   3 |    BITMAP MINUS                |      |      1 |        |      1 |00:00:00.01 |       8 |
|   4 |     BITMAP MINUS               |      |      1 |        |      1 |00:00:00.01 |       6 |
|   5 |      BITMAP AND                |      |      1 |        |      1 |00:00:00.01 |       4 |
|*  6 |       BITMAP INDEX SINGLE VALUE| I_N6 |      1 |        |      1 |00:00:00.01 |       2 |
|*  7 |       BITMAP INDEX SINGLE VALUE| I_N4 |      1 |        |      1 |00:00:00.01 |       2 |
|*  8 |      BITMAP INDEX SINGLE VALUE | I_N5 |      1 |        |      1 |00:00:00.01 |       2 |
|*  9 |     BITMAP INDEX SINGLE VALUE  | I_N5 |      1 |        |      1 |00:00:00.01 |       2 |
-------------------------------------------------------------------------------------------------

   6 - access("N6"=11)
   7 - access("N4"=6)
   8 - access("N5"=42)
   9 - access("N5" IS NULL)

Costs: 6

CIB:

SELECT /*+ index(t BX_I_N456) */ * 
FROM t 
WHERE n4 =6 and n5 != 42 and n6 = 11

----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID | T         |      1 |      8 |      7 |00:00:00.01 |      12 |
|   2 |   BITMAP CONVERSION TO ROWIDS|           |      1 |        |      7 |00:00:00.01 |       5 |
|*  3 |    BITMAP INDEX RANGE SCAN   | BX_I_N456 |      1 |        |      7 |00:00:00.01 |       5 |
----------------------------------------------------------------------------------------------------

   3 - access("N4"=6)
       filter(("N6"=11 AND "N5"<>42 AND "N4"=6))

Costs: 107

no hints:

SELECT  * 
FROM t 
WHERE n4 =6 and n5 != 42 and n6 = 11    
    
-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS BY INDEX ROWID | T    |      1 |      8 |      7 |00:00:00.01 |      12 |
|   2 |   BITMAP CONVERSION TO ROWIDS|      |      1 |        |      8 |00:00:00.01 |       4 |
|   3 |    BITMAP AND                |      |      1 |        |      1 |00:00:00.01 |       4 |
|*  4 |     BITMAP INDEX SINGLE VALUE| I_N6 |      1 |        |      1 |00:00:00.01 |       2 |
|*  5 |     BITMAP INDEX SINGLE VALUE| I_N4 |      1 |        |      1 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------

   1 - filter("N5"<>42)
   4 - access("N6"=11)
   5 - access("N4"=6)

Costs: 4

My a little bit more explicit view of composite bitmap indexes is:

They can be useful in AND statements:
even not optimal for OR statements, they can replace the bitmap index which is created only on the first column without high cost increasement
in NOT EQUAL AND statements they really kill the performance if enfoced.

syntax highlighter

2008-10-10T08:07:00.016+02:00

I'm bored of playing around with html-tags just to get the code formatted a little bot better. So I'm switching to syntaxhighlighter from alex.gorbatchev As I'm a lazy boy, I used this documentation.

from now on, code should look lihe this:

select sysdate from dual;select sysdate+1 from dual;select sysdate+2 from dual;

Maybe I will rewrite some of my older posts to use this for better readability. - Maybe ;-)

UPDATE1: there is something going wrong, I don't know where the <br> come from. But I will check.
UPDATE2: solution: I must NOT put a newline at the beginning and end of the statements.
UPDATE3: I have to switch to draft blogger for editiong to make it work. strange thing. but the code formatting is worth the change.
UPDATE4: A solution is posted in syntaxhighlighters wiki.

result_cache invalidated within the same transaction

2008-10-08T10:31:00.015+02:00

I just stumbled across one sentence about Server Result Cache in Christian Antogninis Troubleshooting Oracle Performance: " ... To guarantee the consistency of the results ... every time that something changes on the objects reference by a query, the cache entries dependent on it are invalidated ..." (p484).

This made me a little bit curious, as other caches can deal with different versions of cached objects.

so here are some tests:

(2 connections to the same instance, no RAC, 11.1.0.6):

Default isolation level:

Session 1:

preparations:

alter session set timed_statistics = true;
   alter session set statistics_level=all;
   alter session set "_rowsource_execution_statistics" = true;
   set serveroutput on
   BEGIN
     IF dbms_result_cache.flush 
  THEN 
    dbms_output.put_line('Flush Successful');
  ELSE 
    dbms_output.put_line('Flush Failure');
  END IF;
  END;
  /
  set serveroutput off
  drop table dob;
  create table dob as select * from dba_objects;

test:

select /*+ result_cache */ max(OBJECT_ID) from dob;    
 MAX(OBJECT_ID)
 --------------
 74959
 
 SQL_ID  36tj9136q6pxx, child number 1
 -------------------------------------
 select /*+ result_cache */ max(OBJECT_ID) from dob
 Plan hash value: 648181113
 ---------------------------------------------------------------------------------------------------------------------
 | Id  | Operation           | Name                       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
 ---------------------------------------------------------------------------------------------------------------------
 |   1 |  RESULT CACHE       | bskcp8b45qj3q5ju5uwg5fuscg |      1 |        |      1 |00:00:00.12 |     976 |    209 |
 |   2 |   SORT AGGREGATE    |                            |      1 |      1 |      1 |00:00:00.12 |     976 |    209 |
 |   3 |    TABLE ACCESS FULL| DOB                        |      1 |  69871 |  65761 |00:00:00.07 |     976 |    209 |
 ---------------------------------------------------------------------------------------------------------------------
 
 select /*+ result_cache */ max(OBJECT_ID) from dob;
 MAX(OBJECT_ID)
 --------------
 74959
 
 SQL_ID 36tj9136q6pxx, child number 1
 -------------------------------------
 select /*+ result_cache */ max(OBJECT_ID) from dob
 Plan hash value: 648181113
 --------------------------------------------------------------------------------------------------
 | Id  | Operation           | Name                       | Starts | E-Rows | A-Rows |   A-Time   |
 --------------------------------------------------------------------------------------------------
 |   1 |  RESULT CACHE       | bskcp8b45qj3q5ju5uwg5fuscg |      1 |        |      1 |00:00:00.01 |
 |   2 |   SORT AGGREGATE    |                            |      0 |      1 |      0 |00:00:00.01 |
 |   3 |    TABLE ACCESS FULL| DOB                        |      0 |  69871 |      0 |00:00:00.01 |
 --------------------------------------------------------------------------------------------------

Session 2:

update dob set object_id=74999 where object_id=74959;
1 row updated.

Session 1:

select /*+ result_cache */ max(OBJECT_ID) from dob;
MAX(OBJECT_ID)
--------------
74959

SQL_ID  36tj9136q6pxx, child number 1
-------------------------------------
select /*+ result_cache */ max(OBJECT_ID) from dob
Plan hash value: 648181113
--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Starts | E-Rows | A-Rows |   A-Time   |
--------------------------------------------------------------------------------------------------
|   1 |  RESULT CACHE       | bskcp8b45qj3q5ju5uwg5fuscg |      1 |        |      1 |00:00:00.01 |
|   2 |   SORT AGGREGATE    |                            |      0 |      1 |      0 |00:00:00.01 |
|   3 |    TABLE ACCESS FULL| DOB                        |      0 |  69871 |      0 |00:00:00.01 |
--------------------------------------------------------------------------------------------------

Session 2:

commit;
Commit complete.

Session 1:

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

MAX(OBJECT_ID)
--------------
74999

SQL_ID  36tj9136q6pxx, child number 1
-------------------------------------
select /*+ result_cache */ max(OBJECT_ID) from dob

Plan hash value: 648181113

------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   1 |  RESULT CACHE       | bskcp8b45qj3q5ju5uwg5fuscg |      1 |        |      1 |00:00:00.06 |     976 |
|   2 |   SORT AGGREGATE    |                            |      1 |      1 |      1 |00:00:00.06 |     976 |
|   3 |    TABLE ACCESS FULL| DOB                        |      1 |  69871 |  65761 |00:00:00.01 |     976 |
------------------------------------------------------------------------------------------------------------

as expected - so far.

SET TRANSACTION ISOLATION LEVEL serializable;

flush result cache

Session 1:

select /*+ result_cache */ max(OBJECT_ID) from dob;

MAX(OBJECT_ID)
--------------
75003

SQL_ID  36tj9136q6pxx, child number 1
-------------------------------------
select /*+ result_cache */ max(OBJECT_ID) from dob

Plan hash value: 648181113

------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   1 |  RESULT CACHE       | bskcp8b45qj3q5ju5uwg5fuscg |      1 |        |      1 |00:00:00.06 |     976 |
|   2 |   SORT AGGREGATE    |                            |      1 |      1 |      1 |00:00:00.06 |     976 |
|   3 |    TABLE ACCESS FULL| DOB                        |      1 |  69871 |  65761 |00:00:00.01 |     976 |
------------------------------------------------------------------------------------------------------------

select /*+ result_cache */ max(OBJECT_ID) from dob;

MAX(OBJECT_ID)
--------------
75003

SQL_ID  36tj9136q6pxx, child number 1
-------------------------------------
select /*+ result_cache */ max(OBJECT_ID) from dob

Plan hash value: 648181113

--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Starts | E-Rows | A-Rows |   A-Time   |
--------------------------------------------------------------------------------------------------
|   1 |  RESULT CACHE       | bskcp8b45qj3q5ju5uwg5fuscg |      1 |        |      1 |00:00:00.01 |
|   2 |   SORT AGGREGATE    |                            |      0 |      1 |      0 |00:00:00.01 |
|   3 |    TABLE ACCESS FULL| DOB                        |      0 |  69871 |      0 |00:00:00.01 |
--------------------------------------------------------------------------------------------------

Session 2:

update dob set object_id=75004 where object_id=75003;

Session 1:

select /*+ result_cache */ max(OBJECT_ID) from dob;

MAX(OBJECT_ID)
--------------
75003

SQL_ID  36tj9136q6pxx, child number 1
-------------------------------------
select /*+ result_cache */ max(OBJECT_ID) from dob

Plan hash value: 648181113

--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Starts | E-Rows | A-Rows |   A-Time   |
--------------------------------------------------------------------------------------------------
|   1 |  RESULT CACHE       | bskcp8b45qj3q5ju5uwg5fuscg |      1 |        |      1 |00:00:00.01 |
|   2 |   SORT AGGREGATE    |                            |      0 |      1 |      0 |00:00:00.01 |
|   3 |    TABLE ACCESS FULL| DOB                        |      0 |  69871 |      0 |00:00:00.01 |
--------------------------------------------------------------------------------------------------

Session 2:

commit;Commit complete.

Session 1:

select /*+ result_cache */ max(OBJECT_ID) from dob;

MAX(OBJECT_ID)
--------------
75003
SQL_ID  36tj9136q6pxx, child number 1
-------------------------------------
select /*+ result_cache */ max(OBJECT_ID) from dob

Plan hash value: 648181113

------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   1 |  RESULT CACHE       | bskcp8b45qj3q5ju5uwg5fuscg |      1 |        |      1 |00:00:00.06 |     976 |
|   2 |   SORT AGGREGATE    |                            |      1 |      1 |      1 |00:00:00.06 |     976 |
|   3 |    TABLE ACCESS FULL| DOB                        |      1 |  69871 |  65761 |00:00:00.01 |     976 |
------------------------------------------------------------------------------------------------------------

select cache_id, CACHE_KEY, NAME, status, creation_timestamp, scn, build_time, row_count, scan_count from v$result_cache_objects;

CACHE_ID                       CACHE_KEY                      NAME                                               STATUS     CREATION_TIMEST        SCN BUILD_TIME  ROW_COUNT SCAN_COUNT

bskcp8b45qj3q5ju5uwg5fuscg     d8g29yuwtjtn9d2bcvaykm08fc     select /*+ result_cache */ max(OBJECT_ID) from dob Invalid    08-OCT-08          6102319          6          1          2

Ouch. Even the ISOLATION LEVEL serializable works fine for the Result itselve (75003 all the time), and the SCN of the result is stored somewhere, the result is global invalidated. I guess this is an area for improvement for Oracle.

Update: I got the same results with 11.1.0.7

latches and events - from 11.1.0.6 to 11.1.0.7

2008-09-22T14:29:00.006+02:00

I was just curious what parts of latches and waits oracle changed in it's first patchset of 11.1.0.7. here my results (i run a create table latch_11106 as select * from v$latch; before and after the patch, with 11107 obviously):

Counts:
select count(*) from latch_11106;


COUNT(*)
----------
   496

select count(*) from latch_11107;

  COUNT(*)
----------
    502

select count(*) from EVENT_NAME_11106;

  COUNT(*)
----------
    961

select count(*) from EVENT_NAME_11107;

  COUNT(*)
----------
    997

More Details:
Events which does not exist in 11.1.0.7 anymore:

select name from event_name_11106 minus select name from event_name_11107;

NAME
---------------------------------------------
Intelligent Storage OSS I/O completion
Streams capture: resolve low memory condition
storage device registration
virtual circuit status

new Events:
select name from event_name_11107 minus select name from event_name_11106;

NAME
----------------------------------------------
Archiver slave I/O
DBWR range invalidation sync
DBWR slave I/O
LGWR slave I/O
Logical Standby Debug
PMON to cleanup detached branches at shutdown
RMAN Disk slave I/O
RMAN Tape slave I/O
Streams apply: waiting for dependency
Streams apply: waiting to commit
Streams: flow control
Streams: resolve low memory condition
Streams: waiting for messages
WCR: replay paused
cell multiblock physical read
cell single block physical read
cell smart file creation
cell smart incremental backup
cell smart index scan
cell smart restore from backup
cell smart table scan
cell statistics gather
direct path sync
enq: AM - ASM File Destroy
enq: XC - XDB Configuration
enq: ZA - add std audit table partition
enq: ZF - add fga audit table partition
external table misc IO
external table read
external table write
gc cancel retry
gc recovery
ges DFS hang analysis phase 2 acks
ges/gcs diag dump
listener registration dump
master diskmon startup
process diagnostic dump
securefile chain update
shared server idle wait
virtual circuit wait

40 rows selected.

There are no changes between WAIT_CLASSes.

latches:
removed:
select name from latch_11106 minus select name from latch_11107;

NAME
---------------------
FAL subheap alocation
TXN SGA
cv apply list lock
cv free list lock

New:
select name from latch_11107 minus select name from latch_11106;

NAME
---------------------------------------
VPSO SGA
WCR: MMON Create dir
WCR: kecr File Count
cvmap freelist lock
ges DFS wait callback info
heartbeat check
kcfis stats shared latch
parallel ibr array
rsb inst ckpt scn
storage server table manipulation latch

10 rows selected.

Unfortunately I do't know how to get a List of all existing mutexes. v$mutex_sleep_history does not give all possible mutexes, only these, qou used in the past (and the system holds historical infos about.

If someone knows, how to get all mutexes, please inform me!

ANY translated to filter(("col1"= OR "col2=))

2008-09-19T11:12:00.008+02:00

I had to tweak a sequence of statements, in pseudo-code it was something like this:


select col1 into v1
from tab1
where col2=<condition1>;
delete from tab2
where col3 = v1;
delete from tab2
where col4 = v1;

My first step was to avoid the variable v1 and put the select into the deletes as a subselect.
My 2nd step was to merge the 2 deletes into one, combining the 2 selerated conditions using OR.
this gave an execution plan similar to this (I use the test-table from my previous post and a really simple statement):

delete
from berx1
where type=(select 1 from dual)
or dim=(select 1 from dual);

-------------------------------------
| Id  | Operation           | Name  |
-------------------------------------
|   1 |  DELETE             | BERX1 |
|*  2 |   TABLE ACCESS FULL | BERX1 |
|   3 |    FAST DUAL        |       |
|   4 |     FAST DUAL       |       |
-------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("TYPE"= OR "DIM"=))

The next idea was to avoid running the same subselect twice:
My first thought was to use the subquery_factoring_clause (WITH), but it's only useable in SELECT statements. So I loitered through the documentation and stumbled across the ANY keyword. After some ests I came to this statement:

delete
from berx1
where (select 1 from dual) = any (type, dim);

------------------------------------
| Id  | Operation          | Name  |
------------------------------------
|   1 |  DELETE            | BERX1 |
|*  2 |   TABLE ACCESS FULL| BERX1 |
|   3 |    FAST DUAL       |       |
------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("TYPE"= OR "DIM"=))

Anything more to say? Only one subselect, even the filter is the same.
I tested this behaviour with 11.1.0.6 and 10.2.0.4 - both are the same.

simple tuning using decode

2008-09-19T06:48:00.006+02:00

Currently I have to assist in tuning of a really time critic application (in comparison to other applications/projects I have to support). There I found 2 statements called within an PL/SQL procedure:

select count(id)
from berx1
where val=1 and type is null

select count(id)
from berx1
where val=1 and type is null and dim= 2

(The statements are reduced to test-cases, my test-table is

create table berx1 (id number, val number, type number, dim number);

insert into berx1 values (1, 1, NULL, 1);
insert into berx1 values (2, 1, 1, 2);
insert into berx1 values (3, 1, NULL, 3);
insert into berx1 values (4, 1, 1, 1);
insert into berx1 values (5, 2, NULL, 2);
insert into berx1 values (6, 2, 1, 3);
insert into berx1 values (7, 2, NULL, 1);
insert into berx1 values (8, 2, 1, 2);
insert into berx1 values (9, 3, NULL, 3);
insert into berx1 values (10, 3, 1, 1);
insert into berx1 values (11, 3, NULL, 2);
insert into berx1 values (12, 3, 1, 3);
insert into berx1 values (13, 4, NULL, 1);
insert into berx1 values (14, 4, 1, 2);
insert into berx1 values (15, 4, NULL, 3);
insert into berx1 values (16, 4, 1, 1);
insert into berx1 values (17, 5, NULL, 2);
insert into berx1 values (18, 5, 1, 3);

)
Even both of them are very fast (with an index on val), As we have to save every slice of time we can, I merged the statements into one:

SELECT count(id) count
, NVL( SUM( DECODE(dim, 2   -- if 'dim' is 2 
                      , 1   -- add 1 to sum
                      , 0)  -- otherwise add nothing
          ), 0
     ) count_dim
FROM berx1 where val=1 and type is null;

What did I expect so far? (not tested into detail!)

less context-switches between PL/SQL and SQL
less soft parses / library cache lookups
less activity in the buffer cache

In fact, at the end of the optimization I hope I can modify the program logic, maybe we can save a lot more time on other places and also get rid of this statement. But this is still matter of investigation with the application-designers.

switching patches on and off

2008-08-29T18:01:00.000+02:00

You want to play around with optimizer-patches in your DB?

you want to break your DB a curious way?

have a look at v$system_fix_control and v$session_fix_control and gamble by change the parameter _fix_control. I also found _fix_control_key but no information about it.

For more than one patch to switch, use
alter session set "_fix_control"='4728348:OFF','4663698:OFF'; i learned here.

What is it good for?

To test if the just-applied patch works as expected.

Recommended?

Patches are to fix bugs. Playing around with bugs is a job for exterminators.

Happy playing ;-)

gc current block 2-way - p1,p2,p3

2008-08-29T13:00:00.000+02:00

In some tracing I stumbled about a lot of lines like this:


nam='gc current block 2-way' ela= 1716 p1=7 p2=449034 p3=33554433 obj#=61165 tim=1190815206676979

Even I know the Interconnect is too slow (only 100MBit), I just want to know what I read here. (at least the best way I can do).

the meaning of ela=, obj#= and tim= can be found on many places. But the p1, p2 and p3 where of some interrest. So I started to search. but neither Metalink nor Oracle-L could answer my search-results (maybe I just used the wrong words?) There where even no other direct source I could find.

I also run


SELECT name, parameter1, parameter2, parameter3
FROM V$EVENT_NAME
WHERE name like 'gc current block %way';

and got


NAME                                                       P1    P2    P3
------------------------ ----- ----- -----
gc current block 2-way
gc current block 3-way

back.
there is also nothing in the 11g documentation about gc current block.

Not so good informations so far. This led me to a searching marathon:

(only my findings)

The Book Oracle 10g RAC Grid, Services & Clustering gives a link between gc current block 2-way (10g Wait name) and global cache open x (9i Wait name).
The Book Oracle Wait Interface tells me about global cache open x:
global cache open x Wait Parameters Wait parameters for global cache open x are described here: P1 File number from which Oracle is reading the block P2 Block number from the file that Oracle wants to read into a buffer P3 The lock element number or class of the buffer

one more finding (from limited value): in the docu there is an event GCS lock open X which seems to have the same content for P1, P2 and P3. But of course a 'lock open' is not simply equal to a 'cache open' or a request for a 'current block'.

So far for the (very limited) documentation I found.

Now let's do some tests (on an other DB):

instance 1: create table test1 as select * from dba_objects;

instance 1: select * from test1;27 cr multi block request, 2 gc cr grant 2-way. I don't know where they come from(!) this will be matter for further investigations.

instance 1: select * From test1;no gc cr ... only SQL*Net message

instance 2: select * from test1; this is the most interresting part: a lot of differnt waits, most interresting is:
WAIT #1: nam='gc cr multi block request' ela= 144 file#=6 block#=48017 class#=1 obj#=62850 tim=685587786409 WAIT #1: nam='gc current block 2-way' ela= 1121 p1=6 p2=48018 p3=1 obj#=62850 tim=685588475668
looking down for 6. ( i run it with 48017 and 48018) I can at least compare file# to p1 and block# to p2 - obj# also matches

instance 2: select * From test1;only one library cache lock and of course SQL*Net message

SELECT segment_name FROM dba_segments WHERE header_file=6 and 48017 between HEADER_BLOCK and HEADER_BLOCK+blocks;
SEGMENT_NAME ------------ TEST1
andSELECT OBJECT_NAME, OBJECT_ID FROM dba_objects WHERE OBJECT_ID=62850;
OBJECT_NAME OBJECT_ID ----------- ---------- TEST1 62850

Conclusio:

So my findings are:

more questions, e.g. why are there any gc in the 1st select?
Even I still cannot proove, I assume p1, p2 and p3 of gc current block 2-way (and mayby also others) match to file#, block# (and maybe also class# - even I have not thought about this!)

the 'Magic of 2'

2008-08-29T10:00:00.000+02:00

There is a very popular pdf about parallel queries.

Why I quote it here?

to create another link , give you something to read and as a starting-point for my own tests.

manual Remastering of Global Cache

2008-08-27T09:22:00.003+02:00

this all is stolen from a Presentation from Julian Dyke. Some infos are also from Fairlie Rego.

for more background, please read there (or wherever you want). This is only a container for some code sniplets:

find the object_id from the object you are interested in:


SELECT data_object_id
FROM dba_objects
WHERE owner = 'US01' AND object_name = 'T1';

find current and previous master and a remaster-count (this might be 0 all the time due to BUG:5649377 - fixed in 11G - Backports available for Linux32-10.2.0.2&10.2.0.3


SELECT object_id,current_master, previous_master ,remaster_cnt
FROM V$GCSPFMASTER_INFO
WHERE object_id = 144615;

to remaster the object to a dedicate instance go tho this instance and run


oradebug setmypid
oradebug lkdebug -m pkey <obj_id>

To dissolve remastering of this object on this instance


oradebug lkdebug -m dpkey <obj_id>

There are some more infos availalbe from a bunch of internal tables.
[X$KJDRMREQ-Dynamic Remastering Requests, X$KJDRMAFNSTATS-File Remastering Statistics, X$KJDRMHVSTATS-Hash Value Statistics,X$KJBL-Externalizes GCS Enqueues,
X$KJBR-Externalizes GCS Resources]
(I will not discuss it here)

a fix for OS X syslogd going crazy

2008-08-24T06:37:00.004+02:00

recently the fan of my PowerBook run permanently. I checked which process created the load (and therefore the heat) and found syslogd.
After some tracing, I saw a lot of


lseek(0x6, 0xF0, 0x0)         = 240 0
read_nocancel(0x6, "\004\0", 0x1000)         = 4096 0
lseek(0x6, 0xFD, 0x0)         = 253 0
write_nocancel(0x6, "\0", 0x4)

With some googeling, I found (only) this blog-entry.

The solution from there (copy&paste)

sudo launchctl stop com.apple.syslogd
sudo mv /var/log/system.log /var/log/system.log.old
sudo launchctl start com.apple.syslogd
sudo gzip /var/log/system.log.old

It works really fine for me. Thank you sailor.

Logitech Harmony Remote

2008-07-30T22:11:00.001+02:00

Recently I was so bored by all the different remotes for all different devices laying around, I went off to my next store and bought a Logitech Harmony Remote 555. It's a quite nice device and helped me to put away all other remotes by programming this one.
Like all devices, there is also here a negative point: The Software on my PowerBook didn't start, it just complained: The application configuration was not loaded. Reinstall the software or contact customer service.
The problem is based on the case sensitive filesystem and as I doesn#t want to change my entire Harddisk into a case insensitive one, I followed the hint from the forum:
create a disk image with 'disk utility', make sure it's not case sensitive. About 100 megabyte is enough

move the '/Applications/remotecore.app' and '/Applications/Logitech Harmony Remote Software.app' applications to that disk image

create a symbolic link of remotecore.app to '/Applications/remotecore.app'
So if you mounted your created volume 'Logitech' you can create a symbolic link with:
$ ln -s /Volumes/Logitech/remotecore.app /Applications/remotecore.app

make sure the image is mounted and start the 'Logitech Harmony Remote Software' located in that created volume.

Update:
Version 7.5 is out, and it works well so far. (just tested minimal)

TimeMachine for windows

2008-07-29T22:02:00.003+02:00

I love my Mac, and I like TimeMachine as the best new feature (I use) from Leopard.
I also searched for such a solution for Windows (e.g. for my parents or my sisters laptop).
My search has an end, I found rsyncbackup.vbs from heise.de.
I know, it's not a solution, jus a script. but I'm sure, I can adapt it a little bit, so it would be useable for my family (e.g.: in startup-script, check if target-disk is available, etc.)
We will see...

Oracle Server Architecture - pdf

2008-07-27T09:06:00.003+02:00

I've found a nice PDF (created by Lars Ditzel) which describes an Oracle Server from a file/process and memory point of view.
even it's not that new, it's more or less correct.
(maybe I will try to find the deltas to a current 11g instance with all its flavours)

DBAs little helper

2008-07-25T13:05:00.001+02:00

You might know this situation: Your users call and complain, the DB is hanging. While trying to connect (and making plans what to check first, sessions, waits, locks, ...) you do not get any prompt. Ok, there would be a lot of things you get in mind to check, analyze and then fix. But in fact, the users want their connections back, don't care about current open sessions and just ask to reboot the instance.
Even reboot is not that simple in this situation (have I mentioned the missing prompt in sql*plus?). So the next way is to try kill on os-level (which doesn't do anything at the end, as the process seems to wait for something on OS). A much more entertaining way is kill -9. I hope, if I kill a vital kernel process, the others will do the cleanup and close everything the best way they can (similar to a shutdown abort). But even this doesn't happen. The others are hanging around and doing nothing. At the end I have to kill all processes using -9.
There is just one more thing to do: startup. but, unfortunately, at the login there is one thing missing: 'Connecting to an idle instance' - and a 'startup' just hangs.
Why? Because no one did the cleanup! Do you remember the kill -9 of all, also the last background process? Doing so, I did not gave any process the chance to release the semaphore and the shared memory segments. They are just hanging around. Even no one uses them, sqlplus belives because they exists, there is a running system. So it connects to them, sends messages, but noone ever listens. this makes this sqlplus-instance also hanging.
How can i do the cleanup manually?
The old way, back in those days, when Versions did not contain characters like i or g, is to search all other instances for their ressources using oradebug ipc, and afterwards removing them with ipcs. If anything went wrong - another DB crashed very beautiful and interresting.
The new way, introduced in this millenium, is sysresv, which you can find in $ORACLE_HOME/bin. With this, you can show the semaphor and shared memory segments used by your current os-user, $ORACLE_HOME and $ORACLE_SID (or for multiple SIDs). It can also release the ressources for a given SID. This makes live much easier and more secure in situations like shown above.
I doesn't know why Oracle doesn't mention this binary in its documentation.

Oracle Performance books

2008-07-18T07:59:00.001+02:00

I got 2 new books:
Cost-Based Oracle Fundamentals by Jonathan Lewis (ISBN:1-59059-636-6)
and
Troubleshooting Oracle Performance by Christian Antognini (ISBN:1-59059-917-9)
Both are worth reading.
I will try to read and understand both of them.
Maybe I will reproduce some of their tests and will try to do my own, and maybe maybe I will post the results here ;-)

interpreting CDP packages (the ugly way)

2008-07-17T15:36:00.007+02:00

today I had to interpret CDP packages on our network.
I'm no shell-coder, but sometimes I'm curious and in such situations this script will come out.
In this particular case its output just gives the Switchname, Switchport, VLAN and Duplex settings, but everyone who wants can do a lot more (and better).
the script itselve:

#!/usr/bin/bash

snoopfile="/tmp/snoopy$$.bin"
snoopline="/tmp/snoopy$$.line"
snoop -d $1 -c 1 -vv  -o $snoopfile 'dst 01:00:0c:cc:cc:cc and length > 50'
snoop -i $snoopfile  -x 26 | nawk -F: ' { print $2 } ' | \
cut -b1-41|  sed -e 's/ //g' | nawk 'BEGIN { ORS="" } { print $1 } ' | \
tr [a-z] [A-Z] > $snoopline
instr=`cat $snoopline`
while  [ $instr ]
do
typ=`echo $instr | cut -b1-4`
lhex=`echo $instr | cut -b5-8`
length=$(echo "ibase=16; $lhex*2" | bc)
next=$(echo "ibase=16; $lhex*2+1" | bc)
if [ $length -gt 8 ]
then
texthex=`echo $instr | cut -b9-$length`
else
texthex=""
fi
#  echo "$typ $lhex $texthex"
if [ $typ == "0001" ]
then
printf "Switchname: "
while  [ $texthex ]
do
charhex=`echo $texthex | cut -b1-2`
chardec=$(echo "ibase=16; $charhex" | bc)
printf "%b" `printf '\x%x' $chardec 2>/dev/null`
texthex=`echo $texthex | cut -b3-`
done
echo " "
fi
if [ $typ == "0003" ]
then
printf "Switchport: "
while  [ $texthex ]
do
charhex=`echo $texthex | cut -b1-2`
chardec=$(echo "ibase=16; $charhex" | bc)
printf "%b" `printf '\x%x' $chardec 2>/dev/null`
texthex=`echo $texthex | cut -b3-`
done
echo " "
fi
if [ $typ == "000A" ]
then
echo "VLAN: 0x$texthex $(echo "ibase=16; $texthex" | bc)"
fi
if [ $typ == "000B" ]
then
echo "Duplex: $texthex"
fi
instr=`echo $instr | cut -b$next-`
done
rm $snoopfile $snoopline

Sorry for the line-breaks - you will have to reformat it a little bit (cut, paste & think).

OCM with or without proxy settings

2008-07-17T07:59:00.003+02:00

I tried to install and customize OCM version 10.3.

Installation was quite easy on our development system.
Just as I tried to create a response-file (using emocmrsp) for our test and production-systems, I noticed I cannot add proxy-settings to this.

I have to notice the ability to connect directly to *.oracle.com from this development server.

After some searching I created SR:6969566.993 and got the answer:
Starting with OCM 10.3 setupCCR , configCCR and emocmrsp commands does not include a parameter to specify a proxy server and port. However, If the systems these commands run on does not have direct Internet access then you will be automatically prompted to enter the proxy information. So in case you run emocmrsp on a system that does not have direct Internet access then you will be prompted to enter the proxy information and that will be recorded in the responce file also. If now there is no Internet access then you can enter the word NONE when prompted for a proxy and that will instrall it in disconnected mode.

This is a little bit boring, as I will not run a configuration tool on a Prod-Env, but cannot create a working response-file on my development-node.

I created an Enhancement Request BUG:7258715 enable emocmrsp to add proxy-settings to response-file in any case

Shadow process leak on ASM instance when diskspace exhausted (ORA-20)

2008-07-15T07:59:00.002+02:00

Once again one of 'my' bugs:
Facts:

10.2.0.3
HP-UX
archive_log_dest in ASM
ASM-Diksgroup full

Symptoms:

ORA-20
archivelogs doesn't get archived, even the whole backup-subsystem works properly.

Explanation:
If the DG is full he database will try to connect to ASM for every retry of archiver to create archivelog. Even after the attempt to create archivelog fails, this process is not closed.
This will eat up all 'processes' in ASM, so also the DB-process which tries to backup (and delete) the archivelogs can not connect to ASM.
Even if you freed up space in the ASM-DG in the meantime, this problem still persists.

WorkAround:
find OS-process of archiver (ps -ef | grep arc | grep $ORACLE_SID) and kill this process. this will also release all its childs (which holds processes in ASM) and solve the problem.

Reproduce:

fill up your diskGroup
force archiver to respawn a lot of processes (alter system archive log current;) or wait at least for the factor of the REOPEN parameter of your log_archive_dest_x multiplied with PROCESSES from your ASM.
check if there are no more ASM-processes available (e.g. a select name, free_mb from v$asm_diskgroup; will return no rows selected even there are diskgroups available and in use!)
free some space in the DiskGroup
check again if there are ASM-processes available
kill archiver
check again

The good newas after all: there is no corruption, just a ugly hang situation.

10g RDBMS vs. 11g ASM

2008-07-14T09:06:00.005+02:00

I tried to create a small test-env to compare oracle 10G and 11G.
My setup:
11G ASM (11.1.0.6)
11G DB (11.1.0.6)
10G DB (10.2.0.4)

Both DBs a re using their own DiskGroup within ASM, called according to their SID.

now at the 'CREATE DATABASE' i get:
ORA-00200: control file could not be created
ORA-00202: control file: '+TEST10G_DG'
ORA-17502: ksfdcre:4 Failed to create file +TEST10G_DG
ORA-15001: diskgroup "TEST10G_DG" does not exist or is not mounted
ORA-15204: database version 10.2.0.3.0 is incompatible with diskgroup TEST10G_DG

I also checked the Known Issues from 10.2.0.4 Patch Set - Availability and Known Issues, but with no results.

Once again, this leads to a new ServiceRequest (only visible for my CSI - sorry).

Reason/Solution:

dbca sets 'compatible=10.2.0.3' in the init.ora it uses to create the DB. this was the source of the problem.
By changing this Value to 10.2.0.4, everything is fine now.

ASM doesn't see block devices any longer since 10.2.0.4

2008-07-11T11:00:00.005+02:00

according to BUG:7120987 - base.BUG:6454631 from Version 10.2.0.4 onwards ASM does no longer see any block devices. I also tested and reproduced this in 11.1.0.6. Use character devices instead.
Only on Linux, you can still use block devices.
this doesn't seem to be rated as bug, more a kind of feature. so don't expect this to change back again.
This is also described in Note:601591.1 - After Upgrading to 10.2.0.4 or 11.1.0.6 Unable to discover disks using block devices and marked as expected behavior.

Oracle SIB

2008-07-11T09:30:00.004+02:00

I found an intresting SIB in Oracle clusterware.
Why SIB? IT's not a BUG, it's not a Docu-BUG, nevertheless it's not working and I'm advised to use my WorkAround ( a simple patch to some files). So it must be SomethingInBetween. I wasn't aware Oracle has such a status, but yes, it's there.

Summary:
On an Oracle- RAC Cluster on Solaris, you can not bring up a VIP on an interface, if you can not reach the default-GW via this interface. (If there is enough traffic on that network, it might or might not work, depending on the traffic during the probe-phase).

The problem is based on the way oracle checks if the interface is healthy: it first checks the packages in/out on that If, then tries to create some network-traffic using ping -I (Linux) or ping -i (solaris) and checks the packages again. If the numbers differ, it assumes the interface is good.

Unfortunately the -I (Linux) and -i (Solaris) differ a little bit:

Linux:
-I interface address
Set source address to specified interface address. Argument may be numeric IP address or name of device. When pinging IPv6 link-local address this option is required.

Solaris:
-i interface_address
Specify the outgoing interface address to use for multicast packets for IPv4 and both multicast and unicast packets for IPv6. The default interface address for multicastpackets is determined from the (unicast) routing tables.
interface_address can be a literal IP address, for example, 10.123.100.99, or an interface name, for example, eri0, or an interface index, for example 2.

Links:
my BUG:6839000 - Status: Not a Bug. To Filer - Base BUG:6332776 - Status: Closed, Not a Bug
my enhancement-request:6969925- not published - Status-code 15 (To Internal (Oracle) Review)