Archives mensuelles : avril 2013

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.

DECLARE
  TYPE t_type IS TABLE OF INTEGER;
  v_t1 t_type;
  v_t2 t_type;
  v_t3 t_type := t_type();
BEGIN
  SELECT * BULK COLLECT
  INTO   v_t1
  FROM   (SELECT 1 FROM dual UNION SELECT 2 FROM dual);
  v_t3 := v_t1 MULTISET UNION ALL v_t3;
  SELECT * BULK COLLECT
  INTO   v_t1
  FROM   (SELECT 3 FROM dual UNION SELECT 4 FROM dual);
  v_t3 := v_t1 MULTISET UNION ALL v_t3;
  FOR i IN v_t3.first .. v_t3.last LOOP
    dbms_output.put_line(v_t3(i));
  END LOOP;
END;
/

Which will display

3
4
1
2

New Exadata mode for DBMS_STATS.GATHER_SYSTEM_STATS

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 11.2.0.2.18 or 11.2.0.3.8 (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…..