Multiple indexes on same column

In the past oracle allowed only one index for same columns. if you try to create another index (with same columns), Oracle throws an error ORA-01408 : such column list already indexed

With 12c, Oracle allows multiple indexes on same column(s). However, only one index is visible any time. It also allows having one function based index + one regular index to be visible.

Read Full Post »

How to display/return procedure name when there is error (PL/SQL Directive)

If you would like to return or display pl/sql program unit name when there is an error, you can use code some thing like below.

create or replace procedure test….

is…

begin…

exception

when others then

dbms_output.put_line($$PLSQL_UNIT||’ caused error’);

end;

Using the PL/SQL Directive $$PLSQL_UNIT displays the pl/sql unit name (in this case test, is the name of the procedure ), when exception raised.

Another directive is $$PLSQL_LINE can be used to identify the line number in the program unit.

Oracle 12c database introduced new directives to display additional information as below.

$$PLSQL_OWNER – Owner of the object

$$PLSQL_TYPE - Whether it is procedure, function etc.

This addition would be very useful for developers to identify the owner of the program unit and type.

Read Full Post »

LISTAGG vs Stragg vs WM_CONCAT

One of the Oracle Database 11g features is to provide concatenated data for each group called LISTAGG. In prior versions we used different ways to achieve same results. Some of those are STRAGG and 10g introduced undocumented function WM_CONCAT.

Below I illustrated the differences in performance using these different function. I used same huge data set and same environment.

First look at ListAgg Vs WM_Concat

DEV> select id,listagg(paramlist,’;’) within group (order by paramlist)  as agg_data

2  from KP_AGG_TEST

3  group by id;

287485 rows selected.

Elapsed: 00:00:43.59

Execution Plan

———————————————————

Plan hash value: 2522236266

—————————————————————————————–

Id  | Operation          | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

—————————————————————————————–

0 | SELECT STATEMENT   |             |   287K|  7860K|       |  5030   (2)| 00:01:01 |

1 |  SORT GROUP BY     |             |   287K|  7860K|    30M|  5030   (2)| 00:01:01 |

2 |   TABLE ACCESS FULL| KP_AGG_TEST |   876K|    23M|       |  1137   (2)| 00:00:14 |

—————————————————————————————–

Statistics

———————————————————

67  recursive calls

20  db block gets

4077  consistent gets

8320  physical reads

0  redo size

11625136  bytes sent via SQL*Net to client

134407  bytes received via SQL*Net from client

19167  SQL*Net roundtrips to/from client

0  sorts (memory)

1  sorts (disk)

    287485  rows processed

DEV> select id,wm_concat(paramlist) from KP_AGG_TEST

2  group by id;

287485 rows selected.

Elapsed: 00:29:54.60

Execution Plan

———————————————————-

Plan hash value: 2522236266

——————————————————————————————

| Id  | Operation          | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

——————————————————————————————

|   0 | SELECT STATEMENT   |             |   287K|  7860K|       |  5030   (2)| 00:01:01 |

|   1 |  SORT GROUP BY     |             |   287K|  7860K|    30M|  5030   (2)| 00:01:01 |

|   2 |   TABLE ACCESS FULL| KP_AGG_TEST |   876K|    23M|       |  1137   (2)| 00:00:14 |

——————————————————————————————

Statistics

———————————————————-

51  recursive calls

3449842  db block gets

323291  consistent gets

6328  physical reads

0  redo size

103733023  bytes sent via SQL*Net to client

41398092  bytes received via SQL*Net from client

862457  SQL*Net roundtrips to/from client

0  sorts (memory)

1  sorts (disk)

287485  rows processed

As you can see, WM_CONCAT took longer than List Agg.

Now let’s look at LISTAGG vs STRAGG

Query 1: With below ListAgg query, aggregated data is returned in the order.

DEV> select id,listagg(paramlist,’;’) within group (order by paramlist)  as agg_data

  2  from KP_AGG_TEST

  3  GROUP BY ID;

287485 rows selected.

Elapsed: 00:00:44.95

Execution Plan

———————————————————-

Plan hash value: 2522236266

