Rafu on db

2014-04-11

Gouping consecutive dates

I just read about Tabitosan method on Oracle PL/SQL forum.
Rob Van Wijk has written also about the method recently.

Here is just another example of it.
Something happening on random times. Need to know sequences of dates. Need to get a grouping information on rows, if there exist rows on yesterday or tomorrow.

with dates as (
select TIMESTAMP'2014-04-15 01:00:00' dt from dual union all
select TIMESTAMP'2014-04-15 02:00:00' dt from dual union all
select TIMESTAMP'2014-04-15 03:00:00' dt from dual union all
select TIMESTAMP'2014-04-16 01:00:00' dt from dual union all
select TIMESTAMP'2014-04-17 02:00:00' dt from dual union all
select TIMESTAMP'2014-04-17 03:00:00' dt from dual union all
select TIMESTAMP'2014-04-19 01:00:00' dt from dual union all
select TIMESTAMP'2014-04-19 02:00:00' dt from dual union all
select TIMESTAMP'2014-04-21 02:00:00' dt from dual union all
select TIMESTAMP'2014-04-21 03:00:00' dt from dual
)
select dt,TRUNC(dt)-DENSE_RANK()over(order by trunc(dt)) grp
from dates
;

2014-04-15 01:00:00    2014-04-14
2014-04-15 02:00:00    2014-04-14
2014-04-15 03:00:00    2014-04-14
2014-04-16 01:00:00    2014-04-14
2014-04-17 02:00:00    2014-04-14
2014-04-17 03:00:00    2014-04-14
2014-04-19 01:00:00    2014-04-15
2014-04-19 02:00:00    2014-04-15
2014-04-21 02:00:00    2014-04-16
2014-04-21 03:00:00    2014-04-16

2014-03-07

Variable in-list cardinality


Collect the statistics in my earlier varying in-list example inlist_test table and you might hit the following problem.


select * from table(SYS.odcivarchar2list('a','b'));

select * from table(SYS.odcinumberlist(1,2,3));

----------------------------------------------------------------------------------------------
| Id  | Operation                             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |      |  8168 | 16336 |    29   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR CONSTRUCTOR FETCH|      |  8168 | 16336 |    29   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

The default 8168 cardinality is the root cause for some performance problems. Adrian Billington to the rescue.  
"The Extensible Optimiser feature is the most flexible method to use at this stage and is usable in all versions of 10g. Using this, we have devised a good alternative to the CARDINALITY hint for pipelined functions and also created a generic wrapper for small collections that are typically used in variable in-list queries."

Putting the different lists to a collection wrapper package.


CREATE OR REPLACE package coll
 as 
  FUNCTION wrapper(
                  p_collection IN SYS.odcinumberlist
                  ) RETURN SYS.odcinumberlist;
  FUNCTION wrapper(
                  p_collection IN SYS.odcivarchar2list
                  ) RETURN SYS.odcivarchar2list;
  FUNCTION wrapper(
                  p_collection IN SYS.odcidatelist
                  ) RETURN SYS.odcidatelist;
END coll;
/

CREATE OR REPLACE package body coll

 as 
  FUNCTION wrapper(
                  p_collection IN SYS.odcinumberlist
                  ) RETURN SYS.odcinumberlist as
  BEGIN
     RETURN p_collection;
  END wrapper;
  FUNCTION wrapper(
                  p_collection IN SYS.odcivarchar2list
                  ) RETURN SYS.odcivarchar2list as
  BEGIN
     RETURN p_collection;
  END wrapper;
  FUNCTION wrapper(
                  p_collection IN SYS.odcidatelist
                  ) RETURN SYS.odcidatelist as
  BEGIN
     RETURN p_collection;
  END wrapper;
END;
/

