Skip to Main Content
  • Questions

  • SQL query sometimes taking longer (most of the time working good)

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Mr.

Asked: August 07, 2016 - 9:21 am UTC

Last updated: September 06, 2016 - 2:27 am UTC

Version: 11g

Viewed 1000+ times

You Asked

This is the duplicate (unable to edit the old) question where Connor Sir has asked to add code tags but as I was not aware of code tags.
Here it is...


This is to ask some suggestion about a query which is infrequently taking minutes->hours time to finish (mostly it is fast). This is the query generated by the execute_query; procedure in Oracle forms compiled in Forms 11g and running on Oracle 11g database. Below is the query information, request you to kindly suggest a solution and help me understand the problem.

SQL_ID 07pfqktr7y6gk, child number 0
-------------------------------------
SELECT ROWID,CARCOD,DOCNUM,ERRTYP,RMK,BATSEQ,APXINKIND,CARNUMCOD,CPNNUM,
REFTRNIDR FROM SLPSALEXC WHERE batseq in (select :1 from dual union
select a.batseq from genagpmst b,slpbathdr a where a.stadat >= :2 and
a.enddat <= :3 and a.loctyp=nvl(:4,a.loctyp) and
a.curcod=nvl(:5,a.curcod) and a.agpcod = b.agpcod and a.agpcod =
nvl(:6,a.agpcod) and b.teacod = nvl(:7,b.teacod) ) and ((:8='T' and
errtyp='T') or (:9!='T')) order by errtyp, docnum

Plan hash value: 505003130
 
---------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                 |       |       |     9 (100)|          |
|   1 |  SORT ORDER BY                        |                 |     1 |   114 |     9  (23)| 00:00:01 |
|   2 |   NESTED LOOPS                        |                 |     1 |   114 |     8  (13)| 00:00:01 |
|   3 |    NESTED LOOPS                       |                 |     6 |   114 |     8  (13)| 00:00:01 |
|   4 |     VIEW                              | VW_NSO_1        |     3 |    39 |     8  (25)| 00:00:01 |
|   5 |      SORT UNIQUE                      |                 |     3 |   144 |     8  (25)| 00:00:01 |
|   6 |       UNION-ALL                       |                 |       |       |            |          |
|   7 |        FAST DUAL                      |                 |     1 |       |     2   (0)| 00:00:01 |
|   8 |        CONCATENATION                  |                 |       |       |            |          |
|*  9 |         FILTER                        |                 |       |       |            |          |
|  10 |          NESTED LOOPS                 |                 |     1 |    48 |     2   (0)| 00:00:01 |
|  11 |           NESTED LOOPS                |                 |     1 |    48 |     2   (0)| 00:00:01 |
|* 12 |            TABLE ACCESS BY INDEX ROWID| SLPBATHDR       |     1 |    36 |     1   (0)| 00:00:01 |
|* 13 |             INDEX RANGE SCAN          | SLPBATHDR_IND7  |     1 |       |     1   (0)| 00:00:01 |
|* 14 |            INDEX UNIQUE SCAN          | GENAGPMST_UIND1 |     1 |       |     1   (0)| 00:00:01 |
|* 15 |           TABLE ACCESS BY INDEX ROWID | GENAGPMST       |     1 |    12 |     1   (0)| 00:00:01 |
|* 16 |         FILTER                        |                 |       |       |            |          |
|  17 |          NESTED LOOPS                 |                 |     1 |    48 |     2   (0)| 00:00:01 |
|  18 |           NESTED LOOPS                |                 |     1 |    48 |     2   (0)| 00:00:01 |
|  19 |            TABLE ACCESS BY INDEX ROWID| GENAGPMST       |     1 |    12 |     1   (0)| 00:00:01 |
|* 20 |             INDEX RANGE SCAN          | GENAGPMST_IND3  |     1 |       |     1   (0)| 00:00:01 |
|* 21 |            INDEX RANGE SCAN           | SLPBATHDR_IND7  |     1 |       |     1   (0)| 00:00:01 |
|* 22 |           TABLE ACCESS BY INDEX ROWID | SLPBATHDR       |     1 |    36 |     1   (0)| 00:00:01 |
|* 23 |     INDEX RANGE SCAN                  | SLPSALEXC_IND1  |     2 |       |     1   (0)| 00:00:01 |
|* 24 |    TABLE ACCESS BY INDEX ROWID        | SLPSALEXC       |     1 |   101 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
 
   9 - filter(:7 IS NULL)
  12 - filter(("A"."STADAT">=:2 AND "A"."LOCTYP"=NVL(:4,"A"."LOCTYP") AND 
              "A"."CURCOD"=NVL(:5,"A"."CURCOD") AND "A"."AGPCOD"=NVL(:6,"A"."AGPCOD")))
  13 - access("A"."ENDDAT"<=:3)
  14 - access("A"."AGPCOD"="B"."AGPCOD")
  15 - filter("B"."TEACOD" IS NOT NULL)
  16 - filter(:7 IS NOT NULL)
  20 - access("B"."TEACOD"=:7)
  21 - access("A"."ENDDAT"<=:3)
  22 - filter(("A"."STADAT">=:2 AND "A"."LOCTYP"=NVL(:4,"A"."LOCTYP") AND 
              "A"."CURCOD"=NVL(:5,"A"."CURCOD") AND "A"."AGPCOD"=NVL(:6,"A"."AGPCOD") AND 
              "A"."AGPCOD"="B"."AGPCOD"))
  23 - access("BATSEQ"=":1")
  24 - filter((:9<>'T' OR (:8='T' AND "ERRTYP"='T')))

and Connor said...

What is going to be critical here is the values provided by the Form to the query.

Look at line 9 and 16 in the execution plan

   9 - filter(:7 IS NULL)
  16 - filter(:7 IS NOT NULL)


So depending on :7, you will either be including lines 9-15, or lines 16-22. So I would start by isolating which one of those scenarios is perhaps causing the problems.

In either case, lines 4-22 are in effect giving you a set of keys, that are then used to drive via an index into SLPSALEXC. So if that set of keys returned is huge (eg thousands of rows) that could easily cause a rapid degeneration in performance.

Hope this helps.

Rating

  (15 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Follow Up

Baig, August 08, 2016 - 5:19 am UTC

Thanks for your response. For us it is always :7 is not null case.

Couple of questions around it.
1) What exactly is 100% cpu means at the select statement in the plan?

2) Why this is not causing issues if :7 is always not null and the same query with values assigned to bind variables is executing pretty fast from backend session at the same time when one of the front end session is struck.

