DBMS_STREAMS_ADVISOR_ADM

DBMS_STREAMS_ADVISOR_ADM has only one package and it is ANALYZE_CURRENT_PERFORMANCE. Using DBMS_STREAMS_ADVISOR_ADM is conceptually similar to using DBMS_WORKLOAD_REPOSITORY PACKAGE. You create snapshots to gather data into data dictionary views. Then you can either query the related views directly or use other packages to produce a report.

Note

Run all the scripts in the chapter as Streams Administrator. The Streams Administrator must have been granted all the necessary rights and permissions with DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE. We also set line size 200; or you can use the glogin.sql given in Chapter 5.

In Streams they did not call the gathering of data snapshots; that would be too easy. Instead, the Streams Administrator gathers data by asking Oracle to ANALYZE CURRENT PERFORMANCE for Streams. This is done by executing the following command:

exec DBMS_STREAMS_ADVISOR_ADM.ANALYZE_CURRENT_PERFORMANCE;

-- wait for some time period and then

exec DBMS_STREAMS_ADVISOR_ADM.ANALYZE_CURRENT_PERFORMANCE;

The commands above gather data related to the components and performance of your Streams environment. Waiting between ANALYZE CURRENT PERFORMANCE commands allows changes to be gathered. Essentially what happens is data is gathered from the first execution of ANALYZE CURRENT PERFORMANCE, waits for some time to pass, and executes ANALYZE CURRENT PERFORMANCE again. The difference between the first and second run is what is in the views that are mentioned as follows:

View Name

Answer the question of

Life of Data

DBA_STREAMS_TP_COMPONENT

What are the parts of the Streams environment?

Permanent

DBA_STREAMS_TP_COMPONENT_LINK

How do the parts relate to each other?

Permanent

DBA_STREAMS_TP_DATABASE

What databases are involved in the Stream?

Permanent

DBA_STREAMS_TP_COMPONENT_STAT

How are the parts doing?

Temporary

DBA_STREAMS_TP_PATH_STAT

How is the path doing?

Temporary

DBA_STREAMS_TP_PATH_BOTTLENECK

What might be causing a slow down?

Temporary

The image following is another way to understand what data is permanent or temporary in which view.

DBMS_STREAMS_ADVISOR_ADM

The data in the views on the left are permanent while the data in the views on the right are temporary. Taking a closer look at the tables reveals that the tables on the left relate to the "parts" of Streams, while the tables to the right are "performance" oriented. When you end your session that is performing queries above, then data related to performance is lost.

Tip

If you wish to persist this data in the temporary views, you can select it into a permanent table. For instance:

create table my_streams_tp_path_bottleneck as select * from dba_streams_tp_path_bottleneck

Making the map

The process of mapping out and gathering data about your Streams environment should be done in one session with the following steps:

  1. Collect the data needed into the tables mentioned previously with
    exec DBMS_STREAMS_ADVISOR_ADM.ANALYZE_CURRENT_PERFORMANCE;
    • -- wait for some time and
    exec DBMS_STREAMS_ADVISOR_ADM.ANALYZE_CURRENT_PERFORMANCE;
    • At a minimum, run two ANALYZE_CURRENT_PERFORMANCE.
  2. Querying the respective views in the previous figure. The following table has a listing of the supplied queries for this chapter. These queries are a modification of those supplied by Oracle.

Query Name

Purpose

ListDBs.sql

List out the databases that are part of the Streams environment

ListParts.sql

List out the parts of the Streams environment

ListPaths.sql

List the Path of the Streams environment

ListARs.sql

List out Advisor Runs that have been collected

ListBNs.sql

List out the Bottlenecks using the latest Advisor Run

(The views used in this query require Oracle Diagnostic Pack)

ListPerfParts.sql

List the performance of the parts of the Streams environment using the latest Advisor Run

ListPerfFlows.sql

List the flow and/or waits for the Streams environment the latest Advisor Run

ListPerfS2E.sql

List the performance from start to end of the Stream using the latest Advisor Run

Learn by doing. Just having these queries mean nothing unless you take the time and try them in your environment. So, here is the framework for using these queries. You need to create the Streams environment for this chapter. Setting up Streams multiple times is good practice and by now you should be comfortable with the process. To make things easier for you the scripts to build the environment pictured in the first figure are included.

  • 0000_CleanHouse.sql
  • 0025_Create_STRMADMIN_Both_Sides.sql
  • 0050_Create_Learning_Both_Sides.sql
  • 0100_Source_Grant_All.sql
  • 0200_Both_DB_Link.sql
  • 0300_Source_Setup.sql
  • 0400_Instantiate_Schema_Setup.sql
  • 0500_Destination_Setup.sql
  • 0600_Destination_Startup.sql
  • 0700_Source_Startup.sql

If you have a Streams Administer already created, you can use 0000_CleanHouse.sql and skip 0025_Create_STRMADMIN_Both_Sides.sql then run the remainder of the scripts in numeric order. The comments and instructions are in each script.

If you are starting from scratch, start with 0025_Create_STRMADMIN_Both_Sides.sql and do not use 0000_CleanHouse.sql. Then, run the remainder of the scripts, comments, and instructions in each script in numeric order.

Now that your environment is up and running, here are some suggestions on mapping your environment and learning its particular performance characteristics. Make sure to use the glogin.sql of Chapter 5 to configure your SQLPlus session display.

Build your Streams map by running these scripts in the order presented as follows:

  1. ListDBs.sql
  2. ListParts.sql
  3. ListPaths.sql

Take time to study the results of each query. The result from ListDBs.sql is easy to read and understand. As for ListParts.sql and ListPaths.sql, take some time and notice the order of the data. You should be able to build the Streams map from the result of running ListDBs.sql and ListParts.sql.

To cause a bottleneck, run BN_Exercise.sql. Use three separate SQL*Plus sessions connecting as follows:

  1. SYS on STRM2
  2. STRM_ADMIN on STRM1
  3. STRM_ADMIN on STRM2

The LOCK TABLE command is used to cause the bottleneck. Full instructions and comments are in the BN_Exercise.sql script.

To obtain an understanding of the following, you will need to use loop.sql to cause some load.

  1. ListPerfParts.sql
  2. ListPerfFlows.sql
  3. ListPerfS2E.sql

The loop.sql inserts records into LEARNING.EMPLOYEES at a rate of one record every half second for five minutes. While the load is running, run advise.sql a couple of times. Then run each of the ListPerf*.sql. Get a feel for each of the queries and what it provides. Note that each of the ListPerf*.sql will use the latest Advisor Run.

For a high-level view of how Streams is performing, run ListPerfS2E.sql. If performance is not what you expect then dig down using ListPerfParts.sql or ListPerfFlows.sql.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.129.45.92