Search

Top 60 Oracle Blogs

Recent comments

December 2009

V$SQL_MONITOR and V$SQL_PLAN_MONITOR

In my recent presentation at UKOUG 2009 in Birmingham I also mentioned the new feature of Oracle11gR1 which is a neat solution for monitoring long running SQL statements. It captures statistics about SQL execution every second.

For parallel execution every process involved gets separate entries in V$SQL_MONITOR and V$SQL_PLAN_MONITOR.

It is enabled by default for long running statements if parameter CONTROL_MANAGEMENT_PACK_ACCESS if it is set to “DIAGNOSTIC+TUNING” and STATISTICS_LEVEL=ALL|TYPICAL

It can be enabled at statement level as well with /*+ MONITOR */ hint or disabled with /*+ NO_MONITOR */ hint.

There are some defaults defined which can be altered by setting hidden parameters:
_sqlmon_max_plan - Maximum number of plans entries that can be monitored. Defaults to 20 per CPU
_sqlmon_max_planlines - Number of plan lines beyond which a plan cannot be monitored (default 300)

UKOUG 2009

UKOUG 2009 conference will take place next week in Birmingham. I will speak on Wednesday, December 2nd about "Execution Plan Stability in Oracle11g". As the audience at UKOUG conference is very technical I will add more technical stuff to my presentation this week.

Here is the link for for the conference agenda.

Global Temporary Tables Share Statistics Across Sessions

In another blog posting, I asserted that statistics collected by one session on a Global Temporary table (GTT) would be used by other sessions that reference that table, even though each session has their own physical instance of the table. I thought I should demonstrate that behaviour, so here is a simple test.

We will need two database sessions. I will create a test Global Temporary table with a unique index.

#eeeeee; border: 0px solid rgb(0, 0, 0); overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
DROP TABLE t PURGE;
TRUNCATE TABLE t;

CREATE GLOBAL TEMPORARY TABLE t
(a NUMBER,b VARCHAR2(1000))
ON COMMIT PRESERVE ROWS;

CREATE UNIQUE INDEX t ON t(a);

Partition Maintenance with Global Indexes

(updated 3.12.2009 to deal with UPDATE GLOBAL INDEXES option)
When I decide to partition a table, I also need to consider whether to partition the indexes, and if so how. The easiest option is to locally partition the indexes.  I try to avoid globally partitioned indexes because they can become invalid when you do partition maintenance. However, where an index leads on a column other than the partitioning key, then you might have to scan all partitions of a locally partitioned index if you do not query by the partitioning column.  Sometimes, it is necessary to partition an index differently to the table, or not at all. However, I have found that there are some partition management operations that do not invalidate global indexes.

The Thing (I Don't Like) About Video

Video is awesome. I like high-bandwidth communication. Even on the cheapest, most un-produced videos, I can see facial expressions and body language that I'd never be able to pick up from text. I can see candidness that's not going to come through in a document, even a blog that's written pretty much off the cuff. And videos with high production value, ...well of course it's awesome to watch a great short movie right at the tips of your fingers.

But...

But when you send me a 7:48 video, I have to budget 7:48 to watch it. (Well, more actually, because of the latency required to buffer it up.) When you send me a 13-page document, I can "read" it in 10 seconds if I want to. I can skim the first and final paragraphs really quickly and look for pictures or sidebars or quotes, and it takes practically no time for me to do it.

With a video, it's just more difficult to do that. I can watch the first 10 seconds and usually know whether I want to watch the remainder. But skimming through the whole video—like skipping to the end—is more difficult, because I have to sit there un-utilized while the whole video buffers up. Then I have to sit there while words come at me aurally, which is annoyingly sequential compared to reading buckets of text in one eyeful.

So, the bottom line is that the first 10 seconds of your video need to convince me to watch the remainder.

Or I won't.

Is it just me?

What's out there to make video browsing a better, more time-efficient, and more fulfilling experience?

NEXTGRES Gateway SQL*Plus Teaser…

Do you love SQL*Plus but hate that you can’t use it with other, non-Oracle databases?  Do you wish you could more easily migrate some of your MySQL or Postgres applications to Oracle?  If so, stay tuned, because my next few blog entries are going to show you how to do just that.  But first, I […]

Table Expressions, Cardinality, SYS_OP_ATG and KOKBF$

Recently I was involved in discussion on OTN form (see http://forums.oracle.com/forums/thread.jspa?threadID=986657)
and I found an interesting information inside the posted execution plan which was in "Predicate information" section and was

filter(SYS_OP_ATG(VALUE(KOKBF$),2,3,2)='HQPRM003')

Googling showed some interesting information in Vlad Sadilovski's blog that KOKBF$ is a generic alias given the table expressions. What was more interesting was undocumented SYS_OP_ATG function which attracted my interest. So I decided to create a simple case to observe what is going on.

SQL> CREATE TYPE demo_object AS OBJECT
2 ( ID NUMBER(6)
3 , first_name VARCHAR2(30)
4 , last_name VARCHAR2(20));
5 /

Type created.

SQL> create type demo_object_t as AS TABLE OF demo_object;
2 /

Type created.

My interview at Miracle Open World 2008 in Lalandia, Denmark

Just for fun I post here two videos related to my presentation at Miracle Open World 2008 in Lalandia, Denmark. I had a presentation titled "Cost Based Optimizer Falacies Caused By The Application Design".
This is a short video where I announce my presentation at Miracle Open World 2008. I took this video while I was baking pizzas for my grandchildren. The oven is homemade and if you are interested you can see more details how I have built it.