Home » SQL & PL/SQL » SQL & PL/SQL » performance tuning / optimization (merged 3) (oracle 10.2.0.1.0)
performance tuning / optimization (merged 3) [message #426104] Wed, 14 October 2009 02:08 Go to next message
mohan1760
Messages: 59
Registered: June 2008
Member
can you optimize this query for me....

Thanks in advance..

SELECT AWB_NUMBER,
NVL((SELECT 'AWB'
FROM T_AIR_WAYBILL TAWB
WHERE TAWB.AWB_NUMBER = TUAWB.AWB_NUMBER),

NVL((SELECT 'LOD'
FROM t_Awb_Load_Table TLOD
WHERE ' ' || TRIM(TLOD.AIRLINE_PREFIX) ||
TRIM(TO_CHAR(TLOD.serial_number, '00000000')) || ' ' =
TUAWB.AWB_NUMBER AND ROWNUM < 2),
'MAN')) AWB_TYPE

FROM (SELECT AWB_NUMBER
FROM T_AIR_WAYBILL T
WHERE T.AWB_LIFE_CYCLE_STA != 'PRORATED'
AND T.AWB_LIFE_CYCLE_STA != 'IL BILLED'
AND T.AWB_LIFE_CYCLE_STA != 'LOCKED'
UNION
SELECT ' ' || TRIM(T1.AIRLINE_PREFIX) ||
TRIM(TO_CHAR(t1.serial_number, '00000000')) || ' ' AWB_NUMBER
FROM T_AWB_LOAD_TABLE T1
WHERE t1.status = 'REJECT'
AND t1.serial_number != 0
UNION
SELECT AWB_NUMBER
FROM T_FLIGHT_MANIFEST_ITEM
WHERE STATUS = 'U'
OR STATUS = 'S') TUAWB

Re: optimization [message #426107 is a reply to message #426104] Wed, 14 October 2009 02:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No unless you post the usual and required information.
Read:
- OraFAQ Forum Guide, Performance Tuning section
- Performances Tuning sticky

Regards
Michel
Re: optimization [message #426124 is a reply to message #426104] Wed, 14 October 2009 02:49 Go to previous messageGo to next message
balakrishnay
Messages: 54
Registered: September 2009
Location: Pune
Member

Hi,

Give us the execution plan .. take the execution plan with autotrace i.e ( set autotrace traceonly explain ).

give us the very basic info about the tables i.e( num_rows,distinct rows etc.)

Only then we will be able to help you out.


Regards

Bala
Re: performance tuning [message #426152 is a reply to message #426104] Wed, 14 October 2009 03:55 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
No. It's impossible.

You would need to at least provide the information detailed in the sticky post of the performance tuning forum.
icon13.gif  Re: performance tuning [message #426153 is a reply to message #426104] Wed, 14 October 2009 03:58 Go to previous messageGo to next message
balakrishnay
Messages: 54
Registered: September 2009
Location: Pune
Member
Mohan ,

What happend to this thread .... pls do not waste our time .. try to understand the other people who follows your thread for answering.

http://www.orafaq.com/forum/t/151021/0/

Regards

Bala
Re: optimization [message #426162 is a reply to message #426124] Wed, 14 October 2009 04:25 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
1. Is statsistics upto date? Verify last_analyzed column in user tables and also user_indexes
2. What is the show plan results?

Explain plan for
[ your sql query here. i.e, Select 1 from dual;]
Select * from table(dbms_xplan.display);


3. what are the indexes created on the tables used in the sql.
[Look at User_indexes]
4. How many rows are there in those tables.
5. You know your dats so you canverify the performance rebuilding the sql

And please follow the adivice
Quote:

No unless you post the usual and required information.
Read:
- OraFAQ Forum Guide, Performance Tuning section
- Performances Tuning sticky

Regards
Michel

[Updated on: Wed, 14 October 2009 04:27]

Report message to a moderator

Re: performance tuning / optimization (merged 3) [message #426165 is a reply to message #426104] Wed, 14 October 2009 04:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Stop repeating the same question in all forums, you will have the same answer.
Now post what we asked you.

Regards
Michel
Re: performance tuning / optimization (merged 3) [message #426207 is a reply to message #426165] Wed, 14 October 2009 07:04 Go to previous messageGo to next message
mohan1760
Messages: 59
Registered: June 2008
Member
IN THIS THERE ARE THREE TABLES.
TO capture awb_number priority is that it is taken in the following order if awb number is exists in all three tables
1. t_air_waybill
2. t_awb_load_table (awb number is taken by concatinating airline_prefix and serial_number)
3. t_flight_manifest_item

now this query adds column value AWB if the awb_number comes from t_air_waybill,LOD if awb_number comes from t_awb_load_table
and MAN IF if awb_number comes from t_flight_manifest_item

this is what i have tried but record count is wrong....

SELECT awb_number,'AWB'
FROM t_air_waybill t
WHERE t.awb_life_cycle_sta != 'PRORATED'
AND t.awb_life_cycle_sta != 'IL BILLED'
AND t.awb_life_cycle_sta != 'LOCKED'
UNION
SELECT ' '
|| TRIM (t1.airline_prefix)
|| TRIM (TO_CHAR (t1.serial_number, '00000000'))
|| ' ' awb_number,'LOD'
FROM t_awb_load_table t1
WHERE t1.status = 'REJECT' AND t1.serial_number != 0
AND ROWNUM<2
UNION
SELECT awb_number,'MAN'
FROM t_flight_manifest_item
WHERE status = 'U' OR status = 'S'
Re: performance tuning / optimization (merged 3) [message #426208 is a reply to message #426207] Wed, 14 October 2009 07:06 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Do you actually read the answers you get?
Re: performance tuning / optimization (merged 3) [message #426223 is a reply to message #426207] Wed, 14 October 2009 07:58 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Based on the information provided, the only thing I can suggest is to change the UNION statements to UNION ALL as the records that you return are all distinct.

Other than that, please supply (at the least) an explain plan and details of the indices available on the tables.
Re: performance tuning / optimization (merged 3) [message #426229 is a reply to message #426207] Wed, 14 October 2009 08:44 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Quote:

this is what i have tried but record count is wrong....



You need to build a query that returns correct result first and then then try to optimize.
It would always if you keep in mind what are the indexes
for the tables you gonna use in the query.

You need to provide the information that you have been asked for that will help you to get the answer you are expecting from this forum.

Re: performance tuning / optimization (merged 3) [message #426323 is a reply to message #426229] Wed, 14 October 2009 23:57 Go to previous messageGo to next message
mohan1760
Messages: 59
Registered: June 2008
Member
sorry i am completely new at optimisation

details are as follows:



SQL> select * from T_AIR_WAYBILL;

Execution Plan
----------------------------------------------------------
Plan hash value: 1564155746

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

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

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

| 0 | SELECT STATEMENT | | 45801 | 38M| 1261 (1)| 00:00:1
6 |

| 1 | TABLE ACCESS FULL| T_AIR_WAYBILL | 45801 | 38M| 1261 (1)| 00:00:1
6 |

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


SQL> select * from T_AWB_LOAD_TABLE;

Execution Plan
----------------------------------------------------------
Plan hash value: 1071002316

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

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

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

| 0 | SELECT STATEMENT | | 26252 | 39M| 1466 (1)| 00:0
0:18 |

| 1 | TABLE ACCESS FULL| T_AWB_LOAD_TABLE | 26252 | 39M| 1466 (1)| 00:0
0:18 |

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


SQL> select * from T_FLIGHT_MANIFEST_ITEM;

Execution Plan
----------------------------------------------------------
Plan hash value: 3324987787

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

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


Re: performance tuning / optimization (merged 3) [message #426324 is a reply to message #426229] Thu, 15 October 2009 00:02 Go to previous messageGo to next message
mohan1760
Messages: 59
Registered: June 2008
Member
explain plan for queries:

original query:
SQL> SELECT AWB_NUMBER,
  2         NVL((SELECT 'AWB'
  3               FROM T_AIR_WAYBILL TAWB
  4              WHERE TAWB.AWB_NUMBER = TUAWB.AWB_NUMBER),
  5             NVL((SELECT 'LOD'
  6                   FROM t_Awb_Load_Table TLOD
  7                  WHERE ' ' || TRIM(TLOD.AIRLINE_PREFIX) ||
  8                        TRIM(TO_CHAR(TLOD.serial_number, '00000000')) || ' ' =
  9                        TUAWB.AWB_NUMBER AND ROWNUM < 2),
 10                 'MAN')) AWB_TYPE
 11  FROM (SELECT AWB_NUMBER
 12            FROM T_AIR_WAYBILL T
 13           WHERE T.AWB_LIFE_CYCLE_STA != 'PRORATED'
 14             AND T.AWB_LIFE_CYCLE_STA != 'IL BILLED'
 15             AND T.AWB_LIFE_CYCLE_STA != 'LOCKED'
 16          UNION
 17          SELECT ' ' || TRIM(T1.AIRLINE_PREFIX) ||
 18                 TRIM(TO_CHAR(t1.serial_number, '00000000')) || ' ' AWB_NUMBER
 19            FROM T_AWB_LOAD_TABLE T1
 20           WHERE t1.status = 'REJECT'
 21           AND t1.serial_number != 0
 22          UNION
 23          SELECT AWB_NUMBER
 24            FROM T_FLIGHT_MANIFEST_ITEM
 25           WHERE STATUS = 'U'
 26              OR STATUS = 'S') TUAWB
 27              /

Execution Plan
----------------------------------------------------------
Plan hash value: 1255316772

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

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

|   0 | SELECT STATEMENT        |                        |   110K|   972K|       |  2371   (1)| 00:00:29 |
|*  1 |  INDEX UNIQUE SCAN      | IU01_AIR_WAYBILL1      |     1 |    14 |       |     1   (0)| 00:00:01 |
|*  2 |   COUNT STOPKEY         |                        |       |       |       |            |          |
|*  3 |    INDEX FAST FULL SCAN | IP02_AWB_LOAD_TABLE    |     1 |    11 |       |     2   (0)| 00:00:01 |
|   4 |  VIEW                   |                        |   110K|   972K|       |  2371   (1)| 00:00:29 |
|   5 |   SORT UNIQUE           |                        |   110K|  2138K|  6265K|  2371  (41)| 00:00:29 |
|   6 |    UNION-ALL            |                        |       |       |       |            |          |
|*  7 |     TABLE ACCESS FULL   | T_AIR_WAYBILL          | 18823 |   551K|       |  1257   (1)| 00:00:16 |
|*  8 |     INDEX FAST FULL SCAN| IP02_AWB_LOAD_TABLE    | 25832 |   554K|       |    60   (2)| 00:00:01 |
|*  9 |     TABLE ACCESS FULL   | T_FLIGHT_MANIFEST_ITEM | 66026 |  1031K|       |   368   (1)| 00:00:05 |

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


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

   1 - access("TAWB"."AWB_NUMBER"=:B1)
   2 - filter(ROWNUM<2)
   3 - filter(' '||TRIM("TLOD"."AIRLINE_PREFIX")||TRIM(TO_CHAR("TLOD"."SERIAL_NUMBER",'00000000'))
              ||' '=:B1)
   7 - filter("T"."AWB_LIFE_CYCLE_STA"<>'LOCKED' AND "T"."AWB_LIFE_CYCLE_STA"<>'PRORATED' AND
              "T"."AWB_LIFE_CYCLE_STA"<>'IL BILLED')
   8 - filter("T1"."SERIAL_NUMBER"<>0 AND "T1"."STATUS"='REJECT')
   9 - filter("STATUS"='S' OR "STATUS"='U')

SQL> 

query modified by me for optimization:
 1  SELECT ' '||AWB_NUMBER||' ',typ AWB_TYPE
  2  FROM (SELECT distinct trim(AWB_NUMBER) AWB_NUMBER,'AWB' typ
  3            FROM T_AIR_WAYBILL T
  4           WHERE T.AWB_LIFE_CYCLE_STA != 'PRORATED'
  5             AND T.AWB_LIFE_CYCLE_STA != 'IL BILLED'
  6             AND T.AWB_LIFE_CYCLE_STA != 'LOCKED'
  7          UNION
  8          SELECT  distinct TRIM(T1.AIRLINE_PREFIX) ||
  9                 TRIM(TO_CHAR(t1.serial_number, '00000000'))  AWB_NUMBER ,'LOD' typ
 10            FROM T_AWB_LOAD_TABLE T1
 11           WHERE t1.status = 'REJECT'
 12           AND t1.serial_number != 0
 13          UNION
 14          SELECT trim(AWB_NUMBER) AWB_NUMBER,'MAN' typ
 15            FROM T_FLIGHT_MANIFEST_ITEM
 16           WHERE STATUS = 'U'
 17*             OR STATUS = 'S') TUAWB
SQL> /

Execution Plan
----------------------------------------------------------
Plan hash value: 2161869004

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

| Id  | Operation               | Name                   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                        |   110K|  1405K|       |  2371   (1)| 00:00:29 |
|   1 |  VIEW                   |                        |   110K|  1405K|       |  2371   (1)| 00:00:29 |
|   2 |   SORT UNIQUE           |                        |   110K|  2138K|  6265K|  2371  (41)| 00:00:29 |
|   3 |    UNION-ALL            |                        |       |       |       |            |          |
|*  4 |     TABLE ACCESS FULL   | T_AIR_WAYBILL          | 18823 |   551K|       |  1257   (1)| 00:00:16 |
|*  5 |     INDEX FAST FULL SCAN| IP02_AWB_LOAD_TABLE    | 25832 |   554K|       |    60   (2)| 00:00:01 |
|*  6 |     TABLE ACCESS FULL   | T_FLIGHT_MANIFEST_ITEM | 66026 |  1031K|       |   368   (1)| 00:00:05 |
----------------------------------------------------------------------------------------------------------


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

   4 - filter("T"."AWB_LIFE_CYCLE_STA"<>'LOCKED' AND "T"."AWB_LIFE_CYCLE_STA"<>' PRORATED' AND
              "T"."AWB_LIFE_CYCLE_STA"<>'IL BILLED')
   5 - filter("T1"."SERIAL_NUMBER"<>0 AND "T1"."STATUS"='REJECT')
   6 - filter("STATUS"='S' OR "STATUS"='U')


[Mod-Edit: Frank added code-tags to improve readability]
[Mod-Edit2: Even concatenated broken lines, etc. Next time make sure your code & results are readable please]

[Updated on: Thu, 15 October 2009 00:47] by Moderator

Report message to a moderator

Re: performance tuning / optimization (merged 3) [message #426378 is a reply to message #426324] Thu, 15 October 2009 03:21 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Try adding the following indexes:

CREATE INDEX temp_idx_1 on T_AIR_WAYBILL(AWB_LIFE_CYCLE_STA,AWB_NUMBER);

CREATE INDEX temp_idx_1 on T_FLIGHT_MANIFEST_ITEM(STATUS,AWB_NUMBER);

Gather stats, rerun the queries, and post the revised plans.

Oh - and look at replacing the UNION statements with UNION ALL - if you end up getting duplicate AWB_NUMBER values, it'd be cheaper to have a single distinct operation in the outer query than two of them in the inner query.

[Updated on: Thu, 15 October 2009 03:23]

Report message to a moderator

Re: performance tuning / optimization (merged 3) [message #426407 is a reply to message #426378] Thu, 15 October 2009 05:26 Go to previous messageGo to next message
mohan1760
Messages: 59
Registered: June 2008
Member
can you write the same original query in some other way to improve performance....
Re: performance tuning / optimization (merged 3) [message #426410 is a reply to message #426407] Thu, 15 October 2009 05:35 Go to previous messageGo to next message
cookiemonster
Messages: 12415
Registered: September 2008
Location: Rainy Manchester
Senior Member
mohan1760 wrote on Thu, 15 October 2009 11:26
can you write the same original query in some other way to improve performance....


Considering the query the scope for rewriting is very limited, other than what JRowbottom already suggested:
Quote:

Oh - and look at replacing the UNION statements with UNION ALL - if you end up getting duplicate AWB_NUMBER values, it'd be cheaper to have a single distinct operation in the outer query than two of them in the inner query.


Have you tried that?
Also is AWB_NUMBER unique in any of the tables?
And is the combination of AIRLINE_PREFIX and serial_number unique in T_AWB_LOAD_TABLE?
Re: performance tuning / optimization (merged 3) [message #426421 is a reply to message #426410] Thu, 15 October 2009 06:18 Go to previous messageGo to next message
mohan1760
Messages: 59
Registered: June 2008
Member
AWB_NUMBER IS UNIQUE IN T_AIRWAY_BILL But other unique values for AWB_NUMBER may present in other TABLES i.e.T_AWB_LOAD_TABLE and T_FLIGHT_MANIFEST_ITEM...and that also used in the application and doesnt matter whether it is present in T_AIRWAY_BILL or not.


TO TAKE AWB NUMBER IN APPLICATION FOLLOWING PRIORITY IS SET

IF IT IS PRESENT IN T_AIRWAY_BILL THEN IT IS TAKEN FROM T-T_AIR_WAYBILL table itself,
IF NOT THEN FROM T_AWB_LOAD_TABLE
IF NOT IN ABOVE TABLES THEN ONLY FROM T_FLIGHT_MANIFEST_ITEM
Re: performance tuning / optimization (merged 3) [message #426424 is a reply to message #426421] Thu, 15 October 2009 06:41 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Try this:
select awb_number,awb_type
from  (SELECT AWB_NUMBER
             ,typ  AWB_TYPE
             ,row_number() over (partition by awb_number order by case when typ = 'AWB' then 1
                                                                       when typ = 'LOD' then 2
                                                                       when typ = 'MAN' then 3
                                                                       else 4 end) rnum
       FROM  (SELECT trim(AWB_NUMBER) AWB_NUMBER,'AWB' typ
              FROM   T_AIR_WAYBILL T
              WHERE  T.AWB_LIFE_CYCLE_STA != 'PRORATED'
              AND    T.AWB_LIFE_CYCLE_STA != 'IL BILLED'
              AND    T.AWB_LIFE_CYCLE_STA != 'LOCKED'
             UNION ALL
              SELECT  TRIM(T1.AIRLINE_PREFIX) ||TRIM(TO_CHAR(t1.serial_number,'00000000'))  AWB_NUMBER 
                     ,'LOD' typ
              FROM    T_AWB_LOAD_TABLE T1
              WHERE   t1.status = 'REJECT'
              AND     t1.serial_number != 0
             UNION ALL
              SELECT trim(AWB_NUMBER) AWB_NUMBER,'MAN' typ
              FROM   T_FLIGHT_MANIFEST_ITEM
              WHERE  (STATUS = 'U'
                      OR STATUS = 'S')
              ) )
WHERE  rnum = 1;


This should run faster, as it removes all the sort distinct operations. It will pick the awb_number in the order you've specified.

Performance wise, other than creating the indexes, the only things I can think of are:
1) how many values of AWB_LIFE_CYCLE_STA are there, and what %age of the total number of rows does the WHERE clause in the first
subquery return.

2)It might be worth creating MVIEWS for the 3 clauses, but without numbersm I can't say one way or the other.

If there are any problems with it, my reply will be 'Give us some create table and insert statements - without those, there's not much more we can do'
Re: performance tuning / optimization (merged 3) [message #426430 is a reply to message #426421] Thu, 15 October 2009 07:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do not SHOUT.
Please read OraFAQ Forum Guide.

Regards
Michel
Re: performance tuning / optimization (merged 3) [message #426431 is a reply to message #426430] Thu, 15 October 2009 07:48 Go to previous messageGo to next message
mohan1760
Messages: 59
Registered: June 2008
Member
you are a genious......JRowbottom
u all have done it for me.....
thanks to all of you.......
Re: performance tuning / optimization (merged 3) [message #426434 is a reply to message #426431] Thu, 15 October 2009 08:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do not use IM/SMS speak;
Please read OraFAQ Forum Guide.

Regards
Michel
Re: performance tuning / optimization (merged 3) [message #426512 is a reply to message #426434] Fri, 16 October 2009 02:25 Go to previous messageGo to next message
mohan1760
Messages: 59
Registered: June 2008
Member
there is one problem with this query
for one of the awb_number it is showing MAN,even though it is present in t_air_waybill.....

I checked 100 records among which 3 records having this problem.
Your query is returning correct number of records ....

Thanks in advance........
Re: performance tuning / optimization (merged 3) [message #426513 is a reply to message #426512] Fri, 16 October 2009 02:29 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
If there are any problems with it, my reply will be 'Give us some create table and insert statements - without those, there's not much more we can do'
Previous Topic: UTL_SMTP issue with Canadian French lanaguage
Next Topic: How to take export and import in a procedure
Goto Forum:
  


Current Time: Wed Dec 07 18:24:22 CST 2016

Total time taken to generate the page: 0.21978 seconds