OraFAQ Forum: Performance Tuning » A oracle query causing 100% cpu utilization and blocking other sessions.

Home » RDBMS Server » Performance Tuning » A oracle query causing 100% cpu utilization and blocking other sessions. (oracle 11g,(11.2.0.3), Sun Solaris)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

A oracle query causing 100% cpu utilization and blocking other sessions. [message #633584]

Sun, 22 February 2015 09:03

RamanaRocks
Messages: 5
Registered: September 2014
Location: india

Junior Member

Hi All,

one query in my database is blocking other sessions. i have generated a explain plan for that, in explain plan i found that, query using 100%cpu that is shown below

COST (%CPU)
-----------
1395 (100)

-> it is using INDEX RANGE SCAN
and too many NESTED LOOPS and PARTITION RANGE ITERATOR

-> 4 tables involved in this query and 3 of them doesn't having current stats
LAST_ANALYZED
-------------
5/2/2008 (so its latest stats are not available)

-> Query using all indexes and indexes are also fine (No Fragmentaion)

can any one help me to reduce the CPU Cost. and why its BLOCKING other sessions.

Thanks in advance.

[Updated on: Sun, 22 February 2015 09:04]

Report message to a moderator

Re: A oracle query causing 100% cpu utilization and blocking other sessions. [message #633586 is a reply to message #633584]

Sun, 22 February 2015 09:21

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>one query in my database is blocking other sessions.

I do NOT believe above is true.
in Oracle, readers do not block writers & writers do not block readers.

>-> it is using INDEX RANGE SCAN and too many NESTED LOOPS and PARTITION RANGE ITERATOR
are you claiming to be smarter than the CBO?

>-> 4 tables involved in this query and 3 of them doesn't having current stats
if table data has not changed, then statics would still be valid

we need a few more details. including the actual & complete SELECT statement
http://www.orafaq.com/forum/mv/msg/84315/433888/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof

> and why its BLOCKING other sessions.
post SQL & results that proves above is true.

Report message to a moderator

Re: A oracle query causing 100% cpu utilization and blocking other sessions. [message #633588 is a reply to message #633584]

Sun, 22 February 2015 09:28

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

query using 100%cpu that is shown below

COST (%CPU)
-----------
1395 (100)

You don't understand and misinterpret what you see.
This extract of explain plan just says that the cost consists in only (100%) CPU NOT that the query used 100% of your server CPU.

Quote:

why its BLOCKING other sessions

If it is a query (SELECT) then it cannot block anyone.

Report message to a moderator

Re: A oracle query causing 100% cpu utilization and blocking other sessions. [message #633593 is a reply to message #633588]

Sun, 22 February 2015 13:55

RamanaRocks
Messages: 5
Registered: September 2014
Location: india

Junior Member

Thank you Mr.Michel Cadot for your valuable information.

yes what you said is exactly correct, this query is SELECT query.

this query have 9 bind variables, is it reason for query running slow?

could you please confirm that, are bind variables reason for performance degradation?

Report message to a moderator

Re: A oracle query causing 100% cpu utilization and blocking other sessions. [message #633594 is a reply to message #633593]

Sun, 22 February 2015 13:58

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

We can know why it is slow only after you post the previously requested details.

Report message to a moderator

Re: A oracle query causing 100% cpu utilization and blocking other sessions. [message #633595 is a reply to message #633593]

Sun, 22 February 2015 14:00

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Yes, no, maybe, it depends.
Read about "bind peeking".:
https://www.google.com/search?hl=en&source=hp&q=oracle+bind+peeking&gbv=2&oq=oracle+bind+peeking
https://docs.oracle.com/apps/search/search.jsp?category=database&product=e11882-01&q=bind+peeking

Report message to a moderator

Re: A oracle query causing 100% cpu utilization and blocking other sessions. [message #633600 is a reply to message #633594]

Mon, 23 February 2015 01:05

RamanaRocks
Messages: 5
Registered: September 2014
Location: india

Junior Member

Explain Plan for the Query


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------
SQL_ID far28bz7gkcqy
--------------------
SELECT TAX_CODE , SUM (AMOUNT_DEFERRED) "AMOUNT" FROM (SELECT /*+
USE_CONCAT  */ EB.RESOURCE_ID , EB.TAX_CODE , EB.AMOUNT_DEFERRED FROM
ITEM_T I , EVENT_BAL_IMPACTS_T EB   WHERE ( ( EB.OBJ_ID0 >  :b1  AND
EB.OBJ_ID0 <=  :b2 )  OR ( EB.OBJ_ID0 >  :b3  AND EB.OBJ_ID0 <=  :b4 )
OR EB.OBJ_ID0 <=  :b5 )  AND EB.AMOUNT_DEFERRED !=  0  AND
EB.RESOURCE_ID =  :b6  AND I.POID_DB IS NOT NULL   AND I.POID_ID0 =
EB.ITEM_OBJ_ID0  AND I.AR_BILLINFO_OBJ_ID0 =  :b7  AND EB.OBJ_ID0 =  (
CASE WHEN BITAND(EB.OBJ_ID0 , 1152921504606846976 )  !=  0  THEN ( CASE
WHEN NOT  EXISTS  (SELECT START_POID_ID0 FROM TMP_UNPROCESSED_EVENTS_T
 WHERE EB.OBJ_ID0 BETWEEN START_POID_ID0 AND END_POID_ID0   )   THEN
EB.OBJ_ID0 ELSE 0  END)  ELSE EB.OBJ_ID0  END)   AND ( ( ( (
I.EFFECTIVE_T =  0 )  OR ( I.EFFECTIVE_T <=  :b8  AND I.STATUS =  1 ) )
 AND ( ( I.BILLINFO_OBJ_ID0 =  I.AR_BILLINFO_OBJ_ID0  AND
I.BILL_OBJ_ID0 =  (SELECT BILL_OBJ_ID0 FROM BILLINFO_T   WHERE POID_ID0
=  I.BILLINFO_OBJ_ID0   ) )  OR ( I.BILLINFO_OBJ_ID0 !=
I.AR_BILLINFO_OBJ_ID0  AND ( I.BILL_OBJ_ID0 NOT  IN (SELECT /*+
push_subq no_merge  */ BILL_OBJ_ID0 FROM BILLINFO_T   WHERE POID_ID0 =
I.BILLINFO_OBJ_ID0   ) AND I.BILL_OBJ_ID0 NOT  IN (SELECT
NEXT_BILL_OBJ_ID0 FROM BILLINFO_T   WHERE POID_ID0 =
I.BILLINFO_OBJ_ID0   )) ) ) )  OR ( I.EFFECTIVE_T >  :b9  AND
I.EFFECTIVE_T <=  :b8  AND I.ARCHIVE_STATUS =  0  AND I.AR_BILL_OBJ_ID0
=  0 ) )   )   GROUP BY TAX_CODE

Plan hash value: 336913729

------------------------------------------------------------------------------------------------------------

----------------------
| Id  | Operation                             | Name                     | Rows  | Bytes | Cost (%CPU)| Time 

    | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------

----------------------
|   0 | SELECT STATEMENT                      |                          |       |       |  1395 (100)|      

    |       |       |
|   1 |  HASH GROUP BY                        |                          |     1 |    79 |            |      

    |       |       |
|   2 |   CONCATENATION                       |                          |       |       |            |      

    |       |       |
|   3 |    FILTER                             |                          |       |       |            |      

    |       |       |
|   4 |     NESTED LOOPS                      |                          |       |       |            |      

    |       |       |
|   5 |      NESTED LOOPS                     |                          |     1 |    74 |    47   (0)| 

00:00:01 |       |       |
|   6 |       PARTITION RANGE ALL             |                          |     1 |    48 |    46   (0)| 

00:00:01 |     1 |    61 |
|   7 |        INDEX RANGE SCAN               | I_ITEM_AR_BNFO_OBJ__ID   |     1 |    48 |    46   (0)| 

00:00:01 |     1 |    61 |
|   8 |         TABLE ACCESS BY INDEX ROWID   | BILLINFO_T               |     1 |    16 |     1   (0)| 

00:00:01 |       |       |
|   9 |          INDEX UNIQUE SCAN            | I_BILLINFO__ID           |     1 |       |     1   (0)| 

00:00:01 |       |       |
|  10 |         TABLE ACCESS BY INDEX ROWID   | BILLINFO_T               |     1 |    16 |     1   (0)| 

00:00:01 |       |       |
|  11 |          INDEX UNIQUE SCAN            | I_BILLINFO__ID           |     1 |       |     1   (0)| 

00:00:01 |       |       |
|  12 |         TABLE ACCESS BY INDEX ROWID   | BILLINFO_T               |     1 |    16 |     1   (0)| 

00:00:01 |       |       |
|  13 |          INDEX UNIQUE SCAN            | I_BILLINFO__ID           |     1 |       |     1   (0)| 

00:00:01 |       |       |
|  14 |       PARTITION RANGE ITERATOR        |                          |     1 |       |     1   (0)| 

00:00:01 |     1 |   KEY |
|  15 |        INDEX RANGE SCAN               | I_EVENT_BI_ITEM_OBJ__ID  |     1 |       |     1   (0)| 

00:00:01 |     1 |   KEY |
|  16 |      TABLE ACCESS BY LOCAL INDEX ROWID| EVENT_BAL_IMPACTS_T      |     1 |    26 |     1   (0)| 

00:00:01 |     1 |     1 |
|  17 |     INDEX RANGE SCAN                  | I_UNPR_EVENTS_ST_END__ID |     1 |    26 |     1   (0)| 

00:00:01 |       |       |
|  18 |    FILTER                             |                          |       |       |            |      

    |       |       |
|  19 |     NESTED LOOPS                      |                          |       |       |            |      

    |       |       |
|  20 |      NESTED LOOPS                     |                          |     1 |    74 |    47   (0)| 

00:00:01 |       |       |
|  21 |       PARTITION RANGE ALL             |                          |     1 |    48 |    46   (0)| 

00:00:01 |     1 |    61 |
|  22 |        INDEX RANGE SCAN               | I_ITEM_AR_BNFO_OBJ__ID   |     1 |    48 |    46   (0)| 

00:00:01 |     1 |    61 |
|  23 |         TABLE ACCESS BY INDEX ROWID   | BILLINFO_T               |     1 |    16 |     1   (0)| 

00:00:01 |       |       |
|  24 |          INDEX UNIQUE SCAN            | I_BILLINFO__ID           |     1 |       |     1   (0)| 

00:00:01 |       |       |
|  25 |         TABLE ACCESS BY INDEX ROWID   | BILLINFO_T               |     1 |    16 |     1   (0)| 

00:00:01 |       |       |
|  26 |          INDEX UNIQUE SCAN            | I_BILLINFO__ID           |     1 |       |     1   (0)| 

00:00:01 |       |       |
|  27 |         TABLE ACCESS BY INDEX ROWID   | BILLINFO_T               |     1 |    16 |     1   (0)| 

00:00:01 |       |       |
|  28 |          INDEX UNIQUE SCAN            | I_BILLINFO__ID           |     1 |       |     1   (0)| 

00:00:01 |       |       |
|  29 |       PARTITION RANGE ITERATOR        |                          |     1 |       |     1   (0)| 

00:00:01 |     1 |   KEY |
|  30 |        INDEX RANGE SCAN               | I_EVENT_BI_ITEM_OBJ__ID  |     1 |       |     1   (0)| 

00:00:01 |     1 |   KEY |
|  31 |      TABLE ACCESS BY LOCAL INDEX ROWID| EVENT_BAL_IMPACTS_T      |     1 |    26 |     1   (0)| 

00:00:01 |     1 |     1 |
|  32 |     INDEX RANGE SCAN                  | I_UNPR_EVENTS_ST_END__ID |     1 |    26 |     1   (0)| 

00:00:01 |       |       |
|  33 |    FILTER                             |                          |       |       |            |      

    |       |       |
|  34 |     NESTED LOOPS                      |                          |       |       |            |      

    |       |       |
|  35 |      NESTED LOOPS                     |                          |     1 |    74 |    47   (0)| 

00:00:01 |       |       |
|  36 |       PARTITION RANGE ALL             |                          |     1 |    48 |    46   (0)| 

00:00:01 |     1 |    61 |
|  37 |        INDEX RANGE SCAN               | I_ITEM_AR_BNFO_OBJ__ID   |     1 |    48 |    46   (0)| 

00:00:01 |     1 |    61 |
|  38 |         TABLE ACCESS BY INDEX ROWID   | BILLINFO_T               |     1 |    16 |     1   (0)| 

00:00:01 |       |       |
|  39 |          INDEX UNIQUE SCAN            | I_BILLINFO__ID           |     1 |       |     1   (0)| 

00:00:01 |       |       |
|  40 |         TABLE ACCESS BY INDEX ROWID   | BILLINFO_T               |     1 |    16 |     1   (0)| 

00:00:01 |       |       |
|  41 |          INDEX UNIQUE SCAN            | I_BILLINFO__ID           |     1 |       |     1   (0)| 

00:00:01 |       |       |
|  42 |         TABLE ACCESS BY INDEX ROWID   | BILLINFO_T               |     1 |    16 |     1   (0)| 

00:00:01 |       |       |
|  43 |          INDEX UNIQUE SCAN            | I_BILLINFO__ID           |     1 |       |     1   (0)| 

00:00:01 |       |       |
|  44 |       PARTITION RANGE ITERATOR        |                          |     1 |       |     1   (0)| 

00:00:01 |     1 |   KEY |
|  45 |        INDEX RANGE SCAN               | I_EVENT_BI_ITEM_OBJ__ID  |     1 |       |     1   (0)| 

00:00:01 |     1 |   KEY |
|  46 |      TABLE ACCESS BY LOCAL INDEX ROWID| EVENT_BAL_IMPACTS_T      |     1 |    26 |     1   (0)| 

00:00:01 |     1 |     1 |
|  47 |     INDEX RANGE SCAN                  | I_UNPR_EVENTS_ST_END__ID |     1 |    26 |     1   (0)| 

00:00:01 |       |       |
|  48 |    FILTER                             |                          |       |       |            |      

    |       |       |
|  49 |     NESTED LOOPS                      |                          |       |       |            |      

    |       |       |
|  50 |      NESTED LOOPS                     |                          |     1 |    79 |   279   (1)| 

00:00:04 |       |       |
|  51 |       PARTITION RANGE ALL             |                          |     1 |    48 |    46   (0)| 

00:00:01 |     1 |    61 |
|  52 |        INDEX RANGE SCAN               | I_ITEM_AR_BNFO_OBJ__ID   |     1 |    48 |    46   (0)| 

00:00:01 |     1 |    61 |
|  53 |         TABLE ACCESS BY INDEX ROWID   | BILLINFO_T               |     1 |    16 |     1   (0)| 

00:00:01 |       |       |
|  54 |          INDEX UNIQUE SCAN            | I_BILLINFO__ID           |     1 |       |     1   (0)| 

00:00:01 |       |       |
|  55 |         TABLE ACCESS BY INDEX ROWID   | BILLINFO_T               |     1 |    16 |     1   (0)| 

00:00:01 |       |       |
|  56 |          INDEX UNIQUE SCAN            | I_BILLINFO__ID           |     1 |       |     1   (0)| 

00:00:01 |       |       |
|  57 |         TABLE ACCESS BY INDEX ROWID   | BILLINFO_T               |     1 |    16 |     1   (0)| 

00:00:01 |       |       |
|  58 |          INDEX UNIQUE SCAN            | I_BILLINFO__ID           |     1 |       |     1   (0)| 

00:00:01 |       |       |
|  59 |       PARTITION RANGE ITERATOR        |                          |   360 |       |   167   (1)| 

00:00:03 |   KEY |   KEY |
|  60 |        INDEX RANGE SCAN               | I_EVENT_BI_ITEM_OBJ__ID  |   360 |       |   167   (1)| 

00:00:03 |   KEY |   KEY |
|  61 |      TABLE ACCESS BY LOCAL INDEX ROWID| EVENT_BAL_IMPACTS_T      |     1 |    31 |   233   (1)| 

00:00:03 |     1 |     1 |
|  62 |     INDEX RANGE SCAN                  | I_UNPR_EVENTS_ST_END__ID |     1 |    26 |     1   (0)| 

00:00:01 |       |       |
|  63 |    FILTER                             |                          |       |       |            |      

    |       |       |
|  64 |     NESTED LOOPS                      |                          |       |       |            |      

    |       |       |
|  65 |      NESTED LOOPS                     |                          |     1 |    79 |   279   (1)| 

00:00:04 |       |       |
|  66 |       PARTITION RANGE ALL             |                          |     1 |    48 |    46   (0)| 

00:00:01 |     1 |    61 |
|  67 |        INDEX RANGE SCAN               | I_ITEM_AR_BNFO_OBJ__ID   |     1 |    48 |    46   (0)| 

00:00:01 |     1 |    61 |
|  68 |         TABLE ACCESS BY INDEX ROWID   | BILLINFO_T               |     1 |    16 |     1   (0)| 

00:00:01 |       |       |
|  69 |          INDEX UNIQUE SCAN            | I_BILLINFO__ID           |     1 |       |     1   (0)| 

00:00:01 |       |       |
|  70 |         TABLE ACCESS BY INDEX ROWID   | BILLINFO_T               |     1 |    16 |     1   (0)| 

00:00:01 |       |       |
|  71 |          INDEX UNIQUE SCAN            | I_BILLINFO__ID           |     1 |       |     1   (0)| 

00:00:01 |       |       |
|  72 |         TABLE ACCESS BY INDEX ROWID   | BILLINFO_T               |     1 |    16 |     1   (0)| 

00:00:01 |       |       |
|  73 |          INDEX UNIQUE SCAN            | I_BILLINFO__ID           |     1 |       |     1   (0)| 

00:00:01 |       |       |
|  74 |       PARTITION RANGE ITERATOR        |                          |   360 |       |   167   (1)| 

00:00:03 |   KEY |   KEY |
|  75 |        INDEX RANGE SCAN               | I_EVENT_BI_ITEM_OBJ__ID  |   360 |       |   167   (1)| 

00:00:03 |   KEY |   KEY |
|  76 |      TABLE ACCESS BY LOCAL INDEX ROWID| EVENT_BAL_IMPACTS_T      |     1 |    31 |   233   (1)| 

00:00:03 |     1 |     1 |
|  77 |     INDEX RANGE SCAN                  | I_UNPR_EVENTS_ST_END__ID |     1 |    26 |     1   (0)| 

00:00:01 |       |       |
|  78 |    FILTER                             |                          |       |       |            |      

    |       |       |
|  79 |     NESTED LOOPS                      |                          |       |       |            |      

    |       |       |
|  80 |      NESTED LOOPS                     |                          |     1 |    79 |   279   (1)| 

00:00:04 |       |       |
|  81 |       PARTITION RANGE ALL             |                          |     1 |    48 |    46   (0)| 

00:00:01 |     1 |    61 |
|  82 |        INDEX RANGE SCAN               | I_ITEM_AR_BNFO_OBJ__ID   |     1 |    48 |    46   (0)| 

00:00:01 |     1 |    61 |
|  83 |         TABLE ACCESS BY INDEX ROWID   | BILLINFO_T               |     1 |    16 |     1   (0)| 

00:00:01 |       |       |
|  84 |          INDEX UNIQUE SCAN            | I_BILLINFO__ID           |     1 |       |     1   (0)| 

00:00:01 |       |       |
|  85 |         TABLE ACCESS BY INDEX ROWID   | BILLINFO_T               |     1 |    16 |     1   (0)| 

00:00:01 |       |       |
|  86 |          INDEX UNIQUE SCAN            | I_BILLINFO__ID           |     1 |       |     1   (0)| 

00:00:01 |       |       |
|  87 |         TABLE ACCESS BY INDEX ROWID   | BILLINFO_T               |     1 |    16 |     1   (0)| 

00:00:01 |       |       |
|  88 |          INDEX UNIQUE SCAN            | I_BILLINFO__ID           |     1 |       |     1   (0)| 

00:00:01 |       |       |
|  89 |       PARTITION RANGE ITERATOR        |                          |   360 |       |   167   (1)| 

00:00:03 |   KEY |   KEY |
|  90 |        INDEX RANGE SCAN               | I_EVENT_BI_ITEM_OBJ__ID  |   360 |       |   167   (1)| 

00:00:03 |   KEY |   KEY |
|  91 |      TABLE ACCESS BY LOCAL INDEX ROWID| EVENT_BAL_IMPACTS_T      |     1 |    31 |   233   (1)| 

00:00:03 |     1 |     1 |
|  92 |     INDEX RANGE SCAN                  | I_UNPR_EVENTS_ST_END__ID |     1 |    26 |     1   (0)| 

00:00:01 |       |       |
|  93 |    FILTER                             |                          |       |       |            |      

    |       |       |
|  94 |     NESTED LOOPS                      |                          |       |       |            |      

    |       |       |
|  95 |      NESTED LOOPS                     |                          |     1 |    79 |   136   (1)| 

00:00:02 |       |       |
|  96 |       PARTITION RANGE ALL             |                          |     1 |    48 |    46   (0)| 

00:00:01 |     1 |    61 |
|  97 |        INDEX RANGE SCAN               | I_ITEM_AR_BNFO_OBJ__ID   |     1 |    48 |    46   (0)| 

00:00:01 |     1 |    61 |
|  98 |         TABLE ACCESS BY INDEX ROWID   | BILLINFO_T               |     1 |    16 |     1   (0)| 

00:00:01 |       |       |
|  99 |          INDEX UNIQUE SCAN            | I_BILLINFO__ID           |     1 |       |     1   (0)| 

00:00:01 |       |       |
| 100 |         TABLE ACCESS BY INDEX ROWID   | BILLINFO_T               |     1 |    16 |     1   (0)| 

00:00:01 |       |       |
| 101 |          INDEX UNIQUE SCAN            | I_BILLINFO__ID           |     1 |       |     1   (0)| 

00:00:01 |       |       |
| 102 |         TABLE ACCESS BY INDEX ROWID   | BILLINFO_T               |     1 |    16 |     1   (0)| 

00:00:01 |       |       |
| 103 |          INDEX UNIQUE SCAN            | I_BILLINFO__ID           |     1 |       |     1   (0)| 

00:00:01 |       |       |
| 104 |       PARTITION RANGE ITERATOR        |                          |   360 |       |    24   (0)| 

00:00:01 |   KEY |   KEY |
| 105 |        INDEX RANGE SCAN               | I_EVENT_BI_ITEM_OBJ__ID  |   360 |       |    24   (0)| 

00:00:01 |   KEY |   KEY |
| 106 |      TABLE ACCESS BY LOCAL INDEX ROWID| EVENT_BAL_IMPACTS_T      |     1 |    31 |    90   (2)| 

00:00:02 |     1 |     1 |
| 107 |     INDEX RANGE SCAN                  | I_UNPR_EVENTS_ST_END__ID |     1 |    26 |     1   (0)| 

00:00:01 |       |       |
| 108 |    FILTER                             |                          |       |       |            |      

    |       |       |
| 109 |     NESTED LOOPS                      |                          |       |       |            |      

    |       |       |
| 110 |      NESTED LOOPS                     |                          |     1 |    79 |   136   (1)| 

00:00:02 |       |       |
| 111 |       PARTITION RANGE ALL             |                          |     1 |    48 |    46   (0)| 

00:00:01 |     1 |    61 |
| 112 |        INDEX RANGE SCAN               | I_ITEM_AR_BNFO_OBJ__ID   |     1 |    48 |    46   (0)| 

00:00:01 |     1 |    61 |
| 113 |         TABLE ACCESS BY INDEX ROWID   | BILLINFO_T               |     1 |    16 |     1   (0)| 

00:00:01 |       |       |
| 114 |          INDEX UNIQUE SCAN            | I_BILLINFO__ID           |     1 |       |     1   (0)| 

00:00:01 |       |       |
| 115 |         TABLE ACCESS BY INDEX ROWID   | BILLINFO_T               |     1 |    16 |     1   (0)| 

00:00:01 |       |       |
| 116 |          INDEX UNIQUE SCAN            | I_BILLINFO__ID           |     1 |       |     1   (0)| 

00:00:01 |       |       |
| 117 |         TABLE ACCESS BY INDEX ROWID   | BILLINFO_T               |     1 |    16 |     1   (0)| 

00:00:01 |       |       |
| 118 |          INDEX UNIQUE SCAN            | I_BILLINFO__ID           |     1 |       |     1   (0)| 

00:00:01 |       |       |
| 119 |       PARTITION RANGE ITERATOR        |                          |   360 |       |    24   (0)| 

00:00:01 |   KEY |   KEY |
| 120 |        INDEX RANGE SCAN               | I_EVENT_BI_ITEM_OBJ__ID  |   360 |       |    24   (0)| 

00:00:01 |   KEY |   KEY |
| 121 |      TABLE ACCESS BY LOCAL INDEX ROWID| EVENT_BAL_IMPACTS_T      |     1 |    31 |    90   (2)| 

00:00:02 |     1 |     1 |
| 122 |     INDEX RANGE SCAN                  | I_UNPR_EVENTS_ST_END__ID |     1 |    26 |     1   (0)| 

00:00:01 |       |       |
| 123 |    FILTER                             |                          |       |       |            |      

    |       |       |
| 124 |     NESTED LOOPS                      |                          |       |       |            |      

    |       |       |
| 125 |      NESTED LOOPS                     |                          |     1 |    79 |   136   (1)| 

00:00:02 |       |       |
| 126 |       PARTITION RANGE ALL             |                          |     1 |    48 |    46   (0)| 

00:00:01 |     1 |    61 |
| 127 |        INDEX RANGE SCAN               | I_ITEM_AR_BNFO_OBJ__ID   |     1 |    48 |    46   (0)| 

00:00:01 |     1 |    61 |
| 128 |         TABLE ACCESS BY INDEX ROWID   | BILLINFO_T               |     1 |    16 |     1   (0)| 

00:00:01 |       |       |
| 129 |          INDEX UNIQUE SCAN            | I_BILLINFO__ID           |     1 |       |     1   (0)| 

00:00:01 |       |       |
| 130 |         TABLE ACCESS BY INDEX ROWID   | BILLINFO_T               |     1 |    16 |     1   (0)| 

00:00:01 |       |       |
| 131 |          INDEX UNIQUE SCAN            | I_BILLINFO__ID           |     1 |       |     1   (0)| 

00:00:01 |       |       |
| 132 |         TABLE ACCESS BY INDEX ROWID   | BILLINFO_T               |     1 |    16 |     1   (0)| 

00:00:01 |       |       |
| 133 |          INDEX UNIQUE SCAN            | I_BILLINFO__ID           |     1 |       |     1   (0)| 

00:00:01 |       |       |
| 134 |       PARTITION RANGE ITERATOR        |                          |   360 |       |    24   (0)| 

00:00:01 |   KEY |   KEY |
| 135 |        INDEX RANGE SCAN               | I_EVENT_BI_ITEM_OBJ__ID  |   360 |       |    24   (0)| 

00:00:01 |   KEY |   KEY |
| 136 |      TABLE ACCESS BY LOCAL INDEX ROWID| EVENT_BAL_IMPACTS_T      |     1 |    31 |    90   (2)| 

00:00:02 |     1 |     1 |
| 137 |     INDEX RANGE SCAN                  | I_UNPR_EVENTS_ST_END__ID |     1 |    26 |     1   (0)| 

00:00:01 |       |       |

find the excecution plan and give me some sujetions

THIS query having 9 bind variables and the initialization parameter

cursor_sharing=EXACT (is there any problem with this?)

[Updated on: Mon, 23 February 2015 01:28] by Moderator

Report message to a moderator

Re: A oracle query causing 100% cpu utilization and blocking other sessions. [message #633607 is a reply to message #633600]

Mon, 23 February 2015 01:31

John Watson
Messages: 8960
Registered: January 2010
Location: Global Village

Senior Member

Ramana, your code is unreadable - even after adding [code] tag, which I did for you. I do not undersand how anyone can work like this. Ask yourself what you would do if someone presented you wth a mess like that.
Then, please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

THen post it all again.

In particular, format you code! and use te [code] tags!

Report message to a moderator

Re: A oracle query causing 100% cpu utilization and blocking other sessions. [message #633608 is a reply to message #633600]

Mon, 23 February 2015 01:31

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

This plan is unreadable, correctly repost it.

Michel Cadot wrote on Sun, 22 February 2015 21:00

Yes, no, maybe, it depends.
Read about "bind peeking".:
https://www.google.com/search?hl=en&source=hp&q=oracle+bind+peeking&gbv=2&oq=oracle+bind+peeking
https://docs.oracle.com/apps/search/search.jsp?category=database&product=e11882-01&q=bind+peeking

Report message to a moderator

Re: A oracle query causing 100% cpu utilization and blocking other sessions. [message #633615 is a reply to message #633608]

Mon, 23 February 2015 03:53

RamanaRocks
Messages: 5
Registered: September 2014
Location: india

Junior Member

Please find the attached file that containg the execution plan for the query.

when i fired the bleow query

SELECT CHILD_NUMBER, EXECUTIONS, BUFFER_GETS, IS_BIND_SENSITIVE AS "BIND_SENSI",
IS_BIND_AWARE AS "BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE"
FROM V$SQL
WHERE SQL_TEXT LIKE 'SELECT TAX_CODE , SUM (AMOUNT_DEFERRED) "AMOUNT" FROM (SELECT /*+%'

CHILD_NUMBER EXECUTIONS BUFFER_GETS BIND_SENSI BIND_AWARE BIND_SHARE
----------- ---------- ----------- ---------- ---------- ----------
0 9 12947575 N N Y

BIND_AWARE=no

is It means oracle optimizer not ware of bind variables?

Attachment: FAQ.txt
(Size: 19.28KB, Downloaded 2215 times)

[Updated on: Mon, 23 February 2015 03:59]

Report message to a moderator

Re: A oracle query causing 100% cpu utilization and blocking other sessions. [message #633617 is a reply to message #633615]

Mon, 23 February 2015 04:00

John Watson
Messages: 8960
Registered: January 2010
Location: Global Village

Senior Member

This is like getting blood out of a stone. I am feeling kind today, so I shall paste in your code and plan, using tags, the way you shoild have done it:

SELECT TAX_CODE , SUM (AMOUNT_DEFERRED) "AMOUNT"
 FROM (SELECT /*+USE_CONCAT  */ EB.RESOURCE_ID , EB.TAX_CODE , EB.AMOUNT_DEFERRED
	 FROM ITEM_T I , EVENT_BAL_IMPACTS_T EB  
              WHERE ( ( EB.OBJ_ID0 >  :b1  AND EB.OBJ_ID0 <=  :b2 )  OR ( EB.OBJ_ID0 >  :b3  AND EB.OBJ_ID0 		<=  :b4)  OR EB.OBJ_ID0 <=  :b5) AND EB.AMOUNT_DEFERRED !=  0  AND EB.RESOURCE_ID =  :b6  		AND I.POID_DB IS NOT NULL   AND I.POID_ID0 = EB.ITEM_OBJ_ID0  AND I.AR_BILLINFO_OBJ_ID0 =  		:b7  AND EB.OBJ_ID0 =( CASE WHEN BITAND(EB.OBJ_ID0 , 1152921504606846976 )  !=  0  THEN 		(CASE WHEN NOT  EXISTS
	               (SELECT START_POID_ID0 FROM TMP_UNPROCESSED_EVENTS_T   WHERE EB.OBJ_ID0 BETWEEN 				START_POID_ID0 AND END_POID_ID0   )   THEN EB.OBJ_ID0 ELSE 0  END)  ELSE 				EB.OBJ_ID0 END)   AND ( ( ( ( I.EFFECTIVE_T =  0 )  OR ( I.EFFECTIVE_T <=  				:b8 AND I.STATUS =  1 ) )  AND ( I.BILL_OBJ_ID0 = 
					 (SELECT /*+  push_subq no_merge  */ DECODE(PAY_TYPE , 10007 , 						LAST_BILL_OBJ_ID0 ,BILL_OBJ_ID0 )  FROM BILLINFO_T   WHERE 							POID_ID0 =  I.BILLINFO_OBJ_ID0




PLAN TABLE OPUTPUT

Plan hash value: 336913729

----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                          |       |       |  1395 (100)|          |       |       |
|   1 |  HASH GROUP BY                        |                          |     1 |    79 |            |          |       |       |
|   2 |   CONCATENATION                       |                          |       |       |            |          |       |       |
|   3 |    FILTER                             |                          |       |       |            |          |       |       |
|   4 |     NESTED LOOPS                      |                          |       |       |            |          |       |       |
|   5 |      NESTED LOOPS                     |                          |     1 |    74 |    47   (0)| 00:00:01 |       |       |
|   6 |       PARTITION RANGE ALL             |                          |     1 |    48 |    46   (0)| 00:00:01 |     1 |    61 |
|   7 |        INDEX RANGE SCAN               | I_ITEM_AR_BNFO_OBJ__ID   |     1 |    48 |    46   (0)| 00:00:01 |     1 |    61 |
|   8 |         TABLE ACCESS BY INDEX ROWID   | BILLINFO_T               |     1 |    16 |     1   (0)| 00:00:01 |       |       |
|   9 |          INDEX UNIQUE SCAN            | I_BILLINFO__ID           |     1 |       |     1   (0)| 00:00:01 |       |       |
|  10 |         TABLE ACCESS BY INDEX ROWID   | BILLINFO_T               |     1 |    16 |     1   (0)| 00:00:01 |       |       |
|  11 |          INDEX UNIQUE SCAN            | I_BILLINFO__ID           |     1 |       |     1   (0)| 00:00:01 |       |       |
|  12 |         TABLE ACCESS BY INDEX ROWID   | BILLINFO_T               |     1 |    16 |     1   (0)| 00:00:01 |       |       |
|  13 |          INDEX UNIQUE SCAN            | I_BILLINFO__ID           |     1 |       |     1   (0)| 00:00:01 |       |       |
|  14 |       PARTITION RANGE ITERATOR        |                          |     1 |       |     1   (0)| 00:00:01 |     1 |   KEY |
|  15 |        INDEX RANGE SCAN               | I_EVENT_BI_ITEM_OBJ__ID  |     1 |       |     1   (0)| 00:00:01 |     1 |   KEY |
|  16 |      TABLE ACCESS BY LOCAL INDEX ROWID| EVENT_BAL_IMPACTS_T      |     1 |    26 |     1   (0)| 00:00:01 |     1 |     1 |
|  17 |     INDEX RANGE SCAN                  | I_UNPR_EVENTS_ST_END__ID |     1 |    26 |     1   (0)| 00:00:01 |       |       |
|  18 |    FILTER                             |                          |       |       |            |          |       |       |
|  19 |     NESTED LOOPS                      |                          |       |       |            |          |       |       |
|  20 |      NESTED LOOPS                     |                          |     1 |    74 |    47   (0)| 00:00:01 |       |       |
|  21 |       PARTITION RANGE ALL             |                          |     1 |    48 |    46   (0)| 00:00:01 |     1 |    61 |
|  22 |        INDEX RANGE SCAN               | I_ITEM_AR_BNFO_OBJ__ID   |     1 |    48 |    46   (0)| 00:00:01 |     1 |    61 |
|  23 |         TABLE ACCESS BY INDEX ROWID   | BILLINFO_T               |     1 |    16 |     1   (0)| 00:00:01 |       |       |
|  24 |          INDEX UNIQUE SCAN            | I_BILLINFO__ID           |     1 |       |     1   (0)| 00:00:01 |       |       |
|  25 |         TABLE ACCESS BY INDEX ROWID   | BILLINFO_T               |     1 |    16 |     1   (0)| 00:00:01 |       |       |
|  26 |          INDEX UNIQUE SCAN            | I_BILLINFO__ID           |     1 |       |     1   (0)| 00:00:01 |       |       |
|  27 |         TABLE ACCESS BY INDEX ROWID   | BILLINFO_T               |     1 |    16 |     1   (0)| 00:00:01 |       |       |
|  28 |          INDEX UNIQUE SCAN            | I_BILLINFO__ID           |     1 |       |     1   (0)| 00:00:01 |       |       |
|  29 |       PARTITION RANGE ITERATOR        |                          |     1 |       |     1   (0)| 00:00:01 |     1 |   KEY |
|  30 |        INDEX RANGE SCAN               | I_EVENT_BI_ITEM_OBJ__ID  |     1 |       |     1   (0)| 00:00:01 |     1 |   KEY |
|  31 |      TABLE ACCESS BY LOCAL INDEX ROWID| EVENT_BAL_IMPACTS_T      |     1 |    26 |     1   (0)| 00:00:01 |     1 |     1 |
|  32 |     INDEX RANGE SCAN                  | I_UNPR_EVENTS_ST_END__ID |     1 |    26 |     1   (0)| 00:00:01 |       |       |
|  33 |    FILTER                             |                          |       |       |            |          |       |       |
|  34 |     NESTED LOOPS                      |                          |       |       |            |          |       |       |
|  35 |      NESTED LOOPS                     |                          |     1 |    74 |    47   (0)| 00:00:01 |       |       |
|  36 |       PARTITION RANGE ALL             |                          |     1 |    48 |    46   (0)| 00:00:01 |     1 |    61 |
|  37 |        INDEX RANGE SCAN               | I_ITEM_AR_BNFO_OBJ__ID   |     1 |    48 |    46   (0)| 00:00:01 |     1 |    61 |
|  38 |         TABLE ACCESS BY INDEX ROWID   | BILLINFO_T               |     1 |    16 |     1   (0)| 00:00:01 |       |       |
|  39 |          INDEX UNIQUE SCAN            | I_BILLINFO__ID           |     1 |       |     1   (0)| 00:00:01 |       |       |
|  40 |         TABLE ACCESS BY INDEX ROWID   | BILLINFO_T               |     1 |    16 |     1   (0)| 00:00:01 |       |       |
|  41 |          INDEX UNIQUE SCAN            | I_BILLINFO__ID           |     1 |       |     1   (0)| 00:00:01 |       |       |
|  42 |         TABLE ACCESS BY INDEX ROWID   | BILLINFO_T               |     1 |    16 |     1   (0)| 00:00:01 |       |       |
|  43 |          INDEX UNIQUE SCAN            | I_BILLINFO__ID           |     1 |       |     1   (0)| 00:00:01 |       |       |
|  44 |       PARTITION RANGE ITERATOR        |                          |     1 |       |     1   (0)| 00:00:01 |     1 |   KEY |
|  45 |        INDEX RANGE SCAN               | I_EVENT_BI_ITEM_OBJ__ID  |     1 |       |     1   (0)| 00:00:01 |     1 |   KEY |
|  46 |      TABLE ACCESS BY LOCAL INDEX ROWID| EVENT_BAL_IMPACTS_T      |     1 |    26 |     1   (0)| 00:00:01 |     1 |     1 |
|  47 |     INDEX RANGE SCAN                  | I_UNPR_EVENTS_ST_END__ID |     1 |    26 |     1   (0)| 00:00:01 |       |       |
|  48 |    FILTER                             |                          |       |       |            |          |       |       |
|  49 |     NESTED LOOPS                      |                          |       |       |            |          |       |       |
|  50 |      NESTED LOOPS                     |                          |     1 |    79 |   279   (1)| 00:00:04 |       |       |
|  51 |       PARTITION RANGE ALL             |                          |     1 |    48 |    46   (0)| 00:00:01 |     1 |    61 |
|  52 |        INDEX RANGE SCAN               | I_ITEM_AR_BNFO_OBJ__ID   |     1 |    48 |    46   (0)| 00:00:01 |     1 |    61 |
|  53 |         TABLE ACCESS BY INDEX ROWID   | BILLINFO_T               |     1 |    16 |     1   (0)| 00:00:01 |       |       |
|  54 |          INDEX UNIQUE SCAN            | I_BILLINFO__ID           |     1 |       |     1   (0)| 00:00:01 |       |       |
|  55 |         TABLE ACCESS BY INDEX ROWID   | BILLINFO_T               |     1 |    16 |     1   (0)| 00:00:01 |       |       |
|  56 |          INDEX UNIQUE SCAN            | I_BILLINFO__ID           |     1 |       |     1   (0)| 00:00:01 |       |       |
|  57 |         TABLE ACCESS BY INDEX ROWID   | BILLINFO_T               |     1 |    16 |     1   (0)| 00:00:01 |       |       |
|  58 |          INDEX UNIQUE SCAN            | I_BILLINFO__ID           |     1 |       |     1   (0)| 00:00:01 |       |       |
|  59 |       PARTITION RANGE ITERATOR        |                          |   360 |       |   167   (1)| 00:00:03 |   KEY |   KEY |
|  60 |        INDEX RANGE SCAN               | I_EVENT_BI_ITEM_OBJ__ID  |   360 |       |   167   (1)| 00:00:03 |   KEY |   KEY |
|  61 |      TABLE ACCESS BY LOCAL INDEX ROWID| EVENT_BAL_IMPACTS_T      |     1 |    31 |   233   (1)| 00:00:03 |     1 |     1 |
|  62 |     INDEX RANGE SCAN                  | I_UNPR_EVENTS_ST_END__ID |     1 |    26 |     1   (0)| 00:00:01 |       |       |
|  63 |    FILTER                             |                          |       |       |            |          |       |       |
|  64 |     NESTED LOOPS                      |                          |       |       |            |          |       |       |
|  65 |      NESTED LOOPS                     |                          |     1 |    79 |   279   (1)| 00:00:04 |       |       |
|  66 |       PARTITION RANGE ALL             |                          |     1 |    48 |    46   (0)| 00:00:01 |     1 |    61 |
|  67 |        INDEX RANGE SCAN               | I_ITEM_AR_BNFO_OBJ__ID   |     1 |    48 |    46   (0)| 00:00:01 |     1 |    61 |
|  68 |         TABLE ACCESS BY INDEX ROWID   | BILLINFO_T               |     1 |    16 |     1   (0)| 00:00:01 |       |       |
|  69 |          INDEX UNIQUE SCAN            | I_BILLINFO__ID           |     1 |       |     1   (0)| 00:00:01 |       |       |
|  70 |         TABLE ACCESS BY INDEX ROWID   | BILLINFO_T               |     1 |    16 |     1   (0)| 00:00:01 |       |       |
|  71 |          INDEX UNIQUE SCAN            | I_BILLINFO__ID           |     1 |       |     1   (0)| 00:00:01 |       |       |
|  72 |         TABLE ACCESS BY INDEX ROWID   | BILLINFO_T               |     1 |    16 |     1   (0)| 00:00:01 |       |       |
|  73 |          INDEX UNIQUE SCAN            | I_BILLINFO__ID           |     1 |       |     1   (0)| 00:00:01 |       |       |
|  74 |       PARTITION RANGE ITERATOR        |                          |   360 |       |   167   (1)| 00:00:03 |   KEY |   KEY |
|  75 |        INDEX RANGE SCAN               | I_EVENT_BI_ITEM_OBJ__ID  |   360 |       |   167   (1)| 00:00:03 |   KEY |   KEY |
|  76 |      TABLE ACCESS BY LOCAL INDEX ROWID| EVENT_BAL_IMPACTS_T      |     1 |    31 |   233   (1)| 00:00:03 |     1 |     1 |
|  77 |     INDEX RANGE SCAN                  | I_UNPR_EVENTS_ST_END__ID |     1 |    26 |     1   (0)| 00:00:01 |       |       |
|  78 |    FILTER                             |                          |       |       |            |          |       |       |
|  79 |     NESTED LOOPS                      |                          |       |       |            |          |       |       |
|  80 |      NESTED LOOPS                     |                          |     1 |    79 |   279   (1)| 00:00:04 |       |       |
|  81 |       PARTITION RANGE ALL             |                          |     1 |    48 |    46   (0)| 00:00:01 |     1 |    61 |
|  82 |        INDEX RANGE SCAN               | I_ITEM_AR_BNFO_OBJ__ID   |     1 |    48 |    46   (0)| 00:00:01 |     1 |    61 |
|  83 |         TABLE ACCESS BY INDEX ROWID   | BILLINFO_T               |     1 |    16 |     1   (0)| 00:00:01 |       |       |
|  84 |          INDEX UNIQUE SCAN            | I_BILLINFO__ID           |     1 |       |     1   (0)| 00:00:01 |       |       |
|  85 |         TABLE ACCESS BY INDEX ROWID   | BILLINFO_T               |     1 |    16 |     1   (0)| 00:00:01 |       |       |
|  86 |          INDEX UNIQUE SCAN            | I_BILLINFO__ID           |     1 |       |     1   (0)| 00:00:01 |       |       |
|  87 |         TABLE ACCESS BY INDEX ROWID   | BILLINFO_T               |     1 |    16 |     1   (0)| 00:00:01 |       |       |
|  88 |          INDEX UNIQUE SCAN            | I_BILLINFO__ID           |     1 |       |     1   (0)| 00:00:01 |       |       |
|  89 |       PARTITION RANGE ITERATOR        |                          |   360 |       |   167   (1)| 00:00:03 |   KEY |   KEY |
|  90 |        INDEX RANGE SCAN               | I_EVENT_BI_ITEM_OBJ__ID  |   360 |       |   167   (1)| 00:00:03 |   KEY |   KEY |
|  91 |      TABLE ACCESS BY LOCAL INDEX ROWID| EVENT_BAL_IMPACTS_T      |     1 |    31 |   233   (1)| 00:00:03 |     1 |     1 |
|  92 |     INDEX RANGE SCAN                  | I_UNPR_EVENTS_ST_END__ID |     1 |    26 |     1   (0)| 00:00:01 |       |       |
|  93 |    FILTER                             |                          |       |       |            |          |       |       |
|  94 |     NESTED LOOPS                      |                          |       |       |            |          |       |       |
|  95 |      NESTED LOOPS                     |                          |     1 |    79 |   136   (1)| 00:00:02 |       |       |
|  96 |       PARTITION RANGE ALL             |                          |     1 |    48 |    46   (0)| 00:00:01 |     1 |    61 |
|  97 |        INDEX RANGE SCAN               | I_ITEM_AR_BNFO_OBJ__ID   |     1 |    48 |    46   (0)| 00:00:01 |     1 |    61 |
|  98 |         TABLE ACCESS BY INDEX ROWID   | BILLINFO_T               |     1 |    16 |     1   (0)| 00:00:01 |       |       |
|  99 |          INDEX UNIQUE SCAN            | I_BILLINFO__ID           |     1 |       |     1   (0)| 00:00:01 |       |       |
| 100 |         TABLE ACCESS BY INDEX ROWID   | BILLINFO_T               |     1 |    16 |     1   (0)| 00:00:01 |       |       |
| 101 |          INDEX UNIQUE SCAN            | I_BILLINFO__ID           |     1 |       |     1   (0)| 00:00:01 |       |       |
| 102 |         TABLE ACCESS BY INDEX ROWID   | BILLINFO_T               |     1 |    16 |     1   (0)| 00:00:01 |       |       |
| 103 |          INDEX UNIQUE SCAN            | I_BILLINFO__ID           |     1 |       |     1   (0)| 00:00:01 |       |       |
| 104 |       PARTITION RANGE ITERATOR        |                          |   360 |       |    24   (0)| 00:00:01 |   KEY |   KEY |
| 105 |        INDEX RANGE SCAN               | I_EVENT_BI_ITEM_OBJ__ID  |   360 |       |    24   (0)| 00:00:01 |   KEY |   KEY |
| 106 |      TABLE ACCESS BY LOCAL INDEX ROWID| EVENT_BAL_IMPACTS_T      |     1 |    31 |    90   (2)| 00:00:02 |     1 |     1 |
| 107 |     INDEX RANGE SCAN                  | I_UNPR_EVENTS_ST_END__ID |     1 |    26 |     1   (0)| 00:00:01 |       |       |
| 108 |    FILTER                             |                          |       |       |            |          |       |       |
| 109 |     NESTED LOOPS                      |                          |       |       |            |          |       |       |
| 110 |      NESTED LOOPS                     |                          |     1 |    79 |   136   (1)| 00:00:02 |       |       |
| 111 |       PARTITION RANGE ALL             |                          |     1 |    48 |    46   (0)| 00:00:01 |     1 |    61 |
| 112 |        INDEX RANGE SCAN               | I_ITEM_AR_BNFO_OBJ__ID   |     1 |    48 |    46   (0)| 00:00:01 |     1 |    61 |
| 113 |         TABLE ACCESS BY INDEX ROWID   | BILLINFO_T               |     1 |    16 |     1   (0)| 00:00:01 |       |       |
| 114 |          INDEX UNIQUE SCAN            | I_BILLINFO__ID           |     1 |       |     1   (0)| 00:00:01 |       |       |
| 115 |         TABLE ACCESS BY INDEX ROWID   | BILLINFO_T               |     1 |    16 |     1   (0)| 00:00:01 |       |       |
| 116 |          INDEX UNIQUE SCAN            | I_BILLINFO__ID           |     1 |       |     1   (0)| 00:00:01 |       |       |
| 117 |         TABLE ACCESS BY INDEX ROWID   | BILLINFO_T               |     1 |    16 |     1   (0)| 00:00:01 |       |       |
| 118 |          INDEX UNIQUE SCAN            | I_BILLINFO__ID           |     1 |       |     1   (0)| 00:00:01 |       |       |
| 119 |       PARTITION RANGE ITERATOR        |                          |   360 |       |    24   (0)| 00:00:01 |   KEY |   KEY |
| 120 |        INDEX RANGE SCAN               | I_EVENT_BI_ITEM_OBJ__ID  |   360 |       |    24   (0)| 00:00:01 |   KEY |   KEY |
| 121 |      TABLE ACCESS BY LOCAL INDEX ROWID| EVENT_BAL_IMPACTS_T      |     1 |    31 |    90   (2)| 00:00:02 |     1 |     1 |
| 122 |     INDEX RANGE SCAN                  | I_UNPR_EVENTS_ST_END__ID |     1 |    26 |     1   (0)| 00:00:01 |       |       |
| 123 |    FILTER                             |                          |       |       |            |          |       |       |
| 124 |     NESTED LOOPS                      |                          |       |       |            |          |       |       |
| 125 |      NESTED LOOPS                     |                          |     1 |    79 |   136   (1)| 00:00:02 |       |       |
| 126 |       PARTITION RANGE ALL             |                          |     1 |    48 |    46   (0)| 00:00:01 |     1 |    61 |
| 127 |        INDEX RANGE SCAN               | I_ITEM_AR_BNFO_OBJ__ID   |     1 |    48 |    46   (0)| 00:00:01 |     1 |    61 |
| 128 |         TABLE ACCESS BY INDEX ROWID   | BILLINFO_T               |     1 |    16 |     1   (0)| 00:00:01 |       |       |
| 129 |          INDEX UNIQUE SCAN            | I_BILLINFO__ID           |     1 |       |     1   (0)| 00:00:01 |       |       |
| 130 |         TABLE ACCESS BY INDEX ROWID   | BILLINFO_T               |     1 |    16 |     1   (0)| 00:00:01 |       |       |
| 131 |          INDEX UNIQUE SCAN            | I_BILLINFO__ID           |     1 |       |     1   (0)| 00:00:01 |       |       |
| 132 |         TABLE ACCESS BY INDEX ROWID   | BILLINFO_T               |     1 |    16 |     1   (0)| 00:00:01 |       |       |
| 133 |          INDEX UNIQUE SCAN            | I_BILLINFO__ID           |     1 |       |     1   (0)| 00:00:01 |       |       |
| 134 |       PARTITION RANGE ITERATOR        |                          |   360 |       |    24   (0)| 00:00:01 |   KEY |   KEY |
| 135 |        INDEX RANGE SCAN               | I_EVENT_BI_ITEM_OBJ__ID  |   360 |       |    24   (0)| 00:00:01 |   KEY |   KEY |
| 136 |      TABLE ACCESS BY LOCAL INDEX ROWID| EVENT_BAL_IMPACTS_T      |     1 |    31 |    90   (2)| 00:00:02 |     1 |     1 |
| 137 |     INDEX RANGE SCAN                  | I_UNPR_EVENTS_ST_END__ID |     1 |    26 |     1   (0)| 00:00:01 |       |       |

but it is still not possible to work with this. You have not formatted the code in a way that makes it readable, and you have not included the predicate section of the execution plan.

It looks very much as though you have still not read the forum guide.

Report message to a moderator

Previous Topic:	Execution plan
Next Topic:	query to data dictionary tables taking long time

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Nov 21 10:19:10 CST 2024