This is my first blog.
Based on your feedback , I will be updating this blog , post new blogs .Thanks for looking into this .I started my career as a PowerBuilder / Sybase or Oracle Developer ; later on transitioned into DB Administration .
Now, I have seen in my places, where the programmers were given training in / asked to learn in Java / .NET technologies but they forget to get trained on database fundamentals.They treat the database as data storage. If they read through the basics of db development , I am sure it will benefit everybody.
There are few things a developer can do. Though the examples given below for Oracle. It is equally applicable for SQL Server, DB2, Sybase, so on.
Avoid Literals. Use Bind variables in an OLTP system
By using bind variables
1) shared pool is efficiently used .
2) SQL injection can be avoided .
Let us say , if you issue the following statements , you will see two versions of theSQL in v$sql . Every statement has to hard parsed , ….
SELECT object_name into obj_name from t where object_name = 'T';
SELECT object_name into obj_name from t where object_name = 'ICOL$';
Every statement has to hard parsed , ….Some shops overcome this by setting the variable CURSOR_SHARING to either SIMILAR or FORCE . But this has to be short term fix.
This topic could alone run into a separate volume.
Use UNION ALL , instead of UNION if applicable
If your logic could support distinct values or if you are sure your query retrieves distinct values , use UNION ALL.
When you use UNION ALL , you can cut down on the sorting step .
Explain Plan for
Select object_name
from t
Where object_type = 'TABLE'
union
Select object_name
from t
Where object_type = 'PROCEDURE';
----------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
----------------------------------------------------------------------------
0 SELECT STATEMENT 3559 99652 321 (51) 00:00:04
1 SORT UNIQUE 3559 99652 321 (51) 00:00:04
2 UNION-ALL
* 3 TABLE ACCESS FULL T 3491 97748 159 (1) 00:00:02
* 4 TABLE ACCESS FULL T 68 1904 159 (1) 00:00:02
----------------------------------------------------------------------------
Explain Plan for
Select object_name
from t
Where object_type = 'TABLE'
union all
Select object_name
from t
Where object_type = 'PROCEDURE';
Plan hash value: 1078996545
---------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
---------------------------------------------------------------------------
0 SELECT STATEMENT 3559 99652 319 (51) 00:00:04
1 UNION-ALL
* 2 TABLE ACCESS FULL T 3491 97748 159 (1) 00:00:02
* 3 TABLE ACCESS FULL T 68 1904 159 (1) 00:00:02
---------------------------------------------------------------------------
Avoid DISTINCT , if you can.
Again If your logic could avoid DISTINCT , please do so.
I have seen in my work assignment , some of the developers worked on the "side effects of DISTINCT" . Prior to 10g Release 2 , Oracle was doing SORT JOIN , so the results were ordered . Not any more ... Starting from Oracle 10g Release 2 , Oracle does HASH JOIN ( avoiding sort is good for performance reason ) . If you want the results to be sorted , use the ORDER BY clause . Otherwise , there is no gurantee , your results would be ordered.
Explain Plan for
Select object_name from t;
--------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
--------------------------------------------------------------------------
0 SELECT STATEMENT 50433 837K 159 (1) 00:00:02
1 TABLE ACCESS FULL T 50433 837K 159 (1) 00:00:02
--------------------------------------------------------------------------
Explain Plan for
Select distinct object_name from t;
-----------------------------------------------------------------------------------
Id Operation Name Rows Bytes TempSpc Cost (%CPU) Time
-----------------------------------------------------------------------------------
0 SELECT STATEMENT 50433 837K 443 (2) 00:00:06
1 HASH UNIQUE 50433 837K 2392K 443 (2) 00:00:06
2 TABLE ACCESS FULL T 50433 837K 159 (1) 00:00:02
l-----------------------------------------------------------------------------------
Avoid ORDER BY , if you can.
If you could avoid ORDER BY , please do so . Quick execution plan will show the additional overhead in ordering
Explain Plan for
Select object_name from t
order by object_name;
-----------------------------------------------------------------------------------
Id Operation Name Rows Bytes TempSpc Cost (%CPU) Time
-----------------------------------------------------------------------------------
0 SELECT STATEMENT 50433 837K 443 (2) 00:00:06
1 SORT ORDER BY 50433 837K 2392K 443 (2) 00:00:06
2 TABLE ACCESS FULL T 50433 837K 159 (1) 00:00:02
-----------------------------------------------------------------------------------
Based on your feedback , I will be updating this blog , post new blogs .Thanks for looking into this .I started my career as a PowerBuilder / Sybase or Oracle Developer ; later on transitioned into DB Administration .
Now, I have seen in my places, where the programmers were given training in / asked to learn in Java / .NET technologies but they forget to get trained on database fundamentals.They treat the database as data storage. If they read through the basics of db development , I am sure it will benefit everybody.
There are few things a developer can do. Though the examples given below for Oracle. It is equally applicable for SQL Server, DB2, Sybase, so on.
Avoid Literals. Use Bind variables in an OLTP system
By using bind variables
1) shared pool is efficiently used .
2) SQL injection can be avoided .
Let us say , if you issue the following statements , you will see two versions of theSQL in v$sql . Every statement has to hard parsed , ….
SELECT object_name into obj_name from t where object_name = 'T';
SELECT object_name into obj_name from t where object_name = 'ICOL$';
Every statement has to hard parsed , ….Some shops overcome this by setting the variable CURSOR_SHARING to either SIMILAR or FORCE . But this has to be short term fix.
This topic could alone run into a separate volume.
Use UNION ALL , instead of UNION if applicable
If your logic could support distinct values or if you are sure your query retrieves distinct values , use UNION ALL.
When you use UNION ALL , you can cut down on the sorting step .
Explain Plan for
Select object_name
from t
Where object_type = 'TABLE'
union
Select object_name
from t
Where object_type = 'PROCEDURE';
----------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
----------------------------------------------------------------------------
0 SELECT STATEMENT 3559 99652 321 (51) 00:00:04
1 SORT UNIQUE 3559 99652 321 (51) 00:00:04
2 UNION-ALL
* 3 TABLE ACCESS FULL T 3491 97748 159 (1) 00:00:02
* 4 TABLE ACCESS FULL T 68 1904 159 (1) 00:00:02
----------------------------------------------------------------------------
Explain Plan for
Select object_name
from t
Where object_type = 'TABLE'
union all
Select object_name
from t
Where object_type = 'PROCEDURE';
Plan hash value: 1078996545
---------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
---------------------------------------------------------------------------
0 SELECT STATEMENT 3559 99652 319 (51) 00:00:04
1 UNION-ALL
* 2 TABLE ACCESS FULL T 3491 97748 159 (1) 00:00:02
* 3 TABLE ACCESS FULL T 68 1904 159 (1) 00:00:02
---------------------------------------------------------------------------
Avoid DISTINCT , if you can.
Again If your logic could avoid DISTINCT , please do so.
I have seen in my work assignment , some of the developers worked on the "side effects of DISTINCT" . Prior to 10g Release 2 , Oracle was doing SORT JOIN , so the results were ordered . Not any more ... Starting from Oracle 10g Release 2 , Oracle does HASH JOIN ( avoiding sort is good for performance reason ) . If you want the results to be sorted , use the ORDER BY clause . Otherwise , there is no gurantee , your results would be ordered.
Explain Plan for
Select object_name from t;
--------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
--------------------------------------------------------------------------
0 SELECT STATEMENT 50433 837K 159 (1) 00:00:02
1 TABLE ACCESS FULL T 50433 837K 159 (1) 00:00:02
--------------------------------------------------------------------------
Explain Plan for
Select distinct object_name from t;
-----------------------------------------------------------------------------------
Id Operation Name Rows Bytes TempSpc Cost (%CPU) Time
-----------------------------------------------------------------------------------
0 SELECT STATEMENT 50433 837K 443 (2) 00:00:06
1 HASH UNIQUE 50433 837K 2392K 443 (2) 00:00:06
2 TABLE ACCESS FULL T 50433 837K 159 (1) 00:00:02
l-----------------------------------------------------------------------------------
Avoid ORDER BY , if you can.
If you could avoid ORDER BY , please do so . Quick execution plan will show the additional overhead in ordering
Explain Plan for
Select object_name from t
order by object_name;
-----------------------------------------------------------------------------------
Id Operation Name Rows Bytes TempSpc Cost (%CPU) Time
-----------------------------------------------------------------------------------
0 SELECT STATEMENT 50433 837K 443 (2) 00:00:06
1 SORT ORDER BY 50433 837K 2392K 443 (2) 00:00:06
2 TABLE ACCESS FULL T 50433 837K 159 (1) 00:00:02
-----------------------------------------------------------------------------------
