OraFAQ Forum: Performance Tuning » Optimisation of query sql

Home » RDBMS Server » Performance Tuning » Optimisation of query sql (ORacle 9i)

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

Optimisation of query sql [message #630353]

Thu, 25 December 2014 04:00

achraf_ef
Messages: 16
Registered: December 2014
Location: Morocco

Junior Member

Hello guys,

I'm trying to optimize the below request, can you please give some advices :
SELECT /* Array Size 500 - Exec 1*/
1 FROM v_vignette_consomme_carb
where TYPECOUP = 'G'
And CODTYPAC = '4'
And ANNEVIGN = '2013'
And ALFADEBU = 'AK'
and ((numevign BETWEEN 'C13GAK980001'
AND 'C13GAK980100')
OR (numevigf BETWEEN 'C13GAK980001'
AND 'C13GAK980100')
OR ( numevign <= 'C13GAK980001'
AND numevigf >= 'C13GAK980001')
OR ( numevign <= 'C13GAK980100'
AND numevigf >= 'C13GAK980100')
)

v_vignette_consomme_carb is a view which relates 4 tables by union all.

thanks and regards

Report message to a moderator

Re: Optimisation of query sql [message #630355 is a reply to message #630353]

Thu, 25 December 2014 04:33

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

Senior Member
Account Moderator

Please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Forgot:
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals.

[Updated on: Thu, 25 December 2014 04:33]

Report message to a moderator

Re: Optimisation of query sql [message #630361 is a reply to message #630355]

Thu, 25 December 2014 05:36

achraf_ef
Messages: 16
Registered: December 2014
Location: Morocco

Junior Member

Hello,

I need more help about optimisation of this sql query in order to detect overlapping interval quickly.

Thanks and regards.

Report message to a moderator

Re: Optimisation of query sql [message #630363 is a reply to message #630361]

Thu, 25 December 2014 06:45

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

Senior Member
Account Moderator

And if you want help you have to provide the information that we need to help.

Report message to a moderator

Re: Optimisation of query sql [message #630365 is a reply to message #630363]

Thu, 25 December 2014 07:05

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>(ORacle 9i)
is RBO or CBO being used?

why are there no columns listed in the SELECT clause?

> /* Array Size 500 - Exec 1*/

what is the purpose of the comment above; since it seems to devolve from a malformed HINT?

[Updated on: Thu, 25 December 2014 07:16]

Report message to a moderator

Re: Optimisation of query sql [message #630366 is a reply to message #630365]

Thu, 25 December 2014 07:24

achraf_ef
Messages: 16
Registered: December 2014
Location: Morocco

Junior Member

Hello,

Thanks for your reply.

Actually it's RBO.

The purpose of the request is to check if there are overlapping interval or not.

About hint , i found it in this request and i don't really know if it's optimize something.

Regards.

Report message to a moderator

Re: Optimisation of query sql [message #630367 is a reply to message #630366]

Thu, 25 December 2014 07:27

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>About hint , i found it in this request and i don't really know if it's optimize something.

so post both EXPLAIN PLANS; one where hint exists & one where no hint is used.

post the details that Michael directed; otherwise You're On Your Own (YOYO)!

Report message to a moderator

Re: Optimisation of query sql [message #630368 is a reply to message #630367]

Thu, 25 December 2014 07:37

achraf_ef
Messages: 16
Registered: December 2014
Location: Morocco

Junior Member

Here is the execution plan of the request with using hint :


| Id  | Operation                            |  Name                           | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                                 |    13 |   637 |  1062   (3)|       |       |
|   1 |  VIEW                                | V_VIGNETTE_CONSOMME_CARB        |    13 |   637 |            |       |       |
|   2 |   UNION-ALL                          |                                 |       |       |            |       |       |
|   3 |    CONCATENATION                     |                                 |       |       |            |       |       |
|*  4 |     TABLE ACCESS BY LOCAL INDEX ROWID| VIGNETTE_CARBURANT              |     1 |    34 |     4  (25)|     4 |     4 |
|*  5 |      INDEX RANGE SCAN                | I_V_CARB_NUMEVIGF               |     1 |       |     4  (25)|     4 |     4 |
|*  6 |     INDEX RANGE SCAN                 | I_V_CARB_1PAR                   |     1 |    34 |     4  (25)|     4 |     4 |
|*  7 |     INDEX RANGE SCAN                 | I_V_CARB_1PAR                   |     1 |    34 |     4  (25)|     4 |     4 |
|*  8 |     INDEX RANGE SCAN                 | I_V_CARB_1PAR                   |     1 |    34 |     4  (25)|     4 |     4 |
|*  9 |    TABLE ACCESS BY INDEX ROWID       | ECHANGE_VIGNETTE                |     7 |   224 |  1003   (2)|       |       |
|* 10 |     INDEX RANGE SCAN                 | I_ANNVIGN_ECHVIGN               | 46330 |       |   105   (5)|       |       |
|* 11 |    TABLE ACCESS BY INDEX ROWID       | CARBUCARTE_VIGNETTE_REG_DOT     |     1 |    32 |    19   (6)|       |       |
|* 12 |     INDEX RANGE SCAN                 | I1_CARBUCARTE_VIGNETTE_REG_DOT  |   432 |       |     2  (50)|       |       |
|* 13 |    TABLE ACCESS BY INDEX ROWID       | CARBUCARTE_REG_VIGN_FACT        |     1 |    32 |    23   (5)|       |       |
|* 14 |     INDEX RANGE SCAN                 | I1_CARBUCARTE_REG_VIGN_FACT     |   544 |       |     3  (34)|       |       |
-----------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - filter("VIGNETTE_CARBURANT"."TYPECOUP"='G' AND "VIGNETTE_CARBURANT"."VIGNVALI"=0 AND 
              "VIGNETTE_CARBURANT"."ALFADEBU"='AK' AND "VIGNETTE_CARBURANT"."CODTYPAC"=TO_NUMBER(NVL('4',TO_CHAR(UID@!))) AND 
              "VIGNETTE_CARBURANT"."ANNEVIGN"=2013)
   5 - access("VIGNETTE_CARBURANT"."NUMEVIGF">='C13GAK980001' AND "VIGNETTE_CARBURANT"."NUMEVIGF"<='C13GAK980100')
   6 - access("VIGNETTE_CARBURANT"."ANNEVIGN"=2013 AND "VIGNETTE_CARBURANT"."CODTYPAC"=TO_NUMBER(NVL('4',TO_CHAR(UID@!))) 
              AND "VIGNETTE_CARBURANT"."TYPECOUP"='G' AND "VIGNETTE_CARBURANT"."ALFADEBU"='AK' AND 
              "VIGNETTE_CARBURANT"."NUMEVIGN">='C13GAK980001' AND "VIGNETTE_CARBURANT"."VIGNVALI"=0 AND 
              "VIGNETTE_CARBURANT"."NUMEVIGN"<='C13GAK980100')
       filter("VIGNETTE_CARBURANT"."VIGNVALI"=0 AND (LNNVL("VIGNETTE_CARBURANT"."NUMEVIGF"<='C13GAK980100') OR 
              LNNVL("VIGNETTE_CARBURANT"."NUMEVIGF">='C13GAK980001')))
   7 - access("VIGNETTE_CARBURANT"."ANNEVIGN"=2013 AND "VIGNETTE_CARBURANT"."CODTYPAC"=TO_NUMBER(NVL('4',TO_CHAR(UID@!))) 
              AND "VIGNETTE_CARBURANT"."TYPECOUP"='G' AND "VIGNETTE_CARBURANT"."ALFADEBU"='AK' AND 
              "VIGNETTE_CARBURANT"."NUMEVIGF">='C13GAK980100' AND "VIGNETTE_CARBURANT"."VIGNVALI"=0 AND 
              "VIGNETTE_CARBURANT"."NUMEVIGN"<='C13GAK980100')
       filter("VIGNETTE_CARBURANT"."VIGNVALI"=0 AND (LNNVL("VIGNETTE_CARBURANT"."NUMEVIGN"<='C13GAK980100') OR 
              LNNVL("VIGNETTE_CARBURANT"."NUMEVIGN">='C13GAK980001')) AND (LNNVL("VIGNETTE_CARBURANT"."NUMEVIGF"<='C13GAK980100') OR 
              LNNVL("VIGNETTE_CARBURANT"."NUMEVIGF">='C13GAK980001')) AND "VIGNETTE_CARBURANT"."NUMEVIGF">='C13GAK980100')
   8 - access("VIGNETTE_CARBURANT"."ANNEVIGN"=2013 AND "VIGNETTE_CARBURANT"."CODTYPAC"=TO_NUMBER(NVL('4',TO_CHAR(UID@!))) 
              AND "VIGNETTE_CARBURANT"."TYPECOUP"='G' AND "VIGNETTE_CARBURANT"."ALFADEBU"='AK' AND 
              "VIGNETTE_CARBURANT"."NUMEVIGF">='C13GAK980001' AND "VIGNETTE_CARBURANT"."VIGNVALI"=0 AND 
              "VIGNETTE_CARBURANT"."NUMEVIGN"<='C13GAK980001')
       filter("VIGNETTE_CARBURANT"."VIGNVALI"=0 AND (LNNVL("VIGNETTE_CARBURANT"."NUMEVIGN"<='C13GAK980100') OR 
              LNNVL("VIGNETTE_CARBURANT"."NUMEVIGF">='C13GAK980100')) AND (LNNVL("VIGNETTE_CARBURANT"."NUMEVIGN"<='C13GAK980100') OR 
              LNNVL("VIGNETTE_CARBURANT"."NUMEVIGN">='C13GAK980001')) AND (LNNVL("VIGNETTE_CARBURANT"."NUMEVIGF"<='C13GAK980100') OR 
              LNNVL("VIGNETTE_CARBURANT"."NUMEVIGF">='C13GAK980001')) AND "VIGNETTE_CARBURANT"."NUMEVIGF">='C13GAK980001')
   9 - filter("ECHANGE_VIGNETTE"."TYPECOUP"='G' AND "ECHANGE_VIGNETTE"."ALFADEBU"='AK' AND 
              "ECHANGE_VIGNETTE"."CODTYPAC"=TO_NUMBER(NVL('4',TO_CHAR(UID@!))) AND ("ECHANGE_VIGNETTE"."NUMEVIGN"<='C13GAK980001' AND 
              "ECHANGE_VIGNETTE"."NUMEVIGF">='C13GAK980001' OR "ECHANGE_VIGNETTE"."NUMEVIGN"<='C13GAK980100' AND 
              "ECHANGE_VIGNETTE"."NUMEVIGF">='C13GAK980100' OR "ECHANGE_VIGNETTE"."NUMEVIGF"<='C13GAK980100' AND 
              "ECHANGE_VIGNETTE"."NUMEVIGF">='C13GAK980001' OR "ECHANGE_VIGNETTE"."NUMEVIGN"<='C13GAK980100' AND 
              "ECHANGE_VIGNETTE"."NUMEVIGN">='C13GAK980001'))
  10 - access("ECHANGE_VIGNETTE"."ANNEVIGN"=2013)
  11 - filter("CARBUCARTE_VIGNETTE_REG_DOT"."TYPECOUP"='G' AND ("CARBUCARTE_VIGNETTE_REG_DOT"."NUMEVIGF">='C13GAK980001' AND 
              "CARBUCARTE_VIGNETTE_REG_DOT"."NUMEVIGN"<='C13GAK980001' OR "CARBUCARTE_VIGNETTE_REG_DOT"."NUMEVIGF">='C13GAK980100' AND 
              "CARBUCARTE_VIGNETTE_REG_DOT"."NUMEVIGN"<='C13GAK980100' OR "CARBUCARTE_VIGNETTE_REG_DOT"."NUMEVIGN">='C13GAK980001' AND 
              "CARBUCARTE_VIGNETTE_REG_DOT"."NUMEVIGN"<='C13GAK980100' OR "CARBUCARTE_VIGNETTE_REG_DOT"."NUMEVIGF">='C13GAK980001' AND 
              "CARBUCARTE_VIGNETTE_REG_DOT"."NUMEVIGF"<='C13GAK980100') AND "CARBUCARTE_VIGNETTE_REG_DOT"."ALFADEBU"='AK' AND 
              "CARBUCARTE_VIGNETTE_REG_DOT"."CODTYPAC"=TO_NUMBER(NVL('4',TO_CHAR(UID@!))))
  12 - access("CARBUCARTE_VIGNETTE_REG_DOT"."ANNEVIGN"=2013)
  13 - filter("CARBUCARTE_REG_VIGN_FACT"."TYPECOUP"='G' AND ("CARBUCARTE_REG_VIGN_FACT"."NUMEVIGN"<='C13GAK980100' AND 
              "CARBUCARTE_REG_VIGN_FACT"."NUMEVIGN">='C13GAK980001' OR "CARBUCARTE_REG_VIGN_FACT"."NUMEVIGF"<='C13GAK980100' AND 
              "CARBUCARTE_REG_VIGN_FACT"."NUMEVIGF">='C13GAK980001' OR "CARBUCARTE_REG_VIGN_FACT"."NUMEVIGN"<='C13GAK980001' AND 
              "CARBUCARTE_REG_VIGN_FACT"."NUMEVIGF">='C13GAK980001' OR "CARBUCARTE_REG_VIGN_FACT"."NUMEVIGN"<='C13GAK980100' AND 
              "CARBUCARTE_REG_VIGN_FACT"."NUMEVIGF">='C13GAK980100') AND "CARBUCARTE_REG_VIGN_FACT"."ALFADEBU"='AK' AND 
              "CARBUCARTE_REG_VIGN_FACT"."CODTYPAC"=TO_NUMBER(NVL('4',TO_CHAR(UID@!))))
  14 - access("CARBUCARTE_REG_VIGN_FACT"."ANNEVIGN"=2013)




Without using hint :

| Id  | Operation                            |  Name                           | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                                 |    13 |   637 |  1062   (3)|       |       |
|   1 |  VIEW                                | V_VIGNETTE_CONSOMME_CARB        |    13 |   637 |            |       |       |
|   2 |   UNION-ALL                          |                                 |       |       |            |       |       |
|   3 |    CONCATENATION                     |                                 |       |       |            |       |       |
|*  4 |     TABLE ACCESS BY LOCAL INDEX ROWID| VIGNETTE_CARBURANT              |     1 |    34 |     4  (25)|     4 |     4 |
|*  5 |      INDEX RANGE SCAN                | I_V_CARB_NUMEVIGF               |     1 |       |     4  (25)|     4 |     4 |
|*  6 |     INDEX RANGE SCAN                 | I_V_CARB_1PAR                   |     1 |    34 |     4  (25)|     4 |     4 |
|*  7 |     INDEX RANGE SCAN                 | I_V_CARB_1PAR                   |     1 |    34 |     4  (25)|     4 |     4 |
|*  8 |     INDEX RANGE SCAN                 | I_V_CARB_1PAR                   |     1 |    34 |     4  (25)|     4 |     4 |
|*  9 |    TABLE ACCESS BY INDEX ROWID       | ECHANGE_VIGNETTE                |     7 |   224 |  1003   (2)|       |       |
|* 10 |     INDEX RANGE SCAN                 | I_ANNVIGN_ECHVIGN               | 46330 |       |   105   (5)|       |       |
|* 11 |    TABLE ACCESS BY INDEX ROWID       | CARBUCARTE_VIGNETTE_REG_DOT     |     1 |    32 |    19   (6)|       |       |
|* 12 |     INDEX RANGE SCAN                 | I1_CARBUCARTE_VIGNETTE_REG_DOT  |   432 |       |     2  (50)|       |       |
|* 13 |    TABLE ACCESS BY INDEX ROWID       | CARBUCARTE_REG_VIGN_FACT        |     1 |    32 |    23   (5)|       |       |
|* 14 |     INDEX RANGE SCAN                 | I1_CARBUCARTE_REG_VIGN_FACT     |   544 |       |     3  (34)|       |       |
-----------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - filter("VIGNETTE_CARBURANT"."TYPECOUP"='G' AND "VIGNETTE_CARBURANT"."VIGNVALI"=0 AND 
              "VIGNETTE_CARBURANT"."ALFADEBU"='AK' AND "VIGNETTE_CARBURANT"."CODTYPAC"=TO_NUMBER(NVL('4',TO_CHAR(UID@!))) AND 
              "VIGNETTE_CARBURANT"."ANNEVIGN"=2013)
   5 - access("VIGNETTE_CARBURANT"."NUMEVIGF">='C13GAK980001' AND "VIGNETTE_CARBURANT"."NUMEVIGF"<='C13GAK980100')
   6 - access("VIGNETTE_CARBURANT"."ANNEVIGN"=2013 AND "VIGNETTE_CARBURANT"."CODTYPAC"=TO_NUMBER(NVL('4',TO_CHAR(UID@!))) 
              AND "VIGNETTE_CARBURANT"."TYPECOUP"='G' AND "VIGNETTE_CARBURANT"."ALFADEBU"='AK' AND 
              "VIGNETTE_CARBURANT"."NUMEVIGN">='C13GAK980001' AND "VIGNETTE_CARBURANT"."VIGNVALI"=0 AND 
              "VIGNETTE_CARBURANT"."NUMEVIGN"<='C13GAK980100')
       filter("VIGNETTE_CARBURANT"."VIGNVALI"=0 AND (LNNVL("VIGNETTE_CARBURANT"."NUMEVIGF"<='C13GAK980100') OR 
              LNNVL("VIGNETTE_CARBURANT"."NUMEVIGF">='C13GAK980001')))
   7 - access("VIGNETTE_CARBURANT"."ANNEVIGN"=2013 AND "VIGNETTE_CARBURANT"."CODTYPAC"=TO_NUMBER(NVL('4',TO_CHAR(UID@!))) 
              AND "VIGNETTE_CARBURANT"."TYPECOUP"='G' AND "VIGNETTE_CARBURANT"."ALFADEBU"='AK' AND 
              "VIGNETTE_CARBURANT"."NUMEVIGF">='C13GAK980100' AND "VIGNETTE_CARBURANT"."VIGNVALI"=0 AND 
              "VIGNETTE_CARBURANT"."NUMEVIGN"<='C13GAK980100')
       filter("VIGNETTE_CARBURANT"."VIGNVALI"=0 AND (LNNVL("VIGNETTE_CARBURANT"."NUMEVIGN"<='C13GAK980100') OR 
              LNNVL("VIGNETTE_CARBURANT"."NUMEVIGN">='C13GAK980001')) AND (LNNVL("VIGNETTE_CARBURANT"."NUMEVIGF"<='C13GAK980100') OR 
              LNNVL("VIGNETTE_CARBURANT"."NUMEVIGF">='C13GAK980001')) AND "VIGNETTE_CARBURANT"."NUMEVIGF">='C13GAK980100')
   8 - access("VIGNETTE_CARBURANT"."ANNEVIGN"=2013 AND "VIGNETTE_CARBURANT"."CODTYPAC"=TO_NUMBER(NVL('4',TO_CHAR(UID@!))) 
              AND "VIGNETTE_CARBURANT"."TYPECOUP"='G' AND "VIGNETTE_CARBURANT"."ALFADEBU"='AK' AND 
              "VIGNETTE_CARBURANT"."NUMEVIGF">='C13GAK980001' AND "VIGNETTE_CARBURANT"."VIGNVALI"=0 AND 
              "VIGNETTE_CARBURANT"."NUMEVIGN"<='C13GAK980001')
       filter("VIGNETTE_CARBURANT"."VIGNVALI"=0 AND (LNNVL("VIGNETTE_CARBURANT"."NUMEVIGN"<='C13GAK980100') OR 
              LNNVL("VIGNETTE_CARBURANT"."NUMEVIGF">='C13GAK980100')) AND (LNNVL("VIGNETTE_CARBURANT"."NUMEVIGN"<='C13GAK980100') OR 
              LNNVL("VIGNETTE_CARBURANT"."NUMEVIGN">='C13GAK980001')) AND (LNNVL("VIGNETTE_CARBURANT"."NUMEVIGF"<='C13GAK980100') OR 
              LNNVL("VIGNETTE_CARBURANT"."NUMEVIGF">='C13GAK980001')) AND "VIGNETTE_CARBURANT"."NUMEVIGF">='C13GAK980001')
   9 - filter("ECHANGE_VIGNETTE"."TYPECOUP"='G' AND "ECHANGE_VIGNETTE"."ALFADEBU"='AK' AND 
              "ECHANGE_VIGNETTE"."CODTYPAC"=TO_NUMBER(NVL('4',TO_CHAR(UID@!))) AND ("ECHANGE_VIGNETTE"."NUMEVIGN"<='C13GAK980001' AND 
              "ECHANGE_VIGNETTE"."NUMEVIGF">='C13GAK980001' OR "ECHANGE_VIGNETTE"."NUMEVIGN"<='C13GAK980100' AND 
              "ECHANGE_VIGNETTE"."NUMEVIGF">='C13GAK980100' OR "ECHANGE_VIGNETTE"."NUMEVIGF"<='C13GAK980100' AND 
              "ECHANGE_VIGNETTE"."NUMEVIGF">='C13GAK980001' OR "ECHANGE_VIGNETTE"."NUMEVIGN"<='C13GAK980100' AND 
              "ECHANGE_VIGNETTE"."NUMEVIGN">='C13GAK980001'))
  10 - access("ECHANGE_VIGNETTE"."ANNEVIGN"=2013)
  11 - filter("CARBUCARTE_VIGNETTE_REG_DOT"."TYPECOUP"='G' AND ("CARBUCARTE_VIGNETTE_REG_DOT"."NUMEVIGF">='C13GAK980001' AND 
              "CARBUCARTE_VIGNETTE_REG_DOT"."NUMEVIGN"<='C13GAK980001' OR "CARBUCARTE_VIGNETTE_REG_DOT"."NUMEVIGF">='C13GAK980100' AND 
              "CARBUCARTE_VIGNETTE_REG_DOT"."NUMEVIGN"<='C13GAK980100' OR "CARBUCARTE_VIGNETTE_REG_DOT"."NUMEVIGN">='C13GAK980001' AND 
              "CARBUCARTE_VIGNETTE_REG_DOT"."NUMEVIGN"<='C13GAK980100' OR "CARBUCARTE_VIGNETTE_REG_DOT"."NUMEVIGF">='C13GAK980001' AND 
              "CARBUCARTE_VIGNETTE_REG_DOT"."NUMEVIGF"<='C13GAK980100') AND "CARBUCARTE_VIGNETTE_REG_DOT"."ALFADEBU"='AK' AND 
              "CARBUCARTE_VIGNETTE_REG_DOT"."CODTYPAC"=TO_NUMBER(NVL('4',TO_CHAR(UID@!))))
  12 - access("CARBUCARTE_VIGNETTE_REG_DOT"."ANNEVIGN"=2013)
  13 - filter("CARBUCARTE_REG_VIGN_FACT"."TYPECOUP"='G' AND ("CARBUCARTE_REG_VIGN_FACT"."NUMEVIGN"<='C13GAK980100' AND 
              "CARBUCARTE_REG_VIGN_FACT"."NUMEVIGN">='C13GAK980001' OR "CARBUCARTE_REG_VIGN_FACT"."NUMEVIGF"<='C13GAK980100' AND 
              "CARBUCARTE_REG_VIGN_FACT"."NUMEVIGF">='C13GAK980001' OR "CARBUCARTE_REG_VIGN_FACT"."NUMEVIGN"<='C13GAK980001' AND 
              "CARBUCARTE_REG_VIGN_FACT"."NUMEVIGF">='C13GAK980001' OR "CARBUCARTE_REG_VIGN_FACT"."NUMEVIGN"<='C13GAK980100' AND 
              "CARBUCARTE_REG_VIGN_FACT"."NUMEVIGF">='C13GAK980100') AND "CARBUCARTE_REG_VIGN_FACT"."ALFADEBU"='AK' AND 
              "CARBUCARTE_REG_VIGN_FACT"."CODTYPAC"=TO_NUMBER(NVL('4',TO_CHAR(UID@!))))
  14 - access("CARBUCARTE_REG_VIGN_FACT"."ANNEVIGN"=2013)

*BlackSwan added {code} tags. Please do so yourself in the future. http://www.orafaq.com/forum/t/174502/

[Updated on: Thu, 25 December 2014 07:40] by Moderator

Report message to a moderator

Re: Optimisation of query sql [message #630369 is a reply to message #630368]

Thu, 25 December 2014 07:42

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Appear to be the same, but if you really used what was previously posted "/* Array Size 500 - Exec 1*/ " is just a comment & not a valid HINT

post the details that Michael directed; otherwise You're On Your Own (YOYO)!

based upon EXPLAIN PLAN details I would expect query to complete in under 1 second to return the 13 rows.

You have posted no specific evidence about why you believe that any problem actually exists.

[Updated on: Thu, 25 December 2014 07:46]

Report message to a moderator

Re: Optimisation of query sql [message #630370 is a reply to message #630369]

Thu, 25 December 2014 07:56

achraf_ef
Messages: 16
Registered: December 2014
Location: Morocco

Junior Member

What i need is a new approach about this request in order to detect quickly if there are an overlapping intervals.

Actually this resquest is highly used by a lot of users who scan a many interval barcode. The purpose is to avoid entering overaoverlapping interval barcode and ensure better Response time for this query?

Do you understand me ?

Thanks again.

Report message to a moderator

Re: Optimisation of query sql [message #630371 is a reply to message #630370]

Thu, 25 December 2014 08:01

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Do you understand me ?
no

> The purpose is to avoid entering overaoverlapping interval barcode and ensure better Response time for this query?
better than what specifically?

How fast is fast enough?

Query is using INDEX at every opportunity.
At some point in time no additional optimization is possible.

Report message to a moderator

Re: Optimisation of query sql [message #630372 is a reply to message #630353]

Thu, 25 December 2014 08:11

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

Senior Member
Account Moderator

Cross-ref:

http://www.dba-village.com/village/dvp_forum.OpenThread?ThreadIdA=74881&SkipA=0

Report message to a moderator

Re: Optimisation of query sql [message #630373 is a reply to message #630371]

Thu, 25 December 2014 08:17

achraf_ef
Messages: 16
Registered: December 2014
Location: Morocco

Junior Member

Actually, i search the better and the fast way to detect the nearset value to the lower limit of entered interval(in our example C13GAK980001).

So i propose this resquest by supposing that it will no be an interval which contains more than 5000 records, but i'm wondering if there no better proposition

with req as (SELECT
numevign, numevigf FROM SIONT.v_vignette_consomme_carb
where TYPECOUP = 'G'
And CODTYPAC = NVL ('4', UID)
And ANNEVIGN = '2013'
And ALFADEBU = 'AK'
and (to_number(seridebu) between (980001 - 5000) and (980001 + 5000)))

select 1 from req where
((numevign BETWEEN 'C13GAK980001'
AND 'C13GAK980100')
OR (numevigf BETWEEN 'C13GAK980001'
AND 'C13GAK980100')
OR ( numevign <= 'C13GAK980001'
AND numevigf >= 'C13GAK980001')
OR ( numevign <= 'C13GAK980100'
AND numevigf >= 'C13GAK980100'))

Report message to a moderator

Re: Optimisation of query sql [message #630374 is a reply to message #630373]

Thu, 25 December 2014 08:21

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>but i'm wondering if there no better proposition

You are free to wonder & we are free to stop responding since you provide us no useful details.

Does actual query use bind variables; not all hardcoded values as seen in this thread?

>Actually, i search the better and the fast way to detect the nearset value
Faster than what specifically?

Report message to a moderator

Re: Optimisation of query sql [message #630375 is a reply to message #630373]

Thu, 25 December 2014 08:24

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

Senior Member
Account Moderator

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals.

Report message to a moderator

Re: Optimisation of query sql [message #630376 is a reply to message #630374]

Thu, 25 December 2014 08:30

achraf_ef
Messages: 16
Registered: December 2014
Location: Morocco

Junior Member

Yes the actual query use bind variables (from Oracle Forms)

Faster than the way used currently by sweepping out all intervals.

Report message to a moderator

Re: Optimisation of query sql [message #630377 is a reply to message #630376]

Thu, 25 December 2014 08:36

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

So all this time you have been lying to us about the actual problem!

PLONK!
http://en.wikipedia.org/wiki/Plonk_(Usenet)

Report message to a moderator

Re: Optimisation of query sql [message #630378 is a reply to message #630377]

Thu, 25 December 2014 08:43

achraf_ef
Messages: 16
Registered: December 2014
Location: Morocco

Junior Member

i didn't ly to you.

I'm a beginner as dba oracle.

What is the difference about hard coded and bind variables in query performance ?
Sorry for misunderstanding caused
Thanks

Report message to a moderator

Re: Optimisation of query sql [message #630405 is a reply to message #630378]

Fri, 26 December 2014 07:59

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

If what you want is a better understanding of SQL Tuning then try these.

This is a link to the scripts from my new book - "Oracle SQL Performance Tuning and Optimization: Its all about the Cardinalities". These scripts are free for you to use and share. They will help you diagnose performance problems with SQL.

Also, I have attached the first chapter of the book here as well. This too is free and you can share it with anyone. This is the promotional chapter for the book and it is the full chapter #1 so it will teach you about the role of Cardinality in SQL Tuning and provide you with additional tools that aid you in tuning SQL you need to tune. You do NOT need to buy the book in order to use these free scripts and share the free chapter and scripts with others.

Since your query is a view, it would be nice to see the view text.

If you want a full analysis then also post the table descriptions and their indexes (there is a script for the indexes in the script package).

Also, after you read the promotional chapter, please do the work of producing the FRP spreadsheet and then post that here as well.

With this information we will be able to provide more insight to help you. It might even be that you will figure out what is wrong yourself in which case if you do, please post your solution so others can see it and learn from it.

The possible obvious problems with this query are:

1. the concatenation might be bad and you need a concatenated index instead.

2. stats may be bad and your estimates are subsequently off causing you to use an index where you should be using a full table scan.

3. there may be nothing wrong.  You have not provided any runtimes or any description of a desired runtime so there is no way to know if the query is actually underperforming.

#3 above is actually the first thing noted in the chapter provided above. You are supposed to have an idea of how long you need the query to go or at least have some clear indication that the query is not meeting an SLA, before you spend any time to tune the query. If a query is "FAST ENOUGH" then there is no need to tune it. "AS FAST AS POSSIBLE" is not a valid tuning goal.

Kevin

add the scripts file.

Attachment: Oracle SQL Performance Tuning and Optimization v25 chapter 1.pdf
(Size: 780.44KB, Downloaded 2775 times)
Attachment: scripts.rar
(Size: 19.14KB, Downloaded 2236 times)

[Updated on: Sat, 03 January 2015 09:30]

Report message to a moderator

Re: Optimisation of query sql [message #630406 is a reply to message #630353]

Fri, 26 December 2014 08:38

achraf_ef
Messages: 16
Registered: December 2014
Location: Morocco

Junior Member

Hello Kevin,

Thanks a lot for your help.

Currently the elapsed of this query(which is implemented in stored procedure and the literal values used are a bind variables i found in v$sqlarea :"SYS_B_38", :"SYS_B_01", :1 ....)
is about 1.5 s when there is no overlapping intervals(which is the normal case and consequently the most frequent).
I'm aiming to reduce the elapsed time less than 1s because this query is highly used by many users.

beside tuning the quesry i was thinking about modifying the parameter CURSOR_SHARING which have currently the value EXACT, do you think if i modify this parameter with value SIMILAR the QUERY PERFORMANCE will be better ?

Once again thank you a lot for your help and support.

Regards.

Report message to a moderator

Re: Optimisation of query sql [message #630407 is a reply to message #630406]

Fri, 26 December 2014 08:43

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

The CBO in V9 was only marginally better than the RBO it replaced.
The RBO is majorly better in the now supported releases.
I suspect you would hit your performance goal simply be upgrading the DB to a current version.

Report message to a moderator

Re: Optimisation of query sql [message #630409 is a reply to message #630407]

Fri, 26 December 2014 08:52

achraf_ef
Messages: 16
Registered: December 2014
Location: Morocco

Junior Member

Hello,

Here is the view text:

CREATE OR REPLACE FORCE VIEW "V_VIGNETTE_CONSOMME" ("NUMEVIGN", "NUMEVIGF", "CODEROWI", "CODETABL", "CODEVIGN", "CODVIGFI", "TYPECOUP", "CODTYPAC", "ANNEVIGN", "MONTVIGN", "ALFADEBU", "ALFA_FIN", "SERIDEBU", "SERI_FIN", "VIGNVALI") AS
SELECT numevign,
numevigf,
ROWID coderowi,
'VR' CODETABL,
CODEVIGN,
CODVIGFI,
TYPECOUP,
CODTYPAC,
ANNEVIGN,
MONTVIGN,
ALFADEBU,
ALFA_FIN,
SERIDEBU,
SERI_FIN,
vignvali
FROM VIGNETTE_REPARATION
WHERE NVL (VIGNVALI, 0) = 0
UNION ALL
SELECT
numevign,
numevigf,
ROWID coderowi,
'VC' CODETABL,
CODEVIGN,
CODVIGFI,
TYPECOUP,
CODTYPAC,
ANNEVIGN,
MONTVIGN,
ALFADEBU,
ALFA_FIN,
SERIDEBU,
SERI_FIN,
vignvali
FROM VIGNETTE_CARBURANT
WHERE NVL (VIGNVALI, 0) = 0
UNION ALL
SELECT numevign,
numevigf,
ROWID coderowi,
'VT' CODETABL,
CODEVIGN,
CODVIGFI,
TYPECOUP,
CODTYPAC,
ANNEVIGN,
MONTVIGN,
ALFADEBU,
ALFA_FIN,
SERIDEBU,
SERI_FIN,
vignvali
FROM VIGNETTE_BON_TRANSPORT
WHERE NVL (VIGNVALI, 0) = 0
UNION ALL
SELECT numevign,
numevigf,
ROWID coderowi,
'FC' CODETABL,
CODEVIGN,
CODVIGFI,
TYPECOUP,
CODTYPAC,
ANNEVIGN,
MONTVIGN,
ALFADEBU,
ALFA_FIN,
SERIDEBU,
SERI_FIN,
vignvali
FROM VIGNETTE_REGL_FACT_CARN_BON
WHERE NVL (VIGNVALI, 0) = 0
UNION ALL
SELECT numevign,
numevigf,
ROWID coderowi,
'VAT' CODETABL,
CODVIGDE CODEVIGN,
CODVIGFI,
TYPECOUP,
CODTYPAC,
ANNEVIGN,
MONTVIGN,
ALFADEBU,
ALFA_FIN,
SERIDEBU,
SERI_FIN,
vignvali
FROM VIGNETTE_BON_ABON_AUTOR
WHERE NVL (VIGNVALI, 0) = 0
UNION ALL
SELECT numevign,
numevigf,
ROWID coderowi,
'VTICK' CODETABL,
CODVIGDE CODEVIGN,
CODVIGFI,
TYPECOUP,
CODTYPAC,
ANNEVIGN,
MONTVIGN,
ALFADEBU,
ALFA_FIN,
SERIDEBU,
SERI_FIN,
vignvali
FROM REG_VIGN_FACT_TICKET
WHERE NVL (VIGNVALI, 0) = 0
UNION ALL
SELECT numevign,
numevigf,
ROWID coderowi,
'VA' CODETABL,
CODEVIGN,
CODVIGFI,
TYPECOUP,
CODTYPAC,
ANNEVIGN,
MONTVIGN,
ALFADEBU,
ALFA_FIN,
SERIDEBU,
SERI_FIN,
vignvali
FROM VIGNETTE_REGL_FACT_AUTRE_CARN
WHERE NVL (VIGNVALI, 0) = 0
UNION ALL
SELECT numevign,
numevigf,
ROWID coderowi,
'VE' CODETABL,
CODEVIGN,
CODVIGFI,
TYPECOUP,
CODTYPAC,
ANNEVIGN,
MONTECHA MONTVIGN,
ALFADEBU,
ALFA_FIN,
SERIDEBU,
SERI_FIN,
0 vignvali
FROM ECHANGE_VIGNETTE
WHERE ANNEVIGN >= 2011
UNION ALL
SELECT numevign,
numevigf,
ROWID coderowi,
'VDOT' CODETABL,
CODEVIGN,
CODVIGFI,
TYPECOUP,
CODTYPAC,
ANNEVIGN,
MONTVIGN,
ALFADEBU,
ALFA_FIN,
SERIDEBU,
SERI_FIN,
0 vignvali
FROM CARBUCARTE_VIGNETTE_REG_DOT
WHERE ANNEVIGN >= 2011

UNION ALL
SELECT numevign,
numevigf,
ROWID coderowi,
'VFACTCARBCART' CODETABL,
CODVIGDE CODEVIGN,
CODVIGFI CODVIGFI,
TYPECOUP,
CODTYPAC,
ANNEVIGN,
MONTVIGN,
ALFADEBU,
ALFA_FIN,
SERIDEBU,
SERI_FIN,
0 vignvali
FROM CARBUCARTE_REG_VIGN_FACT
WHERE ANNEVIGN >= 2011
UNION ALL
SELECT numevign,
numevigf,
ROWID coderowi,
'VI' CODETABL,
CODVIGDE CODEVIGN,
CODVIGFI,
TYPECOUP,
CODTYPAC,
ANNEVIGN,
MONTVIGN,
ALFADEBU,
ALFA_FIN,
SERIDEBU,
SERI_FIN,
vignvali
FROM REG_VIGN_FACT_CARTES
WHERE NVL (VIGNVALI, 0) = 0
UNION ALL
SELECT numevign,
numevigf,
ROWID coderowi,
'VR' CODETABL,
CODEVIGN,
CODVIGFI,
TYPECOUP,
CODTYPAC,
ANNEVIGN,
MONTVIGN,
ALFADEBU,
ALFA_FIN,
SERIDEBU,
SERI_FIN,
vignvali
FROM VIGNETTE_VISITET
WHERE NVL (VIGNVALI, 0) = 0;

Report message to a moderator

Re: Optimisation of query sql [message #630412 is a reply to message #630409]

Fri, 26 December 2014 11:13

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

Senior Member
Account Moderator

Michel Cadot wrote on Thu, 25 December 2014 15:24

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals.

Report message to a moderator

Re: Optimisation of query sql [message #630413 is a reply to message #630353]

Fri, 26 December 2014 11:23

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

Better. So now we also need to see the indexes for the tables in this view. Try using the SHOWINDEXES.SQL script library I pointed you to.

Also, start using the code tags. Notice that when you are typing into these windows to make a post, above the text there are buttons you can use. One of the buttons looks like this {..}. Highlight your code (all lines you want as a group) then press that button. This will put the code tags around the highlight text and thus keep the formatting that you had when you pasted it in. Otherwise your formatting will be lost. This of course means you need to format your code first. Try using Toad or this website.

Kevin

Report message to a moderator

Re: Optimisation of query sql [message #630414 is a reply to message #630406]

Fri, 26 December 2014 12:21

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

achraf_ef wrote on Fri, 26 December 2014 08:38

Currently the elapsed of this query(which is implemented in stored procedure and the literal values used are a bind variables i found in v$sqlarea :"SYS_B_38", :"SYS_B_01", :1 ....)
is about 1.5 s when there is no overlapping intervals(which is the normal case and consequently the most frequent).

So what is it when there ARE "overlapping intervals"?
How often does that occur?
How does that impact the business?

Quote:

I'm aiming to reduce the elapsed time less than 1s because this query is highly used by many users.

How much effort and time (which equals expense) is justified by saving half a second? Would the end user even notice? How much more work could the end user get done in a day if this query ran half a second faster?

[Updated on: Fri, 26 December 2014 12:40]

Report message to a moderator

Re: Optimisation of query sql [message #630476 is a reply to message #630353]

Mon, 29 December 2014 02:44

achraf_ef
Messages: 16
Registered: December 2014
Location: Morocco

Junior Member

Hello,

>So what is it when there ARE "overlapping intervals"?
There is an application message (in oracle forms) which inform user that is he is entering an existing interval
>How often does that occur?
if we can say by pourcentage : 5%
>How does that impact the business?
no impact this a case which is handled, but what i'm trying the enhance is the normal case (no overlapping intervals), and i try to enhance performance of runtime of the query because it's used by many users at the same time .

Can you give me please some advices(how to avoid lock for example ....?)

Thanks.

Report message to a moderator

Re: Optimisation of query sql [message #630504 is a reply to message #630476]

Mon, 29 December 2014 07:47

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

achraf_ef wrote on Mon, 29 December 2014 02:44

but what i'm trying the enhance is the normal case (no overlapping intervals), and i try to enhance performance of runtime of the query because it's used by many users at the same time .

Can you give me please some advices(how to avoid lock for example ....?)

Thanks.

This is the first time you've mentioned a lock. Are you actually getting locks or are you just assuming it is possible? Is your task to eliminate actual locks that are occurring, or is it to save that 1/2 second for the end user?

Up to this point, your description of this situation is only two cases. In the first case the user enters invalid data and gets an appropriate error message and you seem to indicate that we can ignore that case. The second case the query runs in 1.5 seconds and your goal is to get it under one second. So again, how much expense is it worth to the organization to save 1/2 second? What is the return on investment for that effort? Will the user even notice if/when you "succeed"? Will he be able to accumulate the saved time into a useful single block of time to allow him to complete other tasks that he is not able to complete today? Computing from the company's cost to employ the end user, how many 1/2 seconds would have to be reclaimed to pay their cost to employ you for the amount of time you've already spent on this? And even that calculation is assuming the reclaimed 1/2 could be turned into other useful work.

[Updated on: Mon, 29 December 2014 07:50]

Report message to a moderator

Re: Optimisation of query sql [message #630507 is a reply to message #630476]

Mon, 29 December 2014 08:48

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Can you give me please some advices(how to avoid lock for example ....?)
plain SELECT does not generate any lock; so there is nothing to avoid.

http://en.wikipedia.org/wiki/Snipe_hunt

Report message to a moderator

Re: Optimisation of query sql [message #630509 is a reply to message #630504]

Mon, 29 December 2014 08:50

achraf_ef
Messages: 16
Registered: December 2014
Location: Morocco

Junior Member

>This is the first time you've mentioned a lock. Are you actually getting locks or are you just assuming it is possible

I'm just assuming it's an option

> Is your task to eliminate actual locks that are occurring, or is it to save that 1/2 second for the end user?
Both of them and any action that will help me to enhance the performance of database.

After reflexion i find that you're rigth about the profit if we save 0.5 seconds per execution per user.
But the users are complaining about slowness of each time when users are perfoming the operation of entering intervals of data.

Do you think if i modify the value of parameter cursor_sharing ='SIMILAR' (current value is exact) the performance will be better.
FYI Currently OPTIMIZER_MODE = CHOOSE

Thanks a lot for your support

Report message to a moderator

Re: Optimisation of query sql [message #630510 is a reply to message #630507]

Mon, 29 December 2014 08:52

achraf_ef
Messages: 16
Registered: December 2014
Location: Morocco

Junior Member

Hi,

thats because i'm testting in database of test.

Thanks

Report message to a moderator

Re: Optimisation of query sql [message #630511 is a reply to message #630510]

Mon, 29 December 2014 08:58

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>But the users are complaining about slowness of each time when users are perfoming the operation of entering intervals of data.

like INSERT statement?
what SQL is involved during this "slowness"
ALTER SESSION SET SQL_TRACE=TRUE to see where elapsed time is actually being spent.

Report message to a moderator

Re: Optimisation of query sql [message #630522 is a reply to message #630509]

Mon, 29 December 2014 16:00

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

achraf_ef wrote on Mon, 29 December 2014 08:50

>This is the first time you've mentioned a lock. Are you actually getting locks or are you just assuming it is possible

I'm just assuming it's an option

fundamental rule of Oracle: Readers don't block writers and writers don't block readers. There is no way a SELECT statement will cause a blocking lock or be blocked by a lock.

Report message to a moderator

Re: Optimisation of query sql [message #630523 is a reply to message #630509]

Mon, 29 December 2014 16:05

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

achraf_ef wrote on Mon, 29 December 2014 08:50

>
After reflexion i find that you're rigth about the profit if we save 0.5 seconds per execution per user.
But the users are complaining about slowness of each time when users are perfoming the operation of entering intervals of data.

I find it hard to believe users are complaining about a query with 1.5 second response time. I find it hard to believe they'd stop complaining - or even perceive - if that time went from 1.5 seconds to 0.99 seconds.

Quote:

Do you think if i modify the value of parameter cursor_sharing ='SIMILAR' (current value is exact) the performance will be better.
FYI Currently OPTIMIZER_MODE = CHOOSE

On what basis would you expect that to improve performance for that query? And not have a negative impact for other operations? Sounds like a case of 'ready, fire, AIM'.

Report message to a moderator

Re: Optimisation of query sql [message #630525 is a reply to message #630523]

Mon, 29 December 2014 16:26

achraf_ef
Messages: 16
Registered: December 2014
Location: Morocco

Junior Member

Hello,
Should i try this action?
By the way i update the statistics for the schema(last update was performed in 10/2013) and the perfomance of the query was enhanced.
Regards

Report message to a moderator

Re: Optimisation of query sql [message #630528 is a reply to message #630525]

Mon, 29 December 2014 17:08

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

achraf_ef wrote on Mon, 29 December 2014 14:26

Hello,
Should i try this action?
By the way i update the statistics for the schema(last update was performed in 10/2013) and the perfomance of the query was enhanced.
Regards

So is your perceived problem now solved?

Report message to a moderator

Re: Optimisation of query sql [message #630530 is a reply to message #630525]

Mon, 29 December 2014 19:24

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

achraf_ef wrote on Mon, 29 December 2014 16:26

Hello,
Should i try this action?
By the way i update the statistics for the schema(last update was performed in 10/2013) and the perfomance of the query was enhanced.
Regards

What does it cost to try it and see for yourself how it impacts?

That said, the preferred method of performance tuning is to identify the actual bottleneck then select a solution appropriate to that actual, measured, demonstrated bottleneck. IT sounds like you are just grasping at random techniques. What lead you to want to ask about cursor_sharing instead db-multiblock_read, or sga_target, or adding an index to your table, or upping the priority of the disks within the SAN architecture, or .... or ...... or ......

But I still question if you have an actual problem to solve. It sounds like you suffer from Compulsive Tuning Disorder. If you worked for me, I'd tell you in no uncertain terms to find something more productive to work on, as you have already cost me (your employer) more money in wasted time that will ever be recovered by tuning this query.

Report message to a moderator

Previous Topic:	Monitor performance
Next Topic:	LNS wait on SENDREQ+ARCH wait on SENDREQ on ASYNC dataguard at primary database

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Feb 02 17:50:57 CST 2025