Oracle Class – Wissem's Oracle and NOSQL Tips – Tunisia's First Oracle, NOSQL Website

Introduction:

Oracle defines the ORA-7445 as an unhandled exception error that could be result of an Oracle bug. The ORA-7445 errors depend on the version of the Oracle software, OS version.
For more details about the error, you need to check the trace files generated. The ORA-7445 exception generates a trace file located under the CORE_DUMP_DEST directory for oracle versions prior to 11g and under the ADR Home / cdump (Automatic Diagnostic Repository) for 11g versions.
This article is not indented to give solutions to all ORA-7445 errors. For any details about the ORA-7445 errors please use the ORA-600/ORA-7445/ORA-700 Error Look-up Tool [ID 153788.1] and open a service request.

(more…)

The following two files can be executed to revert new security configuration changes in 11g to 10g configuration;

$ORACLE_HOME/rdbms/admin/undopwd.sql
$ORACLE_HOME/rdbms/admin/undoaud.sql

For more details about Security changes in 11g;
http://docs.oracle.com/cd/E14072_01/network.112/e10574/whatsnew.htm

Introduction:

This article describes how to export / import table statistics from one user to another.
The goal is to import table statistics of the user wissem to a user wiss. The table conserned is named W_WRKFC_EVT_FS. Both users are in the same database. In the case of the users are created in different databases, the same steps can be used by using export/import datapump instead of create the table statistics using CTAS (Create Table As Select).

(more…)

Introduction:

This article describes how to manage optimal SQL execution plans using SQL plan management. SQL plan management preserves efficient execution plans and evaluates every changed plan. Plan changes can be due to the system change (Database upgrade, changes in database parameters, changes in optimizer parameters, changes in optimizer statistics, system configuration, etc …).
SQL plan management (SPM) is a proactive way to guarantee stable performance when system changes.

This article doesn’t cover all the SPM functions. But, it is a good start to understand some of the SPM capabilities.

Load changed SQL Text to the SQL plan baselines:

It is not always allowed to change the SQL Text of bad SQL. As part of tuning effort, the DBA can include hints, force the execution plan changes; change the table joins orders, etc …

In the following example, I am showing how to associate a good plan of a changed SQL Text to an already sub-optimal sql plan.

(more…)

Introduction:

I have been recently asked to investigate a performance issue in one 11gR2 Oracle database.
I have found that some queries need to be tuned so the whole instance performance is improved.
I am concerned with one query executed thousands of time; this query is using an index hint.

Of course hints can improve the performance of the queries but in some cases it can be catastrophic.
The developer who wrote this query used an index hint to force the optimizer choosing an index in the execution plan. Indexes can improve the performance but bad indexes can be dramatic and cause performance issues. Identifying the adequate indexes is not an easy task. In the following example, I am going to show you how an index can cause performance issues, further, how a hint can result in a bad execution plan. In this example, I am going to use the Sql Performance Analyzer (SPA) to compare / analyze the current implemented query in production (using an index) with a query that supposes to improve the performance (without an index).

(more…)