Ask The Real Tom

What is vertical partitioning?

Vertical partitioning is if you partition (physicaly separate) by columns (not by rows).

How can I implement vertical partitions?

There are basicaly two methods.

Method 1: With a Index

Demo Script:

connect test8/test8

create table t1 (
        id0 number,
        id1 number,
        id2 number,
        id3 number,
        id4 number
);

insert into t1 select 1,2,3,4,5 from dual connect by level <= 100000;
commit;

explain plan for select id0,id1 from t1;
select * from table(dbms_xplan.display);

create index x1 on t1 (id0,id1);
alter table t1 modify (id0 not null);
alter table t1 modify (id1 not null);

exec DBMS_STATS.GATHER_table_STATS (OWNNAME => 'TEST8', TABNAME => 'T1',cascade=>TRUE );

explain plan for select id0,id1 from t1 t;

select * from table(dbms_xplan.display);

Output:

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 95936 |  2435K|    84   (2)| 00:00:02 |
|   1 |  TABLE ACCESS FULL| T1   | 95936 |  2435K|    84   (2)| 00:00:02 |
--------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3702471258

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      | 99418 |   582K|    55   (2)| 00:00:01 |
|   1 |  INDEX FAST FULL SCAN| X1   | 99418 |   582K|    55   (2)| 00:00:01 |
-----------------------------------------------------------------------------

Note:
It is essential, that all the columns in the index are not NULL. Since this garanties, that all rows are in the index.

Method 2: Split a table to tow tables with a 1:1 relation ship and access them by view

Demo Script:

create table t1 (
        id0 number,
        id1 number,
        id2 number
);

create table t2 (
        id0 number,
        id3 number,
        id4 number
);

create view v1 as select t1.id0,id1,id2,id4 from t1,t2 where t1.id0=t2.id0;

insert into t1 select rownum,2,3 from dual connect by level <= 100000;
insert into t2 select rownum,4,5 from dual connect by level <= 100000;
commit;
explain plan for select id0,id1 from v1;
select * from table(dbms_xplan.display);

ALTER TABLE t1 add CONSTRAINT p1 PRIMARY KEY (id0);
ALTER TABLE t2 add CONSTRAINT p2 PRIMARY KEY (id0);

exec DBMS_STATS.GATHER_table_STATS (OWNNAME => 'TEST8', TABNAME => 'T1',cascade=>TRUE );
exec DBMS_STATS.GATHER_table_STATS (OWNNAME => 'TEST8', TABNAME => 'T2',cascade=>TRUE );

explain plan for select id0,id1 from t1 t;

select * from table(dbms_xplan.display);

Output:

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2959412835

-----------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 83107 |  3165K|       |   387   (2)| 00:00:05 |
|*  1 |  HASH JOIN         |      | 83107 |  3165K|  2544K|   387   (2)| 00:00:05 |
|   2 |   TABLE ACCESS FULL| T2   |   104K|  1322K|       |    56   (2)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T1   | 83107 |  2110K|       |    56   (2)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."ID0"="T2"."ID0")

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100K|   783K|    56   (2)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T1   |   100K|   783K|    56   (2)| 00:00:01 |
--------------------------------------------------------------------------

Here ist the first really working version of the online rebuild scirpt to resize index tablespaces.

CREATE GLOBAL TEMPORARY TABLE rim_dba_extents ON COMMIT  PRESERVE ROWS as select * from dba_extents where 1=2
/

create or replace procedure rim_rebuild_last_objects (p_object_type VARCHAR,p_tablespace_name VARCHAR)  as
begin
  execute immediate 'truncate table rim_dba_extents';
  insert into rim_dba_extents select * from dba_extents where
    owner not in ('SYS','SYSTEM')
    and tablespace_name not in ('SYSTEM','SYSAUX')
    and segment_type like p_object_type;
  for rec in (select distinct 'alter '||
      decode(segment_type,'INDEX','INDEX','INDEX PARTITION','INDEX','INDEX SUBPARTITION','INDEX',
                  'TABLE','TABLE','TABLE PARTITION','TABLE','TABLE SUBPARTITION','TABLE')
      ||' '||
      owner||'.'||segment_name||' '||
      decode(segment_type,'INDEX','rebuild ','INDEX PARTITION','rebuild partition','INDEX SUBPARTITION','rebuild subpartition',
       'TABLE','move','TABLE PARTITION','move partition','TABLE SUBPARTITION','move subpartition')
      ||' '||partition_name||' '||decode(substr(segment_type,1,5),'INDEX','online','TABLE',' update indexes','') cmd
    from rim_dba_extents
    where (file_id,block_id) in (select file_id,max(block_id) from rim_dba_extents
                                  where tablespace_name like p_tablespace_name
                                   group by file_id)
  )  loop
    dbms_output.put_line(rec.cmd||';');
    execute immediate rec.cmd;
  end loop;
  execute immediate 'truncate table rim_dba_extents';
end;
/
show errors;

set serveroutput on
exec rim_rebuild_last_objects('INDEX%','T_I_LIF%');