3) Is it related to bind variable peeking issue (would you be so kind to provide some details about bind variable peeking, I'm not much aware of it)

Thanks
Baig
Connor McDonald
August 08, 2016 - 7:42 am UTC

1) That is just the total. You can see where the optimizer thinks the CPU will be split by looking down at the subequent lines.

2) When you do this, are you still using binds or substiuting the values in. If the latter, than its not the same query

3) Possibly. Depends if you have histograms on relevant columns.

A good next step would be to run a 10046 trace on the Form, and capture the row source information. This will give you the actual rows per execution plan line, which you can then comapare to the estimates.

Follow Up

Mr, August 09, 2016 - 6:32 am UTC

Thanks for the response Sir
1) Even we have noticed that the CPU utilization is going almost 100% when the form's query session is hanging.
How the 100% CPU is computed in the explain plan? because in one of the forum/document (morganslibrary) I read that CPU% (if not matching with the records in the plan) if not correct then there is some error internally with Oracle Optimizer and needs to be reported to Oracle.
Ref: http://www.morganslibrary.org/pres/lad10/peoug_xplan.pdf
page no 57
Could you please provide some input

Thanks for your suggestion to enable the 10046 trace, but the concern is that we not getting the issue frequently, sometime in few days or in weeks. Suggest a way forward.

Thanks
Baig
Connor McDonald
August 10, 2016 - 1:25 am UTC

Next time one is running for hours, try obtain some data from

V$SQL_PLAN_STATISTICS
V$SQL_BIND_CAPTURE

Follow Up

Mr Baig, August 10, 2016 - 3:31 am UTC

Thanks Connor
I will get the data from the views.

Would you be so kind to provide an update on point no 1 regarding CPU utilization.

-
baig
Connor McDonald
August 10, 2016 - 12:18 pm UTC

CPU utilization going to 100 is not unexpected if you are doing lots of nested loops, because a small amount of data gets into memory, and then you loop around lots of times. When there are minimal disk accesses involved, it becomes all memory-based work which will be reported as CPU.


Follow Up

Mr Baig, August 11, 2016 - 2:46 am UTC

I managed to get the data from v$sql_bind_capture but unable to get from v$sql_plan_statistics as we don't have dba control to enable trace. Asked DBA to provide.

Is there a way to get the question deleted from this forum or update the question as the it contains the table names which are in company's standard naming convention?

NAME POS DATATYPE DATATYPE_STR CHAR_SID MAX_LEN WAS_CAPTURED VALUE_STRING VALUE_ANYDATA
:1 1 2 NUMBER 1 22 NO  
:2 2 12 DATE 1 7 YES 08/03/2016  00:00:00 SYS.ANYDATA
:3 3 12 DATE 1 7 YES 08/03/2016  00:00:00 SYS.ANYDATA
:4 4 96 CHAR(32) 178 32 YES NULL 
:5 5 96 CHAR(32) 178 32 YES NULL 
:6 6 96 CHAR(32) 178 32 YES NULL 
:7 7 96 CHAR(32) 178 32 YES RAC SYS.ANYDATA
:8 8 1 VARCHAR2(32) 178 32 YES S SYS.ANYDATA
:9 9 1 VARCHAR2(32) 178 32 YES S SYS.ANYDATA


I'm unable to run the query from back end with the bind variables (:1 unable to create because of naming standard) at the time when the session is hanged.
Could you please help.

Connor McDonald
August 11, 2016 - 6:58 am UTC

The question is no longer visible, so you need to come here by the link:

https://asktom.oracle.com/pls/apex/f?p=100:201:::NO:RP:P201_ID:9531057800346080811

Follow Up

Mr Baig, August 11, 2016 - 7:09 am UTC

Thanks a lot Connor
We are coordinating with DBA to get the trace file for the session. Will keep you posted.
Meanwhile, is there anything we can get out of v$sql_bind_capture data pasted in previous review comment or do you see any issues with the query (to rewrite).
-
baig
Connor McDonald
August 11, 2016 - 12:39 pm UTC

One thing I would consider is that it looks like that query is one of those "one size fits all" types, ie, handle very possible scenario. You might be better off doing something like:

pre-query trigger:
- check which items actually have a value
- build a *custom* where clause that best suits the values provided, and then do a SET_BLOCK_PROPERTY('block',default_where_clause) with that predicate.

Hope this helps.

Follow Up

Mr Baig, August 16, 2016 - 8:43 am UTC

Sorry about being missing in action...last few days I was busy with other deliverable, keeping this on hold.

This has now become bottleneck for us as the client is not ready to sign project closure.

Have proposed the solution to rewrite the code but the question again is why the same is not causing problems for other clients.

Anyways, we are awaiting DBA inputs, will keep you posted once we receive an update.

Do you think that using rule based optimizer makes some sense?
Connor McDonald
August 17, 2016 - 1:10 am UTC

Using the rule optimizer...never makes sense.

If you want to lock down the plan to a known one, consider using a sql plan directive.

Follow Up

Mr Baig, August 16, 2016 - 9:21 am UTC

Got some more information from dbms_xplan.display_cursor(sql_id, NULL, 'outline peeked_binds'), can we make something out of this.
SQL_ID  07pfqktr7y6gk, child number 0
-------------------------------------
SELECT ROWID,CARCOD,DOCNUM,ERRTYP,RMK,BATSEQ,APXINKIND,CARNUMCOD,CPNNUM,
REFTRNIDR FROM SLPSALEXC WHERE batseq in (select :1 from dual union 
select a.batseq from genagpmst b,slpbathdr a where a.stadat >= :2 and 
a.enddat <= :3 and a.loctyp=nvl(:4,a.loctyp) and 
a.curcod=nvl(:5,a.curcod) and a.agpcod = b.agpcod and a.agpcod = 
nvl(:6,a.agpcod) and b.teacod = nvl(:7,b.teacod) ) and ((:8='T' and 
errtyp='T') or (:9!='T'))  order by errtyp, docnum
 
Plan hash value: 786625402
 
