Home » RDBMS Server » Performance Tuning » tunning (Oracle 10G)
tunning [message #345586] Thu, 04 September 2008 04:45 Go to next message
saagar
Messages: 79
Registered: December 2007
Member
dear all
i have a explain plan out out for a particular table ..
the query takes too long time to execute
please help me to solve the query tuning

the plan output is as follows

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------

Plan hash value: 1745514393



----------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ

----------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 16015 | 500K| 72449 (3)| 00:16:55 | | |

| 1 | PX COORDINATOR | | | | | | | |

| 2 | PX SEND QC (RANDOM) | :TQ10002 | 16015 | 500K| 72449 (3)| 00:16:55 | |

| 3 | HASH GROUP BY | | 16015 | 500K| 72449 (3)| 00:16:55 | | | Q1,02

| 4 | PX RECEIVE | | 16015 | 500K| 72449 (3)| 00:16:55 | | | Q1,02

| 5 | PX SEND HASH | :TQ10001 | 16015 | 500K| 72449 (3)| 00:16:55 | |



PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------

| 6 | HASH GROUP BY | | 16015 | 500K| 72449 (3)| 00:16:55 | | | Q1,0

|* 7 | HASH JOIN | | 200M| 6113M| 72377 (3)| 00:16:54 | | | Q1,01

| 8 | PX RECEIVE | | 918K| 14M| 441 (1)| 00:00:07 | | | Q1,01

| 9 | PX SEND BROADCAST | :TQ10000 | 918K| 14M| 441 (1)| 00:00:07 | | | Q

| 10 | PX BLOCK ITERATOR | | 918K| 14M| 441 (1)| 00:00:07 | | | Q1,00

|* 11 | TABLE ACCESS FULL| X_CORP_VWR_REF | 918K| 14M| 441 (1)| 00:00:07 | |

| 12 | PX BLOCK ITERATOR | | 6990M| 104G| 71693 (2)| 00:16:44 | 1 | 172 | Q1,01

| 13 | TABLE ACCESS FULL | L_EDW_CLI_CALL_REVENUE | 6990M| 104G| 71693 (2)| 00:16:44 | 1 |

----------------------------------------------------------------------------------------------------



Predicate Information (identified by operation id):



PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------

---------------------------------------------------



7 - access("B"."OU_NUM"="A"."ACCOUNT_NUM")

11 - filter("B"."OU_NUM" IS NOT NULL)



26 rows selected.



Thanks & Regards,

Re: tunning [message #345601 is a reply to message #345586] Thu, 04 September 2008 04:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

You can first read:
- OraFAQ Forum Guide, Performance Tuning section
- How to Identify Performance Problem and Bottleneck
- OraFAQ Performance Tuning
- Rosco's SQL Tuning Guide

Then provide the requested and usual information.

You can also have a look at Database Performance Tuning Guide.

Regards
Michel
icon9.gif  Re: tunning [message #345634 is a reply to message #345601] Thu, 04 September 2008 06:03 Go to previous messageGo to next message
saagar
Messages: 79
Registered: December 2007
Member
----------------------------------------------------------------------------------------------------

Plan hash value: 1745514393

 

----------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name                   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ 

----------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                        | 16015 |   500K| 72449   (3)| 00:16:55 |       |       |       

|   1 |  PX COORDINATOR             |                        |       |       |            |          |       |       |       

|   2 |   PX SEND QC (RANDOM)       | :TQ10002               | 16015 |   500K| 72449   (3)| 00:16:55 |       |   

|   3 |    HASH GROUP BY            |                        | 16015 |   500K| 72449   (3)| 00:16:55 |       |       |  Q1,02

|   4 |     PX RECEIVE              |                        | 16015 |   500K| 72449   (3)| 00:16:55 |       |       |  Q1,02

|   5 |      PX SEND HASH           | :TQ10001               | 16015 |   500K| 72449   (3)| 00:16:55 |       |       

 

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------

|   6 |       HASH GROUP BY         |                        | 16015 |   500K| 72449   (3)| 00:16:55 |       |       |  Q1,0

|*  7 |        HASH JOIN            |                        |   200M|  6113M| 72377   (3)| 00:16:54 |       |       |  Q1,01

|   8 |         PX RECEIVE          |                        |   918K|    14M|   441   (1)| 00:00:07 |       |       |  Q1,01

|   9 |          PX SEND BROADCAST  | :TQ10000               |   918K|    14M|   441   (1)| 00:00:07 |       |       |  Q

|  10 |           PX BLOCK ITERATOR |                        |   918K|    14M|   441   (1)| 00:00:07 |       |       |  Q1,00

|* 11 |            TABLE ACCESS FULL| X_CORP_VWR_REF         |   918K|    14M|   441   (1)| 00:00:07 |       |     

|  12 |         PX BLOCK ITERATOR   |                        |  6990M|   104G| 71693   (2)| 00:16:44 |     1 |   172 |  Q1,01

|  13 |          TABLE ACCESS FULL  | L_EDW_CLI_CALL_REVENUE |  6990M|   104G| 71693   (2)| 00:16:44 |     1 |

[COLOR=blue][/COLOR]


this the output
the query is taking toolong ...please help me to resolve this problem

CHeers

[Updated on: Thu, 04 September 2008 06:23]

Report message to a moderator

Re: tunning [message #345643 is a reply to message #345634] Thu, 04 September 2008 06:24 Go to previous messageGo to next message
dwarak.k
Messages: 61
Registered: June 2008
Location: Hyderabad
Member
Post the Query.
The bottle neck seems to be scanning this table L_EDW_CLI_CALL_REVENUE
Re: tunning [message #345644 is a reply to message #345634] Thu, 04 September 2008 06:25 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
It's so kind of you of not posting the actual query and only the plan.

Do you realised you are scanning all the partitions (172) in the table and it is returning 6.9 Billion rows which is joined with another table of approx 1 Million records. I don't know how else we could fine tune it. So you need to share with us what you want to do and rather than saying it is taking too long to execute tell us the actual problem. Also, it will be more helpful for you if you could read the very first link in this forum section.

Regards

Raj

[Updated on: Thu, 04 September 2008 06:26]

Report message to a moderator

Re: tunning [message #345651 is a reply to message #345644] Thu, 04 September 2008 06:35 Go to previous messageGo to next message
saagar
Messages: 79
Registered: December 2007
Member
hi raj

which link

link provided by micheal...

of performance tuning guide ???

CHeers
Re: tunning [message #345654 is a reply to message #345634] Thu, 04 September 2008 06:41 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Since you haven't seen it relevant to post the text of the SQL, I'll just guess. Let's say - for argument's sake - that the SQL is as follows:
SELECT x, sum(z)
   FROM ( SELECT x,y, count(*)
   FROM   L_EDW_CLI_CALL_REVENUE a
   JOIN   X_CORP_VWR_REF b
   ON     b.OU_NUM = a.ACCOUNT_NUM
   WHERE  B.OU_NUM IS NOT NULL
   GROUP BY x, y
GROUP BY x


Questions:

1. How many of the rows in X_CORP_VWR_REF have a null OU_NUM?
2. How many rows in X_CORP_VWR_REF?
3. Is OU_NUM a Unique or Primary Key? If not, how many rows on average have the same value?
4. Is ACCOUNT_NUM a Unique or Primary Key? If not, how many rows on average have the same value?
5. How many rows in L_EDW_CLI_CALL_REVENUE?
6. How many rows in the join BEFORE you do the GROUP BY

I see two GROUP BYs in the plan. Is it possible to do with just one?

Ross Leishman

P.S. Oh yeah. Almost forgot. Post the SQL.
Re: tunning [message #345673 is a reply to message #345654] Thu, 04 September 2008 07:30 Go to previous messageGo to next message
saagar
Messages: 79
Registered: December 2007
Member
Dear all
i am very sorry i didnt post the query

here is the quewry

SELECT  a.sac, ROUND (SUM (a.tot_spend), 2) tot_spend1
    FROM (SELECT 
                 b.sac_code sac, a.cli_call_revenue tot_spend, a.bill_date,
                 TRUNC (ADD_MONTHS (SYSTIMESTAMP, -3), 'MM') systime_stp,
                 (TRUNC (SYSDATE, 'MM') - 1) last_mont
            FROM corporate_viewer.s_l_edw_cli_call_revenue a,
                 corporate_viewer.x_corp_vwr_ref b
           WHERE b.ou_num = a.account_num) a
GROUP BY a.sac


1. How many of the rows in X_CORP_VWR_REF have a null OU_NUM?
NONE
2. How many rows in X_CORP_VWR_REF? 20k
3. Is OU_NUM a Unique or Primary Key? If not, how many rows on average have the same value? it is not a primary key nor unique but index is created
4. Is ACCOUNT_NUM a Unique or Primary Key? If not, how many rows on average have the same value? it is not a primary key nor a unique but index is created non unique...
5. How many rows in L_EDW_CLI_CALL_REVENUE? 7billion
6. How many rows in the join BEFORE you do the GROUP BY


{remove surplus cr/lf from code}

[Updated on: Thu, 04 September 2008 07:52] by Moderator

Report message to a moderator

Re: tunning [message #345813 is a reply to message #345673] Thu, 04 September 2008 22:30 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You plan is optimal for that query. You should be looking to write a better query though.

You are saying that the join-key is NOT UNIQUE IN EITHER TABLE.

That means the join will perform a mini-cartesian-product for each key value. For example, if table A has 15 rows with join key value 'X' and table B has 10 rows with join key value 'X', then the join will produce 15x10=150 rows for that one key.

Even if there are just 2 rows per key in X_CORP_VWR_REF, that still multiplies the join from 7 billion rows to 14 billion. Whatever number you end up with, Oracle then has to group this result in TEMP space for the GROUP BY - that's what is taking the time.

Reduce the size of the intermediate result set by choosing a unique join key, and you reduce the amount of temp space required.

Ross Leishman
Re: tunning [message #345826 is a reply to message #345813] Thu, 04 September 2008 23:46 Go to previous message
saagar
Messages: 79
Registered: December 2007
Member
hi ross

thank you very much for suggestion........
they really helped me alot.......
will try to write a good sql statement.....

CHeers
Previous Topic: Query to check that statistics gathered
Next Topic: to determine join order for tuning
Goto Forum:
  


Current Time: Wed Dec 07 09:04:56 CST 2016

Total time taken to generate the page: 0.05487 seconds