Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Slow USER_SEGMENTS query

RE: Slow USER_SEGMENTS query

From: Khemmanivanh, Somckit <somckit.khemmanivanh_at_weyerhaeuser.com>
Date: Wed, 13 Dec 2006 10:34:40 -0800
Message-ID: <65C0D8935651CB4D96E97CEFAC5A12B9038D1ABF@wafedixm10.corp.weyer.pri>

 

Thanks, yes I did mention I tried gathering stats in my original message.  

I executed this:  

exec dbms_stats.gather_dictionary_stats('SYS')  

Then re-ran the script..it ran with the same run time....  

Should I have gather stats in a different manner?

Thanks!  


From: Kerber, Andrew [mailto:Andrew.Kerber_at_umb.com] Sent: Wednesday, December 13, 2006 9:52 AM To: Khemmanivanh, Somckit; tomday2_at_gmail.com; oracle-l Subject: 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_at_umb.com  

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

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Khemmanivanh,
Somckit
Sent: Wednesday, December 13, 2006 11:49 AM To: tomday2_at_gmail.com; 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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] 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.

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Dec 13 2006 - 12:34:40 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US