Search

Top 60 Oracle Blogs

Recent comments

June 2011

Presenting in IOUG Webinar: RAC Performance tuning – Private inter connect

I will be presenting in IOUG Webinar on Thursday June 30, 2011 11AM-12Noon CDT. Hope you can join me.
Register at Private interconnect .
I know, I have not been blogging more actively, but I have been working on books and few presentations lately. Hopefully, you will see more blog entries soon.
Thanks for reading my blog.

Give Me a Hint – How were These Autotrace Execution Statistics Achieved?

June 27, 2011 I recently received an email asking why different performance is achieved when a FIRST_ROWS hint, FIRST_ROWS(100) hint, and an unhinted version of the query are executed.  This seems to be a simple problem, yet it might also be an interesting problem.  I thought that it might be helpful to transform my response into a blog article (allowing the [...]

KSCOPE 2011: Sunday’s Symposium

Sunday’s Conference day started very early at around 07:30 by going straight to registration, then on to a quick breakfast and the first session at 08:30 AM. Joel Kallman of the APEX Development team started my round of that morning by explaining all the tools and processes which are used / were used by the APEX Development teams that is located more or less around a huge amount of different places in the US, Europe and India. Not even to mention al the supporting people on topics like security, creating manuals, testing, supporting etc, etc. Fun to see that the team uses tools like Hudson, Subversion and Oracle’s conference tooling to be able to work wherever you as a team member are located.

Kris Rice talked about new and sometimes not so known features in SQL Developer 3.0. It was cool to see, while demo-ed by Kris, which features went in, in this new version. Stuff like drag & drop tables into a worksheet that give you a wizard for insert, update, delete etc and join options that automatically generates the SQL you need as a basis or the final statement needed. There are a lot of new options in there, I don’t know really which ones, because I started really using SQL Developer now in a project from version 3.0 “onwards” but, although I am not that GUI guy, I really start to like it. Not sure if it already will replace my UltraEdit tool, but it is getting there for some of those features like explain plan, autotrace, doing a “DIFF” between a baseline environment and a development user schema. Something I also use very much is all those fast wizards to create “load” and/or “unload” features in CSV, insert statements, SQL*Loader format or, for example (there are lots more), if needed, secured pdf file format. Something I didn’t notice yet is that you now also can open Oracle trace files and SQL Developer nicely formats the output. You also have the option to sort or filter this trace output in SQL Developer. Nice one. Also still a lot of nice stuff to come like Scheduler wizards that help you graphically layout the Scheduler process.

To give you another small teaser of what you were missing out on, have a look at the peak of the “SQL Developer’s Build in Logging and Monitoring presentation of Kris later on in the afternoon…and although not everything went that smoothly, Kris demo’s some of the power which has been build-in nowadays in SQL Developer V3.

Sue Harper demoed and talked about the SQL Developer Data Modeler, as always, very passionately and justified… SQL Developer Data Modeler is a great tool to design or getting fast info about an existing database environment via reverse engineering. At least for me thats a great plus… SQL Developer Data Modeler is nowadays even a free product, that is “a no cost option”, so in other words “free” as long as you have licensed your database. You can use it as s stand alone product or as a embedded option via SQL Developer. I would really like in SQL Developer as in SQL Developer Data Modeler an option like WebDAV, so it would/could be using XDB Repository options (XMLDB) to store XML related info like metadata stored, in XML, data. Said that most options are integrating with Subversion like looking up the differences between different saved designs. I can’t loose the feeling though that I could might make sense to store, handle XML related data in the database, instead on a file system (or even Subversion), for a tool set that pinpoints on database design and database development and its in there so why not use it. It could make even more sense regarding saving design in the same database as the development /code and options you would have like, backup & restore, oracle (text) search options or XML search via an XMLIndex and / or using XML diff’s.

Tom Kyte’s session started with pointing out all those tools you already have for free, what’s out there for free at least say, those “no cost options”, that are already there in the database and how important it is to gather some info to be able to further improve you environment if it is the code in the database or if there are problems outside the database. So the session mentioned and demo’d items like DBMS_APPLICATION_INFO, DBMS_MONITOR and DBMS_TRACE. To give you a small peak of the presentation have a look at the following short 10 min. Tom Kyte video I taped during the session.

Hope you enjoyed this small overview of the things that went on during Kaleidoscope’s 2011 Sunday’s Conference day.

M.

KSCOPE 2011: Combining Work with Fun

Finally arrived on Friday in Long Beach after a long flight. On Saturday we (in the end: Alex Nuijten, Patrick Barel and Roel Hartman) were very lucky to be asked by Aris Prassinos, who is living and working in the Los Angeles area, if we were interested in a small tourist by car tour for stuff like: Hollywood Blvd, Walk of Fame, Malibu, Sunset Blvd, Universal Pictures etc. So just to show you the area of this years Kaleidoscope 2011 Conference hereby some pictures from, amongst others the places mentioned above plus area’s like Mulholland Drive

One of the old movie theaters in LA, USA
The place to be when the
No honey here (Walk of Fame, LA, USA)
Mickey Mouse, Walk of Fame, LA, USA
Walk of Fame, LA, USA
Errh ! (aka
Outside Universal Studio's, LA, USA
Universal Studios, LA, USA
Los Angeles as seen from Mulholland Drive
The Hollywood sign as seen from Mulholland Drive, LA
The conference center in Long Beach, CA, USA
View on the harbor, Long Beach, CA, USA
View on the harbor, Long Beach, CA, USA
Renaissance Hotel, Long Beach, California, USA
Walk of Fame, Los Angelas, CA, USA
Walk of Fame, Los Angelas, CA, USA
Kermit the Frog, Walk of Fame, Los Angelas, CA, USA
El Capitan, LA, The Movie Theater Disney show its new pictures first
View from one of the elavators from the Renaissance Hotel, Long Beach, CA
View outside the Renaissance Hotel, Long Beach, CA
Patrick Barel and Roel Hartman waiting
Alex Nuijten and Roel Hartman waiting
Full star trek cast
Nerdy geek stuff

Deferred Segment Creation in PeopleSoft

This note has been in my to do folder for a while since I found these two excellent blog entries about Deferred Segment Creation by Chistian Antognini.

They made me think about the use of this feature in PeopleSoft. In most PeopleSoft systems there are lots of empty tables, sometimes because not all the modules delivered in the database are in use, but also because not all the temporary table instances have ever been used. This blog entry from Tom Kyte feels very close to home

Deferred Segment Creation in PeopleSoft

This note has been in my to do folder for a while since I found these two excellent blog entries about Deferred Segment Creation by Chistian Antognini.

They made me think about the use of this feature in PeopleSoft. In most PeopleSoft systems there are lots of empty tables, sometimes because not all the modules delivered in the database are in use, but also because not all the temporary table instances have ever been used. This blog entry from Tom Kyte feels very close to home

Mything 2

It’s about time I wrote a sequel to Mything in Action – and funnily enough it’s also about bitmap indexes. It starts with a note on the OTN database forum that prompted me to run up a quick test to examine something that turned out to be a limitation in the optimizer. The problem was that the optimizer didn’t do a “bitmap and” between two indexes when it was obviously a reasonable – possibly even good – idea. Here’s some sample code:

create table t1
as
with generator as (
	select	--+ materialize
		rownum id
	from dual
	connect by
		rownum <= 10000
)
select
	mod(rownum-1,10)	c1,
	mod(rownum-1,100)	c2,
	mod(rownum-1,101)	c3,
	rownum			id,
	lpad(rownum,10,'0')	small_vc,
	rpad('x',100)		padding
from
	generator	v1,
	generator	v2
where
	rownum <= 1000000
;

-- stats collection goes here.

create bitmap index t1_b1b2 on t1(c1,c2);
create bitmap index t1_b1b3 on t1(c1,c3);

This was a reasonable model of the situation described in the original posting; and here’s the critical query with the surprise execution path:

select
	/*+
		index_combine(t1 t1_b1b2 t1_b1b3)
	*/
	*
from
	t1
where
	c1 = 5
and	c2 = 50
and	c3 = 50
;

------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost  |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    10 |  1250 |   231 |
|*  1 |  TABLE ACCESS BY INDEX ROWID | T1      |    10 |  1250 |   231 |
|   2 |   BITMAP CONVERSION TO ROWIDS|         |       |       |       |
|*  3 |    BITMAP INDEX SINGLE VALUE | T1_B1B2 |       |       |       |
------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("C3"=50)
   3 - access("C1"=5 AND "C2"=50)

You might look at the query and the indexing and decide (as I did) that Oracle ought to be able to manage a “bitmap index single value” on both the indexes, then do a “bitmap and” to minimise the work – something like:

------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost  |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    10 |  1250 |     6 |
|   1 |  TABLE ACCESS BY INDEX ROWID | T1      |    10 |  1250 |     6 |
|   2 |   BITMAP CONVERSION TO ROWIDS|         |       |       |       |
|   3 |    BITMAP AND                |         |       |       |       |
|*  4 |     BITMAP INDEX SINGLE VALUE| T1_B1B2 |       |       |       |
|*  5 |     BITMAP INDEX SINGLE VALUE| T1_B1B3 |       |       |       |
------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("C1"=5 AND "C2"=50)
   5 - access("C1"=5 AND "C3"=50)

But it doesn’t – and there’s a clue about why not in the “Predicate Information”. To create this plan the optimizer would have to duplicate an existing predicate (c1 = 5) so that it could find the second index after selecting the first one. There’s no reason, of course, why the optimizer code couldn’t do this – but at present, even in 11.2.0.2, it just doesn’t. Perhaps this is another opportunity for thinking about manual optimisation strategies – or perhaps ensuring that you’ve created the right set of indexes.

You might notice, of course, that Oracle seems to have ignored my index_combine() hint. Oracle doesn’t ignore hints, of course (apart from cases suffering from problems with syntax, legality, or bugs) but remember that index_combine() is only supplying a list of indexes that Oracle should consider, it doesn’t require the optimizer to use every index in the list. In this case, of course, the hint also has an error because it’s naming an index that can’t be used.

Anyway, I wrote a note suggesting that there was a gap in the optimizer’s strategies, specifically:

I’ve just spent a few minutes playing with a data set where this (c1,c2) (c1,c3) type of index combination is obviously a good idea – and can’t get the bitmap_tree() hint to force the path. I think this means there’s a hole in the optimizer’s legal strategies that you might have to fill by other methods.

Here’s where the mything starts. The OP replied as follows:

Do I right understand that it is impossible to combine bitmap non-one-column indexes?

ABSOLUTELY NOT!, the OP has jumped from the particular to the general; fortunately he asked the question, rather than silently making the assumption then spreading the gospel. Of course I was at fault because I could have pointed out explicitly that the pattern was dependent on the two indexes starting with the same column – but is it so hard to interpret patterns.

What’s more annoying is that the OP was already using a model to test what happened – would it have been so hard for him to try a few different combinations of indexes – switching the column order on both indexes. For example what happens if the indexes are (c2, c1)(c3,c1) ?


----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    10 |  1250 |    12   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID | T1      |    10 |  1250 |    12   (0)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS|         |       |       |            |          |
|   3 |    BITMAP AND                |         |       |       |            |          |
|*  4 |     BITMAP INDEX SINGLE VALUE| T1_B2B1 |       |       |            |          |
|   5 |     BITMAP MERGE             |         |       |       |            |          |
|*  6 |      BITMAP INDEX RANGE SCAN | T1_B3B1 |       |       |            |          |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("C2"=50 AND "C1"=5)
   6 - access("C3"=50)
       filter("C3"=50)

See how easy it is to show that the optimizer can combine multi-column bitmap indexes; but we can observe, at the same time, that it doesn’t make “perfect” use of the (c3, c1) index. Oracle still does excess work in the index because it hasn’t repeated the use of the c1 predicate.

Maxim:

When you see some unexpected behaviour the least you should do when investigating it is to ask yourself: “in what way might this be a special case?”

Training, twitter, Oracle security products

Time flies by so fast..:-), its been two months since my last blog post but it only seems like yesterday...:-(, Times are very busy for me so blogging has become harder to fit in. Just writing a blog post seems....[Read More]

Posted by Pete On 24/06/11 At 11:29 AM

Data Science Fun with the OOW Mix Session Voting Data

Over the past few weeks Oracle Mix had opened the Oracle OpenWorld 2011 Suggest-a-Session to the general public where anyone could submit or vote on a session. One limitation of the Oracle Mix site was that it was impossible to sort the sessions by votes but that challenge was tackled by Roel Hartman with his blog post and APEX demo. After seeing the top session by votes, it was very interesting to me that around half of the top 15 sessions were all from the same author. That got me thinking…and that thinking turned into a little data hacking project that I embarked on. Now I admit it, I think data is very cool, and even cooler is extracting patterns and neat information from data.

Getting the Data

The Oracle Mix site is very “crawler friendly” — it has well defined code and tags which made extracting the data fairly painless. The basic process I used came down to this:

  1. Get the listing of all the session proposals. That was done by going to the Mix main proposal page and walking all 43 pages of submissions, scraping the direct URL to each session.
  2. Now that I had all of the session abstract URLs, grab each of those pages, all 424 of them
  3. From each session page, extract the relevant bits: Session Name, Session Author, Total Vote Count, and most importantly, who voted for this session.

I did all of that with curl, wget and some basic regex as a “version 1″ but was hoping to go back and try it again using some more sexy technology like Beautiful Soup. That will have to be continued…

The Social Network Effect

With Oracle Mix Suggest-a-Session, people generally vote for a session for one of two reasons:

  1. They are generally interested in the session topic
  2. The session author asked them to vote because of their social relationship

What I think is interesting to know is just how much of the voting is done because of #2. After all, Oracle Mix is a social networking site so there certainly is some voting for that reason. In fact, one of the session authors, Yury Velikanov from Pythian, even blogged his story of rounding up votes. The data shows us this, but more on that in just a bit…

The (Unofficial) Data

I took some time to mingle around the data and found some very interesting things. Let’s just start with a few high level points:

  • There were 424 sessions submitted from 252 different authors.
  • There were 10,125 votes from 2,447 unique voters.
  • The number of submissions ranged from 1 to 24 per author.

Here are some interesting tidbits I extracted from the data set (apologize for not making a cool visualization chart of all this – but I’ll make up for it later):

-- top 10 sessions by total votes:
+-------------+-----------------+--------------------------------------------------------------------------------+
| total_votes | session_author  | title                                                                          |
+-------------+-----------------+--------------------------------------------------------------------------------+
|         167 | tariq farooq    | Oracle RAC Interview Q/A Interactive Competition                               |
|         156 | tariq farooq    | Database Performance Tuning: Getting the best out of Oracle Enterprise Manager |
|         137 | tariq farooq    | Overview & Implementation of Clustering & High Availability with Oracle VM     |
|         130 | tariq farooq    | Migrate Your Online Oracle Database to RAC Using Streams and Data Pump         |
|         127 | tariq farooq    | 360 Degrees - Achieving High Availability for Enterprise Manager Grid Control  |
|         126 | yury velikanov  | Oracle11G SCAN: Sharing successful implementation experience                   |
|         124 | sandip nikumbha | Accelerated Interface Development Approach - Integration Framework             |
|         123 | tariq farooq    | Oracle VM: Overview, Architecture, Deployment Planning & Demo/Exercise         |
|         123 | sandip nikumbha | Remote SOA - Siebel Local Web Services Implementation                          |
|         119 | yury velikanov  | AWR Performance data mining                                                    |
+-------------+-----------------+--------------------------------------------------------------------------------+

-- top 10 voters (who place the most votes)
+--------------------+--------------+
| voter_name         | votes_placed |
+--------------------+--------------+
| arup nanda         |           53 |
| tariq farooq       |           43 |
| connie cservenyak  |           36 |
| xiaohuan xue       |           36 |
| bruce elliott      |           36 |
| peter khoury       |           35 |
| yugant patra       |           35 |
| balamohan manickam |           35 |
| suresh kuna        |           34 |
| eddie awad         |           34 |
+--------------------+--------------+

-- top 10 voters by unique session authors (how many unique authors did they vote for?)
+--------------------+----------------+
| name               | unique_authors |
+--------------------+----------------+
| arup nanda         |             28 |
| paul guerin        |             24 |
| eddie awad         |             24 |
| bruce elliott      |             23 |
| xiaohuan xue       |             23 |
| connie cservenyak  |             23 |
| peter khoury       |             22 |
| wai ling ng        |             22 |
| yugant patra       |             22 |
| balamohan manickam |             22 |
+--------------------+----------------+

-- top 10 session authors by total votes received, number of sessions, avg votes per session
+---------------------+-------------+----------+-----------------------+
| session_author      | total_votes | sessions | avg_votes_per_session |
+---------------------+-------------+----------+-----------------------+
| tariq farooq        |        1057 |        8 |              132.1250 |
| yury velikanov      |         557 |        5 |              111.4000 |
| alex gorbachev      |         429 |        6 |               71.5000 |
| sandip nikumbha     |         360 |        3 |              120.0000 |
| syed jaffar hussain |         281 |        4 |               70.2500 |
| kristina troutman   |         233 |        5 |               46.6000 |
| russell tront       |         221 |        3 |               73.6667 |
| wendy chen          |         217 |        3 |               72.3333 |
| asif momen          |         184 |        2 |               92.0000 |
| alison coombe       |         183 |        5 |               36.6000 |
+---------------------+-------------+----------+-----------------------+

Diving In Deeper

I could not help noticing that Tariq Farooq had the top 5 spots by total vote count. I would assert that is related to these two points:

  1. Tariq has some very interesting and apealing sessions
  2. Tariq has lots of friends who voted for his sessions

I have no doubt there there is some of both in the mix, but just how much influence on the votes is there from a person’s circle of friends? Or to put another way: How many voters only voted for a single session author? Or even more interesting, how many people voted for every session for a single author, and voted for no other sessions? All good questions…with answers that reside in the data!

-- number of users who voted for exactly one author
+---------------------------+
| users_voting_for_1_author |
+---------------------------+
|                       828 |
+---------------------------+

-- number of voters who voted for every session by a given author
-- and total # of votes per voter is the same # as sessions by an author
+-------------------------------------------------+
| users_who_voted_for_every_session_of_an_author |
+-------------------------------------------------+
|                                             826 |
+-------------------------------------------------+

Wow – now that interesting! Of people only voting for a single session author, just two of them did not vote for every one of that author’s sessions. That’s community for you!

Visualizing the Voting Graph

I was very interested to see what the Mix Voting Graph looked liked, so I imported the voting data into Gephi and rendered a network graph. What I was in search of was to identify the community structure of the voting community. Gephi lets you do this by partitioning the graph into modularity classes so that the communities become visible. This process is similar to how the LinkedIn InMap breaks your professional network into different communities.

Here is what the Oracle Mix voting community looks like: />

This is a great visualization of the communities and it accentuates the data from above – the voters who only voted for a single author. This can be seen by the small nodes on the outer part of the graph that have just a single edge between it and the session author’s node. Good examples of this are for Yury Velikanov and Tariq Farooq. Also clearly visible is what I’d refer to the “Pythian and friends” community centered around Alex Gorbachev and Yury Velikanov in the darker green color. There are also several other distinct communities and the color coding makes that visible.

Shouts Out

This is my first real data hacking attempt with web data and using some of the tools like Gephi for the graph analysis. One of my inspirations was Neil Kodner‘s Hadoop World 2010 Tweet Analysis, so I need to give a big shout out to Neil for that as well as his help with Gephi. Thanks Neil!

And One Last Thing

So what are people’s sessions about that were submitted? This Wordle says quite a bit.

Source

If you wish to play on you own: https://github.com/grahn/oow-vote-hacking

Addendum

Here is another graph where the edges are weighed according to votes to an author (obviously related to number of sessions for that author). />

JL @ Turkish Oracle Users Group

Media: 
See video

Recording courtesy of the people at Formspider.
The topic is about tuning a two-table join, and the video is about an hour long (so there’s a lot more that could be said on the topic, and one day I might do a half day “masterclass” on it).