Internal dictionary queries in OLEDB/ODBC 9.2-10g -- sample trace
- From: "Alvaro Jose Fernandez" <alvaro.fernandez@xxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 17 Aug 2007 15:02:25 +0200
Hi,
I'm including a sample trace from the app (using default tkprof
aggregate across all executions). This query return no rows, since
the tables in the app (and so the passed TABLE_NAMEs in this query) are
synonyms whose names are different from the real schema tables.
Im' using cursor_sharing=similar, so the literal replacement binds.
Worst of all is these queries are generated on each row from the
RecordSet returned by the RDBMS. You can see the SQL*Net waits due to
this "crappy" client/server traffic.
Alvaro
--
select *
from
(select null TABLE_CATALOG, idx.table_owner TABLE_SCHEMA,
idx.table_name
TABLE_NAME, null INDEX_CATALOG, idx.owner INDEX_SCHEMA, idx.index_name
INDEX_NAME, null PRIMARY_KEY, decode(idx.uniqueness, :"SYS_B_00",
-:"SYS_B_01", :"SYS_B_02") UNIQUE_,null CLUSTERED,null TYPE, null
FILL_FACTOR, idx.initial_extent INITIAL_SIZE, null NULLS, null
SORT_BOOKMARKS, -:"SYS_B_03" AUTO_UPDATE, :"SYS_B_04" NULL_COLLATION,
col.column_position ORDINAL_POSITION, col.column_name COLUMN_NAME,
null
COLUMN_GUID, null COLUMN_PROPID, :"SYS_B_05" COLLATION, null
CARDINALITY,
null PAGES, null FILTER_CONDITION, null INTEGRATED from all_indexes
idx,
all_ind_columns col where idx.owner = col.index_owner and
idx.index_name =
col.index_name and idx.table_owner = col.table_owner and
idx.table_name =
col.table_name ) DBSCHEMA_INDEXES WHERE TABLE_SCHEMA = :"SYS_B_06"
AND
TABLE_NAME = :"SYS_B_07" order by :"SYS_B_08", :"SYS_B_09",
:"SYS_B_10",
:"SYS_B_11", :"SYS_B_12"
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 9722 1.89 2.32 0 0 0
0
Execute 9722 6.63 11.93 0 0 0
0
Fetch 9722 1.26 1.45 0 68054 0
0
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 29166 9.78 15.71 0 68054 0
0
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 26
.
.
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total
Waited
---------------------------------------- Waited ----------
------------
SQL*Net message to client 9722 0.01
0.04
SQL*Net message from client 9722 0.04
43.14 <== client/server "ping-pong"
latch free 1 0.00
0.00
Other related posts:
- » Internal dictionary queries in OLEDB/ODBC 9.2-10g -- sample trace
