oracle

The basic formula for calculating the costs of a Nested Loop Join is pretty straightforward and has been described and published several times. In principle it is the cost of acquiring the driving row source plus the cost of acquiring the inner row source of the Nested Loop as many times as the driving row source dictates via the cardinality of the driving row source. Cost (outer rowsource) + Cost (inner rowsource) * Card (outer rowsource) Obviously there are cases where Oracle has introduced refinements to the above formula where this is no longer true. Here is one of these cases that is probably not uncommon. Let's start with a simple two table join that shows above formula in action. It represents a parent-child relationship where the parent table has 10,000 rows with a unique identifier, and a child table with 100 rows each that map to a single parent row, having 1,000,000 rows in total. which gives the following execution plan in 11.2: There are a couple of noteworthy comments: 1. T2.FK and T.ID have the same number of distinct keys each (10,000), so assuming corresponding primary and foreign key constraints in place this means that there is at least one matching row for every parent row. 2. There is a filter on T that restricts the driving row source to 500 rows. It is interesting to note that this filter results in a "biased join" where Oracle picks the NUM_DISTINCT from the other table for the join selectivity calculation rather than using the greatest NUM_DISTINCT of both, in this case arriving at a correct cardinality estimate of approx. 50,000 3. The T2_IDX index has a worst-case clustering factor due to the scattering of T2.FK via the MOD function, hence the resulting cost of a single iteration of the Nested Loop join is 100 for picking up 100 rows from the table (plus 2 for the index access) The overall cost for the loop is 45 for acquiring the driving row source plus 500 times 102, the remaining part being CPU costing overhead. This matches above formula. Now let's re-create table T2 using a different distribution of the T2.FK column: The only difference is now that the FK column no longer has 10,000 distinct keys but only 500. Of course on average now 2,000 rows in T2 match a parent row in T, but obviously no longer all of them have a match in T2. Let's review the execution plan for the same SQL statement as above: Since now 2,000 rows match a single parent key on average, the cost per iteration has increased accordingly. The index range scan needs to visit some root / branch blocks plus four leaf blocks on average, resulting in a cost of 5. The table access needs to visit 2,000 rows on average, hence with the same worst-case clustering factor the cost per iteration is 2,000 plus CPU overhead. Given the fact that we need to do this 500 times the final cost of the Nested Loop join ought to be something close to one million, but surprisingly it is only a little bit higher than the cost of the previous scenario. So clearly the original formula doesn't apply here, although the cost for a single iteration in the execution plan seems to match the expectations. It looks like Oracle a long way back introduced a refinement to the original formula in the case of less distinct keys of the inner row source join column than the driving row source join column. The idea behind it seems to be that this is what Oracle calls a "sparse" join. Obviously not every row from the driving row source will find a match in the inner row source, hence some of the loop iterations should end with the index lookup, not finding a match and therefore no need to visit the inner table afterwards. The refinement hence calculates a lower average cost of the table visit per loop iteration. This is of course true when looking at the join by itself. But if you are unlucky and a corresponding filter on the driving row source turns the "sparse" join into a (rather) "dense" join, then this refinement can lead to a significant cost underestimate of a Nested Loop join, potentially driving the optimizer towards favouring that join method over others. And it is exactly that scenario what above query simulates: The filter on T results in a full match of all 500 driving rows, and the actual cost of the Nested Loop join ought to be closer to one million than 50,000 in this case. The refinement to the cost calculation seems to be based on the ratio between the NUM_DISTINCT of two join coin columns: In my example the ratio is 10,000:500, so the overall cost is only approx. 1/20 of the original cost. There are some further details how the formula deals with a small number of loop iterations. For example, the first iteration will get the full original cost, a number of further iterations (again seems to correspond to the ratio, here for example 20) get only the index cost added, and from then on the costing of the table access levels at the original cost downscaled by the ratio (2000 / 20 which is 100 in this case). The refinement obviously has been added in release 10.1.0.3 and can be found in the Fix Control list as bug number 3120429. The text for this fix is: "account for join key sparsity in computing NL index access cost" and apparently only applies if the inner row source uses an index access. This also means that the original costing can be activated by disabling the fix: So if you happen to have such cases where the filter on a driving row source leads to an underestimate of the Nested Loop join cost as outlined here, using the fix control allows arriving at more reasonable costing figures. I recently had such a case at a client where only a small part of a fully populated time dimension was used in the foreign key of a fact table, but the filter on the time dimension lead to exactly the scenario described here - the join wasn't really sparse and the Nested Loop join cost was significantly underestimated.

Today I have fixed an issue related with serial direct path reads.

There is 11.2 database

SQL> select * from v$version where rownum = 1;
 
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
 

SQL> SELECT a.ksppinm "Parameter", b.ksppstvl "Session Value"
  2    FROM x$ksppi a, x$ksppcv b
  3   WHERE a.indx = b.indx
  4     AND a.ksppinm = '_serial_direct_read';
 
Parameter                Session Value
------------------------ ----------------------------
_serial_direct_read      auto

There is a very small table, let’s call it dualcopy.

SQL> exec dbms_stats.gather_table_stats(user, 'dualcopy', estimate_percent => 100);
 
PL/SQL procedure successfully completed
 
SQL> select object_type,
  2         num_rows,
  3         blocks,
  4         empty_blocks
  5    from all_tab_statistics
  6   where table_name = 'DUALCOPY';
 
OBJECT_TYPE    NUM_ROWS     BLOCKS EMPTY_BLOCKS
------------ ---------- ---------- ------------
TABLE                 1          1            0

Let’s try to do select from the table with enabled 10046 event:

SQL> alter session set events '10046 trace name context forever, level 8';
 
Session altered

SQL> select * from dualcopy;
 
DUMMY
-----
X

Below is an excerpt from the trace file

PARSING IN CURSOR #18446744071497556472 len=24 dep=0 uid=2500 oct=3 lid=2500 tim=3988723224325 hv=250290826 ad='3e911e388' sqlid='g32a9w87fq8na'
select * from dualcopy
END OF STMT
PARSE #18446744071497556472:c=20000,e=26430,p=0,cr=27,cu=0,mis=1,r=0,dep=0,og=2,plh=769194902,tim=3988723224324
EXEC #18446744071497556472:c=0,e=21,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=2,plh=769194902,tim=3988723224417
WAIT #18446744071497556472: nam='SQL*Net message to client' ela= 4 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=3988723224541
WAIT #18446744071497556472: nam='SQL*Net message from client' ela= 66047 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=3988723290684
WAIT #18446744071497556472: nam='direct path read' ela= 7852 file number=705 first dba=23954 block cnt=1 obj#=2229858 tim=3988723301159
WAIT #18446744071497556472: nam='SQL*Net message to client' ela= 5 driver id=1413697536 #bytes=1 p3=0 obj#=2229858 tim=3988723301360
FETCH #18446744071497556472:c=10000,e=10521,p=1,cr=2,cu=0,mis=0,r=1,dep=0,og=2,plh=769194902,tim=3988723301437
STAT #18446744071497556472 id=1 cnt=1 pid=0 pos=1 obj=2229858 op='TABLE ACCESS FULL DUALCOPY (cr=2 pr=1 pw=0 time=10419 us cost=2 size=2 card=1)'

What?! serial direct path read for the table in one block?! What’s going on?

The table can be small, but if your application accesses it many times it can lead to significant amount of physical I/O.

Serial direct read mode (_serial_direct_read) is the property of the child cursor. Similar behavior is possible if the same query was parsed early in ALWAYS or TRUE mode (_serial_direct_read=always or _serial_direct_read=true). If we pay attention on the parse call, it was a hard parsing (mis=1) and new child cursor. Thus, this is not the reason in our case.

