?
Solved

How can I gather schema stats for schema A from schema B?

Posted on 2005-03-11
7
Medium Priority
?
2,639 Views
Last Modified: 2012-05-05

I need to gather schema stats about once a week for various schemas, but mostly as needed.  I want to schedule a job to call one procedure to this.

1.
Is there a figure/status that I can look for to determine if a schema need to be analyzed?

2.
I want user A to be able to analyze the schema of user b, c, d, etc.  This way don't have to create a procedure for each schema.  Any idea?

Thanks,
Troy
0
Comment
Question by:tdsimpso
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 25

Expert Comment

by:jrb1
ID: 13522626
the easist way to analyze as a schema is:

SQL> exec dbms_utility.analyze_schema('<Schema Name>', 'COMPUTE', NULL, NULL, NULL)
0
 
LVL 25

Assisted Solution

by:jrb1
jrb1 earned 900 total points
ID: 13522666
For 9i and higher, this is recommended:

begin
  dbms_stats.gather_schema_stats (
    ownname              => '<schmea>',
    estimate_percent   => dbms_stats.auto_sample_size,
    method_opt           => 'for all columns size auto',
    degree                  => 7
  )
end;
/

Schedule this to run daily during regular working hours.  For details about this recommendation:

http://www.dba-oracle.com/art_orafaq_cbo_stats.htm
0
 

Author Comment

by:tdsimpso
ID: 13522757

Can user A run the dbms_stats.gather_schema_stats() on user B's Schema?

Thanks,
Troy
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 11

Assisted Solution

by:sujit_kumar
sujit_kumar earned 200 total points
ID: 13523119
Yes he can in the same database(not possible through data links), But it needs to have DBA previledge. Like SYSTEM user can gather statistics for SCOTT schema.
0
 
LVL 48

Accepted Solution

by:
schwertner earned 900 total points
ID: 13523454
To compute statistics (for indexes also) and to schedule a job to run
the procedure once weekly use:

CREATE OR REPLACE PROCEDURE compute_statistics IS
BEGIN
   dbms_stats.gather_database_stats(cascade=>true);
END compute_statistics;


set serveroutput on
set linesize 10000
variable x number;
begin
   DBMS_OUTPUT.enable(100000);  
   dbms_job.submit(:x,'compute_statistics;',trunc(sysdate),'trunc(sysdate+7)');
   commit;
   dbms_output.put_line(TO_char(:x));
end;

The ussual error is to analyze the tbles, but not the indexes (cascade=>true) !
 
This procedure should be run only from SYS account.

Statistics is DBA duty and do not mix these activities.
0
 

Author Comment

by:tdsimpso
ID: 13546165
Is there a way to determine if dbms_stats.gather_database_stats needs to be run?

For example, I have a process that collects and refreshed data in one schema.  After the refresh process, I gather stats on the schema.  But other schemas don't change much at all, so it would be a waste of resources to actually gather stats on those schemas.  

Can I test a value to determine if stats need to be gathered or not?

Thanks,
Troy
0
 
LVL 48

Expert Comment

by:schwertner
ID: 13552898
No, there is no way to do this.
After long period of time, after bulk iserts, updates, imports, creating new tables, indexes, columns - the DBA need to run the statistics.
We run it once a week, at midnight.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question