Dominic Brooks

A follow-up to my post yesterday.

A quick recap on the situation:

A sql statement such as this:


SELECT col1
FROM my_schema.table1 tab1
WHERE …
AND NVL(col2,’UNASSIGNED’) = SYS_CONTEXT(’MY_CONTEXT’,’MY_COL2_VALUE’)


is raising an error such as this:


ORA-00904: “MY_SCHEMA”.”TAB1″.”SYS_NC00062$”: invalid identifier


where SYS_NC00062$ is not a column directly specified in the sql but a virtual column related to a function-based index.

This matches a bug which is currently under development and not yet published (Bug 7026584).

The explanation is that the 11g optimizer uses a new mechanism to work with function-based indexes at parse time, a new mechanism which as yet not fully robust.


The problem comes when virtual column processing is done from qkacol where it clones the predicates and later tries to resolve the copied columns in the predicate with the supplied fro. The root cause here, the unnesting driver generates an alias for table and adds it for a correlated column. [sic]


In addition to the 10046 and 10053 trace files, a 904 errorstack trace file provided the information required:


alter system set events=’904 trace name errorstack’;
…
alter system set events=’904 trace name errorstack off’;


Oracle’s currently (at the time of writing this) recommended solution to that bug to set undocumeted parameter _replace_virtual_columns to false.

I’m still awaiting an explanation on that cost_io virtual column adjustment.

17976931348623157081452742373170435679807056752584499
6598917476803157260780028538760589558632766878171540458953514
3824642343213268894641827684675467035375169860499105765512820
7624549009038932894407586850845513394230458323690322294816580
8559332123348274797826204144723168738177180919299881250404026
184124858368.00 to be precise.

I’ve got a problem with a set of sql statements in production. Fortunately the problem is reproducible in a non-prod environment.

These statements all reference one particular virtual column in a composite function-based index.

The sql statements all look something like this:


