Performance Advisor - Oracle 11g

Oracle Database 11g introduces another advisor to troubleshoot configu ration issues and performance bottlenecks associated with your Streamsenvironment.The Streams Performance Advisor uses database links toperform discovery of the Streams configuration.

The overall objective of the Streams Performance Advisor is to recommend areas that can be modified for better performance such as identifying latency and throughput for each separate Streams component.

Additional information collected includes calculating bottleneck components, top wait events, message rates,and transaction rates.When the Performance Advisor collects statistics about the Streams environment, it works backward.

It starts from the apply process and works backward to the capture process. Along the way,the Streams Performance Advisor assigns a component ID to each component and a path ID to each path.

Streams Advisor Admin Package

The DBMS_ STREAMS_ ADVISOR_ ADM package comes with a procedure namedanalyze_ current_ performance to gather and diagnose Streams performancestatistics. This package in conjunction with a set of views—referred to as the Streams topology views—make up the Streams Performance Advisor.By default, this procedure does not require any parameters.

Argument Name Type In/Out Default?
---------------- --------- ------ ---------

First you must execute the analyze_current_performance procedure as the Streams administrator account:

execute dbms_streams_advisor_adm.analyze_current_performance;

This will allow the dynamic Streams views to be populated with thefollowing information pertaining to the Streams environment:

  • Capture process
  • Propagation senders and receivers
  • Apply process
  • Queue

When you execute the analyze_current_performance procedure,you should run it more than once in the same session, especially if you are looking for rate, bandwidth,and event and flow control statistics.

If you are looking for just the number of current messages in a queue, a single execution will provide you with this information.

Also,your goal is to keep the Streams topology statistics current.You should reexecute this procedure when any components are added to any database in the Streams environment

Streams Advisor Admin Package

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

Oracle 11g Topics