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 |