Home » RDBMS Server » Performance Tuning » Please help for Improving Query Performance (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)
Please help for Improving Query Performance [message #645823] Tue, 15 December 2015 08:14 Go to next message
msol25
Messages: 368
Registered: June 2011
Senior Member
Dear,

Please can you help me for Improving Performance for my query:


//Table: dm_crm_gsm.s_prod_int_edi having 1000 Records
//Table: dm_crm_gsm.s_asset_edi having 524 million records.

SELECT  /*+ use_hash(a) parallel(a 10) index(c S_PROD_INT_PK1) */
        a.row_id
from    dm_crm_gsm.s_prod_int_edi c,
        dm_crm_gsm.s_asset_edi a
where   c.atm_type_cd = 'Postpaid'
and     c.PROD_ATTRIB03_CD = 'Voice'
and     a.prod_id = c.row_id

Explain Plan:

PLAN_TABLE_OUTPUT

Plan hash value: 1633564373
 
---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                |  1798K|    65M| 98152  (13)| 00:00:04 |        |      |            |
|   1 |  PX COORDINATOR                   |                |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)             | :TQ10001       |  1798K|    65M| 98152  (13)| 00:00:04 |  Q1,01 | P->S | QC (RAND)  |
|*  3 |    HASH JOIN                      |                |  1798K|    65M| 98152  (13)| 00:00:04 |  Q1,01 | PCWP |            |
|   4 |     BUFFER SORT                   |                |       |       |            |          |  Q1,01 | PCWC |            |
|   5 |      PX RECEIVE                   |                |    18 |   324 |    35   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|   6 |       PX SEND BROADCAST           | :TQ10000       |    18 |   324 |    35   (0)| 00:00:01 |        | S->P | BROADCAST  |
|*  7 |        TABLE ACCESS BY INDEX ROWID| S_PROD_INT_EDI |    18 |   324 |    35   (0)| 00:00:01 |        |      |            |
|   8 |         INDEX FULL SCAN           | S_PROD_INT_PK1 |  5505 |       |     1   (0)| 00:00:01 |        |      |            |
|   9 |     PX BLOCK ITERATOR             |                |   596M|    11G| 97795  (12)| 00:00:04 |  Q1,01 | PCWC |            |
|* 10 |      TABLE ACCESS STORAGE FULL    | S_ASSET_EDI    |   596M|    11G| 97795  (12)| 00:00:04 |  Q1,01 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("A"."PROD_ID"="C"."ROW_ID")
   7 - filter("C"."PROD_ATTRIB03_CD"='Voice' AND "C"."ATM_TYPE_CD"='Postpaid')
  10 - storage(SYS_OP_BLOOM_FILTER(:BF0000,"A"."PROD_ID"))
       filter(SYS_OP_BLOOM_FILTER(:BF0000,"A"."PROD_ID"))





[EDITED by LF: switched topic title and product info]

[Updated on: Wed, 16 December 2015 01:15] by Moderator

Report message to a moderator

