Po upgradu jsme našli pomalé dotazy. Kdy vyplynulo na povrch pár neoptimalizovaných dotazů.
SQL> explain plan for SELECT DISTINCT PMEDBT, PM55MDMSGN, PM55MDDKCD, PM55MDPART, PMVR01, PMLOCKFLG, PMEV01, PM55MHTP, PM55MHSUCO, PM55MHPIDA, PMAN8,
PMSHAN, PMLITM, PMMCU, PMDTAI, PM55MDCSGN FROM PRODDTA.F5542MDB WHERE (( PMLOCKFLG = 'N' AND PMEV01 NOT IN ('1','Y'))) ORDER BY PMVR01 ASC,PM55MDPAR
T ASC,PM55MDMSGN ASC,PM55MDCSGN ASC;
Explained.
SQL> SET LINESIZE 130 SQL> SET PAGESIZE 0 SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); Plan hash value: 444175491
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 71 | 18318 | 37673 (1)| 00:07:33 |
| 1 | SORT ORDER BY | | 71 | 18318 | 37673 (1)| 00:07:33 |
| 2 | HASH UNIQUE | | 71 | 18318 | 37672 (1)| 00:07:33 |
|* 3 | TABLE ACCESS FULL| F5542MDB | 71 | 18318 | 37671 (1)| 00:07:33 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("PMEV01"<>U'Y' AND "PMLOCKFLG"=U'N' AND "PMEV01"<>U'1')
15 rows selected.
Kontrola indexů
SQL> column table_owner format a15 SQL> column table_name format a20 SQL> column index_name format a20 SQL> column column_name format a20 SQL> Select table_owner, table_name, index_name, column_name 2 FROM dba_ind_columns 3 Where table_owner='PRODDTA' 4 AND table_name='F5542MDB' 5 Order by 1,2,3,4; PRODDTA F5542MDB F5542MDB_0 PM55MDKNBS PRODDTA F5542MDB F5542MDB_0 PM55MDMSGN PRODDTA F5542MDB F5542MDB_0 PMEDBT PRODDTA F5542MDB F5542MDB_2 PM55MDMAN0 PRODDTA F5542MDB F5542MDB_3 PM55MDCSGN PRODDTA F5542MDB F5542MDB_3 PM55MDDKCD PRODDTA F5542MDB F5542MDB_3 PM55MDITEMN PRODDTA F5542MDB F5542MDB_3 PM55MDMSGN PRODDTA F5542MDB F5542MDB_4 PMEDBT PRODDTA F5542MDB F5542MDB_5 PM55MDCSGN PRODDTA F5542MDB F5542MDB_5 PMEDBT PRODDTA F5542MDB F5542MDB_6 PM55MHPIDA PRODDTA F5542MDB F5542MDB_6 PMEDBT PRODDTA F5542MDB F5542MDB_6 PMLITM PRODDTA F5542MDB F5542MDB_7 PM55MDKNBS PRODDTA F5542MDB F5542MDB_8 PM55MDMSGN PRODDTA F5542MDB F5542MDB_8 PM55MDPART PRODDTA F5542MDB F5542MDB_8 PMLOCKFLG PRODDTA F5542MDB F5542MDB_8 PMVR01
Chybí index nad sloupcem PMEV01
Vytvoření kompozitního indexu přes oba sloupce.
SQL> create index PRODDTA.F5542MDB_1 on PRODDTA.F5542MDB("PMEV01","PMLOCKFLG"); Index created.
Kontrola exekučního plánu SQL
SQL> explain plan for SELECT DISTINCT PMEDBT, PM55MDMSGN, PM55MDDKCD, PM55MDPART, PMVR01, PMLOCKFLG, PMEV01, PM55MHTP, PM55MHSUCO, PM55MHPIDA, PMAN8,
PMSHAN, PMLITM, PMMCU, PMDTAI, PM55MDCSGN FROM PRODDTA.F5542MDB WHERE (( PMLOCKFLG = 'N' AND PMEV01 NOT IN ('1','Y'))) ORDER BY PMVR01 ASC,PM55MDPAR
T ASC,PM55MDMSGN ASC,PM55MDCSGN ASC;
Explained.
SQL> SET LINESIZE 130
SQL> SET PAGESIZE 0
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Plan hash value: 3508459007
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 71 | 18318 | 944 (1)| 00:00:12 |
| 1 | SORT ORDER BY | | 71 | 18318 | 944 (1)| 00:00:12 |
| 2 | HASH UNIQUE | | 71 | 18318 | 943 (1)| 00:00:12 |
| 3 | TABLE ACCESS BY INDEX ROWID| F5542MDB | 71 | 18318 | 942 (0)| 00:00:12 |
|* 4 | INDEX SKIP SCAN | F5542MDB_1 | 71 | | 931 (0)| 00:00:12 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("PMLOCKFLG"=U'N')
filter("PMEV01"<>U'Y' AND "PMLOCKFLG"=U'N' AND "PMEV01"<>U'1')
17 rows selected.