OraFAQ Forum: Performance Tuning » SQL Statement taking too long (merged 3)

Home » RDBMS Server » Performance Tuning » SQL Statement taking too long (merged 3) (Oracle 11.2.0, AIX 6.1 (64bit))

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

SQL Statement taking too long (merged 3) [message #564102]

Tue, 21 August 2012 07:10

Creems
Messages: 70
Registered: July 2007
Location: Johannesburg

Member

Hi there,

I have a statements that is performing differently on 2 different systems.

With the below plans, I wonder why it's taking so long on db6..
- Could it be related to connected sessions?

Here is the explain plan:

## Database - db7 (CPU => 16, Cores => 4, RAM => 48.00)
- query ran for 10min
- Currently have 173 sessions running

PLAN_TABLE_OUTPUT
| Id  | Operation             | Name                         |Rows | Bytes|TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                              | 20M | 4821M|       | 5429K (1)  | 27:08:54 |
|*  1 |  HASH JOIN RIGHT OUTER|                              | 20M | 4821M|678M   | 5429K (1)  | 27:08:54 |
|   2 |   TABLE ACCESS FULL   | TMP_STG_IBEQUIPMENT_PREPAID  | 24M |  397M|       | 5132K (1)  | 25:39:40 |
|   3 |   TABLE ACCESS FULL   | TMP_SUBSCRIPTION_BASE        | 20M | 4410M|       |  118K (1)  | 00:35:34 |

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("MAIN"."MSISDN"="TMP_STG_IBEQUIPMENT_PREPAID"."MSISDN"(+))



## Database - db6 (CPU => 48, Cores => 12, RAM => 48.00)
- query is running for more than one hour
- Currently have 444 sessions running

PLAN_TABLE_OUTPUT

| Id  | Operation             | Name                         |Rows | Bytes|TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         		     |  20M| 4821M|       |   286K  (1)| 01:26:04 |
|*  1 |  HASH JOIN RIGHT OUTER|                              |  20M| 4821M|  678M |   286K  (1)| 01:26:04 |
|   2 |   TABLE ACCESS FULL   | TMP_STG_IBEQUIPMENT_PREPAID  |  24M|  397M|       |  29615  (1)| 00:08:54 |
|   3 |   TABLE ACCESS FULL   | TMP_SUBSCRIPTION_BASE        |  20M| 4410M|       |  78278  (2)| 00:23:30 |

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("MAIN"."MSISDN"="TMP_STG_IBEQUIPMENT_PREPAID"."MSISDN"(+))

Thank you.

Cherish
*BlackSwan added {code} tags. Do so yourself in the future

[Updated on: Tue, 21 August 2012 17:00] by Moderator

Report message to a moderator

Re: SQL Statement taking too long (merged 3) [message #564105 is a reply to message #564102]

Tue, 21 August 2012 07:21

cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester

Senior Member

Explain plans should be wrapped in code tags as described here How to use [code] tags and make your code easier to read? otherwise they are hard to read.
The plans say the slow one is faster, so they're not telling us anything useful. Trace the sessions to see where the time is being spent.

Report message to a moderator

Re: SQL Statement taking too long (merged 3) [message #564107 is a reply to message #564105]

Tue, 21 August 2012 07:32

Creems
Messages: 70
Registered: July 2007
Location: Johannesburg

Member

## Database - db7 (CPU => 16, Cores => 4, RAM => 48.00)
- query ran for 10min
- Currently have 173 sessions running

PLAN_TABLE_OUTPUT
| Id | Operation              | Name                        | Rows | Bytes|TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0  | SELECT STATEMENT       |                             | 20M  | 4821M|       | 5429K (1) | 27:08:54 |
|* 1 | HASH JOIN RIGHT OUTER  |                             | 20M  | 4821M|  678M | 5429K (1) | 27:08:54 |
| 2  | TABLE ACCESS FULL      | TMP_STG_IBEQUIPMENT_PREPAID | 24M  |  397M|       | 5132K (1) | 25:39:40 |
| 3  | TABLE ACCESS FULL      | TMP_SUBSCRIPTION_BASE | 20M | 4410M|      |       |  118K (1) | 00:35:34 |

Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("MAIN"."MSISDN"="TMP_STG_IBEQUIPMENT_PREPAID"."MSISDN"(+))



## Database - db6 (CPU => 48, Cores => 12, RAM => 48.00)
- query is running for more than one hour
- Currently have 444 sessions running

PLAN_TABLE_OUTPUT

| Id | Operation             | Name                          |Rows | Bytes|TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
| 0  | SELECT STATEMENT      |                               |  20M| 4821M|       |   286K (1) | 01:26:04 |
|* 1 | HASH JOIN RIGHT OUTER |                               |  20M| 4821M|  678M |   286K (1) | 01:26:04 |
| 2  | TABLE ACCESS FULL     | TMP_STG_IBEQUIPMENT_PREPAID   |  24M|  397M|       |   29615 (1)| 00:08:54 |
| 3  | TABLE ACCESS FULL     | TMP_SUBSCRIPTION_BASE         |  20M| 4410M|       |   78278 (2)| 00:23:30 |

Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("MAIN"."MSISDN"="TMP_STG_IBEQUIPMENT_PREPAID"."MSISDN"(+))

Report message to a moderator

Re: SQL Statement taking too long (merged 3) [message #564158 is a reply to message #564107]

Tue, 21 August 2012 13:40

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

Could you please send two execution plans instead of these explain plans. And your sql, too.

Report message to a moderator

Re: SQL Statement taking too long (merged 3) [message #564173 is a reply to message #564158]

Tue, 21 August 2012 16:48

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Are the tables the same size in each database?

It is possible that if less memory is allocated on one database then the hash join will spill to disk.
Are you using Automatic Memory Management or Automatic PGA Memory Management? What are the values of parameters MEMORY_TARGET and PGA_AGGREGATE_TARGET on each database?

Ross Leishman

Report message to a moderator

Re: SQL Statement taking too long (merged 3) [message #564174 is a reply to message #564173]

Tue, 21 August 2012 16:52

Creems
Messages: 70
Registered: July 2007
Location: Johannesburg

Member

Yes, the tables are same sizes.

One more thing I forget to mention.

DB7 runs in AMM while DB6 is not.

I was also suspecting manual memory management in DB6.

I'll set DB6 to AMM and see again.

Thanks a lot.

Report message to a moderator

Previous Topic:	Slow query with full table scan
Next Topic:	Frequent changed table stats problem

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Nov 21 16:17:30 CST 2024