DBPedias

Your Database Knowledge Community

berxblog

  1. do not touch if you do not know for sure


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

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

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

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

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

    My test-script here
    VARIABLE lev number
SET AUTOPRINT ON
EXECUTE sys.dbms_system.read_ev(10046, :lev)

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

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


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

oradebug setmypid
oradebug eventdump session

    gives the expected result in one of my 10g test DBs:
    @test_read_ev.sql

PL/SQL procedure successfully completed.

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

Session altered.

PL/SQL procedure successfully completed.

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

Statement processed.
10046 trace name CONTEXT level 8, forever

    but an unexpected result in my 11.2.0.3 test DB:

    @test_read_ev.sql

PL/SQL procedure successfully completed.

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

Session altered.

PL/SQL procedure successfully completed.

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

Statement processed.
sql_trace level=8

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

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

  2. total abuse of technology



    I had a (for my environment) unusual request:
    After the migration of a Repository Database from 9i to latest 10g I was asked to keep a backup of the old DB for at least 3 years.
    This does not sound very unusual, but it's not that simple in our environment. We do only keep backups for weeks to some month, worst case. I also cannot just backup the datafiles at all: The old database run on Solaris, but we are switching to Linux right now. With just some bad luck I would not have any system to restore (or open) this database backup at all.
    This brought me to another solution; in my point of view it was not worth to write a blog about it, but I was asked by Leighton L. Nelson and so I write:

    1. export of the full database
      I run a simple export of the database. There is no expdp in 9i, so the choice was easy.

    2. compress the files
      the dump (and the logfile!) where tared together and compressed. Just to save space.

    3. prepare a proper store
      As mentioned above, there is no dedicated system for this purpose. So I had to prepare a place where the dump is safe. As a DBA, of course I know a good place to store data: A database!
      First a DBFS came to my mind. But the DB is in Version 10.2 - no DBFS.
      But it's quite simple to do the important steps manually:
      create tablespace old_dump datafile '+<DG>' autoextend on;
create user old_dump identified by dump_old1 default tablespace old_dump;
GRANT CONNECT, CREATE SESSION,  CREATE TABLE to old_dump;
alter user old_tech_dump quota unlimited on old_dump;

connect old_dump/dump_old1

create table old_dump_store 
(id integer primary key, description VARCHAR(2000), file_store BLOB) 
LOB (file_store) STORE AS  SECUREFILE 
(TABLESPACE old_dump DISABLE STORAGE IN ROW   NOCACHE LOGGING);

    4. insert the dump (and some metadata)

      There is a nice way in SQL Developer to load a file to a BLOB. It's just so simple.
      At last some words in the comment field are worth - so everyone knows what's inside the BLOB.
    It still might sound strange to save the dump of an old database into it's descendant. But at the end: do you know a better place to store data than a database?

  3. restore DBMS_SCHEDULER.CREATE_CREDENTIAL cleartext password


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

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


    It's quite easy to see the values again:

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

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


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

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

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

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

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

  4. Setting Up Oracle Connection Manager (without SOURCE_ROUTE)


    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.

  5. Who created that process?

    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.

  6. bzip2 twice?


    To check the performance of RMAN backup I recently started to trace it a little bit. As most of the time was not spent in any reading from disk or writing to media manager library event, it was on CPU. It's good to know the CPUs are of any good, but as I still want to know what's going on I tried to dig any deeper. CPU 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.

  7. non-Exadata HCC

    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

  8. when does PMON register to remote listeners

    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

  9. incomplete list of SRVCTL commands

    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.

  10. estimated plan stability

    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?


  1. 1
  2. Next ›
  3. Last »