Monday, January 26, 2015

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 instance SURNAME, FIRSTNAME is more useful than FIRSTNAME, SURNAME because queries against SURNAME only are more likely to occur than queries against FIRSTNAME only.

Global indexes provide better performance for queries that must span all partitions.

Sunday, January 18, 2015

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.
Managing a remote Oracle Database instance with “Geographic Edition”.

SQL Server:

Learn how you can use SQLCop to prevent your developers from writing stored procedures that are named sp_ something.
Data Cleaning in SQL 2012 with Data Quality Services.
Stairway to PowerPivot and DAX - Level 9: Function / Iterator Function Pairs: The DAX MAX() and MAXX() Functions.
Options to Improve SQL Server Bulk Load Performance.
Dynamically Create Tables Based on an Access Table

MySQL:

Stored Procedures: critiques and defences.
JSON UDF functions 0.3.3 have been released.
Business Scalability, Operational Efficiency and Competitive Edge with MariaDB MaxScale 1.0 GA.
MySQL 5.7 labs and the HTTP Plugin – inserting, updating and deleting records in MySQL via HTTP.
Hyper-threading – how does it double CPU throughput?

Published on Pythian Blog

Friday, January 9, 2015

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.


Tuesday, January 6, 2015

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 in any such company then that company is not great. It's as simple as that.





Sunday, January 4, 2015

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.

Saturday, December 20, 2014

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 is my data unique?
Questions About T-SQL Transaction Isolation Levels You Were Too Shy to Ask.
Introduction to Advanced Transact SQL Stairway and Using the CROSS JOIN Operator.
Introduction to Azure SQL Database Scalability.
What To Do When the Import and Export Wizard Fails.

MySQL:

Orchestrator 1.2.9 GA released.
Making HAProxy 1.5 replication lag aware in MySQL.
Monitor MySQL Performance Interactively With VividCortex.
InnoDB’s multi-versioning handling can be Achilles’ heel.
Memory summary tables in Performance Schema in MySQL 5.7.

Also published here.

Thursday, October 16, 2014

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 on the 19th. It's one of the premier Oracle conferences in Southern hemisphere.

Where there is Oracle, there is Pythian. Pythian will be present in full force in NZOUG 2014.

Following are Pythian sessions at NZOUG14:

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

Everyone Talks About DR – But Why So Few Implement It
Francisco Munoz Alvarez

DBA 101: Calling All New Database Administrators
Gustavo Rene Antunez

My First 100 Days with an Exadata
Gustavo Rene Antunez

Do You Really Know the Index Structures?
Deiby Gómez

Oracle Exadata: Storage Indexes vs Conventional Indexes
Deiby Gómez

Oracle 12c Test Drive
Francisco Munoz Alvarez

Why Use OVM for Oracle Database
Francisco Munoz Alvarez
Please check the full agenda of NZOUG14 here.

Wednesday, June 4, 2014

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.


Sunday, May 25, 2014

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 about the material
The primary speaker's presentation skills were satisfactory
The slides and materials presented in the session were satisfactory
The agenda abstract and objectives were accurate to the session presentation
I learned a lot of new information in this session.
I can apply what I learned in this session to my job
Overall, how would you rate this session?
4.33
4.33
4.67
4
4
4.33
4.33