Well it wasn't the post I planned to return to technical matters with.
Lots of readers here have asked me when I'm going to get round to
writing about 11g Incremental Statistics as part of the stats series. Although Incrementals are on my To Do list, I wanted to finish off the stats copying posts first. In any case, Randolf Geist got there already so I'll cross it off my list and point you towards his post instead.
Def v_secs=3600 -- bucket size Def v_days=1 -- total time analyze Def v_bars=5 -- size of one AAS in characters Def v_graph=80 col aveact format 999.99 col graph format a80 col fpct format 9.99 col spct format 9.99 col tpct format 9.99 col aas format 9.99 col pct1 format 999 col pct2 format 999 col first format a15 col second format a15 Def p_value=4 select to_char(start_time,'DD HH24:MI'), --samples, --total, --waits, --cpu, (total/&v_secs) aas, --round(fpct * (total/&v_secs),2) aas1, fpct*100 pct1, decode(fpct,null,null,first) first, --round(spct * (total/&v_s
January 16, 2012 I put together a test case to demonstrate how the physical reads autotrace statistic could exceed the consistent gets autotrace statistic if a single-pass or multi-pass workarea execution were performed during the execution of the SQL statement. If you are interested, you can see the test case in this recent OTN thread. [...]
At this time of year, I'm usually panicking about my Hotsos presentation/paper and up to my eyeballs in test scripts. I enjoy it and the absence feels a little strange, but I decided to do something different this year.
Although it's the 10th Symposium and I'm sure it will be as educational and fun as ever, I decided not to submit an abstract for a variety of reasons, one of which was the cluster of conferences that occur during the spring. Although the Oracle ACE Director program helps with travel expenses, the rest is self-funded including the time off work so I need to pick and choose my conferences carefully. For years Hotsos, Openworld and the UKOUG were fixtures but that means I've missed out on the wonders of Miracle spring events and the various Scandinavian boat trips!
Here is a summary of the findings while evaluating Incremental Partition Statistics that have been introduced in Oracle 11g.
The most important point to understand is that Incremental Partition Statistics are not "cost-free", so anyone who is telling you that you can gather statistics on the lowest level (partition or sub-partition in case of composite partitioning) without any noticeable overhead in comparison to non-incremental statistics (on the lowest level) is not telling you the truth.
Although this might be obvious I've already personally heard someone making such claims so it's probably worth to mention.
In principle you need to test on your individual system whether the overhead that is added to each statistics update on the lowest level outweighs the overhead of actually gathering statistics on higher levels, of course in particular on global level.
OK, so wordpress got hacked. I’ve had problems with this in the past and tried tactical surgery, but this time decided to do a full re-install. My first attempt today left me with the wordpress blank screen of death, so here I’m outlining the steps I took that finally got the new version working:
Browsing a little history recently I came across a note I’d written about the new-style index hint. In that note I claimed that:
… the index has to start with the columns (product_group, id) in that order – with preference given to an exact match, otherwise using the lowest cost index that starts the right way.
On reading this statement I suddenly realised that I hadn’t actually proved (to myself, even) that if I had the indexes (product_group, id) and (product_group, id, other_col) then a two-column hint forced Oracle to use the two column index in all (legal) circumstances.
So, tonight’s quiz – are there any edge cases, and what easy ways can you think of to prove (or disprove) the claim for the general case.
I’ve not become all religious. I’m just using this variant on new year to start my new year in earnest. The start of this year has been a little shaky for me. The first couple of days I was a little under the weather and I kinda decided to break all my planned new years resolutions to make myself feel better. Seeing as now we are half way through January, that process lasted a little longer than I expected.
I don’t really take new years resolutions very seriously, so instead I’m going to list a few things I am going to focus on in the coming year. Kinda like a mission statement to try and live up to.
UPDATE: thanks to Alex Gorbachev for pointing out that Oracle has a public yum for OEL which is compatible with REHEL and Oracle has Oracle certified packages:
Oracle Certified packages:
A small but time consuming LINUX issue:
When installing on RHEL for both 10 and 11, I get errors that the following libraries are missing
In my previous introductory IOT post, I illustrated how an Index Organized Table (IOT) might be worth consideration if most or all columns in a table were to be included within an index. I’m going to use a slightly different demo this time, replacing one of the columns with a much larger DESCRIPTION column, one which [...]