Working Around an Internal Error Based on Source Mnemonics

My frenzied hacking through the unexplored territory of Expression Filters was derailed (to mix my metaphors) by an internal error when using the Evaluate() function in a rather complex query that uses an inline view, analytic functions etc..

The following was extracted from the alert log:

ORA-07445: exception  encountered: core dump [kkqsCkLegalEqvExpCB()+199] [SIGSEGV] [Address not mapped  to object] [0x000000000] [] []

The mnemonic beginning “kkqs” in the first parameter apparantly indicates a fault raised from the query rewrite source module. Because query rewrite is not helpful to the execution of the query I coded an alter session to set query_rewrite_enabled to false, and executed the problem query again.

Success.

Interestingly, adding a NO_REWRITE hint to the query did not prevent the error occurring. A shame, because I would have preferred that approach.

According to the website query rewrite problems might also be indicated by any of the codes kkqg, kkqs, kkqs1, kkqs2, kkqs3, kkqu, kkqv, and kkqw, so disabling query rewrite might also be a good workaround for internal errors that reference mnemonics beginning with any of those strings.

It also prompts the thought that in some cases it might be possible to get guidance on working around internal erros based on the mnemonic. For example an error in “kkfd” probably indicates a parallel query fault, and the remediation might be to disable parallel query. “kxtr” … trigger problem?

And there I was thinking that all of these kernel code things were for nerds.

Stupid Yahoo Mail

I just browsed my Yahoo spam folder and found an email purporting to be from an ex-work colleague. It was sent to a bunch of other ex-colleagues as well and contained a link to what looked like a site for selling fake ED pills.

I replied to the colleague to say, “Hey looks like you’ve been hacked! Ha ha ha”, and was told by Yahoo:

Your message was not sent

Your account has been temporarily blocked from sending messages. This block can be caused by sending messages that trigger our spam filters, or by having too many recipients in one email. We encourage you to review the contents and recipient list of your message, and try sending it at after an hour or two. Doing so will usually resolve the matter.
If you are still unable to send messages after a 24-hour period, please read our FAQ for more information and to request Customer Care assistance.


We apologise for the inconvenience.

Thanks,

The Yahoo! Mail Team

Thanks for apologising for locking my account, assholes.

“If you are still unable to send messages after a 24-hour period …”? Well, I don’t think I’ll bother with your “Customer Care assistance” as it has done me fuck-all good in the past. I think I’ll just make that switch to a less irritating email provider that I’ve been thinking about for the past ten years.

A Cause of ORA-38435 in Evaluate()

I’m part way through an implementation of some ELT code that uses expression filters to parse records and assign zero-to-many “inboxes” to each row, and as is often the case with the less well-explored parts of the system it often feels like I’m hacking through the undergrowth in unknown territory.

Under such conditions it’s not surprising to hit an error that few others seem to have hit before, and for which the majority of Google hits are for that irritating type of site that merely lists all of the Oracle errors in the documentation whether or not they have information on how to fix them.

Thus it is with “ORA-38435: missing elementary attribute value or invalid name-value pairs”.

Here’s how I found the root cause of the error in my case.

It was thrown when running a SELECT statement with the EVALUATE condition. The code looks something like this (slightly obfuscated for no real reason other than paranoia):

select
 i.inbox_name,
 D.*
from
 INBOX_EXPRESSIONS i,
 DATA_TABLE D
where
 evaluate (
   I.INBOX_EXPRESSION,
     My_Event(
       D.SERVICE,
       D.FILTER,
       ... blah blah blah ...
       D.SUMMARY,
       D.IDENTIFIER).getVarChar()
 )= 1
/

Firstly, the error was thrown in a predictable fashion after something like the 450th row of the result set had been returned. I re-executed it through SQL*Plus with an arraysize of 1, slightly modified to just return the ROWID of DATA_TABLE.

This result set showed ascending values of ROWID, which agreed with the execution plan that showed a full table scan of DATA_TABLE.

The error was thrown after the ROWID “AABonGAAJAAH1UeAAA” was returned.

I modified the query to something like:

select
 i.inbox_name,
 D.ROWID
from
 INBOX_EXPRESSIONS i,
 (SELECT * FROM DATA_TABLE WHERE ROWID = 'AABonGAAJAAH1UeAAA') D
where
 evaluate (
   I.INBOX_EXPRESSION,
     My_Event(
       D.SERVICE,
       D.FILTER,
       ... blah blah blah ...
       D.SUMMARY,
       D.IDENTIFIER).getVarChar()
 )= 1
/

This returned a correct row.

