Blogs

SQL Tuning made easy in Oracle 10g

articles: 

“Find out how to use DBMS_SQLTUNE package to tune SQL Statements in Oracle 10g”

Have you ever been frustrated with SQL Tuning?
Did you get lost in the maze of /+ HINTS +/ and analysis paralysis?
Pre-10g you have to be either a SQL tuning expert or should have a license to expensive tuning tools available in the market to effectively tune SQL Statements. Not the case anymore.

In 10g you can use DBMS_SQLTUNE package to get tuning recommendations for SQL Statements.

Overview

Tuning of SQL Statements using DBMS_SQLTUNE involves following 4 steps:

Dynamic creation of procedures in PL/SQL

articles: 

Every PL/SQl devloper must have tried their hands with dynamic SQl. Its an indelible part of oracle.But its generally used when we dont know at compile time about the object on which we are to perform the task. Suppose we want to insert data into a table but we get the name of the table during the execution of the procedure, in those cases we would use dynamic SQl with the table name passed as bind variable. Dynamic Sql is even used to execute DDl commands inside a PL/SQl block.

Kevin Meade's picture

Point in Time (Nearest Neighbor Algorithms for SQL)

articles: 

Been doing a lot with Historical Perspective lately. This has caused me to think a bit about the different variations of lookup that can be called a Point In Time query. My customers have found this discussion useful in understanding the details of how their report programs find specific rows in time. It is helpful to them because it provides an understanding as to why a specific row shows up on a report, and thus allows them to create more exacting definitions of what they want. Plus I find it is a good primer for newbies on staff to read so they don't make the same mistakes we made when we first started doing PIT queries. Maybe you can use it too.

How to kill RAC using the db_files parameter?

articles: 

Once upon a time Le Petit Chaperon Rouge (LPCR) decided to change db_files from 200 to 300 on a busy cluster of four nodes.

Soon after the change applications started to block each other, timeout and crash (enq: TX - contention, cursor: pin S wait on X, enq: HW - contention). Those applications which were actively inserting into LOB columns - suffered the most.

Many lock holders were waiting for "DFS lock handle". Thus LPCR learned that DFS stands for "Distributed File System".

As soon as she reversed the changes - everything went back to normal.

Moral:

Kevin Meade's picture

Difference between two rows

articles: 

One of my recent clients had a real data twisting process to validate. It didn't help much that their source had been corrected by many "ONE-SHOTS" over the years to deal with bugs, and accounting methods mandated by Act of Congress. What we needed was a way to see changes in a stream of related rows. In the end I created for them, a solution that allowed developers to pick two rows from a table at random and compare them in SQL with a result set returned showing only differences. Its a simple thing, but kind of neat, and very useful. So I'd like to share it with you.

Kevin Meade's picture

Two of my Favorite Index Tuning Techniques

articles: 

Recently my friends at work saw me doing things with indexes they didn't understand. They saw what looked like unneeded columns in some indexes, and funny expressions they didn't know could be done in an index. So I explained to them the benefits of INDEX ONLY ACCESS, and FUNCTION BASED INDEXES. They had not seen these two techniques before so I figured maybe other people would be interested too. Here is it, a short discussion on what are fast becoming my two favorite tuning techniques.

saibal's picture

Shared Server -- Part III

articles: 

To continue with the shared server series, as mentioned in my last post, I will here be taking up the issues of configuration of memory, and the management of shared servers using available data dictionary views and initialization parameters.
How much memory should you configure for shared servers?

saibal's picture

Shared Server -- Part II

articles: 

So, to continue with our shared server series, let's begin with how a user connects through a shared server connection:

saibal's picture

Shared Server -- Part I

articles: 

This is the first of my three-part series dealing with Shared servers: Today an organization can have hundreds or thousands of users connecting to their databases. The traditional dedicated server model maps each user to a dedicated process running on the database server. Each dedicated server process requires memory and as the number of users increase, there is a linear increase in the demand for memory.

Tuning "BETWEEN" Queries

articles: 

A common sight in databases is a table that contains the start and end values of a range - usually dates. One or both of the dates are typically part of the primary key; sometimes they are the entire key. Some examples:

  • History tables History tables record the changes made to a row over time. Instead of updating a row and losing the prior values of each non-key attribute, a new row in inserted. Each row is differentiated by two columns that record the date the row was created (START_DATE) and the date it was superceded (END_DATE).

Pages

Subscribe to RSS - blogs