Internals

So, which number takes more bytes inside an Oracle row?

A: 123

B:  1000000000000000000000000000000000000

And the correct answer is … (drumroll) … A! The “big” number 1000000000000000000000000000000000000 actually takes less space than the “small” 123!

Let’s verify this:

SQL> select vsize(123) A, vsize(1000000000000000000000000000000000000) B from dual;

         A          B
---------- ----------
         3          2

WTF? Why does such a small number 123 take more space than  1000000000000000000000000000000000000 ?

Well, the answer lies in how Oracle stores numbers. Oracle NUMBER datatype doesn’t store numbers in their platform-native integer format. Oracle uses it’s own format which stores numbers in scientific notation, in exponent-mantissa form. More details about this here.

You can use the DUMP sql function to see the actual binary value of the number data stored:

select dump(123) from dual;

DUMP(123)
---------------------
Typ=2 Len=3: 194,2,24

SQL> select dump(1000000000000000000000000000000000000) from dual;

DUMP(10000000000000
-------------------
Typ=2 Len=2: Typ=2 Len=2: 211,2

So, although the number 1000000000000000000000000000000000000 is bigger than 123, when stored in base-10 exponent form, it really carries much less information in it than 123 (1 x 10^36 vs 123 x 10^0). Oracle doesn’t need many bits for keeping the precision of this large value as it happens to be a power of 10.

See what happens when I store a number only slightly bigger or smaller than the original large number, now the stored number requires much more storage for keeping the required precision:

From time to time the main Oracle XML DB page gets updated with new whitepapers, tooling or Oracle By Example/ Hands-on Lab examples. “Lately” some cool and interesting new whitepapers and updated tooling content were created on this main Oracle XML DB page. The following items and content are really worth reading. Small issue, though, is that you need a bit more than basic understanding to put all this “lessons learned from the last one, two years” into context, but its worth it and otherwise a small reprise on the Oracle XML DB Developers Guide is always useful. A bit like re-reading the Oracle Concepts Manual.

• Technology Preview: Oracle XML DB Ease of Use Tools for Structured Storage (ZIP)

The “Ease of Use Tools” (xdbutilities.zip tool set) for handling XMLType Object Relational storage has been updated and is now applicable on Oracle 10.x and 11.x. No specific to be installed versioned tool set needed anymore. This prepacked tool set on PL/SQL packages is installable on both versions. The zip file also contains a whitepaper that describes some of the (performance) lessons learned while using XMLType Object Relational storage.

I think this posting might go down as one of my more pointless contributions to the Oracle knowledge sphere

I was looking at V$SYS_TIME_MODEL and V$SESS_TIME_MODEL and I just happened to run “select * from V$SYS_TIME_MODEL” several times in very quick succession. And I noticed the values for the various counters stayed the same between a couple of the runs.

“Hmmm, interesting” I thought “The values are only flushed down to the view ‘periodically’. I wonder how periodically?”… and thus I wasted a lunch time.

I used the below sql*plus-PL/SQL script to investigate the refreshing of v$sess_time_model. Yes, I know the title says v$sys_time_model but the numbers are smaller and easier to check for the session version of the view and they are virtually the same, I can bet on the results being very similar. This is my script (and it is on 10.2.0.3 on linux):

--test_vstm_upd
-- how often is v$sessstat updated
set trims on
set term off
set serveroutput on size unli
spool test_vstm_upd
begin
  for a in 1..1000  loop
    for vrec in
      (select rpad(rpad(to_char(a),4)||' '||to_char(systimestamp,'ss.ff')||' '|| stat_name||' ',45)||value rec
      from v$sess_time_model
-- replace with your current session ID
      where sid=1989
      and stat_id in (3649082374,2748282437,2821698184,2643905994)
      )
    loop
      dbms_output.put_line(vrec.rec);
    end loop;
    dbms_output.put_line(chr(9));
  end loop;
end;
/
set term on
spool off

As you can see, it simply loops around selecting four of the values from v$sess_time_model, including the loop counter and current timestamp. Timetamp is evaluated afresh for each executed sql statement.

Here is the output for the first three iterations;

You probably already know that it’s possible to drop tables in Oracle read only tablespaces… (You did know that already, right? ;-) Here’s a little example:

SQL> create tablespace ronly datafile '/u03/oradata/LIN112/ronly.01.dbf' size 10m;

Tablespace created.

SQL> create table test tablespace ronly as select * from all_users;

Table created.

SQL> alter tablespace ronly READ ONLY;

Tablespace altered.

SQL> drop table test;

Table dropped.

I just dropped a table from a read only tablespace! Well, perhaps it’s because that instead of dropping the table was put into recyclebin instead (which is a data dictionary update)? Let’s check which segments remain in the RONLY tablespace:

SQL> select owner,segment_name,segment_type from dba_segments where tablespace_name = 'RONLY';

OWNER   SEGMENT_NAME                    SEGMENT_TYPE
------- ------------------------------- ------------------
TANEL   BIN$ix7rAUXZfB3gQKjAgS4LXg==$0  TABLE

Indeed, it seems that the table segment wasn’t actually dropped. Well, let’s purge the recycle bin to try to actually drop the table segment:

Last week I blogged a piece all about DBA_TAB_MODIFICATIONS and how it captures all normal DML against table. However, a word of warning. This may not be true for multi-table inserts. It appears that inserts via multi-table inserts are not recorded in DBA_TAB_MODIFICATIONS for 10.2.0.3 and a couple of 9.2 versions of oracle.

{My thanks got to my comrade-in-arms Basavaraj Patil for this. He not only noticed the issue but tracked it down to multi-table inserts, came and told me about it and then said I could put it in my blog. This is his work really}.

I will not go into multi-table inserts right now but it is a very nice feature that was introduced with oracle 9 which allows you to insert a row into several tables at a time or into one of different tables. In fact, I think it is a relatively unknown feature. Check out this oracle-base article on new 9i features that mentions it or go pull up the Oracle 10 SQL reference manual.

There is a bug with Oracle up to 10.2.0.3 and 9.2.0.6 and 9.2.0.7.
It is fixed in 9.2.0.8, 10.2.0.4 and 11.1.0.6
Bug number 4572043 it you have metalink.
It impacts direct load merge statements as well as multi-table inserts, but I have not tested and verified the direct load merge statement.

Below is a demonstration of the issue:

I’ve mentioned DBA_TAB_MODIFICATIONS (and there is also of course ALL_TAB_MODIFICATIONS and USER_TAB_MODIFICATIONS) in a couple of posts, but mostly buried in long, rambling posts about other things. So here is an overview and demonstration of DBA_TAB_MODIFICATIONS. Skip to the end if you just want to see an example of how it works.

The below example is from 10.2.0.3 but for V11 it is pretty much the same, as is 9 except where I say otherwise.

SYS.DBA_TAB_MODIFICATIONS is used by oracle internally to track how many inserts, updates and deletes have been done to a table, table partition or table subpartition since the segment last had stats gathered on it with dbms_stats. It also records if the segment has been truncated since the last stats gather. Why since the last stats gather? Because Oracle uses it internally to detect how much a segment has changed and if it is 10% or more oracle will deem it stale for stats gathering purposes. (under 11 you can alter the stale percentage). Note I said SYS.DBA_TAB_MODIFICATIONS – on 10 at least the public synonym for it is missing, so unlike most other DBA_ views you need to state the owner (or create a synonym).

I haven’t written any blog entries for a while, so here’s a very sweet treat for low-level Oracle troubleshooters and internals geeks out there :)

