|
|
|
12.4 Reporting on Database ChangesThe remaining sections in this chapter describe the various kinds of reports you can produce once you have collected the necessary baseline database data. This section focuses on database changes resulting from changes to database objects and parameters. The next section focuses on changes to the SQL execution plan. After collecting our first set of baseline data, we made some changes to the database, collected additional baseline data, made some more changes, and then collected even more baseline data. We did this for several weeks. 12.4.1 Database ChangesThe details and times of the changes made to the database are summarized in Table 12-4.
12.4.2 Reporting on Parameter Changes with spdrvr.plNow let's see how we can use the repository to engage in a form of time travel. Although less exciting than as H.G. Wells envisioned it, you'll effectively be able to go back in time to find elusive missing columns and corner privilege revocations. Pretend for a moment that the date is October 15. We've just become aware that the initialization file for one of our databases was recently modified and that, coincidentally, the server is now a little low on memory. We know that everything was correct a week ago, on October 8, but how do we determine what the settings were back then? Without our repository, there would be no easy way to determine the correct values, except by restoring a tape backup of the file as it appeared last week. This would probably be neither practical nor desirable. But with the repository, you can find these values as they appeared prior to the change. Because you know that the values from October 8 are correct, let's see which parameters have been changed since then. Let's produce a report detailing the difference. We can do that with the -rep_report parameter_diff_rpt command: $ spdrvr.pl -machine sherlock -database ts01 -username pdbarep \
-rep_report parameter_diff_rpt -rep_instance ts99.jks.com \
-rep_start_date '10/08/2001' -rep_end_date '10/15/2001'
Even though the first baseline was actually run on October 5, the spdrvr.pl script determines which baseline to use by searching for the most recent baseline date that is less than or equal to the date specified. The same type of operation takes place with the end date specified in the example by the following switch: -rep_end_date When using this switch, and its complement -rep_start_date, be sure to use a date format matching the setting in the pdbarepq.conf configuration file. If the parameter_diff_report were run with the International date format, the command line would look like this: $ spdrvr.pl -machine sherlock -database ts01 -username pdbarep \
-rep_report parameter_diff_rpt -rep_instance ts99.jks.com \
-rep_start_date '2001/10/08' -rep_end_date '2001/10/15'
The output from the parameter_diff_report report appears in Example 12-6. Note that data appears only when there are differences within values associated with the database parameters, as stored in PDBA_PARAMETERS. If, for example, there have been no changes to the Oracle initialization parameters between October 5 and October 13, there will be nothing to report. A parameter comparison on these two dates will reveal that nothing has changed. Example 12-6. Output from the parameter_diff_rpt reportPDBAREP Parameter Differences report Page: 1
as of 2001/10/13 03:09:37 compared to 2001/10/05 18:29:05
TS99.JKS.COM 2002/05/19 21:55:40
S S
E Y
S S
D M M M A
E O O O D
PARAMETER DESCRIPTION VALUE F D D D J
-------------------- -------------------- ---------- - - - - -
db_block_buffers Number of database b 1000 N N N N N
locks cached in memo
ry
job_queue_processes number of job queue 2 Y N I N N
processes to start
shared_pool_size size in bytes of sha 3145728 N N N N N
red pool
3 rows selected.
PDBAREP Parameter Differences report Page: 1
as of 2001/10/05 18:29:05 compared to 2001/10/13 03:09:37
TS99.JKS.COM 2002/05/19 21:55:40
S S
E Y
S S
D M M M A
E O O O D
PARAMETER DESCRIPTION VALUE F D D D J
-------------------- -------------------- ---------- - - - - -
db_block_buffers Number of database b 500 N N N N N
locks cached in memo
ry
job_queue_processes number of job queue 0 Y N I N N
processes to start
shared_pool_size size in bytes of sha 2097152 N N N N N
red pool
3 rows selected.
On the other hand, suppose that sometime between October 5 and October 13 you changed the database initialization parameters DB_BLOCK_BUFFERS, JOB_QUEUE_PROCESSES, and SHARED_POOL_SIZE. When you run the parameter_diff_rpt report, it would show the values of these parameters as they appeared on October 13, followed by the values as they appeared on October 5. Looking again at Example 12-6, you'll see that the report appears in two parts:
We now know what the correct settings were eight days ago, and we can change them back to the former values if necessary. The pdbarepq.conf configuration file contains templates of the SQL for each of the available repository reports. The command-line options of spdrvr.pl are used with these templates to generate the actual SQL used. Typical PDBA-generated SQL used to create this kind of report is shown in Example 12-7. Example 12-7. Sample query for spdrvr.plselect
s.global_name cinstance
, p.name parm_name
, p.description parm_description
, p.value parm_value
, decode(
nvl(p.isdefault,'FALSE'),
'FALSE','N',
'TRUE','Y',
substr(nvl(p.isdefault,'F'),1,1)
) isdefault
, decode(
nvl(p.isses_modifiable,'FALSE'),
'FALSE','N',
'TRUE','Y',
substr(nvl(p.isses_modifiable,'F'),1,1)
) isses_modifiable
, decode(
nvl(p.issys_modifiable,'FALSE'),
'FALSE','N',
'TRUE','Y',
substr(nvl(p.issys_modifiable,'F'),1,1)
) issys_modifiable
, decode(
nvl(p.ismodified,'FALSE'),
'FALSE','N',
'TRUE','Y',
substr(nvl(p.ismodified,'F'),1,1)
) ismodified
, decode(
nvl(p.isadjusted,'FALSE'),
'FALSE','N',
'TRUE','Y',
substr(nvl(p.isadjusted,'F'),1,1)
) isadjusted
from pdba_parameters p, pdba_snap_dates s
where s.global_name like 'TS99.JKS.COM'
and s.pk = 1000000
and s.pk = p.snap_date_pk
minus
select
s.global_name cinstance
, p.name parm_name
, p.description parm_description
, p.value parm_value
, decode(
nvl(p.isdefault,'FALSE'),
'FALSE','N',
'TRUE','Y',
substr(nvl(p.isdefault,'F'),1,1)
) isdefault
, decode(
nvl(p.isses_modifiable,'FALSE'),
'FALSE','N',
'TRUE','Y',
substr(nvl(p.isses_modifiable,'F'),1,1)
) isses_modifiable
, decode(
nvl(p.issys_modifiable,'FALSE'),
'FALSE','N',
'TRUE','Y',
substr(nvl(p.issys_modifiable,'F'),1,1)
) issys_modifiable
, decode(
nvl(p.ismodified,'FALSE'),
'FALSE','N',
'TRUE','Y',
substr(nvl(p.ismodified,'F'),1,1)
) ismodified
, decode(
nvl(p.isadjusted,'FALSE'),
'FALSE','N',
'TRUE','Y',
substr(nvl(p.isadjusted,'F'),1,1)
) isadjusted
from pdba_parameters p, pdba_snap_dates s
where s.global_name like 'TS99.JKS.COM'
and s.pk = 1001570
and s.pk = p.snap_date_pk
order by 1,2;
12.4.3 More Report ExamplesOn October 25, we decided to run some additional reports to see what might have changed in the ts99 database since the time that we started capturing repository metadata.[2] Example 12-8 shows different variants on the commands you can specify in order to view the data in different ways.
Example 12-8. Reports on database changes as of October 19$ spdrvr.pl -machine sherlock -database ts01 -username pdbarep \
-rep_report table_rpt -rep_instance ts99% -rep_schema scott \
-rep_end_date '10/25/2001'
$ spdrvr.pl -machine sherlock -database ts01 -username pdbarep \
-rep_report column_diff_rpt -rep_instance ts99% -rep_schema scott \
-rep_end_date '10/25/2001'
$ spdrvr.pl -machine sherlock -database ts01 -username pdbarep \
-rep_report table_privs_diff_rpt -rep_instance ts99% \
-rep_grantee scott -rep_end_date '10/25/2001'
$ spdrvr.pl -machine sherlock -database ts01 -username pdbarep \
-rep_report user_rpt -rep_instance ts99%
The reports table_rpt, column_diff_rpt, and table_privs_diff_rpt produce the outputs summarized here:
12.4.4 spdrvr.pl ImplementationBefore we look further at the kinds of reports you can produce with the spdrvr.pl script, let's dig down and take a quick look at its implementation and see why, despite our abiding love for Perl, we've also used SQL*Plus in our implementation. Have you ever had a household tool you love so much that you find yourself exploring the house from attic to basement, looking for ways to make use of it? That's the way we feel about Perl. However, as Clint Eastwood said once of a man's belief in himself, we've got to recognize its limitations. A screwdriver may sometimes get called up for reserve duty as a chisel, but that usage will impact its longevity as a screwdriver. And if you trim the hedges around your home with a circular power-saw, people are going to talk. We've come to realize, somewhat sadly, that Perl does indeed have its limits. The most glaring one we've noticed emerges when we're writing ad hoc SQL reports. Perl is a good choice when writing reports that demand lots of computation, but it fails to do things easily that long-time users of SQL*Plus take for granted. Here are a few examples:
12.4.5 Predefined spdrvr.pl ReportsThe spdrvr.pl repository script allows you to invoke a number of predefined database reports simply by specifying the appropriate command-line options. Several reports are predefined for you.
12.4.6 Command-line Options for spdrvr.plYou can request the reports listed in the previous section by including the appropriate command-line options when you invoke the spdrvr.pl repository script. Table 12-5 and Table 12-6 summarize these options. 12.4.6.1 Common command-line optionsYou will need to include most of the options in Table 12-5 regardless of what report you want to produce. You can obtain a list of available reports and their required command-line options by typing: $ spdrvr.pl -report_list We'll show the output from this command a little later in this section.
The arguments for -machine, -database, -username, and -rep_report are always required. The argument for -rep_database is optional but recommended. The use of -rep_database really depends on the nature of the report. If you want to find out what changes have been made to a table on a specific database between two dates, you'll need to specify which database the report should be querying on. If you omit this option, any tables from other databases with the same name will be included in the output. (This is probably not what you want.) Let's suppose you want to run the report table_rpt for the database ts99.jks.com and that the repository owned by pdbarep is in database ts01 on server sherlock. The minimal command line needed to run this report would be: $ spdrvr.pl -machine sherlock -database ts01 -username pdbarep \
-rep_instance 'ts99%' -rep_report table_rpt
To include only tables with "PSAP" as the first four characters of their name, within accounts that begin with "SAP," the command line would look like this: $ spdrvr.pl -machine sherlock -database ts01 -username pdbarep \
-rep_instance 'ts99%' -rep_report table_rpt \
-rep_schema 'sap%' -rep_table_name 'psap%'
12.4.6.2 Report-specific command-line optionsTable 12-6 lists the report-specific command-line options. For example, you might use the -rep_privilege option if you are requesting the table_privs_rpt report, but that option would have no effect on the parameter_rpt that lists database initialization parameters.
No single report makes use of all of these switches, although some are used in several reports. 12.4.6.3 Using the -report_list optionThe first time you run the spdrvr.pl script, you should use the -report_list option, which prints out a list of currently configured reports along with the report-specific command-line options that may be used with it: $ sprdrvr.pl -report_list Go ahead and try it. The output should be similar to that displayed in Example 12-12 (although we've cut down the actual output, as it can run to several pages). Example 12-12. Partial output from "spdrvr.pl -report_list"column_diff_rpt : report on differences in table columns in repository may be delimited by a date range. if dates not supplied, the oldest and newest dates from the repository will be used may be limited by the following tags: <<GLOBAL_NAME>> -rep_instance <<OWNER>> -rep_schema <<TABLE_NAME>> -rep_table_name <<START_DATE_PK>> -rep_start_date <<END_DATE_PK>> -rep_end_date master_priv_rpt : report on privileges granted in repository may be limited by the following tags: <<GLOBAL_NAME>> -rep_instance <<GRANTEE>> -rep_grantee <<PRIVILEGE>> -rep_privilege <<OBJECT_OWNER>> -rep_object_owner <<OBJECT_NAME>> -rep_object_name <<GRANTED_ROLE>> -rep_granted_role parameter_diff_rpt : report on database parameter differences in repository may be delimited by a date range. if dates not supplied, the oldest and newest dates from the repository will be used may be limited by the following tags: <<GLOBAL_NAME>> -rep_instance <<START_DATE_PK>> -rep_start_date <<END_DATE_PK>> -rep_end_date sys_privs_diff_rpt : report on differences in system privileges in repository may be limited by the following tags: <<GLOBAL_NAME>> -rep_instance <<GRANTEE>> -rep_grantee <<PRIVILEGE>> -rep_privilege <<START_DATE_PK>> -rep_start_date <<END_DATE_PK>> -rep_end_date table_rpt : report on tables in repository may be limited by the following tags: <<GLOBAL_NAME>> -rep_instance <<OWNER>> -rep_schema <<TABLE_NAME>> -rep_table_name 12.4.7 Options and TagsEach command-line option shown in Example 12-12 is associated with a tag enclosed in a double set of angle brackets: <<TAG>>. The column_diff_rpt report has these tags associated with the following command-line options: <<GLOBAL_NAME>> -rep_instance <<OWNER>> -rep_schema <<TABLE_NAME>> -rep_table_name <<START_DATE_PK>> -rep_start_date <<END_DATE_PK>> -rep_end_date These tags are used internally within spdrvr.pl to replace values in a SQL script, values that will later get sent to SQL*Plus. The tags are divided into two types, date and text, as discussed in the following sections. 12.4.7.1 Date optionsThe -rep_start_date and -rep_end_date command-line options are the only date-style options. The tags usually associated with these options are <<START_DATE_PK>> and <<END_DATE_PK>>.
This logic may seem rather convoluted, but trust us, it does make reports easier to run. Let's look at an example. Suppose that midway through November you suspect that changes have been made to the HELP_DESK schema. You know that last month everything worked fine — but now there's a problem. To figure out what's going on, you won't need to check every date in the repository. You can simply enter the approximate range and let spdrvr.pl determine the actual dates. The following reports all changes: $ spdrvr.pl -machine sherlock -database ts01 -username pdbarep \
-rep_report column_diff_rpt -rep_instance ts99% \
-rep_schema help_desk \
-rep_start_date '2001/10/01' \
-rep_end_date '2001/11/12'
The actual report dates used would be October 5 and November 8. 12.4.7.2 Text optionsMost of the command-line options on the spdrvr.pl script are text options. These include all the database objects — users, tables, tablespaces, indexes, and so on. We can be somewhat inexact in specifying these text strings. Most of the SQL queries found in pdbarepq.conf use the LIKE operator, rather than the = equality operator. This allows the use of the % wildcard for text-based columns, used in WHERE clauses. The following command requests a report on which roles have been granted to which users, where the grantee's name begins with an S: spdrvr.pl -machine sherlock -database ts01 \ -username pdbarep -rep_database ts99% \ -rep_report role_privs_rpt -rep_grantee s% If you wish to see the actual SQL generating the report, add the -verbose option to the command line. In our example, the SQL looks like this: select s.global_name cinstance , p.grantee , p.granted_role , p.admin_option , p.default_role , s.snap_date from pdba_role_privs p, pdba_snap_dates s where s.global_name like 'TS99%' and p.grantee like 'S%' and s.pk between 1000000 and 1006331 and s.pk = p.snap_date_pk order by global_name, grantee, granted_role, snap_date; Notice how the switches -rep_database ts99% and -rep_grantee s% uppercase their corresponding values in the SQL statement. (The % wildcard may be used in any of the non-date command-line arguments.) |
|
|
|
