Merging PL/SQL arrays… or when Oracle is so nice with me

Today i was searching a nice way to merge 2 arrays inside a pl/sql bloc.

I have found plenty of solutions over the web with select statment and cast, or with loop inside PL/SQL.

But in fact there is a more easier way to do that in 1 row … This is possible with the MULTISET operator.

  v_t1 t_type;
  v_t2 t_type;
  v_t3 t_type := t_type();
  INTO   v_t1
  v_t3 := v_t1 MULTISET UNION ALL v_t3;
  INTO   v_t1
  v_t3 := v_t1 MULTISET UNION ALL v_t3;
  FOR i IN v_t3.first .. v_t3.last LOOP

Which will display



There is a new configuration in DBMS_STATS that can help Oracle to understand that it is working on an Exadata.

Most of the time the FULL TABLE SCAN is more performant than an INDEX SCAN on Exadata systems (i say most of the time, so i mean you have to test by yourself). The problem of this is with default system stats.
With the default system stats does not know that a FTS may be less expensive than accessing an index.

To help Oracle to know it is working on a EXADATA system there is a new GATHER_SYSTEM_STATS mode exclusively for EXADATA.

To enable this new mode you have to execute the following command

exec dbms_stats.gather_system_stats('EXADATA');

This new mode is available from the or (nore information on Metalink note Oracle Sun Database Machine Setup/Configuration Best Practices [ID 1274318.1])

Problems with incremental stats + compression + extended stats

I have recently hit a bug (that seems to be fixed in the BP6 for Exadata)

But i thought it was interesting to post about this (it may help someone)

When you have a table that is compress, you need to be carefull with Extended statistics cause it had a virtual hidden column to manage those stats. Right now there is no problem, the problems start when you drop the extended stats and try to use incremental stats on the table.

When you drop the extended stats it does not remove the virtual hidden column (first problem) it just set the column as UNUSED because of the compression.

My issue cames from this and the use of the incremental stats which had a bug and does not know anything about UNUSED columns. So each time i was trying to gather stats on the table Oracle was gathering all the stats again and again because it was finding that this UNUSED column did not get any stats…..

How to find tables analyzed by dbms_stats job

Sometime it can be usefull to know what have done the automatic stats jobs

Here is a statment that will let you know the objects that have been analyzed by the job

As SYS execute the following

