Re: Datafile HWM without querying dba_extents

  • From: MARK BRINSMEAD <mark.brinsmead@xxxxxxxxx>
  • To: k3nnyp@xxxxxxxxx
  • Date: Sat, 31 Jan 2015 16:05:24 -0500

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@xxxxxxxxx> 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@xxxxxxxxxxxxxxxxxx>
> 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
> @jloracle
> ------------------------------
> *From:* oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on
> behalf of Deepak Sharma [dmarc-noreply@xxxxxxxxxxxxx]
> *Sent:* 31 January 2015 05:57
> *To:* oracle-l@xxxxxxxxxxxxx
> *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.
>
>
>
>

Other related posts: