Home » RDBMS Server » Performance Tuning » SQL performance tunning (Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production)
SQL performance tunning [message #615023] |
Thu, 29 May 2014 09:16 |
|
msinha8
Messages: 43 Registered: March 2014 Location: Hyderbad
|
Member |
|
|
Hi All,
I have been working on performance tuinning of a OLTP system and found that a SQL query is taking more time to fetch the records :-
SELECT /*+ PARALLEL(XXCS_NORM_ALARM,4) */
ALM_ID,
CUST_ID,
CHANNEL_SITE_ID,
SITE_NAME,
RECEIVER,
NVL(DESCRIPTION, 'No Summary') DESCRIPTION,
SOURCE,
TIME_RECEIVED,
TIME_OCCURRED,
TIME_DIALOUT,
CONTROLLER,
SUB_CONTROLLER,
ALM_TYPE,
ALM_STATE,
ALM_PRIORITY,
CALLER_ID,
RECEIVER_ID,
DESC_PROBABLE,
decode(field14,'CB Maintenance',decode(cb_rg_flag,'Y',nvl(cb_routing_group,na.ROUTING_GROUP),na.ROUTING_GROUP),na.ROUTING_GROUP) routing_group,
CONTROLLER_INSTANCE,
field5,
field9,
field8,
field6,
field14,
pa.cb_rg_flag,
pa.cb_routing_group
FROM
XXCS_NORM_ALARM xna
,xxar.xxar_party_attributes xpa
WHERE
PROCESSED_FLAG IN ('N','E')
AND pa.party_id(+) = na.cust_id
ORDER BY ALM_ID ;
My area of concern is, initially the explain plan fro this query is as below
SELECT STATEMENT CHOOSE Cost: 30 Bytes: 214 Cardinality: 1
7 SORT ORDER BY Cost: 30 Bytes: 214 Cardinality: 1
6 NESTED LOOPS OUTER Cost: 5 Bytes: 214 Cardinality: 1
3 INLIST ITERATOR
2 TABLE ACCESS BY INDEX ROWID XXCS.XXCS_NORM_ALARM Cost: 4 Bytes: 180 Cardinality: 1
1 INDEX RANGE SCAN NON-UNIQUE XXCS.XXCS_NORM_ALARM_N1 Cost: 3 Cardinality: 1
5 TABLE ACCESS BY INDEX ROWID XXAR.XXAR_PARTY_ATTRIBUTES Cost: 1 Bytes: 34 Cardinality: 1
4 INDEX UNIQUE SCAN UNIQUE XXAR.SYS_C00174657 Cardinality: 1
but once i gather stats on the tables which is used in the query,the explain plan shows huge deviation
SELECT STATEMENT CHOOSE Cost: 10,834 Bytes: 189,668,627 Cardinality: 982,739
4 SORT ORDER BY PARALLEL_TO_SERIAL :Q152537002 Cost: 10,834 Bytes: 189,668,627 Cardinality: 982,739
3 HASH JOIN OUTER PARALLEL_TO_PARALLEL :Q152537001 Cost: 5,974 Bytes: 189,668,627 Cardinality: 982,739
1 TABLE ACCESS FULL PARALLEL_COMBINED_WITH_PARENT XXCS.XXCS_NORM_ALARM :Q152537001 Cost: 5,605 Bytes: 180,823,976 Cardinality: 982,739
2 TABLE ACCESS FULL PARALLEL_FROM_SERIAL XXAR.XXAR_PARTY_ATTRIBUTES :Q152537000 Cost: 2 Bytes: 1,368 Cardinality: 152
Please let me know what mistake i have done.
I am processing with 1000 records
Thanks
Mritunjay
but
|
|
|
SQL performance tunning [message #615024 is a reply to message #615023] |
Thu, 29 May 2014 09:16 |
|
msinha8
Messages: 43 Registered: March 2014 Location: Hyderbad
|
Member |
|
|
Hi All,
I have been working on performance tuinning of a OLTP system and found that a SQL query is taking more time to fetch the records :-
SELECT /*+ PARALLEL(XXCS_NORM_ALARM,4) */
ALM_ID,
CUST_ID,
CHANNEL_SITE_ID,
SITE_NAME,
RECEIVER,
NVL(DESCRIPTION, 'No Summary') DESCRIPTION,
SOURCE,
TIME_RECEIVED,
TIME_OCCURRED,
TIME_DIALOUT,
CONTROLLER,
SUB_CONTROLLER,
ALM_TYPE,
ALM_STATE,
ALM_PRIORITY,
CALLER_ID,
RECEIVER_ID,
DESC_PROBABLE,
decode(field14,'CB Maintenance',decode(cb_rg_flag,'Y',nvl(cb_routing_group,na.ROUTING_GROUP),na.ROUTING_GROUP),na.ROUTING_GROUP) routing_group,
CONTROLLER_INSTANCE,
field5,
field9,
field8,
field6,
field14,
pa.cb_rg_flag,
pa.cb_routing_group
FROM
XXCS_NORM_ALARM xna
,xxar.xxar_party_attributes xpa
WHERE
PROCESSED_FLAG IN ('N','E')
AND pa.party_id(+) = na.cust_id
ORDER BY ALM_ID ;
My area of concern is, initially the explain plan fro this query is as below
SELECT STATEMENT CHOOSE Cost: 30 Bytes: 214 Cardinality: 1
7 SORT ORDER BY Cost: 30 Bytes: 214 Cardinality: 1
6 NESTED LOOPS OUTER Cost: 5 Bytes: 214 Cardinality: 1
3 INLIST ITERATOR
2 TABLE ACCESS BY INDEX ROWID XXCS.XXCS_NORM_ALARM Cost: 4 Bytes: 180 Cardinality: 1
1 INDEX RANGE SCAN NON-UNIQUE XXCS.XXCS_NORM_ALARM_N1 Cost: 3 Cardinality: 1
5 TABLE ACCESS BY INDEX ROWID XXAR.XXAR_PARTY_ATTRIBUTES Cost: 1 Bytes: 34 Cardinality: 1
4 INDEX UNIQUE SCAN UNIQUE XXAR.SYS_C00174657 Cardinality: 1
but once i gather stats on the tables which is used in the query,the explain plan shows huge deviation
SELECT STATEMENT CHOOSE Cost: 10,834 Bytes: 189,668,627 Cardinality: 982,739
4 SORT ORDER BY PARALLEL_TO_SERIAL :Q152537002 Cost: 10,834 Bytes: 189,668,627 Cardinality: 982,739
3 HASH JOIN OUTER PARALLEL_TO_PARALLEL :Q152537001 Cost: 5,974 Bytes: 189,668,627 Cardinality: 982,739
1 TABLE ACCESS FULL PARALLEL_COMBINED_WITH_PARENT XXCS.XXCS_NORM_ALARM :Q152537001 Cost: 5,605 Bytes: 180,823,976 Cardinality: 982,739
2 TABLE ACCESS FULL PARALLEL_FROM_SERIAL XXAR.XXAR_PARTY_ATTRIBUTES :Q152537000 Cost: 2 Bytes: 1,368 Cardinality: 152
Please let me know what mistake i have done.
I am processing with 1000 records
Thanks
Mritunjay
*BlackSwan added {code} tags. Please do so yourself in the future. http://www.orafaq.com/forum/t/174502/102589/
[Updated on: Thu, 29 May 2014 09:18] by Moderator Report message to a moderator
|
|
|
|
|
Re: SQL performance tunning [message #615027 is a reply to message #615024] |
Thu, 29 May 2014 09:25 |
John Watson
Messages: 8938 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
You are not telling the truth. This use of table aliases is impossible:
FROM
XXCS_NORM_ALARM xna
,xxar.xxar_party_attributes xpa
WHERE
PROCESSED_FLAG IN ('N','E')
AND pa.party_id(+) = na.cust_id
|
|
|
|
Re: SQL performance tunning [message #615029 is a reply to message #615028] |
Thu, 29 May 2014 09:29 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
msinha8 wrote on Thu, 29 May 2014 19:56
My query is returning 1000 record only.
msinha8 wrote on Thu, 29 May 2014 19:46
SELECT STATEMENT CHOOSE Cost: 10,834 Bytes: 189,668,627 Cardinality: 982,739
Cardinality estimates are way of than actuals. So, how did you gather the stats?
|
|
|
|
|
|
Re: SQL performance tunning [message #615033 is a reply to message #615032] |
Thu, 29 May 2014 09:56 |
|
msinha8
Messages: 43 Registered: March 2014 Location: Hyderbad
|
Member |
|
|
After removing the PARALLEL hint below is the stats ;-
SELECT STATEMENT CHOOSE Cost: 76,668 Bytes: 189,668,627 Cardinality: 982,739
4 SORT ORDER BY Cost: 76,668 Bytes: 189,668,627 Cardinality: 982,739
3 HASH JOIN OUTER Cost: 47,769 Bytes: 189,668,627 Cardinality: 982,739
1 TABLE ACCESS FULL XXCS.XXCS_NORM_ALARM Cost: 44,840 Bytes: 180,823,976 Cardinality: 982,739
2 TABLE ACCESS FULL XXAR.XXAR_PARTY_ATTRIBUTES Cost: 2 Bytes: 1,368 Cardinality: 152
|
|
|
|
|
Re: SQL performance tunning [message #615036 is a reply to message #615033] |
Thu, 29 May 2014 10:12 |
John Watson
Messages: 8938 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
How are you generating that execution plan? It doesn't show what is needed. If you are using EXPLAIN PLAN, use dbms_xplan.display and be sure to show the predicate information. Then set statistics_level=all, run the statement, and use dbms_xplan.display_cursor(format=>'allstats last')
|
|
|
Re: SQL performance tunning [message #615037 is a reply to message #615036] |
Thu, 29 May 2014 11:09 |
|
msinha8
Messages: 43 Registered: March 2014 Location: Hyderbad
|
Member |
|
|
John,
As suggested got the expalin plan.
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 786K| 145M| | 70426 |
| 1 | SORT ORDER BY | | 786K| 145M| 307M| 70426 |
|* 2 | HASH JOIN OUTER | | 786K| 145M| 147M| 47196 |
|* 3 | TABLE ACCESS FULL | XXCS_NORM_ALARM | 786K| 138M| | 44840 |
| 4 | TABLE ACCESS FULL | XXAR_PARTY_ATTRIBUTES | 152 | 1368 | | 2 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("PA"."PARTY_ID"(+)="NA"."CUST_ID")
3 - filter("NA"."PROCESSED_FLAG"='E' OR "NA"."PROCESSED_FLAG"='N')
Note: cpu costing is off
[Updated on: Thu, 29 May 2014 11:11] Report message to a moderator
|
|
|
Re: SQL performance tunning [message #615038 is a reply to message #615037] |
Thu, 29 May 2014 11:25 |
John Watson
Messages: 8938 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
OK, you have provided a little bit of useful information. But not enough. How many rows does the query actually return? How many rows are there where ("NA"."PROCESSED_FLAG"='E' OR "NA"."PROCESSED_FLAG"='N') ?
How long does it take? Was it faster or slower when it was running with a nested loop join? How long would you like it to take?
--update: and, of course, how many rows are there in each table?
[Updated on: Thu, 29 May 2014 11:27] Report message to a moderator
|
|
|
Re: SQL performance tunning [message #615039 is a reply to message #615037] |
Thu, 29 May 2014 11:36 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
Unfortunately, I didn't get much of a chance to work on 9i DB. But what concerns me is the optimizer mode as choose and the cpu costing being off. Since you are confident about stats being gathered, so RBO is ruled out. Now, with cpu costing off, I am not pretty sure in your DB version if it has to do anything with _optimizer_cost_model. Someone with good experience in 9i would help with that, and I might also be completely incorrect.
P.S. I don't have 9i version to test it.
[Updated on: Thu, 29 May 2014 11:47] Report message to a moderator
|
|
|
Re: SQL performance tunning [message #615065 is a reply to message #615039] |
Thu, 29 May 2014 20:20 |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 786K| 145M| | 70426 |
| 1 | SORT ORDER BY | | 786K| 145M| 307M| 70426 |
|* 2 | HASH JOIN OUTER | | 786K| 145M| 147M| 47196 |
|* 3 | TABLE ACCESS FULL | XXCS_NORM_ALARM | 786K| 138M| | 44840 |
| 4 | TABLE ACCESS FULL | XXAR_PARTY_ATTRIBUTES | 152 | 1368 | | 2 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("PA"."PARTY_ID"(+)="NA"."CUST_ID")
3 - filter("NA"."PROCESSED_FLAG"='E' OR "NA"."PROCESSED_FLAG"='N')
Note: cpu costing is off
There is something very odd here. Note the TEMPSPC requirement - what Oracle is doing is hashing the BIG table and probing the hash with the SMALL table. This should be the other way around: i.e. hash the small table so it only takes up a small amount of memory and does not have to spill to disk, then do a single pass with the big table - also without the need for TEMP space.
To test the theory, you need to do ALL of the following
- Reverse the order of tables in the FROM clause so that the small table is first
- Add an ORDERED hint. ie. SELECT /*+ ORDERED */ ALM_ID, CUST_ID, etc
- Check the plan to make sure that PA is now mentioned first in the hash join and there is no longer a TEMPSPC entry
- If that works, try to figure out another way of getting this plan because the ORDERED hint is a bit hard-core.
Even if it works, this will still give you a hash join, but in the correct order. If you truly have only 1000 rows with PROCESSED_FLAG N or E, then you will still want to get back to your original plan, where it used an index on that column.
To do this, I would add a column histogram on PROCESSED_FLAG when you gather statistics. This will let CBO know that N and E are relatively rare values and that it is safe to use an index.
Ross Leishman
|
|
|
Re: SQL performance tunning [message #615080 is a reply to message #615065] |
Fri, 30 May 2014 01:31 |
John Watson
Messages: 8938 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
The outer join forces the join order. Which is why I always question outer joins. Many developers just throw them in all the time without thinking whether they are necessary.
|
|
|
Re: SQL performance tunning [message #615084 is a reply to message #615080] |
Fri, 30 May 2014 02:23 |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
Oracle® Database SQL Tuning Guide 12c Release 1 (12.1)Hash Join Outer Joins
The optimizer uses hash joins for processing an outer join when either of the following conditions is met:
•The data volume is large enough to make the hash join method efficient.
•It is not possible to drive from the outer table to the inner table.
The cost determines the order of tables. The outer table, including preserved rows, may be used to build the hash table, or it may be used to probe the hash table.
Notwithstanding this, I only have access to a 12c database at the moment and I can't make it hash the outer table. I distinctly remember controlling join order of outer hash joins in prior versions, but now I am beginning to question my memory.
Ross Leishman
|
|
|
Re: SQL performance tunning [message #615092 is a reply to message #615084] |
Fri, 30 May 2014 02:57 |
John Watson
Messages: 8938 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
I get the same result (I had done a test before posting). I used 12.1 and ANSI join syntax,
SELECT /*+ use_hash(emp dept) */ * FROM EMP LEFT|RIGHT OUTER JOIN DEPT;
I added LEADING hints which were ignored. Perhaps that 10053 trace would show why, but I'm not going to do that test now (though Mritunjay might want to, except that he seems to have lost interest) - I'll stay with the pragmatic answer, though it does contradict that doc you quoted.
|
|
|
Re: SQL performance tunning [message #615573 is a reply to message #615092] |
Thu, 05 June 2014 08:56 |
|
msinha8
Messages: 43 Registered: March 2014 Location: Hyderbad
|
Member |
|
|
Hi All,
We Achieve(almost) the Required optimization by replacing the table 'XXCS_NORM_ALARM' with a in line query where we are filtering the records first based on processed_flag and then applying the outer condition:-
FROM
(select * from XXCS_NORM_ALARM where PROCESSED_FLAG IN ('N','E')) na -- CSS Alarms Normalization XXCS_RAW_UARD
,xxar.xxar_party_attributes pa -- chandra Apr, 12, 2012 CB maintenance
WHERE pa.party_id(+) = na.cust_id
ORDER BY ALM_ID ;
Now the computed stats is :-
Plan
SELECT STATEMENT CHOOSE Cost: 73 Bytes: 68,445 Cardinality: 351
6 SORT ORDER BY Cost: 73 Bytes: 68,445 Cardinality: 351
5 HASH JOIN OUTER Cost: 39 Bytes: 68,445 Cardinality: 351
3 INLIST ITERATOR
2 TABLE ACCESS BY INDEX ROWID XXCS.XXCS_NORM_ALARM Cost: 36 Bytes: 65,286 Cardinality: 351
1 INDEX RANGE SCAN NON-UNIQUE XXCS.XXCS_NORM_ALARM_N1 Cost: 5 Cardinality: 351
4 TABLE ACCESS FULL XXAR.XXAR_PARTY_ATTRIBUTES Cost: 2 Bytes: 1,368 Cardinality: 152
Thanks all for their responses. It's been a good learning experience with all of you.
|
|
|
Goto Forum:
Current Time: Sat Jun 29 06:38:45 CDT 2024
|