SELECT /*+ dynamic_sampling(4) dynamic_sampling_est_cdn */
                  WHEN o.type# IN (1, 20, 35)
                     THEN 'INDEX'
                  WHEN o.type# IN (2, 19, 34)
                     THEN 'TABLE'
                  ELSE 'UNKNOWN'
               END object_type,
               u.NAME owner, o.NAME object_name,
                  WHEN o.type# IN (19, 20)
                     THEN o.subname
                  WHEN o.type# IN (34, 35)
                     THEN (SELECT po.subname
                             FROM SYS.obj$ po
                            WHERE po.obj# =
                  ELSE NULL
               END PARTITION,
                  WHEN o.type# IN (34, 35)
                     THEN o.subname
                  ELSE NULL
               END SUBPARTITION,
               CASE st.STATUS
                  WHEN 0
                     THEN 'PENDING'
                  WHEN 1
                     THEN 'BEING PROCESSED'
                  WHEN 2
                     THEN 'COMPLETED'
                  WHEN 3
                     THEN DECODE (BITAND (st.flags, 8),
                                  8, 'TIMEOUT',
                  ELSE NULL
               END STATUS,
                  WHEN st.staleness <= -99
                     THEN 'MISSING'
                  ELSE 'STALE'
               END reason
          FROM SYS.stats_target$ st, SYS.obj$ o, SYS.user$ u
         WHERE st.obj# = o.obj#
           AND o.owner# = u.user#
           AND st.STATUS != 4
           AND (st.SID, st.serial#) IN (
                  SELECT /*+ no_unnest */
                         REGEXP_REPLACE (session_id, ',.*') SID,
                         REGEXP_REPLACE (session_id, '.*,') serial#
                    FROM (SELECT   CASE
                                      WHEN REGEXP_REPLACE
                                                      ) IS NULL
                                         THEN session_id
                                      ELSE NULL
                                   END session_id
                              FROM dba_scheduler_job_run_details
                             WHERE job_name = 'GATHER_STATS_JOB'
                          ORDER BY actual_start_date DESC)
                   WHERE ROWNUM < 2)

Rotating table with Oracle UNPIVOT

I had recently faced a SQL question about rotating a table

The question was how to rotate transform a table gettint multiple columns into rows

Here is a small sample to create the table

CREATE TABLE test_tab(productm VARCHAR2(30),
                      leg1 VARCHAR2(30),
                      legval1 INTEGER,
                      leg2 VARCHAR2(30),
                      legval2 INTEGER,
                      leg3 VARCHAR2(30),
                      legval3 INTEGER);

INSERT INTO test_tab
VALUES ('prod 1', 'subprod 1-1', 11, 'subprod 1-2', 12, 'subprod 1-3', 13);
INSERT INTO test_tab
VALUES ('prod 2', 'subprod 2-1', 21, 'subprod 2-2', 22, 'subprod 2-3', 23);
INSERT INTO test_tab
VALUES ('prod 3', 'subprod 3-1', 31, 'subprod 3-2', 32, 'subprod 3-3', 33);
INSERT INTO test_tab
VALUES ('prod 4', 'subprod 4-1', 41, 'subprod 4-2', 42, NULL, NULL);
INSERT INTO test_tab
VALUES ('prod 5', 'subprod 5-1', 51, NULL, NULL, NULL, NULL);

SELECT * FROM test_tab;

PRODUCTM  LEG1         LEGVAL1  LEG2         LEGVAL2  LEG3         LEGVAL3
--------- ------------ -------- ------------ -------- ------------ --------
prod 1    subprod 1-1        11 subprod 1-2        12 subprod 1-3        13
prod 2    subprod 2-1        21 subprod 2-2        22 subprod 2-3        23
prod 3    subprod 3-1        31 subprod 3-2        32 subprod 3-3        33
prod 4    subprod 4-1        41 subprod 4-2        42                 
prod 5    subprod 5-1        51                                          

And the result needed was something like that

---------------- ---------------- ---------------
prod 1           subprod 1-1                   11
prod 1           subprod 1-2                   12
prod 1           subprod 1-3                   13
prod 2           subprod 2-1                   21
prod 2           subprod 2-2                   22
prod 2           subprod 2-3                   23
prod 3           subprod 3-1                   31
prod 3           subprod 3-2                   32
prod 3           subprod 3-3                   33
prod 4           subprod 4-1                   41
prod 4           subprod 4-2                   42
prod 5           subprod 5-1                   51

After some researchs i finally found that Oracle 11G got the solution for me

SELECT productm, prodname, prodval
FROM   test_tab unpivot((prodname, prodval) 
FOR    prod IN( (leg1, legval1),
                (leg2, legval2),
                (leg3, legval3)));

Tuning Database link operations

I was recently trying to tune one of my process that is working with a database link. The process is to insert datas based on a join using a small local table and a distant big one. The problem i was hitting was that because Oracle does not have enough information on the distant table it was sending me back all rows from the distant table.

After some research i found on the Oracle documentation (


Description of driving_site_hint.gif follows
Description of the illustration driving_site_hint.gif

(See « Specifying a Query Block in a Hint »tablespec::=)

The DRIVING_SITE hint instructs the optimizer to execute the query at a different site than that selected by the database. This hint is useful if you are using distributed query optimization.

For example:

SELECT /*+ DRIVING_SITE(departments) */ * 
  FROM employees, departments@rsite 
  WHERE employees.department_id = departments.department_id;

If this query is executed without the hint, then rows from departments are sent to the local site, and the join is executed there. With the hint, the rows fromemployees are sent to the remote site, and the query is executed there and the result set is returned to the local site.

But after doing some tests my statment was still retrieving locally the distant table. So i have continue my research and finally found this article on metalink Limitations of DRIVING_SITE Hint [ID 825677.1] that says

« So keep in mind when using the DRIVING_SITE hint this is merely for query optimization and not intended for DML or DDL. »

So this hint can be usefull in some case but not in mine…

Hope this will help.


Going to Exadata what’s change for a DBA PART1

There is a lot of things that can be disturbing for a DBA when coming from a « normal » database to Exadata.

The first one i have been discover and will talk about is : HCC Compression

This compression type is only available on Exadata and offer 4 new compression methods :


Those compression methods are quite different for a « normal » DBA since they do not use compress data present in a block, but use a different algorythm that works with column (see for more details)

This new compression method allow a better compression ratio than normal database. (10x for QUERY HIGH and 15x for ARCHIVE HIGH)

The use of those methods are not different from Oracle database and are enabled with the following command


Here are some things you have to know before starting to use HCC compression

  • Only direct inserts (with APPEND hint), parallel DML, create table as select and SQL*Loader in direct mode can use the compression. For example if you do a normal insert, the compression will not occur.
  • Be careful with updates, cause HCC does not « support » updates and compression. In fact when you do an update on a row that have been compressed in QUERY HIGH for example this row will be uncompressed (move out of the compression unit) then add in a new block that will use OLTP compression. The result will be a lower compression ratio on this table/partition/subpartititon
  • You have to know that the column COMPRESS_FOR of views <DBA|ALL|USER>_<TABLES|TAB_PARTITIONS|TAB_SUBPARTITIONS> does not reflect the reality. It does just reflect the configuration of the table/partition/subpartitions. It means that you can have QUERY HIGH displayed for a table but no row compressed in it. The only way to check if rows are compressed is to use the DBMS_COMPRESSION package.
  • Deletes will preserve the compression, BUT you have to know that the compression unit will not be release until it will contains rows. So it can result in a lot of unused space in the table if you do to many deletes

For myself, my own datas have allow me to get a score of 12x compression in QUERY HIGH and a score around 16x in ARCHIVE HIGH.

Backup Exadata databases on Netbackup with Infiniband

Recently i had to check the backup configuration of my Exadata. We relealized that the configuration could be changed to obtain better performance with Netbackup because were not using the Infiniband network.

The configuration is composed of 3 servers (those ip are completly wrong and are just here to help you to understand how it works) :

  • 1 Exadata with the following ip addresses
    Public : (for name exadb01)
    Priv : (for name exadb01-priv)
  • 1 Netbackup Media Server with the following ip addresses
    Public : (for name media)
    Priv : (for name media-priv)
  • 1 Netbackup Master Server
    Public : (for name master)

The hole configuration is just to fake the ip of the Exadata server to the master server

The first thing to do is to force the Exadata and the Media server to communicate over the Infiny Band Network. To do that you just need to change both Exadata/Media server host name

On the Exadata you should add (so the Exadata will communicate on the Infiniband network to the media server) media.domain media

On the Media Server you should add (to allow it to communicate with the Exadata on its Infiniband network card) exadb01-priv.domain exadb01-priv

To finish the configuration you need to fake the master server to make it think it is communicating with Exadata on the private interface, so you need to add this in the host file of the master server exadb01-priv.domain exadb01-priv

Now the configuration is complete from the network point of view. To finish it from the Netbackup configuration you will have to use the exadb01-priv name for the client name

At the end of this change i have been able to backup at 49 Mb/s/channel with 8 channels giving 1,3Tb/h for 8 channels. The backup was start from the node 1 with direct db connection.

My limitation is coming from the storage use for the VTL

Hopes this will help you

Starting to post again

This is it!
It has been so long i was thinking to re-open my blog.

With my new mission, i have been able to work on a new Oracle technology that has get me to think about many things new for me.

Those are just personnal reflexions that you may found interesting.

Forum 0.7.1 has been released!!


Here is the new version of the forum application.

The new things on this version are :

  • Hot topics system
  • New lite version
  • New wrapped version (this version has been wrapped for 10g, if you have any problem please let me know here so the forum is using less space in memory

– 0000627[Administration] Error when viewing Messages to Approve (administrator) – resolved.
– 0000625[General] Error when making a new post that has much text (administrator) – resolved.
– 0000630[General] Javascript error document.oldtxt.v_old_txt.value does not exist (administrator) – resolved.
– 0000621[Administration] error in Manage Moderators (administrator) – resolved.
– 0000622[Administration] Wrong permissions displayed on forum moderators page (administrator) – resolved.
– 0000618[Administration] How can I search to list all users (administrator) – resolved.
– 0000617[Install script] When I tried create a New file extension fail. (administrator) – resolved.
– 0000616[General] Add a forum subscription managment page (administrator) – resolved.
– 0000615[Private Message] Can not drop, add contact in msg display (administrator) – resolved.
– 0000612[General] [Error in forum_functions.can_read : -1422 ORA-01422: exact fetch returns more than requested number of rows] (administrator) – resolved.
– 0000608[Administration] « [Error in forum_admin.status_update : 100 ORA-01403: no data found] » (administrator) – resolved.
– 0000609[Administration] When I delete a status, it shows error: « 404 Not Found Unable to process request. Please check the log file for more details. » (administrator) – resolved.
– 0000605[Email] Email are not sent when login is defined (administrator) – resolved.
– 0000601[General] Review moderation page code (administrator) – resolved.
– 0000600[General] Remove the forum_package.go_last function (administrator) – resolved.
– 0000602[General] use date formats in forum_constant (administrator) – resolved.
– 0000598[User] Add a profile view counter (administrator) – resolved.
– 0000599[User] Add last action date on user profiles (administrator) – resolved.
– 0000595[General] Review top ten user page (administrator) – resolved.
– 0000596[General] Improve performance of top ten pages (administrator) – resolved.
– 0000592[Search] Add a search in files only option (administrator) – resolved.
– 0000593[Search] Error when searching in title only (administrator) – resolved.
– 0000591[Search] B.FILE_ID invalid identifier when searching in title only (administrator) – resolved.
– 0000590[General] Title does not update when you go on a forum (administrator) – resolved.
– 0000588[General] Title does not update when you go on a category (administrator) – resolved.
– 0000589[Lite Version] Security issue on lite version categories page (administrator) – resolved.
– 0000587[General] Remove categorie test in categorie pages (administrator) – resolved.
– 0000526[Administration] Complete rebuild of the rank part (administrator) – resolved.
– 0000586[Administration] Add a clear user top record option in administration (administrator) – resolved.
– 0000585[Administration] Add an option to resynchronise all forum at once (administrator) – resolved.
– 0000573[Lite Version] Rebuild the lite version (administrator) – resolved.
– 0000583[Lite Version] Page title is not updated (administrator) – resolved.
– 0000575[Lite Version] Review low version forum page (administrator) – resolved.
– 0000576[Lite Version] Add number of topic and number of replies on forum page (administrator) – resolved.
– 0000574[Lite Version] Review low speed forum statment (administrator) – resolved.
– 0000578[Lite Version] Review low version topics page (administrator) – resolved.
– 0000580[Lite Version] Review low version posts page (administrator) – resolved.
– 0000577[Lite Version] Review low version categories page (administrator) – resolved.
– 0000584[Lite Version] Add a back to top button (administrator) – resolved.
– 0000581[Lite Version] Add answer and new post button on posts page (administrator) – resolved.
– 0000579[Lite Version] Add last post date on topic page (administrator) – resolved.
– 0000582[Lite Version] Add new post button on topics page (administrator) – resolved.
– 0000571[General] Remove multiple call to can_mododo on parent page (administrator) – resolved.
– 0000569[General] Remove multiple check for avatar display (administrator) – resolved.
– 0000570[General] Remove statment for user details on topic page (administrator) – resolved.
– 0000568[General] Remove counting replies statment (administrator) – resolved.
– 0000567[General] Empty description area removed (administrator) – resolved.
– 0000562[General] Add Hot feature (administrator) – resolved.
– 0000565[General] Change the function to get topic status (administrator) – resolved.
– 0000566[General] Need to update legende area (administrator) – resolved.
– 0000564[General] Remove is_readyonly test for the forum page (administrator) – resolved.
– 0000563[General] Change the statment for the forum read status (administrator) – resolved.
– 0000557[Apis] Add api to resync search indexes (administrator) – resolved.
– 0000556[Administration] Add forum name in the forum update page (administrator) – resolved.
– 0000555[General] Remove one statment of the birthday part (administrator) – resolved.
– 0000554[General] Getting last created user can be slow (administrator) – resolved.
– 0000553[General] Moving overall links (administrator) – resolved.