One application stopped sending messages through. DBAs looked at the database. Only once in a while was there a session running SELECT TABLE_NAME FROM USER_TABLES and a few other queries and DMLs; most of the time the database was idle. Nevertheless the query against user_tables caught my eye because it's frequently executed and each exec took 1200 to 1800 buffer gets. The plan shows the joins are between 7 internal tables: The sql_text is "SELECT TABLE_NAME FROM USER_TABLES". SQL> select buffer_gets, executions, buffer_gets/executions, rows_processed, buffer_gets/rows_processed from gv$sqlstats where sql_id = '64qgr0gsdtmkf'; BUFFER_GETS EXECUTIONS BUFFER_GETS/EXECUTIONS ROWS_PROCESSED BUFFER_GETS/ROWS_PROCESSED ----------- ---------- ---------------------- -------------- -------------------------- 13649288 7563 1804.74521 7084 1926.77696 11154490 8220 1356.99392 3396 3284.59658 2583050 2089 1236.50072 577 4476.68977 870577 665 1309.13835 245 3553.37551 717269 466 1539.20386 299 2398.89298 278670 226 1233.0531 62 4494.67742 SQL> select * from table(dbms_xplan.display_cursor('64qgr0gsdtmkf',null,'all')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------- SQL_ID 64qgr0gsdtmkf, child number 0 ------------------------------------- SELECT TABLE_NAME FROM USER_TABLES Plan hash value: 701401877 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 386 (100)| | |* 1 | HASH JOIN RIGHT OUTER | | 943 | 151K| 386 (3)| 00:00:05 | | 2 | TABLE ACCESS FULL | USER$ | 124 | 496 | 3 (0)| 00:00:01 | |* 3 | HASH JOIN OUTER | | 943 | 147K| 383 (3)| 00:00:05 | | 4 | NESTED LOOPS OUTER | | 943 | 139K| 246 (3)| 00:00:03 | |* 5 | HASH JOIN RIGHT OUTER | | 943 | 135K| 246 (3)| 00:00:03 | | 6 | TABLE ACCESS FULL | SEG$ | 4982 | 54802 | 46 (0)| 00:00:01 | |* 7 | HASH JOIN | | 943 | 125K| 199 (4)| 00:00:03 | | 8 | TABLE ACCESS FULL | TS$ | 84 | 252 | 20 (0)| 00:00:01 | | 9 | NESTED LOOPS | | 943 | 122K| 178 (3)| 00:00:03 | | 10 | MERGE JOIN CARTESIAN| | 943 | 97129 | 139 (4)| 00:00:02 | |* 11 | HASH JOIN | | 1 | 68 | 1 (100)| 00:00:01 | |* 12 | FIXED TABLE FULL | X$KSPPI | 1 | 55 | 0 (0)| | | 13 | FIXED TABLE FULL | X$KSPPCV | 100 | 1300 | 0 (0)| | | 14 | BUFFER SORT | | 943 | 33005 | 139 (4)| 00:00:02 | |* 15 | TABLE ACCESS FULL | OBJ$ | 943 | 33005 | 139 (4)| 00:00:02 | |* 16 | TABLE ACCESS CLUSTER| TAB$ | 1 | 30 | 1 (0)| 00:00:01 | |* 17 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 0 (0)| | |* 18 | INDEX UNIQUE SCAN | I_OBJ1 | 1 | 5 | 0 (0)| | | 19 | TABLE ACCESS FULL | OBJ$ | 46231 | 361K| 136 (2)| 00:00:02 | --------------------------------------------------------------------------------------- ... In my own session: SQL> select table_name from user_tables; TABLE_NAME ------------------------------ SMALL BIG CI_TEST TESTSTRING T Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 1545 consistent gets 0 physical reads 0 redo size 612 bytes sent via SQL*Net to client 488 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 5 rows processed Then I tried dba_tables restricting on owner. SQL> select table_name from dba_tables where owner = 'YHUANG'; TABLE_NAME ------------------------------ TESTSTRING BIG SMALL T CI_TEST Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 949 consistent gets 0 physical reads 0 redo size 612 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) 5 rows processed The same is true for all_tables. The actual SQL was run by app user. But the conclusion is the same: accessing dba_tables or all_tables restricting on owner is cheaper than accessing user_tables. For both queries, the buffer gets does not change after I gather_fixed_objects_stats and gather_dictionary_stats. Forcing CBO to not use hash join doesn't make much difference in buffer gets. The most expensive step in the plan is full scan on obj$. The most CPU intensive step in the plan (not reflected in buffer gets) is scanning x$ksppi and x$ksppcv, checking if _dml_monitoring_enabled is set. (On a test database, setting that to false does not eliminate that step.) So the initial suggestion to the app team is they contact the vendor to change the code to select table_name from all_tables where owner = 'APP' and in the meantime we create a table create table app.user_tables as select table_name from all_tables where owner = 'APP'; We're lucky in this case in that user_tables is a public synonym for sys.user_tables view. Otherwise we wouldn't be able to change the app user's query in an underhand way.[note] Then I checked gv$sql*, gv$open_cursor and dba_hist_sqltext for possible use of other columns of user_tables, and found none. So I suggested to the vendor they select tname from tab, which is one more step cheaper in buffer gets. Then came surprising news. One old member in the app team vaguely remembers that the query only serves the purpose of checking database connectivity. I asked Why not query dual table instead? No answer. With caution, in another schema this application connects to, we created a view create view app.user_tables as select 1 from dual; Now buffer gets dropped to practically 0 (app connects to 3 nodes of the RAC database): SQL> select inst_id, first_load_time, executions, buffer_gets, buffer_gets/executions 2 from gv$sql where sql_id = '64qgr0gsdtmkf' order by 1; INST_ID FIRST_LOAD_TIME EXECUTIONS BUFFER_GETS BUFFER_GETS/EXECUTIONS ---------- ------------------- ---------- ----------- ---------------------- 6 2008-05-14/14:08:55 135458 50 .000369118 6 2008-05-14/14:08:55 80382 562707 7.00041054 7 2008-05-14/14:08:53 147603 1033248 7.00018292 7 2008-05-14/14:08:53 269919 47 .000174126 8 2008-05-14/14:08:54 271405 2 7.3691E-06 8 2008-05-14/14:08:54 160273 1121944 7.0002059 The last column shows buffers per exec 7 for the first schema where a user_tables table is created, and 0 for the second schema where a view based on dual is created. Epilog: The app has been running for some time and nobody complained about any problem. The vendor still has not answered our question, but the worry our bold action may break the app logic is gradually being forgotten. The original problem of application hanging was further tested with our work. When the two "fake" table/view were put in place, the messages were sent through. With the table renamed and view dropped, it hung. I kept on scratching my head, to the limit of few hairs left, trying to come up with a hypothesis why our heroic, so claimed by them, could possibly be relevant. The database was mostly idle during the hang. One theory I thought of was the app may have some kind of self-throttling mechanism such that if their heavy-duty connectivity check comes back not within a certain amount of time, it assumes the database is too busy and throttles on message enqueuing. The vendor support guy rejected my otherwise beautiful theory. But he didn't know the purpose of the frequent query of user_tables so his credibility is discounted, by me at least. [note] Here we're replacing the reference to sys.user_tables view with something *better*. Oracle is close to providing a way for us to do this. 10g's dbms_advanced_rewrite is probably meant to replace the cumbersome outline technology. But if it were not due to the SQL equivalence check, we might be able to use that package to do the same stealthy work, even if the application were not accessing something through a synonym. [Update 2015-05] In 12c, SQL Translation Framework can do exactly that: exec dbms_sql_translator.create_profile('TRANS'); exec dbms_sql_translator.register_sql_translation('TRANS', 'select nosuchcol from mytab', 'select mycol from mytab') alter session set events = '10601 trace name context forever, level 32'; alter session set sql_translation_profile = trans; select nosuchcol from mytab; select * from v$mapped_sql; Ref: How to Use the SQL Translation Framework to Workaround Performance and Parsing Problems (Doc ID 1585091.1) How to Diagnose and Track SQL Translations (Doc ID 1586667.1)