Home » RDBMS Server » Performance Tuning » How to recreate statistics to whole schema?
How to recreate statistics to whole schema? [message #259826] Thu, 16 August 2007 11:15 Go to next message
mson77
Messages: 208
Registered: August 2007
Location: Brazil
Senior Member
Hello ALL,

Is there a way to ask oracle to delete the current statistics information and create new set of statistics information to a particular schema?

Thank you.

mson77

Report message to a moderator

Re: How to recreate statistics to whole schema? [message #259829 is a reply to message #259826] Thu, 16 August 2007 11:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
dbms_stats.delete_schema_stats
dbms_stats.gather_schema_stats

Regards
Michel

Report message to a moderator

Re: How to recreate statistics to whole schema? [message #259830 is a reply to message #259826] Thu, 16 August 2007 11:22 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
SQL> DESC DBMS_STATS
contains the answer

Report message to a moderator

Re: How to recreate statistics to whole schema? [message #259841 is a reply to message #259830] Thu, 16 August 2007 11:49 Go to previous messageGo to next message
mson77
Messages: 208
Registered: August 2007
Location: Brazil
Senior Member
Hello ALL,

Let me ask of your experience/knowledge:


  1. If I delete statistics from some schema (or all schemas) and recreate the corresponding statistics... how about the chances (%) to have overall performance improved?
  2. Is there a possibility to have the performance improved after this action?
  3. In which case it may apply?


Thank you.

mson77

Report message to a moderator

Re: How to recreate statistics to whole schema? [message #259842 is a reply to message #259826] Thu, 16 August 2007 11:55 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It depends.
It depends upon the SQL statement & the underlying data.
If the values of the "old" statistics are close to the values of the "new" statistics, no change in performance will be noticed.
Having current statistics improves the odds of having "optimal" performance, but won't solve every slow running SQL statement.

Report message to a moderator

Re: How to recreate statistics to whole schema? [message #259847 is a reply to message #259842] Thu, 16 August 2007 12:08 Go to previous messageGo to next message
mson77
Messages: 208
Registered: August 2007
Location: Brazil
Senior Member
In your oppinion... who should primarly correct the slowly sql statement?
  1. The application developer; or
  2. The dba.
Regards,

mson77

Report message to a moderator

Re: How to recreate statistics to whole schema? [message #259849 is a reply to message #259826] Thu, 16 August 2007 12:17 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>In your oppinion... who should primarly correct the slowly sql statement?
>The application developer; or
>The dba.

In theory the application deleveloper should only deliver into production tuned SQL.
In reality the DBA typically gets involved after poor performing SQL gets complaints about slow response time.

Either or both of these 2 books are worth their weight in gold.
SQL Tuning by Dan Tow ISBN 0-596-00573-3
Optimizing Oracle Performance by Cary Milsap ISBN 0-596-00527-x

Report message to a moderator

Re: How to recreate statistics to whole schema? [message #259853 is a reply to message #259849] Thu, 16 August 2007 12:41 Go to previous messageGo to next message
mson77
Messages: 208
Registered: August 2007
Location: Brazil
Senior Member
So what I am realizing about tuning issue is:

  1. hardware tuning... not so hard;
  2. database tuning... (sga/pga;extents;tablespace;disk)... not so hard too yet;
  3. application tuning... (sql statement tuning)... this is hard.


So... the tuning task is very close to 'having'/'not having' sql language expertise/experience. Is it correct?

mson77
PS: Yes... I have deleted/gathered stats from schemas and no change on performance... Sad
Have used "estimate_percent=>10, cascade=>true".
Hard to find a solution.

Report message to a moderator

Re: How to recreate statistics to whole schema? [message #259854 is a reply to message #259826] Thu, 16 August 2007 12:52 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>application tuning... (sql statement tuning)... this is hard.
SQL is easy to do poorly.
SQL performance can depend upon distribution of the data & depend upon how it is organized (table layouts, indexes, etc.)
Here is an example of both in an accounting application.
Invariable there will be an INVOICE table & a field denoting whether it is open or closed.
Typically (historically) this table has many, many more closed invoices than open invoices.
Placing an index on this column & using WHERE OPEN='Y' will greatly speed up SELECT looking at/for open invoices.
This index won't help dealing with closed invoices.

Report message to a moderator

Re: How to recreate statistics to whole schema? [message #259864 is a reply to message #259854] Thu, 16 August 2007 13:18 Go to previous messageGo to next message
mson77
Messages: 208
Registered: August 2007
Location: Brazil
Senior Member
SQL Tuning by Dan Tow ISBN 0-596-00573-3
Optimizing Oracle Performance by Cary Millsap ISBN 0-596-00527-x
Both from O'Reilly (R$ 200,00 ~ USD 100.00).
Ordered... will take 6 weeks to get here... Sad (too much time).

Meantime... checkup again: hardware resources okay, memory allocation okay,... traced session/ran tkprof/explain plan/execution plan/... recreate stats info... no more things to do... just rewrite that big sql... is it my unique way?
Regards,

mson77

Report message to a moderator

Re: How to recreate statistics to whole schema? [message #259865 is a reply to message #259853] Thu, 16 August 2007 13:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The correct way to tune is to tune:
1/ application
2/ database
3/ hardware

Regards
Michel

[Updated on: Thu, 16 August 2007 14:01]

Report message to a moderator

Re: How to recreate statistics to whole schema? [message #259872 is a reply to message #259865] Thu, 16 August 2007 13:31 Go to previous message
mson77
Messages: 208
Registered: August 2007
Location: Brazil
Senior Member
Hello Michel,

Thank you!
Now I realize... you are 101% right: application/database/hardware in this priority sequence. If application is not tuned (poor)... there is no database neither hardware that will handle such mess.

Now I am faced with the real and bitter world.
Is there a way to break down a big sql statement into many other smaller... to ease reach some good result?

I have a poor sql statement that generates many full table scan... and I have no idea how to deal with it. Also... let's say I have a guess... to correct this sql... I don't know how to apply this guess to the execution plan.
Thank you,

mson77

Report message to a moderator

Previous Topic: DB Monitoring tool
Next Topic: Indexes on LMT
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Nov 04 21:18:04 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.