Over a year ago I wrote that Oracle 11g has a completely new low-level kernel diagnostics & tracing infrastructure built in to it. I wanted to write a longer article about it with comprehensive examples and use cases, but by now I realize I won’t ever have time for this, so I’ll just point you to the right direction :)

Basically, since 11g, you can use SQL_Trace, kernel undocumented traces, various dumps and other actions at much better granularity than before.

For example, you can enable SQL_Trace for a specific SQL_ID only:

SQL> alter session set events 'sql_trace[SQL: 32cqz71gd8wy3] 
{pgadep: exactdepth 0} {callstack: fname opiexe}
plan_stat=all_executions,wait=true,bind=true';


Session altered.

Actually I have done more in above example, I have also said that trace only when the PGA depth (the dep= in tracefile) is zero. This means that trace only top-level calls, issued directly by the client application and not recursively by some PL/SQL or by dictionary cache layer. Additionally I have added a check whether we are currently servicing opiexe function (whether the current call stack contains opiexe as a (grand)parent function) – this allows to trace & dump only in specific cases of interest!

Randolf Geist has written a good article about systematic troubleshooting of a PL/SQL memory allocation & CPU utilization problem – and he has used some of my tools too!

http://oracle-randolf.blogspot.com/2010/05/advanced-oracle-troubleshooting-session.html

Here’s a little trick question. Check out the execution plan below.

What the hell, shouldn’t the INDEX/TABLE access be the other way around?!

Also, how come it’s TABLE ACCESS FULL (and not by INDEX ROWID) in there?

This question is with a little gotcha, but can you come up with a query which produced such plan? ;-)

----------------------------------------------
| Id  | Operation          | Name   | E-Rows |
----------------------------------------------
|   0 | SELECT STATEMENT   |        |        |
|*  1 |  INDEX RANGE SCAN  | PK_EMP |      1 |
|*  2 |   TABLE ACCESS FULL| EMP    |      1 |
----------------------------------------------

Did you know that it’s possible to use EXPLAIN PLAN FOR CREATE INDEX ON table(col1,col2,col3) syntax for explaining what exactly would be done when an index is created?

That’s useful for example for seeing the Oracle’s estimated index size without having to actually create the index.

You can also use EXPLAIN PLAN FOR ALTER INDEX i REBUILD to see whether this operation would use a FULL TABLE SCAN or a FAST FULL INDEX SCAN (offline index rebuilds of valid indexes can use this method).

Anyway, you can experiment with this yourself, but here’s a little quiz (with a little gotcha :)

What kind of index creation statement would create such an execution plan?