The reason is that the table has been configured for using of KEEP pool, but database has not.

SQL> select table_name, buffer_pool
  2    from all_tables
  3   where table_name = 'DUALCOPY';
 
TABLE_NAME                     BUFFER_POOL
------------------------------ -----------
DUALCOPY                       KEEP

SQL> show parameter db_keep_cache_size
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_keep_cache_size                   big integer 0

This is the bug 12530276. Oracle 11.2.0.2 treats all ‘keep objects’ as large objects when ‘keep pool’ is not configured.

Allocation of KEEP pool fixes the problem.

THE WICKED WITCH OF THE WEST NEEDS HELP BE IT KNOWN BY THESE PRESENTS that the Wicked Witch of the West needs your help to create a magic spell to ensure that the Third Annual Witching & Wizarding Ball is a grand success. A great tournament has been organized for practitioners of the arts of [...]

May 20, 2012 An interesting quirk was recently brought to my attention by Mich Talebzadeh.  He generated a 10046 trace at level 8 for a session, executed some SQL statements, disabled the trace, and then processed the resulting trace file with TKPROF.  His TKPROF output included the following: UPDATE TESTWRITES SET PADDING1 = RPAD('y',4000,'y') WHERE [...]

Although it’s great fun to travel the world speaking at events such as Oracle Openworld, Collaborate and ODTUG KScope, Rittman Mead in the UK still consider the UK Oracle User Group Conference, held in Birmingham in November or December each year, to be our “home” conference and our main showcase for what we’ve been up to in the last year. Like most of the big annual conferences, the UKOUG event has multiple streams and the one we’re particularly interested in is the Business Intelligence and Data Warehousing stream, covering topics and products such as OBIEE, ODI, Essbase and Oracle OLAP, BI methodologies and the BI Applications.

Jon Mead is on the board of directors for the UKOUG, and both Jon and I have chaired the Business Intelligence & Reporting Tools SIG within the UKOUG in previous years. I’m particularly keen that we get some good, new and in-depth content around Oracle BI, DW and ETL for this year’s event, and I’d imagine papers on the following topics might go down well:

• OBIEE development methodologies and best/right practices
• OBIEE metadata modeling techniques, particularly against OLTP sources, OLAP sources and federating disparate data sources
• Dashboard design best practices, and anything around effective / innovative visualizations
• Presentations around data mining, Oracle R, big data and Hadoop
• Tips and techniques around ODI, including migrations from OWB and Informatica
• Papers around mapping, mobile, scorecards or any of the other OBIEE extensions introduced with 11g
• Techniques to get the best out of the BI Apps, including handling upgrades, preparing for the Fusion Apps, and combining packaged data models with custom ones

I’ll be putting a paper in on real-world use of the Exalytics In-Memory Machine, based on testing we’ll be doing over the summer along with feedback from customer PoCs. All speakers get a three-day pass to attend all of the other sessions, and if you get accepted you’ll be presenting at Europe’s biggest Oracle conference as well as knowing you’re contributing to a great event. There’s also a number of great social events and awards over the three days of the conference, and the photo below is of Debra Lilley (UKOUG President) and Lisa Dobson (Vice President) giving the awards whilst Vikki Lira (OTN and the ACE Program) stands in the foreground.

The call for papers website is here, and you’ve got until the 1st of June to get your abstracts in. Hopefully I’ll see some of you attend, and maybe present,

Last week i had to investigate a perf issue on the ACCEPTANCE environment which is a restore of the production database, I had to compare the awr reports of a monthly job. On production I've set a retention of 40 days in order to be able to debug issues with a monthly job and to have some buffer. We restore ACCEPTANCE from PRODUCTION backups and the change dbname and dbid with nid (for some reason aka bug we are not able to duplicate the db with rman) as you can see from the sql hereunder the retentention is different for the PROD db (dbid 2789041670) as for it's cloned brother ACC (1857427655) select * from DBA_HIST_WR_CONTROL;

DBID              SNAP_INTERVAL RETENTION      TOPNSQL --------             --------------------------        -------------------      --------------- 2789041670 0 0:20:0.0                           40 0:0:0.0            DEFAULT 1857427655 0 1:0:0.0                             7 0:0:0.0               DEFAULT

I’m just back now from Atlanta, having been over there for the past week helping run the second week of the RM BI Forum 2012. Around 55 BI professionals from around the USA (and with a few from Europe) got together over four days to network, share tips and techniques around Oracle BI development, meet the Oracle PMs, and enjoy themselves downtown in Atlanta, GA.

The format of the US BI Forum followed the same structure as the UK one, with Kevin McGinley providing the masterclass on the Tuesday, the main conference running on Wednesday and Thursday, and the NDA BI Developer day on the Friday organized in conjunction with Oracle. Kevin’s session was if anything even better received than in Brighton, with topics ranging from the Action Framework through Oracle Scorecard & Strategy Management, UI customization and of course BI Mobile. Thanks again to Kevin for taking the time to develop the materials, and then join us over two weeks to deliver the masterclass and then take part in the event itself.

The main conference then kicked-off with Tim and Dan Vlamis talking about dashboard best practices, then went on to cover Endeca, OBIEE performance tuning, security, Exalytics, RPD data modeling, big data and the new 11.1.1.6.2 SampleApp. As with Brighton, we ran a number of 10-minute sessions over the two days, including some Ignite-style sessions that had slides that auto-advanced every thirty seconds, and TED-style sessions where the speaker covered a controversial or counter-intuitive topic with minimal slides and sometimes props. Here’s Christian Screen, from Cap Gemini and ArtofBI.com, delivering his Ignite session on how to become an Oracle ACE.

The Best Presentation Award went in the end to our own Jordan Meyer, who talked about the wider world of data visualizations including examples such as the Billion Dollar Gram, a facebook network visualization created using R, and other examples created using Oracle’s R toolkit and embedded in Oracle BI dashboards. Jordan had a great relaxed but engaging presenting manner, covered some hot new technology and even managed to create a visualization based on Stewart’s comments about Apple on our internal mailing list, shown in the photo below along with the subject.

Although the event is primarily community organized, we had some exceptional support from the Oracle BI product management/development team again this year in Atlanta, including Matt Bedin who heads-up developer outreach for Oracle BI, Philippe Lions who demonstrated the new 11.1.1.6.2 SampleApp and provided a beta version for delegates to take away with them, and Pravin Janardanam who recently joined the product management team and is responsible for the metadata elements of the BI Server. We were also privileged to be joined for the second year by Jean-Pierre Dijcks who ran a whiteboarding session on big data, and Adam Ferrari, ex-CTO of Endeca who talked about the Oracle Endeca Information Discovery platform and analyzed, live, the tweet stream from this week’s, and the previous week’s, attendees. Thanks again to everyone from Oracle, especially for staying around for all four days and taking part in all of the sessions.

Friday, as with the Brighton week, was a special BI Developer day organized in conjunction with Oracle BI product development and held under NDA (non-disclosure agreement), where we were taken through the product roadmap in more detail and looked in particular at a couple of significant changes/developments in the OBIEE product architecture. Of course I can’t go into any detail now, but thanks again to Oracle for this and watch this space for insight and analysis once things become public.

So that’s it for now. I’ll do one final blog post early next week to post all of the presentation PDFs, and photos from the US event can be viewed in this BI Forum Atlanta 2012 Flickr set. Thanks again to everyone, and no doubt we’ll start planning the 2013 event very soon!

Harbingers is the tenth book in the Repairman Jack series by F. Paul Wilson.

The last of Jack’s relatives are gone. Are his girlfriend (Gia), her daughter (Vicky) and Jack’s unborn child the next in line? Is there anything Jack can do to protect them?

This book focuses more on Jack’s relationship to “The Otherness” and “The Ally”. We see a more aggressive side of Jack, as well as the cold calm detachment when he’s doing his job. Dark, dark, dark, but also kinda exciting.

Cheers

Tim…

 

 

---

Repairman Jack : Harbingers was first posted on May 19, 2012 at 3:16 pm. ©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

I've built up quite a list of upcoming events that I want to blog about, so I'll deal with them in a single post (... then the OUGN review and then *finally*, *maybe* a technical post or three ...) Chris Date SeminarI think I've seen this mentioned in a few blog posts already, but Peter Robson is organising a 2-day seminar presented by relational database luminary, C. J. Date. I was lucky enough to attend a single day listening to Chris in Edinburgh a few years ago now and, whilst I might not have agreed with everything he said that day (so sue me), the clarity and force of his arguments and the intellectual rigour of the discussion was a contrast to the rather more lightweight content of most conference presentations. Definitely recommended and you can find more details here Throw in a copy of his latest book and, if I wasn't taking so many days off work at the moment anyway, I would definitely be there. OUG ScotlandSpeaking of too many days off work ... It's been too long since I've been to the OUG Scotland Conference - at least over the past 3 years that I've been working in London. Otherwise I would be there this year and it's probably about time I was going back next year. It's a cracking event. Free registration; free beer; an absolutely stellar agenda that the Scottish user group types like Thomas Presslie always pull together; and, of course, it's in the absolutely best part of the world Given that it costs nothing to attend, I think that anyone who can't find a way of getting a day out of the office to hear some fantastic speakers needs their head examined. (Oh, a little bit of local information. Oracle's place in Linlithgow - where I used to work for a while when it was Sun's place - might look a little out of the way but the train service to Edinburgh is regular, fast and excellent. UKOUG Call For PapersThere's only a few weeks left to submit an abstract for one of the finest Oracle conferences - the UKOUG conference in (erm) sunny Birmingham. It was the first Oracle conference I presented at so, like Tom Kyte, I would encourage as many people to give it a try as possible (as I have with many people over the years). I've discussed this with a few people recently and they seem a little frightened to submit an presentation abstract for such a well-known conference with big-name speakers, but they seem to be forgetting something. UKOUG is a User Group. The whole point is for other users to hear your experiences and some of the best presentations I've attended have been from inexperienced speakers sharing their Real World experiences of using Oracle. Sure, I want to hear deeply technical presentations based on experimentation, but a user group conference is about so much more than that and I dread the day when I see an agenda filled with the same old speakers I've heard many times before. Consider this, too ... How did those speakers become experienced and well-known? We all have to start somewhere and UKOUG is as good as anywhere. Of course, having an abstract accepted can be tough, but unless you give it a try, how will you know? Having been so positive about the conference over the years and encouraged people to present, I can't help being honest in saying that if UKOUG had stuck to their original idea of giving speakers a one-day pass to the conference, rather than the standard full conference pass (and when I say standard, I mean like every other conference does!) then I wouldn't have recommended it. Fortunately they've performed a U-turn on what I always considered a ridiculous decision Personally, I found it a bit cheap and strangely hurtful so I'm still not sure whether I'll submit an abstract this year, but it shouldn't stop others and might lead to a wider and more varied agenda. Regardless, the agenda is always excellent. Red Gate SeminarsThere have been several times over the past few years when I've considered producing an online video of my OEM Performance Page presentations but it hasn't happened for various reasons. I recall Alex Gorbachev suggesting I repeat my (failed) Hotsos version for Pythian to post online but I never got round to it. So I was absolutely delighted when Red Gate Software asked me if I had any ideas for online webinars to supplement their existing series! I have two sessions planned in the near future. One based on the original presentation that covers Performance Page fundamentals and a second focussing more closely on the new OEM 12 features, including ASH Analytics. You can see a list of upcoming seminars here. Even better, it includes archived copies of previous presentations by some of the best Oracle presenters around. Definitely worth a look. I'll post more details on by own webinars nearer the time. Enkitec E4Last, but definitely not least, my friends at Enkitec are organising an Exadata-focussed conference in Dallas - E4. Again, although there are some pre-selected speakers, there is also an open Call For Papers so if you feel you have something interesting to say about Exadata, the opportunity is there. I'm definitely planning on going and will hopefully be giving a new Parallel Query presentation.

Attendees to this year’s ODTUG Kscope conference will have the opportunity to get involved in a live software development experience on the Red Gate exhibition stand (stand no. 424 – 425). Attendees will provide input to go from zero to first prototype in 3 days through a series of rapid development sessions live on the stand.

Monday’s feedback is Tuesday’s features

The entire stand will be turned into a mini development lab to create a prototype for an Oracle schema version control tool over the course of the 3 exhibition days. Red Gate will be involving 100′s of people in rapid feedback sessions during 9 sprints to develop a paper prototype and then using this feedback to build an HTML prototype. The prototype will constantly evolve based on the feedback from Kscope attendees: features will be added, deleted and improved, and the prototype may even be scrapped completely several times. The main thing is that Oracle developers and DBAs can define precisely how they want this tool to work, and this will go straight into the final product. Find out more…

A unique two-way learning experience

Participants will get to experience rapid prototyping, agile development and learn how a company like Red Gate develops software. At the same time Red Gate will gain loads of feedback on the features, UI and requirements for an Oracle version control tool. So if you’re making your way to Kscope12, please join in on stand 424 – 425 for a unique two-way learning experience.

Not going to Kscope12? Join in here

Daily update

As the prototype progresses we’ll be keeping a blog post up-to-date on All Things Oracle.com and including photos. Please keep checking back to see how Kscope attendees and the Red Gate team are getting on and use the comments function to add your feedback.

Current version of the HTML prototype

A link to the most recent version of the HTML prototype will be put up after the first round of feedback and development sessions on Monday 25 June.

Fill out our quick survey

If you have some time now, please fill in Red Gate’s short survey: Oracle Database Source Control Survey

Be part of the Early Access program

Please email oracletools.info@red-gate.com with ‘Source Control for Oracle EAP’ as your subject line and Red Gate will be in touch.

How will the tool work?

The tool – currently named Source Control for Oracle – will connect to your existing version control system to automatically store a complete history of your Oracle database schema changes. With your database under source control (just like your application code) your development team can share, track and understand schema changes quickly and easily. Find out more…

I was in a discussion recently about how to estimate the size of a bitmap index before you build it, and why it’s much harder to do this for bitmap indexes than it is for B-tree indexes. Here’s what I wrote in “Practical Oracle 8i”:

An interesting feature of bitmap indexes is that it is rather hard to predict how large the index segment will be. The size of a b-tree index is based very closely on the number of rows and the typical size of the entries in the index column.  The size of a bitmap index is dictated by a fairly small number of bit-strings which may have been compressed to some degree depending upon the number of consecutive 1’s and 0’s.   To pick an extreme example, imagine a table of one million rows that has one column that may contain one of eight values ‘A’ to ‘H’ say, which has been generated in one of of the two following extreme patterns:

• All the rows for a given value appear together, so scanning down the table we get 125,000 rows with ‘A’ followed by 125,000 rows of ‘B’ and so on.
• The rows cycle through the values in turn, so scanning down the table we get ‘A’,’B’. . . ‘H’ repeated 125,000 times.  

