Quick Guide: New Metric

From zzat
Jump to: navigation, search

Prerequisites

To successfully complete this guide as a hands-on experience, you should already have a working zztat installation:

  • zztat repository
  • At least one zztat target database
  • The framework must be started on both the repository and the target databases
  • The target database must be able to communicate with the repository and be properly initialized. How do I check?

Introduction

Step-by-step guide illustrating how to create a new metric and what exactly goes on under the covers when you do so.

To create a new metric, you require the following:

  • A data source
  • The metric query to read said data source
  • A database table for zztat to store the data in

In this guide, we will be creating a standard metric that collects data about users connecting to the target database. Once this guide is complete, you will end up with the following:

  • A set of database tables that store the metric data
  • A snapshot job that collects data every 5 minutes
  • A replicate job that copies data to the repository every 10 minutes

Data Source

The data source for our example metric will be v$session. This is the view that our metric query is going to read from. For zztat to successfully use a data source as specified in the metric query, the data source must be accessible to zztat (the appropriate grants, and/or private synonyms must be in place).

Since our example uses v$session, which is monitored by zztat's default metrics, we do not need to add any other privileges. If you are planning to add another data source, ensure that the following privilege is granted, as shown here for v$session:

grant select on sys.v_$session to zz$user
/

Notes

  • The username under which zztat runs is configurable. If you have changed the zztat target database application user during installation, you have to change the above statement to issue the grant to the proper user. The user name can also be found in the zz$schema database table, if you prefer to create a dynamic script, for example.
  • If your data source is an X$-table, you need to take additional steps (do NOT create a view and grant select on that - zztat has a better mechanism, which is described here).
  • Ensure that you are running the above grant statement with a privileged user (either SYS or an account with DBA-privileges).

Metric Query

The metric query is zztat's interface to the data source and connects the data to zztat's framework. Metric queries need to follow certain standards for zztat to be able to use them efficiently. Those standards are documented here. To keep with the flow, the essential requirements we need to be aware of in the scope of this guide are listed here.

Our query will be accessing v$session and collect basic user statistics. To provide integration with zztat, we need to add the zztat query header to our query:

select /*+ ##ZZHINT## zz$q */ :zzdb, :zztat,

Combined with the query against v$session and the columns we want to capture, the complete metric query becomes:

select /*+ ##ZZHINT## zz$q */ :zzdb, :zztat,
       sid, serial#, username, status, module, action, service_name
  from sys.v_$session
 where username is not null

For further details on why the ZZHINT placeholder and those two additional bind variables are there, please see the page on metric queries here.

With that, we're ready to now prepare the storage for our new metric.

Database Table

The final thing you will need is a table that can store the data produced by the metric. In the current version of zztat, you have to create the table yourself, and name it accordingly so that zztat will be able to find it - in future versions this may change, and zztat will construct the table automatically from the metric query.

Our metric will be called EX_SESSION . We therefore need to create a table called ZZ$EX_SESSION.

create table zz$ex_session
(
  zz$db        int           constraint zz$ex_session_db_nn not null,
  snap#        int           constraint zz$ex_session_s#_nn not null,
  sid          number        constraint zz$ex_session_si_nn not null,
  serial#      number        constraint zz$ex_session_se_nn not null,
  username     varchar2(30)  constraint zz$ex_session_un_nn not null,
  module       varchar2(64),
  action       varchar2(64),
  service_name varchar2(64)
)
/

Notes

  • As you can see above, our table also includes the two internal zztat columns zz$db and snap#. Those must be present in every metric storage table. The rest of the table definition is entirely up to you - whether or not to add constraints, indexes, etc. If, for example, you're on Oracle Enterprise Edition and have the partitioning option licensed, feel free to partition the table and bypass zztat's partition view functionality entirely. Full partitioning support may be added to zztat in a future release, if there is demand.
  • Ensure that you perform any steps that follow while connected to the zztat repository database, as the zztat repository user (by default that user is called zz$repo).
  • All zztat metric tables must follow the naming standard of "ZZ${metric name}" (in upper case).
  • Creating this table is all you need to do - and you only need to do this on the repository. The staging table on the target databases will be created by zztat automatically - using this table as a template.
  • If, for some reason, you wish to create the staging table yourself as well (for example if it needs to be an IOT or a hash cluster), you may do so. If the metric table already exists when zztat attempts to create it during metric synchronization, it will use the existing table.

Creating the Metric definition

Now that we have everything we need, we are ready to add the metric to zztat:

begin
zz$manage.metric_add
(
  name                    => 'EX_SESSION',
  snap_interval           => 5,
  snap_unit               => 'MINUTE',
  replicate_interval      => 10,
  replicate_unit          => 'MINUTE',
  part_interval           => 'MONTH',
  comments                => 'Sample session metric to collect user connection data',
  query                   => q'[
select /*+ ##ZZHINT## zz$q */ :zzdb, :zztat,
       sid, serial#, username, status, module, action, service_name
  from sys.v_$session
 where username is not null
]'
);
end;
/

Let's take a closer look at what we've executed here:

  • The metric name is EX_SESSION.
  • Snapshot interval has been set to 5 minutes.
  • Replicate interval has been set to 10 minutes.
  • Soft-partitioning interval has been set to monthly. The framework will automatically create a new table each month - at least a week ahead.
  • No flags have been passed to the metric_add procedure - this will cause it to create a standard metric.

Notes

  • When you're running this, the metric is created on the repository. It will take up to two minutes for the metric to appear on the target database(s).
  • Alternatively, you can connect to the application user on the target database and run zz$manage.metric_refresh; ( for more details on zz$manage see here)
  • You can see zztat "live" in action by opening a second terminal and running zztail.sh at the same time you are executing this command. You may also run zztail.sh on the target database and you will see instantly when the new metric arrives and is prepared on the target database(s).

Next?

You can now take a look at the archive tables that zztat has created from your ZZ$EX_SESSION template table. Run the following script to see them:

col table_name for a30
col partition_name for a30
col upper_bound for a40
set lines 200 pages 999
select table_name, partition_name, upper_bound from zz$part_idx where table_name = 'ZZ$EX_SESSION'
order by partition_name
/

You may also want to check the statistics on your new metric and see how many times it has executed. Run the following script once some time has passed to see the details:

col name for a10
col begin_time for a40
col end_time for a40
select d.name, s.snap#, s.begin_time, s.end_time
  from zz$db d, zz$snap s
 where d.zz$db = s.zz$db
   and s.metric = 'EX_SESSION'
 order by s.snap#
/