Analyze Oracle Database schema

Why is it important to analyze , and what is analyze anyway ?
well analyze is a method to gather statistics on table objects in order for the optimizer
to choose the best way for executing queries .
for example the optimizer may choose to use full table scan or to use table index ,
it does so by looking at the table statistics .
Oracle doesn’t gather statistics on schema’s all by it self and the DBA must do it
as part of database maintenance . its is wise to analyze your schema on regular basis’s ,
depend on the data changes . i will show you a small script that can help you analyze your schema .

 

#!/bin/sh
#
# This script will call dbms_stats to Analyze SCHEMA_OWNER schema

ORACLE_SID=<sid name>
ORACLE_BASE=<path to oracle base>
ORACLE_HOME=<path to oracle home>
export ORACLE_SID ORACLE_BASE ORACLE_HOME

$ORACLE_HOME/bin/sqlplus -s ” / as sysdba” <<eof1
spool /tmp/Analyzing.txt
exec dbms_stats.gather_schema_stats(ownname=>’SCHEMA_OWNER’,estimate_percent=>5,cascade=>true);
exit;
eof1

Leave a comment ?

11 Comments.

  1. woot, thankyou! I finally came to a site where the webmaster knows what they’re talking about. Do you know how many results are in Google when I search.. too many! It’s so annoying having to go from page after page after page, wasting my day away with thousands of people just copying eachother’s articles… bah. Anyway, thankyou very much for the info anyway, much appreciated.

  2. Great article. Thank you to tell us more useful information. I am looking forward to reading more of your articles in the future.

  3. Jenifer - trackback on April 12, 2012 at 10:55 pm
    • construct, but SQL*Plus as an option you had to pay for? That was news to me and I’ve been using Oracle since the days when you eernted your SQL commands using the “User Friendly Interface (UFI)”. I also remember talk back then of Oracle developing a procedural interface (the widely anticipated “Super-UFI”) which eventually arrived as PL/SQL. I always thought that SQL*Plus was just a renaming of UFI, just as the “Interactive Application Facility (IAF)” became SQL*Forms. We really had to pay extra for SQL*Plus?

  4. Merely wanna input on couple of common points, The site style is perfect, the topic material is rattling superb : D. 445065

  5. Oakley Dispatch - trackback on June 29, 2012 at 1:32 am
  6. wonderful points altogether, you just received a new reader.
    What might you recommend about your put up that you made a few days
    ago? Any certain?
    my web-site; reverse mortgage cons

  7. Hi! I know this is sort of off-topic but I needed to ask.

    Does running a well-established blog such as yours require a massive amount work?
    I’m brand new to blogging however I do write in my diary every day. I’d like to start a blog so I can share my own experience and thoughts online. Please let me know if you have any kind of ideas or tips for brand new aspiring blog owners. Appreciate it!
    My web-site :: “http www INFORMATIONABOUTREVERSEMORTGAGES US” information about reverse mortgages

  8. Custom USB Drive - trackback on September 18, 2012 at 4:36 am
  9. Many thanks for the example, it is straight forward

  10. And I was just wnodreing about that too!

Leave a Comment


NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

 

Trackbacks and Pingbacks:

  • Jenifer - Trackback on 2012/04/12/ 22:55
  • Oakley Dispatch - Trackback on 2012/06/29/ 01:32
  • Custom USB Drive - Trackback on 2012/09/18/ 04:36