Pakistan's First Oracle Blog
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.
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.
Categories: DBA Blogs
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
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
Categories: DBA Blogs
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 have an irregular distribution, and where the column is involved in WHERE or JOIN expressions.
CREATING HISTOGRAMS WITH METHOD_OPT
The METHOD_OPT option controls how column level statistics, in particular histograms, are created. The default value is ‘FOR ALL COLUMNS SIZE AUTO’,
which enables Oracle to choose the columns that will have a histogram collected and set the appropriate histogram bucket size.
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 have an irregular distribution, and where the column is involved in WHERE or JOIN expressions.
CREATING HISTOGRAMS WITH METHOD_OPT
The METHOD_OPT option controls how column level statistics, in particular histograms, are created. The default value is ‘FOR ALL COLUMNS SIZE AUTO’,
which enables Oracle to choose the columns that will have a histogram collected and set the appropriate histogram bucket size.
Categories: DBA Blogs
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.
So who is DBA leader? The obvious answer is the person who leads a team of database administrators. Sounds simple enough, but it takes a lot to be a true leader. There are many DBA bosses at various layers, DBA managers at various layers, but being a DBA leader is very different. If you are a DBA leader, then you should be kinda worshiped. If you work in a team which has a DBA leader, then you are a very lucky person.
A DBA leader is the one who leads by an example. He walks the talk. He is the doer and not just talker. He inspires, motivates, and energizes the team members to follow him and then exceed his example. For instance, when client asks to improve that performance issue in the RAC cluster, the DBA leader would first jump in at the problem and start collaborating with team. He would analyze the problem, would present his potential solutions or at least line of action. He would engage the team and would listen to them. He won't just assing the problem to somebody, then disappear, and come back at 5pm asking about status. DBA leader is not super human, so he will get problems of which he won't have any knowledge. He will research the the problem with team and will learn and grow with them. That approach would electrify the team.
A DBA leader is a grateful person. He doesn't seem to thank his team enough for doing a great job. When under the able leadership of the DBA leader, team would reach to a solution, then regardless of his contribution, a DBA leader would make his team look awesome. That will generate immense prestige for the DBA leader at the same time, while making team looking great. Team would cherish the fact that solution was reached after deep insights of the DBA leader, and yet leader gave credit to them.
A DBA leader is the one who is always there. He falls before the team falls, and doesn't become aloof when things don't go well. Things will go wrong and crisis will come. In such situations, responsibility is shared and DBA leader doesn't shirk from it. In the team of DBA leader, there are no scapegoats.
A leader of DBAs keeps both big piture and ther details in perspective at the same time. He provides the vision and lives the vision from the front. He learns and then he leads. He does all of this and does it superbly and that is why he is the star and such a rare commodity, and that is why he he is the DBA LEADER.
So who is DBA leader? The obvious answer is the person who leads a team of database administrators. Sounds simple enough, but it takes a lot to be a true leader. There are many DBA bosses at various layers, DBA managers at various layers, but being a DBA leader is very different. If you are a DBA leader, then you should be kinda worshiped. If you work in a team which has a DBA leader, then you are a very lucky person.
A DBA leader is the one who leads by an example. He walks the talk. He is the doer and not just talker. He inspires, motivates, and energizes the team members to follow him and then exceed his example. For instance, when client asks to improve that performance issue in the RAC cluster, the DBA leader would first jump in at the problem and start collaborating with team. He would analyze the problem, would present his potential solutions or at least line of action. He would engage the team and would listen to them. He won't just assing the problem to somebody, then disappear, and come back at 5pm asking about status. DBA leader is not super human, so he will get problems of which he won't have any knowledge. He will research the the problem with team and will learn and grow with them. That approach would electrify the team.
A DBA leader is a grateful person. He doesn't seem to thank his team enough for doing a great job. When under the able leadership of the DBA leader, team would reach to a solution, then regardless of his contribution, a DBA leader would make his team look awesome. That will generate immense prestige for the DBA leader at the same time, while making team looking great. Team would cherish the fact that solution was reached after deep insights of the DBA leader, and yet leader gave credit to them.
A DBA leader is the one who is always there. He falls before the team falls, and doesn't become aloof when things don't go well. Things will go wrong and crisis will come. In such situations, responsibility is shared and DBA leader doesn't shirk from it. In the team of DBA leader, there are no scapegoats.
A leader of DBAs keeps both big piture and ther details in perspective at the same time. He provides the vision and lives the vision from the front. He learns and then he leads. He does all of this and does it superbly and that is why he is the star and such a rare commodity, and that is why he he is the DBA LEADER.
Categories: DBA Blogs
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;
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;
Categories: DBA Blogs
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 Grid Infrastructure to 12.1.0.2 and applied the Oct 2014 PSU. Had an error during rootupgrade.sh as well, due to the ASM spfile being on disk instead of on ASM diskgroup.
If you (already) created your first Oracle Service Bus 12c application/project with SOAP webservices and tried to deploy it to your IntegratedWeblogic server you might be familiar with this error.
Using Drag-Drop functionality in af:treeTable to move data between nodes.
SQL Server:
Hadoop has been making a lot of noise in the Big Data world.
Lets look at two different ways of creating an HDInsight Cluster: Creating an HDInsight Cluster through Azure Management Portal, and creating an HDInsight Cluster through Windows Azure PowerShell.
Why you need test driven development.
SQL Server Data Import System to Alert For Missed Imports.
Create stunning visualizations with Power View in 20 minutes or less!
MySQL:
So assume you just uploaded the certificate you use to identify yourself to the MySQL server to Github or some other place it doesn’t belong…and there is no undelete.
MySQL Plugin for Oracle Enterprise Manager on VirtualBox: installation gotchas.
MariaDB slave restore using GTID & xtrabackup bug.
How small changes impact complex systems – MySQL example.
In this post, Louis talk about MHA GTID behavior, we test different cases and find something is different from previous versions.
Also Published at Pythian Blog.
Categories: DBA Blogs
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.
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.
Categories: DBA Blogs
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.
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:
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.
Categories: DBA Blogs