Hello All,
We are running a 11.2.0.3 database and we detected a query that was running really slow. Here is below the query :
INSERT INTO SERVICEDRIVERTBL
(PARENTCONTEXTID,CONTEXTID)
SELECT PARENTCONTEXTID,CONTEXTID
FROM WM97_421_ISCA.WMSERVICE S1
WHERE S1.STATUS IN (2,4)
AND S1.AUDITTIMESTAMP < TO_DATE('20170621', 'YYYYMMDD') AND S1.AUDITTIMESTAMP = (SELECT MAX(S2.AUDITTIMESTAMP) FROM WM97_421_ISCA.WMSERVICE S2 WHERE S1.CONTEXTID=S2.CONTEXTID)
The purpose of this query is to retrieve two identifiers corresponding to some webmethods services executed in the COMPLETED (2) and FAILED (4) statuses, and making sure that they are outside the retention period and than no other corresponding entry is more recent. After fetching data with the select, data are inserted into a table.
We then ran the SELECT statement within the INSERT and it took about 27 minutes to complete. When running it live, the statement did not even seem to finish.
The version of the database is 11.2.0.3.
These are the parameters relevant to the optimizer:
SQL> show parameter optimizer
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 11.2.0.3
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE
optimizer_use_invisible_indexes boolean FALSE
optimizer_use_pending_statistics boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
SQL> show parameter db_file_multi
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 128
SQL> show parameter db_block_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
SQL> show parameter cursor_sharing
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string EXACT
SQL> column sname format a20
SQL> column pname format a20
SQL> column pval2 format a20
SQL> select sname, pname , pval1, pval2 from sys.aux_stats$;
SNAME PNAME PVAL1 PVAL2
-------------------- -------------------- ---------- --------------------
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 07-18-2013 16:11
SYSSTATS_INFO DSTOP 07-18-2013 16:11
SYSSTATS_INFO FLAGS 1
SYSSTATS_MAIN CPUSPEEDNW 996
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM
SYSSTATS_MAIN MREADTIM
SYSSTATS_MAIN CPUSPEED
SYSSTATS_MAIN MBRC
SNAME PNAME PVAL1 PVAL2
-------------------- -------------------- ---------- --------------------
SYSSTATS_MAIN MAXTHR
SYSSTATS_MAIN SLAVETHR
13 rows selected.
Here is the DBMS_XPLAN.DISPLAY_CURSOR output:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8g0qdpg8rg69q, child number 1
-------------------------------------
SELECT /*+ qb_name(main_query) */ PARENTCONTEXTID, CONTEXTID FROM
WM97_421_ISCA.WMSERVICE S1 WHERE S1.STATUS IN (2,4) AND
S1.AUDITTIMESTAMP < TO_DATE('20170621', 'YYYYMMDD') AND
S1.AUDITTIMESTAMP = (SELECT /*+ qb_name(inner_query) */
MAX(S2.AUDITTIMESTAMP) FROM WM97_421_ISCA.WMSERVICE S2 WHERE
S1.CONTEXTID=S2.CONTEXTID)
Plan hash value: 377437756
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp|
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 276K|00:26:10.72 | 8221K| 1847K| 62000 | | | | |
|* 1 | FILTER | | 1 | | 276K|00:26:10.72 | 8221K| 1847K| 62000 | | | | |
| 2 | HASH GROUP BY | | 1 | 1 | 276K|00:26:10.26 | 8221K| 1847K| 62000 | 1158M| 30M| | |
|* 3 | HASH JOIN | | 1 | 6437K| 6659K|00:25:34.19 | 8221K| 1847K| 39029 | 388M| 16M| 450M (0)| 331K|
|* 4 | TABLE ACCESS BY INDEX ROWID| WMSERVICE | 1 | 3197K| 3329K|00:22:38.68 | 6571K| 250K| 0 | | | | |
|* 5 | INDEX RANGE SCAN | IDX_SVC_AUDTM | 1 | 1 | 6659K|00:05:49.09 | 76468 | 55203 | 0 | | | | |
| 6 | TABLE ACCESS FULL | WMSERVICE | 1 | 35M| 36M|00:01:24.74 | 1650K| 1557K| 0 | | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(SYS_OP_UNDESCEND("S1"."SYS_NC00021$")=MAX("S2"."AUDITTIMESTAMP"))
3 - access("S1"."CONTEXTID"="S2"."CONTEXTID")
4 - filter(("S1"."STATUS"=2 OR "S1"."STATUS"=4))
5 - access("S1"."SYS_NC00021$">HEXTORAW('878AF9EAFEF8FEFAFF') )
filter(SYS_OP_UNDESCEND("S1"."SYS_NC00021$")<TIMESTAMP' 2017-06-21 00:00:00')
32 rows selected.
We try to create a few indexes and try to get rid of line *3 that takes most of the time but no luck so far. Would you give us a hand on tuning this query ?
Many thanks !