Kerry Osborne's Oracle Blog

Oracle 10g came with a new version of Statspack – they called it AWR, but basically it’s the same old estat/bstat report. There are of course a few new things in it and the snapshot process is automatically configured to collect data once per hour when you create a 10g instance. Unfortunately, this “new” feature is part of the separately licensed Diagnostics Pack, despite the fact the there is no easy way to disable the data collection. I posted earlier on Oracle Management Packs and associated licensing here, by the way.

But Statspack is still available and still runs fine if you choose to install it. As a matter of fact, it still exists in 11gR1 and has in fact been updated to accommodate some of the data dictionary changes in 11g. It looks a little like an after thought though as there are a couple of problems with it. The script to set it up is still in the $ORACLE_HOME/rdbms/admin directory and it’s still called spcreate.sql. This script creates the PERFSTAT user and the tables and necessary code objects.

The Statspack report looks very similar to the AWR report. It has basically the same Header Section, the same Wait Events Section, the same SQL Statements Ordered By XXX Sections, the same Tablespace and File I/O Sections, the same Buffer Pool Advisory Section, and the same Non-Default INIT.ORA Parameters Section at the bottom. Hey, this is the same thing!

The best things about Statspack:

1. It is very, very similar to AWR
2. It’s Open Source (sort of) – AWR is wrapped, but Statspack isn’t. You can modify it to suit your own taste. 
3. It’s still free!

  Continue reading ‘Statspack Still Works in 11g’ »

There has been quite a bit of confusion about the licensing of Oracle Management Packs and about their functionality. Here are a couple of links to the Oracle Docs which describe what’s not allowed without licensing these packs.

10gR2 Management Pack Licensing

11gR1 Management Pack Licensing

A couple of things to be aware of.

1. They are only available with Enterprise Edition
2. The Tuning Pack depends on the Diagnostic Pack 

11gR1 includes a new parameter (CONTROL_MANAGEMENT_PACK_ACCESS) which controls access to features based on the Diagnostic and Tuning Packs at the database level. The valid values for this parameter are NONE, DIAGNOSTIC, and DIAGNOSTIC+TUNING. Setting this parameter to NONE disables several features at the database level including AWR (which is the only feature I’ll discuss in this post). By the way, the default is TUNING+DIAGNOSTICS, so AWR is still enabled by default. Also, note that snapshots are still created, even if the parameter is set to NONE, but they don’t contain any data. So they are not just limiting access to the reporting capability, but also disabling the collection of data. Here’s a quick example on a 11.1.0.7 database:

SQL> @test_cmpa
SQL> --
SQL> -- Test setting control_management_pack_access=NONE
SQL> --
SQL> select name,value from v$parameter where name like '%pack%';

NAME                                                                             VALUE
-------------------------------------------------------------------------------- ------------------------------
control_management_pack_access                                                   DIAGNOSTIC+TUNING

SQL> select snap_id, begin_interval_time, snap_level, snap_flag from dba_hist_snapshot
  2    where snap_id = (select max(snap_id) from dba_hist_snapshot);

   SNAP_ID BEGIN_INTERVAL_TIME       SNAP_LEVEL  SNAP_FLAG
---------- ------------------------- ---------- ----------
      3826 27-OCT-08 08.12.32.405 PM          1          1

SQL> select count(*) from dba_hist_sysstat
  2    where snap_id = (select max(snap_id) from dba_hist_snapshot);

  COUNT(*)
----------
       500

SQL> alter system set control_management_pack_access=NONE;

System altered.

SQL> select name,value from v$parameter where name like '%pack%';

NAME                                                                             VALUE
-------------------------------------------------------------------------------- ------------------------------
control_management_pack_access                                                   NONE

SQL> @awr_snap
SQL> exec dbms_workload_repository.create_snapshot();

PL/SQL procedure successfully completed.

SQL> select snap_id, begin_interval_time, snap_level, snap_flag from dba_hist_snapshot
  2    where snap_id = (select max(snap_id) from dba_hist_snapshot);

   SNAP_ID BEGIN_INTERVAL_TIME       SNAP_LEVEL  SNAP_FLAG
---------- ------------------------- ---------- ----------
      3827 27-OCT-08 08.13.10.553 PM          1          5

SQL> select count(*) from dba_hist_sysstat
  2    where snap_id = (select max(snap_id) from dba_hist_snapshot);

  COUNT(*)
----------
         0

Continue reading ‘Oracle Management Packs’ »

Here’s a link to the presentation materials from a talk I did last night. It was an internal consultants meeting for Enkitec. The zip file contains a power point file and two text files with examples of a couple of new features (the new results cache feature and the so-called Real-Time SQL Monitoring). Anyway, the presentation is a modified version of one I did last week for an Oracle Tech day (it’s a little more focused than that one). 

So anyway, here’s the link: 11g Advanced Features

Comments are always welcome.

One of the interesting new features of 11gR1 is the automatic tracking of long running SQL statements. Oracle calls this new feature Real-Time SQL Monitoring. There a couple of parameters that must be set to enable this behavior.

