Indexing Points to Remember

Indexing depends upon the queries in the application.

There is no one-size-fits-all break-even point for indexed versus table scan access. If only a few rows are being accessed, the index will be preferred.

If almost all the rows are being accessed, the full table scan will be preferred. In between these two extremes, your “mileage” will vary.

A concatenated index is more useful if it also supports queries where not all columns are specified. For (more...)

Log Buffer #406, A Carnival of the Vanities for DBAs

This Log Buffer Edition covers blog posts from various bloggers of Oracle, SQL Server and MySQL.

Oracle:

Sync tables: generate MERGE using Unique constraint.
What Hardware and Software Do YOU Want Oracle to Build?
There were a number of new features introduced in Ops Center 12.2.2. One of the shiny ones is an expansion of the backup and recovery capabilities to include Proxy Controllers.
Want to Be a Better Leader? Answer One Question.
(more...)

Histograms Tidbits

Make sure histograms exist on columns with uneven data distributions to ensure that the optimizer makes the best choice between indexes and table scans.




For range scans on data that is not uniformly distributed, the optimizers’ decisions will be improved by the presence of a histogram.

Histograms increase the accuracy of the optimizer’s cost calculations but increase the overhead of statistics collections. It’s usually worth creating histograms for columns where you believe the data will (more...)

Who is a DBA Leader?

Sitting behind a big mahogany table, smoking Cuban Cigar, glaring at the person sitting across, one hand taking the receive of black phone to right ear, and the other hand getting the mobile phone off the left ear can be the image of a DBA boss in any white elephant government outfit, but it certainly cannot work in organization made up of professionals like database administrators. And if such image or similar image is working (more...)

Script for Exadata I/O Report

select function_name,sum(SMALL_READ_MEGABYTES)/1024 SM_Read_GB,
sum(SMALL_WRITE_MEGABYTES)/1024 SM_Write_GB,
sum(LARGE_READ_MEGABYTES)/1024 LG_Read_GB, sum(LARGE_WRITE_MEGABYTES)/1024 LG_Write_GB,
sum(LARGE_READ_REQS) LG_Read_Requests,
sum(LARGE_Write_REQS) LG_Write_Requests
from v$iostat_function_detail
group by function_name;

Log Buffer #404, A Carnival of the Vanities for DBAs


With new year already in fast gear, bloggers are sparing no stone unturned to come up with innovative ideas. This Log Buffer edition is keeping pace with them as always.

Oracle:

While playing with 12c Scott tried the upgrade to the DEFAULT column syntax that now allows sequences.
This is an age old question and of course the answer depends on how you say “SQL”.
Happy New Year! Upgraded 12.1.0.1 (more...)

Log Buffer #402, A Carnival of the Vanities for DBAs

This Log Buffer edition hits the ball out of park by smashing yet another record of surfacing with a unique collection of blog posts from various database technologies. Enjoy!!!

Oracle:

EM12c and the Optimizer Statistics Console.
SUCCESS and FAILURE Columns in DBA_STMT_AUDIT_OPTS.
OBIEE and ODI on Hadoop : Next-Generation Initiatives To Improve Hive Performance.
Oracle 12.1.0.2 Bundle Patching.
Performance Issues with the Sequence NEXTVAL Call.

SQL Server:

GUIDs GUIDs everywhere, but how (more...)

NZOUG14 Beckons

New Zealand is famous for Kiwis, pristine landscape, and New Zealand Oracle User Group (NZOUG) conference.  The location of choice is New Zealand when it comes to making Lord of the Rings and making Oracle Lord of the Databases.


NZOUG 2014 will be held 19–21 November in the Owen G. Glenn Building at the University of Auckland. The main conference will be held on the 20th and 21st, preceded by a day of workshops (more...)

RMAN-07518: Reason: Foreign database file DBID

While cloning a database to another system through RMAN in an 11.2 database on a Linux system, there was a need to catalog the backups after putting the destination instance in mount status, before the restore/recover process could be started.





During the phase of cataloging, got the following error:

RMAN-07518: Reason: Foreign database file DBID

Upon checking, it was learned that there was a mismatch of database incarnation at the source and the destination (more...)

