Practical Apps DBA

Oracle Apps DBA’s Blog

FND_STATS vs DBMS_STATS

Posted by Navdeep Saini on April 4, 2008

I have been asked this questions many times. Shoud we use fnd_stats or should we use dbms_stats in 11i (or release 12). Most of the Apps DBAs you will face will say use fnd_stats. In fact Oracle also recommends to use fnd_stats in 11i E-Business Environments. But do you know what are the basic difference or you can say advantages or using FND_STATS over DBMS_STATS.
Here I have tried to compare the two…

About these ads

12 Responses to “FND_STATS vs DBMS_STATS”

  1. Puneet Sachar said

    Wonderfull, till now I’m unaware of the facts!!!

    U r the champ buddy!!!

  2. Mohsin said

    Really a very useful info…
    I have been using fnd_stats but the idea behind never clicked .
    now i will ask this while interviewing….

    Great !!!!

  3. stephen said

    What are the input parameters for calling the stats from a stored procedure, I am able to run successfully from SQL prompt whereas when I call this routine from store parameters I am getting the error ‘incorrect parameter’.

    Thanks,
    Stephen

    • Rajanish Joshi said

      If you desc fnd_stats it will give you parameters it takes ,it has many packages and functions inside it.
      One of which I use to verify the table statistics is as follows
      set serveroutput on
      exec apps.fnd_stats.verify_stats(upper( ‘&tableowner’),upper(‘&tablename’));
      for e.g.
      SQL> @verifystats.sql
      Enter value for tableowner: APPLSYS
      Enter value for tablename: FND_CONCURRENT_REQUESTS
      ===================================================================================================
      Table FND_CONCURRENT_REQUESTS
      ===================================================================================================
      last analyzed sample_size num_rows blocks
      11-22-2010 21:47 11546 115460 55591

      Index name last analyzed num_rows LB DK LB/key DB/key CF
      —————————————————————————————————-
      FND_CONCURRENT_REQUESTS_F1 11-22-2010 21:47 116161 1011 137 7 96 13278
      FND_CONCURRENT_REQUESTS_N1 11-22-2010 21:47 116164 1182 96807 1 1 78596
      FND_CONCURRENT_REQUESTS_N10 11-22-2010 21:47 116161 1021 2914 1 7 23240
      FND_CONCURRENT_REQUESTS_N11 11-22-2010 21:47 116161 1038 72438 1 1 80474
      FND_CONCURRENT_REQUESTS_N2 11-22-2010 21:47 116164 1143 5 228 2755 13778
      FND_CONCURRENT_REQUESTS_N3 11-22-2010 21:47 116161 1405 65909 1 1 69834
      FND_CONCURRENT_REQUESTS_N4 11-22-2010 21:47 116163 1337 100443 1 1 80911
      FND_CONCURRENT_REQUESTS_N5 11-22-2010 21:47 38606 1297 179 7 93 16734
      FND_CONCURRENT_REQUESTS_N6 11-22-2010 21:47 116164 2866 171 16 313 53525
      FND_CONCURRENT_REQUESTS_N7 11-22-2010 21:47 116164 1248 5 249 2759 13797
      FND_CONCURRENT_REQUESTS_N8 11-22-2010 21:47 116164 2903 187 15 312 58421
      FND_CONCURRENT_REQUESTS_N9 11-22-2010 21:47 116164 1370 6 228 2300 13803
      FND_CONCURRENT_REQUESTS_U1 11-22-2010 21:47 116164 866 116164 1 1 81265
      —————————————————————————————————-
      Histogram Stats
      Schema Table Name Status last analyzed Column Name
      —————————————————————————————————-
      APPLSYS FND_CONCURRENT_REQUESTS present 22-11-2010 21:47 PHASE_CODE
      APPLSYS FND_CONCURRENT_REQUESTS present 22-11-2010 21:47 STATUS_CODE

      Legend:
      LB : Leaf Blocks
      DK : Distinct Keys
      DB : Data Blocks
      CF : Clustering Factor

      PL/SQL procedure successfully completed.

  4. Free Traffic,adsense…

    […]FND_STATS vs DBMS_STATS « Practical Apps DBA[…]…

  5. site worth said

    site worth…

    […]FND_STATS vs DBMS_STATS « Practical Apps DBA[…]…

  6. Spot on with this write-up, I really think this site needs far more attention. I’ll probably be back again to read through more, thanks for the info!

  7. Jonathon said

    Hi i am kavin, its my first time to commenting anywhere, when i read this post i thought
    i could also create comment due to this brilliant piece of writing.

  8. Praveen said

    Best Practices for Gathering Statistics with Oracle E-Business Suite (Doc ID 1586374.1)

    Good one on the same topic…..

  9. More Info said

    I most certainly will straight away get a person’s rss while i cannot to find the email membership url or perhaps e-newsletter program. Do you could have virtually any? Nicely ok, i’ll understand to ensure I might simply sign up to. Thank you.

  10. Yesterday, while I was at work, my cousin stole my iphone and tested to see if it can survive a twenty five foot drop, just so she can be a youtube sensation. My iPad is
    now broken and she has 83 views. I know this is entirely off topic but
    I had to share it with someone!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 60 other followers

%d bloggers like this: