What does ALTER FRAGMENT do extending a fragment?
Dennis Melnikov saw a long pause when ALTER FRAGMENT extended a partition's range. Art Kagel guessed it read the other partitions for qualifying rows and that a new partition was created; David Williams suggested checking the thread's stack with onstat -g stk. Melnikov concluded the server was still reading data, since nreads counts only disk reads.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hi,
Running ALTER FRAGMENT extending a fragment I notice a significant pause when the session does nothing (?)
ALTER FRAGMENT ON TABLE tab01MODIFY PARTITION tab01_0047 TO PARTITION tab01_0047
((item_id >= 115000) AND (item_id < 120000))
-- was: item_id>=115100 AND item_id<120000
IN dbspace01;
First, the session reads data.
At the end, it opens a transaction to move data into the fragment.
And between these steps there is a long interval-what does it do at that time?
The matter is that duration of the interval does not depend just on size of the increment. Extending a fragment closer to MAX(item_id) takes less than one in the middle.
------------------------------
Sincerely,
Dennis
------------------------------
What the ALTER FRAGMENT is likely doing during that "pause" is reading all of the other partitions to determine if any rows qualify to be moved to the new partition. ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------
Does nreads counter of `onstat -u` increase while session is reading?
During the pause the nreads counter stalls, as well as nwrites.
BTW, I don't add a new partition, I extend an existing one.
------------------------------
Sincerely,
Dennis
------------------------------
Under the hood, adding an additional partition and altering the partition expression for an existing partition are handled the same way. A new partition is created, data is moved into it, and the original partition is dropped. You may note that the partnum for this partition is different from the original one. it is strange though that the nreads count is not moving. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------
Hi,
onstat -g ses |<sid> - get thread id.
onstat -g stk <thread-id>What does the stack say it is doing?
Regards,
David.
------------------------------
David Williams
------------------------------
Hi Art, From my perspective, extending fragment limits does not create a new partition, the partnum remains the same. And I have to admit I was wrong that there is an idle delay. The server still reads data, nreads just does not show it `cause it counts disk reads. ------------------------------ Sincerely, Dennis ------------------------------