---------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                 |       |       |   284 (100)|          |
|   1 |  SORT ORDER BY                        |                 |   133 | 15162 |   284   (1)| 00:00:04 |
|   2 |   NESTED LOOPS                        |                 |   133 | 15162 |   283   (1)| 00:00:04 |
|   3 |    NESTED LOOPS                       |                 |  3422 | 15162 |   283   (1)| 00:00:04 |
|   4 |     VIEW                              | VW_NSO_1        |  1711 | 22243 |   113   (2)| 00:00:02 |
|   5 |      SORT UNIQUE                      |                 |  1711 |   160K|   113   (2)| 00:00:02 |
|   6 |       UNION-ALL                       |                 |       |       |            |          |
|   7 |        FAST DUAL                      |                 |     1 |       |     2   (0)| 00:00:01 |
|   8 |        CONCATENATION                  |                 |       |       |            |          |
|*  9 |         FILTER                        |                 |       |       |            |          |
|* 10 |          HASH JOIN                    |                 |  1709 | 82032 |   107   (0)| 00:00:02 |
|* 11 |           TABLE ACCESS FULL           | GENAGPMST       |   552 |  6624 |    12   (0)| 00:00:01 |
|* 12 |           TABLE ACCESS BY INDEX ROWID | SLPBATHDR       |  1966 | 70776 |    95   (0)| 00:00:02 |
|* 13 |            INDEX RANGE SCAN           | SLPBATHDR_IND2  |  1966 |       |     1   (0)| 00:00:01 |
|* 14 |         FILTER                        |                 |       |       |            |          |
|  15 |          NESTED LOOPS                 |                 |     1 |    48 |     2   (0)| 00:00:01 |
|  16 |           NESTED LOOPS                |                 |     1 |    48 |     2   (0)| 00:00:01 |
|* 17 |            TABLE ACCESS BY INDEX ROWID| SLPBATHDR       |     1 |    36 |     1   (0)| 00:00:01 |
|* 18 |             INDEX SKIP SCAN           | SLPBATHDR_IND2  |     1 |       |     1   (0)| 00:00:01 |
|* 19 |            INDEX UNIQUE SCAN          | GENAGPMST_UIND1 |     1 |       |     1   (0)| 00:00:01 |
|* 20 |           TABLE ACCESS BY INDEX ROWID | GENAGPMST       |     1 |    12 |     1   (0)| 00:00:01 |
|* 21 |     INDEX RANGE SCAN                  | SLPSALEXC_IND1  |     2 |       |     1   (0)| 00:00:01 |
|* 22 |    TABLE ACCESS BY INDEX ROWID        | SLPSALEXC       |     1 |   101 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      OPT_PARAM('optimizer_index_cost_adj' 10)
      OPT_PARAM('optimizer_index_caching' 100)
      FIRST_ROWS(10)
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SEL$3")
      OUTLINE_LEAF(@"SET$FCA7A018")
      OUTLINE_LEAF(@"SEL$940CEDA2")
      UNNEST(@"SET$1" UNNEST_INNERJ_DISTINCT_VIEW)
      OUTLINE_LEAF(@"SEL$3_1")
      USE_CONCAT(@"SEL$3" 8 OR_PREDICATES(6))
      OUTLINE_LEAF(@"SEL$3_2")
      OUTLINE(@"SET$1")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$3")
      NO_ACCESS(@"SEL$940CEDA2" "VW_NSO_1"@"SEL$940CEDA2")
      INDEX(@"SEL$940CEDA2" "SLPSALEXC"@"SEL$1" ("SLPSALEXC"."BATSEQ"))
      LEADING(@"SEL$940CEDA2" "VW_NSO_1"@"SEL$940CEDA2" "SLPSALEXC"@"SEL$1")
      USE_NL(@"SEL$940CEDA2" "SLPSALEXC"@"SEL$1")
      NLJ_BATCHING(@"SEL$940CEDA2" "SLPSALEXC"@"SEL$1")
      FULL(@"SEL$3_1" "B"@"SEL$3")
      INDEX_RS_ASC(@"SEL$3_1" "A"@"SEL$3" ("SLPBATHDR"."STADAT" "SLPBATHDR"."AGPCOD"))
      INDEX_SS(@"SEL$3_2" "A"@"SEL$3_2" ("SLPBATHDR"."STADAT" "SLPBATHDR"."AGPCOD"))
      INDEX(@"SEL$3_2" "B"@"SEL$3_2" ("GENAGPMST"."AGPCOD"))
      LEADING(@"SEL$3_1" "B"@"SEL$3" "A"@"SEL$3")
      LEADING(@"SEL$3_2" "A"@"SEL$3_2" "B"@"SEL$3_2")
      USE_HASH(@"SEL$3_1" "A"@"SEL$3")
      USE_NL(@"SEL$3_2" "B"@"SEL$3_2")
      NLJ_BATCHING(@"SEL$3_2" "B"@"SEL$3_2")
      PX_JOIN_FILTER(@"SEL$3_1" "A"@"SEL$3")
      END_OUTLINE_DATA
  */
 
Peeked Binds (identified by position):
--------------------------------------
 
   2 - :2 (DATE): 08/10/2016 00:00:00
   3 - :3 (DATE): 08/10/2016 00:00:00
   4 - :4 (CHAR(30), CSID=178): (null)
   5 - :5 (CHAR(30), CSID=178): (null)
   6 - :6 (CHAR(30), CSID=178): (null)
   7 - :7 (CHAR(30), CSID=178): 'RAC'
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   9 - filter(:6 IS NULL)
  10 - access("A"."AGPCOD"="B"."AGPCOD")
  11 - filter("B"."TEACOD"=NVL(:7,"B"."TEACOD"))
  12 - filter(("A"."ENDDAT"<=:3 AND "A"."LOCTYP"=NVL(:4,"A"."LOCTYP") AND 
              "A"."CURCOD"=NVL(:5,"A"."CURCOD")))
  13 - access("A"."STADAT">=:2)
       filter("A"."AGPCOD" IS NOT NULL)
  14 - filter(:6 IS NOT NULL)
  17 - filter(("A"."ENDDAT"<=:3 AND "A"."LOCTYP"=NVL(:4,"A"."LOCTYP") AND 
              "A"."CURCOD"=NVL(:5,"A"."CURCOD")))
  18 - access("A"."STADAT">=:2 AND "A"."AGPCOD"=:6)
       filter("A"."AGPCOD"=:6)
  19 - access("A"."AGPCOD"="B"."AGPCOD")
  20 - filter("B"."TEACOD"=NVL(:7,"B"."TEACOD"))
  21 - access("BATSEQ"=":1")
  22 - filter((:9<>'T' OR (:8='T' AND "ERRTYP"='T')))
 
Note
-----
   - cardinality feedback used for this statement
 
