OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » tunning (Oracle 10G)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

tunning [message #345586]

Thu, 04 September 2008 04:45

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,

Report message to a moderator

Re: tunning [message #345601 is a reply to message #345586]

Thu, 04 September 2008 04:57

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: tunning [message #345634 is a reply to message #345601]

Thu, 04 September 2008 06:03

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

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

Report message to a moderator

Re: tunning [message #345644 is a reply to message #345634]

Thu, 04 September 2008 06:25

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

saagar
Messages: 79
Registered: December 2007

Member

hi raj

which link

link provided by micheal...

of performance tuning guide ???

CHeers

Report message to a moderator

Re: tunning [message #345654 is a reply to message #345634]

Thu, 04 September 2008 06:41

rleishman
Messages: 3728
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.

Report message to a moderator

Re: tunning [message #345673 is a reply to message #345654]

Thu, 04 September 2008 07:30

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

rleishman
Messages: 3728
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

Report message to a moderator

Re: tunning [message #345826 is a reply to message #345813]

Thu, 04 September 2008 23:46

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 26 14:11:38 CST 2024