On Oracle 10g pga configuration has got very easy - maby too easy. If found many databases where the DBA just didn’t know about or did not care.

So here ist the tow minute drill to check your database.

First check you v$pgastats table. if you have a “over allocation count” of zero and a “cache hit percentage” of 100% (or very near 100%) your Database is ok.

SQL> select * from v$pgastat

NAME                                                                  VALUE UNIT
---------------------------------------------------------------- ---------- ------------
aggregate PGA target parameter                                   2147483648 bytes
aggregate PGA auto target                                        1426295808 bytes
global memory bound                                               214743040 bytes
total PGA inuse                                                   562710528 bytes
total PGA allocated                                               827747328 bytes
maximum PGA allocated                                            4815665152 bytes
total freeable PGA memory                                         161021952 bytes
process count                                                           252
max processes count                                                     276
PGA memory freed back to OS                                      1.2470E+13 bytes
total PGA used for auto workareas                                         0 bytes
maximum PGA used for auto workareas                              1011056640 bytes
total PGA used for manual workareas                                       0 bytes
maximum PGA used for manual workareas                               2150400 bytes
over allocation count                                               2589860
bytes processed                                                  3.7341E+13 bytes
extra bytes read/written                                         7.0942E+11 bytes
cache hit percentage                                                  98.13 percent
recompute count (total)                                             4812194

Why is this imporant?

If you have not enougth PGA temp space will be used to save stuff from the PGA (ex. for sorting). This cause dramatic perfomance impact.

If you have to resize it, the pga advisor view might help

 select * from V$PGA_TARGET_ADVICE;

Last week I postet my dirty notes to move objects from the end of a datafile.

Here is an update. Still dirty! But it works.

• the temporary table is nesseary for perfomance reasons (oracle bug)
• the script may fail for not table order index objects
• parameter 1: TABLE%, INDEX% or %
• parameter 2: tablespace name

CREATE GLOBAL TEMPORARY TABLE rim_dba_extents as select * from dba_extents where 1=2
-- ON COMMIT DELETE ROWS
/

create or replace procedure rim_rebuild_last_objects (p_object_type VARCHAR,p_tablespace_name VARCHAR)  as
begin
execute immediate 'truncate table rim_dba_extents';
insert into rim_dba_extents select * from dba_extents where
owner not in ('SYS','SYSTEM')
and tablespace_name not in ('SYSTEM','SYSAUX')
and segment_type like p_object_type;
for rec in (select 'alter '||
decode(segment_type,'INDEX','INDEX','INDEX PARTITION','INDEX','INDEX SUBPARTITION','INDEX',
'TABLE','TABLE','TABLE PARTITION','TABLE','TABLE SUBPARTITION','TABLE')
||' '||
owner||'.'||segment_name||' '||
decode(segment_type,'INDEX','rebuild ','INDEX PARTITION','rebuild partition','INDEX SUBPARTITION','rebuild subpartition',
'TABLE','move','TABLE PARTITION','move partition','TABLE SUBPARTITION','move subpartition')
||' '||partition_name||' '||decode(substr(segment_type,1,5),'INDEX','online','TABLE',' update indexes','')||';' cmd
from rim_dba_extents
where (file_id,block_id) in (select file_id,max(block_id) from rim_dba_extents
where tablespace_name like p_tablespace_name
group by file_id)
)  loop
-- execute immediate rec.cmd;
dbms_output.put_line(rec.cmd);
end loop;
execute immediate 'truncate table rim_dba_extents';
EXCEPTION when others then
null;
end;
/
show errors;

set serveroutput on
exec rim_rebuild_last_objects('INDEX%','T_I_LIF%');

I’ll post updates soon. If you have any modified version please send it to me.

Daniel: Why does the view “DBA_JOBS” not show all jobs for example the default jobs?

RealTom:Hi Daniel, interesting question. It’s because you are using Oracle 10g or 11g. 10g got a new job system, the scheduler. Check the in “DBA_SCHEDULER_JOBS”.

I made you a short example with the most important columns:

SQL> select OWNER,JOB_NAME,ENABLED,STATE,SCHEDULE_NAME from DBA_SCHEDULER_JOBS;
SYS           PURGE_LOG                      TRUE  SCHEDULED       DAILY_PURGE_SCHEDULE
SYS           FGR$AUTOPURGE_JOB              FALSE DISABLED
SYS           GATHER_STATS_JOB               TRUE  SCHEDULED       MAINTENANCE_WINDOW_GROUP
SYS           AUTO_SPACE_ADVISOR_JOB         TRUE  SCHEDULED       MAINTENANCE_WINDOW_GROUP
SYS           ADV_SEGMENTADV_2721110         FALSE SUCCEEDED
ORACLE_OCM    MGMT_CONFIG_JOB                TRUE  SCHEDULED       MAINTENANCE_WINDOW_GROUP
ORACLE_OCM    MGMT_STATS_CONFIG_JOB          TRUE  SCHEDULED

Generally there is a more generic solution to answer such question. It’s commonly known as “READ THE ORACLE DOCUMENTATION” or RTFM.