Oracle



General
     Oracle 8.1.6 through 11.2 supported

     We ship ojdbc14.jar (Oracle 11g JDBC library) so the Oracle client installation is not required.

Setup
      Grant SELECT_CATALOG_ROLE to <connecting user>

    

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. 
There are basically 3 reasons for "unknown"
  1. 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.
  2. Oracle internal processes - when the run much of their SQL, Oracle doesn't expose the SQL ID and we can't track it
  3. 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
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)
for example
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

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

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.





Comments