Rafu on db: 2014

2014-11-11

ASH Mining Slow Queries

"Database is slow." It was fast three days ago. We know the problem table but no queries are informed to the dba. Luckily we have diagnostics pack purchased. Time to start finding the slowest queries touching the table.

with sqid as (
  select /*+materialize*/
         distinct sql_id 
    from dba_hist_sql_plan p, dba_tables t 
   where t.owner = :table_owner
     and t.table_name = :table_name
     and (p.object_owner,p.object_name) in (
            select t.owner,t.table_name 
              from dual
            union all
            select owner,index_name
              from dba_indexes i
             where i.table_owner = t.owner 
               and i.table_name = t.table_name
            )
)
select /*+leading(s)*/ 
       trunc(h.sample_time) dt
     , h.sql_id
     , max(h.sample_time-h.sql_exec_start) dur
     , min(h.sample_time) mins
     , max(h.sample_time) maxs
     , count(distinct h.sql_plan_hash_value) cntpln
     , collect(distinct h.sql_plan_hash_value) plns
     , count(distinct h.sql_exec_id) cntexec
     , count(distinct h.session_id) cntsess
     , collect(distinct h.event) events
     , (select dbms_lob.substr(t.sql_text,2000) 
          from dba_hist_sqltext t 
         where t.sql_id = h.sql_id) txt
  from dba_hist_active_sess_history h, sqid s
 where sample_time > trunc(sysdate)-7 
   and h.sql_id = s.sql_id
group by trunc(h.sample_time),h.sql_id
order by max(dur)over(partition by h.sql_id) desc, dt desc
;

2014-08-29

DBMS_STATS.GET_PREFS for a Table

select t.owner
     , t.table_name
     , p.column_value pname
     , dbms_stats.get_prefs(p.column_value,t.owner,t.table_name) pvalue
     , case when dbms_stats.get_prefs(p.column_value,t.owner,t.table_name) != dbms_stats.get_prefs(p.column_value) then dbms_stats.get_prefs(p.column_value) end pvaluedefault
 from dba_tables t,
 table(sys.odcivarchar2list('AUTOSTATS_TARGET','CASCADE','DEGREE','ESTIMATE_PERCENT','METHOD_OPT','NO_INVALIDATE','GRANULARITY','PUBLISH','INCREMENTAL','INCREMENTAL_STALENESS','INCREMENTAL_LEVEL','STALE_PERCENT','TABLE_CACHED_BLOCKS','OPTIONS')) p
 where owner = :owner
   and table_name = :table_name
;

RAFU P AUTOSTATS_TARGET AUTO 
RAFU P CASCADE   DBMS_STATS.AUTO_CASCADE 
RAFU P DEGREE   NULL 
RAFU P ESTIMATE_PERCENT DBMS_STATS.AUTO_SAMPLE_SIZE 
RAFU P METHOD_OPT  FOR ALL COLUMNS SIZE AUTO 
RAFU P NO_INVALIDATE  DBMS_STATS.AUTO_INVALIDATE 
RAFU P GRANULARITY  AUTO 
RAFU P PUBLISH   TRUE 
RAFU P INCREMENTAL  TRUE     FALSE
RAFU P STALE_PERCENT  10

2014-04-11

Gouping consecutive dates

I just read about Tabibitosan 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

Rafu on db