Datafile HWM

Cleaning up "joins gone wild" application bug. First cleaned up a mess that consumed a lot of space in tablespaces. After that had to move objects with high block_id values so the datafile size may be redused. To avoid hitting the error ORA-03297: file contains used data beyond requested RESIZE value. Alter table move may be used only the objects are not used. If you need to do this as an online operation use DBMS_REDEFINITION.

Generators I used for tablespaces containing only tables.

SELECT file_id,'alter table '||owner||'.'||segment_name||' move'||
   case when segment_type='TABLE PARTITION' then ' PARTITION '||partition_name
        when segment_type='TABLE SUBPARTITION' then ' SUBPARTITION '||partition_name 
    end ||';' sq, 
MAX(BLOCK_ID) max_block_id,
SEGMENT_NAME, partition_name, SEGMENT_TYPE
  FROM DBA_EXTENTS
 WHERE FILE_ID in (4,5,6,7,8)
 group by file_id,owner,SEGMENT_NAME, partition_name, SEGMENT_TYPE
 having MAX(BLOCK_ID) > 100000
 order by file_id,max_block_id desc
;

select 'alter database datafile '||file_id||' resize '||(ceil(max(block_id)*db_block_size/1024/1024/1024))||'G;'
  from dba_extents, (
    select value db_block_size 
      from v$parameter 
     where name='db_block_size' 
     )
 where FILE_ID in (4,5,6,7,8)
 group by db_block_size,file_id
 order by 1
;

Indexes went broken.

select 'alter index '||owner||'.'||index_name||' rebuild;' from all_indexes where status = 'UNUSABLE' order by 1;

select 'alter index '||index_owner||'.'||index_name||' rebuild partition '||partition_name||';' from all_ind_partitions where status = 'UNUSABLE' order by 1;

select 'alter index '||index_owner||'.'||index_name||' rebuild subpartition '||partition_name||';' from all_ind_subpartitions where status = 'UNUSABLE' order by 1;

Tablespaces including indexes.

SELECT distinct 'alter index '||owner||'.'||segment_name||' rebuild'||
   case when segment_type='INDEX PARTITION' then ' PARTITION '||partition_name
        when segment_type='INDEX SUBPARTITION' then ' SUBPARTITION '||partition_name 
    end ||';' sq, 
MAX(BLOCK_ID) max_block_id
  FROM DBA_EXTENTS
 WHERE FILE_ID in (9,10,11,12,13)
 group by file_id,owner,SEGMENT_NAME, partition_name, SEGMENT_TYPE
 having MAX(BLOCK_ID) > 100000
 order by max_block_id desc
;

And after that datafile resize.

Anoying Toad

Two anoying features of Toad. Use SQLPLUS or SQL Developer instead.

create table pa(p number constraint pa_pk primary key) 
partition by hash(p) 
partitions 2;

select partition_name from user_tab_partitions where table_name = 'PA';

SYS_P61
SYS_P62

Truncating a partition with Toad gives an error.

alter table pa truncate partition SYS_P61;

ORA-14006: invalid partition name

Modifying index visibility with Toad gives an error.

alter index pa_pk invisible;

ORA-14141: ALTER INDEX VISIBLE|INVISIBLE may not be combined with other operations

alter index pa_pk visible;

ORA-14141: ALTER INDEX VISIBLE|INVISIBLE may not be combined with other operations

Just get rid of the semi colon and you are able to use these commands with Toad.

alter index pa_pk visible

A dark side of Easter

It was an Easter a while ago. I had a bottle of Finnish porter beer. It was made by Laitilan breweries. My mother in law got some of it and made it clear that it tasted like mämmi. She put some vanilla sauce in the class and the taste was, well similar like mämmi with vanilla sauce. This year Laitilan breweries has a bottle that has a yellow lable saying Mämmi on it. They have actually made beer out of mämmi. Well if you do not know what mämmi is take alook of something else from Finnish kitchen called Kalakukko. They make that mainly in Kuopio. I had an exiting evening in Hakametsä. Ice hockey arena in Tampere. My favourite ice hockey team Tappara just won Kalpa - a team from Kuopio.

Putting things together that do not actually have nothing to do with each other make sometimes wheels running. Ice hockey and mämmi have nothing in common like vanilla sauce and Kuopio. During last year I have had several occasions when the ideas from spatial advisor have came to the rescue. There have been several cases when I have been dealing with query performance problems with temporal data. Well spatial information is more close to temporal than Kalpa is to Laitila. These ideas have come to the rescue several times when the number count in tables have increased from a million to ten or hundred million. Changing a traditional b-tree index to a function based locator index and a query where predicate to use that has helped the response time to be usable. Joining similar table with greater than or between comparison has also been a troublesome to deal with. Using those function based locator indexes and sdo_join have helped a lot.

