Search

OakieTags

Who's online

There are currently 0 users and 33 guests online.

Recent comments

Affiliations

Oakies Blog Aggregator

VirtualBox 4.1 Released…

Hot on the heels of the VirtualBox 4.0.12 maintenance release, shipped a few days ago, comes VirtualBox 4.1. It contains loads of new features, explained here and in the changelog.

The upgrade went smoothly on my MacBook Pro, but on my Fedora 15 servers I had to uninstall the old version manually before installing the new version. None of my settings were lost so everything was easy enough.

It certainly seems applying VirtualBox upgrades is becoming a fulltime job. Of course, the quick release cycle is a lot better than getting no updates, like VMware Server. :)

Cheers

Tim…




Bitmap Indexes & Minimize Records_Per_Block (Little Wonder)

As mentioned in my first post regarding the use of Bitmap Indexes to service Not Equal predicates, an index entry in a Bitmap Index consists of the indexed value and a pair of rowids that determine the range of potential rows spanned by the corresponding bitmap sequence. However, Oracle has no way of determining how many [...]

Listener won’t register database

 

I must admit, I’m a sqlnet hack.  I never really sat down to figure it out. If I have problems getting a database to register with a currently listener then I generally just add a separate listener. Well, today I was stuck on a system where I couldn’t add a second listener. Everything seems fine.  There was a default listener running on 1521 with  a database instance already registered. The first and second database were in the same ORACLE_HOME but I just couldn’t get the listener  to pick up the second databases.  I bounced and reloaded the listener and registered the second database

  • lsnrctl start
  • lsnrctl stop
  • lsnrctl start
  • alter system register

No dice. Then I tried

alter system set local_listener=’(ADDRESS = (PROTOCOL=TCP)(HOST =myhost)(PORT=1521))’

and bang, it registered.

Of course, I guess I should track down why it wouldn’t register in the first place but getting it working for now was enough.

 

 

 

VirtaThon – Mining the AWR

Earlier I did a presentation at VirtaThon which is the same topic that I presented at Hotsos 2011.. Mining the AWR and Capacity Planning are very dear to my heart and up until now I’m using every research I did on that presentation to work on an “Exadata Provisioning Tool” which I’m planning to present at the next Hotsos 2012… well, the only thing that’s different this time is.. my attendees are virtual geeks all over the world ;)

I was at the Virtual Room #100, and the staff earlier were really helpful and most of all I had fun doing that webinar.. and I get to “doodle” on a lot of my slides!

Overall, that was a fun first time experience ;)

Check out the presentation slides below and the scripts here..

BTW, as per Tariq Farooq.. Everything is being recorded! and they will be “broken up” and uploaded within a week or so.. So I’ll be updating this post and include that recording once the video comes out ;)

Hope I’ve shared you some good stuff ;)





Google+ Hangouts and Support…

You open a ticket and wait… When you do get a reply it tells you to send information you’ve already posted, or suggests you try some workarounds you’ve already listed in the ticket as having not worked for you. You get frustrated and write a blog post ranting about how terrible the support service is etc. I guess this could be a story about just about any internet support service I’ve had to use over the years.

Do you remember in the old days, before the internet was popular, when you phoned support lines? Do you remember how quickly some of these annoying issues were resolved by simply saying, “I’ve already sent that!”, to a real person at the end of the line? OK. I’ve conveniently forgotten to mention being put on hold for hours, but this is my blog and I’m allowed to have a totally biased opinion about things… :)

Maybe elements of the good old days are coming back thanks to social media. Check out this article where Michael Dell proposes using Google+ Hangouts as a way of connecting to Dell service and sales.

Imagine the joy of being able to rant directly at a real person again. :)

Cheers

Tim…




How Many Ways to Solve this Problem? Generate a Calendar that Displays Average Hours Per Day

July 18, 2011 I am surprised at the significant number of unique solutions to the SQL problems that I have previously posed on this blog.  For fun I thought that I would give people another chance to demonstrate their unique approaches to solving another problem. Supposed that a transaction table exists with the following definition: CREATE [...]

Index Organized Tables – the Basics.

IOT2 – Examples and proofs..>
IOT3 – Greatly reducing IO with IOTs….>
IOT4 – Boosting Buffer Cache Efficiency……>

I think Index Organized Tables(IOTs) are a much under-used and yet very useful feature of Oracle. Over the next few postings I’m going to cover some aspect of Index Organised Tables, both good and not-so-good. I am going to cover some benefits of IOTs that I think many people are unaware of. In this first post I am just going to run through the basics of IOTs.

The idea behind an IOT is simple. You hold all the data for the table in the ordered structure of an index. Why would you want to do that? Let us consider a very common requirement, accessing a row in a “large” table via a known, unique key.

