All Forums Database
ahmed2502 11 posts Joined 12/12
11 Jul 2014
All AMP active for Updates based on primary index

Hi All,
I am updating my tables based on primary index, however from the DBQL I see all the amps are active. Could someone explain, if this is expected ?
 
Cannot post the table structre for confidentialy reasons.
But it's like:
MyTable
(Column1 CHAR(10)
,Column2 CHAR(5)
,Column3 INTEGER
....
) PRIMARY_INDEX(Column1 , Column2);
Query:
UPDATE MyTable
Set Column3 = ? , Column = ? ....
Where Column1 = ? AND Column2 = ?
 
The table has less than 100 rows and stats are collected over primary index.
I was expecting this to be 1 AMP operation.
 

VandeBergB 182 posts Joined 09/06
11 Jul 2014

There may be rows on every amp that are getting updated. 

Some drink from the fountain of knowledge, others just gargle.

ahmed2502 11 posts Joined 12/12
11 Jul 2014

How can rows be on every amp ? I have the primary index in where clause. Here is the explain plan:
 

The explain plan shows Single Amp Update.

Dixxie 58 posts Joined 12/10
11 Jul 2014

Ahemmad,
The registers will be storaged at the AMP = hash(Column1 , Column2)

Your query will update one or more registers (if you define the table as Multiset),
but allways in the same AMP.

Regards.

ToddAWalter 316 posts Joined 10/11
11 Jul 2014

- how are you determining that all amps are active for that single update?
- are you running multiple updates concurrently - against the same or multiple tables?

ahmed2502 11 posts Joined 12/12
14 Jul 2014

Thanks Dixxie and Todd.
I am running only single update at a time.
My table is defined as MultiSet - I don't see why the behaviour would be different.
I found out the number of Amps active from DBQL field  NumOfActiveAMPs.
 
When I run explain on the update statement, it shows only 1 AMP operation. But not sure why the DBQL log shows otherwise.
Since the update is going to be against huge table in live environment, I am worried for the performance issue that may arise for this.

dnoeth 4628 posts Joined 11/04
14 Jul 2014

The only case I can imagine if it's actually a single update using the PI is a LOCKING TABLE.
Do you run the update against a view with?
Then switching to LOCKING ROW shoul help.

Dieter

ahmed2502 11 posts Joined 12/12
15 Jul 2014

hi dnoeth,
I run update against view and the view is defined as locking row for access. Surprisingly, the same query in production results 1 Active Amp (as it should be), whereas in UAT its all AMP. 

You must sign in to leave a comment.