oracle

Google Search Auto-complete.

Nuff said!

Cheers

Tim…

This is just a temporary link to Randolf Geist’s note on a small, but potentially significant, change in the way that Oracle handles statistics when you’re fiddling with indexes and empty tables. I’ve added the link to my “bits and pieces” list (see top right) so I’ve disabled comments and will be deleting this post in a few days.

And here’s a link to a really smart piece of SQL from Timur Ahkmedeev which uses v$sysmetric to emulate the output of the statspack/AWR profile over the last few seconds. (This one’s going into the AWR/Statspack list – also in the catalogue at top right).

 

From vintage-themed photographs, to vibrant vector graphics, here's an infographic detailing the top global design trends of the year.

After 8 years, 17 million images and over 200 million downloads, Shutterstock is one of the world’s leading marketplaces for visual media. We have artists and photographers from more than 100 countries, and customers in more than 150. But perhaps most significant about these milestones is that it has led to thousands of image searches each day – giving us valuable insight into design trends around the world.

From vintage-themed photographs, to vibrant vector graphics, here’s an infographic detailing what visual stories were told over the last year.

February 22, 2012 There are some amazing (or just plain interesting) facts to be found about how indexes work in Oracle Database.  One Internet site in particular that has been helpful in my understanding of Oracle indexes is Richard Foote’s blog.  I occasionally see information on the Internet, in the Oracle documentation, in Metalink (My [...]

A colleague came to me a couple of days ago with a SQL problem. He has something like this:

@get_source

NAME          INPUT
------------- -----
GROUP_1       5
GROUP_2       3
GROUP_3       4
GROUP_4       7
GROUP_5       3

What he wanted to do was multiply all the inputs across the groups, to get the total number of possible permutations. ie 5*3*4*7*3. The product of all the INPUTS. This has to be in straight SQL. Easy! You just need to… Ahhh… Hmmm…

No, I just could not think of a way to do it that my colleague could use.

- There is no group-by function that gives a product of a column {that I know of} - We could not use PL/SQL {for reasons I won’t go into}, so no cursor looping or passing in an array, which would make it simple - Neither of us could think of an analytical function that could take the result of itself in the prior row (though I suspect there may be a way to do it). - The number of groups could and would vary from a few to possibly a hundred, so the old tricks of converting rows to columns or nesting so many sql statements would not help.

So, I asked my friend, the queen of SQL Query, Dawn Rigby.

She came straight back with an answer. In case you want to try and work it out yourself before seeing the solution, below is a cat picture. The answer she came up with is below that:

The key to the solution is natural logs {ln}. I don’t know about you, but I learnt about using logs at school and have pretty much not used them since. In summary

If x=3*5*9 then ln(x) = ln(3)+ln(5)+ln(9) = 1.09861+1.60944+2.19722 = 4.90527 ie using log converts multiplication to addition. You then use EXP, the inverse of ln, to convert your added-up log value into your result.

exp(4.90527) = 135

{NB if you use my figures, exp(4.90527) realy equals 134.999355, as I have truncated the log values shown. Oracle does this far more accurately internally but be aware you might get some slight rounding errors)

So, what we can do is simply use the SQL GROUP function SUM to add together the natural logs of all the rows:

sum(ln(input)) {grouped by the whole statement, so no group by is needed in this case}

As an example:

-- show the expected result first
select 3*7*4*5*1 from dual;

 3*7*4*5*1
----------
       420


select min(name),max(name),count(name)
,EXP (SUM (LN (gr_sum))) gr_prod
from
(select 'group_1' name, 3 gr_sum from dual
 union
 select 'group_2' name, 7 gr_sum from dual
 union
 select 'group_3' name, 4 gr_sum from dual
 union
 select 'group_4' name, 5 gr_sum from dual
 union
 select 'group_5' name, 1 gr_sum from dual
)
/

MIN(NAM MAX(NAM COUNT(NAME)    GR_PROD
------- ------- ----------- ----------
group_1 group_5           5        420

As you can see, it works – even if when you first look at the formula your brains {if you are not a mathematician} try to leak out of your head. Just try and remember what your maths teacher said about log books and how, before calculators, they were used to speed up manual long multiplication tasks by converting the task into log addition.

If you want more information on logs, see this discussion about how they are actually about growth or wikipedia if you must .

Dawn actually pointed me to this very nice post about using logs in oracle by Anju Parashar, which I have borrowed from.

One issues to be aware of (which is highlighted in Anuj Parashar’s article) is that you can’t get a log of negative values, as a consequence Oracle will give you an ora-01428 error:

select ln(-3) from dual; select ln(-3) from dual * ERROR at line 1: ORA-01428: argument ‘-3′ is out of range

Anuj gives a version of code that works if all values are negative, below I have one that copes with any number of negatives. Basically, you convert all the values to be multiplied to positive values and then make it negative if the count of negative values is odd. Mathematically, the result of a multiplication can ONLY be negative if there are an odd number of negative values.

,EXP (SUM (LN (abs(gr_sum)))) *decode (mod(sum(decode(sign(gr_sum),0,0,1,0, 1)),2) ,0,1,-1) correct_gr_prod

I’m sure that the above expression could be simplified, but I have to go and do the day job.

Finally, here is a little set of test cases covering the above, so you can play with this.

mdw1123> select 3*7*4*5*1 from dual;

 3*7*4*5*1
----------
       420

1 row selected.

mdw1123> --
mdw1123> select 'group_1' name, 3 gr_sum from dual
  2  union
  3  select 'group_2' name, 7 gr_sum from dual
  4  union
  5  select 'group_3' name, 4 gr_sum from dual
  6  union
  7  select 'group_4' name, 5 gr_sum from dual
  8  union
  9  select 'group_5' name, 1 gr_sum from dual
 10  /

NAME        GR_SUM
------- ----------
group_1          3
group_2          7
group_3          4
group_4          5
group_5          1

5 rows selected.

mdw1123>
mdw1123> select min(name),max(name),count(name)
  2  ,EXP (SUM (LN (gr_sum))) gr_prod
  3  from
  4  (select 'group_1' name, 3 gr_sum from dual
  5   union
  6   select 'group_2' name, 7 gr_sum from dual
  7   union
  8   select 'group_3' name, 4 gr_sum from dual
  9   union
 10   select 'group_4' name, 5 gr_sum from dual
 11   union
 12   select 'group_5' name, 1 gr_sum from dual
 13  )
 14  /

MIN(NAM MAX(NAM COUNT(NAME)    GR_PROD
------- ------- ----------- ----------
group_1 group_5           5        420

1 row selected.

mdw1123> --
mdw1123> -- now with a negative
mdw1123> select 'group_1' name, 3 gr_sum from dual
  2   union
  3   select 'group_2' name, -7 gr_sum from dual
  4   union
  5   select 'group_3' name, 4 gr_sum from dual
  6   union
  7   select 'group_4' name, 5 gr_sum from dual
  8   union
  9   select 'group_5' name, 1 gr_sum from dual
 10  /

NAME        GR_SUM
------- ----------
group_1          3
group_2         -7
group_3          4
group_4          5
group_5          1

5 rows selected.

mdw1123> -- and if the values contain negatives
mdw1123> select min(name),max(name),count(name)
  2  ,EXP (SUM (LN (abs(gr_sum)))) gr_prod
  3  ,mod(sum(decode(sign(gr_sum),0,0
  4                          ,1,0
  5                          ,  1)
  6           ),2) -- 0 if even number of negatives, else 1
  7           modifier
  8  ,EXP (SUM (LN (abs(gr_sum))))
  9   *decode (mod(sum(decode(sign(gr_sum),0,0,1,0,     1)),2)
 10         ,0,1,-1) correct_gr_prod
 11  from
 12  (select 'group_1' name, 3 gr_sum from dual
 13   union
 14   select 'group_2' name, -7 gr_sum from dual
 15   union
 16   select 'group_3' name, 4 gr_sum from dual
 17   union
 18   select 'group_4' name, 5 gr_sum from dual
 19   union
 20   select 'group_5' name, 1 gr_sum from dual
 21  )
 22  /

MIN(NAM MAX(NAM COUNT(NAME)    GR_PROD   MODIFIER CORRECT_GR_PROD
------- ------- ----------- ---------- ---------- ---------------
group_1 group_5           5        420          1            -420

1 row selected.

Well, after the minor news that OBIEE 11.1.1.6 was finally released yesterday (and a very late night last night), it’s back to Endeca week, with us today look at how Endeca’s product line will fit in with Oracle’s BI, data warehousing and EPM tools. In the first two postings in this series, we looked at Endeca’s product line prior to the Oracle acquisition, and yesterday we took at closer look at MDEX, the hybrid search/analytics database that powers the Endeca product line. So how will the various Endeca products fit in with what Oracle have already got, and where will they add new capabilities that haven’t been possible before?

If you look at the press release that Oracle published a couple of weeks ago after the transaction closed, Oracle’s rationale for purchasing Endeca is pretty clear. Across all of their major product lines, Endeca technology adds support for unstructured data that complements the existing structured data support provided by the Oracle database. In the diagram below, for example, we can see Oracle strategy where unstructured data gets captured by Endeca technology, processed by tools such as Hadoop and ODI, and then is stored in structured form in the Oracle database, and in unstructured and semi-structured form in MDEX.

Similarly, Oracle’s Engineered Systems (“Exa-”) hardware will eventually incorporate Endeca MDEX technology, with the Big Data Appliance performing ontology and sentiment analysis, Exadata housing an MDEX engine, and Exalytics running the successor to Endeca’s Latitude software (more on this later).

As we saw on Monday, Endeca had two main product lines; Endeca Infront, an e-commerce/faceted search product that’s used as the underpinnings to websites such as Autotrader.com, HMV.com and Sony’s e-commerce site, and Endeca Latitude, a BI tool based on the same MDEX technology as Infront but positioned as a more general purpose, search/discovery platform. The plan therefore is to use Infront alongside Oracle ATG, Oracle’s own e-commerce product line, whilst Latitude will be rebranded as Oracle Endeca Information Discovery, to better emphasize the search/discovery aspect of the tool.

So putting Infront aside for the moment, how will Latitude/Information Discovery fit in with Oracle’s own BI, EPM and DW product line – will it replace OBIEE, will OBIEE replace it, or is the some way they’ll all be integrated?

From my understanding, there’s several ways in which Endeca and Oracle BI technology will come together:

• Endeca MDEX can become a data source for the OBIEE BI Server repository
• OBIEE’s repository could become a data source for Endeca’s MDEX engine (likely to be rebranded as the “Endeca Server”)
• OBIEE’s Answers/analysis editor could therefore report against MDEX data
• Similarly, Endeca Studio could report against OBIEE repository data alongside other data sources loaded into MDEX
• OBIEE dashboards could embed Endeca Studio components with them, similar to how BI Publisher reports embed now

For me, the most promising integration possibility is bringing OBIEE data into the Endeca Server (MDEX) and using it, along with other structured, unstructured and semi-structured data to create data discovery dashboards and “mashups”. You can imagine this as an interesting type of integration – the Endeca Latitude-style data discoverery interface with search, faceted navigation, tag clouds and so on, coupled with BI components such as graphs, tables, pivots and so forth sourcing jagged data from sources such as social media feeds, product catalogs, ERP data, analytics data from a warehouse and so on – all running at high-speed on an Exalytics box. Sounds very interesting.

The other aspect that I think Endeca Latitude/Data Discovery will bring to things is around Agile BI; recently, we’ve been talking about using OBIEE and Exadata in an agile way, harnessing OBIEE’s semantic model and the sheer horsepower of Exadata to avoid the need to pre-summarize data, but OBIEE itself is still a fairly significant install, and the process of creating a formal dimensional model is still a brittle process; with Endeca Latitude/Data Discovery, the install (currently…) is still fairly lightweight and the dimension model emerges and adapts as you add more data to the model. I can definitely see an “agile BI” role for Endeca’s products within Oracle’s technology stack, something that’ll appeal to the Qlickview-style audience who want fast ROI, a lightweight footprint and minimal data modelling.

So, to round our Endeca week off, we’ll be concluding the series with a look at the development process for MDEX databases and the Studio front-end. Check back tomorrow for the next installment.

So if you’re a repository developer working on Oracle Business Intelligence 11g, probably the most interesting new feature announced with the release of OBIEE 11.1.1.6 yesterday was support for version control within the BI Administrator tool, and a new repository format called MDS XML. Let’s take a look now into what MDS XML is, and how version control integration with the BI Administrator tool works.

Up until now, Oracle BI Repositories were stored in binary files called “RPD” files, after the .rpd filename extension. Using a single monolithic file was a design decision nQuire took back when the product was first developed, and allowed their consulting team to rapidly deploy metadata sets onto servers without having to worry about database connections, deployment procedures and so on. However, having all of the repository metadata in a single file causes us lots of issues now, in particular:

• Its pretty-much impossible to extract individual objects out of the repository, and put those objects under version/source control, and
• Having all metadata in a single file means we end up jumping through hoops when trying to run multi-developer projects, giving us the pleasures of the Multi-User Development Environment

So, primarily I think driven by the requirements of the Fusion Apps development team, the 11.1.1.6 release of Oracle Business Intelligence introduces a new format for saving repositories; the MDS XML format. Similar, but not quite the same as the XUDML format that’s used when patching repositories, MDS XML breaks the repository into individual XML files, with each one corresponding to a first-class object such as a logical table, logical dimension, physical table, physical join and so on. MDS XML is only available when working in offline mode, so to save a repository in MDS XML format, first open it offline, and then select File > Save As > MDS XML Documents…

Once you’ve saved the repository in this format, the title bar for the Oracle BI Administrator tool will display the folder name, and if you look inside the filesystem folder, you’ll see your repository stored as a set of subfolders containing individual XML documents.

If you then subsequently want to open the repository saved in this format, you open the Oracle BI Administration tool as normal, but this time select File > Open > MDS XML …, which then prompts you to select a folder rather than an RPD file, and then supply the repository password. So what’s the point of this? Well, the answer is in the next question that the Oracle BI Administration tool asks you – do you want to open the MDS XML repository standalone, or use source control?

Choosing the Standalone option just opens the repository as normal. If you subsequently want to upload this repository to the Oracle BI Domain using Enterprise Manager, you’ll need to re-save it in the classic RPD format, and then do the upload using that file. But how does the source control option work?

Let’s look at a situation where you’re using Subversion as your source control system, and TortoiseSVN as your SVN client. You’ve set up your SVN repository using the standard folder structure, so that it looks like this (I’m using the free-as-in-beer Visual SVN Server as my SVN server and Apache server combined):

Now to configure the Oracle BI Administration tool to work with Subversion, I need to go into the Tools > Options… menu and select the Source Control tab. This presents me with a dialog with two buttons; one to Edit a configuration file, the other to create a New one. The Oracle BI Administration tool is actually designed in this release to work with any (in theory) source control tool, by mapping a set of internal commands to add files, remove files and mark files for update ito the actual commands used by the source control system you’ve chosen; let’s press the New button to see how this works.

When I press New, I get a dialog where I can enter the actual command-line commands used by my source control tool to match the generic source control operations that the Oracle BI Administration tool will perform.

Now obviously this might sound a bit daunting, so if you’re using Subversion, there’s a pre-defined configuration file you can use instead. Pressing Cancel, I then press the Edit button instead, select the subversion template file, and view the options that have been added for me.

This looks OK, so I press Cancel and go back to the main menu. Let’s now go through the check-out, development, save and check-in process.

1. I’m starting now with an empty /trunk/Repository folder in the Subversion repository. I start therefore by using TortoiseSVN to check-out this directory to somewhere on my filesystem, like this:

2. Now, I want to save my repository in MDS XML format into this checked out folder (in SVN terms, the working copy folder). I do so by opening the regular RPD file in offline mode, and then selecting the File > Save As > MDS XML Documents… option. I select the folder that’s just been created by TortoiseSVN as my working copy folder, and then save the repository in the MDS XML format to this folder.

3. Then, if I want to upload this first source-controlled version of the repository to SVN, I can come out of the Oracle BI Administration tool and right-click on the folder, and select SVN Commit… from the TortoiseSVN shell menu.

As at this point, all of the folders and XML files will be unversioned, I need to add them to the working set, and then press OK.

TortoiseSVN will now upload the folders and files to the SVN repository, like this:

4. If I then check in the SVN repository, I’ll see my uploaded BI repository in this MDS XML format.

5. So, what the process that you go through if you subsequently want to check this repository out again, make some changes, and then upload the changes back to the source control system? This is where the source control integration in the Oracle BI Administration tool comes in. Let’s start by using TortoiseSVN to update our working copy of the SVN repository files, in case anyone else has committed and changes in the meantime.

Now, to open this repository under version control, I select File > Open > MDS XML… from the Oracle BI Adminstrator tool, enter the repository password, but then select Use Source Control rather than Standalone, and use the Browse… button to pick up the SVN configuration file I opened a while ago.

6. When I now go on to work with the repository, the title bar for the Oracle BI Administration tool has the message (under source control) next to the folder name where the MDS XML-format repository is saved, and it now keeps track of all the object add, delete and edit operations I perform whilst working with the repository.

7. Let’s now make some changes to the repository, deleting that Staff (Parent Child) logical dimension, and adding a new subject area. Now, when I save the repository, I’m asked first to check global consistency, and then a new dialog comes up confirming the source control actions that this will lead to. In this case, my changes have lead to five new MDS XML files being created, six being modifed, and two being deleted; information that the Oracle BI Adminstration tool will then pass to the Subversion client, via those command-line mappings we saw in the configuration file.

Once I press the OK button, the Source Control log dialog comes up, and shows me the progress of my files being synchronized to the working set in the folder I checked-out of the SVN repository.

Then, I press Close to close the dialog, and my repository is saved.

8. All that’s left to do now is to return to TortoiseSVN, and make the final commit back to the SVN repository (remember, all that the Oracle BI Administration tool has done use SVN commands to synchronize the list of changes you’ve made with SVN’s local record, it’s not committed the files back to the SVN repository). So, I right-click on the folder and select SVN Commit, and I can see from the Commit dialog that the SVN client knows about the files I’ve added, deleted and modified in the Oracle BI Administrator tool.

I then press OK and TortoiseSVN, the SVN client, updates the SVN repository with the changes.

9. If I then want to view the history of changes in SVN, I can use TortoiseSVN again to view the change log, where I can see the repository revisions and who’s made the changes.

So, there’s the basic process, and that’s what MDS XML is for. I can certainly think of lots of ways this could be improved, but it’s better than where were were before, and it’ll be interesting to see how this work with branching and merging repositories, or trying to take versions of objects from previous check-ins, or even other repositories, and trying to merge them together using these XML files. But the thing to bear in mind is that you’ll need access to a version control system such as Subversion, and a client such as TortoiseSVN, to make this all work, with the Oracle BI Administrator’s role being mainly to keep track of what you’ve changed, and relay that information to the version control tool before you try and check-in your updated repository.

As you probably already heard, the ACE Director program has recently altered their travel assistance policy. Some of the bigger, well established events will no longer be considered for travel assistance, and even those that are will have a limit to the number of ACEs that will get funding. The immediate fallout of this is some ACE Directors (including myself) have had to pull out of some events we had planned to speak at.

My first reaction was to post the following message to Victoria, Lillian and Justin,

Thanks for the update. I hope you guys don’t get too much hassle from people about this change. If you do, just remember, people will always find something to moan about.

As always, I’m very grateful for all the time and effort you guys have put into the program over the years.

Of course I am disappointed about having to pull out of events like KScope12, but I’m not about to throw my toys out of the pram.

I still think the ACE Director program is a really cool thing and I will continue to support it for as long as they will have me. I’ve met some truly brilliant people through the program and I fully expect to continue doing so. In my opinion, the travel assistance is only a small aspect of what I’ve gained from being a member of the group, so I hope people don’t focus on it too much.

Of course, if I stop getting free t-shirts, it’s all over.

Cheers

Tim… (Still proud to be an ACED)

By now, a few thousand of you are aware of MR Trace, the Method R Trace extension for Oracle SQL Developer. It’s the easiest way to create a perfectly time-scoped trace file and put it onto your desktop computer without having to do any of the work of talking to your DBA, finding your trace file directory on the database server, groping (or grepping) through all the files to find the one you want, and then copying the file across your network to where you want it.

MR Trace eliminates all that work for you, completely automatically, with no extra clicking. You just run your SQL or PL/SQL code in your SQL Developer worksheet with the Run Script (F5) button click, and—presto!—your trace file appears on your desktop.

But what if you want to fetch a trace file that you didn’t just now create from SQL Developer? What if you want to grab that trace file created by the GL Posting job that ran last night? What then? Enter MR Trace version 2.

Let’s review. With MR Trace version 1, you got a Local Trace Files panel, which I like to dock at the bottom of my Oracle SQL Developer window. It shows all the trace files that have been retrieved for me (copied from the database server) and which are now stored locally on my desktop. The files that have been retrieved most recently are emboldened, in case the sort order you’ve selected causes them not to be listed at the top. Whenever you use the Run Script (F5) button with MR Trace enabled, trace files automatically arrive here for you to do what you want with them. ...I use the tools in the Method R Workbench package.

With MR Trace version 2, you get a new “MR Trace → Trace Files” item in the navigation panel for each of your Oracle Connections. Clicking the “Trace Files” item opens a new window that lets you access all the trace files on the database server you’re connected to. To copy a trace file to your Local Trace Files panel, just double-click the file you want in the Trace Files panel for your connection (or right-click like I've done in the screen shot).

Do you have 42,000,000 trace files on your system? Don’t worry, MR Trace isn’t going to show up in one of my “Messed-Up App of the Day” blog posts for fetching all 42,000,000 rows across your network on every screen refresh. We’ve used the standard SQL Developer filter widget to give you control over how many files show up in your Trace Files list. By default, MR Trace will fetch information about only the 100 newest files in the directory.

In spite of packing a whole lot more power, MR Trace version 2 is just as easy to install as Mr Trace version 1. We use a few server-side Java and PL/SQL objects to enable the new magic, which install automatically the first time you use the new feature. If you have SYSDBA privileges, you can complete the installation yourself in just a few seconds. If you don’t, then MR Trace gives you the complete installation script that you can send to your DBA to enable your connection. That way your DBA can see exactly what you’re asking for in a single, easy-to-read sqlplus script.

MR Trace version 2 still costs just us$49.95. If you already own MR Trace version 1 and would like to upgrade, just contact us, and we’ll be happy to give you a coupon good for a us$20 upgrade discount. Buy MR Trace version 2 today at our online store, or visit the Method R Trace web page for a free 14-day trial.

My note on “NOT IN” subqueries is one of the most popular on my blog, staying in the top 5 hits for the last five years – but it’s getting a bit old, so it’s about time I said something new about “NOT IN” – especially since the Null Aware Anti Join has been around such a long time. The example I want to talk about is, as so often, something that came up as a problem on a customer site. Here’s a bit of SQL to model the situation, which is currently running under Oracle 11.1.0.7:

create table t1
as
with generator as (
        select  --+ materialize
                rownum id 
        from dual 
        connect by 
                level <= 10000
)
select
        rownum                  id1,
        trunc((rownum-1)/10000) id2,
        trunc((rownum-1)/10000) n1,
        lpad(rownum,10,'0')     small_vc,
        rpad('x',100)           padding
from
        generator       v1,
        generator       v2
where
        rownum <= 100000
;

-- collect stats, compute, no histograms

alter table t1 add constraint t1_pk primary key(id1, id2);
alter index t1_pk invisible;
create index t1_i2 on t1(id1, id2, n1);

create type jpl_scalar as object(x1 number);
/
create type jpl_table as table of jpl_scalar;
/

create type jpl_scalar3 as object(x1 number, x2 number, x3 number);
/
create type jpl_table3 as table of jpl_scalar3;
/

There are a couple of oddities in the model – one is that the second index starts with the same columns as the primary key index, and this is to emulate the functionality of the client system; the other is that I’ve made the primary key index invisible, the nature of the client data and code was such that the primary key index was not used in the query I’m about to emulate and I’ve made the index invisible so that I don’t have to mess around with statistics or complicated data routines to make the appropriate behaviour appear in the model.

Now that I’ve got the model in place, let’s take a look at the query:

delete 
from    t1
where
        (id1) in (
                select x1 from table(cast(:b1 as jpl_table)) v1
        )
and     (id1, id2, n1) not in (
                select x1, x2, x3 from table(cast(:b2 as jpl_table3)) v2
        )
;

This query is actually trying to do delete a load of data from the table. The surrounding PL/SQL package populates two collections with up to 1,000 items, The first collection identifies rows that may need to be deleted but the second collection rows then identifies rows from the first set that should not be deleted. You’ll notice that the first collection identifies rows only by the first column of the primary key, and the second collection uses both parts of the key and a non-key column to countermand the deletion. Typically both collections would hold close to the 1,000 item limit set by the developer, and typically only one or two rows would end up being deleted each time the statement ran (the id1 column tends to be “nearly unique” across the first collection).

With that overview in mind, thinking particularly of the number of rows intially identified and the number of rows that survive, look at the execution plan:

------------------------------------------------------
| Id  | Operation                            | Name  |
------------------------------------------------------
|   0 | DELETE STATEMENT                     |       |
|   1 |  DELETE                              | T1    |
|   2 |   NESTED LOOPS                       |       |
|   3 |    SORT UNIQUE                       |       |
|   4 |     COLLECTION ITERATOR PICKLER FETCH|       |
|*  5 |    INDEX RANGE SCAN                  | T1_I2 |
|*  6 |     COLLECTION ITERATOR PICKLER FETCH|       |
------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("ID1"=SYS_OP_ATG(VALUE(KOKBF$),1,2,2))
       filter( NOT EXISTS (SELECT 0 FROM TABLE() "KOKBF$" WHERE
              LNNVL(SYS_OP_ATG(VALUE(KOKBF$),1,2,2)<>:B1) AND
              LNNVL(SYS_OP_ATG(VALUE(KOKBF$),2,3,2)<>:B2) AND
              LNNVL(SYS_OP_ATG(VALUE(KOKBF$),3,4,2)<>:B3)))
   6 - filter(LNNVL(SYS_OP_ATG(VALUE(KOKBF$),1,2,2)<>:B1) AND
              LNNVL(SYS_OP_ATG(VALUE(KOKBF$),2,3,2)<>:B2) AND
              LNNVL(SYS_OP_ATG(VALUE(KOKBF$),3,4,2)<>:B3))

The optimizer has unnested the first collection (IN list) sorted the set for uniqueness, and used it to drive a nested loop through the index we need (avoiding the table) to pick up the rowid and all the columns we need to check against the second collection. However, as it does the index range scan for each unique item in the first collection it runs the NOT IN subquery checking whether the row it has acquired has a match in the second collection. This means that around 1,000 times we fetch a row and scan the second collection for a match. We almost always find a match so, on average, we will have to scan 500 items from the second collection to find that match. The statement was CPU intensive; on the production system it did about 3,000 buffer gets but took about 1 CPU second to find and delete (on average) one row.

The problem is the repeated scanning on the second collection – and it’s a problem that shouldn’t exist. I want Oracle to unnest the second query and do a hash anti join with it. If we did that then we would only scan the second collection once, scatter it into memory, and only do one probe and comparison for each row brought back by the first collection. This is (a mockup of) the plan I want to see:

----------------------------------------------------------
| Id  | Operation                              | Name    |
----------------------------------------------------------
|   0 | DELETE STATEMENT                       |         |
|   1 |  DELETE                                | T1      |
|   2 |   HASH JOIN ANTI                       |         |
|   3 |    NESTED LOOPS                        |         |
|   5 |     SORT UNIQUE                        |         |
|   6 |      COLLECTION ITERATOR PICKLER FETCH |         |
|   7 |     INDEX RANGE SCAN                   | T1_I2   |
|   8 |    COLLECTION ITERATOR PICKLER FETCH   |         |
----------------------------------------------------------

With the SQL supplied, I couldn’t make this plan appear in 11.1. I had hoped to force the path I wanted and then create an SQL Baseline for it, but I actually had to rewrite the query, converting the “NOT IN” to “NOT EXISTS” – now this isn’t always legal, of course, but in my case I knew that all the relevant columns would be non-null (even though the n1 column in the table had not been declared as such) and the data accumulated in the collections would also be non-null so the transformation was safe. So here’s the rewrite and the new plan:

delete 
from    t1
where
        exists (
                select
                        null
                from    table(cast(:b1 as jpl_table)) v1
                where   v1.x1 = t1.id1
        )
and     not exists (
                select
                        null
                from 
                        table(cast(:b2 as jpl_table3)) v2
                where   v2.x1 = t1.id1
                and     v2.x2 = t1.id2
                and     v2.x3 = t1.n1
        )
;

-------------------------------------------------------
| Id  | Operation                             | Name  |
-------------------------------------------------------
|   0 | DELETE STATEMENT                      |       |
|   1 |  DELETE                               | T1    |
|*  2 |   HASH JOIN ANTI                      |       |
|   3 |    NESTED LOOPS                       |       |
|   4 |     SORT UNIQUE                       |       |
|   5 |      COLLECTION ITERATOR PICKLER FETCH|       |
|*  6 |     INDEX RANGE SCAN                  | T1_I2 |
|   7 |    COLLECTION ITERATOR PICKLER FETCH  |       |
-------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T1"."ID1"=SYS_OP_ATG(VALUE(KOKBF$),1,2,2) AND
              "T1"."ID2"=SYS_OP_ATG(VALUE(KOKBF$),2,3,2) AND
              "T1"."N1"=SYS_OP_ATG(VALUE(KOKBF$),3,4,2))
   6 - access("T1"."ID1"=SYS_OP_ATG(VALUE(KOKBF$),1,2,2))

It’s exactly what I wanted – but the code has to be modified, which means the full testing cycle and delay, and because of the complexities of the collection objects and the need to use realistic data it’s something that I can’t actually do on the sand-pit that the client lets me play with. So, as it stands, I think it ought to be quite a bit more efficient – but I’ll have to wait a couple of weeks to find out.

Here’s the irritating bit. The client will be upgrading to 11.2.0.3 in the not too distant future, and here’s the plan you get from the original query on that version of Oracle:

------------------------------------------------------------
| Id  | Operation                               | Name     |
------------------------------------------------------------
|   0 | DELETE STATEMENT                        |          |
|   1 |  DELETE                                 | T1       |
|   2 |   MERGE JOIN ANTI NA                    |          |
|   3 |    SORT JOIN                            |          |
|   4 |     NESTED LOOPS                        |          |
|   5 |      VIEW                               | VW_NSO_1 |
|   6 |       SORT UNIQUE                       |          |
|   7 |        COLLECTION ITERATOR PICKLER FETCH|          |
|*  8 |      INDEX RANGE SCAN                   | T1_I2    |
|*  9 |    SORT UNIQUE                          |          |
|  10 |     COLLECTION ITERATOR PICKLER FETCH   |          |
------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   8 - access("ID1"="X1")
   9 - access(INTERNAL_FUNCTION("ID1")=SYS_OP_ATG(VALUE(KOKBF$),1,2,2)
              AND INTERNAL_FUNCTION("ID2")=SYS_OP_ATG(VALUE(KOKBF$),2,3,2) AND
              INTERNAL_FUNCTION("N1")=SYS_OP_ATG(VALUE(KOKBF$),3,4,2))
       filter(INTERNAL_FUNCTION("N1")=SYS_OP_ATG(VALUE(KOKBF$),3,4,2)
              AND INTERNAL_FUNCTION("ID2")=SYS_OP_ATG(VALUE(KOKBF$),2,3,2) AND
              INTERNAL_FUNCTION("ID1")=SYS_OP_ATG(VALUE(KOKBF$),1,2,2))

With my data set it uses a merge join rather than a hash join, but it manages to unnest both collections – note, also, the appearance of the null-aware anti join,/a>; the appearance of the non-mergeable view (vw_nso_1) is also an interesting detail – 11.1 didn’t have this operator in its plan.

In principle 11.1 ought to be able to produce the same plan, all the units of functionality seem to be there – including the null-aware anti-join – but the I just can’t make the plan appear (although I did managed to get an ORA-00600 error with one of my more bizarre attempts at hinting.)

Despite the automatic appearance of what seems to be a suitable (though slightly sub-optimal) path with the upgrade, I think we’ll still be doing the rewrite – interestingly 11.2 does produce a slightly different plan when you go for existence subqueries – it’s a (differently named) non-mergeable view again:

----------------------------------------------------------
| Id  | Operation                              | Name    |
----------------------------------------------------------
|   0 | DELETE STATEMENT                       |         |
|   1 |  DELETE                                | T1      |
|*  2 |   HASH JOIN ANTI                       |         |
|   3 |    NESTED LOOPS                        |         |
|   4 |     VIEW                               | VW_SQ_1 |
|   5 |      SORT UNIQUE                       |         |
|   6 |       COLLECTION ITERATOR PICKLER FETCH|         |
|*  7 |     INDEX RANGE SCAN                   | T1_I2   |
|   8 |    COLLECTION ITERATOR PICKLER FETCH   |         |
----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T1"."ID1"=SYS_OP_ATG(VALUE(KOKBF$),1,2,2) AND
              "T1"."ID2"=SYS_OP_ATG(VALUE(KOKBF$),2,3,2) AND
              "T1"."N1"=SYS_OP_ATG(VALUE(KOKBF$),3,4,2))
   7 - access("ITEM_2"="T1"."ID1")

One last thought – it looks as if the optimizer has some new ways (including dynamic sampling) of handling collections and subquery manipulation of collections in 11.2: and this client loves doing cunning things with collections – so we’re probably going to get a number of better execution plans from the upgrade – but they’re going to have to check every single example they’ve got of code using collections, because you can bet that somewhere they’ll hit an edge case where the “new improved” mechanisms manage to be the wrong choice.

Footnote: I put a couple of /*+ cardinality (XXX 10) */ hints into my code while I was creating the examples above, but took them out to present the code and results. My data set was small compared to the client’s data set, so I needed the hints but didn’t want to give the impression that they were a necessary part of the solution.

Well, the news broke late this afternoon that OBIEE 11.1.1.6 was now available for download, so here’s some of the key new features that you should look out for once you’ve installed this new release. We’ll start first with the front-end, then take a look at some administration and server-side changes, and then say where Exalytics fits in with things.

From an end-user perspective, OBIEE 11.1.1.6 provides a much-enhanced interface for analyses and dashboards, plus some changes to the way that you create BI Publisher reports. Starting off with analyses, one of the big changes is that you can now create selection steps for an analysis by right-clicking on it and selecting the step you wish to add. By adding this feature, it’s now possible to publish a basic analysis to the dashboard, then have the end-user add further steps to add, remove or keep dimension members; add totals, groups or calculated items, or otherwise manipulate the analysis results set. For me, this is the major new end-user feature in 11.1.1.6, and gives “power users” much more control over how results are displayed on their dashboards, especially considering that any selection steps they add can then be saved as a dashboard customization for later recall.

Now that it’s possible to add selection steps to an analysis after it’s been published to the dashboard, it’s especially important the end-user knows what selection steps have been applied, as well as any filters. Therefore, there’s a new Selection Steps view that you can add to an analysis compound layout, that shows you just what’s been applied.

As well as these changes to how analyses are presented, you can also now save analyses and other BI objects as favorites, which are then accessed from the global header area.

Another new feature, inspired by Exalytics (more on this later) but available for all 11.1.1.6 installations, is the ability to remove the Apply and Reset buttons from prompts. To do this, you can either set this at the individual prompt level, or you can remove them at the dashboard page level. Once you do so, any change to a prompt value will trigger an automatic refresh of those analyses connected to the prompts, though you should use this feature with care if not using an Exalytics, OLAP or fast-database data source.

There’s also a bunch of miscellaneous enhancements to the front-end including adding of new columns to existing views automatically, a new wizard for creating BI Publisher reports, and the ability to rename views once you’ve created them. We’ll post more on the new front-end features in a few days.

In terms of the repository (RPD), the major new change is the introduction of MDS XML as a repository storage option, and the ability now to integrate the Oracle BI Administration tool with source control systems. MDS XML is a fore-runner of future changes to how the Oracle BI Repository is structured and stored, and allows us to save the repository, not as a single monolithic binary file, but instead as a set of XML files. Each XML file contains the definition of an individual repository object, with objects corresponding to logical and physical tables, logical dimensions, logical joins and so on. The main point of MDS XML is to be able to upload these XML documents to a source-control system, with the XML documents then needing to be re-saved as a single RPD file before you can then upload it to the Oracle BI Server in the Oracle BI Domain. It’s not really a substitute for MUD, it’s more a way of integrating with source control, and we’ll have a posting on how this works, in detail, on our blog tomorrow.

Finally, the other major thing that 11.1.1.6 enables is Oracle Exalytics BI Machine, one of which is on order for us and due to be delivered in the next couple of weeks. Exalytics uses an optimized version of OBIEE 11.1.1.6 as it’s business intelligence platform, which includes a new utility called the Summary Advisor used for recommending and creating in-memory aggregates. Again, keep an eye on the blog for more details next week on Exalytics, including how the Summary Advisor recommends and generates aggregates and how TimesTen works under the covers.

So that’s it for now. We’ll be back tomorrow with the next installment in Endeca week, and then check back later in the day for the more details on version control within the Oracle BI Administration tool.

This seems like a straightforward question, but it’s proven to be a difficult one to answer. Even visualization researchers – people who think about the subject all day and every day – don’t have a clear definition of what visualization is. Is it synonymous with information graphics? Does visualization have to be computer generated? Does data have to be involved, or can it be abstract? The answers vary depending on who you ask.

To me, visualization is a medium. It’s not just an analysis tool nor just a way to prove a point more clearly through data.

Visualization is like books. There are different writing styles and categories, there are textbooks and there are novels, and they communicate ideas in different ways for varied purposes. And just like authors who use words to communicate, there are rules that you should always follow and others that are guidelines that you can bend and break...

I like Load profile section of Statspack or AWR reports (who doesn’t). It’s short and gives a brief understanding of what kind of work a database does. But what if you don’t have an access to Statspack or AWR but still want to see something similar? It’s possible to use V$SYSMETRIC to get this numbers [...]

It’s been too long from a previous wish for Oracle. While reading a MOS updated articles (in a new HTML interface which looks nice, much better than flash-based introduction) I’ve seen this document that made me write this blog post. As you know, Oracle has V$SQL_SHARED_CURSOR view that helps to understand why a particular child [...]

Yesterday, I kicked-off our Endeca week with an overview of Endeca’s product line prior to the recent acquisition by Oracle, looking at Endeca’s two main products: Endeca Infront, which powers the “faceted” search feature behind many e-commerce sites, and Endeca Latitude, an “agile” BI tool designed to create web-based applications against the Endeca MDEX engine, which also powers Infront. So what is MDEX, and how does it compare to Oracle products such as Oracle Database and Oracle Essbase?

First of all, it’s worth understanding the design goals behind MDEX compared to, say, an Essbase cube or an Oracle relational database. Oracle databases are designed to store lots of detail-level data in the most space-efficient way possible, and with fast retrieval times for individual rows of data (a generalization, I know…); Essbase cubes are designed to pre-compute and aggregate lots of detail-level data and then provide slices of it quickly, making strong assumptions about the query paths that users will take. MDEX though was designed to support Endeca’s “search and discovery” uses cases, where the user can search and filter arbitrarily, and get fast aggregated views returned back to them. As such, Endeca position MDEX as a hybrid search/analytical database designed for analysis of diverse, and fast-changing, data.

To make this possible, the underlying data structure is made up of data records (analogous to facts) made up of attributes that contain arbitrary sets of key/value pairs. These attributes can contain hierarchical data (i.e. XML element hierarchies) which gives end-users the ability to drill-into record sets using applications built using Endeca Studio. The main approach with MDEX is to try and take an agile approach and do away with as much of the data modelling process as possible, with the MDEX database being mostly schema-less and designed to derive its structure from the actual data that is loaded; the design-goal then was to make MDEX as much “load and go” as possible, to support Endeca’s focus on data discovery vs. the traditional analysis that you perform with tools such as Oracle BI or Essbase.

Under the covers, MDEX started-off as entirely in-memory, with this evolving over time to use a column-store on disk coupled with an in-memory cache, to make the product more scaleable. The in-memory cache aims to keep available a working set of data required for user queries, in order to support sub-second response times, with the cache itself containing sections of the data columns in active use as well as re-usable intermediate query results. The actual MDEX data is then persisted in a column-based database on disk, and mapped I/O is used to treat additional disk as RAM to extend the capacity of the cache element. All-in-all, most MDEX databases come in under under a terabyte in size, with numbers of data records in the low hundreds of millions, with the main limitation on size being the amount of RAM you can provide for the cache; MDEX is designed provide as much query data as possible from the cache, but with limitations in RAM size even on large servers, in the end too-large a data set stored in the disk-based column-store database is going to lead to slower response times for queries, which really need to return data in under a second for the type of search/discovery applications Endeca supports.

Once you’ve loaded data into MDEX, the data and metadata within it are then accessed via web service calls typically from Endeca Studio components. As such, there’s no ODBC, JDBC or XML/A interface into MDEX, with communication between client and MDEX instead following the pattern below:

MDEX then provides sets of records out to the calling Endeca Studio application, which can contain dimensional drill structures based on the hierarchies contained with each record’s attributes. This is not a formal dimensional model as you’d find with tools such as Essbase or Oracle BI Server, but it allows the user to explore and drill-into the dataset using Endeca’s “faceted” search feature, with the MDEX engine being optimized for the support of very ad-hoc, search-based filtering. This is an interesting contrast with the use-cases supported by Essbase and the Oracle Database; Essbase is designed to pre-aggregate data, whereas MDEX assumes arbitrary filtering which makes pre-computing aggregates very difficult. In a way MDEX is more like a relational database, but aims to provide fast response times by caching the main working dataset and then storing the rest of the data in a compressed, column-store format, making un-cached queries also fast, something essential for data discovery-type applications where you can’t make assumptions about what the user is going to query.

So, in summary, MDEX can be characterized in the following way:

• It’s designed to be metadata and schema-light, with data stored in records made up of values and key/value pairs, which can contain hierarchies, giving us a form of dimensional model
• Storage is a combination of an in-memory cache coupled with a disk-based column-store database
• It’s designed as “load and go”, with little up-front data modeling and a design optimized for agile data discovery across disparate and jagged data sources
• Access is via web service calls, primarily from web-based applications written using Endeca Studio
• MDEX datasets are typically sub-terabyte, with the main limitation being on how much RAM cache you can provide

All interesting stuff, I’m sure you’ll agree. For some additional background reading, here’s some useful resources on the MDEX engine:

• Endeca’s MDEX Technology (data sheet)
• In-Memory But Not Memory-Bound (Agile Analytics blog)
• How similar are faceted search and OLAP? See CIO Mag: 20 to Watch in 2010 (Agile Analytics blog)
• Vertical Stores for Vertical Web Search (Agile Analytics blog)
• Curt Monash on Endeca’s product and MDEX technology

So where does this leave MDEX, and the rest of the Endeca product line, in relation to Oracle’s current product set? Check back tomorrow for our thoughts on this.

There is a lot more to say about Dynamic Sampling and indexes, and I'll try to cover these basics in my Dynamic Sampling series on AllThingsOracle.com, but two recent discussions on the OTN forums and on Charles Hooper's blog prompted me to publish this blog post. These discussions revolved around the following issues with Dynamic Sampling and indexes: 1. CREATE INDEX On Empty Table There is an interesting change in behaviour that has been introduced with release 11.2. Whereas pre-11.2 versions do not automatically gather index statistics on empty tables, 11.2 does so. So a simple CREATE TABLE followed by a CREATE INDEX command (or an ALTER TABLE add constraint that implicitly/explictly creates indexes) will now lead to a table without statistics, but index statistics with every value set to 0 (rows, distinct keys, clustering factor, leaf blocks etc.) Since Oracle 10g a CREATE INDEX command automatically computes statistics for an index (since Oracle 9i you could add optionally a COMPUTE STATISTICS clause to the CREATE INDEX command). The interesting point is that there is no "NOCOMPUTE STATISTICS" clause, so you cannot prevent this from happening via the syntax. There is a way to prevent it, but only indirectly by locking the table statistics. Funny enough, if the table statistics are locked, there is again no way to overwrite the lock by using a FORCE keyword or similar as part of the CREATE INDEX command, whereas DBMS_STATS offers such a FORCE option. Note that creating constraints requiring indexes as part of the CREATE TABLE command shows a different behaviour: The indexes do not have statistics in this case. This shouldn't be too relevant for real-life cases, but is probably more important to setups of test cases / demonstrations. Of course it can become relevant to real-life deployment tasks that add new objects to a database. If statistics are not handled properly afterwards you now end up with tables missing statistics but indexes with 0 statistics. Of course when deliberately leaving a table without statistics you need to do something about it anyway, because otherwise the default statistics job (since 10g) will turn this table into a potential threat by gathering statistics during the maintenance window where such tables might be in some state that is not representative for the actual workload during processing. Either the default statistics job has to be disabled or re-configured via the AUTOSTATS_TARGET option of the SET_GLOBAL_PREFS / SET_PARAM procedures of DBMS_STATS, or the table statistics need to be locked individually via LOCK_TABLE_STATS. So a reasonable order of commands for such a table at deployment time could look like this: This way the statistics will be handled consistently for both table and indexes. Note that all this doesn't apply to Global Temporary Tables (GTTs) as they are not analyzed by the default statistics job and creating an index on them doesn't automatically gather statistics for that index either, even in 11.2. The change introduced in 11.2 can be seen by simply trying the following in 11.2 and pre-11.2 versions to see the difference: 2. Inconsistent Costing The reason why this change in behaviour is particularly relevant is the fact that the optimizer behaves inconsistently if a table doesn't have statistics but a suitable index does. If Dynamic Sampling gets used (very likely from 10g on because the table is missing statistics and the default level of 2 will trigger Dynamic Sampling for such a table in this case) the optimizer will not only obtain basic statistics information about the table but also overwrite the existing index statistics, but only partially. The problem is that the existing index leaf blocks statistics get overwritten by the Dynamic Sampling code, but the clustering factor is not. You can see this from the following optimizer trace snippet: So although the index has statistics gathered (there is no (NOT ANALYZED) mentioned for the index) the Dynamic Sampling updates the index stats "blocks". This is not consistent with the expected behaviour of Dynamic Sampling, because by default it is not supposed to overwrite existing statistics. If you want to force Dynamic Sampling to do so you need to specify the (not really) documented table level hint DYNAMIC_SAMPLING_EST_CDN. However, another pretty important number, the clustering factor of the index, doesn't get updated. Since the clustering factor is important for accessing the table via an index any execution plan that needs to visit the table in such a way will now be costed inconsistently: The index access part will be based on the index block statistics updated by Dynamic Sampling whereas the table access part will be costed using the clustering factor (and potentially other existing index statistics left unmodified by Dynamic Sampling). This will potentially lead to rather unreasonable plans, as the following test case demonstrates: I get the following execution plans from 11.2.0.1: Notice how the optimizer for the first execution plan gets a reasonable cardinality estimate from the Dynamic Sampling (due to the increased level it is spot on in this case) but still thinks that reading 100,000 rows from a 100,000 table via an index is a good idea. While the index access is costed reasonably based on the updated index blocks statistics, the table access is "cost-free" due to the clustering factor left unmodified at 0. When deleting the index statistics we can see that a default clustering factor of 800 gets assumed (you can see this in the optimizer trace file), which still makes the index access more favourable. Only with index statistics gathered a more reasonable plan gets selected. There is a bug filed against 11.2 (bug no 12942119 "Suboptimal plan if index has statistics but table has no statistics and dynamic sampling occurs") that seems to address this issue, but it is not clear from the bug description what exactly the bug fix does. It hasn't made it into the 11.2.0.3 patch set but it is available as one-off patch and part of some Windows platform intermediate patch sets. 3. 11.2.0.3 Change In Behaviour The 11.2.0.3 patch set introduces another interesting change in behaviour: As I've just demonstrated even with index statistics missing and hence consistent optimizer behaviour the default clustering factor assumed might still favour unreasonable execution plans. There is a bug fix 12399886:"OPTIMIZER STILL USES DEFAULT INDEX STATISTICS AFTER DYNAMIC SAMPLING WAS USED" that is by default enabled in 11.2.0.3 that addresses this issue and uses a different clustering factor derived from the Dynamic Sampling results (it turns out to be based on the blocks of the table, not the rows, see below for more details). This is a good thing in principle because the cost estimates now might be closer to reality, but as always this might cause a lot of headaches after applying the patch sets because execution plans might change. This applies of course only to those scenarios that rely on Dynamic Sampling and can make use indexes. Note that the inconsistent behaviour described under 2. is still there in 11.2.0.3, so tables without statistics having index statistics gathered still don't mix very well. Here are the execution plans that I got from 11.2.0.3 for above test case: Notice 11.2.0.3 suffers from the same cost-free table access with the zero index statistics in place, but after deleting the index statistics a full table scan will be chosen. It looks like that the new clustering factor is based on table blocks * 8 rather than a hard coded value of 800. Both constants (8, 800) might depend on the default block size - these tests were performed on 8KB.

Kathy Schrock's ISTE presentation...

A Picture is Worth 1000 Words: Infographics as a Creative Assessment.

I thought it was pretty cold in Sarajevo a couple of weeks ago, and therefore fairly mild in Minneapolis and Denver when the temperature was only just around freezing point – but after reading this report I don’t think I’m every going to say anything more about cold weather.

(Note: with my scientific/skeptic hat on, I have yet to be convinced that the story is completely true – there is one obvious weak point.)

 

A couple of weeks ago I posted a reference list of links to the bug fix notes for several of the most recent versions of Oracle - and several of the links recorded a surprisingly large number of clicks very rapidly, especially the 11.2.0.3 link. As a follow-up on the difficulties of upgrading, then, and with an insight into the number of enhancements and fixes to the optimizer that take place I decided to take a look at recent developments in the “fix control” list, and the “optimizer environment” parameters. Here’s a breakdown of the number of entries in recent versions of Oracle.

v$sys_optimizer_env / x$qkcesys

Version       v$ count       x$ count
10.2.0.3            25            194
10.2.0.5            25            207
11.1.0.7            41            270
11.2.0.3            49            321

v$system_fix_control / x$qksbgsys

Version          count
10.2.0.3            38
10.2.0.5           243
11.1.0.7           229
11.2.0.3           652

It’s interesting to note the difference between the number of parameters in the v$ and x$ versions of the optimizer environment – and when you see all the hidden parameters in the x$ it may give you some idea of how many things might explain a strange change in execution path on an upgrade. Just for reference, here’s a simple query, with output, from 11.2.0.3 (the commented column doesn’t exist in 10g):

set linesize 180
set trimspool on
set pagesize 60

column name             format a40
column feature          format a24
column value            format a15
column def_value        format a15

break on feature skip 1

select
--      FID_QKSCESYROW          feature,        -- vc64
        PNAME_QKSCESYROW        name,           -- vc40
        PVALUE_QKSCESYROW       value,          -- vc25
        DEFPVALUE_QKSCESYROW    def_value,      -- vc25
        KSPNUM_QKSCESYROW       parameter,      -- number
        PNUM_QKSCESYROW         opt_param,      -- number
        FLAGS_QKSCESYROW        flags           -- number
from
        X$QKSCESYS
order by
--      feature,
        name
;


FEATURE                  NAME                                     VALUE           DEF_VALUE        PARAMETER  OPT_PARAM      FLAGS
------------------------ ---------------------------------------- --------------- --------------- ---------- ---------- ----------
QKSFM_ALL                _add_stale_mv_to_dependency_list         true            true                  2009        140          6
                         _bloom_filter_enabled                    true            true                  2195        160          7
                         _bloom_folding_enabled                   true            true                  2200        265          7
                         _bloom_minmax_enabled                    true            true                  2387        314          6
                         _bloom_pruning_enabled                   true            true                  2256        211          7
                         _db_file_optimizer_read_count            8               8                     1075         22          6
                         _direct_path_insert_features             0               0                     2298        226          6
                         _disable_parallel_conventional_load      false           false                 2142        202          6
                         _dm_max_shared_pool_pct                  1               1                     2224        163          6
                         _dml_monitoring_enabled                  true            true                  2114        117          6
                         _enable_dml_lock_escalation              false           false                 2213        159          6
                         _enable_pmo_ctas                         0               0                    65535        232         14
                         _enable_row_shipping                     true            true                  2268        215          6
                         _enable_type_dep_selectivity             true            true                  2023         84          7
                         _fic_area_size                           131072          131072                2117        133          6
                         _first_k_rows_dynamic_proration          true            true                  2266        207          7
                         _fix_control_key                         0               0                    65535        191         14
                         _force_datefold_trunc                    false           false                 2104        111          6
                         _force_slave_mapping_intra_part_loads    false           false                 2092        192          6
                         _force_temptables_for_gsets              false           false                 2051        100          6
                         _force_tmp_segment_loads                 false           false                 2091        193          6
                         _full_pwise_join_enabled                 true            true                  2011         79          6
                         _globalindex_pnum_filter_enabled         true            true                  2211        189          7
                         _gs_anti_semi_join_allowed               true            true                  2068        103          7
                         _hash_multiblock_io_count                0               0                     1794         21          6
                         _improved_row_length_enabled             true            true                  2019         82          7
                         _is_lock_table_for_ddl_wait_lock         0               0                    65535        236         14
                         _kdt_buffering                           true            true                  2141        206          6
                         _left_nested_loops_random                true            true                  2018         81          7
                         _local_communication_costing_enabled     true            true                  2015        122          7
                         _local_communication_ratio               50              50                    2016        123          6
                         _minimal_stats_aggregation               true            true                  2045         99          7
                         _mv_generalized_oj_refresh_opt           true            true                  2333        266          6
                         _nlj_batching_enabled                    1               1                     1991        255          7
                         _optim_new_default_join_sel              true            true                  2073        104          7
                         _optimizer_adaptive_cursor_sharing       true            true                  1908        225          7
                         _optimizer_aw_join_push_enabled          true            true                  2285        230          6
                         _optimizer_aw_stats_enabled              true            true                  2267        210          6
                         _optimizer_block_size                    8192            8192                  2163         19          6
                         _optimizer_cache_stats                   false           false                 1912        130          6
                         _optimizer_connect_by_combine_sw         true            true                  2101        231          7
                         _optimizer_connect_by_elim_dups          true            true                  2102        283          7
                         _optimizer_cost_hjsmj_multimatch         true            true                  2227        164          7
                         _optimizer_enable_extended_stats         true            true                  2297        235          7
                         _optimizer_extended_cursor_sharing_rel   simple          simple                1907        224          7
                         _optimizer_fast_access_pred_analysis     true            true                  2326        278          7
                         _optimizer_fast_pred_transitivity        true            true                  2325        277          7
                         _optimizer_ignore_hints                  false           false                 2180        146          6
                         _optimizer_min_cache_blocks              10              10                    2244        178          6
                         _optimizer_mode_force                    true            true                  1891         43          7
                         _optimizer_percent_parallel              101             101                   2147         40          6
                         _optimizer_random_plan                   0               0                     2181        147          6
                         _optimizer_rownum_pred_based_fkr         true            true                  2241        175          7
                         _optimizer_skip_scan_enabled             true            true                  1934        134          7
                         _optimizer_skip_scan_guess               false           false                 2269        214          6
                         _optimizer_sortmerge_join_inequality     true            true                  2250        208          7
                         _optimizer_squ_bottomup                  true            true                  1928        132          7

FEATURE                  NAME                                     VALUE           DEF_VALUE        PARAMETER  OPT_PARAM      FLAGS
------------------------ ---------------------------------------- --------------- --------------- ---------- ---------- ----------
QKSFM_ALL                _optimizer_system_stats_usage            true            true                  1911        113          7
                         _optimizer_transitivity_retain           true            true                  2228        165          7
                         _parallel_broadcast_enabled              true            true                  1939         62          7
                         _partial_pwise_join_enabled              true            true                  2012         80          6
                         _partition_view_enabled                  true            true                  1900         44          7
                         _pga_max_size                            204800 KB       204800 KB             2054         25          6
                         _project_view_columns                    true            true                  1979        121          6
                         _px_broadcast_fudge_factor               100             100                   1940         63          6
                         _px_minus_intersect                      true            true                  1923        190          7
                         _px_numa_support_enabled                 true            true                  2397        323          6
                         _px_pwg_enabled                          true            true                  2230        166          7
                         _px_ual_serial_input                     true            true                  1922        213          7
                         _replace_virtual_columns                 true            true                  2139        204          7
                         _result_cache_auto_size_threshold        100             100                   1759        252          6
                         _result_cache_auto_time_threshold        1000            1000                  1760        253          6
                         _right_outer_hash_enable                 true            true                  2126        128          7
                         _row_shipping_explain                    false           false                 2264        217          6
                         _row_shipping_threshold                  80              80                    2263        216          6
                         _slave_mapping_enabled                   true            true                  2013        125          6
                         _smm_auto_cost_enabled                   true            true                  2058        102          6
                         _smm_auto_max_io_size                    248 KB          248 KB                2057         29          6
                         _smm_auto_min_io_size                    56 KB           56 KB                 2056         28          6
                         _smm_max_size                            52428 KB        52428 KB              2062         31          6
                         _smm_min_size                            262 KB          262 KB                2061         30          6
                         _smm_px_max_size                         131072 KB       131072 KB             2063         32          6
                         _sort_multiblock_read_count              2               2                     1863         20          6
                         _spr_push_pred_refspr                    true            true                  2162        129          6
                         _sql_compatibility                       0               0                     2370        305          6
                         _sql_model_unfold_forloops               run_time        run_time              2212        158          7
                         _suppress_scn_chk_for_cqn                nosuppress_1466 nosuppress_1466      65535        296         14
                         _table_scan_cost_plus_one                true            true                  2036         93          7
                         _trace_virtual_columns                   false           false                 2138        203          6
                         _update_bji_ipdml_enabled                0               0                    65535        161         14
                         _virtual_column_overload_allowed         true            true                  2140        205          6
                         _with_subquery                           OPTIMIZER       OPTIMIZER             2305        242          6
                         active_instance_count                    1               1                    65535         12          2
                         bitmap_merge_area_size                   1048576         1048576               1753         15          2
                         cpu_count                                2               2                    65535         11          2
                         db_file_multiblock_read_count            128             128                   1073        264          2
                         dst_upgrade_insert_conv                  true            true                  2346        286          2
                         flashback_table_rpi                      non_fbt         non_fbt              65535        169         10
                         hash_area_size                           131072          131072                1793         14          2
                         iot_internal_cursor                      0               0                    65535        293         10
                         is_recur_flags                           0               0                    65535        257          2
                         kkb_drop_empty_segments                  0               0                    65535        310         10
                         only_move_row                            0               0                    65535        315         10
                         optimizer_mode                           all_rows        all_rows              1890         38          3
                         optimizer_secure_view_merging            true            true                  2229        167          2
                         parallel_ddl_mode                        enabled         enabled              65535         37          2
                         parallel_dml_mode                        disabled        disabled             65535         36          2
                         parallel_query_mode                      enabled         enabled              65535         35          2
                         pga_aggregate_target                     262144 KB       262144 KB             2052         24          2
                         result_cache_mode                        MANUAL          MANUAL                1758        212          2
                         sort_area_retained_size                  0               0                     1862         17          2
                         sort_area_size                           65536           65536                 1861         16          2
                         total_cpu_count                          2               2                    65535        275          2
                         total_processor_group_count              1               1                    65535        324          2

FEATURE                  NAME                                     VALUE           DEF_VALUE        PARAMETER  OPT_PARAM      FLAGS
------------------------ ---------------------------------------- --------------- --------------- ---------- ---------- ----------
QKSFM_ALL                transaction_isolation_level              read_commited   read_commited        65535        218          2
                         workarea_size_policy                     auto            auto                  2055        101          2

QKSFM_AQ                 advanced_queuing_internal_cursor         0               0                    65535        287         10

QKSFM_CBO                _and_pruning_enabled                     true            true                  2365        300          7
                         _b_tree_bitmap_plans                     true            true                  1902         49          7
                         _cpu_to_io                               0               0                     1905         33          7
                         _default_non_equality_sel_check          true            true                  2038         95          6
                         _disable_datalayer_sampling              false           false                 1986         69          6
                         _disable_function_based_index            false           false                 2186        150          6
                         _extended_pruning_enabled                true            true                  2210        154          6
                         _fast_full_scan_enabled                  true            true                  1933         60          7
                         _gby_hash_aggregation_enabled            true            true                  2209        188          7
                         _generalized_pruning_enabled             true            true                  2085        109          7
                         _improved_outerjoin_card                 true            true                  2025         85          7
                         _index_join_enabled                      true            true                  2021         83          7
                         _like_with_bind_as_equality              false           false                 2035         92          6
                         _nested_loop_fudge                       100             100                   1978        120          6
                         _new_initial_join_orders                 true            true                  2039         96          7
                         _new_sort_cost_estimate                  true            true                  1913         52          7
                         _oneside_colstat_for_equijoins           true            true                  2040         97          6
                         _optim_adjust_for_part_skews             true            true                  2094        110          7
                         _optim_enhance_nnull_detection           true            true                  1936         61          7
                         _optim_peek_user_binds                   true            true                  2042         98          7
                         _optimizer_adjust_for_nulls              true            true                  2026         86          6
                         _optimizer_better_inlist_costing         all             all                   2242        176          7
                         _optimizer_cartesian_enabled             true            true                  2206        152          6
                         _optimizer_complex_pred_selectivity      true            true                  2254        186          7
                         _optimizer_compute_index_stats           true            true                  2095        144          7
                         _optimizer_connect_by_cb_whr_only        false           false                 2359        295          6
                         _optimizer_connect_by_cost_based         true            true                  2100        187          7
                         _optimizer_correct_sq_selectivity        true            true                  2169        149          7
                         _optimizer_cost_filter_pred              false           false                 2168        135          6
                         _optimizer_cost_model                    choose          choose                1909         51          7
                         _optimizer_enable_density_improvements   true            true                  2279        229          7
                         _optimizer_extended_cursor_sharing       udo             udo                   1906        162          7
                         _optimizer_extended_stats_usage_control  192             192                   2323        260          7
                         _optimizer_fkr_index_cost_bias           10              10                    2308        243          7
                         _optimizer_improve_selectivity           true            true                  2284        227          7
                         _optimizer_join_order_control            3               3                     2193        151          7
                         _optimizer_join_sel_sanity_check         true            true                  1935        137          7
                         _optimizer_max_permutations              2000            2000                  1981         23          7
                         _optimizer_mjc_enabled                   true            true                  2127        127          6
                         _optimizer_new_join_card_computation     true            true                  2077        107          7
                         _optimizer_or_expansion_subheap          true            true                  2302        249          6
                         _optimizer_rownum_bind_default           10              10                    2280        222          7
                         _optimizer_search_limit                  5               5                     2148         10          6
                         _optimizer_self_induced_cache_cost       false           false                 2243        177          6
                         _optimizer_sortmerge_join_enabled        true            true                  2128        136          6
                         _optimizer_star_trans_min_cost           0               0                     2303        240          6
                         _optimizer_star_trans_min_ratio          0               0                     2304        241          6
                         _optimizer_undo_changes                  false           false                 2146        118          6
                         _optimizer_undo_cost_change              11.2.0.3        11.2.0.3              1910         34          7
                         _optimizer_use_feedback                  true            true                  2371        306          7
                         _optimizer_use_subheap                   true            true                  2301        244          6
                         _ordered_nested_loop                     true            true                  1977         64          7

FEATURE                  NAME                                     VALUE           DEF_VALUE        PARAMETER  OPT_PARAM      FLAGS
------------------------ ---------------------------------------- --------------- --------------- ---------- ---------- ----------
QKSFM_CBO                _sort_elimination_cost_ratio             0               0                     1894         18          6
                         _system_index_caching                    0               0                     1984         68          6
                         _use_column_stats_for_function           true            true                  2028         88          7
                         cursor_sharing                           exact           exact                 1755         48          2
                         optimizer_capture_sql_plan_baselines     false           false                 2288        238          2
                         optimizer_dynamic_sampling               2               2                     2075        105          3
                         optimizer_features_enable                11.2.0.3        11.2.0.3              1858          9          2
                         optimizer_features_hinted                0.0.0           0.0.0                65535          1         10
                         optimizer_index_caching                  0               0                     1983         67          2
                         optimizer_index_cost_adj                 100             100                   1982         66          2
                         optimizer_mode_hinted                    false           false                65535          0         10
                         optimizer_use_pending_statistics         false           false                 2283        228          2
                         optimizer_use_sql_plan_baselines         true            true                  2289        239          2
                         parallel_ddl_forced_degree               0               0                    65535          5         10
                         parallel_ddl_forced_instances            0               0                    65535          6         10
                         parallel_dml_forced_dop                  0               0                    65535          4         10
                         parallel_execution_enabled               true            true                 65535          2          2
                         parallel_query_forced_dop                0               0                    65535          3         10
                         parallel_threads_per_cpu                 2               2                     1954         13          2
                         skip_unusable_indexes                    true            true                  2149        114          3
                         sqlstat_enabled                          false           false                65535         39         10
                         statistics_level                         typical         typical               2105        112          2

QKSFM_COMPILATION        _allow_level_without_connect_by          false           false                 2179        319          6
                         _connect_by_use_union_all                true            true                  2103        285          7

QKSFM_CVM                _complex_view_merging                    true            true                  1914         53          7

QKSFM_DML                _dml_frequency_tracking                  false           false                 2353        308          6

QKSFM_EXECUTION          _aggregation_optimization_settings       0               0                     2343        282          7
                         _deferred_constant_folding_mode          DEFAULT         DEFAULT               1867        301          6
                         _rdbms_internal_fplib_enabled            false           false                 1869        263          6
                         _rowsrc_trace_level                      0               0                     2240        173          6
                         cell_offload_compaction                  ADAPTIVE        ADAPTIVE              1879        267          2
                         cell_offload_plan_display                AUTO            AUTO                  1880        268          2
                         cell_offload_processing                  true            true                  1876        262          2

QKSFM_FBA                flashback_data_archive_internal_cursor   0               0                    65535        259         10

QKSFM_INDEX              optimizer_use_invisible_indexes          false           false                 2322        258          2

QKSFM_JPPD               _bloom_predicate_enabled                 true            true                  2198        234          6
                         _optimizer_extend_jppd_view_types        true            true                  2098        157          7
                         _optimizer_interleave_jppd               true            true                  2307        309          7
                         _optimizer_multi_level_push_pred         true            true                  2272        220          7

QKSFM_OR_EXPAND          _optimizer_or_expansion                  depth           depth                 2246        179          7

QKSFM_PARTITION          deferred_segment_creation                true            true                  2275        317          2
                         partition_pruning_internal_cursor        0               0                    65535        303         10

QKSFM_PQ                 _bloom_predicate_pushdown_to_storage     true            true                  2199        269          6
                         _bloom_pushing_max                       512             512                   2203        271          6
                         _bloom_vector_elements                   0               0                     2197        270          6
                         _optimizer_degree                        0               0                     2027         87          6
                         _optimizer_instance_count                0               0                     2331        294          6

FEATURE                  NAME                                     VALUE           DEF_VALUE        PARAMETER  OPT_PARAM      FLAGS
------------------------ ---------------------------------------- --------------- --------------- ---------- ---------- ----------
QKSFM_PQ                 _parallel_cluster_cache_policy           adaptive        adaptive              1957        291          6
                         _parallel_scalability                    50              50                    2314        292          6
                         _parallel_syspls_obey_force              true            true                  2315        261          6
                         _parallel_time_unit                      10              10                    2311        248          6
                         _px_partition_scan_enabled               true            true                  2377        311          7
                         _px_partition_scan_threshold             64              64                    2378        312          6
                         parallel_autodop                         0               0                    65535        289          2
                         parallel_ddldml                          0               0                    65535        290          2
                         parallel_degree                          0               0                    65535        246          2
                         parallel_degree_limit                    65535           65535                 2312        272          2
                         parallel_degree_policy                   manual          manual                1945        245          2
                         parallel_force_local                     false           false                 2313        273          2
                         parallel_hinted                          none            none                 65535        304         10
                         parallel_max_degree                      4               4                    65535        274          2
                         parallel_min_time_threshold              10              10                    2310        247          2
                         parallel_query_default_dop               0               0                    65535        256          2

QKSFM_STAR_TRANS         _optimizer_star_tran_in_with_clause      true            true                  2249        185          7
                         _optimizer_starplan_enabled              true            true                  2207        153          6
                         _optimizer_try_st_before_jppd            true            true                  2373        307          7
                         _optimizer_use_cbqt_star_transformation  true            true                  2362        298          7
                         star_transformation_enabled              false           false                 1903         50          2

QKSFM_TRANSFORMATION     _always_anti_join                        choose          choose                1898         41          7
                         _always_semi_join                        choose          choose                1976         42          7
                         _always_star_transformation              false           false                 1901         45          6
                         _bt_mmv_query_rewrite_enabled            true            true                  2008        139          6
                         _convert_set_to_join                     false           false                 1920         57          7
                         _cost_equality_semi_join                 true            true                  2037         94          7
                         _dimension_skip_null                     true            true                  2252        183          7
                         _distinct_view_unnesting                 false           false                 1925        141          6
                         _eliminate_common_subexpr                true            true                  1918         55          7
                         _enable_query_rewrite_on_remote_objs     true            true                  2281        223          6
                         _force_rewrite_enable                    false           false                 2253        184          6
                         _hash_join_enabled                       true            true                  1792         47          6
                         _max_rwgs_groupings                      8192            8192                  2010        320          6
                         _mmv_query_rewrite_enabled               true            true                  2007        138          7
                         _no_or_expansion                         false           false                 1980         65          6
                         _optimizer_cbqt_factor                   50              50                    1929        131          6
                         _optimizer_cbqt_no_size_restriction      true            true                  2234        170          7
                         _optimizer_coalesce_subqueries           true            true                  2278        276          7
                         _optimizer_cost_based_transformation     linear          linear                1927        126          7
                         _optimizer_dim_subq_join_sel             true            true                  2177        142          7
                         _optimizer_disable_strans_sanity_checks  0               0                     2178        143          6
                         _optimizer_distinct_agg_transform        true            true                  2342        281          7
                         _optimizer_distinct_elimination          true            true                  2270        219          7
                         _optimizer_distinct_placement            true            true                  2277        302          7
                         _optimizer_eliminate_filtering_join      true            true                  2345        284          7
                         _optimizer_enable_table_lookup_by_nl     true            true                  2388        316          7
                         _optimizer_enhanced_filter_push          true            true                  2237        171          7
                         _optimizer_false_filter_pred_pullup      true            true                  2382        313          7
                         _optimizer_filter_pred_pullup            true            true                  2099        172          7
                         _optimizer_filter_pushdown               true            true                  2393        318          6
                         _optimizer_free_transformation_heap      true            true                  2299        250          6
                         _optimizer_full_outer_join_to_outer      true            true                  2396        322          7
                         _optimizer_group_by_placement            true            true                  2276        221          7
                         _optimizer_join_elimination_enabled      true            true                  2208        168          7

FEATURE                  NAME                                     VALUE           DEF_VALUE        PARAMETER  OPT_PARAM      FLAGS
------------------------ ---------------------------------------- --------------- --------------- ---------- ---------- ----------
QKSFM_TRANSFORMATION     _optimizer_join_factorization            true            true                  2360        297          7
                         _optimizer_native_full_outer_join        force           force                 2296        233          7
                         _optimizer_nested_rollup_for_gset        100             100                   2316        254          6
                         _optimizer_null_aware_antijoin           true            true                  1899        156          7
                         _optimizer_order_by_elimination_enabled  true            true                  2248        180          7
                         _optimizer_outer_join_to_inner           true            true                  2395        321          7
                         _optimizer_outer_to_anti_enabled         true            true                  2247        181          7
                         _optimizer_push_down_distinct            0               0                     1926        116          7
                         _optimizer_push_pred_cost_based          true            true                  2097        155          7
                         _optimizer_reuse_cost_annotations        true            true                  2306        251          6
                         _optimizer_table_expansion               true            true                  2364        299          7
                         _optimizer_unnest_all_subqueries         true            true                  1917        288          6
                         _optimizer_unnest_corr_set_subq          true            true                  2341        280          7
                         _optimizer_unnest_disjunctive_subq       true            true                  2340        279          7
                         _or_expand_nvl_predicate                 true            true                  2034         91          7
                         _pivot_implementation_method             choose          choose                2300        237          7
                         _pre_rewrite_push_pred                   true            true                  2076        106          7
                         _pred_move_around                        true            true                  1919         56          7
                         _predicate_elimination_enabled           true            true                  1970        119          6
                         _push_join_predicate                     true            true                  1930         58          7
                         _push_join_union_view                    true            true                  1931         59          7
                         _push_join_union_view2                   true            true                  1932        145          7
                         _query_cost_rewrite                      true            true                  1997         72          6
                         _query_mmvrewrite_maxcmaps               20              20                    2261        198          6
                         _query_mmvrewrite_maxdmaps               10              10                    2260        197          6
                         _query_mmvrewrite_maxinlists             5               5                     2259        196          6
                         _query_mmvrewrite_maxintervals           5               5                     2258        195          6
                         _query_mmvrewrite_maxpreds               10              10                    2257        194          6
                         _query_mmvrewrite_maxqryinlistvals       500             500                   2265        201          6
                         _query_mmvrewrite_maxregperm             512             512                   2262        199          6
                         _query_rewrite_1                         true            true                  1999         74          6
                         _query_rewrite_2                         true            true                  1998         73          6
                         _query_rewrite_drj                       true            true                  2004         78          6
                         _query_rewrite_expression                true            true                  2001         75          6
                         _query_rewrite_fpc                       true            true                  2003         77          6
                         _query_rewrite_fudge                     90              90                    2000          8          6
                         _query_rewrite_jgmigrate                 true            true                  2002         76          6
                         _query_rewrite_maxdisjunct               257             257                   2005         27          6
                         _query_rewrite_or_error                  false           false                 1893         46          6
                         _query_rewrite_setopgrw_enable           true            true                  2184        148          7
                         _query_rewrite_vop_cleanup               true            true                  2006        124          6
                         _remove_aggr_subquery                    true            true                  1924        115          7
                         _selfjoin_mv_duplicates                  true            true                  2251        182          7
                         _simple_view_merging                     true            true                  1915        174          6
                         _subquery_pruning_enabled                true            true                  2031         89          7
                         _subquery_pruning_mv_enabled             false           false                 2032         90          7
                         _union_rewrite_for_gs                    yes_gset_mvs    yes_gset_mvs          2083        108          7
                         _unnest_subquery                         true            true                  1916         54          7
                         query_rewrite_enabled                    true            true                  1995         70          3
                         query_rewrite_integrity                  enforced        enforced              1996         71          2

321 rows selected.

There are a few parameters in there which look a little unexpected – why, for example, should deferred_segment_creation affect the optimizer ? One day I might get around to checking how many of these parameters do result in new child cursors being generated if you change them with an alter session or alter system – but it’s not a job I’m going to do in a hurry.

Oracle’s Big Data development team is Hiring a Product Manager

About Us and the Product

Big Data is here today. Leading-edge enterprises are implementing Big Data projects. Oracle has extended its product line to embrace the new technologies and the new opportunities in Big Data. The Big Data development team has recently introduced the Big Data Appliance, an engineered system that combines hardware and software optimized for Hadoop and Oracle NoSQL Database (More information on Oracle’s Big Data offerings can be found at oracle.com/bigdata). The Big Data development team is part of the data warehouse organization in Oracle’s Database Server Technologies division, a vibrant engineering organization with deep experience in scalable, parallel data processing, complex query optimization, and advanced analytics.

About the Role

This position is located at our headquarters location in Redwood Shores, CA (San Francisco Bay Area). We are seeking a product manager for the Big Data Appliance. As product manager, you would leverage your strong technical background in order to help define the roadmap for the Oracle Big Data Appliance, and become one of the faces of Big Data at Oracle. You will be actively writing collateral, delivering presentations, and visiting customers to ensure the success of Oracle Big Data Appliance and other products in Oracle’s Big Data portfolio, while also working internally within the development organization to ensure that the Big Data Appliance meets all of the current and future requirements of our customers. 

Learn more and apply: http://www.linkedin.com/jobs?viewJob=&jobId=2555091