SELECT col1
FROM my_schema.table1 tab1
WHERE …
AND NVL(col2,’UNASSIGNED’) = SYS_CONTEXT(’MY_CONTEXT’,'MY_COL2_VALUE’)
…


And they are raising the following error:


ORA-00904: “MY_SCHEMA”.”TAB1″.”SYS_NC00062$”: invalid identifier


This “SYS_NC00062$” is a hidden/virtual column on this table that is part of a function-based index. I can see it here at least:


select *
from sys.col$
where obj# in (select object_id from dba_objects where object_name = ‘TABLE1′ and owner = ‘MY_SCHEMA’) order by col#;


This article isn’t about this error specifically. Not yet at least but I might blog about it later once I know some more.

However, in the meantime, I’ve just started gathering details, running some extended trace files (10046, 10053), etc with a view to finding some extra information and then probably raising an issue on metalink.

I’ve not even started analysing this info yet but as I zipped through the 10053 file, I noted something odd.

Note that in this 10053 trace file, the original reported SYS_NC00062$ has now become SYS_NC00066$ because I dropped and recreated the FBI to see if that helped - it didn’t.

Looking at the cost_io numbers, you get various costs for various access methods - e.g a 16.00, a 3.00, a 15632.00, a 53.00 but then holy moly:


***** Virtual column Adjustment ******
Column name SYS_NC00066$
cost_cpu 300.00
cost_io 17976931348623157081452742373170435679807056752584499
6598917476803157260780028538760589558632766878171540458953514
3824642343213268894641827684675467035375169860499105765512820
7624549009038932894407586850845513394230458323690322294816580
8559332123348274797826204144723168738177180919299881250404026
184124858368.00
***** End virtual column Adjustment ******


I’ve literally done nothing yet but seen that number and thought I’d put in down quickly in an article.

But wow - that’s a big adjustment! What’s that about?

I picked the wrong day to leave my Jonathan Lewis CBO book at home…

Here’s the 10053 trace file.

*Or “can I use result_cache to cache for 1 minute/day/hour/day/week/month”

There is already a plethora of excellent resources on the new 11g result_cache functionality, notably:

• Oracle Database Concepts Manual
• oracle-developer.net - pl/sql function result cache
• oracle-developer.net - query result cache

Including a couple of interesting angles on things here:

• At Pythian - RC Enqueue, RC Latches, Does 11g result cache scale poorly?,Result Cache on eight-way itanium, Result Cache in the real world
• And some further comments back and forth on the above articles and other nuances at askTom

Now one of my first tasks now that we’re on 11g and EE (having upgraded from 9i SE) is to identify new features to implement from a performance perspective.

Having read some of the above resources, it won’t surprise you to know that you can’t just point anything at the result_cache and expect a dramatic improvement - quite the contrary sometimes. As Adrian Billington notes in his article, if you’re already doing session memory caching using pl/sql structures, you’re unlikely to see any saving in time - the opposite in fact - but the advantages of the result cache are “cross-session availability, managed SGA memory usage and protected data integrity”. Of course, depending on what you’re doing, you could even choose to use pl/sql caching on top of the result cache.

So, if you’ve got a result_cache target in mind - make sure you test it.

One of the things I’m currently looking at is whether to implement a function result_cache lookup for time sensitive information. The sort of thing I mean is information that changes daily or hourly or weekly independently of the underlying data changing.

For example, fund prices and valuation dates. A fund might have a scheduled set of valuation dates from which a certain price applies. And on any given day, you might be in a different valuation period.

Exactly the sort of thing you might cache using whatever caching method you might choose. And exactly the sort of thing that you could use function result_cache functionality for.

If the underlying data changes, the cache will be refreshed (having used the RELIES_ON clause).

If the timing point changes (the day/week/hour/minute/whatever), a new set of data is cached.

Here is a simple example of what I mean.

- Create a function that will cache by the minute. We will do this by creating a function with a single parameter that defaults to the current date truncated to the current minute and by calling it without specifying a parameter:


CREATE OR REPLACE FUNCTION f_test_minute_result_cache (
i_today IN DATE DEFAULT TRUNC(SYSDATE,’MI’)
)
RETURN DATE
RESULT_CACHE
AS
BEGIN
RETURN SYSDATE;
END f_test_minute_result_cache;
/


Then call it:


dominic@11gTest>l
1 select to_char(sysdate,’DD-MON-YYYY HH24:MI:SS’) now
2 ,to_char(trunc(sysdate,’MI’),’DD-MON-YYYY HH24:MI:SS’) this_minute
3 ,to_char(f_test_minute_result_cache,’DD-MON-YYYY HH24:MI:SS’) cached_result
4* from dual
dominic@11gTest>/

Elapsed: 00:00:00.00


Call it again, in the same minute, but a few seconds later:


dominic@11gTest>l
1 select to_char(sysdate,’DD-MON-YYYY HH24:MI:SS’) now
2 ,to_char(trunc(sysdate,’MI’),’DD-MON-YYYY HH24:MI:SS’) this_minute
3 ,to_char(f_test_minute_result_cache,’DD-MON-YYYY HH24:MI:SS’) cached_result
4* from dual
dominic@11gTest>/

Elapsed: 00:00:00.00


You can see from “Now” that time has inevitably moved on, but as the default in parameter has not changed, the result has come from the cache.

Then, run the statement again a bit later:


dominic@11gTest>l
1 select to_char(sysdate,’DD-MON-YYYY HH24:MI:SS’) now
2 ,to_char(trunc(sysdate,’MI’),’DD-MON-YYYY HH24:MI:SS’) this_minute
3 ,to_char(f_test_minute_result_cache,’DD-MON-YYYY HH24:MI:SS’) cached_result
4* from dual
dominic@11gTest>/

Elapsed: 00:00:00.00


Time has moved on, the default in parameter has moved on, a new result is generated and cached.

So, just to show that you could also use result_cache in this way. Doesn’t necessarily mean that you should though.

Post 11g upgrade, one of the first things I noticed was in the AWR reports.

In the Instance Efficiency Percentages, the “Parse CPU to Parse Elapsd %” was a less than impressive “0.00″. I’ve become accustomed to this being low in reports here, but not that low .

This has been confirmed as a new bug but the support analyst was not hopeful of it being fixed at any point soon (periods of not months but years and 12g was mooted).

It’s not a big deal. The forumula for this ratio is an unsurprising 100*(parse time cpu / parse time elapsed), figures which can be found elsewhere in AWR (and statspack of course) in the Instance Activity Stats.

So, the figure that should have reported was 85% - still less than impressive.

I’m slightly surprised that something so obvious slipped through the net but then again maybe not if it’s unlikely that more than 1% of Oracle sites are running 11g.

Still looking good.

CPU on the new box is hovering under the 20% mark having been habitually > 80% on the old kit. But it could be a quite time for the business so we will have to compare the metrics that we capture as a proxy for “business activity”.

Best news is that we were getting a significant number of “ORA-01801: date format too long for internal buffer” errors every day causing the client-server application to crash.

These have been due to cursors being shared when they shouldn’t be, for example due to NLS mismatches.

As hoped (and promised to the business), these have disappeared with the upgrade.

Production database was upgraded to 11g over the weekend: 9i -> 11g, SE -> EE, Windows -> Linux.

This is the first application in this client’s company to upgrade.

Seems to have gone well given that I was not called over the weekend.

As ever though, the only true test is going live and I would be very, very surprised if there were no suprises!

So, in the absence of any ringing hotlines and before I start on something new later this morning, I’m just going to be monitoring active session and also seeing what rises to the top by looking at sql with greater than average buffer_gets (I know roughly what’s normally there so will be interesting to see if anything changes).

(or my database emails have stopped working)

Wotcha.

Here’s the background:
We’re in the latter stages of preparing for a production upgrade to 11g (2 weeks to go).

Best practice would say that we should have a stable database by now and very high confidence factor, but the truth is that the production upgrade coincides with a new release of functionality and every now and then it is “discovered” that some core functionality is not working (For example 1200 lines of code to change from using the function XSLPROCESSOR.VALUEOF which has a 4k node size limit to use the procedure which doesn’t. Or actually creating a wrapper for the function signature that calls the procedure, etc, etc)

Back to the matter at hand:

Emails failing with

ORA-24247: network access denied by access control list (ACL)
ORA-06512: at “SYS.UTL_TCP”, line 17
ORA-06512: at “SYS.UTL_TCP”, line 246
ORA-06512: at “SYS.UTL_SMTP”, line 115


This is part of the nattily named “Fine-grained Access to External Network Services“, a new feature in 11g. Essentially, previously allowed access to services over the network using packages like UTL_SMTP, UTL_HTTP, UTL_TCP, etc.

Have some links:

• Tim @ Oracle-Base
• Marco Gralike
• Metalink Note 557070.1

So, the problem itself was pretty easy to distill:

declare
l_email clob := ‘test email’;
begin
email_pkg.send_email(
i_sender => ‘me@myemail.com’,
i_recipients => ‘me@myemail.com’,
i_subject => ‘test’,
i_mime_type => ‘text/html’,
i_priority => NULL,
i_message => l_email);
end;


which gave, as mentioned:


ORA-24247: network access denied by access control list (ACL)
ORA-06512: at “SYS.UTL_TCP”, line 17
ORA-06512: at “SYS.UTL_TCP”, line 246
ORA-06512: at “SYS.UTL_SMTP”, line 115


Solution is to create an ACL:


BEGIN
BEGIN
DBMS_NETWORK_ACL_ADMIN.DROP_ACL (
acl => ‘my_mailserver_acl.xml’);
EXCEPTION
WHEN others THEN
DBMS_OUTPUT.PUT_LINE(’Error dropping ACL ‘||SQLERRM);
END;
DBMS_NETWORK_ACL_ADMIN.create_acl (
acl => ‘my_mailserver_acl.xml’,
description => ‘Connect Access To Mail Server’,
principal => ‘UTILITY’,
is_grant => TRUE,
privilege => ‘connect’,
start_date => SYSTIMESTAMP,
end_date => NULL);
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
acl => ‘my_mailserver_acl.xml’,
host => ‘my.mailserver.com’,
lower_port => 25);
COMMIT;
END;
/


This failed for me initially because:


function-based index XDB.XDB$ACL_XIDX is disabled


After rebuild of index (note to self - don’t be lazy and use Toad because it expands the syntax to include defaults that aren’t always valid), email test works fine.

Job done, move along - nothing to see here.