What will the bitmap indexes look like in the two cases?  

For the first example, the basic map for the ‘A’ value will be 125,000 one-bits, followed by 875,000 zero bits – which will be trimmed off.  Splitting the 125,000 bits into bytes and adding the necessary overhead of about 12% we get an entry for the ‘A’ rows of 18K.  A similar argument applies for each of the values ‘B’ to ‘H’, so we get a total index size of around 8 x 18K – giving 156K.  

For the second example, the basic map for the ‘A’ value will be a one followed by 7 zeros, repeated 125,000 times.  There is no chance of compression here, so the ‘A’ entry will start at 125,000 bytes.  Adding the overhead this goes up to 140K, and repeating the argument for the values ‘B’ to ‘H’ we get a total index of 1.12 MB.  

This wild variation in size looks like a threat, but to put this into perspective, a standard B-tree index on this column would run to about 12 Mb irrespective of the pattern of the data.  It would probably take about ten times as long to build as well.

I wrote up a test case to recreate this model some time ago, so here it is with the results from an instance of 11.1.0.7:

create table t1
as
with generator as (
        select  --+ materialize
                rownum id 
        from dual 
        connect by 
                level <= 10000
)
select
        chr(65 + mod(rownum-1,8))               bit_scattered,
        chr(65+trunc((rownum-1)/125000))        bit_clustered
from
        generator       v1,
        generator       v2
where
        rownum <= 1e6
;

create bitmap index t1_b1_scattered on t1(bit_scattered);
create bitmap index t1_b1_clustered on t1(bit_clustered);

select
        index_name, leaf_blocks, 8 * leaf_blocks KB
from
        user_indexes
where
        table_name = 'T1'
;

set doc off
doc

Results from 11.1.0.7
---------------------

INDEX_NAME           LEAF_BLOCKS         KB
-------------------- ----------- ----------
T1_B1_SCATTERED              164       1312
T1_B1_CLUSTERED               24        192

2 rows selected.
#

So, no big change there, then. If you modify the code to create B-tree indexes you’ll find they are 14MB each if you don’t use compression, 12MB if you do.

I just checked to find out that there has been 3,000 downloads of SLOB – The Silly Little Benchmark. People seem to be putting it to good use. That’s good.

Before I get very far in this post I’d like to take us back in time–back before the smashing popularity of the Orion I/O testing tool.

When Orion first appeared on the scene there was a general reluctance to adopt it. I suspect some of the reluctance stemmed from the fact that folks had built up their reliance on other tools like bonnie, LMbench, vxbench and other such generic I/O generators. Back in the 2006 (or so) time frame I routinely pointed out that no tool other than Orion used the VOS layer Oracle I/O routines and libraries. It’s important to test as much of the real thing as possible.

Who wants to rely on an unrealistic platform performance measurement tool after all?

My “List” Over time I built a list of reasons I could no longer accept Orion as sufficient for platform I/O testing. Please note, I just wrote “platform I/O testing” not “I/O subsystem testing.”  I think the rest of this post will make the distinction between these two quoted phrases quite clear. The following is a short version of the list:

• Orion does not simulate Oracle processing in any way, shape or form. More on that as this blog series matures.
• Orion is what I refer to as mindless I/O. More on that as this blog series matures.
• Orion is useless in assessing a platform’s capability to handle modify-intensive DML (thus REDO processing, LGWR and DBWR, etc). More on that as this blog series matures.

My present-tense views on Orion sometimes surface on twitter where I am occasionally met with vigorous disagreement–most notably from my friend Alex Gorbachev. Alex is a friend, co-member of the Oaktable Network, CTO of Pythian (I love those Pythian folks), and someone who generally disagrees with most everything I say.

I respect Alex, because he has vast knowledge and valuable skills. His arguments make me think. That’s a good thing. I’m not sure, however, our respective spheres of expertise overlap.

So how do these disagreements regarding SLOB get started? Recently I tweeted:

The difference between SLOB and Orion is akin to Elliptical trainer versus skiing on the side of a mountain.

Alex replied with:

I could just as well argue that SLOB is useless because that’s not real workload anyway and you should test with your app

This quick exchange of ideas set into motion some Pythian testing by Yury. As it turns out I think the goal of that test was to prove parity between SLOB and Orion for random reads–and perhaps not much more.  If only I have published “My List” above before then.

Yury’s tests were good, albeit, exceedingly small in scope. His blog post suggests more testing on the way. That is good. If you read the comment thread on his blog entry you’ll see where I thank Yury for a good tweak to the SLOB kit that eliminates the db file parallel reads associated with the index range scans incurred by SLOB reader processes. Come to think of it though, Matt from Violin Memory pointed that one out to me some time back. Hmm, oh well, I digress. The modifications Yury detailed (init.ora parameters) will be included in the next drop of the SLOB kit. Again, thanks Yury for the testing and the init.ora parameter change recommendations!

Feel free to see Yury’s findings. They are simple: SLOB and Orion do the same thing. Really, SLOB and Orion do the same thing? Well, that may be the case so long as a) you compare SLOB to Orion only for simple random read testing and/or b) your testing is limited to a little, itsy-bitsy, teeny, tiny, teensy, minute, miniscule, meager, puny, Lilliputian-grade undersized I/O subsystem incapable of producing reasonable, modern-scale IOPS.  Yury’s experiment topped out at roughly 4,500 random read IOPS.  I’ll try to convince you that there is more to it than that (hint, modern servers are fit for IOPS in the 20,000/core range). But first, I have two quotable quotes to offer at this point:

When assessing the validity of an I/O testing tool, do so on a system that isn’t badly bottlenecked on storage.

If your application (e.g., Oracle Database)  is “mindless” use a “mindless” I/O generator–if not, don’t.

Mindless I/O So what do I mean when I say “mindless I/O?”  The answer to that is simple. If the code performs an I/O into a memory buffer, without any application concurrency overhead, and no processes even so much as peeks at a single byte of that buffer populated through DMA from the I/O adapter device driver–it’s mindless. That is exactly how Orion does what it does. That’s what every other synthetic I/O generator I know of does as well.

So what does mindless I/O look like and why does it show up on my personal radar as a problem? Let’s take a look–but first let me just say one thing–I analyze I/O characteristics on extremely I/O capable platforms. Extremely capable.

The following screen shot shows a dd(1) command performing mindless I/O by copying an Oracle OMF datafile from an XFS file system to /dev/null using direct I/O. After that another dd(1) was used to show the difference between “mindless” and meaningful I/O. The second dd(1) was meaningful because after each 1 MB read the buffer is scanned looking for lower case ASCII chars to convert to their upper-case counterpart. That is, the second dd(1) did data processing–not just a mindless tickling of the I/O subsystem.

The mindless I/O was 2.5 GB/s but the meaningful case fell to about 1/6th that at 399 MB/s. See, CPU matters. It matters in I/O testing. CPU throttles I/O–unless you are interested in mindless I/O. What does this have to do with Orion and SLOB? A moment ago I mentioned that I test very formidable I/O subsystems commensurate with modern platforms–so hold on to your hat while I tie these trains of thought together.

Building on my dd(1) example of mindless I/O, I’ll offer the following screen shot which shows Orion accessing the same OMF SLOB datafile (also via direct I/O validated with strace). Notice how I force all the threads of Orion (it’s threaded with libpthreads) to OS CPU 0 using numactl(8) on this 2s12c24t Xeon 5600 server?  What you are about to see is the single-core capacity of Orion to perform “mindless I/O”:

Unrealistic Platform Performance Measurement Tools This is only Part I in this series.  I’ll be going through a lot of proof points to solidify backing for my Orion-related assertions in the list above, but please humor me for a moment. I’d like to know just how realistic are platform performance measurements from an I/O tool that demonstrates capacity for 144,339 physical 8K random IOPS while pinned to a single core of a Xeon 5600 processor?

We are interested in database platform IOPS capacity, right?

Through this blog series I aim to help you conclude that any tool demonstrating such an unrealistic platform performance measurement is, well, an unrealistic platform performance measurement tool.

Do you feel comfortable relying on an unrealistic platform performance measurement tool? Before I crafted SLOB I too accepted test results from unrealistic platform performance measurement tools but I learned that I needed to include the rest of the platform (e.g., CPU, bus, etc) when I’m studying platform performance so I left behind unrealistic platform performance measurement tools.

Until recently I didn’t spend any time discussing measurements taken from unrealistic platform performance measurement tools. However, since friends and others in social media are pitting unrealistic platform performance measurement tools against SLOB (not an unrealistic platform performance measurement tool) such comparisons are blog-worthy. Hence, I’ll trudge forward blogging about how unrealistic certain unrealistic platform performance measurement tools are. And, if you stay with me on the series, you might discover some things you don’t know because, perhaps, you’ve been relying on unrealistic platform performance measurement tools.

As this series evolves, I’ll be sharing several similar unrealistic platform performance measurement tool results as I go though the list above. That is, of course, what motivated me to leave behind unrealistic platform performance measurement tools.

Final Words For This Installment In Yury’s post he quoted me as having said:

It’s VERY easy to get huge Orion nums

His assessment of that quote was, “kind of FALSE on this occasion.”  Having now shown what I mean by “VERY easy” (e.g., even a single core can drive massive Orion IOPS) and “huge Orion” numbers  (e.g., 144K IOPS), I wonder whether Yury will be convinced about my assertions regarding unrealistic platform performance measurement tools? If not yet, perhaps he, and other readers will eventually. After all, this is only Part I. If not, Yury, I still want to say, “thanks for testing with SLOB and please keep the feedback coming.”

Alex and I may always disagree

Oh, by the way folks, if all you have is Orion, use it. It is better than wild guesses–at least a little better.

Filed under: oracle

The Third International NoCOUG SQL & NoSQL Challenge sponsored by Pythian—Love Your Data™ will be revealed on Monday, May 21 at 9 AM PST at http://bit.ly/JvJS46. In this challenge, the Wicked Witch of the West needs help in creating a magic spell to ensure that the Third Annual Witching & Wizarding Ball is a grand [...]

When you have something insightful to say about one of these topics:

Business Intelligence & Data Warehousing Business Strategy Development Fusion Middleware Oracle E-Business Suite Oracle RDBMS Emerging Technologies MySQL Engineered Systems Infrastructure

Then take the chance to submit your abstract for the UKOUG Conference 2012 – it has a strong international reputation apart from being the the most important educational and networking event in the UK of the year for Oracle topics.

Go here to apply until Friday 1st June 2012.

Tagged: advert

Imagine, if you will, that someone invented time travel. What would you do with it? Nip back into the past and fix that error you made? And maybe you’d want to do something about that silly, ill-advised haircut you had when you were 17. Well, the good news is that, in a manner of speaking, time travel has been invented. At least as far as Oracle databases go.

And the bad news?  There’s nothing it can do about your teenage haircut.

Oracle Flashback Technology is a suite of features that enables Oracle application developers and database administrators to wind the clock back and examine their database objects and data in a previous state without having to resort to backup media.

In this series of articles I intend to outline the benefits of Oracle Flashback to application developers and to database administrators. This first installment will concentrate on the tools that will be of interest to application developers.

Oracle Flashback Technology is actually a collection of features that enables the developer to examine the database’s recent past in a number of ways:

Oracle Flashback Query:

Flashback query is a neat trick. It allows you run a query against your data as it was at past time. Imagine you accidentally delete a number of rows and commit your changes. With flashback query you can easily reach into the past and retrieve those lost rows.  Using the SELECT… AS OF syntax you can examine the ghost of data past by referencing its timestamp.

SELECT *
FROM emp AS OF TIMESTAMP TO_TIMESTAMP('2012-04-21 17:00:00','YYYY-MM-DD HH24:MI:SS');

And if you wanted to view those rows that you accidentally deleted 5 minutes ago?

SELECT *
FROM emp AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '6' MINUTE)
MINUS
SELECT *
FROM emp;

Oracle Flashback Version Query:

While flashback query seems pretty much like magic, it actually comes with a cherry on it. Flashback Version Query has the power to not only resurrect bygone data, it can also give you important metadata about those rows. You can find out the exact chronology of the changes that have occurred to those rows – the whens and the whats. You do this using the SELECT … VERSIONS BETWEEN syntax referencing a start and end timestamp (you can also reference the System Change Number – SCN – if you prefer). Using this syntax you can query a number of pseudocolumns:

VERSIONS_STARTTIME	The timestamp at which the row version was created.
VERSIONS_ENDTIME	The timestamp at which the row version ended.
VERSIONS_OPERATION	This pseudocolumn contains the operation that resulted in the row version. I(nsert), U(pdate) or D(elete).
VERSIONS_XID	The identifier for the transaction that resulted in the row version.

Imagine that those rows from the previous example were not simply deleted, but were updated and changed numerously, and you are interested in finding out the details of each edition that occurred to a particular row. You might write a query such as the following:

SELECT versions_starttime, versions_endtime, versions_operation, versions_xid, ename, sal
FROM  emp BETWEEN TIMESTAMP (systimestamp - interval '6' minute) AND systimestamp;

Edit a row or two in one of your database tables and give the above query a spin. It will immediately strike you how important the flashback version query pseudocolumns could be. Pay attention to versions_xid, we will need it to add a further layer of functionality to our flashback cake.

Oracle Flashback Transaction Query:

The versions_xid value that we got from the previous query is the internal id that Oracle assigns each transaction. It’s a hexadecimal value, seemingly unremarkable in itself; but using it we can rummage around in the FLASHBACK_TRANSACTION_QUERY data dictionary view and obtain further information about our query. Amongst the columns in this view are the following:

XID START_TIMESTAMP COMMIT_TIMESTAMP LOGON_USER OPERATION UNDO_SQL

There are a few other columns in the view; one identifying the table and a few identifying the SCN. Describe the view yourself to get the full picture. However, do pay LOGON_USER and UNDO_SQL especial attention. Going back to our previous example, we can use this view to not only discover who deleted the missing rows, but also to allow Oracle generate for us the code it would take to undo the change.

SELECT logon_user, operation, start_timestamp, undo_sql
FROM flashback_transaction_query
WHERE xid in (
SELECT  versions_xid
FROM  emp BETWEEN TIMESTAMP (systimestamp - interval '6' minute) AND systimestamp);

DBMS_FLASHBACK

You know how, when you go to a really fancy store, they package your shopping up for you in a really nice bag? Well, that’s what Oracle have done with the DBMS_FLASHBACK package. The flashback technology is nothing short of amazing, but with DBMS_FLASHBACK they have wrapped it up and put a bow on it.

Using the tag team of DBMS_FLASHBACK.ENABLE_AT_TIME and DBMS_FLASHBACK.DISABLE, you can dial back your session to a past time and carry out select queries without having to bother with the SELECT…AS OF or SELECT … VERSIONS BETWEEN syntaxes. Using this package, the above example can be rewritten as follows:

EXECUTE DBMS_FLASHBACK.ENABLE_AT_TIME(systimestamp- interval '6' minute);