I then incremented the ROWID to 'AABonGAAJAAH1UeAAB' and re-executed, getting 0 rows. I kept incrementing until I reached 'AABonGAAJAAH1UeAAF' at which point the error was thrown.

Ah hah.

From the error stack it looked as if the error was raised by Evaluate, rather than the function My_Event.GatVarChar(), so I tested that by including the call to My_Event.GetVarchar() in a query against the columns of DATA_TABLE, and to see what it was returning for each row. Hopefully I'd spot something that looked like bad syntax on the error-raising row that might trip up the Evaluate() function.

And there it was:

SERVICE=>to_number('0'),FILTER=>'abc', ... ,SUMMARY=>'System's IPs are not responding.',...'

The inclusion of a single quote in the value of the column SUMMARY caused the GetVarchar function to construct an invalid string that was then passed to Evaluate().

The fix was fairly straightforward of course -- replace every occurrence of a single single-quote with two single-quotes in the string columns.

select
 i.inbox_name,
 D.*
from
 INBOX_EXPRESSIONS i,
 DATA_TABLE D
where
 evaluate (
   I.INBOX_EXPRESSION,
     My_Event(
       D.SERVICE,
       Replace(D.FILTER,'''',''''''),
       ... blah blah blah ...
       Replace(D.SUMMARY,'''',''''''),
       Replace(D.IDENTIFIER,'''','''''')).getVarChar()
 )= 1
/

I'd not be surprised if there were other potential causes.

And by the way the handsome list of single-quotes reminds me of this:

James, while John had had "had", had had "had had"; "had had" had had a better effect on the teacher

My_Event.GetVarchar()

Serious Space Savings

I knew this would happen before I even tried it.

I Request: “The SYSAUX tablespace is 500MB and it’s nearly full. The contents are mostly the default 30 days worth of DBMS_Scheduler logs. Can you increase it to 2GB?

A DBA Responds: “I’ve increased it to one GB for now“.

An entire gigabyte of disk space saved. Well done.

Now why didn’t I ask for 4GB?

Pushing the Technical Frontier

I have a dilemma.

In the BI system that I’m working on I have the perfect application for the use of Expression Filters. It is almost as if someone specifically devised this problem in order to tempt me into using them, and I really am very tempted indeed.

Let me explain.

In the application which feeds data to the BI system users can define “display filters” that determine whether network alerts are displayed in particular windows or not. For example they might create a filter called “Failed Core Devices” for which the definition reads: “IS_CORE=’Y’ and FAILED>=1″. Network events which meet those criteria are displayed in that window. They might also have a filter called “Failed Cisco Devices” with a definition: “DEV_TYPE LIKE ‘CISCO’ AND FAILED >= 1″. So, a failed Cisco core device will actually appear in both windows.

The requirement is to tag which windows an event was displayed in at a particular time, and because there are no data elements for each event to tell you the display window(s) the only way to work that out is to evaluate the expression for each event.

This is exactly what an Oracle Expression Filter does, and although I would have to convert the definitions from Netcool ObjectSQL to Oracle SQL (LIKE in Netcool ObjectSQL is a regular expression, so “DEV_TYPE LIKE ‘CISCO'” has to be converted to “REGEXP_LIKE(DEV_TYPE,’CISCO’)”), they are an obvious fit for the problem.

My query to determine the filter names for the events would be reduced to something like:

select
  e.event_id,
  f.filter_name
from
  events e,
  filter_definitions f
where
  Evaluate(
    f.filter_condition,
    get_filters.getVarChar(
      e.dev_type,
      e.is_core,
      e.failed)) = 1

Which would obviously be pretty neat and tidy, considering the alternatives (think of how you might implement an expression such as “(COL1 NOT IN (1,3,4,5,78,900) AND COL2 IS NOT NULL) OR NOT REGEXP_LIKE(COL3,’^WHATEVER’)” by other means as simply as you would with an expression filter). It could be implemented as part of the ELT or as a filter or join in Business Objects (or your current query tool of choice).

However, what happens if I move on to another client and this method fails because of a new filter definition that references a new data element not currently included in the Expression Filter attribute set? Or an upgrade goes wrong? Or a patch breaks it? Or we discover a bug? Can I sleep at night if I’m leaving code behind that will have most reasonably competent Oracle practitioners reaching for Google to find out what EVALUATE is, and then has them reading documents for the next couple of hours? Even if I sit the support staff down and show them all how it works, that knowledge will have evaporated in a couple of months.

Documentation? Well I can certainly document how expression filters work, and how to detect problems and perform apparantly simple tasks like modifying the attribute set (I already did something similar for DBMS_Scheduler chains), but I’m not going to think of everything and even then do I want people to be following written instructions without understanding the architecture of expression filters? It’s pretty easy to get into a mess over this object oriented stuff.

The alternative? Currently unknown. The few options identified are prone to inaccuracy or imprecision (eg. they’ll only identify a single filter instead of the full set), and they tend to require continual maintenance of the ELT or the query tool or both whenever a filter is created, deleted or modified.

So to summarise: do I implement a solution that is a technical exact-match with potentially high future maintenance problems, or a poor technical solution with definite low-level ongoing maintenance problems.

At the moment, I have no idea. Input welcome.

Listing DBMS_Scheduler Named-Calendar Dates with SQL

Just a quick follow up to my post from the other day about using DBMS_Scheduler to generate lists of dates in SQL.

I mentioned that you can use named schedules in this method, and that’s actually a handy way of testing your schedule definition.

The syntax is along the lines of:

begin
 dbms_scheduler.create_schedule(
 schedule_name => 'DAILY_4AM',
 repeat_interval => 'FREQ=DAILY;BYHOUR=4;BYMINUTE=0;BYSECOND=0');
end;
/

select *
from   table(
  list_of_dates(
    'daily_4am',
    sysdate    ,
    null));

2010-08-21 04.00.00.000000000
2010-08-22 04.00.00.000000000
2010-08-23 04.00.00.000000000
2010-08-24 04.00.00.000000000
2010-08-25 04.00.00.000000000
2010-08-26 04.00.00.000000000
2010-08-27 04.00.00.000000000
2010-08-28 04.00.00.000000000
2010-08-29 04.00.00.000000000
...

Another interesting syntax, which of course is inherited directly from the DBMS_Scheduler calendaring syntax, allows named schedules to be combined in interesting ways that broadly correspond to the SQL set operators UNION, INTERSECT and MINUS by specifying INCLUDE, INTERSECT and EXCLUDE clauses (note the missed opportunity to use the same key words there, rather a shame I think).

So this would allow you to specify:

select *
from   table(
  list_of_dates(
    'WEEKDAYS;EXCLUDE=PUBLIC_HOLIDAYS',
    sysdate    ,
    null));


select *
from   table(
  list_of_dates(
    'WEEKENDS,PUBLIC_HOLIDAYS',
    sysdate    ,
    null));

INCLUDE is a funny one -- the syntax diagrams imply that you can "union" named schedules by listing them, as in the example above, or by using the INTERSECT clause. INTERSECT is really used for combining a regular schedule such as "FREQ=DAILY;BYHOUR=3;BYMIN=30;BYSECOND=0' with a named schedule.

This prompts the thought of whether it is better to specify a regular calendar string in the SQL when querying for a list of dates, or to define a named schedule and reference that. I suspect that named schedules would really be of value for defining a complex list such as "US_PUBLIC_HOLIDAYS" or "UK_PUBLIC_HOLIDAYS" that you want to keep as a standard reference.

I might be over-thinking this.

Mind you, this listing of dates would be a handy feature in SQL Developer's enhancement for DBMS_Scheduler I suppose.

Generating Lists of Dates in Oracle the DBMS_Scheduler Way

A rather common question in the Oracle community goes along the lines of this: “How do I generate a list of dates?”, with variations such as needing only working days, excluding specific dates, etc..

If there is a standard method for this it tends to go along the lines of this:

  1. Generate a rather vanilla list of dates using a CONNECT BY clause on the DUAL table
  2. Filter the results based on day of week or other criteria.

Other more sophisticated variations are also out there in the wild, and for the most part there is nothing technically wrong with them if they are fast and accurate and easily understood.

At the upper end of complexity it can be rather tricky to develop an accurate query. Here then is an alternative method that uses DBMS_Scheduler to define a calendar. DBMS_Scheduler has a sophisticated calendaring syntax described in the 11gR2 documentation here (with some interesting examples that are definately worth browsing here), that would do a very good job of implementing some complex list of date requirements. Although it would probably be my first choice for very complex list generation it also does such a decent job of simple list generation that I’d be inclined to consider it for those as well.

The method is built around the DBMS_Scheduler subprogram Evaluate_Calendar_String. This subprogram returns as one of its parameters a single date (well actually a TIMESTAMP WITH TIME ZONE) which is the “next” date in a calendar calculated from the following three input parameters::

  1. A calendar string that defines the repeating interval of the list of dates. Here is the point where you would specify that you are interested only in the last-but-one day of every month as “FREQ=MONTHLY; BYMONTHDAY=-2;” for example.
  2. A start date that gives the earliest date from which the calendar string is to be evaluated. For example, from today’s date, or from the beginning of this year. The start date may be redundant depending on how complete the specification of the calendar string is, and to me it seems like a hangover from the days of DBMS_Job in which you specify a start point and a method for calculating the next date based on it. Anyway, with a complete calendar specification the start date can be ignored. The example above is not complete because it does not specify the time of day for the calendar dates. This could be inferred from the start date if it were something like “2010-01-01 00:00:00″, but if you did not specify a start date then the calendar will inherit the time of day that you executed the program. It’s safer in my opinion to specify a complete calendar string such as ‘FREQ=MONTHLY; BYMONTHDAY=-2;BYHOUR=0;BYMINUTE=0;BYSECOND=0′
  3. A date from which the next date in the calendar will be calculated. Typically this date would be the previous date in the list of calendar dates.

So with a complete calendar specification the Evaluate_Calendar_String program comes down to two elements: a calendar string, and a date to return the next calendar date after. Pretty simple.

We can therefore use the program as the basis for a pipe-lined function that accepts a calendar string and an initial date to start calculating subsequent calendar dates from, and which returns the desired list of dates.

Here is a hastily cobbled-together version.

create or replace type timestamp_table_type
is
table of timestamp;
/

create or replace function
 list_of_dates (
   calendar_string varchar2,
   start_date      TIMESTAMP WITH TIME ZONE,
   stop_date       TIMESTAMP WITH TIME ZONE)
return
  timestamp_table_type
pipelined
is
  l_return_date_after TIMESTAMP WITH TIME ZONE := start_date - interval '1' second;
  l_next_run_date     TIMESTAMP WITH TIME ZONE;
begin
  loop
    DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING(  
      calendar_string   => list_of_dates.calendar_string,
      start_date        => list_of_dates.start_date,
      return_date_after => l_return_date_after,
      next_run_date     => l_next_run_date);
 
      exit when list_of_dates.l_next_run_date > coalesce(list_of_dates.stop_date,date '9999-12-31');
 
      pipe row (list_of_dates.l_next_run_date);
 
      list_of_dates.l_return_date_after    := list_of_dates.l_next_run_date;
 
 end loop;
end;
/

Here it is in action then:

“Give me a list of days for this year”

select *
from
 table(
   list_of_dates(
     'FREQ=DAILY;BYHOUR=0;BYMINUTE=0;BYSECOND=0',
      trunc(sysdate,'yyyy'),
      add_months(trunc(sysdate,'yyyy'),12)-1));

“Give me a list of the last-but-one day for each month from now to the end of the year”

select *
from
 table(
   list_of_dates(
     'FREQ=MONTHLY; BYMONTHDAY=-2;BYHOUR=0;BYMINUTE=0;BYSECOND=0',
     sysdate,
     add_months(trunc(sysdate,'yyyy'),12)-1));

“1am, 3am, 7am at 1pm every day from tomorrow for a very long time”

select *
from
  table(
    list_of_dates(
     'FREQ=DAILY; BYHOUR=1,4,7,13;BYMINUTE=0;BYSECOND=0',
     trunc(sysdate),
     date '9999-12-31'));

“First day of each calendar quarter etc”

select *
from
  table(
    list_of_dates(
     'FREQ=YEARLY;BYDATE=0101,0401,0701,1001;BYHOUR=0;BYMINUTE=0;BYSECOND=0',
     trunc(sysdate),
     date '9999-12-31'));

Guess what this one does:

select *
from
  table(
    list_of_dates(
     'FREQ=DAILY;BYDAY=MON,WED,FRI;BYHOUR=12;BYMINUTE=0;BYSECOND=30',
     trunc(sysdate),
     date '9999-12-31'));

A couple of other issues worth mentioning:

  1. The optimiser will think that the function will return 8,168 rows. Possibly there is a way around this.
  2. You can reference existing schedules as part of the calendaring syntax.
  3. If you really do stick to using the TIMESTAMP WITH TIME ZONE data type then you can be daylight savings time sensitive (see docs).

Anyway, there you go. I quite like this method because it’s very easy to tackle date lists of just about any complexity with it, especially when you can just copy-paste an existing method and modify it. If you are used to using it then as soon as you see this code you immediately recognise that a list of dates is being generated, and the definition of the list probably leaps out at you in the way that my last example hopefully did.

Comments always welcome, of course.

;BYHOUR=0;BYMINUTE=0;BYSECOND=0