SQL_ID  07pfqktr7y6gk, child number 1
-------------------------------------
SELECT ROWID,CARCOD,DOCNUM,ERRTYP,RMK,BATSEQ,APXINKIND,CARNUMCOD,CPNNUM,
REFTRNIDR FROM SLPSALEXC WHERE batseq in (select :1 from dual union 
select a.batseq from genagpmst b,slpbathdr a where a.stadat >= :2 and 
a.enddat <= :3 and a.loctyp=nvl(:4,a.loctyp) and 
a.curcod=nvl(:5,a.curcod) and a.agpcod = b.agpcod and a.agpcod = 
nvl(:6,a.agpcod) and b.teacod = nvl(:7,b.teacod) ) and ((:8='T' and 
errtyp='T') or (:9!='T'))  order by errtyp, docnum
 
Plan hash value: 505003130
 
---------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                 |       |       |     9 (100)|          |
|   1 |  SORT ORDER BY                        |                 |     1 |   114 |     9  (23)| 00:00:01 |
|   2 |   NESTED LOOPS                        |                 |     1 |   114 |     8  (13)| 00:00:01 |
|   3 |    NESTED LOOPS                       |                 |     6 |   114 |     8  (13)| 00:00:01 |
|   4 |     VIEW                              | VW_NSO_1        |     3 |    39 |     8  (25)| 00:00:01 |
|   5 |      SORT UNIQUE                      |                 |     3 |   144 |     8  (25)| 00:00:01 |
|   6 |       UNION-ALL                       |                 |       |       |            |          |
|   7 |        FAST DUAL                      |                 |     1 |       |     2   (0)| 00:00:01 |
|   8 |        CONCATENATION                  |                 |       |       |            |          |
|*  9 |         FILTER                        |                 |       |       |            |          |
|  10 |          NESTED LOOPS                 |                 |     1 |    48 |     2   (0)| 00:00:01 |
|  11 |           NESTED LOOPS                |                 |     1 |    48 |     2   (0)| 00:00:01 |
|* 12 |            TABLE ACCESS BY INDEX ROWID| SLPBATHDR       |     1 |    36 |     1   (0)| 00:00:01 |
|* 13 |             INDEX RANGE SCAN          | SLPBATHDR_IND7  |     1 |       |     1   (0)| 00:00:01 |
|* 14 |            INDEX UNIQUE SCAN          | GENAGPMST_UIND1 |     1 |       |     1   (0)| 00:00:01 |
|* 15 |           TABLE ACCESS BY INDEX ROWID | GENAGPMST       |     1 |    12 |     1   (0)| 00:00:01 |
|* 16 |         FILTER                        |                 |       |       |            |          |
|  17 |          NESTED LOOPS                 |                 |     1 |    48 |     2   (0)| 00:00:01 |
|  18 |           NESTED LOOPS                |                 |     1 |    48 |     2   (0)| 00:00:01 |
|  19 |            TABLE ACCESS BY INDEX ROWID| GENAGPMST       |     1 |    12 |     1   (0)| 00:00:01 |
|* 20 |             INDEX RANGE SCAN          | GENAGPMST_IND3  |     1 |       |     1   (0)| 00:00:01 |
|* 21 |            INDEX RANGE SCAN           | SLPBATHDR_IND7  |     1 |       |     1   (0)| 00:00:01 |
|* 22 |           TABLE ACCESS BY INDEX ROWID | SLPBATHDR       |     1 |    36 |     1   (0)| 00:00:01 |
|* 23 |     INDEX RANGE SCAN                  | SLPSALEXC_IND1  |     2 |       |     1   (0)| 00:00:01 |
|* 24 |    TABLE ACCESS BY INDEX ROWID        | SLPSALEXC       |     1 |   101 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      OPT_PARAM('optimizer_index_cost_adj' 10)
      OPT_PARAM('optimizer_index_caching' 100)
      FIRST_ROWS(10)
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SEL$3")
      OUTLINE_LEAF(@"SET$FCA7A018")
      OUTLINE_LEAF(@"SEL$940CEDA2")
      UNNEST(@"SET$1" UNNEST_INNERJ_DISTINCT_VIEW)
      OUTLINE_LEAF(@"SEL$3_1")
      USE_CONCAT(@"SEL$3" 8 OR_PREDICATES(7))
      OUTLINE_LEAF(@"SEL$3_2")
      OUTLINE(@"SET$1")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$3")
      NO_ACCESS(@"SEL$940CEDA2" "VW_NSO_1"@"SEL$940CEDA2")
      INDEX(@"SEL$940CEDA2" "SLPSALEXC"@"SEL$1" ("SLPSALEXC"."BATSEQ"))
      LEADING(@"SEL$940CEDA2" "VW_NSO_1"@"SEL$940CEDA2" "SLPSALEXC"@"SEL$1")
      USE_NL(@"SEL$940CEDA2" "SLPSALEXC"@"SEL$1")
      NLJ_BATCHING(@"SEL$940CEDA2" "SLPSALEXC"@"SEL$1")
      INDEX_RS_ASC(@"SEL$3_1" "A"@"SEL$3" ("SLPBATHDR"."ENDDAT"))
      INDEX(@"SEL$3_1" "B"@"SEL$3" ("GENAGPMST"."AGPCOD"))
      INDEX_RS_ASC(@"SEL$3_2" "B"@"SEL$3_2" ("GENAGPMST"."TEACOD"))
      INDEX(@"SEL$3_2" "A"@"SEL$3_2" ("SLPBATHDR"."ENDDAT"))
      LEADING(@"SEL$3_1" "A"@"SEL$3" "B"@"SEL$3")
      LEADING(@"SEL$3_2" "B"@"SEL$3_2" "A"@"SEL$3_2")
      USE_NL(@"SEL$3_1" "B"@"SEL$3")
      NLJ_BATCHING(@"SEL$3_1" "B"@"SEL$3")
      USE_NL(@"SEL$3_2" "A"@"SEL$3_2")
      NLJ_BATCHING(@"SEL$3_2" "A"@"SEL$3_2")
      END_OUTLINE_DATA
  */
 
Peeked Binds (identified by position):
--------------------------------------
 
   2 - :2 (DATE): (null)
   3 - :3 (DATE): (null)
   4 - :4 (CHAR(30), CSID=178): (null)
   5 - :5 (CHAR(30), CSID=178): (null)
   6 - :6 (CHAR(30), CSID=178): (null)
   7 - :7 (CHAR(30), CSID=178): (null)
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   9 - filter(:7 IS NULL)
  12 - filter(("A"."STADAT">=:2 AND "A"."LOCTYP"=NVL(:4,"A"."LOCTYP") AND 
              "A"."CURCOD"=NVL(:5,"A"."CURCOD") AND "A"."AGPCOD"=NVL(:6,"A"."AGPCOD")))
  13 - access("A"."ENDDAT"<=:3)
  14 - access("A"."AGPCOD"="B"."AGPCOD")
  15 - filter("B"."TEACOD" IS NOT NULL)
  16 - filter(:7 IS NOT NULL)
  20 - access("B"."TEACOD"=:7)
  21 - access("A"."ENDDAT"<=:3)
  22 - filter(("A"."STADAT">=:2 AND "A"."LOCTYP"=NVL(:4,"A"."LOCTYP") AND 
              "A"."CURCOD"=NVL(:5,"A"."CURCOD") AND "A"."AGPCOD"=NVL(:6,"A"."AGPCOD") AND 
              "A"."AGPCOD"="B"."AGPCOD"))
  23 - access("BATSEQ"=":1")
  24 - filter((:9<>'T' OR (:8='T' AND "ERRTYP"='T')))
 