Traditionally you have a heap table holding the data you want to access and a standard index to support access to that table. See the first diagram below. The 4-layer triangle represents the index, with a root block, two levels of branch blocks and then the leaf blocks at the “bottom”. The blue rectangle represents the table with the squares being individual rows. Of course, in a large table there would be thousands or millions of “squares”, this is just a simple diagram to show the idea.

When you issue a SQL statement to select the row via the indexed column(s) then oracle will read the root block (1), find the relevent block in the first level of branch blocks (2), then the relevant block in the second level of branch blocks (3) and finally (as far as the index is concerned) the relevant Leaf Block for the unique key. The leaf block holds the indexed column(s) and also the rowid. The rowid is the fastest way to look up a record, it states the file, block and row offset for the row. This allows oracle to go straight to the block and get the row. That is read number (5).
The number of branch blocks {and thus the number of blocks that need to be read to find a row} will vary depending on how much data is indexed, the number and size of the columns in the index, how efficiently the space has been used in the blocks and one or two other factors. In my experience most indexes for tables with thousands or millions of rows have one, two or three levels of branch blocks.

The second diagram shows a representation of the Index Organized Table. The table has in effect disappeared as a distinct object and the information has been moved into the leaf blocks of the index {part of me feels Index Organized Tables should really be called Table Organized Indexes or Table Containing Indexes as that would better indicate what is physically done}:

So with the IOT oracle reads the root block (1), the two branch level blocks (2 and 3) and finally the leaf block (4). The leaf block does not hold the rowid but rather the rest of the columns for the table {this can be changed, a more advanced feature allows you to store some or all the extra columns in an overflow segment}. Thus to access the same data, Oracle has to read only 4 blocks, not 5. Using an IOT saves one block read per unique lookup.

This saving of block reads is probably the main feature that IOTs are known for, but there are others which I will cover in later posts. Two things I will mention now is that, firstly, the use of IOTs is potentially saving disc space. An index is in effect duplication of data held in the table. When you create an index no new information is created but space is used up holding some of the table information in a structure suitable for fast lookup. Secondly, the index and table have to be maintained whenever a change is made to the columns that are indexed. IOTs reduce this maintenance overhead as there is only one thing to maintain.

Now for some drawbacks.

  • The IOT has to be indexed on the primary key. There is no option to create an IOT based on other indexes. As such you have to either be accessing the table via the primary key to get the benefit – or you have to be a little cunning.
  • The index is going to be larger than it was and very often larger than the original table. This can slow down range scans or full scans of the index and a “full table scan” will now be a full index scan on this large object, so that can also negatively impact performance. However, if a range scan would then have resulted in access to the table to get extra columns, the IOT gives a similar benefit in reducing IO to that for single row lookups.
  • I just want to highlight that you now have no rowid for the rows.
  • Secondary indexes are supported but will potentially be less efficient due to this lack of rowid.

So, a brief summary is that Index Organised Tables effectively move the table data into the Primary Key index, reduce the number of block lookups needed to select one row, can save some disc space. But you can only organize the table via the Primary Key and it can make full or partial table scans and lookups via other indexes slower.

There are several more important benefits to IOTs {in my opinion} which I will come to over the next week or two.

VirtualBox 4.0.12 Released…

VirtualBox 4.0.12 has arrived. It’s another maintenance released with a bunch of bug fixes. You can see what’s changed here. Happy upgrading. :)

Cheers

Tim…




Embarcadero Performance Panel

Karen Morton, Cary MIllsap and I will be participating in a on-line panel discussion about Oracle Performance on July 28th. Since we all worked together in the past we thought it would be a fun to listen to each other answer questions. Embarcadero is sponsoring this event and invited us to participate. Here’s a graphic from the mailer they sent out.

I only point it out because Cary and Karen look like they are posing for a picture, while I, as usual, look like someone just poured a drink down my pants. That’s normal though. I’ve been told I have a great face for radio.

You can sign up here: Register Now!

Test HTML

Upside Down Text

& nbsp;

Big Text

ipmitool sunoem cli ‘show /SP system_identifier’
Deep and Wide

Highlight Text:

Normal text color different text color normal text color different background color
Here’s a numbered list with blank lines between items:

  1. create a copy of the statement in a test script
  2.  

  3. add the GATHER_PLAN_STATISTICS hint
  4.  

  5. duplicate all the bind variables (with values used at parse if possible)
  6.  

  7. run it
  8.  

  9. run xplan on it

Another Section with Bullets:

  • first item
  • second item
  • third item
CAPTION_WHICH COULD BE
LONG

Video:

The Hit

The Hit

Your browser does not support iframes.

EXAMPLE

Test Table
DODA DODA2
OBOY OBOY2