Life of an Oracle DBA – The Basics Access Paths: Table Access By Rowid.

Dang, that just sounds fast, and it is. Rowid points to the datafile:block:location in block. So, what is this good for? How do you get Oracle to access by rowid? If you want to access a specific row or set of rows in the database and can derive the rowid you can get a lot of speed accessing the data. To get the rowid you need to either pass the rowid to the query like I did here or by accessing it through an index.

But what is a rowed? In Oracle 8 and above rowed is 10 bytes that breaks down to:

Bits 1 – 32: object_id

Bits 33 – 44: file number

Bits 45 – 64: block number

Bits 65 – 80: row number inside of block

You can use the package dbms_rowid to get interesting information from rowid.

SQL> variable vrowid varchar2(20);

SQL> execute :vrowid := ‘AABAkfAAIAAIdZKAAA’

PL/SQL procedure successfully completed.

SQL> select dbms_rowid.rowid_object(:vrowid) “object”,

2 dbms_rowid.rowid_relative_fno(:vrowid) “file”,

3 dbms_rowid.rowid_block_number(:vrowid) “block”,

4 dbms_rowid.rowid_row_number(:vrowid) “row”

5 from dual;

object file block row

———- ———- ———- ———-

264479 8 2217546 0

Lets’ look at getting a row from the database by using rowed. One thing you will notice is we did not have to make a round trip to the index t1_idx to find the rowed because we already had it. Therefore Oracle was able to go directly to the row in the database and return it to us. I’m thinking I could have done this better because using trace files and autotrace will create a lot of additional recursive sql in the trace file; such as delete from plan_table and insert into plan_table.

alter session set timed_statistics = true;

alter session set statistics_level = all ;

alter session set sql_trace = true ;

alter session set max_dump_file_size=unlimited;

alter session set tracefile_identifier = owi_trace;

alter session set events ‘10046 trace name context forever, level 12’;

set autotrace on

select object_type, object_name from t1 where rowid = :vrowid;

OBJECT_TYPE OBJECT_NAME

———————————————————————-TABLE FILE$

Execution Plan

———————————————————-

Plan hash value: 2759577407

———————————————————————————–

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

———————————————————————————–

| 0 | SELECT STATEMENT | | 1 | 44 | 1 (0)| 00:00:01 |

|* 1 | TABLE ACCESS BY USER ROWID| T1 | 1 | 44 | 1 (0)| 00:00:01 |

———————————————————————————–

Predicate Information (identified by operation id):

—————————————————

1 – access(CHARTOROWID(:VROWID))

Statistics

———————————————————-

6 recursive calls

4 db block gets

5 consistent gets

0 physical reads

760 redo size

596 bytes sent via SQL*Net to client

488 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

select object_type, object_name

from

t1 where rowid = :vrowid

call count cpu elapsed disk query current rows

——- —— ——– ———- ———- ———- ———- ———-

Parse 1 0.00 0.00 0 0 0 0

Execute 2 0.00 0.00 0 0 13 0

Fetch 2 0.00 0.00 0 1 0 1

——- —— ——– ———- ———- ———- ———- ———-

total 5 0.00 0.00 0 1 13 1

Misses in library cache during parse: 0

Optimizer mode: ALL_ROWS

Parsing user id: 56

Rows Row Source Operation

——- —————————————————

1 TABLE ACCESS BY USER ROWID T1 (cr=1 pr=0 pw=0 time=71 us)

Elapsed times include waiting on following events:

Event waited on Times Max. Wait Total Waited

—————————————- Waited ———- ————

SQL*Net message to client 3 0.00 0.00

SQL*Net message from client 3 1.58 1.58

********************************************************************************

What does this tell me: I got the data in 71 micro seconds. That’s fast. Now I’m leaving out all of the recursive work that Oracle did to execute the command.

Lets look at some more timing information.

mrskew.exe \stage\*.trc > H:\tmp\x.txt

CALL-NAME DURATION % CALLS MEAN MIN MAX

SQL*Net message from client 11.256462 98.9% 4 2.814115 0.001859 9.667408

EXEC 0.120000 1.1% 3 0.040000 0.000000 0.070000

log file sync 0.001551 0.0% 2 0.000776 0.000513 0.001038

SQL*Net message to client 0.000022 0.0% 4 0.000005 0.000004 0.000006

PARSE 0.000000 0.0% 2 0.000000 0.000000 0.000000

FETCH 0.000000 0.0% 2 0.000000 0.000000 0.000000

TOTAL (6) 11.378035 100.0% 17 0.669296 0.000000 9.667408

Using mrskew from Method-R I get some more interesting information from the trace file. What is this telling me? sqlnet message from client was the big wait event; tanking 98.9% of the time. Well that’s network io. After that EXEC was my next highest wait event with 3 calls taking 1.1% of the execution time. BTW: this data includes all recursive sql. So what does the EXEC timing look like?

mrskew.exe \stage\*.trc –name=”EXEC” –ebucket > H:\tmp\x.txt

RANGE {min <= e < max} DURATION % CALLS MEAN MIN MAX

0.000000 0.000001 0.000000 0.0% 1 0.000000 0.000000 0.000000

0.000001 0.000010 0.000000 0.0% 0

0.000010 0.000100 0.000000 0.0% 0

0.000100 0.001000 0.000000 0.0% 0

0.001000 0.010000 0.000000 0.0% 0

0.010000 0.100000 0.120000 100.0% 2 0.060000 0.050000 0.070000

0.100000 1.000000 0.000000 0.0% 0

1.000000 10.000000 0.000000 0.0% 0

10.000000 100.000000 0.000000 0.0% 0

100.000000 1000.000000 0.000000 0.0% 0

1000.000000 Infinity 0.000000 0.0% 0

TOTAL (2) 0.120000 100.0% 3 0.040000 0.000000 0.070000

This entry was posted in Database Stuff, Life of a Oracle DBA by rlockard. Bookmark the permalink.

About rlockard

Robert Lockard is a professional Oracle Designer, Developer and DBA working in the world of financial intelligence. In 1987 his boss called him into his office and told him that he is now their Oracle Wizard then handed him a stack of Oracle tapes and told him to load it on the VAX. Sense then, Robert has worked exclusively as an Oracle database designer, developer and Database Administrator. Robert enjoys flying vintage aircraft, racing sailboats, photography, and technical diving. Robert owns and fly’s the “Spirit of Baltimore Hon” a restored 1948 Ryan Navion and lives in Glen Burnie Maryland on Marley Creek

Welcome to oraclewizard