Note
-----
   - cardinality feedback used for this statement
 
SQL_ID  07pfqktr7y6gk, child number 3
-------------------------------------
SELECT ROWID,CARCOD,DOCNUM,ERRTYP,RMK,BATSEQ,APXINKIND,CARNUMCOD,CPNNUM,
REFTRNIDR FROM SLPSALEXC WHERE batseq in (select :1 from dual union 
select a.batseq from genagpmst b,slpbathdr a where a.stadat >= :2 and 
a.enddat <= :3 and a.loctyp=nvl(:4,a.loctyp) and 
a.curcod=nvl(:5,a.curcod) and a.agpcod = b.agpcod and a.agpcod = 
nvl(:6,a.agpcod) and b.teacod = nvl(:7,b.teacod) ) and ((:8='T' and 
errtyp='T') or (:9!='T'))  order by errtyp, docnum
 
Plan hash value: 3840543052
 
--------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                 |       |       |   708 (100)|          |
|   1 |  SORT ORDER BY                       |                 |   400 | 45600 |   708   (1)| 00:00:09 |
|*  2 |   HASH JOIN                          |                 |   400 | 45600 |   707   (1)| 00:00:09 |
|   3 |    VIEW                              | VW_NSO_1        |  5149 | 66937 |   304   (1)| 00:00:04 |
|   4 |     SORT UNIQUE                      |                 |  5149 |   482K|   304   (1)| 00:00:04 |
|   5 |      UNION-ALL                       |                 |       |       |            |          |
|   6 |       FAST DUAL                      |                 |     1 |       |     2   (0)| 00:00:01 |
|   7 |       CONCATENATION                  |                 |       |       |            |          |
|*  8 |        FILTER                        |                 |       |       |            |          |
|*  9 |         HASH JOIN                    |                 |  5147 |   241K|   297   (0)| 00:00:04 |
|* 10 |          TABLE ACCESS FULL           | GENAGPMST       |   552 |  6624 |    12   (0)| 00:00:01 |
|* 11 |          TABLE ACCESS BY INDEX ROWID | SLPBATHDR       |  5921 |   208K|   285   (0)| 00:00:04 |
|* 12 |           INDEX RANGE SCAN           | SLPBATHDR_IND2  |  5921 |       |     3   (0)| 00:00:01 |
|* 13 |        FILTER                        |                 |       |       |            |          |
|  14 |         NESTED LOOPS                 |                 |     1 |    48 |     2   (0)| 00:00:01 |
|  15 |          NESTED LOOPS                |                 |     1 |    48 |     2   (0)| 00:00:01 |
|* 16 |           TABLE ACCESS BY INDEX ROWID| SLPBATHDR       |     1 |    36 |     1   (0)| 00:00:01 |
|* 17 |            INDEX SKIP SCAN           | SLPBATHDR_IND2  |     1 |       |     1   (0)| 00:00:01 |
|* 18 |           INDEX UNIQUE SCAN          | GENAGPMST_UIND1 |     1 |       |     1   (0)| 00:00:01 |
|* 19 |          TABLE ACCESS BY INDEX ROWID | GENAGPMST       |     1 |    12 |     1   (0)| 00:00:01 |
|* 20 |    TABLE ACCESS FULL                 | SLPSALEXC       |  4085 |   402K|   405   (1)| 00:00:05 |
--------------------------------------------------------------------------------------------------------
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      OPT_PARAM('optimizer_index_cost_adj' 10)
      OPT_PARAM('optimizer_index_caching' 100)
      FIRST_ROWS(10)
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SEL$3")
      OUTLINE_LEAF(@"SET$FCA7A018")
      OUTLINE_LEAF(@"SEL$940CEDA2")
      UNNEST(@"SET$1" UNNEST_INNERJ_DISTINCT_VIEW)
      OUTLINE_LEAF(@"SEL$3_1")
      USE_CONCAT(@"SEL$3" 8 OR_PREDICATES(6))
      OUTLINE_LEAF(@"SEL$3_2")
      OUTLINE(@"SET$1")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$3")
      FULL(@"SEL$940CEDA2" "SLPSALEXC"@"SEL$1")
      NO_ACCESS(@"SEL$940CEDA2" "VW_NSO_1"@"SEL$940CEDA2")
      LEADING(@"SEL$940CEDA2" "SLPSALEXC"@"SEL$1" "VW_NSO_1"@"SEL$940CEDA2")
      USE_HASH(@"SEL$940CEDA2" "VW_NSO_1"@"SEL$940CEDA2")
      SWAP_JOIN_INPUTS(@"SEL$940CEDA2" "VW_NSO_1"@"SEL$940CEDA2")
      FULL(@"SEL$3_1" "B"@"SEL$3")
      INDEX_RS_ASC(@"SEL$3_1" "A"@"SEL$3" ("SLPBATHDR"."STADAT" "SLPBATHDR"."AGPCOD"))
      INDEX_SS(@"SEL$3_2" "A"@"SEL$3_2" ("SLPBATHDR"."STADAT" "SLPBATHDR"."AGPCOD"))
      INDEX(@"SEL$3_2" "B"@"SEL$3_2" ("GENAGPMST"."AGPCOD"))
      LEADING(@"SEL$3_1" "B"@"SEL$3" "A"@"SEL$3")
      LEADING(@"SEL$3_2" "A"@"SEL$3_2" "B"@"SEL$3_2")
      USE_HASH(@"SEL$3_1" "A"@"SEL$3")
      USE_NL(@"SEL$3_2" "B"@"SEL$3_2")
      NLJ_BATCHING(@"SEL$3_2" "B"@"SEL$3_2")
      PX_JOIN_FILTER(@"SEL$3_1" "A"@"SEL$3")
      END_OUTLINE_DATA
  */
 
