Re: Find accessed partitions

But in this system many people runs queries against it so I am not sure how exact can this be? I mean it is easy if I am alone but it is not the case

Alex

2010/9/17 Teehan, Mark <mark.teehan_at_credit-suisse.com>

>
> Why not use v$segment_statistics?
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Yong Huang
> Sent: 15 September 2010 22:39
> To: ax.mount_at_gmail.com
> Cc: oracle-l_at_freelists.org
> Subject: Re: Find accessed partitions
>
> Alex,
>
> If event 10128 is too resource intensive, and if each of the partitions
> uses its own tablespace, you can bring some tablespaces offline to see
> if the query fails to read a datafile. Try half of possible partitions
> at a time to speed up the research. If the partitions are in one
> tablespace but you can afford moving them to their own tablespaces, you
> can work that way too.
>
> I wish we could change a partition to offline or unusuable.
>
> Other than that, you may guess the partitions by checking the file and
> block numbers in v$bh (or x$bh) when the query is running. But it's
> possible you miss one or two and may be labor-intensive.
>
> Yong Huang
>
> > I have a query which joins a partition table with some dimensions,
> > when I look the execution plan I can see pstart and pstop shows KEY
> > KEY so pruning is occuring.
> >
> > Is there a way to find out exactly what partitions are accessed?
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>
> ===============================================================================
> Please access the attached hyperlink for an important electronic
> communications disclaimer:
> http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
>
> ===============================================================================
>
>

--
http://www.freelists.org/webpage/oracle-l