One of the few advantages of such long flights as UK to New Zealand is you get plenty of time to read.

I’ve mentioned several times that I’ve struggled to find something to follow the Discworld series. Several people recommended The Dresden Files series so I went out and bought the first few books. I tried a couple of times to read the first book (Storm Front) and really couldn’t get into it. I brought the first 3 books with me, just in case I got bored, and fortunately for me it was a case of third time lucky.

I really enjoyed Storm Front and can’t believe it was the same book I gave up on two times before. Just goes to show how mood and circumstance has a big impact on your taste. I’m on to the second book (Fool Moon) now and so far so good.

Cheers

Tim…

The day started in the normal way. First, an introduction by Francisco, the new president of NZOUG, then some keynotes, then on to the presentations…

One of the difficult things about being a speaker at so many conferences is you often struggle to see something on the agenda you have not already seen several times around the world (nice problem to have). As a result I side-stepped Dan Morgan’s first presentation because I had already seen it (or most of it) at OpenWorld. I’m sure it went down well because Dan is a natural speaker.

My first session of the day was Robert Freeman’s. A quick romp through the Oracle features that have been around for ages, but you probably don’t know exist. You will struggle to find a nicer guy than Robert, so it’s always good to see him present. I kinda like this sort of presentation because because Oracle has such a lot of functionality, it’s easy to miss things. Robert has turned “to the dark side” and is now an Oracle employee. Come on Oracle. Stop poaching our ACEs.

I spent a lot of time during the day chatting to people (there’s a surprise). Plenty of familiar faces along with some new ones.

My session was the last time slot for the day. I’m doing a two-part session on PL/SQL presentation layers. I had a good turnout. It felt like it went well. I guess the real test will be how many people turn up for the second part, although I’ve just noticed I clash with Robert Freeman on my second slot, so when nobody turns up I can massage my ego by telling myself I was amazing, but Robert is famous.

In the evening we went over for the social event at the Skyline Skyrides. It was great fun going on the luge again. I learnt two lessons last year.

1. You have to brake on the corners, or you die.
2. You don’t enter the “carry a cup of water to the bottom” game unless you want to be ridiculed the whole evening for having a wet ass.

I remained alive and dry, so all was good.

After the luge rides came the dinner. Lots of food, lots of drinking (for those that do), comedian, party games and very little in the way of speeches. I think the NZ sponsors have got it right. Essentially it was, “Thanks for coming. Have a good night. Cheers”. Perfect.

This morning I’ve got a “Learn about the Oracle ACE Programme” session, followed by the second part of my PL/SQL presentation layers session. Then later in the afternoon I’ve got a “Meet the Experts” session. I’m interested to see what experts they’ve got for me to meet.

Cheers

Tim…

In her 1995 national best-selling book, Jesus CEO, Laurie Beth Jones uses the story of Jesus Christ as a metaphor for visionary leadership.  Her book is divided into three sections:

• Strength of Self-Mastery
• Strength of Action
• Strength of Relationships

I read this book a long time ago.  Today, I got an email from one of my managers pointing out a decision I made should have been made differently.  Reading what he said made me feel like “kicking the donkey” and that reminded me of a particular chapter in her book based on one of the stories she used as a leadership metaphor in the “Self-Mastery” section.  It goes something like this:

A man was heading up a road with his donkey.  All of the sudden the donkey stops moving and refuses to move.  The man kicks the donkey to try and get it to move (donkeys are known to be very stubborn). The donkey moves a little more and stops again refusing to move. The man kicks the donkey again…  

To make this short and simpler, it turns out that in the end the donkey was actually trying to let the man know there was possible trouble ahead. It was, after all, looking after his best interest. 

How often do we kick “donkeys” in life?  How often do we refuse to listen, pay attention, and get mad at people who have good intentions and whom may be looking after us, our teams, our companies, etc.?

Any time you hear or read something that makes you “mad”, stop and think before you kick the donkey.  I almost did today and God knows how often I have not realized I had.  We need to learn to explore those things that trigger strong emotional responses in ourselves and others.  If someone else is kicking a donkey, you or someone else, use this metaphor to trigger meaning exploration.

• What did that mean to me or the other person?
• Why am I or why are they reacting this way?
• What is their intention or purpose?
• Do they have my/our best interests at hand?

It takes a lot of presence of mind to be aware that you are about to kick a donkey and to be able to withhold the “kick” from starting or hitting.  It takes practice.  Start by keeping a daily reminder of recalling any possible donkey kicking moments of the day.  Tell the story to your team and ask them to use the term to remind you if you are doing so (kicking a donkey).

Finally, here is some advice for the “donkeys” — Make sure you have a solid rationale and as much factual basis before you refuse to keep moving or pointing potential issues.  Sometimes the kicks we get as donkeys result from our approach rather than the content of our messages.  When you do so, take time to think how to best present the case for caution.

And by the way, independent of personal beliefs, this little book offers a wealth of leadership ideas well worth keeping in mind!  I highly recommend you explore it with an open mind. It is an easy read!  I am amazed at how she was able to extract so much from the story!  AMAZING!!!

The BEST is Yet to Come!

Epi Torres, CEO

Have You Kicked Any “Donkeys” Lately? is a post from: Remote DBA Experts

