Search

Top 60 Oracle Blogs

Recent comments

July 2013

Install the Oracle Client on a Synology DiskStation DS1813+ or DS412+ for Nagios to Monitor Databases

July 26, 2013 (Back to the Previous Post in the Series) (Forward to the Next Post in the Series) In the previous article of this series I provided steps to install and run the Nagios network monitoring utility on either a Synology DiskStation DS1813+ or a DS412+ for the purpose of pinging network devices to verify […]

Analyzing IO at the Exadata Cell level… iostat summary

While analyzing Write-Back cache activity on Exadata storage cells, I wanted something to interactively monitor IO while I was running various tests.  The problem is summarizing the results from ALL storage cell.  So, I decided to use my old friend "iostat" and a quick easy script to roll up the results for both DISK and FLASH.  This allowed me to monitor the IOPS, IO size, wait times, and service times.  

The "iostat-all.sh" tool shows the following data:

Analyzing IO at the Exadata Cell level… iostat summary

While analyzing Write-Back cache activity on Exadata storage cells, I wanted something to interactively monitor IO while I was running various tests.  The problem is summarizing the results from ALL storage cell.  So, I decided to use my old friend “iostat” and a quick easy script to roll up the results for both DISK and FLASH.  This allowed me to monitor the IOPS, IO size, wait times, and service times.  

The “iostat-all.sh” tool shows the following data:

How big will my index be ?

You’ve got a big table…you want to add an index.  Will you have enough space ?

Well, its not perfect but you can a reasonable approximation using DBMS_SPACE

SQL> drop table T purge;

Table dropped.

SQL> create table T nologging as
  2  select d.* from dba_objects d,
  3  ( select 1 from dual connect by level <= 100 )
  4  /

Table created.

SQL> select num_rows from user_tables where table_name = 'T';

  NUM_ROWS
----------


SQL> set serverout on
SQL> declare
  2    x int;
  3    y int;
  4  begin
  5    dbms_space.CREATE_INDEX_COST(
  6       'create index ix on T ( owner, object_type, object_id)'
  7       ,used_bytes=>x
  8       ,alloc_bytes=>y);
  9    dbms_output.put_line(x);
 10    dbms_output.put_line(y);
 11  end;
 12  /
366481370
528482304

PL/SQL procedure successfully completed.

So not statistics, the estimate is about 500MB. Now we calculate stats

Global Index Maintenance – Pre 12c (Unwashed and Somewhat Slightly Dazed)

Before I discuss another Oracle Database 12c new feature, Asynchronous Global Index Maintenance, thought it might be worthwhile discussing how Global Indexes were handled prior to 12c. I’ll begin by creating and populating a simple range partitioned table: I’ll now create two global indexes, one non-partitioned, the other partitioned: So we currently have two happy […]

Enkitec E4 2013

Just a quick note that I'll be presenting at this year's Enkitec E4 conference. You can find the schedule here. I did some under the hood investigation regarding how the whole DBFS stack works from the performance perspective and, needless to say, some findings simply left me startled. If you want to see a session which will forever change the way you look at DBFS then this will definitely be the one to attend.

Parallel to Serial

Here’s a little problem that came up on the Oracle-L listserver today:

I’m trying to write a query which reads the corresponding partition of the fact, extracts the list of join keys, materialises this result set, and finally joins the necessary dimensions. The key thing I’m trying to do is to run the initial query on the fact in parallel and then the rest of the query serially.

My day with the Ohio Oracle Users Group

Thanks so much to everyone who attended the Ohio Oracle User's Group event with me on July 18. I really enjoyed the opportunity to be there. The group appears to be vibrant and growing and everyone involved made it a top-notch experience (for me at least!).

My topic for the day was "SQL Stuff You Should Know" and was a mash-up of lots of different bits and pieces of information about SQL Tuning and the Oracle Optimizer. Links to my presentation slides will be made available on the OOUG web site, but I thought I'd also provide them here.

Downloads:

Upgrading to Oracle Database 12c : First Steps…

I’ve taken my first tentative steps into upgrading to Oracle 12c.

This article is targeted at the type of information you are likely to need for the 12c OCP DBA exam. In reality, upgrades are too important to rely on a generic article like this. Every time I do a real upgrade I go back to the upgrade docs and work my way through them. That’s the only way to make sure you’ve not missed out an important step, specific to the features you are using.

My first impressions are:

Linear Decay

I’ve mentioned “linear decay” in several posts when explaining a problem that someone has seen with an execution path – but I’ve recently realised that I don’t have a post describing what it is and how it works – although it’s in Cost Based Oracle – Fundamentals, of course, if you want some detail – so here’s a brief introduction (based on simple stats with no histograms).