RE: Slow USER_SEGMENTS query

If you are on 10.1 or above, do this:

Exec dbms_stats.gather_dictionary_stats

 

Then try running the query.

 

Andrew W. Kerber 
Oracle DBA 
UMB 
816-860-3921 
andrew.kerber@xxxxxxx 

 

"If at first you dont succeed, dont take up skydiving" 

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Khemmanivanh,
Somckit
Sent: Wednesday, December 13, 2006 11:49 AM
To: tomday2@xxxxxxxxx; oracle-l
Subject: RE: Slow USER_SEGMENTS query

 

I tried it but it was pretty much the same...I didn't look at the plan
super close...but it either ignored the hint or it made no
difference....

 

Here's the statement:

 

SELECT /*+ FIRST_ROWS */
NVL(BYTES,-1) FROM USER_SEGMENTS
WHERE SEGMENT_NAME = 'mytab'
OR SEGMENT_NAME IN
(SELECT SEGMENT_NAME FROM USER_LOBS WHERE TABLE_NAME = 'mytab');

Thanks! 

 

 

________________________________

From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Thomas Day
Sent: Wednesday, December 13, 2006 9:12 AM
To: oracle-l
Subject: Re: Slow USER_SEGMENTS query

OK.  Here's a hint

 

/* FIRST ROW */

 

If it works, great.  If not, you're no worse off.


------------------------------------------------------------------------------
NOTICE:  This electronic mail message and any attached files are confidential.  
The information is exclusively for the use of the individual or entity intended 
as the recipient.  If you are not the intended recipient, any use, copying, 
printing, reviewing, retention, disclosure, distribution or forwarding of the 
message or any attached file is not authorized and is strictly prohibited.  If 
you have received this electronic mail message in error, please advise the 
sender by reply electronic mail immediately and permanently delete the original 
transmission, any attachments and any copies of this message from your computer 
system. Thank you.

==============================================================================

Other related posts: