Tip#19 Oracle Date Insight

Note : Partial text from Metalink Note:69028.1

Oracle DATE values are always stored in 7 bytes, excluding the length byte, within a datafile. These bytes store the century, year, month, day, hour, minute, and second details respectively. The following is the definition of Oracle's internal DATE storage structure:

BYTE Meaning
---- -------
1 Century -- stored in excess-100 notation
2 Year -- " "
3 Month -- stored in 0 base notation
4 Day -- " "
5 Hour -- stored in excess-1 notation
6 Minute -- " "
7 Second -- " "

Note that the century and year components are stored in 'excess 100 format', which means that 100 must be deducted from the byte's value. If a negative number results, then we've got a BC date at which point we take the absolute number. Also, to avoid ever having a zero byte, 1 is added to the hour, minute and second bytes. Therefore, 1 must be detected to get the correct value.

For example, take the following date:
11-MAR-2009 13:08:00

we would expect this date to be stored internally as follows:
120,109,3,11,14,9,1

Let's confirm this,

SQL> create table test1 as select sysdate sd from dual;

SQL> select to_char(sd, 'DD-MON-YYYY HH24:MI:SS'), dump(sd) from test1;

Result:

TO_CHAR(SD,'DD-MON-YYYYHH24:MI:SS') : 11-MAR-2009 13:08:00

DUMP(SD) : Typ=12 Len=7: 120,109,3,11,14,9,1

Let's try using the DUMP() function to do the same thing with TO_DATE now. Issue the following statement:

SQL> SELECT dump(to_date('11-MAR-2009 13:08:00', 'DD-MON-YYYY HH24:MI:SS'))
FROM dual;

Result: Typ=13 Len=8: 217,7,3,11,13,8,0,0

Note the different "Typ=" values to understand why we are seeing these results. The datatype returned is 13 and not 12, the external DATE datatype. This occurs because we rely on the TO_DATE function! External datatype 13 is an internal c-structure whose length varies depending on how the c-compiler represents the structure. Note that the "Len=" value is 8 and not 7. Type 13 is not a part of the published 3GL interfaces for Oracle and is used for date calculations mainly within PL/SQL operations.

Note that the same result can be seen when DUMPing the value SYSDATE.

Using deductive logic, we can derive the following storage format for type 13 data:

Byte 1 - Base 256 year modifier
2 - Base 256 year
3 - Month
4 - Day
5 - Hours
6 - Minutes
7 - Seconds
8 - Unused

For AD dates, the year and base 256 modifier are stored in base 0 notation and we must add the modifier to the year to obtain the true year. For BC dates, the year and base 256 modifier are stored in excess-255 notation. We must subtract the modifier from the year to obtain the true year.

For our year 2009, we could read this to be, Byte 1 + Byte 2 * 256. In other words, 217 + 7 * 256 = 2009.

Oracle is capable of handling dates from 01-JAN-4712 BC 00:00:00 TO 31-DEC-9999 AD 23:59:59 AD OR in terms of Julian Day: 1 through Julian Day: 5373484

The Julian Day number is a count of days elapsed since Greenwich mean noon on 1 January 4712 B.C. in the Julian proleptic calendar. The Julian Date is the Julian Day number followed by the fraction of the day elapsed since the preceding noon.

Tip#18 Rebuilding A Table

The DBA may have to rebuild a table after maintenance e.g. after a huge delete would like to lower the High Water Mark (HWM). There are several options to do it,

Option 1: Export and Import the table
Option 2: CTAS - Create table as Select
Option 3: Alter table MOVE (> 8.1.6)
Optoin 4: Alter table SHRINK (> 10g )

First two option are fairly straight forward so I wont go in the details here.

ALTER TABLE MOVE :

This one is my preferred option compare to the first two (if not already on 10g or above) and would like to point out few facts.

The ALTER TABLE MOVE command was implemented in 8.1.6. The command will allow tables to be moved from one tablespace to another, reorganise a table, provide the ability to modify of the INITIAL parameter. Also note that we don't lose grants (unlike CTAS), above all it is easy and fast.

Although it has a drawback, please note that when you use it, all the indices on the table become invalid, have to do an ALTER INDEX REBUILD.

e.g. I have a large table MBS with 50 million rows and now due to some process change I need to delete 25 million rows. After such huge delete obviously I would like to lower the HWM to avoid scanning those empty blocks below HWM.

Since I dont want to change the tablespace or initial parametere I would simply execute this,

Alter table MBS move; --tablespace test_tbs pctfree 20 pctused 40;

Since indices on the table will become invalid, I will also need to do this,

SELECT index_name FROM user_indexes WHERE table_name = 'MBS';

Once you know the indices used by the table just rebuild each of them as shown below,

Alter index pk_mbs rebuild; -- unrecoverable; --to avoid generating redo


ALTER TABLE SHRINK :

If you are on 10g or above, both of the above mentioned steps (table move and index rebuild) are done using single statement using SHRINK option.

alter table MBS enable row movement;
alter table MBS shrink space cascade;
alter table MBS disable row movement;

Just for comparison of Alter table options,

1.) deallocate unused : claims back unused space above HWM and doesnt lowers the HWM.
2.) move : claims back unused space below HWM and lowers the HWM
3.) shrink : claims back unused space below and above HWM and lowers the HWM

This blog was intended to just give you an overview, for more details on both the command please refer to the documentation. Also as usual always do backup before and after the changes.

Tip#17 How to find out 32/64 Bit

Find out whether OS is 32 bit or 64 bit

Linux : getconf LONG_BIT

Solaris : /usr/bin/isainfo -kv

AIX : getconf -a | grep KERN

HPUX : /usr/bin/getconf KERNEL_BITS

Windows : Start>All Programs>accessories> System Tools>System Information> System summary


Find out whether Oracle Software is 32 bit or 64 bit.

Method # 1
cd $ORACLE_HOME/bin/
file oracle

Method # 2
sqlplus / as sysdba (Look for "Connected to:")

Method # 3
If the two directories $ORACLE_HOME/lib32 and $ORACLE_HOME/lib are existing then it is 64 bit else 32 bit

Tip#16 Fix Migrated/Chained rows

What do they mean?

Row chaining is what happens when a row cannot fit into a single Oracle block ever, because it is just too big. Oracle therefore has to break it up into several smaller row pieces, store each piece in a separate block, and link (or “chain”) from one block to another so that the entire set of pieces can be retrieved when you query the table for the row.

Row migration is what happens when a row grows in size and can no longer fit into its original Oracle block: Oracle will move it (or ‘migrate’ it) into a completely new block into which it can fit.

How to Find them?

Assuming you have latest stats for the tables, run following SQL to find out if you have any tables with high percentage of chained or migrated rows in a table.

SELECT owner, table_name, num_rows, chain_cnt,
round((chain_cnt * 100 / num_rows),2) pct, pct_free, pct_used
FROM dba_tables WHERE chain_cnt > 0
AND owner NOT IN ('SYS', 'SYSTEM')
ORDER BY 5 desc ;

If you have any tables with large number of chained/migrated rows then follow the below steps to fix it,

How do I fix them?

1. connect to the database as the owner of the table having chained rows run utlchain.sql script which is in your ORACLE_HOME/rdbms/admin. It should create a table called 'chained_rows'.

2. Analyze the table suppose that the table having the issue is called: MBS

SQL>analyze table MBS list chained rows into chained_rows;

3. The above statement will load the chained row information into the table created in step 1

SQL>select * from chained_rows;

4. To fix the rows, put them in a temp table and remove it from original table and copy them from temp table to original table

create table my_tmp AS select * from mbs where rowid IN (select head_rowid from chained_rows where table_name = 'MBS');


DELETE FROM MBS WHERE rowid IN (select head_rowid from chained_rows where table_name = 'MBS');


INSERT INTO MBS SELECT * FROM my_tmp;

COMMIT;

Any Suggestion to avoid it?