SELECT * FROM emp;

EXECUTE DBMS_FLASHBACK.DISABLE;

There are a few things you need to bear in mind, however. Only run select statements, no other DML and definitely no DDL statements. Also, you must follow each call to DBMS_FLASHBACK.ENABLE_AT_TIME with a call to DBMS_FLASHBACK.DISABLE before calling DBMS_FLASHBACK.ENABLE_AT_TIME again.

The DBMS_FLASHBACK package can also smooth out the hassle of restoring lost data for you. Using the TRANSACTION_BACKOUT procedure, Oracle will not only rollback any specified transactions, but also any dependent transactions that may have taken place as a result of them.

DBMS_FLASHBACK.BACKOUT_TRANSACTION

The default value of the options parameter is NOCASCADE. If your transaction has dependencies, you will want to use CASCADE instead. Also bear in mind that you will need to commit the changes the procedure has made. However, you will probably want to examine the reports that the procedure has created in USER_FLASHBACK_TXT_STATE and USER_FLASHBACK_TXT_REPORT (or DBA_FLASHBACK_TXN_STATE and DBA_FLASHBACK_TXN_REPORT, if you have the access to them) before saving the changes.

Conclusion:

No man, Oscar Wilde once said, is rich enough to buy back his past. Perhaps so, but with Oracle Flashback Technology we can make the mistakes of the past a little less expensive. Using the functionality I have outlined above, developers can either deal with spilt milk on a transaction by transaction basis as needed, or perhaps build a simple overlying application that can reverse time at the press of a button.

“The past is not dead,” William Faulkner once said. “In fact, it’s not even past.”

Perhaps he was an Oracle developer too.

In the next instalment in this series I will discuss the tools in the Oracle Flashback Technology locker that database administrators will find invaluable.

While everyone is aware of the issues of mixing EHCC compression and OLTP type activities, I had a customer who was interested in finding out what happens upon adding a column to a table that has EHCC compression enabled on it.

As I could not see any definitive statements in the documentation on this particular scenario I ran up some tests to see the behaviour.

First of all they are using partitioning by date range, so we create a partitioned table:

SQL: db01> create table t_part  ( 
username varchar2(30), 
user_id  number, 
created date ) 
partition by range (created) 
( partition p_2009 values less than (to_date('31-DEC-2009', 'dd-MON-YYYY')) tablespace users, 
partition p_2010 values less than (to_date('31-DEC-2010', 'dd-MON-YYYY')) tablespace users, 
partition p_2011 values less than (to_date('31-DEC-2011', 'dd-MON-YYYY')) tablespace users, 
partition p_2012 values less than (to_date('31-DEC-2012', 'dd-MON-YYYY')) tablespace users )

/

Table created

The customer is particularly interested in using partitioning for ILM type scenarios in that they will compress historical partitions but not more up-to-date ones. Lets enable HCC compression on the table and check that it’s on:

SQL: db01> alter table t_part compress for query high 
/

Table altered

SQL: db01> select table_name, partition_name, compression, compress_for 
from all_tab_partitions 
where table_name='T_PART' 
/

TABLE_NAME                     PARTITION_NAME                 COMPRESS COMPRESS_FOR 
------------------------------ ------------------------------ -------- ------------ 
T_PART                         P_2009                         ENABLED  QUERY HIGH 
T_PART                         P_2010                         ENABLED  QUERY HIGH 
T_PART                         P_2011                         ENABLED  QUERY HIGH 
T_PART                         P_2012                         ENABLED  QUERY HIGH

Lets insert some data and check that the actual row is compressed (thanks to Kerry Osborne)

SQL: db01>; insert /*+ APPEND */ into t_part select * from all_users 
/ 
3008 rows created
SQL: db01> commit
/
Commit complete

SQL: db01> select max(rowid) from t_part
/

MAX(ROWID) 
------------------ 
AAAexSAANAAHGoUAAN

SQL: db01> select decode( 
DBMS_COMPRESSION.GET_COMPRESSION_TYPE ( 'SYS', 'T_PART', '&rowid'), 
    1, 'No Compression', 
    2, 'Basic/OLTP Compression', 
    4, 'HCC Query High', 
    8, 'HCC Query Low', 
   16, 'HCC Archive High', 
   32, 'HCC Archive Low', 
   'Unknown Compression Level') compression_type 
from dual;

Enter value for rowid: AAAexSAANAAHGoUAAN 
old   2: DBMS_COMPRESSION.GET_COMPRESSION_TYPE ( 'SYS', 'T_PART', '&rowid'), 
new   2: DBMS_COMPRESSION.GET_COMPRESSION_TYPE ( 'SYS', 'T_PART', 'AAAexSAANAAHGoUAAN'),

COMPRESSION_TYPE 
------------------------- 
HCC Query High

So we are confident we have a row that is compressed. Now we add a new column to the table and give it a default value, we then check again what compression the row has:

SQL: db01> alter table t_part add city varchar2(30) default 'Oxford' 
/

Table altered.

select decode( 
DBMS_COMPRESSION.GET_COMPRESSION_TYPE ( 'SYS', 'T_PART', '&rowid'), 
  2    3  1, 'No Compression', 
  4  2, 'Basic/OLTP Compression', 
  5  4, 'HCC Query High', 
  6  8, 'HCC Query Low', 
  7  16, 'HCC Archive High', 
32, 'HCC Archive Low', 
    'Unknown Compression Level') compression_type 
from dual; 
Enter value for rowid: AAAexSAANAAHGoUAAN 
old   2: DBMS_COMPRESSION.GET_COMPRESSION_TYPE ( 'SYS', 'T_PART', '&rowid'), 
new   2: DBMS_COMPRESSION.GET_COMPRESSION_TYPE ( 'SYS', 'T_PART', 'AAAexSAANAAHGoUAAN'),

COMPRESSION_TYPE 
------------------------- 
Basic/OLTP Compression

Oh Dear! Our compression has changed.

This maybe is not that surprising. But what if you have a requirement to add a column but with no default value, and you just want to update more recent records, can we avoid downgrading all records from HCC compression?

So we are using the same table and data as before. We will focus on two rows, one in the 2011 partition and one in the 2012 partition.

SQL: db01> select max(rowid) from t_part where created  > TO_DATE('31-Dec-2010', 'DD-MM-YYYY') and created < TO_DATE('01-Jan-2012', 'DD-MM-YYYY');

MAX(ROWID) 
------------------ 
AAAezbAAHAAFwIKAE/

SQL: db01> select decode( 
DBMS_COMPRESSION.GET_COMPRESSION_TYPE ( 'SYS', 'T_PART', '&rowid'), 
    1, 'No Compression', 
    2, 'Basic/OLTP Compression', 
    4, 'HCC Query High', 
    8, 'HCC Query Low', 
    16, 'HCC Archive High', 
    32, 'HCC Archive Low', 
    'Unknown Compression Level') compression_type 
from dual;  
Enter value for rowid: AAAezbAAHAAFwIKAE/ 
old   2: DBMS_COMPRESSION.GET_COMPRESSION_TYPE ( 'SYS', 'T_PART', '&rowid'), 
new   2: DBMS_COMPRESSION.GET_COMPRESSION_TYPE ( 'SYS', 'T_PART', 'AAAezbAAHAAFwIKAE/'),

COMPRESSION_TYPE 
------------------------- 
HCC Query High

SQL: db01> select max(rowid) from t_part where created  > TO_DATE('31-Dec-2011', 'DD-MM-YYYY') and created < TO_DATE('31-Dec-2012', 'DD-MM-YYYY');

