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 Go to next message
msinha8
Messages: 22
Registered: March 2014
Location: Hyderbad
Junior 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 Go to previous messageGo to next message
msinha8
Messages: 22
Registered: March 2014
Location: Hyderbad
Junior 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 #615025 is a reply to message #615023] Thu, 29 May 2014 09:20 Go to previous messageGo to next message
Lalit Kumar B
Messages: 1845
Registered: May 2013
Location: World Wide on the Web
Senior Member
Without code tags thats unreadable. Have a look at this link http://www.orafaq.com/forum/t/84315/

msinha8 wrote on Thu, 29 May 2014 19:46
I am processing with 1000 records


How many rows does the query actualy returns? The cardinality estimates seems to be waf off than what you mention as 1000.

[Updated on: Thu, 29 May 2014 09:23]

Report message to a moderator

Re: SQL performance tunning [message #615026 is a reply to message #615024] Thu, 29 May 2014 09:23 Go to previous messageGo to next message
BlackSwan
Messages: 22512
Registered: January 2009
Senior Member
your attempts at obfuscation results in invalid SQL syntax.
I refuse to guess what is real & what you fabricated.
table aliases to not match what is used in SELECT statement.

Re: SQL performance tunning [message #615027 is a reply to message #615024] Thu, 29 May 2014 09:25 Go to previous messageGo to next message
John Watson
Messages: 4390
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 #615028 is a reply to message #615025] Thu, 29 May 2014 09:26 Go to previous messageGo to next message
msinha8
Messages: 22
Registered: March 2014
Location: Hyderbad
Junior Member
Thanks Lalit,from next time onwards i will take care of code tags.

My query is returning 1000 record only.
Re: SQL performance tunning [message #615029 is a reply to message #615028] Thu, 29 May 2014 09:29 Go to previous messageGo to next message
Lalit Kumar B
Messages: 1845
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 #615030 is a reply to message #615027] Thu, 29 May 2014 09:30 Go to previous messageGo to next message
msinha8
Messages: 22
Registered: March 2014
Location: Hyderbad
Junior Member
Apologies John, while formating mistakenly aliases name is changed.

the actual code is

 FROM
      XXCS_NORM_ALARM na 
      ,xxar.xxar_party_attributes pa 
  WHERE
    PROCESSED_FLAG IN ('N','E')
    AND pa.party_id(+) = na.cust_id
Re: SQL performance tunning [message #615031 is a reply to message #615029] Thu, 29 May 2014 09:33 Go to previous messageGo to next message
msinha8
Messages: 22
Registered: March 2014
Location: Hyderbad
Junior Member
Lalit,

I have use below code to gather stats on my table.

EXECUTE dbms_stats.gather_table_stats(
    ownname=>'XXCS', 
    tabname=>'XXCS_NORM_ALARM',
    estimate_percent=>dbms_stats.auto_sample_size,
    cascade=>true);
Re: SQL performance tunning [message #615032 is a reply to message #615031] Thu, 29 May 2014 09:47 Go to previous messageGo to next message
BlackSwan
Messages: 22512
Registered: January 2009
Senior Member
CBO or RBO?

what results after removing PARALLEL hint?
Re: SQL performance tunning [message #615033 is a reply to message #615032] Thu, 29 May 2014 09:56 Go to previous messageGo to next message
msinha8
Messages: 22
Registered: March 2014
Location: Hyderbad
Junior 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 #615034 is a reply to message #615033] Thu, 29 May 2014 09:59 Go to previous messageGo to next message
BlackSwan
Messages: 22512
Registered: January 2009
Senior Member
are pa.party_id(+) = na.cust_id both indexed?

CBO or RBO?

are statistics current on XXAR.XXAR_PARTY_ATTRIBUTES?
Re: SQL performance tunning [message #615035 is a reply to message #615034] Thu, 29 May 2014 10:02 Go to previous messageGo to next message
msinha8
Messages: 22
Registered: March 2014
Location: Hyderbad
Junior Member
yes pa.party_id and na.cust_id both are indexed.

it's CBO

yes statistics is on XXAR.XXAR_PARTY_ATTRIBUTES
Re: SQL performance tunning [message #615036 is a reply to message #615033] Thu, 29 May 2014 10:12 Go to previous messageGo to next message
John Watson
Messages: 4390
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 Go to previous messageGo to next message
msinha8
Messages: 22
Registered: March 2014
Location: Hyderbad
Junior 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 Go to previous messageGo to next message
John Watson
Messages: 4390
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 Go to previous messageGo to next message
Lalit Kumar B
Messages: 1845
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 Go to previous messageGo to next message
rleishman
Messages: 3688
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 Go to previous messageGo to next message
John Watson
Messages: 4390
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 Go to previous messageGo to next message
rleishman
Messages: 3688
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 Go to previous messageGo to next message
John Watson
Messages: 4390
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 Go to previous message
msinha8
Messages: 22
Registered: March 2014
Location: Hyderbad
Junior 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.
Previous Topic: SQL id shows multiple plan table
Next Topic: Staging table without indexes, slow queries
Goto Forum:
  


Current Time: Mon Jul 28 06:40:25 CDT 2014

Total time taken to generate the page: 0.13257 seconds