Guenadi N Jilevski's Oracle BLOG


What to check when an export “hangs”

What to check when an export “hangs”

If you are running Data Pump export, and it is hanging when exporting the object definitions and you are exporting the SYS and SYSTEM objects, but I don’t know how to diagnose the hanging issue in export what will you check when Oracle hangs while exporting object definitions?

Hanging in export can occur for a variety of reasons.  You can check v$session_wait for details, or run a 10046 trace to see exactly what’s wrong.

Common causes of exp hanging when exporting cluster or object definitions include:

– According to MetaLink note : 351650.1, check your LD_LIBRARY_PATH set improperly, without ORACLE_HOME/lib first

– There are known issues with “full” exports hanging when exporting SYS and SYSTEM objects. Some recommend running utlrp.sql to re-compile all objects to remedy an export hang.  If you don’t need to export the SYS and SYSTEM objects, I would simply redo it, specifying the exact schemas to export.

– Exports can hang if it runs out of disk space, especially on a 32-bit system with a 2-gig file size limit.  In this case, you need to run the split command to split out the dump file.

August 24, 2009 Posted by | oracle | Leave a comment

Oracle Wait Event Table

Oracle Wait Event Table

Prior to Oracle10g, capturing wait event information was a cumbersome process involving the setting of special events (e.g. 10046) and the reading of complex trace dumps. Fortunately, Oracle10g has simplified the way that wait event information is captured and there are a wealth of new v$ and wrh$ views relating to Oracle wait events.

Oracle10g has brand-new wait events and the database kernel now captures statistics on more than 800 specific wait events. These new wait events are the result of Oracle breaking-out their latch waits into their individual components and breaking-out enqueue waits (locks) into a finer level of granularity.

The foundation concept of the ASH architecture is called the “time model”, and Oracle10g has introduced several important new wait event v$ views.

V$ View                  DBA_HIST View
v$active_sess_hist       dba_hist_active_sess_history
v$sys_time_model         dba_hist_sys_time_model
v$active_session_history dba_hist_active_sess_history
v$event_histogram        No equivalent DBA view
Unlike the old-fashioned v$session and v$session_wait views (where you could only see waits at the exact instant when they occurred), the new v$session_wait_history and v$sys_time_model views allow Oracle10g to capture system waits details in a time-series mode. But wait, there’s more! Now let’s look at the new ASH tables structures.

What a great ASH
One of the most important area of Oracle10g wait event tuning is the Oracle10g Active Session History (ASH). ASH data is visualized through the v$active_sess_hist view and the wrh$active_session_history tables.

At a basic level, ASH stores the history of a recent session’s activity and facilitates the analysis of the system performance at the current time. ASH is designed as a rolling buffer in memory, and earlier information is overwritten when needed. ASH uses the memory of the SGA.

Another new innovation is the ability to use the new Oracle10g hash key for tracking session identification. This new hash key allows you to tracks common session processes and allows inter-cal session tracking in cases like OCI session ‘bouncing’ where each call to Oracle is a different session ID.

As we have already noted, the ASH samples for wait events every second and tracks the waits in the new v$active_sess_hist view. New data values are written to the wrh$ tables every hour, or when a new AWR snapshot is taken. In listing 203 below we see the Oracle10g WRH$ wait event table.

Now, let’s move away from the wrh$ tables and explore the new Oracle10g dba_hist views that are used to create time-series performance reports, both manually and within Enterprise Manager. We will begin with an overview of the dba_hist views and then show you examples of custom Oracle10g performance exception reports that can be easily generated from these views with SQL*Plus. For more details, note that these views are fully documented in the Oracle 10g Database Reference Manual.

The default collection retention for AWR data is only seven days. By using the new dbms package called dbms_workload_repository.modify_snapshot_settings., many Oracle DBAs will increase the storage of detail information over longer time periods. This will change the retention period and collection frequency, providing you with longer timer periods of data:

execute dbms_workload_repository.modify_snapshot_settings(
interval  => 60,
retention => 43200);
As you see, the retention period is indicated as 30 days (43200 min) while the interval between each snapshot is 60 min. You will see changes to these settings if you query the dba_hist_wr_control view after this procedure is executed.


Once we understand the AWR table data and inter-table relationships between AWR and performance metrics, we will be ready to understand how the WRH$ tables are used as input to the Automatic Memory Manager (AMM), the Automatic Database Diagnostic Monitor (ADDM), and the SQL Tuning Advisor.

The creation of AWR and ASH provides a complete repository for diagnosing and fixing any Oracle performance issue. The AWR provides the foundation for sophisticated performance analysis including exception reporting, trend analysis, correlation analysis, hypothesis testing and data mining.

August 17, 2009 Posted by | oracle | Leave a comment