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.
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 |
---|---|---|
|
What are the parts of the Streams environment? |
Permanent |
|
How do the parts relate to each other? |
Permanent |
|
What databases are involved in the Stream? |
Permanent |
|
How are the parts doing? |
Temporary |
|
How is the path doing? |
Temporary |
|
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.
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.
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
The process of mapping out and gathering data about your Streams environment should be done in one session with the following steps:
exec DBMS_STREAMS_ADVISOR_ADM.ANALYZE_CURRENT_PERFORMANCE;
exec DBMS_STREAMS_ADVISOR_ADM.ANALYZE_CURRENT_PERFORMANCE;
ANALYZE_CURRENT_PERFORMANCE
.
Query Name |
Purpose |
---|---|
|
List out the databases that are part of the Streams environment |
|
List out the parts of the Streams environment |
|
List the Path of the Streams environment |
|
List out Advisor Runs that have been collected |
|
List out the Bottlenecks using the latest Advisor Run (The views used in this query require Oracle Diagnostic Pack) |
|
List the performance of the parts of the Streams environment using the latest Advisor Run |
|
List the flow and/or waits for the Streams environment the latest Advisor Run |
|
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:
ListDBs.sql
ListParts.sql
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:
STRM2
STRM_ADMIN
on STRM1
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.
ListPerfParts.sql
ListPerfFlows.sql
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
.