My Session Evaluation from Collaborate14 in Las Vegas Arrived :)

Dear Fahd Chughtai,

Thank you for presenting at COLLABORATE 14 – IOUG Forum in Las Vegas, Nevada. We hope that you will consider presenting at future IOUG events. You can view your evaluation results for the following session below: 194-12c Multi-Tenancy and Exadata IORM: An Ideal Cloud Based Resource Management

Note: Session evaluations were based on a 1-5 scoring scale. (1 being the lowest and 5 being the highest)

Primary speaker was knowledgeable (more...)

From Las Vegas to Ottawa

After a very engaging session at Collaborate14 in sunny Las Vegas amidst the desert of Nevada, I just arrived in not-so-bitterly cold Ottawa, the capital of Canada. Looking forward meeting with various Pythian colleagues and hanging out with the friends I cherish most.

My Exadata IORM session went well. Lots of follow back discussion plus questions are still pouring in. I promise I will answer them as soon as I return to Australia after couple (more...)

DBRM for 12c Container Database in Multi-tenant Environment

In multi-tenant environment, Database Resource Manager (DBRM), at the Container Database (CDB) level enables us to manage the resources like CPU usage and number of parallel execution servers among the plugabble databases (PDBs). Then within each PDB, DBRM enables us to manage resources like CPU, parallelism and managing the runaway queries which exceed the certain thresholds of CPU, physical IO, logical IO or elapsed time.

In 12c version of Oracle database, at the CDB level, (more...)

Speaking at Collaborate14 in Las Vegas

12c Multi-Tenancy and Exadata IORM: An Ideal Cloud Based Resource Management



Cloud database enables administrators to scale up and down the resource usage according to the business requirements. Oracle 12c renders multi-tenancy to manage multiple pluggable databases within a multi-tenant container database. In an Exadata, at the compute nodes, the resource manager controls the resources among pluggable databases, and all this resource management is trickled down to the storage servers, where IORM controls the resources. (more...)

IORM Architecture in Exadata




From the database notes, the I/O requests are sent to the cell nodes. These requests go through the intelligent iDB protocol containing information pieces like database name, category name, consumer group etc. These I/O requests are placed (more...)

Most Simplest Example of Big Data Ever

When I have to travel by air to somewhere, I go to a specific website to search and book my flight. Every time I visit that website, I encounter the same experience. I have to go through the same steps according to my same preferences, every time I need to book the flight.

Here is what I always do to book my flight at that website on each visit:

  1. Sign in with my userid
  2. Type (more...)

My Article on SearchOracle about Exadata Administration

The massive explosion in data, combined with an ever-increasing hunger to boost processing speeds while keeping costs low, has led to engineered systems in data centers.

Read More

Attended UKOUG Tech13 and Oaktable World UK

In cold, yet dry Manchester’s winter, it was quite thermal to simultaneously relish the meritorious UKOUG Tech13 and elite Oaktable World UK 2013 conferences. Pythian’s presence was overwhelming at both congresses. Paul Vallee, Alex Gorbachev, Elliot Zissman, Luke Davies, Paul Logan, Maris Elsins, Marc Fielding, Christo Kutrovsky and Michael McKee (more...)

Off to UKOUG Manchester UK

I am reserved speaker at the UKOUG this year in Manchester and will be traveling almost full length of globe to reach Manchester next week from Australia. Looking forward for technology-packed, fun-infested event. Pythian will be there again in full force too.

Will keep you posted from there :)

SQL_ID in Auto Trace Explain Plan Only

It would be a great enhancement to the nifty and very helpful set autotrace explain plan only statement if it showed the SQL_ID of the query in its output. Currently it shows the SQL Text and the PLAN_HASH_VALUE, but not SQL_ID.

May be an enhancement in Oracle 12cR2 :)

To Get Serial Number of Exadata Rack or Individual Server in Exadata

For the whole Exadata rack serial number:

ipmitool sunoem cli 'show /SP system_identifier'

For the server serial number you are connected to:

ipmitool sunoem cli "show /SYS product_serial_number"

For each component serial number on a single server:

You can also get the individual serial numbers for each component in an (more...)