Sounds like a fragmentation problem.
This will help you identify the segments with a large number of
fragments. They are good candidates for reorganization.
HTH,
Beth
select a.tablespace_name as tablespace,
cast(a.segment_name as char(30)) as segment,
a.partition_name as
Babu,
We had a similiar problem, and setting optimizer_mode
= choose in our session solved it. Something to do
with optimizer and DD access. Give that a try. I had
the same problem with DBA_INDEXES and that fixed it.
hth,
Jack
--- Janardhana Babu Donga [EMAIL PROTECTED] wrote:
Dear List,
Babu,
Do you have any locally managed tablespaces? I'm wondering if this could be
caused by scanning for extents within LMTs.
Regards,
Mike Hately
-Original Message-
Sent: 09 May 2002 18:58
To: Multiple recipients of list ORACLE-L
Dear List,
When I query dba_extents , Iam getting
Babu - Wow, never had that one. Of course, I don't use that table much. How
many rows are in this table? Mine has 12,937 rows, and is kinda slow to
respond compared to the other system tables. Do you have a test database to
compare with? Is it possible that your system tablespace has become badly
|
|| |
|+---
|
||
| To: [EMAIL PROTECTED] |
| cc: (bcc: Rachel Carmichael) |
| Subject: Re: DBA_EXTENTS problem |
|
Babu,
We had a similiar problem
The Optimizer_mode is already set to CHOOSE. Any other ideas?
Thanks,
--Babu
-Original Message-
Sent: Thursday, May 09, 2002 11:18 AM
To: Multiple recipients of list ORACLE-L
Babu,
We had a similiar problem, and setting optimizer_mode
= choose in our session solved it. Something to do
There are no LMTs in the database.
Pl. let me know if you have any other ideas.
Thanks,
-- Babu
-Original Message-
Sent: Thursday, May 09, 2002 11:29 AM
To: Multiple recipients of list ORACLE-L
Babu,
Do you have any locally managed tablespaces? I'm wondering if this could be
caused by
This is definitely not a fragmentation problem. I have just created a new
table
create table x1 (col1 number) tablespace data_ts; and run:
select * from dba_extents where segment_name = 'X1'; It is still sitting
there. Hope to get response after 30 minutes.
Any other ideas??
Thanks,
-- Babu
Babu,
What does this query return?
select count(*) from dba_extents;
Jared
Janardhana Babu Donga [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
05/09/2002 10:58 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Jared,
It is taking 30 to 40 minutes to respond. I just issued again and is sitting
there. Iam sure it would respond after 30 minutes as happenned many times.
I would E-Mail after getting the response.
Thanks,
-- Janardhana Babu
-Original Message-
Sent: Thursday, May 09, 2002 11:54 AM
Dennis,
It is happenning for any table in the database. I have just created a new
table:
create table x1 (col1 number) tablespace data_ts, inserted one row and run:
select * from dba_extents where segment_name = 'X1'; It is still sitting
there. The response to any other dictionary view is
) |
| Subject: Re: DBA_EXTENTS problem |
|
Babu,
We had a similiar problem, and setting optimizer_mode
= choose in our session solved it. Something to do
with optimizer and DD access. Give that a try. I had
the same problem
Jared,
It is taking 30 to 40 minutes to respond. I just issued again and is sitting
there. Iam sure it would respond after 30 minutes as happenned many times.
Thanks,
-- Janardhana Babu
-Original Message-
Sent: Thursday, May 09, 2002 10:45 AM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
|
|| |
|+---
|
||
| To: [EMAIL PROTECTED] |
| cc: (bcc: Rachel Carmichael) |
| Subject: Re: DBA_EXTENTS problem
what does
select count(*) from uet$
return?
-Original Message-
Sent: Thursday, May 09, 2002 2:54 PM
To: Multiple recipients of list ORACLE-L
Dennis,
It is happenning for any table in the database. I have just created a
new
table:
create table x1 (col1 number) tablespace
or more accurately -
select count(*) from sys.uet$;
-Original Message-
Sent: Thursday, May 09, 2002 2:03 PM
To: '[EMAIL PROTECTED]'
what does
select count(*) from uet$
return?
-Original Message-
Sent: Thursday, May 09, 2002 2:54 PM
To: Multiple
I got the response from the count(*) query. It has returned 4855 and took
nearly 30 minutes to respond.
Thanks,
-- Babu
-Original Message-
Sent: Thursday, May 09, 2002 11:54 AM
To: Multiple recipients of list ORACLE-L
Babu,
What does this query return?
select count(*) from
Is it possible that some of the system owned tables were accidently
analyzed? You might try running:
select table_name
from dba_tables
where owner='SYS'
and last_analyzed is not null;
-Original Message-
Sent: Thursday, May 09, 2002 1:59 PM
To: Multiple recipients of list ORACLE-L
I used to run the following analyze every week:
dbms_utility.analyze_schema(...) statement which included SYSTEM schema,
Two weeks back it was changed to
dbms_stats.gather_database_stats();
Is there anyway to de-analyze SYSTEM schema?
Thanks,
-- Babu
-Original Message-
Sent:
It returned 4855. The response is immediate.
Count(*) from dba_extents also returned 4855 but took nearly 30 minutes.
Thanks,
-- Babu
-Original Message-
Sent: Thursday, May 09, 2002 12:14 PM
To: Multiple recipients of list ORACLE-L
what does
select count(*) from uet$
|
|| |
|+---
|
||
| To: [EMAIL PROTECTED] |
| cc: Rachel Carmichael@Sony_Music |
| Subject: RE: DBA_EXTENTS problem
It responded with 197 tables.
Is it not the correct way to analyze?
dbms_stats.gather_database_stats();
I have recently been using the above statement to analyze the database.
Thanks,
--Babu
-Original Message-
Sent: Thursday, May 09, 2002 12:24 PM
To: Multiple recipients of list
|
|| |
|+---
|
|
|
| To: [EMAIL PROTECTED]
|
| cc: (bcc: Rachel Carmichael)
|
| Subject: Re: DBA_EXTENTS problem
|
|
Babu,
We had
|
|| |
|+---
|
||
| To: [EMAIL PROTECTED] |
| cc: (bcc: Rachel Carmichael) |
| Subject: Re: DBA_EXTENTS problem |
|
Babu,
We had a similiar problem
|
|| |
|+---
|
|
|
| To: [EMAIL PROTECTED]
|
| cc: (bcc: Rachel Carmichael)
|
| Subject: Re: DBA_EXTENTS problem
|
|
Babu,
We
PROTECTED]
cc: '[EMAIL PROTECTED]' [EMAIL PROTECTED]
Subject:RE: DBA_EXTENTS problem
Jared,
It is taking 30 to 40 minutes to respond. I just issued again and is
sitting
there. Iam sure it would respond after 30 minutes as happenned many times.
I would E-Mail after
Yes, I ran the query from the SYS schema and it returned the same
number:4855, the same as count(*) from dba_extents.
-- Babu
-Original Message-
Sent: Thursday, May 09, 2002 12:14 PM
To: Multiple recipients of list ORACLE-L
or more accurately -
select count(*) from sys.uet$;
and e.event not like '%message%client'
order by s.username, upper(e.event);
Janardhana Babu Donga [EMAIL PROTECTED]
05/09/2002 11:03 AM
To: '[EMAIL PROTECTED]' [EMAIL PROTECTED]
cc: '[EMAIL PROTECTED]' [EMAIL PROTECTED]
Subject:RE: DBA_EXTENTS problem
Jared
|
|| |
|+---
|
||
| To: [EMAIL PROTECTED] |
| cc: (bcc: Rachel Carmichael) |
| Subject: Re: DBA_EXTENTS problem |
|
Babu,
We had a similiar problem, and setting
not like '%message%client'
order by s.username, upper(e.event);
Janardhana Babu Donga [EMAIL PROTECTED]
05/09/2002 11:03 AM
To: '[EMAIL PROTECTED]' [EMAIL PROTECTED]
cc: '[EMAIL PROTECTED]' [EMAIL PROTECTED]
Subject:RE: DBA_EXTENTS problem
Jared
] |
| cc: (bcc: Rachel Carmichael) |
| Subject: RE: DBA_EXTENTS problem |
|
I used to run the following analyze every week:
dbms_utility.analyze_schema(...) statement which included SYSTEM schema
) |
| Subject: Re: DBA_EXTENTS problem |
|
dbms_stats.gather_database_stats has a bug, it analyzes SYS objects.
From Metalink:
Bug:1422285 is a severity 3 bug that was logged for the
dbms_stats.gather_database_stats
|
|| |
|+---
|
||
| To: [EMAIL PROTECTED] |
| cc: (bcc: Rachel Carmichael) |
| Subject: Re: DBA_EXTENTS problem |
|
dbms_stats.gather_database_stats has a bug, it analyzes
|
|| |
|+---
|
|
|
| To: [EMAIL PROTECTED]
|
| cc: (bcc: Rachel Carmichael)
|
| Subject: Re: DBA_EXTENTS problem
:
vis.edu Subject: RE: DBA_EXTENTS problem
Sent by:
root@fatcity
|
|| |
|+---
|
||
| To: [EMAIL PROTECTED] |
| cc: (bcc: Rachel Carmichael) |
| Subject: Re: DBA_EXTENTS problem
Babu,
execute dbms_utility.analyze_schema('system','delete') this would do.
-Shaibal
From: Janardhana Babu Donga [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: RE: DBA_EXTENTS problem
Date: Thu, 09 May 2002 11:41:16 -0800
I
37 matches
Mail list logo