SQL Access Advisor allows DBAs and developers to analyze individual SQL statements, SQL tuning sets or the entire system and recommend materialize views, partitioning and indexes on tables. There are two interfaces of SQL Access Advisor: Oracle Enterprise Manager and DBMS_ADVISOR package. The Oracle Enterprise Manager interface is easy and user-friendly, but DBMS_ADVISOR package provides the same recommendations from SQL*Plus, which is useful in environments that do not have OEM installed. SQL Access Advisor is a part of the Oracle Tuning Pack, which also requires the Diagnostic Pack.
Viewing entries in
2013
A customer had an interesting problem recently involving selective bit-flipping. The real scenario was a bit more complicated, but it came down to the following scenario: the client has a large number of members. Each member visits multiple times. They want to switch the value between X2 and NULL every time a ‘true’ event occurs.
A great feature introduced in the Oracle 11g database is the SQL query ‘Result Cache’. A result cache is an area of memory that stores the results of a database query or query block for reuse. The cached rows are shared across SQL statements and sessions unless they become stale. The caching mechanism is efficient and easy to use, and it relieves you of designing and developing your own legacy cache procedures and cache-management policies.
This package enables the collection of data for performance improvement or for determining code coverage for PL/SQL applications. Application developers can use code coverage data to focus their incremental testing efforts. This blog provides DBAs, developers, support analysts, and consultants with PL/SQL tuning hints and tips using profiling.
In the previous SQL_TRACE blog, we discussed how to trace Oracle sessions and how to gather information using “trace” files.
This blog will help you to understand and interpret 10046 raw trace data.