Re: Datafile HWM without querying dba_extents
Date: Sat, 31 Jan 2015 16:05:24 -0500
Message-ID: <CAAaXtLC7gN+HfOXPwCt7K=+9+qXvDdW7Da5NgR5JQ93mLxzOpQ_at_mail.gmail.com>
Definitely a great approach here, and well worth remembering.
Sometimes you *will* need to query views like DBA_EXTENTS in databases with very large numbers of objects/segments/extents, and these queries can definitely take a very long time to run. When the database belongs to a customer, gathering stats might not even be an option. :-(
In cases like this (all one or two that I can recall), I have achieved good results by unwinding the DBA_ views and removing the fluff I don't need. There's a good chance you can probably eliminate a few joins, and on occasion (e.g., a database using only LMTs, so you don't care about information for DMTs) you might even eliminate some UNIONS.
On one occasion, I was able to take a daily health-check query that ran for 36 hours in a particularly degenerate case, and get the information I *really* needed in less than 2 minutes.
In this case, though, turning the problem on its head and finding a completely different (and much better!) source for the information is much superior.
On Sat, Jan 31, 2015 at 9:20 AM, Kenny Payton <k3nnyp_at_gmail.com> wrote:
>
> Thanks Jonathan, this is too simple, I can’t wait to rewrite my
> resize_tablespace.sql script. It’s funny how you head down a road and
> never look back. I’ve been using a script that joins dba_extents,
> db_data_files and dba_tablespaces for some time. It works great but time
> consuming on large tablespaces with many segments/extents. This approach
> turns the job upside down and I suspect will be much faster to derive the
> same result.
>
> Kenny
>
>
>
>
>
>
> On Jan 31, 2015, at 5:56 AM, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
> wrote:
>
>
>
> On second thoughts, why are you querying dba_extents to find where last
> used block id is ? If all you want to do is shrink the datafile then
> querying user_free_space (ordered by file id and block id) will allow you
> to find the starting block of the highest free area in file.
>
> You only need to query dba_extents if you think you've got a lot of space
> lower down the file and think that moving a couple of small objects might
> be sufficient to clear the way to releasing it.
>
>
>
>
>
> Regards
> Jonathan Lewis
> http://jonathanlewis.wordpress.com
> _at_jloracle
> ------------------------------
> *From:* oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on
> behalf of Deepak Sharma [dmarc-noreply_at_freelists.org]
> *Sent:* 31 January 2015 05:57
> *To:* oracle-l_at_freelists.org
> *Subject:* Datafile HWM without querying dba_extents
>
> In order to resize a datafile to release space at the end, we need to find
> whatever the last block_id that is at the start of that free contiguous
> space.
>
> Problem is that we have a very large database such that querying
> dba_extents to find the last block is probably not an option. The standard
> query(ies) that make use of dba_extents runs for hours at stretch and also
> sometimes fails with a 'snapshot too old' (just gives up).
>
> Is there an alternative to using dba_extents?
>
> For example, if the datafile size is 100mb and the last 10mb is vacant, I
> want to know the block_id of where that 10mb begins.
>
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Sat Jan 31 2015 - 22:05:24 CET