SQL Query performance..
rsar001Dec 11 2009 — edited Dec 15 2009Hi There,
I have a SQL query that runs on the database (Oracle 10.2.0.3 std ed. - Windows 2003 server) as part of a daily process.. I noticed that the process took few hrs to complete today as appose to the normal 40+ mins.
The query is as follows:
TRUNCATE TABLE my_table;
SQL> INSERT into my_table (fld1, fld2, fld3, fld4)
2 (select GET_NUM_REC(dd.FLD3,dd.FLD4,'PART') fld1,
3 GET_NUM_RECS_BY_PART(dd.FLD3,dd.FLD4,'SEC41') fld2,
4 dd.fld3,
5 dd.fld4
6 from (select distinct
7 ed.fld3,
8 ev.fld4
9 from tableX ed,
10 tableY ev) dd);
I analyzed the underlying table in the 2 functions above GET_NUM_REC and GET_NUM_RECS_BY_PART.. I also noticed that optimizer_index_cost_adj was set to 100 for this particular database and so I changed it to 10 with:
alter system set optimizer_index_cost_adj=10 scope=both;
the over all execution plan for the above was:
SQL>
SQL> @?/rdbms/admin/utlxpls.sql
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1633339876
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1999K| 49M| | 6759 (2)| 00:01:35 |
| 1 | VIEW | | 1999K| 49M| | 6759 (2)| 00:01:35 |
| 2 | HASH UNIQUE | | 1999K| 17M| 73M| 6759 (2)| 00:01:35 |
| 3 | MERGE JOIN CARTESIAN | | 2827K| 24M| | 1012 (2)| 00:00:15 |
| 4 | INDEX FULL SCAN | TABLEY_PK | 63 | 252 | | 1 (0)| 00:00:01 |
| 5 | BUFFER SORT | | 44885 | 219K| | 1011 (2)| 00:00:15 |
| 6 | INDEX FAST FULL SCAN| TABLEX_PK | 44885 | 219K| | 16 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
13 rows selected.
Elapsed: 00:00:00.03
Statistics
----------------------------------------------------------
18 recursive calls
12 db block gets
35 consistent gets
0 physical reads
0 redo size
1616 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
13 rows processed
SQL>
I then experimented a bit with few types of SQL hints to make it run faster.. and I did manage to change the costs of the execution plan as follows:
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1999K| 49M| | 6058 (2)| 00:01:25 |
| 1 | VIEW | | 1999K| 49M| | 6058 (2)| 00:01:25 |
| 2 | HASH UNIQUE | | 1999K| 17M| 73M| 6058 (2)| 00:01:25 |
| 3 | MERGE JOIN CARTESIAN| | 2827K| 24M| | 312 (1)| 00:00:05 |
| 4 | INDEX FULL SCAN | TABLEY_PK | 63 | 252 | | 1 (0)| 00:00:01 |
| 5 | BUFFER SORT | | 44885 | 219K| | 311 (1)| 00:00:05 |
| 6 | INDEX FULL SCAN | TABLEX_PK | 44885 | 219K| | 5 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
13 rows selected.
Elapsed: 00:00:00.03
Not a lot of difference but better than before.. When I ran the SQL Query 1st time around, it took 3 mins to run which was great:
SQL>
SQL> INSERT into my_table (fld1, fld2, fld3, fld4)
2 (select /*+ FIRST_ROWS */ GET_NUM_REC(dd.FLD3,dd.FLD4,'PART') fld1,
3 GET_NUM_RECS_BY_PART(dd.FLD3,dd.FLD4,'SEC41') fld2,
4 dd.fld3,
5 dd.fld4
6 from (select distinct
7 ed.fld3,
8 ev.fld4
9 from tableX ed,
10 tableY ev) dd);
2827440 rows created.
Elapsed: 00:03:01.07
Execution Plan
----------------------------------------------------------
Plan hash value: 2908342591
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1999K| 49M| | 6058 (2)| 00:01:25 |
| 1 | VIEW | | 1999K| 49M| | 6058 (2)| 00:01:25 |
| 2 | SORT UNIQUE | | 1999K| 17M| 73M| 6058 (2)| 00:01:25 |
| 3 | MERGE JOIN CARTESIAN| | 2827K| 24M| | 312 (1)| 00:00:05 |
| 4 | INDEX FULL SCAN | tableY_PK | 63 | 252 | | 1 (0)| 00:00:01 |
| 5 | BUFFER SORT | | 44885 | 219K| | 311 (1)| 00:00:05 |
| 6 | INDEX FULL SCAN | tableX_PK | 44885 | 219K| | 5 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
5657055 recursive calls
45162 db block gets
28359415 consistent gets
68 physical reads
65516068 redo size
666 bytes sent via SQL*Net to client
994 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
2827440 rows processed
SQL>
However, the subsequent runs are taking 19 mins to run!!!!! As you can see below:
TRUNCATE TABLE my_table;
SQL> INSERT into my_table (fld1, fld2, fld3, fld4)
2 (select /*+ FIRST_ROWS */ GET_NUM_REC(dd.FLD3,dd.FLD4,'PART') fld1,
3 GET_NUM_RECS_BY_PART(dd.FLD3,dd.FLD4,'SEC41') fld2,
4 dd.fld3,
5 dd.fld4
6 from (select distinct
7 ed.fld3,
8 ev.fld4
9 from tableX ed,
10 tableY ev) dd);
2827440 rows created.
Elapsed: 00:19:34.91
Execution Plan
----------------------------------------------------------
Plan hash value: 2908342591
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1999K| 49M| | 6058 (2)| 00:01:25 |
| 1 | VIEW | | 1999K| 49M| | 6058 (2)| 00:01:25 |
| 2 | SORT UNIQUE | | 1999K| 17M| 73M| 6058 (2)| 00:01:25 |
| 3 | MERGE JOIN CARTESIAN| | 2827K| 24M| | 312 (1)| 00:00:05 |
| 4 | INDEX FULL SCAN | tableY_PK | 63 | 252 | | 1 (0)| 00:00:01 |
| 5 | BUFFER SORT | | 44885 | 219K| | 311 (1)| 00:00:05 |
| 6 | INDEX FULL SCAN | tableX_PK | 44885 | 219K| | 5 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
5657115 recursive calls
45250 db block gets
28360375 consistent gets
5173 physical reads
65526968 redo size
668 bytes sent via SQL*Net to client
979 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
2827440 rows processed
SQL>
This just does not make any sense!!!! Well.. I have to admit that the changes I made are not significant, so I'm not sure how I ended up with 3 mins in the 1st place, but for that 3 mins to jump up to 19min in a subsequent run (literally few mins later) just did not make any sense knowing that the plan is exactly identical!!
Anyone can shed some light on this please? Also any ideas what else I can try to tune the SQL above?
Thanks for your help
Edited by: user5545873 on Dec 11, 2009 1:21 PM