CREATE OR REPLACE TYPE coll_wrapper_ot AS OBJECT (

     dummy_attribute NUMBER,
     STATIC FUNCTION ODCIGetInterfaces (
                     p_interfaces OUT SYS.ODCIObjectList
                  ) RETURN NUMBER,
     STATIC FUNCTION ODCIStatsTableFunction (
                     p_function   IN  SYS.ODCIFuncInfo,
                     p_stats      OUT SYS.ODCITabFuncStats,
                     p_args       IN  SYS.ODCIArgDescList,
                     p_collection IN  SYS.odcinumberlist
                     ) RETURN NUMBER,
     STATIC FUNCTION ODCIStatsTableFunction (
                     p_function   IN  SYS.ODCIFuncInfo,
                     p_stats      OUT SYS.ODCITabFuncStats,
                     p_args       IN  SYS.ODCIArgDescList,
                     p_collection IN  SYS.odcivarchar2list
                     ) RETURN NUMBER,
     STATIC FUNCTION ODCIStatsTableFunction (
                     p_function   IN  SYS.ODCIFuncInfo,
                     p_stats      OUT SYS.ODCITabFuncStats,
                     p_args       IN  SYS.ODCIArgDescList,
                     p_collection IN  SYS.odcidatelist
                     ) RETURN NUMBER
  );
/


CREATE OR REPLACE TYPE BODY coll_wrapper_ot AS

     STATIC FUNCTION ODCIGetInterfaces (
                     p_interfaces OUT SYS.ODCIObjectList
                     ) RETURN NUMBER IS
     BEGIN
        p_interfaces := SYS.ODCIObjectList(
                           SYS.ODCIObject ('SYS', 'ODCISTATS2')
                           );
        RETURN ODCIConst.success;
     END ODCIGetInterfaces;
     STATIC FUNCTION ODCIStatsTableFunction (
                     p_function   IN  SYS.ODCIFuncInfo,
                     p_stats      OUT SYS.ODCITabFuncStats,
                     p_args       IN  SYS.ODCIArgDescList,
                     p_collection IN  SYS.odcinumberlist
                     ) RETURN NUMBER IS
     BEGIN
        p_stats := SYS.ODCITabFuncStats(p_collection.COUNT);
        RETURN ODCIConst.success;
     END ODCIStatsTableFunction;
     STATIC FUNCTION ODCIStatsTableFunction (
                     p_function   IN  SYS.ODCIFuncInfo,
                     p_stats      OUT SYS.ODCITabFuncStats,
                     p_args       IN  SYS.ODCIArgDescList,
                     p_collection IN  SYS.odcivarchar2list
                     ) RETURN NUMBER IS
     BEGIN
        p_stats := SYS.ODCITabFuncStats(p_collection.COUNT);
        RETURN ODCIConst.success;
     END ODCIStatsTableFunction;
     STATIC FUNCTION ODCIStatsTableFunction (
                     p_function   IN  SYS.ODCIFuncInfo,
                     p_stats      OUT SYS.ODCITabFuncStats,
                     p_args       IN  SYS.ODCIArgDescList,
                     p_collection IN  SYS.odcidatelist
                     ) RETURN NUMBER IS
     BEGIN
        p_stats := SYS.ODCITabFuncStats(p_collection.COUNT);
        RETURN ODCIConst.success;
     END ODCIStatsTableFunction;
  END;
/

--DISASSOCIATE STATISTICS FROM PACKAGES coll;


ASSOCIATE STATISTICS WITH PACKAGES coll USING coll_wrapper_ot;



select * from table(coll.wrapper(SYS.odcivarchar2list('a','b')));

---------------------------------------------------------------------------------------------
| Id  | Operation                         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |         |     2 |     4 |    29   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| WRAPPER |     2 |     4 |    29   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

select * from table(coll.wrapper(SYS.odcinumberlist(1,2,3)));

---------------------------------------------------------------------------------------------
| Id  | Operation                         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |         |     3 |     6 |    29   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| WRAPPER |     3 |     6 |    29   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

select * from table(coll.wrapper(SYS.odcidatelist(date'2014-03-07',date'2014-03-08',date'2014-03-09')));

