From DB Optimizer
Jump to: navigation, search



Oracle 8.1.6 through 11.2 supported
Note: DB Optimizer does not access in any way, shape or form any of the information, tables or views licensed under Oracle’s diagnostic pack.
We ship ojdbc14.jar (Oracle 11g JDBC library) so the Oracle client installation is not required.


Grant SELECT_CATALOG_ROLE to <connecting user>
Grants Example
Tables and Views Accessed
Access Verification Query


tnsnames.ora or direct connection
Note: LDAP and ifile in tnsname.ora are not supported.


DB Optimizer only reads v$session for load information. We do not read v$active_session_history or any other table licensed in Oracle performance packs.
Sampling Query
List of Tables Accessed by Profiler


Oracle users needs access to V$ views. Both SYS and SYSTEM have access by default.
Other users can be granted
grant SELECT_CATALOG_ROLE to user_name;

UNKNOWN sql text: We collect SQL text every 15 seconds, so before the collection the text is unknown, only the sql_id or sql_hash is known. Every 15 seconds we take sql ids with unknown text and look up the text. Before this lookup is run the text is marked as unknown. If the query gets kicked out of the shared pool between the time we find the sql id and the time we look for the text then the text will be marked as unknown. If getting an unusual amount of UNKNOWN sql text , contact support and try changing the connection type from "TNS alias" to/from "direct connection". Also try and use the Oracle 10g client install on the machine hosting DBO.


v$sqlstats (10g and up)
List of tables accessed by Tuner

Special Oracle Functionality

DB Optimizer has extend functionality for Oracle that will be extended cross platform in 2.0.
The extended functionality for Oracle includes
  • Tuner
    • Index Analysis - finding missing and redundant indexes
    • Statistics Analysis - finding out if table statistics exist and are up to date
    • Column Analysis - analyzing columns to see if the are candidates for histograms
    • Outline Support - hints can be saved to the database without the need to change the text of the query
Known Issues
Index Analysis won't work if there are open transactions on the underlying table because we use virtual indexes to determine whether the indexes will be used by Oracle. If there are open transactions on the underlying table, the error ORA-00054 will be thrown but not displayed except in the SQL error log (choose windows
Outlines that exist won't show up for users that did not create the outlines but are applied to the underlying SQL. This is the way Oracle works. Outlines are applied to SQL text across all users no matter who created the outlines. We are adding a search in the outline tab to display outlines that were created by any user, not just the connected user.
  • Profiling
    • Load Chart
    • Top Activity
Object IO tab
    • Details Editor
Buffer Busy Waits
Cache Buffers Chains
Profiler also provides extra analysis for the wait events "buffer busy waits" and "cache buffers chains latch". If these waits appear in Top Activity section under the "Events Tab" and the user clicks on them, then the details area will include a tab called "analysis" with further information on these waits. The wait analysis will be extended to many more events in the next release.

Oracle Waits