Computacenter (http://www.computacenter.com/), Europe's leading independent provider of IT infrastructure services, recently announced that it is the first partner to offer an Oracle Exadata 'proof-of concept' environment for real-world testing. This new center, combined with Computacenter's extensive database storage skills, will enable organisations to accurately test Oracle Exadata with their own workloads, clearly demonstrating the case for migration.

For more information, read the press release.

Are you planning to migrate to Oracle Exadata? Tell us about it!

var gaJsHost = (("https:" == document.location.protocol) ? "https://ssl." : "http://www.");
document.write(unescape("%3Cscript src='" + gaJsHost + "google-analytics.com/ga.js' type='text/javascript'%3E%3C/script%3E"));


try {
var pageTracker = _gat._getTracker("UA-13185312-1");
pageTracker._trackPageview();
} catch(err) {}

In short: Not useful.

More elaborated: Messy, waste of memory-space and admin-effort.


Let me explain:

I've come across this discussion again and I considered myself lucky that Charles Hooper has done all the research already.

I'm summarizing his findings here:

Multiple Blocksizes do NOT offer Any Proven Advantage.

The theory about more efficient indexes and better managed cache is good, and I dont deny there is good reasoning behind it. But in practice, having multiple blocksizes and multiple db-nK-caches doesnt make a difference.

It is a waste of the extra (little bit of) work.

And most likely, you end up wasteing cache-space because you hard-divide the cache space into chunks and you prevent the Oracle LRU-mechanism from utilizing all available cache as it sees fit.

Of course, you may be the exception that proves the rule, and I would like to hear from you in that case, but until further notice, I'll stick with my motto:

Simple = Better.

In the following months we'll run again Database Upgrade Workshops in several countries in EMEA - would be great to meet YOU and YOUR COLLEAGUES in one of the locations :-)

Please find the registration links here:

07. April 2010 - Zurich (Baden-Daettwil) / Switzerland
08. April 2010 - De Meern / Netherlands
15. April 2010 - Dublin / Ireland (reg link will follow soon)
16. April 2010 - Dublin / Ireland (hands-on) (reg link will follow soon)
27. April 2010 - London / UK
04. May 2010 - Copenhagen (Ballerup) / Denmark
05. May 2010 - Oslo / Norway
06. May 2010 - Helsinki / Finland
07. May 2010 - Stockholm / Sweden

Further workshops will be happen in:

18. May 2010 in Beograd/Serbia
01. June 2010 in Brussels/Belgium
07. June 2010 in Warszaw/Poland
08. June 2010 in Budapest/Hungary
10. June 2010 in Prague/Czech Republic
15. June 2010 in Athens/Greece
16. June 2010 in Istanbul/Turkey

CU there :-)

Herod T has returned to Oracle Blogging. However, I came across this post on an ORA-07445 error on his blog when I re-visited it today.

.
.
.

March 15, 2010

How would you troubleshoot the following situation – what do you know, or believe to be true with at least a 95% confidence about this situation?  You encounter a SQL statement that is taking much longer to execute than is expected.  You enable an extended SQL trace at level 8 and pass the resulting trace file though TKPROF.  In the TKPROF output you find the SQL statement that is of interest:

SELECT                 COUNT ( * )   FROM            I_JOURNAL m                INNER JOIN                   LIU_TYPES lt                ON (m.LIU_TYPE = lt.LIU_TYPE)             INNER JOIN                LAWFUL_I li             ON (m.LIID = li.LIID)          LEFT OUTER JOIN             PHONE_BOOK pb          ON (    m.NORM_CIN = pb.NORM_CIN              AND pb.DELETION_DATE IS NULL              AND pb.OPERATOR_ID = :"SYS_B_00")  WHERE   LIU_PRIORITY >= :"SYS_B_01"          AND (li.ID IN                     (:"SYS_B_02",                      :"SYS_B_03",                      :"SYS_B_04",                      :"SYS_B_05",                      :"SYS_B_06",                      :"SYS_B_07",                      :"SYS_B_08",                      :"SYS_B_09", [...]                      :"SYS_B_59",                      :"SYS_B_60",                      :"SYS_B_61",                      :"SYS_B_62"))          AND (li.END_VALID_DATE IS NULL               OR m.DISPLAY_DATE <= li.END_VALID_DATE)          AND li.OPERATOR_ID = :"SYS_B_63"

In the above, roughly 50 lines of the SQL statement containing bind variables for the IN list were removed to save space.  The summary information for the SQL statement’s execution follows:

call     count       cpu    elapsed       disk      query    current        rows ------- ------  -------- ---------- ---------- ---------- ----------  ---------- Parse        1      0.00       0.00          0          0          0           0 Execute      1      0.05       0.04          0          0          0           0 Fetch        2    105.00     102.69     283093     652774          0           1 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total        4    105.05     102.73     283093     652774          0           1 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 50  Rows     Row Source Operation -------  ---------------------------------------------------       1  SORT AGGREGATE (cr=652774 pr=283093 pw=0 time=102690302 us)  888030   HASH JOIN RIGHT OUTER (cr=652774 pr=283093 pw=0 time=99929786 us)   28488    INDEX RANGE SCAN OBJ#(45035) (cr=130 pr=0 pw=0 time=142564 us)(object id 45035)         PBK_GET_NORM_CIN_UK  888030    HASH JOIN  (cr=652644 pr=283093 pw=0 time=85254971 us)      15     TABLE ACCESS FULL OBJ#(44893) (cr=7 pr=0 pw=0 time=320 us)                             LIU_TYPES  888117     TABLE ACCESS BY INDEX ROWID OBJ#(47625) (cr=652637 pr=283093 pw=0 time=63945559 us)    I_JOURNAL  888179      NESTED LOOPS  (cr=5389 pr=4986 pw=0 time=23801052 us)      61       INLIST ITERATOR  (cr=213 pr=1 pw=0 time=8299 us)      61        TABLE ACCESS BY INDEX ROWID OBJ#(44860) (cr=213 pr=1 pw=0 time=7235 us)             LAWFUL_I      61         INDEX RANGE SCAN OBJ#(45023) (cr=122 pr=0 pw=0 time=2454 us)(object id 45023)      LIN_ID_UK  888117       INDEX RANGE SCAN OBJ#(52001) (cr=5176 pr=4985 pw=0 time=9904545 us)(object id 52001) IJL_LIN_FK_IX Elapsed times include waiting on following events:   Event waited on                             Times   Max. Wait  Total Waited   ----------------------------------------   Waited  ----------  ------------   SQL*Net message to client                       2        0.00          0.00   db file sequential read                    283093        0.02          8.51   SQL*Net message from client                     2        0.00          0.00

While reviewing the above you notice that there were 652,774 consistent gets and 283,093 physical block reads.  The wait events associated with the SQL statement indicate that there were 283,093 waits on the db file sequential read wait event, which indicates that all of the physical block reads were performed one block at a time (the Times Waited statistic for this event matches the disk statistic in the Fetch summary line).  If a total of 8.51 seconds (from the Total Waited statistic for the db file sequential read wait event) were needed to complete those 283,093 single block reads, then on average each single block read required approximately 0.000030 seconds – roughly 133 times faster than one revolution of a fast 15,000 RPM hard drive.

If we experiment with the average single block read time, there were 4,985 single block reads of object ID 52001 (the IJL_LIN_FK_IX index), which suggests that the total time for those physical reads to complete was 4,985 * 0.000030 seconds = 0.15 seconds.  There were 5,176 logical reads for that operation, which is just slightly more than the number of physical blocks that were read from disk for that operation.  The time= statistic for that line in the plan indicates that the operation required 9.90 seconds.  Keeping in mind the other wait events, what happened during the 9.75 seconds of that operation that did not involve the physical block reads?  Notice that the TKPROF output shows that the query statistics summary indicated that the query consumed 105.05 seconds of CPU time with an elapsed query time of 102.73 seconds.

The DBMS_XPLAN output for the SQL statement looks like this:

----------------------------------------------------------------------------------------------------------- | Id  | Operation                         | Name                  | Rows  | Bytes | Cost (%CPU)| Time     | ----------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT                  |                       |       |       | 28440 (100)|          | |   1 |  SORT AGGREGATE                   |                       |     1 |   130 |            |          | |*  2 |   HASH JOIN RIGHT OUTER           |                       | 29838 |  3788K| 28440   (1)| 00:05:42 | |*  3 |    INDEX RANGE SCAN               | PBK_GET_NORM_CIN_UK   | 25437 |   571K|   130   (0)| 00:00:02 | |*  4 |    HASH JOIN                      |                       | 29838 |  3117K| 28399   (1)| 00:05:41 | |*  5 |     TABLE ACCESS FULL             | LIU_TYPES             |    16 |   160 |     3   (0)| 00:00:01 | |*  6 |     TABLE ACCESS BY INDEX ROWID   | I_JOURNAL             |  1172 | 58600 |  1091   (0)| 00:00:14 | |   7 |      NESTED LOOPS                 |                       | 30659 |  2904K| 28396   (1)| 00:05:41 | |   8 |       INLIST ITERATOR             |                       |       |       |            |          | |*  9 |        TABLE ACCESS BY INDEX ROWID| LAWFUL_I              |    26 |  1222 |    10   (0)| 00:00:01 | |* 10 |         INDEX RANGE SCAN          | LIN_ID_UK             |    61 |       |     2   (0)| 00:00:01 | |* 11 |       INDEX RANGE SCAN            | IJL_LIN_FK_IX         |  5318 |       |    30   (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("M"."NORM_CIN"="PB"."NORM_CIN")    3 - access("PB"."OPERATOR_ID"=:SYS_B_00 AND "PB"."DELETION_DATE" IS NULL)        filter("PB"."DELETION_DATE" IS NULL)    4 - access("M"."LIU_TYPE"="LT"."LIU_TYPE")    5 - filter("LT"."LIU_PRIORITY">=:SYS_B_01)    6 - filter(("LI"."END_VALID_DATE" IS NULL OR "M"."DISPLAY_DATE"<="LI"."END_VALID_DATE"))    9 - filter("LI"."OPERATOR_ID"=:SYS_B_63)   10 - access(("LI"."ID"=:SYS_B_02 OR "LI"."ID"=:SYS_B_03 OR "LI"."ID"=:SYS_B_04 OR               "LI"."ID"=:SYS_B_05 OR "LI"."ID"=:SYS_B_06 OR "LI"."ID"=:SYS_B_07 OR "LI"."ID"=:SYS_B_08 OR               "LI"."ID"=:SYS_B_09 OR "LI"."ID"=:SYS_B_10 OR "LI"."ID"=:SYS_B_11 OR "LI"."ID"=:SYS_B_12 OR               "LI"."ID"=:SYS_B_13 OR "LI"."ID"=:SYS_B_14 OR "LI"."ID"=:SYS_B_15 OR "LI"."ID"=:SYS_B_16 OR               "LI"."ID"=:SYS_B_17 OR "LI"."ID"=:SYS_B_18 OR "LI"."ID"=:SYS_B_19 OR "LI"."ID"=:SYS_B_20 OR               "LI"."ID"=:SYS_B_21 OR "LI"."ID"=:SYS_B_22 OR "LI"."ID"=:SYS_B_23 OR "LI"."ID"=:SYS_B_24 OR               "LI"."ID"=:SYS_B_25 OR "LI"."ID"=:SYS_B_26 OR "LI"."ID"=:SYS_B_27 OR "LI"."ID"=:SYS_B_28 OR               "LI"."ID"=:SYS_B_29 OR "LI"."ID"=:SYS_B_30 OR "LI"."ID"=:SYS_B_31 OR "LI"."ID"=:SYS_B_32 OR               "LI"."ID"=:SYS_B_33 OR "LI"."ID"=:SYS_B_34 OR "LI"."ID"=:SYS_B_35 OR "LI"."ID"=:SYS_B_36 OR               "LI"."ID"=:SYS_B_37 OR "LI"."ID"=:SYS_B_38 OR "LI"."ID"=:SYS_B_39 OR "LI"."ID"=:SYS_B_40 OR               "LI"."ID"=:SYS_B_41 OR "LI"."ID"=:SYS_B_42 OR "LI"."ID"=:SYS_B_43 OR "LI"."ID"=:SYS_B_44 OR               "LI"."ID"=:SYS_B_45 OR "LI"."ID"=:SYS_B_46 OR "LI"."ID"=:SYS_B_47 OR "LI"."ID"=:SYS_B_48 OR               "LI"."ID"=:SYS_B_49 OR "LI"."ID"=:SYS_B_50 OR "LI"."ID"=:SYS_B_51 OR "LI"."ID"=:SYS_B_52 OR               "LI"."ID"=:SYS_B_53 OR "LI"."ID"=:SYS_B_54 OR "LI"."ID"=:SYS_B_55 OR "LI"."ID"=:SYS_B_56 OR               "LI"."ID"=:SYS_B_57 OR "LI"."ID"=:SYS_B_58 OR "LI"."ID"=:SYS_B_59 OR "LI"."ID"=:SYS_B_60 OR               "LI"."ID"=:SYS_B_61 OR "LI"."ID"=:SYS_B_62))   11 - access("M"."LIID"="LI"."LIID")

Setting the STATISTICS_LEVEL parameter at the session level to ALL, followed by executing the SQL statement permitted the following execution plan to be retrieved using DBMS_XPLAN:

----------------------------------------------------------------------------------------------------------------------------- | Id  | Operation                        | Name                  | E-Rows | A-Rows | Buffers | Reads  | Writes | A-Time     | ----------------------------------------------------------------------------------------------------------------------------- |   1 | SORT AGGREGATE                   |                       |      1 |      1 |     657K|    245K|      0 |00:01:40.79 | |*  2 |  HASH JOIN RIGHT OUTER           |                       |  30607 |    894K|     657K|    245K|      0 |00:01:37.94 | |*  3 |   INDEX RANGE SCAN               | PBK_GET_NORM_CIN_UK   |  25437 |  28490 |     130 |     19 |      0 |00:00:00.14 | |*  4 |   HASH JOIN                      |                       |  30607 |    894K|     657K|    245K|      0 |00:01:23.16 | |*  5 |    TABLE ACCESS FULL             | LIU_TYPES             |     16 |     15 |       7 |      0 |      0 |00:00:00.01 | |*  6 |    TABLE ACCESS BY INDEX ROWID   | I_JOURNAL             |   1221 |    894K|     657K|    245K|      0 |00:01:01.70 | |   7 |     NESTED LOOPS                 |                       |  31449 |    894K|    5428 |   5099 |      0 |00:00:24.02 | |   8 |      INLIST ITERATOR             |                       |        |     61 |     206 |      1 |      0 |00:00:00.01 | |*  9 |       TABLE ACCESS BY INDEX ROWID| LAWFUL_I              |     26 |     61 |     206 |      1 |      0 |00:00:00.01 | |* 10 |        INDEX RANGE SCAN          | LIN_ID_UK             |     61 |     61 |     122 |      0 |      0 |00:00:00.01 | |* 11 |      INDEX RANGE SCAN            | IJL_LIN_FK_IX         |   5318 |    894K|    5222 |   5098 |      0 |00:00:09.99 | -----------------------------------------------------------------------------------------------------------------------------

Assume that the database version is 10.1.0.x, and that the tables contain the following number of rows:
I_JOURNAL: 5,000,000 rows
LAWFUL_I: 1,000 rows
PHONE_BOOK: 78,000 rows

Here are some of the questions that you might try answering when indicating what you know about the above performance problem:

• What was the server doing in the 9.75 seconds of the INDEX RANGE SCAN operation on the last line of the execution plan that probably could not be attributed to physical I/O?
• How is it possible that 105.05 seconds of CPU time were required for a SQL statement that had a total execution time of 102.69 seconds?  What was the CPU doing for 105.05 seconds?
• How can you explain the 0.000030 second single block read time?
• Why are all of the bind variables named similar to SYS_B_nn?
• Are the number of elements in the IN list contributing to the performance problem?
• Are there any hints that you might try to force a different execution plan, and if so, is there a reason to change the execution plan?
• Why was there a miss in the library cache during both the parse and execute calls?
• What caused the incorrect cardinality estimates, and how would you correct the cardinality estimates?
• Does the ANSI style join syntax have an impact on the execution time for the query?
• Would you recommend any changes to initialization parameters, and why?
• What needs to be done to allow the query to complete in 5 seconds?
• How would the server’s behavior change if significant competition for the server’s RAM caused excessive use of the operating system’s page file?
• Are the server’s CPUs a constraining resource for this SQL statement – if so, why, if not, why not?

This SQL statement and TKPROF output where part of an interesting question in the OTN forums – I attempted to share my thoughts about this SQL statement in that forum thread.

How embedded software engineers really speak:

—-

“That bug is going to need a fuck-ton of testing once we fix its ass.”

“What’s the difference between a fuck-ton and last week’s estimate of a metric buttload?”

“Well, one is metric, the other is english units.  Slightly more, I guess.”

“And you also said it might be a fucking shit-ton?”

“A shit-ton is ten-to-the-fucktieth power fuck-tons. Where were you during orientation?”

“Which would make a fucking shit-ton … ?”

“So Goddamn fucking much work that we’ll never fix that stupid thing because we’re always in ship-mode and too chickenshit to risk screwing the pooch and fucking our customers.  Also, we’re out of ROM space.”

“Thank you for clearing that up. I’ve updated the bug.”

“Yeah, that’ll look great in the deposition transcript. Juries love this shit. Pass the bowl of pills, would you? I have to go write some safety-critical code on that automotive contract.”

LongBridge "FormsImageBean" Java Bean NEW Version 2.1

LongBridge FormsImageBean
Image Capture, Storage, Retrieval
Manipulation and Management

New, Improved & Productive FormsImageBean (Version 2.1) for your Real-World Document Management needs !!!

Version 2.1 features include:

[...]

As always Hotsos started off with a nice keynote, this time done by Tom Kyte. Tom Kyte was introduced by Hotsos president Gary Goodman after the HOTSOS 2010 opening. Tom’s keynote theme was “Should we be less smart some times”. Tom told about own experiences, that he in the past gave sometimes too fast an answer. It is very important to think about an answer before giving it… Why? Well some things applied in the past or for a specific version, and now they don’t anymore… this can be a problem, a real issue. Always make sure you talk about the same definitions, and agree on them. Make sure talking about the same version and of course about similar circumstances. When you start giving answers in general be sure to work with facts and not some assumptions which might be wrong. So you should always think about the information, about the circumstances and the assumptions you do, it means “Continuous Thinking”.

After the keynote Tom Kyte had a talk on “All about Metadata”. Tom Kyte told about how wrong metadata in the database is influencing the performance. So actual explaining the different reasons the performance can be wrong only due to wrong metadata or the lack of it in the database. It is important to feed the database with data you know of the data, because you know your database the best. That can mean adding dimensions, adding materialized views, etc. His most important points were, “Datatypes Count”, “Proper Lengths matter”, “Constraints are important” and “Metadata Matters”, so ensure they are right and there, and the optimizer can do its job the best way possible.

Tom Kyte’s talk was followed up by a talk from Richard Foote. His talk subject was “Oracle Indexing Myths”. When he started his presentation he seemed to be a big fan from “David Bowie” and “Radio Head”, from who he used a lot of times, names to refer to database objects, like table “BOWIE”. Richard told about a lot of different index myths and some of them where quite interesting. The myths he talked about were:
•    Deleted Index Space Is Deadwood
•    Indexes Always Prevent Sort Operations
•    CBO won’t consider an index when retrieving > x% of rows
•    Put Most Discriminating Column 1st In Concatenated Index
•    Small tables Don’t Benefit From Being Indexed
•    Bitmap Indexes Help With Low Cardinality Conditions
•    Bitmap Indexes Inefficient With High Cardinality Columns

Just to give a small impression I will give a few of the answers. In general we can say that deleted index space will be reused, so the idea of ending up with a lot of deadwood in our indexes is really a myth. For indexes and sorts it is always important to realize that “nulls” in columns are not put in the index and so having the index is not always a guarantee that the sorting will actually happen. For indexes it is important to know that the clustering factor is highly involved in choosing an index for an execution plan. Also the amount of rows which will be expected to be retrieved from the index in compare with the table are from influence. So will the index actually been used or not?

The myth of putting the most discriminating column first in a concatenated index, looks very challenging, but in fact it is really a myth. I was even a bit surprised to find this out, so why was my second thought. From base the most discriminating column should be first because of the to be accessed leaf blocks in the index are less, but in fact this doesn’t matter. So if another column is first in the index it will not influence the amount of the to be accessed leaf blocks. Richard showed with some small test cases that it really doesn’t matter, index performance stayed the same. Ok, this said what will happen if we place the most discriminating column at the first, it will actually give problems, like the “Index skip scan”, it will work only efficient as a little distinctive column is first in the index, instead of the other way around. Another index option like index compression will only work as the little distinctive column is first. This has to do with the way an index compression works.

This was just a few examples of the myth coverage Richard did. I think it is very good before starting designing index to check out the work Richard did in this area.

After having had lunch together and making new contacts the presentation room was split in half for two tracks of presentations. I had the choose to go to Alex Gorbachev with “Battle Against any Guess” or Dan Norris with “Consolidation Strategies for Oracle Database Machine”. I choose for Dan and got a good impression on how the Exadata was operating within the database machine. Also things like Oracle I/O Resource Manager and Oracle Database Resource Manager were discussed. After the presentation from Dan I had a small discussion with Christian Antognini, regarding the way a “smart scan” was operating. In my opinion the database is sending a “Bloom Filter” signature to the disk controllers were with the “Bloom Filter” a lot of the not interesting data is pre-filtered before sending it to the database instance. The sent data to the database instance will not be a normal database block format, so it will pass the database buffer cache. The passing of the database buffer cache was also mentioned by Dan in his presentation.

My next presentation I attended was done by Doug Burns with “How I Learned to Love Pictures- Oracle 10g/11g Performance Analysis Using OEM”. Doug tried to do a lot of demos, but due Murphy’s Law he had a lot of issues giving them. To do the load testings on his laptop system he used “Swingbench” as the load-tool. The demo showed how you can use in a very clear way Oracle Enterprise Manager (Grid). Doug showed that the first “Performace Home” screen was based on measured data and the “Top Activity” screen was based on sampled data (ASH). Although a lot of the demos were able to be shown Doug really tried hard to give the message, and in my opinion he did.

Next stop was Kevin Closson with “Ten Years After Y2K, and We Still ‘Party Like It’s 1999’”. Kevin showed a lot of development over the past ten years. He explained about how the techniques over the years have changed and how bottlenecks changed for cpu, memory and I/O. He talked about experiences with Oracle Database File System (DBFS) and how he actually had installed on this file system a complete database including Oracle Home. So he built a database in a database, from a geek point of view like me that is pretty cool. While presenting he had a little buddy with him, the pics will show you how…

The final presentation of the day was for me Kerry Osborne with “Controlling Execution Plans (Without Touching the Code)”. In his presentation he talked about the possibilities of influencing the execution plan of a query, without actual change the query itself. A suggestion could be changing database parameters, well this can be seen as “Big Knob Tuning” and would be very unadvisable.  Some other ideas are start adding new access paths, or remove some, in plain Oracle language this means add indexes or remove some. The other idea could be by start changing stats on objects by the DBMS_STATS package. Well all these suggestion will not be very specific and a lot of different queries are influenced. Lucky Oracle did some other things too. Kerry told that Oracle made three ways, Outlines, Sql profiles and Sql Baselines. These three ways where created with different goals by Oracle, but do all basically the same, with some small twists. The use of outlines was a half baked product, one of the issues was to set every time “use_stored_outlines” to true, with every restart of the database instance. SQL profiles were three quarter baked but are not perfect either, one of the advantages over outlines is the fact that they can be applied to multiple statements. The SQL Tuning Advisor (STA) Profiles (SQL Baselines), are still not perfect but do multiple things for the stability, first of all they can add hints and second they can store metadata for the query to give the optimizer a better understanding of the data and data distribution.

The way Kerry showed the possibilities were very nice and a very good closing of HOTSOS day 1.

Regards,

Gerwin

I talked Friday with Dwight Merriman, founder of 10gen (the MongoDB company). He more or less convinced me of his definition of NoSQL systems, which in my adaptation goes:

NoSQL = HVSP (High Volume Simple Processing) without joins or explicit transactions

Within that realm, Dwight offered a two-part taxonomy of NoSQL systems, according to their data model and replication/sharding strategy. I’d be happier, however, with at least three parts to the taxonomy:

• How data looks logically on a single node
• How data is stored physically on a single node
• How data is distributed, replicated, and reconciled across multiple nodes, and whether applications have to be aware of how the data is partitioned among nodes/shards.

After talking with Dwight, and also with Cassandra project chair Jonathan Ellis, I feel I’m doing decently in understanding the first of those three areas. But there’s a long way yet to go on the other two.

In Dwight’s opinion, as I understand it, NoSQL data models come in four general kinds.

• Key-value stores, more or less pure. I.e., they store keys+BLOBs (Binary Large OBjects), except that the “Large” part of “BLOB” may not come into play.
• Table-oriented, more or less. The major examples here are Google’s BigTable, and Cassandra.
• Document-oriented, where a “document” is more like XML than free text. MongoDB and CouchDB are the big examples here.
• Graph-oriented. To date, this is the smallest area of the four. I’m reserving judgment as to whether I agree it’s properly included in HVSP and NoSQL.

As Dwight sees it, JSON (JavaScript Object Notation) is the emerging markup standard for the document-oriented data models, and to some extent the BLOB part of key-value models as well. Reasons seem to include:

• JSON is something web developers are likely to know anyway.
• JSON, unlike XML, is schema-less. In the NoSQL world, that’s perceived as a good thing.
• Perhaps for both these reasons, JSON is perceived as easier to use than XML.

Except as noted, I’m not aware of anything that solidly contradicts the above.

Dwight went on to say that there are two main NoSQL replication/sharding models, in line with the seminal papers to which I previously linked:

• Based on or resembling Dynamo. The core idea here is accepting eventual consistency among nodes as being good enough, even if that means you sometimes read dirty data. The benefit is that you never are blocked from writing. By way of contrast, systems that enforce true inter-node consistency (think of a two-phase commit) can shut you down from writing if consistency guarantees aren’t being confirmed in a timely manner. Thus, in a Dynamo-like scheme you write data to multiple nodes, via consistent hashing; then when the time comes you read one or more nodes, and hope that what you’re getting back is a correct result.
• Based on or resembling BigTable. In this model you’re trying to keep the nodes fully consistent in the usual way, e.g. by synchronous replication. Indeed, what’s being kept consistent is both data itself, and metadata about the data’s location. Details surely vary a lot from implementation to implementation.

I’m fuzzier on this stuff than on the data models, because to date nobody has ever explained to me how an actual live system (MongoDB, Cassandra, whatever) implements its replication strategy. Also, while I think that in both these models applications are allowed to be ignorant of the replication/sharding strategy, I’m not as sure of that as I’d like to be.

If we stop here, we already have something useful. MongoDB has a document data model, and is in the BigTable-like replication camp, at least at first. Cassandra has a table-like data model, and is on the Dynamo-like eventual consistency side. But to say those are the only differences that matter would be like saying that all shared-disk RDBMS (e.g., Oracle and Sybase IQ) are essentially alike. That, of course, would be nonsense.

So a third dimension needed in this taxonomy is how the systems actually bang data on and off of disk (or silicon, as the case may be). I don’t yet have an overview of that. I know something of how Cassandra does it, and will write about same in a future post, but that’s about it. So please stay tuned.

Going through some old documents, I found this little gem I had uncovered doing some analysis of the source code.

I found this crazy enough to save, so I hope you enjoy it.

I found 14 or 15 commits in a 115 line procedure. I was shocked and stunned. It was wrong on so many different levels. I share with you the pain I went through.

We'll start off with a call to the logging table (just a quick note, this was not a stored procedure but a INSERT statement).log_something;Creating a record in the THIS_TABLE, it will have a status of A. Everything in there has a status of A.INSERT INTO this_table (my_id, start_date)
VALUES ( l_my_id, sysdate)
RETURNING table_id INTO l_table_id;I know some could argue for COMMITs being inside stored procedure, but it was hammered into my head at an early age that the calling application should perform the COMMIT.COMMIT;THIS_TABLE had multiple "rules" tables. I understood the concept, but the implementation was not so good. This is the first "rules" table.UPDATE rule_tab
SET status = 'D'
WHERE my_id = l_my_id
AND status = 'A';Just so you are aware, that STATUS column had no constraint on it other than the size VARCHAR2(1).

Guess what time it is?COMMIT;Here is the secondUPDATE other_rules_table
SET status='D'
WHERE my_id = l_my_id
AND status='A';Guess what?COMMIT;OK...here comes the other awesome part...to switch the status back to A (Active), we're going to create a job...in 30 minutesdbms_job.submit
( job => ln_jobno,
what => 'update rule_table set status=''A'' where my_id = '||l_my_id||' AND status = ''D'';',
next => sysdate+(.5/24));COMMIT;I kind of understand that one...for the job to go into the queue you have to issue the COMMIT.

For some reason, it's now cool to use a nested block.BEGIN
dbms_job.submit
( job => ln_jobno,
what => 'update other_rule_table set status=''A'' where my_id = '||l_my_id||' AND status = ''D'';',
next => sysdate+(.5/24) );

COMMIT;
EXCEPTION
WHEN OTHERS THEN
insert into errors (name,error_date, text)
values ('it broke', sysdate, ' MY_ID '||l_my_id||' creating job to set to a ');
COMMIT;
END;Now we have 2 jobs created, one to update THIS_TABLE and one to update my first "rules" table. -10 for using DML in a job. -10 for not putting them into a single job that could fail together. -30 for not creating a stored procedure to do this.

But wait, it's not over yet. We're getting to the very best part I think.BEGIN
dbms_job.submit
( job => ln_jobno,
what => 'insert into errors (name,error_date, text)
values (''wow'', sysdate, ''error turning it back on') ;',
next => sysdate+(.5/24));

EXCEPTION
WHEN others THEN
insert into errors (name,error_date, text)
values ('doing stuff', sysdate, ' MY_ID '||l_my_id||' creating job to log errors');

COMMIT;
END;Did I catch a "niner" in there?

Did you catch what that final job did? It "logged" an error from the previous 2 jobs. Really? Do jobs work like that? I'm not really sure that they do.

This little snippet is just a snapshot into my life over the last few years. This kind of thing is everywhere. (I know everywhere!). I've mentioned before, but I've been reading The Daily WTF since 2005. Daily. I've learned more from that site than perhaps any other because you learn what not to do...which is just as important as what to do. It takes years to gain the necessary experience (read: screwing up) to know what not to do, The Daily WTF speeds that up significantly by allowing you to witness others mistakes. We've all made them, to be sure. It's whether we learn from them that is important.

I have just added three submisions for OOW 2010.

Automate the creation of extra Coherence machines based on Grid Control data
Use Oracle VM to learn Oracle technology
Build a Coherence cluster using Oracle VM to speed up Oracle FMW

If you didn't have time to review APEX 4.0 Early Adopter I and II, but you want to have a quick look what new features will be in APEX 4.0, you can do that now...

There is an APEX 4.0 New Features Application which shows a lot of the new features in action.
e.g. the screenshot below shows the new features for tabular forms in action.


If you want more detail about the new features and when they got introduced (e.g. you reviewed EA1 but want to see what is new in EA2), you can go to the APEX 4.0 New Features Description.

I find it very useful to have something like above.

Debra is one of my 3 ACE keepers and I love her (and the others) but sometimes there appears to be other things going on in her life that get in the way of my blog being written! I would do it myself but it was decided when I was commissioned that money could be saved but skimping on cloth. I don't even have arms let alone hands and fingers to type myself. I have tried voice recognition but the only people who can hear me speak are the 3 keepers and Mrs Norris.

Still this is meant to be my final holiday blog not a rant at how unfair life is. I was just explaining why this blog is out of order in the timeline. I told you about the journey up to the mountains, well what happened when we got there? If you are a Facebook Fan of mine there are a lot more photos posted there but as usual I was the best behaved of the lot.

My friends are Geeks, IT is their life and the first thing on the list of needs for a suitable condo is Internet access. They need to stay online or become very ill. It isn't an obsession, and in fact most of it is talking to each other, I know Debra often emailed people at the same table not just those who couldn't join us. But this is not about them, this is about me.



One tradition is the hot tub and here you can just make out Alex Gorbachev holding me up and hidden by the steam is Jeremiah Wilton. Then I am left out over night to freeze.










Last year I was then used as a sledge and then Frisbee, but this year I was taken upstairs to the faux oak table.













I got in the way a bit so then they used me as a table! Problem with that is that as all the 'love' in the room warmed me up, I started to drip as I defrosted, so I was banished to the shower to thaw out.

Last year they tried to murder me with a noose but I was too quick for that this year however Debra nearly stabbed me with a very sharp icicle, which would have been very clever as all the evidence melted away soon after.

All too soon the holiday was over again. Thanks Tim for organising a great trip to the mountains and for all my friends who came along, see you again soon.

I walked off the 7 hour flight to Dubai, straight on to the 13+ hour flight to Melbourne. I got a 1 hour rest at Melbourne while they cleaned the plane, before I started the 3+ hour flight to Auckland. I got to Auckland at about 14:00 where I was met by one of my friends. I was whisked straight back to his house and spent the rest of the day catching up on everything that happened since my last skype call ( 4 days before ) and playing with their kid.

The following day was mostly spent with a trip to some of my friends relatives and general idle conversation. Very low pressure. Very cool. The funny thing is, it’s about 18 months since I was last here and I feel like I never left.

This morning I got up early to pick Chris Muir up from the airport, where I bumped into Dan Morgan, not literally. Chris was delayed, so after a bit of messing about we got on our way to Rotorua. The roads were quiet so we made good time and I’m now in the hotel ready to go. We have a little get together tonight, then the conference starts tomorrow.

I guess I’ll do a run through of my presentations now, and maybe sleep a little later.

Cheers

Tim…

Let’s start from some reasonable premises.

• No technology category name is ever perfect.
• It’s particularly hard to describe NoSQL (Not Only SQL) accurately, given the basic confusion as to what NoSQL is all about.
• That said, it seems pretty clear that NoSQL is about making big websites (and perhaps other cloud-like installations) run and scale.
• Dwight Merriman (founder/CEO of MongoDB vendor 10gen) is heading in the right direction when he says that the unifying ideas of NoSQL are that you do away with transactions and joins. But if he’s ever said something like “NoSQL is Foo without joins and transactions,” I don’t know what Foo is.
• Actually, I do know what Foo is – Foo is what happens when lots of people want to get small amounts each of information in or out of a database at the same time. I just don’t know what Foo is called.
• Obviously, Foo is a lot like OLTP (OnLine Transaction Processing). However, it would be pretty silly for Foo to actually be OLTP, given that one of the core points of NoSQL is that you don’t have transactions.
• It not just the “T” part of OLTP that’s fried. Calling something “OnLine” only makes sense as long as offline is an option, and offline transaction processing has been obsolete for a very long time.*

*Sure, if you strain you can talk yourself into exceptions. But the point stands.

So we need a name for Foo, where Foo is what happens when lots of people want to get small amounts each of information in or out of a database at the same time. Thus, three major subcategories of more-or-less disk-based Foo are:

• No-compromises ACID-compliant relational OLTP
• Sharded MySQL
• NoSQL

There may be some more purely memory-centric versions too, but let’s put those aside for the moment.

Absent a better idea, I can squeeze Foo into yet another four-letter acronym:

HVSP (High-Volume Simple Processing)

That’s as imperfect as any other category name, and an awkward mouthful to boot. So I’d love to hear a better one; if you have such, please share it! In the mean time, I think “HVSP” has merit because:

• The “Processing” part should be noncontroversial.
• “High-Volume” is inherent to the challenge. If RDBMS scale well enough for your use case, using something less powerful is probably silly.* Similarly, while Oracle shines at high-volume OLTP workloads, there are many cheaper DBMS that do a fine job of OLTP at lower volumes.
• “Simple” is the core principle of NoSQL systems, which drop joins and transactions as being too much foofarah. That only makes sense at all under the assumption that you have bone-simple queries and updates, so that programming around the lack of joins and transactions isn’t all that much of a burden.
• Something similar is true of sharded MySQL.
• Less obviously, “simple” is a core principle of relational OLTP as well. The point of the relational model is to cap the complexity of data operations, or more precisely to hide that complexity from programmers.
• And overloading the word “simple” a bit, it’s fair to say that if you’re reading or writing one record at a time, you’re doing something relatively simple, at least as opposed to what you do in analytic processing. The OLTP vs. OLAP distinction is preserved in this name change.
• The whole thing matches my definition above, namely “what happens when lots of people want to get small amounts each of information in or out of a database at the same time.”

*Assuming, of course, that rows-and-tables are a good metaphor for your data structure in the first place.

Systems I’m leaving out of the HVSP and hence also NoSQL categories include:

• Hadoop and other batch-oriented MapReduce. Hadoop isn’t part of NoSQL. I’m pretty sure that Cloudera CEO Mike Olson agrees with me.
• More generally, non-SQL data stores that don’t meet the HVSP criteria. Dave Kellogg stretches things when he claims that MarkLogic is a NoSQL system. (But then, that was in a post where he seemingly praised a train wreck of an article.)

But hey – what good is a categorization if it doesn’t leave some things out?

Hi,
This is short instruction how to install Oracle ACFS on CentOS. CentOS is free version of RedHat and it is binary compatible. It is not officially supported by Oracle but all solutions related for RedHat should work on CentOS too.I have found Laurent blog post about it and I used it as a background of my investigation. When I have used Laurent's steps it was fine until I want to register ACFS in Oracle Cluster Registry. I couldn't do it as not all cluster types has been registered during Grid Infrastructure configuration process.Here is a simple solution how to fix it for new installations (still working on solution for existing one).

1. Perform GUI part of Grid Control installation
2. Before executing root.sh on both nodes edit following file on both nodes : $ORACLE_HOME/lib/osds_acfslib.pm
   
   line 299:
   else {
   # we don't know what this is
   $supported = 1;
   $vers = "EL5";
   }
   
3. Execute a root.sh on both nodes

Now ACFS will be installed and configured and all necessary tools will be copied.

Hi,
This is short instruction how to install Oracle ACFS on CentOS. CentOS is free version of RedHat and it is binary compatible. It is not officially supported by Oracle but all solutions related for RedHat should work on CentOS too.I have found Laurent blog post about it and I used it as a background of my investigation. When I have used Laurent's steps it was fine until I want to register ACFS in Oracle Cluster Registry. I couldn't do it as not all cluster types has been registered during Grid Infrastructure configuration process.Here is a simple solution how to fix it for new installations (still working on solution for existing one).

1. Perform GUI part of Grid Control installation
2. Before executing root.sh on both nodes edit following file on both nodes : $ORACLE_HOME/lib/osds_acfslib.pm
   
   line 299:
   else {
   # we don't know what this is
   $supported = 1;
   $vers = "EL5";
   }
   
3. Execute a root.sh on both nodes

Now ACFS will be installed and configured and all necessary tools will be copied.