---------------------------------------------------------------------------------------------
| Id  | Operation                         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |         |     3 |     6 |    29   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| WRAPPER |     3 |     6 |    29   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------


Seems like just the thing desired, but how about binds?


declare
 b sys.odcinumberlist := sys.odcinumberlist(1,2,3,4,5,6,7,8,9,1,2,4,5,7,8,9,0);
 ret number;
begin
 select /*+monitor*/ count(*) into ret from table(coll.wrapper(b));
 dbms_output.put_line(ret);
end;
/

=============================================================================================================================================
| Id |              Operation               |  Name   |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Activity | Activity Detail |
|    |                                      |         | (Estim) |      | Active(s) | Active |       | (Actual) |   (%)    |   (# samples)   |
=============================================================================================================================================
|  0 | SELECT STATEMENT                     |         |         |      |         1 |     +0 |     1 |        1 |          |                 |
|  1 |   SORT AGGREGATE                     |         |       1 |      |         1 |     +0 |     1 |        1 |          |                 |
|  2 |    COLLECTION ITERATOR PICKLER FETCH | WRAPPER |       1 |   29 |         1 |     +0 |     1 |       17 |          |                 |
=============================================================================================================================================


Got unlucky. Esitmated is not 8168 but one. The second documented alternative was to use DYNAMIC_SAMPLING hint. 

declare
 b sys.odcinumberlist := sys.odcinumberlist(1,2,3,4,5,6,7,8,9,1,2,4,5,7,8,9,0);
 ret number;
begin
 select /*+ monitor DYNAMIC_SAMPLING(2)*/ count(*) into ret from table(b);
 dbms_output.put_line(ret);
end;
/

SQL Plan Monitoring Details (Plan Hash Value=3309076612)
==========================================================================================================================================
| Id |              Operation               | Name |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Activity | Activity Detail |
|    |                                      |      | (Estim) |      | Active(s) | Active |       | (Actual) |   (%)    |   (# samples)   |
==========================================================================================================================================
|  0 | SELECT STATEMENT                     |      |         |      |         1 |     +0 |     1 |        1 |          |                 |
|  1 |   SORT AGGREGATE                     |      |       1 |      |         1 |     +0 |     1 |        1 |          |                 |
|  2 |    COLLECTION ITERATOR PICKLER FETCH |      |      17 |   11 |         1 |     +0 |     1 |       17 |          |                 |
==========================================================================================================================================

That seems to work also with the binds. My newly created inlist_test table did not have statistics gathered and so the DYNAMIC_SAMPLING is used to the statement in varying in list. And so the different plans are generated for different collection sizes.

2014-01-27

ORA-00935


select owner,sum(bytes) sumb 
  from dba_segments 
 group by OWNER
 order by sum(bytes) desc
;

ORA-00935: group function is nested too deeply

Oracle Support Document 18529.1


Error:  ORA 935  
Text:   group function is nested too deeply 
-------------------------------------------------------------------------------
Cause:  This is an internal error message not usually issued.
Action: Contact customer support.


The fix for my query is not to use the aggregate in order by:

select owner,sum(bytes) sumb
  from dba_segments 
 group by owner
 order by sumb 
;

2014-01-02

Over million interval partitions. --not

Here is an yet another SQL tale why you should _not_ store dates as a number.

--drop table F_INVOICE_L;

create table F_INVOICE_L(
       INVOICING_DATE_WID NUMBER (8)
      ,product_wid number(20)) 
partition by range (INVOICING_DATE_WID) 
interval (1) 
subpartition by hash (product_wid) 
subpartitions 32
(PARTITION P_MINPART VALUES LESS THAN (20100101))
;

insert into F_INVOICE_L values (20140101,1);

ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions

select count(*) from dba_tab_partitions where table_name = 'F_INVOICE_L';

1


Waat?

There should be room for 1048575 partitions. I have only one, the P_MINPART initial one. Well ok I have 32 subpartitions. There should be room for 32767 partitions.

select 1048575/32 + 20100101 from dual;

20132868,96875

We ran out of placeholders for numbers. If we would have used date datatype we would have some time still to go with this partitioning method.

select 1048575/32 + to_date('20100101','yyyymmdd') from dual;

2099-09-18 


Quick and dirty fix

alter table F_INVOICE_L set interval (100);

insert into F_INVOICE_L values (20140101,1);

1 row inserted.

rollback;

select high_value from user_tab_partitions where table_name = 'F_INVOICE_L';

20100101
20140201



And setup the timebomb again to be triggered at new years day 2018.

alter table F_INVOICE_L set interval (1);

select 1048575/32 + 20140201 from dual;

20172968,96875

insert into F_INVOICE_L values (20170101,1);

1 rows inserted.

insert into F_INVOICE_L values (20180101,1);

SQL Error: ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions

2013-12-20

DUMP to HEX


DUMP function gives decimal presentation of a column contents bits.

select ch,dump(ch) dm 
from (
select 'DUMP to DEC' ch 
from dual
)
;


CH DM
DUMP to DEC Typ=96 Len=11: 68,85,77,80,32,116,111,32,68,69,67

Sometimes hexadecimal presentation would be nice

select ch
     , listagg(to_char((column_value).getnumberval(),'XX'))
        within group (order by rownum) hex 
 from (
  select ch,dump(ch) dm 
   from (
    select 'DUMP to HEX' ch 
      from dual
    )
  ) n
  ,xmltable(substr(n.dm,instr(n.dm,':')+2))
;

CH HEX
DUMP to HEX  44 55 4D 50 20 74 6F 20 48 45 58

Or maybe it is easier just to use the documented second parameter of dump function.

select ch,dump(ch,1016) dm 
 from (
  select 'DUMP to HEX' ch 
  from dual
 )
;


DUMP to HEX    Typ=96 Len=11 CharacterSet=AL32UTF8: 44,55,4d,50,20,74,6f,20,48,45,58

2013-12-02

Is My Index Used

I am in Manchester. The UKOUG tech13 seminar super Sunday information overload gone. Three days still left. I got finally to meet Kyle Hailey. The man who visualized the ash. Even he and Doug Burns could not find the SQL query that has created the lock in a blocking session. Another a bit similar question that is often asked. Is an index used. You could set up monitoring on an index.

ALTER INDEX index_name_here MONITORING USAGE;

And after a while you can browse USED column of the V$OBJECT_USAGE view.
It tels is your undex been used. But it does not tell if the index is used wisely. ASH to the rescue. Possibly increase the time ash is stored. And here is a query that shows the clauses that have been using the index.

select sql_id,TO_CHAR(substr(sql_text,1,4000)) txt
  from dba_hist_sqltext where sql_id in ( 
  select sql_id 
    from dba_hist_sql_plan 
   where object_name = :index_name)
order by txt
;

If no index usage is noticed. The index is a possible candidate for dropping away. The case in V$OBJECT_USAGE.

But it might be so that the index is used in a non wisely manner. From there on you can interpret the plans. It might be that the full table scan would perform better.

And going to 12c there becomes the adaptivity of a cursor execution that will end up in the plan even it executes better in other execution paths. This was covered yesterday quite nicely by Nicolas Jardot. So for 12c the is the index used query here should be attached to dba_hist_active_sess_history. Future work to be done.

Before dropping consider making the index invisible for awhile.


2013-04-12

Tables used

Having a view stack? Trying to find out tables a view is using?
 select d.referenced_name table_name, count(*) times, sum(count(*))over() tables
   from user_dependencies d 
  where d.referenced_type = 'TABLE'
  start with d.name = :name 
connect by   d.name = prior d.referenced_name 
        and  d.type = prior d.referenced_type
 group by d.referenced_name
 order by d.referenced_name
;
Not only restricted to views also eg. procedures have dependencies. Just give the root object name as a parameter to the query and you get the dependent tables out.

About Me

My Photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. I have received Oracle ACE nomination. My main focus is on projects involving Oracle database. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.