Oracle 8.1.6 through 11.2 supported
We ship ojdbc14.jar (Oracle 11g
JDBC library) so the Oracle client installation is not required.
Grant SELECT_CATALOG_ROLE to <connecting user>
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.
List of Tables Accessed by Profiler
Oracle users needs access to V$ views. Both SYS and SYSTEM have access by
Other users can be granted
grant SELECT_CATALOG_ROLE to user_name;
: 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.
There are basically 3 reasons for "unknown"
- commit - when a user commits there is no SQL to speak, we should call it "commit" instead of uknown to make it clear. It's easy to see this because it shows up as "log file sync" or the dark yellow color.
- Oracle internal processes - when the run much of their SQL, Oracle doesn't expose the SQL ID and we can't track it
- SQL has been flushed out of cache - in this case we found the users SQL ID, and you will see an number in brackets under the unknown SQL, but the SQL text has been flushed out of the cache and is no longer available so we can identify what the sql statement wa
creates table EMBARCADERO_EXPLAIN_PLAN
creates functions EMBT_GET_SIGNATURE and EMBT_DISPLAY_ROWS
v$sqlstats (10g and up)
connect sys as sysdba
grant select on v_$SQL_PLAN_STATISTICS_ALL to scott;
grant select on v_$sql to scott;
grant select on v_$sql_plan to scott;
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 targeted for the end of 2009.
The extended functionality for Oracle includes
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
Index Analysis won't work if there are open transactions on the underlying table because we use virtual indexes to determine whehter 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.
Object IO tab
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.