performance tuning / optimization (merged 3) [message #426104] |
Wed, 14 October 2009 02:08  |
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 #426124 is a reply to message #426104] |
Wed, 14 October 2009 02:49   |
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   |
ThomasG
Messages: 3212 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.
|
|
|
|
Re: optimization [message #426162 is a reply to message #426124] |
Wed, 14 October 2009 04:25   |
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 #426207 is a reply to message #426165] |
Wed, 14 October 2009 07:04   |
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 #426223 is a reply to message #426207] |
Wed, 14 October 2009 07:58   |
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   |
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   |
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   |
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   |
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 #426410 is a reply to message #426407] |
Thu, 15 October 2009 05:35   |
cookiemonster
Messages: 13963 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
mohan1760 wrote on Thu, 15 October 2009 11:26can 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   |
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   |
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 #426512 is a reply to message #426434] |
Fri, 16 October 2009 02:25   |
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........
|
|
|
|