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 (more...)

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  (more...)

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 (more...)

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 (more...)

avoid implicit COMMIT at DDL

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;
(more...)