| Please help for Improving Query Performance [message #645823] |
Tue, 15 December 2015 08:14  |
 |
msol25
Messages: 396
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 #645829 is a reply to message #645828] |
Tue, 15 December 2015 08:29  |
|
msol25
Messages: 396
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 #645852 is a reply to message #645850] |
Tue, 15 December 2015 09:29 |
cookiemonster
Messages: 13958
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 #645860 is a reply to message #645858] |
Tue, 15 December 2015 09:50 |
Roachcoach
Messages: 1576
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 #646541 is a reply to message #646533] |
Mon, 04 January 2016 02:53 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
johnsid wrote on Mon, 04 January 2016 07:48Some 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 |
 |
Kevin Meade
Messages: 2103
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