Lets have a simple example not from those real life experiences like I did not reveal earlier. Lets copy the base situation from sum over time writing. Notable here is that the locator indexing is available also in free XE version and also SE and EE. The basic idea for function based locator index may be expressed like

DROP TABLE T CASCADE CONSTRAINTS PURGE;

DROP FUNCTION TF;


CREATE TABLE T (FRO DATE, TIL DATE, N NUMBER);

INSERT INTO T VALUES (TO_DATE('09.04.2009','dd.mm.yyyy'), TO_DATE('10.04.2009','dd.mm.yyyy'), 1);

INSERT INTO T VALUES (TO_DATE('10.04.2009','dd.mm.yyyy'), TO_DATE('12.04.2009','dd.mm.yyyy'), 2);

INSERT INTO T VALUES (TO_DATE('11.04.2009','dd.mm.yyyy'), TO_DATE('14.04.2009','dd.mm.yyyy'), 3);

COMMIT;

Query performing not with satisfactory response time.

SELECT * 
  FROM T 
 WHERE TIL >= TO_DATE('11.04.2009','dd.mm.yyyy') 
   AND FRO <= TO_DATE('14.04.2009','dd.mm.yyyy')
;

10.04.2009 12.04.2009 2
11.04.2009 14.04.2009 3

CREATE OR REPLACE FUNCTION TF(FRO DATE, TIL DATE) RETURN SDO_GEOMETRY deterministic as 
BEGIN
RETURN MDSYS.SDO_GEOMETRY(2002,NULL, NULL, SDO_ELEM_INFO_ARRAY (1,2,1),
    SDO_ORDINATE_ARRAY(to_number(to_char(FRO,'j')),0,to_number(to_char(TIL,'j')),0));
END;
/

SELECT TO_CHAR(to_date('19000101','yyyymmdd'),'J'),TO_CHAR(to_date('22000101','yyyymmdd'),'J') FROM dual;

--2415021 2524594

DELETE FROM USER_SDO_GEOM_METADATA WHERE TABLE_NAME = 'T';

INSERT INTO USER_SDO_GEOM_METADATA(TABLE_NAME,COLUMN_NAME,DIMINFO)
  VALUES (
  'T',
  'RAFU.TF(FRO,TIL)',
  SDO_DIM_ARRAY(
    SDO_DIM_ELEMENT('X', 2415021, 2524594, 0.5),
    SDO_DIM_ELEMENT('Y', 0, 0, 0.5)
     )
  )
;

COMMIT;

CREATE INDEX T_LOCATOR_IDX ON T(TF(FRO,TIL))
  INDEXTYPE IS mdsys.spatial_index;

SELECT * 
  FROM T 
 WHERE SDO_FILTER(TF(FRO,TIL),
                  TF(TO_DATE('11.04.2009','dd.mm.yyyy')
                    ,TO_DATE('14.04.2009','dd.mm.yyyy'))
                 ) = 'TRUE'
;

10.04.2009 12.04.2009 2
11.04.2009 14.04.2009 3

Just try with your own temporal data and PlanViz and compare the a-rows inside your query.

An another thing that I sayed in sum over time writing was "I like to see continous pair stored as open ended". It is just one thing to deal with this approach. SQL standard by the way says that the end in time interval should not be included in the interval. But if the end is the same than the start moment then the interval is presenting the one spot in the timeline. So in that case the end is not excluding.

Yet another thing about timelines and my earlier postings. Last year I wrote about some ideas about not overlapping daily. The idea presented in this post might near to the answer missing in that post. But it is not possible to create a domain index as a unique.

By the way Mämmi beer with vanilla sauce got called today white russian by my mother in law. Wonder what might Dude in Big Lebowski say about that.

Visualizing plans grants

A while ago I post about to use plan visualization by Tanel Poder. That has been a tool worth using. Thou that should be a tool also to other developers than DBAs. A-Rows and A-Time are such valuable information about the execution bottlenecs inside SQL execution.

How to get it working without dba rights.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------

User has no SELECT privilege on V$SESSION

It is not possible to grant select rights straight to v$ views as Coscan has writen.

grant select on v_$session to rafu;

And a try

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------

User has no SELECT privilege on V$SQL_PLAN_STATISTICS_ALL

Giving a grant

grant select on v_$sql_plan_statistics_all to rafu;

And after that no success. The same output.
11.2 documentation says that to use DBMS_XPLAN.DISPLAY_CURSOR the calling user must have SELECT privilege on the fixed views V$SQL_PLAN_STATISTICS_ALL, V$SQL and V$SQL_PLAN views.

grant select on v_$sql_plan to rafu;

grant select on v_$sql to rafu;