• Parameter STATISTICS_LEVEL must be set to ALL or TYPICAL (the default)
• Parameter  CONTROL_MANAGEMENT_PACK_ACCESS must be set to  DIAGNOSTIC+TUNING (the default)

Not all statements are tracked. Only statements that are consider long running invoke this new facility. The following two conditions qualify a statement for tracking:

• The statement must take more than 5 seconds (or so) to execute
•  Or the statement is executed in parallel mode

There are a couple of new views that have been added to expose this feature. They are V$SQL_MONITOR and V$SQL_PLAN_MONITOR. The data appears to be updated once per second and it contains very detailed information including row counts for each step in the plan. These views contain a record of each execution of the qualifying statements. While it is possible to query these views directly, a function (REPORT_SQL_MONITOR) has been provided in the DBMS_SQLTUNE package that produces a nicely formated report in html, xml, or (my favorite) text format . Here is a script that queries the V$SQL_MONITOR view (sql_monitor.sql) and another that executes the afore mentioned function (report_sql_monitor.sql). Here is an example of their usage:

  Continue reading ‘Oracle 11g Real Time SQL Monitoring’ »

The Oracle Explain Plan command is widely used to evaluate the plan that the Oracle optimizer will choose for a given SQL statement. Unfortunately, it doesn’t always tell the truth. This is due to the fact that the Explain Plan statement does not go through the same code path that the optimizer uses when determining a plan for execution. One of the simplest examples of this behavior is the case where bind variables are used in a statement. Explain plan ignores them while the optimizer uses them to determine the plan. Here’s an example.

SQL> @test_bind
SQL> var how_many number
SQL> accept col1 -
>        prompt 'Enter value for col1: '
SQL>
SQL> DECLARE
2  x number;
3  BEGIN
4
5  :how_many := 0;
6  x := &col1;
7
8  select avg(pk_col) into :how_many
9  from kso.skew
10  where col1 = x;
11
12  dbms_output.put_line(trunc(:how_many)||' records.');
13
14  END;
15  /
16487500 records.

PL/SQL procedure successfully completed.

SQL>
SQL> undef col1
SQL> undef how_many
SQL>
SQL> @find_sql
Enter value for sql_text: select avg(pk_col) from kso.skew where col1 =%
Enter value for sql_id:

SQL_ID         CHILD  PLAN_HASH      EXECS     AVG_ETIME      AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ -----------------------------------------
7rf1jw8jsw0v7      0  568322376          1         48.32      173,734 SELECT AVG(PK_COL) FROM KSO.SKEW WHERE COL1 = :B1

SQL> -- Only 1 plan for this statement
SQL>
SQL>@dplan
Enter value for sql_id: 7rf1jw8jsw0v7
Enter value for child_no: 0

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  7rf1jw8jsw0v7, child number 0
-------------------------------------
SELECT AVG(PK_COL) FROM KSO.SKEW WHERE COL1 = :B1

Plan hash value: 568322376

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       | 31720 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    11 |            |          |
|*  2 |   TABLE ACCESS FULL| SKEW |  3150K|    33M| 31720  (37)| 00:00:43 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("COL1"=:B1)

19 rows selected.

SQL> -- It did a full table scan expecting 3M rows
SQL>-- Now lets see what Explain Plan thinks it should have done
SQL>
SQL> explain plan for select avg(pk_col) from kso.skew where col1 = :b1;

Explained.

SQL> @xplan

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3723858078

------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |     1 |    11 |    53   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE              |           |     1 |    11 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| SKEW      |    53 |   583 |    53   (2)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | SKEW_COL1 |    54 |       |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1
2 - SEL$1 / SKEW@SEL$1
3 - SEL$1 / SKEW@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("COL1"=TO_NUMBER(:B1))

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - (#keys=0) AVG("PK_COL")[22]
2 - "PK_COL"[NUMBER,22]
3 - "SKEW".ROWID[ROWID,10]

29 rows selected.

SQL> -- It thinks it will use an index (and that it will get only 54 rows - even though the histogram knows better
SQL>
SQL> select count(*) from kso.skew where col1=1;

COUNT(*)
----------
3199971

10g has a very nice utility (DBMS_XPLAN) to display plans from various locations (AWR, V$SQL_PLAN and Plan Tables). It has been expanded in 11g to work with Plan Baselines as well. So stop using Explain Plan and start looking at the real plan that the optimizer comes up with.

• Just execute the statement (it’s Ok to hit control-C if it’s a long running statement, it will still be in the shared pool)
• Find the SQL ID for the statement (I use find_sql.sql for this step)
• Then use dbms_xplan.display_cursor to see the plan (I use dplan.sql for this this step).

There is are a couple of great posts by Rob Van Wijk and Jonathan Lewis on DBMS_XPLAN.DISPLAY_CURSOR on their blogs. Also the Oracle Optimizer Development Group Blog has a good post on the subject which includes 11g options.