Internal dictionary queries in OLEDB/ODBC 9.2-10g -- sample trace

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: