Home » RDBMS Server » Performance Tuning » SQL Statement taking too long (merged 3) (Oracle 11.2.0, AIX 6.1 (64bit))
SQL Statement taking too long (merged 3) [message #564102] Tue, 21 August 2012 07:10 Go to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 10961
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.
Re: SQL Statement taking too long (merged 3) [message #564107 is a reply to message #564105] Tue, 21 August 2012 07:32 Go to previous messageGo to next message
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"(+))
Re: SQL Statement taking too long (merged 3) [message #564158 is a reply to message #564107] Tue, 21 August 2012 13:40 Go to previous messageGo to next message
LNossov
Messages: 288
Registered: July 2011
Location: Germany
Senior Member
Could you please send two execution plans instead of these explain plans. And your sql, too.
Re: SQL Statement taking too long (merged 3) [message #564173 is a reply to message #564158] Tue, 21 August 2012 16:48 Go to previous messageGo to next message
rleishman
Messages: 3700
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
Re: SQL Statement taking too long (merged 3) [message #564174 is a reply to message #564173] Tue, 21 August 2012 16:52 Go to previous message
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.
Previous Topic: Slow query with full table scan
Next Topic: Frequent changed table stats problem
Goto Forum:
  


Current Time: Thu Sep 18 13:11:19 CDT 2014

Total time taken to generate the page: 0.13851 seconds