MAX(ROWID) 
------------------ 
AAAezcAAHAAHdoSADf

SQL:xldnc911001hdor:(SMALLDB1):PRIMARY> select decode( 
    DBMS_COMPRESSION.GET_COMPRESSION_TYPE ( 'SYS', 'T_PART', '&rowid'), 
    1, 'No Compression', 
    2, 'Basic/OLTP Compression', 
    4, 'HCC Query High', 
    8, 'HCC Query Low', 
    16, 'HCC Archive High', 
    32, 'HCC Archive Low', 
    'Unknown Compression Level') compression_type 
from dual; 
Enter value for rowid: AAAezcAAHAAHdoSADf 
old   2: DBMS_COMPRESSION.GET_COMPRESSION_TYPE ( 'SYS', 'T_PART', '&rowid'), 
new   2: DBMS_COMPRESSION.GET_COMPRESSION_TYPE ( 'SYS', 'T_PART', 'AAAezcAAHAAHdoSADf'),

COMPRESSION_TYPE 
------------------------- 
HCC Query High

Now we add a column to the table and update the records in only the 2012 partition:

SQL: db01> alter table t_part add city varchar2(30);

Table altered.

SQL: db01> update t_part set city='Oxford' where created > to_date('31-Dec-2011', 'DD-MM-YYYY');

448 rows updated.

SQL: db01> commit;

Commit complete.

And now we again check the compression status of our two rows:

SQL: db01> select decode( 
DBMS_COMPRESSION.GET_COMPRESSION_TYPE ( 'SYS', 'T_PART', '&rowid'), 
    1, 'No Compression', 
    2, 'Basic/OLTP Compression', 
    4, 'HCC Query High', 
    8, 'HCC Query Low', 
   16, 'HCC Archive High', 
   32, 'HCC Archive Low', 
       'Unknown Compression Level') compression_type 
from dual;  
Enter value for rowid: AAAezbAAHAAFwIKAE/ 
old   2: DBMS_COMPRESSION.GET_COMPRESSION_TYPE ( 'SYS', 'T_PART', '&rowid'), 
new   2: DBMS_COMPRESSION.GET_COMPRESSION_TYPE ( 'SYS', 'T_PART', 'AAAezbAAHAAFwIKAE/'),

COMPRESSION_TYPE 
------------------------- 
HCC Query High

SQL: db01> select decode( 
DBMS_COMPRESSION.GET_COMPRESSION_TYPE ( 'SYS', 'T_PART', '&rowid'), 
    1, 'No Compression', 
    2, 'Basic/OLTP Compression', 
    4, 'HCC Query High', 
    8, 'HCC Query Low', 
    16, 'HCC Archive High', 
    32, 'HCC Archive Low', 
        'Unknown Compression Level') compression_type 
   from dual; 
Enter value for rowid: AAAezcAAHAAHdoSADf 
old   2: DBMS_COMPRESSION.GET_COMPRESSION_TYPE ( 'SYS', 'T_PART', '&rowid'), 
new   2: DBMS_COMPRESSION.GET_COMPRESSION_TYPE ( 'SYS', 'T_PART', 'AAAezcAAHAAHdoSADf'),

COMPRESSION_TYPE 
------------------------- 
Basic/OLTP Compression

So that is great, we have a way of evolving table definitions without having to suffer the whole set of historical data to not be in HCC compression.

While everyone is aware of the issues of mixing EHCC compression and OLTP type activities, I had a customer who was interested in finding out what happens upon adding a column to a table that has EHCC compression enabled on it.

As I could not see any definitive statements in the documentation on this particular scenario I ran up some tests to see the behaviour.

First of all they are using partitioning by date range, so we create a partitioned table:

SQL: db01> create table t_part  ( 
username varchar2(30), 
user_id  number, 
created date ) 
partition by range (created) 
( partition p_2009 values less than (to_date('31-DEC-2009', 'dd-MON-YYYY')) tablespace users, 
partition p_2010 values less than (to_date('31-DEC-2010', 'dd-MON-YYYY')) tablespace users, 
partition p_2011 values less than (to_date('31-DEC-2011', 'dd-MON-YYYY')) tablespace users, 
partition p_2012 values less than (to_date('31-DEC-2012', 'dd-MON-YYYY')) tablespace users )

/

Table created

The customer is particularly interested in using partitioning for ILM type scenarios in that they will compress historical partitions but not more up-to-date ones. Lets enable HCC compression on the table and check that it’s on:

SQL: db01> alter table t_part compress for query high 
/

Table altered

SQL: db01> select table_name, partition_name, compression, compress_for 
from all_tab_partitions 
where table_name='T_PART' 
/

TABLE_NAME                     PARTITION_NAME                 COMPRESS COMPRESS_FOR 
------------------------------ ------------------------------ -------- ------------ 
T_PART                         P_2009                         ENABLED  QUERY HIGH 
T_PART                         P_2010                         ENABLED  QUERY HIGH 
T_PART                         P_2011                         ENABLED  QUERY HIGH 
T_PART                         P_2012                         ENABLED  QUERY HIGH

Lets insert some data and check that the actual row is compressed (thanks to Kerry Osborne)

SQL: db01>; insert /*+ APPEND */ into t_part select * from all_users 
/ 
3008 rows created
SQL: db01> commit
/
Commit complete

SQL: db01> select max(rowid) from t_part
/

MAX(ROWID) 
------------------ 
AAAexSAANAAHGoUAAN

SQL: db01> select decode( 
DBMS_COMPRESSION.GET_COMPRESSION_TYPE ( 'SYS', 'T_PART', '&rowid'), 
    1, 'No Compression', 
    2, 'Basic/OLTP Compression', 
    4, 'HCC Query High', 
    8, 'HCC Query Low', 
   16, 'HCC Archive High', 
   32, 'HCC Archive Low', 
   'Unknown Compression Level') compression_type 
from dual;

Enter value for rowid: AAAexSAANAAHGoUAAN 
old   2: DBMS_COMPRESSION.GET_COMPRESSION_TYPE ( 'SYS', 'T_PART', '&rowid'), 
new   2: DBMS_COMPRESSION.GET_COMPRESSION_TYPE ( 'SYS', 'T_PART', 'AAAexSAANAAHGoUAAN'),

COMPRESSION_TYPE 
------------------------- 
HCC Query High

So we are confident we have a row that is compressed. Now we add a new column to the table and give it a default value, we then check again what compression the row has:

SQL: db01> alter table t_part add city varchar2(30) default 'Oxford' 
/

Table altered.

select decode( 
DBMS_COMPRESSION.GET_COMPRESSION_TYPE ( 'SYS', 'T_PART', '&rowid'), 
  2    3  1, 'No Compression', 
  4  2, 'Basic/OLTP Compression', 
  5  4, 'HCC Query High', 
  6  8, 'HCC Query Low', 
  7  16, 'HCC Archive High', 
32, 'HCC Archive Low', 
    'Unknown Compression Level') compression_type 
from dual; 
Enter value for rowid: AAAexSAANAAHGoUAAN 
old   2: DBMS_COMPRESSION.GET_COMPRESSION_TYPE ( 'SYS', 'T_PART', '&rowid'), 
new   2: DBMS_COMPRESSION.GET_COMPRESSION_TYPE ( 'SYS', 'T_PART', 'AAAexSAANAAHGoUAAN'),

COMPRESSION_TYPE 
------------------------- 
Basic/OLTP Compression

Oh Dear! Our compression has changed.

This maybe is not that surprising. But what if you have a requirement to add a column but with no default value, and you just want to update more recent records, can we avoid downgrading all records from HCC compression?