Peeked Binds (identified by position):
--------------------------------------
 
   2 - :2 (DATE): 08/01/2016 00:00:00
   3 - :3 (DATE): 08/31/2016 00:00:00
   4 - :4 (CHAR(30), CSID=178): (null)
   5 - :5 (CHAR(30), CSID=178): (null)
   6 - :6 (CHAR(30), CSID=178): (null)
   7 - :7 (CHAR(30), CSID=178): 'RAC'
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("BATSEQ"=":1")
   8 - filter(:6 IS NULL)
   9 - access("A"."AGPCOD"="B"."AGPCOD")
  10 - filter("B"."TEACOD"=NVL(:7,"B"."TEACOD"))
  11 - filter(("A"."ENDDAT"<=:3 AND "A"."LOCTYP"=NVL(:4,"A"."LOCTYP") AND 
              "A"."CURCOD"=NVL(:5,"A"."CURCOD")))
  12 - access("A"."STADAT">=:2)
       filter("A"."AGPCOD" IS NOT NULL)
  13 - filter(:6 IS NOT NULL)
  16 - filter(("A"."ENDDAT"<=:3 AND "A"."LOCTYP"=NVL(:4,"A"."LOCTYP") AND 
              "A"."CURCOD"=NVL(:5,"A"."CURCOD")))
  17 - access("A"."STADAT">=:2 AND "A"."AGPCOD"=:6)
       filter("A"."AGPCOD"=:6)
  18 - access("A"."AGPCOD"="B"."AGPCOD")
  19 - filter("B"."TEACOD"=NVL(:7,"B"."TEACOD"))
  20 - filter((:9<>'T' OR (:8='T' AND "ERRTYP"='T')))
 
Note
-----
   - cardinality feedback used for this statement

Connor McDonald
August 17, 2016 - 1:14 am UTC

These are interesting

OPT_PARAM('optimizer_index_cost_adj' 10)
OPT_PARAM('optimizer_index_caching' 100)
FIRST_ROWS(10)

These are not defaults, and hence can skew the optimizer decisions.

If this is a customer database, I'd be asking them why they are set.

Follow Up

Mr Baig, August 17, 2016 - 1:50 pm UTC

Thanks a bunch for the feedback
The same parameters are set for other clients also but no one had reported problem.
Do you think rule based optimizer would help?

Follow Up

Mr Baig, August 17, 2016 - 6:02 pm UTC

My bad, overlooked your response on rule based optimizer. Would you mind providing a guide or a document or online material for working with performance issues.
I can understand that there are no particular solution for a problem but just to have a glimpse of it.
Chris Saxon
August 18, 2016 - 2:21 am UTC

This is probably a good paper on the particular topic of locking down a good plan for a problem SQL.

http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-sql-plan-management-11gr2-133099.pdf

Hope this helps.

Mr Baig, August 18, 2016 - 3:52 am UTC

Thanks a lot

Follow Up

Mr Baig, August 23, 2016 - 6:01 pm UTC

In one of the forum I read about v$sql_workarea. Can we figure out something out of this.
select
        sql_id, child_number, estimated_optimal_size,
        total_executions, optimal_executions, onepass_executions, multipasses_executions,
        max_tempseg_size
from
        v$sql_workarea a where sql_id = 'aa8apag2udvkb'


 child_number  estimated_optimal_size         total_executions  optimal_executions  onepass_executions  multipasses_executions         max_tempseg_size
1 9216 10 10 0 0 NULL
1 80896 15 15 0 0 NULL
1 1293312 13 13 0 0 NULL

Chris Saxon
August 24, 2016 - 12:57 am UTC

That is information about how sorting was done for a query. In your case, they were all "optimal", ie, did not spill to disk. But that was expected, because NESTED LOOPS typically will not do a lot of sorting

A reader, August 24, 2016 - 3:35 am UTC

Okay...
I thought it might be because of the order by clause in the query which is taking lot of time and memory. But this is not what I'm thinking.

We are unable to get the trace file as the client is having consultant DBA and the problem can be reported any time (the same report with the same parameters runs pretty fast after sometime) and the DBA would not have enough time to enable trace and find the details.
They have standard Oracle database.

How can we go ahead.
-
baig
Connor McDonald
August 25, 2016 - 3:07 am UTC

Does v$sql_plan_statistics give you any information on the bad SQL's ?

Follow Up

Mr Baig, August 27, 2016 - 6:25 pm UTC

No, nothing is available in v$sql_plan_statistics.

If my understanding is correct this table would get populated only if the sql tracing is switched on. As mentioned in my previous update the DBA from the client side is unable to enable the trace as the problem just persists for 30 minutes (though sometimes it stays longer but as they just have contractor DBA and unable to get his time when needed) and gets fixed automatically.
This is really killing us now.
Please help...
Connor McDonald
August 28, 2016 - 7:21 am UTC

It depends on statistics_level parameter.

If you have the SQL_ID, can you tell the database to trace it when it occurs, eg

alter system set events 'sql_trace [sql:b6z8h59a39gv9]';

Follow Up

Mr Baig, August 29, 2016 - 2:48 am UTC

Thanks for the update. Will try to get the details after enabling the trace for a sql_id

I hope that dbms_xplan.display_cursor gives the actual plan for the sql_id even if the query is still running. If not kindly let me know the process to get the actual plan even if the query is not cached and still running.
Connor McDonald
August 29, 2016 - 4:00 am UTC

Yes it will.

Follow Up

Mr Baig, September 04, 2016 - 11:19 am UTC

Hi Connor,

Still we are unable to get the row source optimizer information but they have shared some statpack statistics. To me it doesn't look useful. let me know if we can get some input from statpack.
Below is snap sent by the DBA.
-----------------------------------------------------------------------------------
  ActSes   %Thread |    SID | EVENT                               | WAIT_CLASS
-----------------------------------------------------------------------------------
    1.00    (100%) |    957 | ON CPU                              | ON CPU

--------------------------------------------------------------
  ActSes   %Thread | PLSQL_OBJE | PLSQL_SUBP | SQL_ID
--------------------------------------------------------------
    1.00    (100%) |            |            | 07pfqktr7y6gk

------------------------------------------------------------------------------------------------------
  ActSes   %Thread | PROGRAM                   | MODULE                    | ACTION
------------------------------------------------------------------------------------------------------
    1.00    (100%) | frmweb@rws-zcs-002.hr-zcs | SLP3009                   |

--  End of ASH snap 1, end=2016-07-20 10:43:14, seconds=50, samples_taken=50, AAS=1


