The AVOID_FULL hint

I saw this very odd statement on an SAP system last week.

         SELECT /*+ AVOID_FULL ("/bic/xxx") */ * FROM "/BIC/XXX" WHERE "/BIC/XXX"=:A0

I had never seen that hint before so I thought I’d do a little investigation. First I did a quick check on a test case to see if it worked.

SYS@DEMO1> select /*+ avoid_full(a) */ count(*) from kso.skew a where col1=234657;
 
  COUNT(*)
----------
        32
 
SYS@DEMO1> @x
 
 (more...)

ADF Runtime Diagnostics: Instrumenting your ADF Application – Part 2

In a series of blog articles I show how you can instrument your ADF Application key actions. Instrumenting gives visibility and insight of what is happening inside the ADF application and in the ADF framework (what methods and queries are executed, when and how often). These runtime diagnostics can be very effective in identifying and solving performance issues. This enables developers to diagnose and solve performance problems at an early stage and to build an efficient, (more...)

To_char, Infinity and NaN

Funny that oracle can easily cast ‘nan’,'inf’,'infinity’,'-inf’,'-infinity’ to corresponding binary_float_infinity,binary_double_nan, but there is no any format models for to_char(binary_float_infinity,format) or to_binary_***(text_expr,format) that can output the same as to_char(binary_float_infinity)/to_binary_float(‘inf’) without format parameter:

If a BINARY_FLOAT or BINARY_DOUBLE value is converted to CHAR or NCHAR, and the input is either infinity or NaN (not a number), then Oracle always returns the pound signs to replace the value.

Little example:

SQL> select to_binary_float('inf') from dual;

TO_BINARY_FLOAT('INF')
----------------------
                   Inf

 (more...)

Oracle Midlands : Event #3 – Registration Open

Registration is now open for Oracle Midlands Event #3 on Tuesday 20th May..

As I mentioned in a previous postChristian Antognini will be the speaker for both the sessions this time. He’ll be covering “12c Adaptive Query Optimization” and “Row Chaining and Row Migration Internals”.

Red Gate Software have kindly offered to sponsor the event again, so registration is free!

I’ve already registered. :) Please make the effort to come along and support the (more...)

Cellcli can lie to you…

Yes that is true, i said it, cellcli can lie to you there are some special cases were the output of cellcli is not the reality and you should double check it’s output with your standard OS tools. So this the output if dcli calling cellcli on an Exadata rack from an client:

[root@dm01db01 ~]# dcli -g cell_group -l root cellcli -e list cell attributes cellsrvStatus,msStatus,rsStatus
dm01cel01: running       running         running
dm01cel02: running       running         running
dm01cel03:  (more...)

Oracle Applications User Experience: The New Cool. And Hip With It Too!

Great shout out for the Oracle Applications Cloud Release 8 Simplified UI by all-round Oracle Applications UX champ and enterprise applications guru Floyd Teter (@fteter). He tells us: “Simple is the new cool.

image

Simplified UI Home Page. A tablet-first crisp experience from the cloud.

Nicely put. But, if that’s not enough kudos, try this Dutch language article, “Oracle doet hip en demonstreert interfaces”. The article is about the AMIS-hosted (more...)

Enterprise UX Change Optics: Newton (Isaac not Apple’s)

Tech startups have a key role to play in bringing out change in enterprise user experience (UX), as pointed out in this great Wired article: Enterprise UX: Why the Paradigm Shifts.

Now, there are many nuances to enterprise-level user experience and the stakeholders are different to the consumer UX space, even though the consumerization of information technology driven by users expectation is now a given. Innovation in the startup space ultimately influences consumers before the (more...)

Using Table Functions

Overview of table functions

Table functions are functions that produce a collection or rows (either a nested table or a varray) that can be queried like a physical database table. You use a table function like the name of a database table, in the FROM clause of a query. The difference with a physical database table in the FROM clause of the query is that you need to use the TABLE() operator to tell the (more...)

ORA-00600 [kkzlpllg:5] When Dropping MView Log

This week a co-worker and I have been doing some rapid-fire testing to improve fast-refresh performance on an old materialized view, which does some summary aggregations on a 1.9 billion row (and growing) master table. One of the things we tested was using the new-in-11gR2 COMMIT SCN feature. There is a great blog post describing the benefits of this feature by Alberto Dell'Era. To quickly summarize and over-simplify, it provides a much faster way (more...)

Deterministic functions, result_cache and operators

In previous posts about caching mechanism of determinstic functions I wrote that cached results are kept only between fetch calls, but there is one exception from this rule: if all function parameters are literals, cached result will not be flushed every fetch call.
Little example with difference:

SQL> create or replace function f_deterministic(p varchar2)
  2     return varchar2
  3     deterministic
  4  as
  5  begin
  6     dbms_output.put_line(p);
  7     return p;
  8  end;
  9  /
SQL> set  (more...)

Oracle12c : Truncate Table Cascade

Prior to Oracle 12c truncating tables with dependent children was a  pain. We had to disable the constraint before truncating the parent table followed  by enabling the constraint.  The performance gain achieved using TRUNCATE instead of DELETE  almost disappeared if the constraints were enabled with VALIDATE option.  If enabling the constraint with NOVALIDATE option was not acceptable, then DELETE seemed  to be the only way to go.

Starting with Oracle 12c , Oracle supports truncating (more...)

Gartner 2014 Advanced Analytics Quadrant

The Gartner 2014 Advanced Analytics Quadrant is out now. Well it is if you can find it.

Some of the companies have put it up on their websites to promote their position.

For some reason Oracle hasn't and I wonder why?

Gartner Advanced Analytics MQ Feb2014

You can see that some typical technologies are missing from this, but this is to be expected. How much are companies really deploying these alternatives on real problems and in production. Perhaps the positioning (more...)

DBMS_JAVA Privilege Error?

It’s possible to get an error after granting privileges to an external file system. One of those errors is tedious to resolve until you understand the rules governing Java NIO file permissions.

You grant privileges to external file systems as the sys user with the grant_permission procedure of the dbms_java package, like

SQL> BEGIN
  2    DBMS_JAVA.GRANT_PERMISSION('IMPORTER'
  3                               ,'SYS:java.io.FilePermission'
  4                               ,'C:\Data\Upload'
  5                               ,'read' (more...)

Working Around an ASM Startup Problem

Scenario

I recently hit the problem covered in MOS note “ASM Instance Is Not Coming Up ORA-00064 (1,4468736,Kfchl Array) Kfchl Array (Doc ID 1328620.1)” mid-way through a Grid Infrastructure upgrade from 11.2.0.2 BP16 to 11.2.0.4 BP3 on Exadata. Specifically, it was the application of prerequisite patch 17783101 (required for downgrades) to node 3 that hit the problem. At this point nodes 1 and 2 had been successfully patched (more...)

Better Data Modeling: My Top 3 Reasons why you should put Foreign Keys in your Data Warehouse

This question came up at the recent World Wide Data Vault Consortium. Seems there are still many folks who build a data warehouse (or data mart) that do not include FKs in the database. The usual reason is that it “slows down” load performance. No surprise there. Been hearing that for years. And I say […]

Top 10 queries from v$active_session_history

Description This query return the top 10 queries by resource consumption (CPU+IO+WAIT) in the last hour from v$active_session_history. Be careful, this view is part of the diagnostic pack, you should not query this view if you don’t have license for it. top 10 queries from v$active_session_history [crayon-533b91631cebb871999454/] This query return top queries by resources consumed, [...]

The post Top 10 queries from v$active_session_history appeared first on Oracle DBA Scripts and Articles.

SCAN VIP Troubleshooting

We had a client that was running into a strange issue on their Exadata where new connections coming in through the SCAN were failing.  After doing some troubleshooting, it was discovered that it was related to one of the SCAN listeners not properly accepting requests from new sessions.  The VIP and listener were running, and everything looked normal.

We had the following SCAN setup:

SCAN VIP # VIP IP
1 172.25.2.70
2 (more...)

Speaking at Collaborate 2014

I’m a little behind on my updating my blog with images of conferences where I will be speaking (I’ll get to that later and hopefully fix it).  In the meantime, I wanted to let everyone know that I will be speaking at IOUG Collaborate 2014 this year.  IOUG has decided to hold the conference in Las Vegas, NV.  Should be a fun conference; after all everyone knows the saying “What happens in (more...)

NoSQL vs. NewSQL vs. traditional RDBMS

I frequently am asked questions that boil down to:

  • When should one use NoSQL?
  • When should one use a new SQL product (NewSQL or otherwise)?
  • When should one use a traditional RDBMS (most likely Oracle, DB2, or SQL Server)?

The details vary with context — e.g. sometimes MySQL is a traditional RDBMS and sometimes it is a new kid — but the general class of questions keeps coming. And that’s just for short-request use (more...)

Juggernaut

One of the problems of “knowing” so much about Oracle is that the more you know the more you have to check on each new release of the software. An incoming ping on my posting “Lock Horror” reminded me that I was writing about 11.2.0.1, and the terminal release is 11.2.0.4, and the whole thing may have changed in 12.1.0.1 – so I ought to re-run some tests (more...)