Increase PCTFREE to avoid the future problem for those tables.

Tip#15 DBFile Sequential and Scattered Reads

Both "db file sequential read" and "db file scattered read" events signify time waited for I/O read requests to complete. Most people confuse these events with each other as they think of how data is read from disk. Instead they should think of how data is read into the SGA buffer cache.

db file sequential read:

A sequential read operation reads data into contiguous memory (usually a single-block read with p3=1, but can be multiple blocks). Single block I/Os are usually the result of using indexes. This event is also used for rebuilding the controlfile and reading datafile headers (P2=1). In general, this event is indicative of disk contention on index reads.

db file scattered read:

Similar to db file sequential reads, except that the session is reading multiple data blocks and scatters them into different discontinuous buffers in the SGA. This statistic is NORMALLY indicating disk contention on full table scans. Rarely, data from full table scans could be fitted into a contiguous buffer area, these waits would then show up as sequential reads instead of scattered reads.

The following query shows average wait time for sequential versus scattered reads:

select a.average_wait "SEQ READ", b.average_wait "SCAT READ"
from sys.v_$system_event a, sys.v_$system_event b
where a.event = 'db file sequential read'
and b.event = 'db file scattered read';

Tip#14 Resumable Space Allocation

In the event of space allocation failures, rather than returning the error to the user and stopping the operation, the transaction can be temporarily suspended and corrective action taken. After the error condition is corrected, the suspended operation automatically resumes. This feature is called resumable space allocation. The affected statements are called resumable statements.


When the execution of a resumable statement is suspended, the system issues a Resumable Session Suspended alert. A suspended operation is automatically aborted if the error condition is not fixed within the time-out period. By default, the time-out period is two hours. you can enable resumable space allocation either at the system level, by setting the RESUMABLE_TIMEOUT initialization parameter to a nonzero value, or at the session level, by issuing the ALTER SESSION ENABLE RESUMABLE statement. A resumable statement can be suspended and resumed multiple times during execution.


Which statements are resumable?


• DML statements, including INSERT INTO ... SELECT from external tables, are resumable.
• DDL statements, including CREATE TABLE ... AS SELECT, are resumable.
• SELECT statements that run out of temporary space are also candidates for resumable execution.

A resumable statement can be suspended under any of these conditions:

• Space quota exceeded (e.g. The user has exceeded his or her assigned space quota in the tablespace),
• Maximum extents reached (e.g. The number of extents in a table or index equals the number of maximum extents defined on the object.)
• Out of space (e.g. The operation cannot acquire any more extents for an index in a tablespace.)

Tip#13 Big Vs Little Endian

Little Endian means that the low-order byte of the number is stored in memory at the lowest address, and the high-order byte at the highest address. (The little end comes first.) For example, a 4 byte LongInt

Byte3 Byte2 Byte1 Byte0

will be arranged in memory as follows:
Base Address+0 Byte0
Base Address+1 Byte1
Base Address+2 Byte2
Base Address+3 Byte3

Linux, Windows use "Little Endian" byte order.

Big Endian means that the high-order byte of the number is stored in memory at the lowest address, and the low-order byte at the highest address. (The big end comes first.) Our LongInt, would then be stored as:

Base Address+0 Byte3
Base Address+1 Byte2
Base Address+2 Byte1
Base Address+3 Byte0

Solaris, HPUX, Apple Mac use "Big Endian" byte order.

In Oracle 10g, following SQL should tell you which operating systems follow which byte order,

select * from v$transportable_platform order by platform_id;

To transport tablespaces between OS with same byte orders (endianness), we don’t need conversion in other cases we do which can be done using RMAN. E.g. I need to transport a Tablespace from Linux (Little Endian) to Solaris (Big Endian)

RMAN> convert tablespace XXX to
platform ‘Solaris[tm] OE (64-bit)' 
db_file_name_convert '/app/oracle/oradata/mbs’,
'/app/oracle/rman_bkups';

Now this file can be copied over to the target Solaris system, and the rest of the steps are easy.