And we are there. User rafu may use DBMS_XPLAN.DISPLAY_CURSOR with sql_id=>null parameter. Also grant alter session to avoid the need to use gather_plan_statistics hint might be recommended. That is how you enable ALTER SESSION SET statistics_level = ALL;

grant alter session to rafu;

Also giving these grants through a role to developer users is worth considering.

Pivoting EAV

If you are responsible for designing a data model and just consider to invent again and create this fine generic entity attribute value structure, maybe you should consider attending some teaching about the issue. For example some available soon by C.J. Date in and near Finland.

Well maybe you have a EAV model that you have to deal with. Example

SQL> create table eav as
  2  select 1 e, 'first' a, 'Timo' v from dual union all
  3  select 1 e, 'last' a, 'Raitalaakso' v  from dual union all
  4  select 1 e, 'nic' a, 'Rafu' v  from dual union all
  5  select 2 e, 'first' a, 'John' v  from dual union all
  6  select 2 e, 'last' a, 'Doe' v from dual
  7  ;

Table created.

SQL> select * from eav;

         E A     V
---------- ----- -----------
         1 first Timo
         1 last  Raitalaakso
         1 nic   Rafu
         2 first John
         2 last  Doe

You should not query it in a basic case using joins. Most possibly you have tens of joins to the same table.

SQL> select la.e, fi.v firs, la.v las
  2    from eav la, eav fi
  3   where la.e=fi.e
  4     and fi.a='first'
  5     and la.a='last'
  6  ;

         E FIRS        LAS
---------- ----------- -----------
         1 Timo        Raitalaakso
         2 John        Doe

It is a pivot you want to do.

SQL> select e, firs, las
  2    from eav
  3    pivot (max(v) for a in ('first' as firs, 'last' as las))
  4  ;

         E FIRS        LAS
---------- ----------- -----------
         1 Timo        Raitalaakso
         2 John        Doe

With the pivot you get the nullable columns also easier without filtering out the whole entity

SQL> select e, firs, las, ni
  2    from eav
  3    pivot (max(v) for a in ('first' as firs, 'last' as las, 'nic' as ni))
  4  ;

         E FIRS        LAS         NI
---------- ----------- ----------- -----------
         1 Timo        Raitalaakso Rafu
         2 John        Doe

Maybe you do not have 11g features available.

SQL> select e
  2       , max(case when a = 'first' then v end) firs
  3       , max(case when a = 'last' then v end) las
  4    from eav
  5   group by e
  6  ;

         E FIRS        LAS
---------- ----------- -----------
         1 Timo        Raitalaakso
         2 John        Doe

And the best thing to do with it might be.

SQL> drop table eav purge;

Table dropped.

Parallel query distribution methods

Figuring out should I recommend parallelizing materialized view refresh for several mvs or use parallel partition wise joins. Found valuable help for the issue just yesterday by Tony Hasler. Will be using both. Unfortunately no blog writing coming up about the issue.

not in null countdown

Just a reminder about not in and nulls. Maybe consider using not exists or anti join if any of the columns in not in list may be null. Or maybe one of the following might be the result you want.

SQL> create table nm as
  2  with le as (select level ev from dual connect by level<4)
  3  select l.ev e,e.ev v from le l, le e;

Table created.

SQL> select count(*) from nm;

  COUNT(*)
----------
         9

SQL> select count(*) from nm where (e,v) not in ((1,1));

  COUNT(*)
----------
         8

SQL> select count(*) from nm where (e,v) not in ((1,1),(2,2));

  COUNT(*)
----------
         7

SQL> select count(*) from nm where (e,v) not in ((1,null));

  COUNT(*)
----------
         6

SQL> select count(*) from nm where (e,v) not in ((1,1),(2,null));

  COUNT(*)
----------
         5

SQL> select count(*) from nm where (e,v) not in ((null,1),(2,null));

  COUNT(*)
----------
         4

SQL> select count(*) from nm where (e,v) not in ((1,null),(2,null));

  COUNT(*)
----------
         3

SQL> select count(*) from nm where (e,v) not in ((1,null),(2,null),(3,3));

  COUNT(*)
----------
         2

SQL> select count(*) from nm where (e,v) not in ((null,1),(null,2),(1,3),(2,3));

  COUNT(*)
----------
         1

SQL> select count(*) from nm where (e,v) not in ((null,null),(1,1));

  COUNT(*)
----------
         0

SQL> select count(*)
  2    from nm m
  3   where not exists (select n
  4                       from (select null n from dual) d
  5                      where m.v = d.n
  6                        and m.e = d.n);

  COUNT(*)
----------
         9

SQL> select count(*)
  2    from nm m
  3     left outer join (select 10 e, null n from dual) d
  4       on m.e=d.e
  5  where d.n is null;

  COUNT(*)
----------
         9