Home » RDBMS Server » Performance Tuning » Gather Schema stats (9.2.0.6.0 IBM AIX)
Gather Schema stats [message #341264] Mon, 18 August 2008 01:40 Go to next message
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 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Is it which schema you gather statistics?
Re: Gather Schema stats [message #341273 is a reply to message #341264] Mon, 18 August 2008 02:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
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
Re: Gather Schema stats [message #341279 is a reply to message #341264] Mon, 18 August 2008 03:04 Go to previous messageGo to next message
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
Re: Gather Schema stats [message #341280 is a reply to message #341264] Mon, 18 August 2008 03:07 Go to previous messageGo to next message
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

Re: Gather Schema stats [message #341285 is a reply to message #341280] Mon, 18 August 2008 03:48 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
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 Go to previous message
pokhraj_das
Messages: 64
Registered: February 2008
Member

hi,

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

Pokhraj
Previous Topic: pstart and pstop
Next Topic: Filter in Oracle
Goto Forum:
  


Current Time: Sat Dec 10 14:54:30 CST 2016

Total time taken to generate the page: 0.09172 seconds