Author: mac

Oracle 10gR2 and higher automatically tunes the undo retention period based on how the undo tablespace is configured. If the undo tablespace is configured with the AUTOEXTEND option, the database dynamically tunes the undo retention period to be somewhat longer than the longest-running active query on the system. However, this retention period may be insufficient…

One of the main new features associated with Exadata systems is that I/O can be offloaded to storage servers rather than be done on the database server. Each of the storage servers may get a piece of the SQL statement to operate on, so the processing is also parallelized at the same time. This saves…

The term “seconds in wait” means the number of seconds that a SQL Statement, Database User, etc. was waiting on an event. It is possible that the total number of “seconds in wait” exceeds the total amount of time in the period. The following example will illustrate how this occurs.   Example: Assume that one…

Round-trip time when running “select 1 from dual” (includes network time but not connect time) on this database.       Solution If this is high, contact your network administrator to understand network latency.

Shareable SQL uses bind variables rather than literal values. If an application makes use of literal (unshared) SQL then this can severely limit scalability and throughput. The cost of parsing a new SQL statement is expensive both in terms of CPU and the number of times the library cache and shared pool latches may need…

Contention caused by communication between parallel query processes.       Solutions Decrease the degree of parallelism for the queries Tune the parallel_execution_message_size parameter (may require larger amounts of memory) Disable parallel query if these waits taken longer than a serial run (parallel_max_servers=0)

Occurs when a parallel slave is waiting for data from another slave. Investigate why the other slaves are not sending data fast enough. This is normally considered an idle event, but can cause excessive CPU in some cases.       Solution Reduce the degree of parallelism in the query.

Occurs when a parallel slave is waiting to be told what to do. This is normally considered an idle event, but can cause excessive CPU in some cases.       Solution Reduce the degree of parallelism in the query if excessive CPU usage is detected.

Occurs when the query coordinator is waiting for a response from a parallel slave. This is normally considered an idle event, but can cause excessive CPU in some cases.       Solution Reduce the degree of parallelism in the query if excessive CPU usage is detected.

The library cache (a component of the shared pool) stores the executable (parsed or compiled) form of recently referenced SQL and PL/SQL code. Oracle tries to reuse this code. If the code has been executed previously and can be shared, Oracle will report a library cache hit. If Oracle is unable to use existing code,…