SQL> select * from table(dbms_xplan.display_cursor('07pfqktr7y6gk',null,'ALL'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------                     -----------------------------------------------------------------------------------------------------------------------                     --------------------------------------------------------------
SQL_ID  07pfqktr7y6gk, child number 0
-------------------------------------
SELECT ROWID,CARCOD,DOCNUM,ERRTYP,RMK,BATSEQ,APXINKIND,CARNUMCOD,CPNNUM,
REFTRNIDR FROM SLPSALEXC WHERE batseq in (select :1 from dual union
select a.batseq from genagpmst b,slpbathdr a where a.stadat >= :2 and
a.enddat <= :3 and a.loctyp=nvl(:4,a.loctyp) and
a.curcod=nvl(:5,a.curcod) and a.agpcod = b.agpcod and a.agpcod =
nvl(:6,a.agpcod) and b.teacod = nvl(:7,b.teacod) ) and ((:8='T' and
errtyp='T') or (:9!='T'))  order by errtyp, docnum

Plan hash value: 505003130

---------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                 |       |       |     9 (100)|          |
|   1 |  SORT ORDER BY                        |                 |     1 |   114 |     9  (23)| 00:00:01 |
|   2 |   NESTED LOOPS                        |                 |     1 |   114 |     8  (13)| 00:00:01 |
|   3 |    NESTED LOOPS                       |                 |     6 |   114 |     8  (13)| 00:00:01 |
|   4 |     VIEW                              | VW_NSO_1        |     3 |    39 |     8  (25)| 00:00:01 |
|   5 |      SORT UNIQUE                      |                 |     3 |   144 |     8  (25)| 00:00:01 |
|   6 |       UNION-ALL                       |                 |       |       |            |          |
|   7 |        FAST DUAL                      |                 |     1 |       |     2   (0)| 00:00:01 |
|   8 |        CONCATENATION                  |                 |       |       |            |          |
|*  9 |         FILTER                        |                 |       |       |            |          |
|  10 |          NESTED LOOPS                 |                 |     1 |    48 |     2   (0)| 00:00:01 |
|  11 |           NESTED LOOPS                |                 |     1 |    48 |     2   (0)| 00:00:01 |
|* 12 |            TABLE ACCESS BY INDEX ROWID| SLPBATHDR       |     1 |    36 |     1   (0)| 00:00:01 |
|* 13 |             INDEX RANGE SCAN          | SLPBATHDR_IND7  |     1 |       |     1   (0)| 00:00:01 |
|* 14 |            INDEX UNIQUE SCAN          | GENAGPMST_UIND1 |     1 |       |     1   (0)| 00:00:01 |
|* 15 |           TABLE ACCESS BY INDEX ROWID | GENAGPMST       |     1 |    12 |     1   (0)| 00:00:01 |
|* 16 |         FILTER                        |                 |       |       |            |          |
|  17 |          NESTED LOOPS                 |                 |     1 |    48 |     2   (0)| 00:00:01 |
|  18 |           NESTED LOOPS                |                 |     1 |    48 |     2   (0)| 00:00:01 |
|  19 |            TABLE ACCESS BY INDEX ROWID| GENAGPMST       |     1 |    12 |     1   (0)| 00:00:01 |
|* 20 |             INDEX RANGE SCAN          | GENAGPMST_IND3  |     1 |       |     1   (0)| 00:00:01 |
|* 21 |            INDEX RANGE SCAN           | SLPBATHDR_IND7  |     1 |       |     1   (0)| 00:00:01 |
|* 22 |           TABLE ACCESS BY INDEX ROWID | SLPBATHDR       |     1 |    36 |     1   (0)| 00:00:01 |
|* 23 |     INDEX RANGE SCAN                  | SLPSALEXC_IND1  |     2 |       |     1   (0)| 00:00:01 |
|* 24 |    TABLE ACCESS BY INDEX ROWID        | SLPSALEXC       |     1 |   101 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$940CEDA2
   4 - SET$FCA7A018 / VW_NSO_1@SEL$940CEDA2
   5 - SET$FCA7A018
   7 - SEL$2        / DUAL@SEL$2
   8 - SEL$3
  12 - SEL$3_1      / A@SEL$3
  13 - SEL$3_1      / A@SEL$3
  14 - SEL$3_1      / B@SEL$3
  15 - SEL$3_1      / B@SEL$3
  19 - SEL$3_2      / B@SEL$3_2
  20 - SEL$3_2      / B@SEL$3_2
  21 - SEL$3_2      / A@SEL$3_2
  22 - SEL$3_2      / A@SEL$3_2
  23 - SEL$940CEDA2 / SLPSALEXC@SEL$1
  24 - SEL$940CEDA2 / SLPSALEXC@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

   9 - filter(:7 IS NULL)
  12 - filter(("A"."STADAT">=:2 AND "A"."LOCTYP"=NVL(:4,"A"."LOCTYP") AND
              "A"."CURCOD"=NVL(:5,"A"."CURCOD") AND "A"."AGPCOD"=NVL(:6,"A"."AGPCOD")))
  13 - access("A"."ENDDAT"<=:3)
  14 - access("A"."AGPCOD"="B"."AGPCOD")
  15 - filter("B"."TEACOD" IS NOT NULL)
  16 - filter(:7 IS NOT NULL)
  20 - access("B"."TEACOD"=:7)
  21 - access("A"."ENDDAT"<=:3)
  22 - filter(("A"."STADAT">=:2 AND "A"."LOCTYP"=NVL(:4,"A"."LOCTYP") AND
              "A"."CURCOD"=NVL(:5,"A"."CURCOD") AND "A"."AGPCOD"=NVL(:6,"A"."AGPCOD") AND
              "A"."AGPCOD"="B"."AGPCOD"))
  23 - access("BATSEQ"=":1")
  24 - filter((:9<>'T' OR (:8='T' AND "ERRTYP"='T')))

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=2) "ERRTYP"[CHARACTER,1], "DOCNUM"[NUMBER,22], ROWID[ROWID,10],
       "CARCOD"[VARCHAR2,3], "CPNNUM"[NUMBER,22], "REFTRNIDR"[NUMBER,22], "RMK"[VARCHAR2,2000],
       "BATSEQ"[NUMBER,22], "APXINKIND"[VARCHAR2,1], "CARNUMCOD"[VARCHAR2,3]
   2 - ROWID[ROWID,10], "DOCNUM"[NUMBER,22], "BATSEQ"[NUMBER,22], "CARCOD"[VARCHAR2,3],
       "RMK"[VARCHAR2,2000], "ERRTYP"[CHARACTER,1], "APXINKIND"[VARCHAR2,1], "CARNUMCOD"[VARCHAR2,3],
       "CPNNUM"[NUMBER,22], "REFTRNIDR"[NUMBER,22]
   3 - ROWID[ROWID,10], "BATSEQ"[NUMBER,22]
   4 - ":1"[NUMBER,22]
   5 - (#keys=1) STRDEF[22]
   6 - STRDEF[22]
   8 - "A".ROWID[ROWID,10], "A"."LOCTYP"[CHARACTER,1], "A"."BATSEQ"[NUMBER,22],
       "A"."AGPCOD"[VARCHAR2,7], "A"."CURCOD"[VARCHAR2,3], "A"."STADAT"[DATE,7], "A"."ENDDAT"[DATE,7],
       "B".ROWID[ROWID,10], "B"."AGPCOD"[VARCHAR2,7], "B"."TEACOD"[VARCHAR2,3]
   9 - "A".ROWID[ROWID,10], "A"."LOCTYP"[CHARACTER,1], "A"."BATSEQ"[NUMBER,22],
       "A"."AGPCOD"[VARCHAR2,7], "A"."CURCOD"[VARCHAR2,3], "A"."STADAT"[DATE,7], "A"."ENDDAT"[DATE,7],
       "B".ROWID[ROWID,10], "B"."AGPCOD"[VARCHAR2,7], "B"."TEACOD"[VARCHAR2,3]
  10 - "A".ROWID[ROWID,10], "A"."LOCTYP"[CHARACTER,1], "A"."BATSEQ"[NUMBER,22],
       "A"."AGPCOD"[VARCHAR2,7], "A"."CURCOD"[VARCHAR2,3], "A"."STADAT"[DATE,7], "A"."ENDDAT"[DATE,7],
       "B".ROWID[ROWID,10], "B"."AGPCOD"[VARCHAR2,7], "B"."TEACOD"[VARCHAR2,3]
  11 - "A".ROWID[ROWID,10], "A"."LOCTYP"[CHARACTER,1], "A"."BATSEQ"[NUMBER,22],
       "A"."AGPCOD"[VARCHAR2,7], "A"."CURCOD"[VARCHAR2,3], "A"."STADAT"[DATE,7], "A"."ENDDAT"[DATE,7],
       "B".ROWID[ROWID,10], "B"."AGPCOD"[VARCHAR2,7]
  12 - "A".ROWID[ROWID,10], "A"."LOCTYP"[CHARACTER,1], "A"."BATSEQ"[NUMBER,22],
       "A"."AGPCOD"[VARCHAR2,7], "A"."CURCOD"[VARCHAR2,3], "A"."STADAT"[DATE,7], "A"."ENDDAT"[DATE,7]
  13 - "A".ROWID[ROWID,10], "A"."ENDDAT"[DATE,7]
  14 - "B".ROWID[ROWID,10], "B"."AGPCOD"[VARCHAR2,7]
  15 - "B".ROWID[ROWID,10], "B"."TEACOD"[VARCHAR2,3]
  16 - "B".ROWID[ROWID,10], "B"."AGPCOD"[VARCHAR2,7], "B"."TEACOD"[VARCHAR2,3],
       "A".ROWID[ROWID,10], "A"."LOCTYP"[CHARACTER,1], "A"."BATSEQ"[NUMBER,22],
       "A"."AGPCOD"[VARCHAR2,7], "A"."CURCOD"[VARCHAR2,3], "A"."STADAT"[DATE,7], "A"."ENDDAT"[DATE,7]
  17 - "B".ROWID[ROWID,10], "B"."AGPCOD"[VARCHAR2,7], "B"."TEACOD"[VARCHAR2,3],
       "A".ROWID[ROWID,10], "A"."LOCTYP"[CHARACTER,1], "A"."BATSEQ"[NUMBER,22],
       "A"."AGPCOD"[VARCHAR2,7], "A"."CURCOD"[VARCHAR2,3], "A"."STADAT"[DATE,7], "A"."ENDDAT"[DATE,7]
  18 - "B".ROWID[ROWID,10], "B"."AGPCOD"[VARCHAR2,7], "B"."TEACOD"[VARCHAR2,3],
       "A".ROWID[ROWID,10], "A"."ENDDAT"[DATE,7]
  19 - "B".ROWID[ROWID,10], "B"."AGPCOD"[VARCHAR2,7], "B"."TEACOD"[VARCHAR2,3]
  20 - "B".ROWID[ROWID,10], "B"."TEACOD"[VARCHAR2,3]
  21 - "A".ROWID[ROWID,10], "A"."ENDDAT"[DATE,7]
  22 - "A".ROWID[ROWID,10], "A"."LOCTYP"[CHARACTER,1], "A"."BATSEQ"[NUMBER,22],
       "A"."AGPCOD"[VARCHAR2,7], "A"."CURCOD"[VARCHAR2,3], "A"."STADAT"[DATE,7]
  23 - ROWID[ROWID,10], "BATSEQ"[NUMBER,22]
  24 - ROWID[ROWID,10], "DOCNUM"[NUMBER,22], "CARCOD"[VARCHAR2,3], "RMK"[VARCHAR2,2000],
       "ERRTYP"[CHARACTER,1], "APXINKIND"[VARCHAR2,1], "CARNUMCOD"[VARCHAR2,3], "CPNNUM"[NUMBER,22],
       "REFTRNIDR"[NUMBER,22]


127 rows selected.

Connor McDonald
September 06, 2016 - 2:27 am UTC

Giving us the plan is like doing this:

Customer: "Hey Connor, I need to go to California from Australia"

Connor: "I think it will take 27 minutes to get there by train"

(This is the PLAN...ie, my *estimate* at how long I think it will take).

Customer: "Hey Connor, I've been going for 4 hours, and the train has now entered the ocean, and I'm really wet."

Connor: "Well...that's a pity, because I think it will take 27 minutes to get there by train"

So you can see - you've given me the plan, which is useful to describe what we *hoped* was going to happened, but it's not going to help describe what is *really* happening, because we *know* the plan is causing you problems. We need to know what is *actually* happening (ie, that our train ploughed into the Pacific ocean!).

So we've got some peeked binds....how about your run the query with the problem peeked binds, and run the query yourself in SQL Plus, with the following hint:


SELECT /*+ gather_plan_statistics */ 
  ROWID,CARCOD,DOCNUM,ERRTYP,RMK,BATSEQ,APXINKIND,CARNUMCOD,CPNNUM, 
REFTRNIDR FROM SLPSALEXC 
where ...


and then run:

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'))

when the query is done. That gives us the *actuals*.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library