Oracle

Oracle

General

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.

Setup

Grant SELECT_CATALOG_ROLE to <connecting user>

Grants Example

Tables and Views Accessed

Access Verification Query

Connections

tnsnames.ora or direct connection

Note: LDAP and ifile in tnsname.ora are not supported.

Profiler

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

Privileges

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.

Tuner

creates table EMBARCADERO_EXPLAIN_PLAN

creates functions EMBT_GET_SIGNATURE and EMBT_DISPLAY_ROWS

accesses

v$SQL_PLAN_STATISTICS_ALL

v$sql

v$sql_plan

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

Max CPU

• • 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