Kerry Osborne's Oracle Blog

Well I did the early voting thing last week. It’s the first time I’ve voted in a presidential election since I was 18. I voted for Ronald Regan in that election. I think he did pretty well. This year’s election has been interesting.

Boy, John McCain sure has an expressive face!

Here’s a comparison to a lizard of some sort.

And then there’s Tina Fey. I think she makes a better Sarah Palin than Sarah Palin. Here are a couple of her best shots. The first one is her imitating McCain, but the second one is just her talking.

I can’t seem to find any bad pictures of Obama (so I had to make do with this cartoon of Obama O’s – with Hope in Every Bowl!). 

Same goes for Biden (he does kind of look like the grinch though).

Anyway, all this buzz about tongues made me think of another a couple of other guys with famous tongues. Remember this guy?

But the one I was really thinking about was MJ. We always thought he was pretty cool, even though his tongue was hanging out about half the time.

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.

One of the most frustrating things about the Cost Based Optimizer in Oracle is the sometimes erratic performance. It can seem almost random at times. Resolving these issues can also be challenging. It’s a little like taking your car to the mechanic when it makes a funny noise. It never seems to make the noise when the mechanic is there. Fortunately we have ASH/AWR which captures a ton of information about what our databases are doing.

Here’s a couple of scripts that I find useful.

The first one can be used to show statements that have experienced significant variances in execution time (it can be modified to look for variances in the amount of logical i/o, but I’ll leave it as an exercise for the reader).  I called the script unstable_plans.sql. It uses an analytic function to calculate a standard deviation on the average elapsed time by plan. So the statements that have multiple plans with wild variations in the response time between plans will be returned by the script. The script prompts for a couple of values. The first is minimum number of standard deviations. The second is the minimum elapsed time (I usually don’t care if a statement executes sometimes in .005 seconds and sometimes in .02 seconds, even though this is a large swing statistically). Both these inputs are defaulted by the way.

Continue reading ‘Unstable Plans (Oracle Plan Stability/Instability)’ »

Well I guess I have officially  joined the cool crowd since I am now the proud poppa of a bouncing baby MAC.

The best things about it so far are:

1. It runs windows so I still have a safety net if I get to feeling totally lost. I’m using VMWare which allows me to jump back and forth.
2. It runs some flavor of unix as it’s operating system. (so I can run top, ps, sar, etc… – it’ BSD but it beats the crap out of DOS)
3. It’s about a million pounds lighter and half the size of my old Sony laptop.
4. It’s faster than my old laptop.
5. At 4G, it’s got 4 times the memory my old laptop had.
6. The video iChat thing is pretty cool.
7. It’s the worlds biggest iPod.

It is pretty cool but I just hate the process of going from one machine to another. Even without the frustration of the keyboard changes that the Mac inflicts, changing just makes for some amount of unproductive time. I have to do a presentation next week in Fort Worth at an Oracle Technology Luncheon thing (here’s a link to the presentation: 11g Advanced Features) So anyway, I am working on the Power Point slides using the MAC and it makes me feel like I’m typing with mittens on. I’m sure that will get better over time. I am finding that there are a lot of options with this setup though. Almost everything I am doing can be done in more than one way. Like I can do my calendar stuff in MS Office or in iCal (or on my phone for that matter).  

I have to admit that old laptop was starting to have a lot of problems. Wireless was flakey, it would freeze every other day or so, and last week the keyboard quit working. Kind of reminded me of the PC/MAC commercial where the PC was sick.

Anyway, so far so good. Now if I can just figure out how to make the F11 key work in windows instead of turning on the speaker I should be in good shape.