Re: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production [message #645824 is a reply to message #645823] Tue, 15 December 2015 08:19 Go to previous messageGo to next message
Michel Cadot
Messages: 65380
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Of course, you are using Exadata but, as always, do not think it is an important information to give.
If you are returning 2 millions rows out of 600 millions, it can only be "slow".
So 2 questions:
1/ Are you sure some one will read the 2 millions rows?
2/ Why parallel 10?

Re: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production [message #645825 is a reply to message #645823] Tue, 15 December 2015 08:20 Go to previous messageGo to next message
BlackSwan
Messages: 25849
Registered: January 2009
Location: SoCal
Senior Member
Why is 4 seconds not fast enough?

What is elapsed time when no PARALLEL exists?

Design is flawed if ROWID really exists as static column in application table.
Re: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production [message #645826 is a reply to message #645824] Tue, 15 December 2015 08:22 Go to previous messageGo to next message
Roachcoach
Messages: 1510
Registered: May 2010
Location: UK
Senior Member
3/ why force and index read that then has to read the table anyway and push it into a hash join. Seems counter intuitive on the face of it.
4/ How long does it take? Not many people would consider 4 seconds slow unless it is OLTP but seeing as you've hinted parallel this isn't the case......is it?
Re: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production [message #645828 is a reply to message #645825] Tue, 15 December 2015 08:29 Go to previous messageGo to next message
msol25
Messages: 368
Registered: June 2011
Senior Member
Hi Michel,

I hope there is having more than 2 million records.So,Optimizer is choosing Full table scan on Asset Table.
I have only 7 products for which looking data in Asset table.Optimizer is going to retrieve data using Full table scan.So,Interested to retrieve data with Parallel 10.If my decision wrong here.Please suggest me.

Re: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production [message #645829 is a reply to message #645828] Tue, 15 December 2015 08:29 Go to previous messageGo to next message
msol25
Messages: 368
Registered: June 2011
Senior Member
Dear Blackwan,

Please find explain plan without hint:


PLAN_TABLE_OUTPUT

Plan hash value: 3800598143
 
---------------------------------------------------------------------------------------------
| Id  | Operation                  | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                |  1798K|    65M|   902K (15)| 00:00:36 |
|*  1 |  HASH JOIN                 |                |  1798K|    65M|   902K (15)| 00:00:36 |
|*  2 |   TABLE ACCESS STORAGE FULL| S_PROD_INT_EDI |    18 |   324 |    12   (9)| 00:00:01 |
|   3 |   TABLE ACCESS STORAGE FULL| S_ASSET_EDI    |   596M|    11G|   899K (14)| 00:00:36 |
---------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("A"."PROD_ID"="C"."ROW_ID")
   2 - storage("C"."PROD_ATTRIB03_CD"='Voice' AND "C"."ATM_TYPE_CD"='Postpaid')
       filter("C"."PROD_ATTRIB03_CD"='Voice' AND "C"."ATM_TYPE_CD"='Postpaid')

[Updated on: Tue, 15 December 2015 08:30]

Report message to a moderator

Re: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production [message #645832 is a reply to message #645829] Tue, 15 December 2015 08:36 Go to previous messageGo to next message
msol25
Messages: 368
Registered: June 2011
Senior Member
Dear Friends,

Any Improvement required in query at my end for Faster Retrieval?
Re: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production [message #645834 is a reply to message #645832] Tue, 15 December 2015 08:40 Go to previous messageGo to next message
BlackSwan
Messages: 25849
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/84315/
Re: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production [message #645835 is a reply to message #645828] Tue, 15 December 2015 08:45 Go to previous messageGo to next message
Michel Cadot
Messages: 65380
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I hope there is having more than 2 million records.


So who will read more than 2 million rows?
The faster query is the one you don't execute.

Re: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production [message #645837 is a reply to message #645835] Tue, 15 December 2015 08:53 Go to previous messageGo to next message
msol25
Messages: 368
Registered: June 2011
Senior Member

Dear Michel,

This Data will be Loaded into table for Business Reporting using Business Object Reporting Tool.

Smile
Re: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production [message #645839 is a reply to message #645837] Tue, 15 December 2015 08:54 Go to previous messageGo to next message
msol25
Messages: 368
Registered: June 2011
Senior Member
Dear Blackswan,

You have given URL.This is very useful.

But this time looking for faster Solution.
Re: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production [message #645841 is a reply to message #645837] Tue, 15 December 2015 08:57 Go to previous messageGo to next message
BlackSwan
Messages: 25849
Registered: January 2009
Location: SoCal
Senior Member
msol25 wrote on Tue, 15 December 2015 06:53

Dear Michel,

This Data will be Loaded into table for Business Reporting using Business Object Reporting Tool.

Smile


It cost resources of disk space, CPU cycles & time to duplicate data.
CREATE VIEW would be less resource intensive.
Re: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production [message #645842 is a reply to message #645841] Tue, 15 December 2015 08:59 Go to previous messageGo to next message
msol25
Messages: 368
Registered: June 2011
Senior Member
Dear Blackswan,

Quote:
CREATE VIEW would be less resource intensive.


We Are not creating View in this case.
Re: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production [message #645845 is a reply to message #645842] Tue, 15 December 2015 09:09 Go to previous messageGo to next message
cookiemonster
Messages: 13011
Registered: September 2008
Location: Rainy Manchester
Senior Member
I'm pretty sure this hasn't actually been clarified (we seem to be taking the explain plan as gospel) so:
1) How many rows does the query actually return when you run it?
2) How long does it actually take?
Re: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production [message #645847 is a reply to message #645845] Tue, 15 December 2015 09:14 Go to previous messageGo to next message
msol25
Messages: 368
Registered: June 2011
Senior Member

Dear Cookie,

Quote:


How many rows does the query actually return when you run it?



As per my Prediction,this will retrieve around 25-30 million records.

Quote:


How long does it actually take?



Query is still executing from past half an hour
Re: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production [message #645848 is a reply to message #645847] Tue, 15 December 2015 09:19 Go to previous messageGo to next message
Roachcoach
Messages: 1510
Registered: May 2010
Location: UK
Senior Member
Don't you think that suggests you have issues with stats?

Why are the existing hints, aside from parallel, in play?

Lastly, where is the query spending its time? What are the row outputs from each step in the execution plan?
Re: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production [message #645850 is a reply to message #645848] Tue, 15 December 2015 09:22 Go to previous messageGo to next message
msol25
Messages: 368
Registered: June 2011
Senior Member
Dear Roachcoach,

I hope stats is not having any issues.Time is consuming for considering recordset from Asset Table.

Query is spending time for Retrieving data from Asset table.
Re: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production [message #645851 is a reply to message #645850] Tue, 15 December 2015 09:26 Go to previous messageGo to next message
msol25
Messages: 368
Registered: June 2011
Senior Member
Dear Raochcoach,

Please don't mind.Actually I hope you should suggest me for creating index on Prod_id column.

Please don't consider my observation for creating index.Because this is big table and nobody will allow to create index on Asset Table.

Really Difficult for me Smile

So,Looking for some other alternative at query level.
Re: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production [message #645852 is a reply to message #645850] Tue, 15 December 2015 09:29 Go to previous messageGo to next message
cookiemonster
Messages: 13011
Registered: September 2008
Location: Rainy Manchester
Senior Member
msol25 wrote on Tue, 15 December 2015 15:22

I hope stats is not having any issues.

Well they are - explain plan thinks you're getting 2 millions, you say you're getting 12-15 times that.

msol25 wrote on Tue, 15 December 2015 15:22

Query is spending time for Retrieving data from Asset table.

You know that how?

Where exactly is this data going?
If I ran a query that got every row from a 25 million row table in sqlplus I'd expect it to take at least half an hour to render all that data. If data doesn't need to be rendered then you should expect it to be faster but never the less the problem here might be that whatever is receiving the data can't actually store it any faster.
Re: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production [message #645853 is a reply to message #645851] Tue, 15 December 2015 09:32 Go to previous messageGo to next message
cookiemonster
Messages: 13011
Registered: September 2008
Location: Rainy Manchester
Senior Member
msol25 wrote on Tue, 15 December 2015 15:26
Actually I hope you should suggest me for creating index on Prod_id column.


You're apparent trying to pull 20% of the rows in the table - oracle won't use an index for that.
Re: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production [message #645854 is a reply to message #645852] Tue, 15 December 2015 09:34 Go to previous messageGo to next message
msol25
Messages: 368
Registered: June 2011
Senior Member
Dear Cookie,

Thanks for you good suggestion.

Please suggest me any medicine which can help me to process coomplete data in SGA Shared Pool Area.

Really will helpful for me Smile
Re: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production [message #645855 is a reply to message #645851] Tue, 15 December 2015 09:35 Go to previous messageGo to next message
Roachcoach
Messages: 1510
Registered: May 2010
Location: UK
Senior Member
Are you really sure? Have you traced it?

What about the rows produced from each step?
Re: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production [message #645856 is a reply to message #645854] Tue, 15 December 2015 09:35 Go to previous messageGo to next message
msol25
Messages: 368
Registered: June 2011
Senior Member
Dear Cookie,

Quote:


You're apparent trying to pull 20% of the rows in the table - oracle won't use an index for that.



I am sure data is less than 20% only.
Re: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production [message #645857 is a reply to message #645856] Tue, 15 December 2015 09:40 Go to previous messageGo to next message
Roachcoach
Messages: 1510
Registered: May 2010
Location: UK
Senior Member
Don't be sure.

Prove it.

And find out the rows produced by each step. I would suggest OEM sql monitoring tab, or the same in sql developer. It can be gotten from the command line but that's easier for you.
Re: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production [message #645858 is a reply to message #645857] Tue, 15 December 2015 09:44 Go to previous messageGo to next message
msol25
Messages: 368
Registered: June 2011
Senior Member
Dear Roachcoach,

Please believe on me.This is not the case for which you are looking.

Really, Please believe on me Smile.
Re: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production [message #645860 is a reply to message #645858] Tue, 15 December 2015 09:50 Go to previous messageGo to next message
Roachcoach
Messages: 1510
Registered: May 2010
Location: UK
Senior Member
Sigh.

Nothing you are stating lines up with the data you have posted from that machine. It is effectively paradoxical to think an exadata machine, on flash arrays will take that length of time to process the workload you've stated (30 minutes+ with such a low level of IO)

Therefore one of two cases exist - the machine is doing FAR more work than it thinks or the query is returning faster than you say. I'm more inclined to believe the former however you seem unwilling to investigate this possibility.

If we assume the machine is working harder than the posted data would suggest, it would be prudent to work out where the mistake/mis-estimate was made.

Short of that, I suppose you could yell at the screen...

[Updated on: Tue, 15 December 2015 09:50]

Report message to a moderator

Re: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production [message #645861 is a reply to message #645856] Tue, 15 December 2015 09:50 Go to previous messageGo to next message
cookiemonster
Messages: 13011
Registered: September 2008
Location: Rainy Manchester
Senior Member
msol25 wrote on Tue, 15 December 2015 15:35
Dear Cookie,

Quote:


You're apparent trying to pull 20% of the rows in the table - oracle won't use an index for that.



I am sure data is less than 20% only.

Sigh yes, got my maths the wrong way round - it's 5%
Re: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production [message #645864 is a reply to message #645854] Tue, 15 December 2015 09:53 Go to previous messageGo to next message
cookiemonster
Messages: 13011
Registered: September 2008
Location: Rainy Manchester
Senior Member
msol25 wrote on Tue, 15 December 2015 15:34
Dear Cookie,

Thanks for you good suggestion.

Please suggest me any medicine which can help me to process coomplete data in SGA Shared Pool Area.

Really will helpful for me Smile


If the problem is actually the client (and you still haven't told us anything about the client) then there's nothing you can do with the SGA that would make the blindest bit of difference.

If you run this how long does it take:
BEGIN

FOR rec IN (SELECT  /*+ use_hash(a) parallel(a 10) index(c S_PROD_INT_PK1) */
        a.row_id
from    dm_crm_gsm.s_prod_int_edi c,
        dm_crm_gsm.s_asset_edi a
where   c.atm_type_cd = 'Postpaid'
and     c.PROD_ATTRIB03_CD = 'Voice'
and     a.prod_id = c.row_id) LOOP

null;

END LOOP;

END;
Re: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production [message #645898 is a reply to message #645864] Wed, 16 December 2015 01:17 Go to previous messageGo to next message
msol25
Messages: 368
Registered: June 2011
Senior Member
Thanks Cookie,

I Will further Analyze at my end and will reply,If my problem will getting resolved.

Thanks to everybody for giving good suggestions.
Re: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production [message #646533 is a reply to message #645898] Mon, 04 January 2016 01:48 Go to previous messageGo to next message
johnsid
Messages: 3
Registered: January 2016
Location: India
Junior Member
Some Ways to improve Query Performance
Don't use UPDATE instead of CASE
Don't blindly reuse code
Don't double-dip
Do pre-stage data
Do delete and update in batches
Re: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production [message #646541 is a reply to message #646533] Mon, 04 January 2016 02:53 Go to previous messageGo to next message
cookiemonster
Messages: 13011
Registered: September 2008
Location: Rainy Manchester
Senior Member
johnsid wrote on Mon, 04 January 2016 07:48
Some Ways to improve Query Performance
Don't use UPDATE instead of CASE

What does that mean? Update and Case do completely different things.

johnsid wrote on Mon, 04 January 2016 07:48

Don't double-dip
Do pre-stage data

Those warrant further explanation.

johnsid wrote on Mon, 04 January 2016 07:48

Do delete and update in batches


It's generally best to do everything a single hit rather than batches.
Re: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production [message #647603 is a reply to message #646541] Wed, 03 February 2016 20:57 Go to previous message
Kevin Meade
Messages: 2102
Registered: December 1999
Location: Connecticut USA
Senior Member
Hi all, nice thread. So as ROACHCOACH pointed out, this is an EXADATA box, and the query appears on the surface anyway to be an ideal candidate for the EXADATA platform.

Two critical observations of the fit to EXADATA for this query:

1. it is according to the OP fetching more that .2% (yes that says point two percent) or more than one row out of every 500.
2. it is using only a small number of columns from each of the tables involved.

These two observations suggest that EXADATA SMARTSCAN will be able to do a significant amount of filtering and projection before sending results back to the database servers for joining, which is what EXADATA does best. In the end, this query is a 2 minute query, maybe even a 20 second query, in spite of needing to scan a 1/2 billion row table. So how do we get there?

There are two things you can do with this query:

1. make sure that SMARTSCAN is being used correctly.  You can read up on the internet to see how to do this.
2. use hash partitioning on the join columns between the two tables to do FULL PARTITION WISE HASH JOIN.

Reasons the above are important:

#1 above ensures that your are getting the benefits of SMARTSCAN in reducing physical IO and network traffic.
#2 above ensures that your database servers are doing the most efficient join between large tables as is possible.

Your plan without hints if the proper plan, if the conditions stated are correct.
PLAN_TABLE_OUTPUT

Plan hash value: 3800598143
 
---------------------------------------------------------------------------------------------
| Id  | Operation                  | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                |  1798K|    65M|   902K (15)| 00:00:36 |
|*  1 |  HASH JOIN                 |                |  1798K|    65M|   902K (15)| 00:00:36 |
|*  2 |   TABLE ACCESS STORAGE FULL| S_PROD_INT_EDI |    18 |   324 |    12   (9)| 00:00:01 |
|   3 |   TABLE ACCESS STORAGE FULL| S_ASSET_EDI    |   596M|    11G|   899K (14)| 00:00:36 |
---------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("A"."PROD_ID"="C"."ROW_ID")
   2 - storage("C"."PROD_ATTRIB03_CD"='Voice' AND "C"."ATM_TYPE_CD"='Postpaid')
       filter("C"."PROD_ATTRIB03_CD"='Voice' AND "C"."ATM_TYPE_CD"='Postpaid')

As far as partitioning goes, repartitioning the large table will likely take several hours so do it in test somewhere first please. However, since the query plan does not appear to need temp space due to the limited number of tables in the query, your benefit from FPWHJ may be limited to only reducing interconnect traffic on RAC and the speed up provided by doing the query in parallel. If you are not using RAC then there is no interconnect traffic, if you are not using parallel query then there is no speed benefit (but your said parallel 10 so that looks good). Some new terms here I know. My point is that parallel query and partitioning are not silver bullets (boy I hate that term). Try to keep expectations muted till you have done some real testing with it and can confirm that these changes are the things that actually provided the benefit.

partition by hash (PROD_ID) partitions 128
partition by hash (ROW_ID) partitions 128


You can get Tanel Poder's book on EXADATA to learn how to see what it is doing.
You can get my book on SQL Tuning (it has a chapter on EXADATA directed to an app developer's perspective).

At the moment, Tanel's book is actually free on Kindle for a limited preview. Full book limited to a short number of days. You could easily read it and find what you need as what you want to know is in the first couple of chapters; so get over to Amazon now and download their free Kindle reader so you can learn from that book.

If you are interested in my book, check out these free items first before you buy it. My book is not available for free preview yet (not sure why).
Provided below are:

1. the first chapter of the book.  Reading this will help you decide if the book is something you are interested in before you spend money on it.
2. the scripts from the book.  You can use these in your tuning work regardless of it you purchase the book or not later.  These are free.
3. an organizational document that will help you record the significant events of a SQL Tuning session so you can explain to someone else later how you solved a problem.
4. a brief description of the kind of info you will want to provide to ORAFaq, when you are looking for detailed tuning help.

These artifacts are free, you do not need to buy the book to use them, and you can give them to others freely as well. Good luck. Kevin
Previous Topic: Sql Query Tuning
Next Topic: Oracle index creation
Goto Forum:
  


Current Time: Sun Feb 18 18:17:31 CST 2018

Total time taken to generate the page: 0.01061 seconds