Happy Life KPI

If you want to measure how happy is your life, then ...you can use the following KPI! Of course you can modify the script to fit your own life priorities ;-)
I have created two versions of the query. The first one uses the analytic function ratio_to_report to generate the percentage needed for the KPI and thus makes the query a bit more "advanced" ;-)

select result, round(ratio_to_report(count(result)) over() * 100) ||'%' happy_life_KPI
from (
select  decode(time_spent, 'FAMILY', 'enjoy', 'FRIENDS', 'enjoy', 'ORACLE', 'enjoy', 'deadly dull') result
from mylife.days
start with yesterday is null and status = 'BORN'
connect by prior day = yesterday and status <> 'DEAD'
)
group by result
order by 2 desc
/

select round(count(decode(result,'enjoy',1,null))/count(*) *100) ||'%' happy_life_KPI
from (
select  decode(time_spent, 'FAMILY', 'enjoy', 'FRIENDS', 'enjoy', 'ORACLE', 'enjoy', 'deadly dull') result
from mylife.days
start with yesterday is null and status = 'BORN'
connect by prior day = yesterday and status <> 'DEAD'
)
/

Enjoy
OL

Update your stale statistics

Object statistics are very important for your data warehouse. Tables and indexes must have updated statistics in order for the query optimizer to choose the best execution plan for each query. So you must continously gather objects statistics (especially after ETL jobs have modified significantly the data).

The easiest way to do this is to utilize Oracle's automatic statistics gathering task. This can be easily configured to run at a specific maintenance window (that you will define, unless you want to use the default) from Enterprise Manager (Administration --> Automated Maintenance Tasks --> Optimizer Statistics Gathering).

Note that you should use an appropriate degree of parallelism so that the stats gathering process runs fast enough to fit in the maintenance window. You may have to experiment a bit with this, in order to find what is appropriate for your case.

But how can you monitor if the automated task has succesfully managed to gather statistics in the defined maintenance window?

Don't treat your Data Warehouse as a warehouse!

   It is very common in a large organization an enterprise Data Warehouse to be considered as a "warehouse". In other words, just like in a typical warehouse we store anything that is useless but we cant just discard it - since we need to store it someplace-, or anything that we don't know where else to store it, or anything we just want to get rid of. In the same manner, many people, whenever they have some tables that they need in order to generate some reports, and these tables for X reasons cannot be stored in some transaction system, they choose the enterprise data warehouse as the best place for storing these data. Of course, this means that you treat your data warehouse as if it is the "recycle bin" of your IT systems!

How to connect to Exadata

How can one connect to an Exadata? In our example we will assume a half rack Exadata, which means that at the database layer we have a 4-node database RAC.

At the server side, we need to install a local listener on each database node. So we have 4 listeners "listening" for Exadata connections.

At the client side we assume the so-called "Local Naming Method" for name resolving, which simply means that we have at the client side a tnsnames.ora  file with an appropriate tns entry. But what should be an appropriate tns entry for connecting to our Exadata?

Connecting to Oracle bypassing the listener (aka a "bequeath connection")

Whenever you connect to Oracle from a client  running on the same machine as the database without specifying a net service name in the connect string, then you can connect to Oracle bypassing the listener. This is called a Bequeath connection. For example, when you dont specify @<net service name>, then you connect to Oracle without a listener. In the following example I have set the environment variable ORACLE_SID, so as to connect to a specific instance:

Use ASH for troubleshooting a long running query

You wont believe it how useful ASH (Active Session History) can be.

Step 1: Find the sql_id of the long_running query (the following lists the top 100 most -busy queries. I.e., queries that are in the most ASH samples either on cpu or actively waiting on something) :

[Update]: In this newer post we describe how one can use ASH and SQL Monitoring to tune a complex query that does not even finish!

Two ways to make a table read-only

Here are two ways to make a table read-only:
First lets do it the 11g way:


nikos@NIKOSDB> create table lala (x int, y int)
  2  /

Table created.

nikos@NIKOSDB> insert into lala values (1,1)
  2  /

1 row created.

nikos@NIKOSDB> insert into lala values (2,2)
  2  /

1 row created.

nikos@NIKOSDB> commit
  2  /

Commit complete.

Moving a table and rebuilding the indexes at the same time

After moving a table (ALTER TABLE XXX MOVE ), because the physical location of the rows changes, i.e., the rowids of the records change, all indexes on the table will become "UNUSABLE". This means that we have to manually rebuilt them (ALTER INDEX YYY REBUILD).

Is it  possible to move a table and rebuild the indexes at the same time with a single move statement? (Note: I am not considering the case of using the DBMS_REDEFINITION package for on-line table redefinition)

Hello World!

Hello, World!

This is the fist blog post in Oracle Data Warehousing Stories. This is a blog mainly intended for personal use where little bits from my everyday Oracle Data Warehousing experiences will be published, as well as Oracle-related stuff from my readings, other blogs etc. It is just a place to gather all this info and have it easily accessible as well as share it with anyone else who cares.

As Tom Kyte says, you can learn or relearn something new about Oracle everyday. I am no Oracle guru, nor interested to become one,  I just love walking on this Oracle learning path everyday ...

Cheers.