1. Queries with high cluster overhead
Queries no tuned can also be an overhead to performance across the cluster, causing high delays. In Oracle Database 10g, four new columns have been introduced to help identify queries that are perfoming poorly in general and specially in a RAC environment.
Using the CLUSTER_WAIT_TIME column in the GV$SQLSTATS view, queries that are experiencing cluster-related waits can be identified and tuned. For example, the following lists the SQL queries, giving the wait times experienced at various stages of the operation:
SELECT INST_ID INST,
SQL_ID,
APPLICATION_WAIT_TIME AWT,
CONCURRENCY_WAIT_TIME CONWT,
CLUSTER_WAIT_TIME CLWT,
USER_IO_WAIT_TIME UIWT
FROM GV$SQLSTATS
WHERE USER_IO_WAIT_TIME > 0
ORDER BY USER_IO_WAIT_TIME;
Once the queries with high cluster wait time have been identified, a specific query can be retrieved using the SQL_ID.
SQL> SELECT SQL_FULLTXT FROM GV$SQLSTATS WHERE SQL_ID=’19v5guvsgcd1v’;
2. consistent read block activity, use this formula
(gc cr block receive time * 10) / (gc cr block received)
SELECT
gc_cr_block_receive_time AS “Receive Time”,
gc_cr_block_received AS “Blocks Received”
,(gc_cr_block_receive_time * 10) / gc_cr_block_received AS “Average Latency (MS)”
FROM
(
SELECT value AS gc_cr_block_receive_time FROM v$sysstat
WHERE name = ‘gc cr block receive time’
),
(
SELECT value AS gc_cr_block_received FROM v$sysstat
WHERE name = ‘gc cr blocks received’
)
/
The latency of a consistent block request is the time elapsed between the original request and the receipt of the consistent block image at the local instance. Using Gigabit Ethernet interconnect, this value should normally be less than 5 ms and should not exceed 15 ms, although this can be affected by system configuration and volume.
In this example, you see that the latency is 24 ms and therefore warrants further investigation and volume. In this example, you see that the latency is 24 ms and therefore warrants further investigaion You should first use the Linux operating system utilities, such as netstat detailed later inthis chapter, to determine whether there are any network configuration issue,
such as network packet send and receive errors. You should also use operating system utilities such as network packet send and receive errors. You should also use operating system utilities such as top and sar to measure the load on the nodes themselves.
If you have ensured that the interconnect is configured correctly and you are still experiencing high average latencies, consider reducing the value of DB_FILE_MULTIBLOCK_READ_COUNT. This parameter specifies the number of blocks a process will request in a single operation. The process will have adjust this parameter, carefully assess the impact on the entire workload.
High average latencies may also be caused by a high number of incoming requests or multiple nodes dispatching requests to the LMS process.
3.global cache activity
SELECT
table_name AS “Table Name”
,gc_buffer_busy AS “Buffer Busy”
,gc_cr_blocks_received AS “CR Blocks Received”
,gc_current_blocks_received AS “Current Blocks Received”
FROM
(
SELECT table_name FROM dba_tables
WHERE owner = ‘PVGD’
) t,
(
SELECT object_name, value AS gc_buffer_busy
FROM v$segment_statistics
WHERE owner = ‘PVGD’
AND object_type = ‘TABLE’
AND statistic_name = ‘gc buffer busy’
) ss1,
(
SELECT object_name, value AS gc_cr_blocks_received
FROM v$segment_statistics
WHERE owner = ‘PVGD’
AND object_type = ‘TABLE’
AND statistic_name = ‘gc cr blocks received’
) ss2,
(
SELECT object_name, value AS gc_current_blocks_received
FROM v$segment_statistics
WHERE owner = ‘PVGD’
AND object_type = ‘TABLE’
AND statistic_name = ‘gc current blocks received’
) ss3
WHERE t.table_name = ss1.object_name
AND t.table_name = ss2.object_name
AND t.table_name = ss3.object_name
;
4.current blocks activity
SELECT
gc_current_block_receive_time AS “Receive Time”,
gc_current_blocks_received AS “Blocks Received”,
(gc_current_block_receive_time * 10 ) / gc_current_blocks_received AS “Average (MS)”
FROM
(
SELECT value AS gc_current_block_receive_time
FROM v$sysstat
WHERE name = ‘gc current block receive time’
),
(
SELECT value AS gc_current_blocks_received
FROM v$sysstat
WHERE name = ‘gc current blocks received’
);
5.ges activity
SELECT
global_enqueue_get_time AS “Get Time”,
global_enqueue_gets_sync AS “Synchronous Gets”,
global_enqueue_gets_async AS “Asynchronous Gets”,
(global_enqueue_get_time * 10) /
(global_enqueue_gets_sync + global_euqueue_gets_async)
AS “Average (MS)”
FROM
(
SELECT value AS global_enqueue_get_time
FROM v$sysstat
WHERE name = ‘global enqueue get time’
),
(
SELECT value AS global_enqueue_gets_sync
FROM v$sysstat
WHERE name = ‘global enqueue gets sync’
)
(
SELECT value AS global_enqueue_gets_async
FROM v$sysstat
WHERE name = ‘global enqueue gets async’
);
Synchronous gets are usually locking events, whereas asynchronous gets are usually caused by nonblocking interinstance process activity.
In the preceeding example, the average global enqueue time is 14.6ms, which is within an acceptable range. If this figure is greater than 20ms