——————————————————————————————

| Id  | Operation          | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

——————————————————————————————

|   0 | SELECT STATEMENT   |             |   287K|  7860K|       |  5030   (2)| 00:01:01 |

|   1 |  SORT GROUP BY     |             |   287K|  7860K|    30M|  5030   (2)| 00:01:01 |

|   2 |   TABLE ACCESS FULL| KP_AGG_TEST |   876K|    23M|       |  1137   (2)| 00:00:14 |

——————————————————————————————

Statistics

———————————————————-

         67  recursive calls

         30  db block gets

       4077  consistent gets

      13152  physical reads

          0  redo size

   11644303  bytes sent via SQL*Net to client

     134406  bytes received via SQL*Net from client

      19167  SQL*Net roundtrips to/from client

          0  sorts (memory)

          1  sorts (disk)

     287485  rows processed

 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Query 2: Note that below STRAGG Query doesn’t return aggregated data in the order. Per returned data, this is little diff from listagg

 Dev> select id, stragg(paramlist) from kp_agg_test

  2  group by id;

287485 rows selected.

Elapsed: 00:02:37.82

Execution Plan

———————————————————-

Plan hash value: 2522236266

——————————————————————————————

| Id  | Operation          | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

——————————————————————————————

|   0 | SELECT STATEMENT   |             |   287K|  7860K|       |  5030   (2)| 00:01:01 |

|   1 |  SORT GROUP BY     |             |   287K|  7860K|    30M|  5030   (2)| 00:01:01 |

|   2 |   TABLE ACCESS FULL| KP_AGG_TEST |   876K|    23M|       |  1137   (2)| 00:00:14 |

——————————————————————————————

Statistics

———————————————————-

     287538  recursive calls

         22  db block gets

    1786494  consistent gets

       6341  physical reads

          0  redo size

   18532148  bytes sent via SQL*Net to client

     134406  bytes received via SQL*Net from client

      19167  SQL*Net roundtrips to/from client

          0  sorts (memory)

          1  sorts (disk)

     287485  rows processed

 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Query 3:Below query returns aggregated data in the order, same as listagg.

 

10-NOV-11 LV_DEV@GALVD501> select id, stragg_order_by_item(paramlist) from kp_agg_test

  2  group by id;

287485 rows selected.

Elapsed: 00:02:46.53

Execution Plan

———————————————————-

Plan hash value:2522236266

——————————————————————————————

| Id  | Operation          | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

——————————————————————————————

|   0 | SELECT STATEMENT   |             |   287K|  7860K|       |  5030   (2)| 00:01:01 |

|   1 |  SORT GROUP BY     |             |   287K|  7860K|    30M|  5030   (2)| 00:01:01 |

|   2 |   TABLE ACCESS FULL| KP_AGG_TEST |   876K|    23M|       |  1137   (2)| 00:00:14 |

——————————————————————————————

Statistics

———————————————————-

     287539  recursive calls

         30  db block gets

    1786488  consistent gets

       9938  physical reads

          0  redo size

   11904394  bytes sent via SQL*Net to client

     134406  bytes received via SQL*Net from client

      19167  SQL*Net roundtrips to/from client

     287485  sorts (memory)

          1  sorts (disk)

     287485  rows processed

 

Results Comparision

 

Query 1: Elapsed time is less compare to 2 and 3 below

Query 2:

–   Elapsed time is high

–   Recursive calls, consistent gets high

–   CPU/Cost is same as listagg.

–   High Bytes sent via SQL*Net

– Result set was not in the order as Query 1

 

Query 3: 

 

–   Elapsed time is high

–   Recursive calls, consistent gets high

–   CPU/Cost is same as listagg.

–   High Bytes sent via SQL*Net

– Result set was same as Query 1

 

Conclusion:

Avoid using WM_CONCAT, which is moreover not documented.

If you are using stragg, you can switch them to LISTAGG. It is difficult to change lot of queries that are already present and at atleast  start using LISTAGG over STRAGG. LISTAGG provides options like ordering, customized delimiter.

 

 

 

Read Full Post »