OraFAQ Forum: Performance Tuning » Gather Schema stats

Home » RDBMS Server » Performance Tuning » Gather Schema stats (9.2.0.6.0 IBM AIX)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Gather Schema stats [message #341264]

Mon, 18 August 2008 01:40

pokhraj_das
Messages: 64
Registered: February 2008

Member

Hi,

I have 30 schemas at my test DB. The "last_analyzed" from DBA_INDEXES or DBA_TABLES date I found is 13-dec-2006.

When I am executing dbms_stats.gather_schema_stats('SCHEMA_NAME') the "last_analyzed" column for DBA_INDEXES or DBA_TABLES is not updating the current date, Though the package is running successfully.

My question is

1. Why The last_analyzed column is not updating the current date though I am gathering the schema stats?

Pokhraj

[Updated on: Mon, 18 August 2008 01:41]

Report message to a moderator

Re: Gather Schema stats [message #341267 is a reply to message #341264]

Mon, 18 August 2008 01:56

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

Is it which schema you gather statistics?

Report message to a moderator

Re: Gather Schema stats [message #341273 is a reply to message #341264]

Mon, 18 August 2008 02:12

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Copy and paste what you did, don't just describe it, it is obvious you missed something but you can't describe it as you missed it so we can't say what you missed because you didn't describe it.

Regards
Michel

Report message to a moderator

Re: Gather Schema stats [message #341279 is a reply to message #341264]

Mon, 18 August 2008 03:04

pokhraj_das
Messages: 64
Registered: February 2008

Member

Hi,

The below are the command I fired from sql prompt:=

execute dbms_stats.gather_schema_stats(ownname=>'SCHEMA_NAME',options=>'GATHER AUTO',cascade=>TRUE);

regards-
Pokhraj

Report message to a moderator

Re: Gather Schema stats [message #341280 is a reply to message #341264]

Mon, 18 August 2008 03:07

pokhraj_das
Messages: 64
Registered: February 2008

Member

Hi Mohammad Taj,

This is applicable for all the schemas. Some rows are updating the "CURRENT DATE" and some rows are at the "OLD DATE"

Pokhraj

Report message to a moderator

Re: Gather Schema stats [message #341285 is a reply to message #341280]

Mon, 18 August 2008 03:48

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

try without GATHER AUTO.
GATHER AUTO requires table monitoring to be ON and Oracle will decide which tables to be updated.

http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_stats2.htm#1003995

http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_stats2.htm#1012974

Also, this particular version is prone to certain CBO bugs which I do not remember on top of my head.
Also try (if you can afford) gather_table_stats instead of gather_schema_stats.

Edit:
Added links to docs.
Mahesh Rajendran

[Updated on: Mon, 18 August 2008 04:06]

Report message to a moderator

Re: Gather Schema stats [message #341308 is a reply to message #341264]

Mon, 18 August 2008 05:25

pokhraj_das
Messages: 64
Registered: February 2008

Member

hi,

The prob solved.
Its working fine. thanx for your valuable suggestion.

Pokhraj

Report message to a moderator

Previous Topic:	pstart and pstop
Next Topic:	Filter in Oracle

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jun 30 16:21:54 CDT 2024