Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2005-03-11
7
Medium Priority
?
2,641 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
  • 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
Independent Software Vendors: 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Suggested Courses
Course of the Month10 days, 12 hours left to enroll

571 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