SQL Tuning(World Scenario)
September 19, 2011 Leave a comment
SQL Tuning is very big and difficult subject. Each SQL Tuning scenario is unique.
To solve the problems easily, you need a lot of experience.
I decided to write every SQL Tuning Scenario which I have had.
So, let’s start:
Look at this SQL:
SELECT distinct col1, col2, col3, col4FROM HR.Table1
where col2 = 040533424
and col1 !=
‘Mariam’
order by col4 desc
It’s SQL plan is the following:
PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 269539686 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 134 | 2625 (5)| 00:00:1 | 1 | SORT UNIQUE | | 2 | 134 | 2624 (5)| 00:00:1 |* 2 | TABLE ACCESS FULL| TABLE1 | 2 | 134 | 2623 (5)| 00:00:1 --------------------------------------------------------------------------------
This table has index named col2_indx on col2 column.But in our plan it is not used, why??
Now let’s drill down:
SQL> desc HR.TABLE1 Name Type Nullable Default Comments ------ ------------ -------- ------- -------- col1 VARCHAR2(15) Y
col2 VARCHAR2(30) Y
col3 VARCHAR2(50) Y col4 VARCHAR2(30) Y
As you can see col2 type is varchar2… But in our SQl statement value of this column is equal to number type…
So Oracle is trying to convert number type to varchar2 type and at this time looses the ability to use that index.
If you follow my posts on that blog, you may remember the post about FUNCTION BASED INDEXES. There is the following scenario.
If you have index on some column and in select you are using this column like that to_char(col2) or with some other function… Index on that column is missed. You can have function based index on to_char(col2) combination…
Now, let’s rewrite our SQL:
SELECT distinct col1, col2, col3, col4FROM HR.Table1
where col2 = ‘040533424’
and col1 !=
‘Mariam’
order by col4 desc
Plan for this statement:
PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 4243002513 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CP -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 134 | 7 (2 | 1 | SORT ORDER BY | | 2 | 134 | 7 (2 | 2 | HASH UNIQUE | | 2 | 134 | 6 (1 |* 3 | TABLE ACCESS BY INDEX ROWID| TABLE1 | 2 | 134 | 5 (
|* 4 | INDEX RANGE SCAN | COL2_INDX | 2 | | 3 (
--------------------------------------------------------------------------------
And this select is faster than the previous.
I hope it was helpful for you…
About Mariami Kupatadze
DBA Knowledge Base 