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…
Puneet Sachar said
Wonderfull, till now I’m unaware of the facts!!!
U r the champ buddy!!!
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 !!!!
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.
Navdeep Saini said
Thanks. Thats helpful.
Free Traffic,adsense said
Free Traffic,adsense…
[...]FND_STATS vs DBMS_STATS « Practical Apps DBA[...]…
site worth said
site worth…
[...]FND_STATS vs DBMS_STATS « Practical Apps DBA[...]…
Online Internet Marketer looking for Students! Give Russell Brunson 30 days to help you achieve success. said
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!
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.