So we are using the same table and data as before. We will focus on two rows, one in the 2011 partition and one in the 2012 partition.

SQL: db01> select max(rowid) from t_part where created  > TO_DATE('31-Dec-2010', 'DD-MM-YYYY') and created < TO_DATE('01-Jan-2012', 'DD-MM-YYYY');

MAX(ROWID) 
------------------ 
AAAezbAAHAAFwIKAE/

SQL: db01> select decode( 
DBMS_COMPRESSION.GET_COMPRESSION_TYPE ( 'SYS', 'T_PART', '&rowid'), 
    1, 'No Compression', 
    2, 'Basic/OLTP Compression', 
    4, 'HCC Query High', 
    8, 'HCC Query Low', 
    16, 'HCC Archive High', 
    32, 'HCC Archive Low', 
    'Unknown Compression Level') compression_type 
from dual;  
Enter value for rowid: AAAezbAAHAAFwIKAE/ 
old   2: DBMS_COMPRESSION.GET_COMPRESSION_TYPE ( 'SYS', 'T_PART', '&rowid'), 
new   2: DBMS_COMPRESSION.GET_COMPRESSION_TYPE ( 'SYS', 'T_PART', 'AAAezbAAHAAFwIKAE/'),

COMPRESSION_TYPE 
------------------------- 
HCC Query High

SQL: db01> select max(rowid) from t_part where created  > TO_DATE('31-Dec-2011', 'DD-MM-YYYY') and created < TO_DATE('31-Dec-2012', 'DD-MM-YYYY');

MAX(ROWID) 
------------------ 
AAAezcAAHAAHdoSADf

SQL:xldnc911001hdor:(SMALLDB1):PRIMARY> select decode( 
    DBMS_COMPRESSION.GET_COMPRESSION_TYPE ( 'SYS', 'T_PART', '&rowid'), 
    1, 'No Compression', 
    2, 'Basic/OLTP Compression', 
    4, 'HCC Query High', 
    8, 'HCC Query Low', 
    16, 'HCC Archive High', 
    32, 'HCC Archive Low', 
    'Unknown Compression Level') compression_type 
from dual; 
Enter value for rowid: AAAezcAAHAAHdoSADf 
old   2: DBMS_COMPRESSION.GET_COMPRESSION_TYPE ( 'SYS', 'T_PART', '&rowid'), 
new   2: DBMS_COMPRESSION.GET_COMPRESSION_TYPE ( 'SYS', 'T_PART', 'AAAezcAAHAAHdoSADf'),

COMPRESSION_TYPE 
------------------------- 
HCC Query High

Now we add a column to the table and update the records in only the 2012 partition:

SQL: db01> alter table t_part add city varchar2(30);

Table altered.

SQL: db01> update t_part set city='Oxford' where created > to_date('31-Dec-2011', 'DD-MM-YYYY');

448 rows updated.

SQL: db01> commit;

Commit complete.

And now we again check the compression status of our two rows:

SQL: db01> select decode( 
DBMS_COMPRESSION.GET_COMPRESSION_TYPE ( 'SYS', 'T_PART', '&rowid'), 
    1, 'No Compression', 
    2, 'Basic/OLTP Compression', 
    4, 'HCC Query High', 
    8, 'HCC Query Low', 
   16, 'HCC Archive High', 
   32, 'HCC Archive Low', 
       'Unknown Compression Level') compression_type 
from dual;  
Enter value for rowid: AAAezbAAHAAFwIKAE/ 
old   2: DBMS_COMPRESSION.GET_COMPRESSION_TYPE ( 'SYS', 'T_PART', '&rowid'), 
new   2: DBMS_COMPRESSION.GET_COMPRESSION_TYPE ( 'SYS', 'T_PART', 'AAAezbAAHAAFwIKAE/'),

COMPRESSION_TYPE 
------------------------- 
HCC Query High

SQL: db01> select decode( 
DBMS_COMPRESSION.GET_COMPRESSION_TYPE ( 'SYS', 'T_PART', '&rowid'), 
    1, 'No Compression', 
    2, 'Basic/OLTP Compression', 
    4, 'HCC Query High', 
    8, 'HCC Query Low', 
    16, 'HCC Archive High', 
    32, 'HCC Archive Low', 
        'Unknown Compression Level') compression_type 
   from dual; 
Enter value for rowid: AAAezcAAHAAHdoSADf 
old   2: DBMS_COMPRESSION.GET_COMPRESSION_TYPE ( 'SYS', 'T_PART', '&rowid'), 
new   2: DBMS_COMPRESSION.GET_COMPRESSION_TYPE ( 'SYS', 'T_PART', 'AAAezcAAHAAHdoSADf'),

COMPRESSION_TYPE 
------------------------- 
Basic/OLTP Compression

So that is great, we have a way of evolving table definitions without having to suffer the whole set of historical data to not be in HCC compression.

Infernal is the ninth book in the Repairman Jack series by F. Paul Wilson.

Another family tragedy has the effect of reuniting Jack with his brother Tom, the judge. It turns out Tom is not as squeaky clean has he appears and needs Jack’s help for something less than legal. As you can probably guess by now, it all turns sinister and mystical…

I’ve definitely become desensitized to the darkness now. Every time a new character is introduced, pretty much my first thought is, “I wonder how they will die?” It’s a bit like watching Star Trek and knowing the security officer (in the red top) you’ve never seen before is the one that’s going to eat lead/laser…

Cheers

Tim…

---

Repairman Jack : Infernal… was first posted on May 16, 2012 at 9:02 am. ©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

UI Enhancements:

The new User Interface for HFM is definitely a notable update when compared to the earlier version.  It is easier to navigate and includes some special features as well. This is an outcome of migrating EPM components like HFM and Planning to Oracle’s ADF (Application Development Framework).

Let’s take a look at all these UI enhancements –

Multiple applications in Workspace:

Different HFM applications can be opened at the same time.

Multiple modules in an application:

Different HFM application modules in each application can be opened simultaneously.

POV enhancements:

There are significant changes to the way we select dimensions to Rows and Columns. We can relate this approach to Hyperion Financial Reporting wherein we ‘drag and drop’ dimensions to Rows and Columns in dimension layout. This approach is simpler and made common to both data grids and data forms. We can also add dimensions manually in data forms.

Data grid enhancements:

Again, we can relate the creation of data grids to how we create the reports in Financial Reporting. In HFR, we create/design the report and run the report to view it. Similarly, we have Grid designer and Grid Viewer in data grids.

Display options were docked to the right hand side thus decreasing the time wasted on navigation.

Another new feature is the indication of cell colors at the bottom of the Grid Viewer. This will be very helpful to the business users and they don’t need to reach support or documentation to understand the Cell Colors.

Data form enhancements:

Member selection process is pretty much similar to what we do in data grids. And there exists designer and viewer in data forms too. Export to/Import from Excel options were disabled.

Favorite members in Member selection:

Frequently used members can be selected and saved as favorites. These are available across other modules of the application as well.

Loads and Extracts page:

Load and Extract tasks like Security, Metadata, Member Lists and Rules are consolidated in one single page.

Journals Module Enhancements:

Journal tasks functionality is pretty much same but with major changes in UI again. Journal reports module exists both as a separate task and as a part of Manage Journals (opens in a new window when clicked).

Intercompany transactions module is not available and will be included in the upcoming patch – 11.1.2.2.101. It might take some time for the old version users to get used to navigating the new interface. Hope we can leverage from these ADF features. There are other enhancements to the HFM Win 32 client and Custom dimension configuration, which will be covered in a different post.