Re: How Oracle finds a data block address?

<dusanmiloradovic_at_hotmail.com> wrote in message news:2bfc209f.0411180252.30939b81_at_posting.google.com...
| In order to find a data block in the buffer, file_id and block_id must
| be provided. But where from Oracle gets them?
| I presumed that Oracle creates row source when it hard parse the
| query, use some recursive sql from the data dictionary,and then search
| for the block in the buffer. I supposed that this inforamtion was
| included with the parsed sql.
| But:
|
| SQL> create table t as select * from all_objects;
|
| Table created.
|
| SQL> set autotrace traceonly
| SQL> select * from t
| 2 /
|
| 32446 rows selected.
|
|
| Execution Plan
| ----------------------------------------------------------
| 0 SELECT STATEMENT Optimizer=CHOOSE
| 1 0 TABLE ACCESS (FULL) OF 'T'
|
|
|
|
| Statistics
| ----------------------------------------------------------
| 0 recursive calls
| 0 db block gets
| 2587 consistent gets
| 442 physical reads
| 0 redo size
| 2294399 bytes sent via SQL*Net to client
| 24297 bytes received via SQL*Net from client
| 2165 SQL*Net roundtrips to/from client
| 0 sorts (memory)
| 0 sorts (disk)
| 32446 rows processed
|
| Altough this was the first time this query was executed, it had 0
| recursive calls.
|
| After that i typed this:
|
| SQL> alter system flush shared_pool;
|
| System altered.
|
| SQL> select * from t
| 2 /
|
| 32446 rows selected.
|
|
| Execution Plan
| ----------------------------------------------------------
| 0 SELECT STATEMENT Optimizer=CHOOSE
| 1 0 TABLE ACCESS (FULL) OF 'T'
|
|
|
|
| Statistics
| ----------------------------------------------------------
| 238 recursive calls
| 0 db block gets
| 2634 consistent gets
| 419 physical reads
| 0 redo size
| 2294399 bytes sent via SQL*Net to client
| 24297 bytes received via SQL*Net from client
| 2165 SQL*Net roundtrips to/from client
| 2 sorts (memory)
| 0 sorts (disk)
| 32446 rows processed
|
| Now it has 238 recursive calls!
| So, where Oracle keeps this info?
|
| Dusan Miloradovic

data dictionary tables are owned by SYS, you can see some of the information in data dictionary views

the USER_SEGMENTS and USER_EXTENTS views expose some of the physical storage characteristics of tables, indexes, clusters, LOB storage, etc.

recursive calls are only needed if the necessary data dictionary information is not cached

immediately after creating the table, there is no need to retrieve its definition for the data dictionary since the just-used info is already cached

take a look at the concepts and dba manuals for more details

++ mcs Received on Thu Nov 18 2004 - 05:03:58 CST