This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Feb 19, 2013 7:10 AM by JohnWatson RSS

impact to production if I shrink space on table...

977635 Newbie
Currently Being Moderated
Hello experts.

I am running Oracle 11.2 and saw in OEM that I have a table that is candidate for shrinking space.
According to OEM, I can gain 5G by shrinking the table.
The table is 16G, so if I shrink the table, then it should reduce to approx. 11G, right?
This table is the primary table used by our application, so impact to production is very important.

Okay, so I'm wondering what the impact to production is on the table while I shrink the space on the table.
I am testing it now in my test environment.
First, I found I had a function based index which has to be dropped before I can shrink the table.
So I ran dbms_metadata to get the syntax to recreate the FBI, then dropped the FBI.

Next, i ran alter table owner.tablename enable row movement;
Then, I ran alter table owner.tablename shrink space cascade;

While I am running the shrink table command, I opened another session and I queried the table and even did updates on the table.
I was quite surprised in that the table is not locked and is still accessible for update and select statements.
In fact, I do not see any noticeable impact by the shrink table command.
Obviously, I am not in the production environment with hundreds of concurrent users, but my question is what kind of impact should I expect to see if I do this in production with people hitting this table? (No, this is not an IOT.)

Any ideas?

Edited by: 974632 on Feb 19, 2013 5:54 AM

NOTE: The table shrink command took: Elapsed: 00:43:17.19
Recreating the function based index took: Elapsed: 00:00:28.80
  • 1. Re: impact to production if I shrink space on table...
    Fran Guru
    Currently Being Moderated
    shrink space can make performance issues and invalide objects if you use cascade option.

    Please check:
    http://docs.oracle.com/cd/E11882_01/server.112/e10595/schema003.htm#CBBBIADA
  • 2. Re: impact to production if I shrink space on table...
    UweHesse Expert
    Currently Being Moderated
    What you observe is standard behavior.
    A potential impact comes from the massive internal DELETE & INSERT during the shrink operation.
    Will generate lots of redo & undo therefore.
    That's is not necessarily a problem, though.

    Kind regards
    Uwe Hesse

    "Don't believe it, test it!"
    http://uhesse.com
  • 3. Re: impact to production if I shrink space on table...
    EdStevens Guru
    Currently Being Moderated
    Uwe Hesse wrote:
    What you observe is standard behavior.
    A potential impact comes from the massive internal DELETE & INSERT during the shrink operation.
    Will generate lots of redo & undo therefore.
    That's is not necessarily a problem, though.

    Kind regards
    Uwe Hesse

    "Don't believe it, test it!"
    http://uhesse.com
    Uwe,

    Correct me if I'm wrong, but my thought on reading the OP is that he could very well see a performance degradation during the shrink operation, but I'd also expect to see some overall improvement after the operation due to a large decrease in the number of blocks that would have to be scanned during any FTS, and possibly some cascading of that effect back through any indexes on that table.
  • 4. Re: impact to production if I shrink space on table...
    977635 Newbie
    Currently Being Moderated
    Thanks Fran. I see that it says: As with other DDL operations, segment shrink causes subsequent SQL statements to be reparsed because of invalidation of cursors...

    This makes sense. But I don't see how it can cause dependent objects to become invalid. I read the link at the end of the warning statement, +(Note:+
    Shrinking database segments online might cause dependent database objects to become invalid. See "About Object Dependencies and Object Invalidation".) and it doesn't mention anything about dependent objects becoming invalid from the shrink command, but I imagine it is possible. Question would be how to avoid it or how to protect ourselves from it?

    Also, I wonder since rows are moved, do all the indexes have to be rebuilt? We have several indexes on the table to be shrunk.
  • 5. Re: impact to production if I shrink space on table...
    977635 Newbie
    Currently Being Moderated
    You are absolutely correct Ed. That is exactly my main goal, (not recovery of space).
    Our application regularly does full table scans on this table and my thinking is that it should improve performance significantly by reducing the number of blocks for 16G of space down to 11G of space.
  • 6. Re: impact to production if I shrink space on table...
    Fran Guru
    Currently Being Moderated
    The CASCADE clause extends the segment shrink operation to all dependent segments of the object. For example, if you specify CASCADE when shrinking a table segment, all indexes of the table will also be shrunk. (You need not specify CASCADE to shrink the partitions of a partitioned table.) To see a list of dependent segments of a given object, you can run the OBJECT_DEPENDENT_SEGMENTS procedure of the DBMS_SPACE package.
    HTH
  • 7. Re: impact to production if I shrink space on table...
    977635 Newbie
    Currently Being Moderated
    So now I have a critical question about the table that i shrunk:

    I wonder since rows are moved in this process, do all the indexes have to be rebuilt?
  • 8. Re: impact to production if I shrink space on table...
    977635 Newbie
    Currently Being Moderated
    I get that part Fran, but where does it talk about how it can cause dependent objects to become invalid, and why would it cause them to become invalid?
    I am concerned that the indexes would need to be rebuilt since shrinking the table causes rows to be moved.
    Perhaps this is what it is referring to. I'm not sure.
  • 9. Re: impact to production if I shrink space on table...
    JohnWatson Guru
    Currently Being Moderated
    974632 wrote:
    So now I have a critical question about the table that i shrunk:

    I wonder since rows are moved in this process, do all the indexes have to be rebuilt?
    Indexes are maintained during the shrink: it just normal DML, matched INSERTs and DELETEs in a set of transactions. Triggers are disabled for the operation (but not of course for anything else). If you shrink an index (or use CASCADE) then the index is coalesced. That's all. You don't need to do anything.
    --
    John Watson
    Oracle Certified Master DBA
    http://skillbuilders.com
  • 10. Re: impact to production if I shrink space on table...
    977635 Newbie
    Currently Being Moderated
    Thank you very much John. I've searched several web sites for this information without any luck.
    This is very helpful..
  • 11. Re: impact to production if I shrink space on table...
    Aman.... Oracle ACE
    Currently Being Moderated
    974632 wrote:
    So now I have a critical question about the table that i shrunk:

    I wonder since rows are moved in this process, do all the indexes have to be rebuilt?
    Nope, indexes are going to remain valid and will be automatically maintained with the shrink command going on(as John has mentioned already) .

    Aman....
  • 12. Re: impact to production if I shrink space on table...
    977635 Newbie
    Currently Being Moderated
    Thank you for the confirmation.
    I finally found a web site that confirms that as well.

    http://managingoracle.blogspot.com/2010/08/oracle-table-reorganization-online-or.html

    Triggers and indexes:
    In contradiction to moving a table, or exporting/importing its data, triggers and indexes are NOT affected by a shrink space command. They will remain valid and functional during and after the operation.
  • 13. Re: impact to production if I shrink space on table...
    Fran Guru
    Currently Being Moderated
    No, shrink don't invalided indexes.

    It means that when you use CASCADE option, index will be shrunk too. When you use shrink you use the clause "alter table XXXX enable row movement" that can cause invalid objects like triggers or procedures related to XXXX table.
    Also, if you issue the following statement "alter table <table name> enable/disable row movement;" 
    it will invalidate any stored procedure that references that table. But, once you reference that 
    invalid stored procedure, Oracle recompiles it
    from :http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:35203106066718
  • 14. Re: impact to production if I shrink space on table...
    977635 Newbie
    Currently Being Moderated
    Thank you Fran.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points