UTL_SPADV

The DBA's time is precious. So, automate and stop wasting it on mundane and tedious tasks. That is what UTL_SPADV does; it automates the collection of statistics related to Streams performance. UTL_SPADV is simple to use and once configured, you can forget about it. Then take it to the next level and automated custom reports against the data collected.

UTL_SPADV has six subprograms. Following are their names along with their definitions:

  • UTL_SPADV.COLLECT_STATS
    UTL_SPADV.COLLECT_STATS(
interval IN NUMBER DEFAULT 60,
num_runs IN NUMBER DEFAULT 10,
comp_stat_table IN VARCHAR2 DEFAULT
'STREAMS$_ADVISOR_COMP_STAT',
path_stat_table IN VARCHAR2 DEFAULT
'STREAMS$_ADVISOR_PATH_STAT',
top_event_threshold IN NUMBER DEFAULT 15,
bottleneck_idle_threshold IN NUMBER DEFAULT 50,
bottleneck_flowctrl_threshold IN NUMBER DEFAULT 50);
  • UTL_SPADV.START_MONITORING
    UTL_SPADV.START_MONITORING(
job_name IN VARCHAR2 DEFAULT
'STREAMS$_MONITORING_JOB',
client_name IN VARCHAR2 DEFAULT NULL,
query_user_name IN VARCHAR2 DEFAULT NULL,
interval IN NUMBER DEFAULT 60,
top_event_threshold IN NUMBER DEFAULT 15,
bottleneck_idle_threshold IN NUMBER DEFAULT 50,
bottleneck_flowctrl_threshold IN NUMBER DEFAULT 50,
retention_time IN NUMBER DEFAULT 24);
  • UTL_SPADV.IS_MONITORING
    UTL_SPADV.IS_MONITORING(
job_name IN VARCHAR2 DEFAULT
'STREAMS$_MONITORING_JOB',
client_name IN VARCHAR2 DEFAULT NULL)
RETURN BOOLEAN;
  • UTL_SPADV.SHOW_STATS
    UTL_SPADV.SHOW_STATS(
path_stat_table IN VARCHAR2 DEFAULT
'STREAMS$_ADVISOR_PATH_STAT',
path_id IN NUMBER DEFAULT NULL,
bgn_run_id IN NUMBER DEFAULT -1,
end_run_id IN NUMBER DEFAULT -10,
show_path_id IN BOOLEAN DEFAULT TRUE,
show_run_id IN BOOLEAN DEFAULT TRUE,
show_run_time IN BOOLEAN DEFAULT TRUE,
show_optimization IN BOOLEAN DEFAULT TRUE,
show_setting IN BOOLEAN DEFAULT FALSE,
show_stat IN BOOLEAN DEFAULT TRUE,
show_sess IN BOOLEAN DEFAULT FALSE,
show_legend IN BOOLEAN DEFAULT TRUE);
  • UTL_SPADV.ALTER_MONITORING
    UTL_SPADV.ALTER_MONITORING(
interval IN NUMBER DEFAULT NULL,
top_event_threshold IN NUMBER DEFAULT NULL,
bottleneck_idle_threshold IN NUMBER DEFAULT NULL,
bottleneck_flowctrl_threshold IN NUMBER DEFAULT NULL,
retention_time IN NUMBER DEFAULT NULL);
  • UTL_SPADV.STOP_MONITORING(
    purge IN BOOLEAN DEFAULT FALSE);

Automating the collection of Streams performance data

Using the Streams setup of STRM1 to STRM2 at the beginning of this chapter we will do the following:

  1. Configure UTL_SPADV using defaults settings.
  2. Configure and confirm automated collection of data.
  3. Place load on STRM1 to be STREAMed to STRM2.
  4. Analyze the data.
  5. Change UTL_SPADV to more aggressive collection schedule.
  6. Use UTL_SPADV to stop collection of data.

The above should be done as [email protected], except where noted in Step 1.

  1. Configure UTL_SPADV using defaults settings

    For some unknown reason Oracle decided not to include the package UTL_SPADV as a default install. So, we have to do some additional work before using it.

    • As SYSDBA
      GRANT EXECUTE ON sys.dbms_lock TO STRM_ADMIN;
    • As STRM_ADMIN
      • Locate and run the utlspadv.sql script.
      • It can be found in ORACLE_HOME/ rdbms/admin directory
      • @utlspadv.sql

    Use UTL_SPADV.COLLECT_STATS to start things all off. Think of it as a manual collection of Advisor Runs for a small period. It also creates the STREAMS$_ADVISOR_COMP_STAT and STREAMS$_ADVISOR_PATH_STAT tables.

    exec UTL_SPADV.COLLECT_STATS

    By default, this runs for 10 minutes creating Advice Runs (think snapshots) every 60 seconds. We would suggest running it with the following, just to get the tables created and so we can move on to setting up the automated version:

    exec UTL_SPADV.COLLECT_STATS( interval => 30,
num_runs => 2);
  2. Configure and confirm automated collection of data.

    You first start of by using: 

    exec UTL_SPADV.START_MONITORING

    The Advice Runs are taken every 60 seconds and retained for 24 hours. We will modify this to be more aggressive and retain data for a longer period in a moment. It continues collecting until we stop it.

    To confirm that the Advice Runs are being collected, use: 

    SET SERVEROUTPUT ON
DECLARE
this_collecting BOOLEAN;
BEGIN
this_collecting := UTL_SPADV.IS_MONITORING(
job_name => 'STREAMS$_MONITORING_JOB'),
IF this_collecting=TRUE THEN
DBMS_OUTPUT.PUT_LINE('Good job collecting of advice is automatic.'),
ELSE
DBMS_OUTPUT.PUT_LINE('Nothing is being collected.'),
END IF;
END;
/
  3. Place load on STRM1 to be STREAMed to STRM2

    Just simply run the included script on STRM1, loop_50.sql to create inserts for the next 25 minutes. 

    declare
n_numb number := 1;
begin
loop
Insert into LEARNING.EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, TIME) Values (n_numb, 'Hello' || n_numb , 'You', NULL);
COMMIT;
n_numb := n_numb + 1;
DBMS_LOCK.SLEEP(.25); -- quarter second pause
exit when n_numb >= 6000; -- about 25 minutes
end loop;
end;
/
  4. Analyze the data
    -- run this if you used UTL_SPADV.COLLECT_STATS
spool run1.txt
SET SERVEROUTPUT ON SIZE 50000
BEGIN
UTL_SPADV.SHOW_STATS();
END;
/
spool off;

    Once automatic collection of Advisor Runs is set up, use the following: 

    -- run this if you used UTL_SPADV.START_MONITORING
spool run2.txt
SET SERVEROUTPUT ON SIZE 50000
BEGIN
UTL_SPADV.SHOW_STATS(path_stat_table => 'STREAMS$_PA_SHOW_PATH_STAT'),
END;
/
spool off;
  5. Change UTL_SPADV to more aggressive collection schedule

    Changing the collections of Advisor Runs is accomplished with the following: 

    -- more aggressive monitoring every 30 seconds
-- retain for 48 hours
BEGIN
UTL_SPADV.ALTER_MONITORING(
interval => 30,
retention_time => 48);
END;
/
  6. Use UTL_SPADV to stop collection of data.
    exec UTL_SPADV.STOP_MONITORING

The collection of data from the previous gathering of Advisor Runs will still be retained.

It is easy to use UTL_SPADV packages. There are some minor "issues".

  • You need to GRANT EXECUTE ON sys.dbms_lock TO STRM_ADMIN.
  • You have to install the package with @utlspadv.sql. It is not installed by default.
  • Format of output from UTL_SPADV.SHOW_STATS should be much easier to read.(I honestly think that Oracle support must have some kind of PERL script that makes the output of UTL_SPADV.SHOW_STATS more readable.)

But even with these minor "issues", the use of UTL_SPADV packages is similar to the use and evolution of DBMS_WORKLOAD_REPOSITORY PACKAGE. The UTL_SPADV packages will mature as more people use and ask for enhancements. Until then, the decision to use the UTL_SPADV.SHOW_STATS or to query the tables created by utlspadv.sql will be the choice of the Streams Administrator. The twelve core tables created are listed at the end of this chapter.

  • STREAMS$_PA_COMPONENT
  • STREAMS$_PA_COMPONENT_LINK
  • STREAMS$_PA_COMPONENT_PROP
  • STREAMS$_PA_COMPONENT_STAT
  • STREAMS$_PA_CONTROL
  • STREAMS$_PA_DATABASE
  • STREAMS$_PA_DATABASE_PROP
  • STREAMS$_PA_MONITORING
  • STREAMS$_PA_PATH_BOTTLENECK
  • STREAMS$_PA_PATH_STAT
  • STREAMS$_PA_SHOW_COMP_STAT
  • STREAMS$_PA_SHOW_PATH_STAT

Note

The DBA_STREAMS_TP_* and the STREAMS$_PA_* are very similar. They do not exactly match. But a good starting point is modifying the scripts supplied after the summary.

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

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