Van egy select, ami sehogy nem akart leftuni, egy tizenmilliós táblán szalad végig, plusz néhány kisebb lookup táblából vesz még adatokat. Tettem indexeket a join mezőkre, de az sem segített. Hívjuk segítségül a tuning advisort!
There is a select that never happened to finish running. It scans through a 10M record table, and uses some other lookup tables to fetch additional data. I have created indexes on the join fields, but they didn't help. Let's call the tuning advisor.
DECLARE
ret_val VARCHAR2(4000);
SqlStr CLOB := 'create TABLE bt_tesztusp2 AS (
SELECT ... <select statement goes here> )';
BEGIN
ret_val := dbms_sqltune.create_tuning_task(SqlStr);
dbms_output.put_line(ret_val);
END;
Megvan az tuning task / we have the tuning task:
TASK_25827
Futtassuk le / let's run it:
SQL> exec dbms_sqltune.execute_tuning_task('TASK_25827');Miután lefutott, megnézhetjük az eredményét, ami egy CLOB-ban látható / After it has finished running, we can check the results coming in a CLOB:
PL/SQL procedure successfully completed
SELECT dbms_sqltune.report_tuning_task('TASK_25827') FROM dual;
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : TASK_25827
Tuning Task Owner : BAGOT
Scope : COMPREHENSIVE
Time Limit(seconds) : 1800
Completion Status : COMPLETED
Started at : 09/02/2011 11:40:13
Completed at : 09/02/2011 12:08:02
Number of SQL Profile Findings : 1
-------------------------------------------------------------------------------
Schema Name: BAGOT
SQL ID : bgv2s20aqq8b2
SQL Text : create TABLE bt_tesztusp2 AS (
SELECT <my select statement>
)
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 98,51%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_25827',
replace => TRUE);
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 2282809337
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | 96528 | 38M| | 790K (1)| 01:11:30 |
| 1 | LOAD AS SELECT | BT_TESZTUSP2 | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | L_1INST_MUSP001 | 1 | 28 | | 2 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 96528 | 38M| | 790K (1)| 01:11:29 |
|* 4 | HASH JOIN | | 73151 | 27M| 21M| 668K (1)| 01:00:27 |
| 5 | TABLE ACCESS BY INDEX ROWID | L_1INST_MEPF00 | 1 | 75 | | 2 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 73541 | 20M| | 668K (1)| 01:00:25 |
| 7 | NESTED LOOPS | | 73367 | 15M| | 594K (1)| 00:53:46 |
| 8 | NESTED LOOPS | | 133K| 18M| | 460K (1)| 00:41:42 |
|* 9 | TABLE ACCESS BY INDEX ROWID| L_1INST_PCPF00 | 153K| 13M| | 52 (2)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | PCPF00_IX1 | 1386 | | | 4 (0)| 00:00:01 |
| 11 | TABLE ACCESS BY INDEX ROWID| L_1INST_SOPF00 | 1 | 53 | | 3 (0)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | SOPF00_IX1 | 1 | | | 2 (0)| 00:00:01 |
| 13 | TABLE ACCESS BY INDEX ROWID | L_1INST_SVPF00 | 1 | 81 | | 2 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | SVPF00_IX1 | 1 | | | 1 (0)| 00:00:01 |
|* 15 | INDEX RANGE SCAN | MEPF00_IX1 | 1 | | | 1 (0)| 00:00:01 |
|* 16 | TABLE ACCESS FULL | L_1INST_MUSP00 | 15643 | 1405K| | 7 (43)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | MUSP001_IX1 | 1 | | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access <masked>
9 - filter <masked>
10 - access <masked>
12 - access <masked>
14 - access <masked>
15 - access <masked>
16 - filter <masked>
17 - access <masked>
2- Using SQL Profile
--------------------
Plan hash value: 1624737859
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | 96528 | 38M| | 11737 (14)| 00:01:04 |
| 1 | LOAD AS SELECT | BT_TESZTUSP2 | | | | | |
|* 2 | HASH JOIN | | 96528 | 38M| | 11490 (14)| 00:01:03 |
|* 3 | TABLE ACCESS FULL | L_1INST_MUSP001 | 20648 | 564K| | 5 (60)| 00:00:01 |
|* 4 | HASH JOIN | | 73151 | 27M| | 11482 (14)| 00:01:03 |
| 5 | TABLE ACCESS BY INDEX ROWID| L_1INST_MEPF00 | 14825 | 1085K| | 4 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | MEPF00_IX1 | 60 | | | 2 (0)| 00:00:01 |
|* 7 | HASH JOIN | | 72978 | 21M| | 11476 (14)| 00:01:03 |
|* 8 | TABLE ACCESS FULL | L_1INST_MUSP00 | 15643 | 1405K| | 7 (43)| 00:00:01 |
|* 9 | HASH JOIN | | 73367 | 15M| | 11467 (14)| 00:01:03 |
|* 10 | TABLE ACCESS FULL | L_1INST_SVPF00 | 1937 | 153K| | 2 (0)| 00:00:01 |
|* 11 | HASH JOIN | | 133K| 18M| 14M| 11462 (14)| 00:01:03 |
|* 12 | TABLE ACCESS FULL | L_1INST_PCPF00 | 153K| 13M| | 50 (76)| 00:00:01 |
|* 13 | TABLE ACCESS FULL | L_1INST_SOPF00 | 10M| 534M| | 2419 (45)| 00:00:14 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access <masked>
3 - filter <masked>
4 - access <masked>
6 - access <masked>
7 - access <masked>
8 - filter <masked>
9 - access <masked>
10 - filter <masked>
11 - access <masked>
12 - filter <masked>
13 - filter <masked>
-------------------------------------------------------------------------------
OK, fogadjuk el a javaslatot / Let's accept the recommendation:
SQL> execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_25827', replace => TRUE);
PL/SQL procedure successfully completed
Az explain plan valóban frissült, a SELECT szövegének betűre egyeznie kell a tuning advisornak paraméterként megadottal, és végül 41 perc alatt lefutott. Ez az adott helyzetben már elfogadható / The explain plan really has been refreshed, the text of the SELECT statement has to be exactly the same as the one given to the tuning advisor as a parameter, and eventually it has completed in 41 minutes. This is acceptable in this situation.
Takarítsunk / let's do a clean-up:
SQL> exec dbms_sqltune.drop_tuning_task('TASK_25827');
PL/SQL procedure successfully completed
Ajánlott bejegyzések:
A bejegyzés trackback címe:
Kommentek:
A hozzászólások a vonatkozó jogszabályok értelmében felhasználói tartalomnak minősülnek, értük a szolgáltatás technikai üzemeltetője semmilyen felelősséget nem vállal, azokat nem ellenőrzi. Kifogás esetén forduljon a blog szerkesztőjéhez. Részletek a Felhasználási feltételekben és az adatvédelmi tájékoztatóban.