HOW TO CHECK COST OF YOUR SQL QUERY ON CPU and PROCESSING TIME
IF YOUR QUERY IS TAKING TOO MUCH TIME FOR OUTPUTING THE RESULT THEN SOMETHING IS WRONG...EITHER THE QUERY IS NOT OPTIMIZED OR SAY THE INDEX ARE NOT SET ON THE CROSSPONDING TABLES COLUMNS...ETC..
THUS YOU NEED TO FIGURE IT OUT ??????????????????????????
Its quite simple in oracle ..THE EXPLAIN Command help us to fid the cost of processing the query.
this need to be Done on cmd .lets see how?
1. first you need to log on to the database you are using using the credentials,then enter the following command followeded by your query-
SQL> Explain plan
for select a.rain-b.rain as "02:30:00+05:30" from
rainfall_2010 a,rainfall_2010 b
where a.raingauge=452 and
b.raingauge=452 and a.raindate=to_date('03-07-2010','dd-MM-yyyy' ) and b.rainda
te=to_date('03-07-2010','dd-MM-yyyy' ) and a.raintime= ('02:30:00+05:30') and
b.raintime=('01:30:00+05:30');
Explained.
SQL> set long 999999999
SQL> set lines 3000
SQL> @?/rdbms/admin/utlxpls.sql
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | | 1 |
60 | 29289 (2)|
| 1 | MERGE JOIN CARTESIAN| | 1 |
60 | 29289 (2)|
| 2 | INDEX SKIP SCAN | INDX_RAINFALL | 1 |
30 | 361 (0)|
| 3 | BUFFER
SORT | | 1 |
30 | 28928 (2)|
| 4 | TABLE ACCESS FULL | RAINFALL_2010 | 1 |
30 | 28928 (2)|
---------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
Thus here you can see that we get all the operation that are being performed when the query executes .
here we can simply figure it out that TABLE ACCESS FULL | RAINFALL_2010 is done hence the cost is high .
this is due to non availblity of index on the table.
after correcting the necessary things when we again executed the plan then we got much better cost.
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------
| 0 | SELECT
STATEMENT | | 1 |
60 | 722 (1)|
| 1 | HASH JOIN | | 1 |
60 | 722 (1)|
| 2 | INDEX SKIP SCAN| INDX_RAINFALL | 1 |
30 | 361 (0)|
| 3 | INDEX SKIP SCAN| INDX_RAINFALL | 1 | 30 | 361 (0)|-----------------------------------------------------------------------
THus it help you to analyze your query and take necessary action.
To see WHAT IS INDEX ,WHY TO PUT INDEX,HOW TO PUT INDEX VISIT THE ARTICLE:
No comments :
Post a Comment