| Need help in 'Gather Stats' [message #480899] |
Thu, 28 October 2010 01:56  |
jimit_shaili
Messages: 237
Registered: June 2006
Location: India, Ahmedabad
|
Senior Member |
|
|
Dear Friends
I have problem/misunderstanding with gather schema stat utility of oracle. Herewith i'm posting my try and output of it.
My main question is why the column 'LAST_ANALYZED' of dba_tables not updated on gathering fresh schema level statistics.
SQL>select OWNER,TABLE_NAME,NUM_ROWS,AVG_ROW_LEN,BLOCKS,SAMPLE_SIZE,LAST_ANALYZED from dba_tables where owner='STO' and rownum<=10 order by LAST_ANALYZED;
OWNER TABLE_NAME NUM_ROWS AVG_ROW_LEN BLOCKS SAMPLE_SIZE LAST_ANAL
------------------------------ ------------------------------ ---------- ----------- ---------- ----------- ---------
STO BILLSLIPB 7695 26 36 7695 29-MAR-10
STO BILL_CHECKING_SLIP 2634 71 28 2634 29-MAR-10
STO FACTORYBILLA 2 119 1 2 29-MAR-10
STO FACTORYBILLSETTLE 3 66 1 3 29-MAR-10
STO FACTORYBILLB 50 46 1 50 29-MAR-10
STO FACTORYTENDER 172 256 7 172 29-MAR-10
STO JANGADACTION 359 49 3 359 29-MAR-10
STO JANGADINSTRUCTION 359 69 4 359 29-MAR-10
STO JOBWORKISSUEA 8 67 1 8 21-OCT-10
STO BILLSLIPA 2725 51 23 2725 21-OCT-10
10 rows selected.
SQL>execute dbms_stats.gather_schema_stats(OWNNAME => 'STO', OPTIONS => 'GATHER AUTO');
PL/SQL procedure successfully completed.
SQL> select OWNER,TABLE_NAME,NUM_ROWS,AVG_ROW_LEN,BLOCKS,SAMPLE_SIZE,LAST_ANALYZED from dba_tables where owner='STO' and rownum<=10 order by LAST_ANALYZED;
OWNER TABLE_NAME NUM_ROWS AVG_ROW_LEN BLOCKS SAMPLE_SIZE LAST_ANAL
------------------------------ ------------------------------ ---------- ----------- ---------- ----------- ---------
STO BILLSLIPB 7695 26 36 7695 29-MAR-10
STO BILL_CHECKING_SLIP 2634 71 28 2634 29-MAR-10
STO FACTORYBILLA 2 119 1 2 29-MAR-10
STO FACTORYBILLSETTLE 3 66 1 3 29-MAR-10
STO FACTORYBILLB 50 46 1 50 29-MAR-10
STO FACTORYTENDER 172 256 7 172 29-MAR-10
STO JANGADACTION 359 49 3 359 29-MAR-10
STO JANGADINSTRUCTION 359 69 4 359 29-MAR-10
STO JOBWORKISSUEA 8 67 1 8 21-OCT-10
STO BILLSLIPA 2725 51 23 2725 21-OCT-10
10 rows selected.
SQL>
|
|
|
|
| Re: Need help in 'Gather Stats' [message #480904 is a reply to message #480899] |
Thu, 28 October 2010 02:02  |
jimit_shaili
Messages: 237
Registered: June 2006
Location: India, Ahmedabad
|
Senior Member |
|
|
|
Some more information by me on post, As per my understanding by gathering schema level statistics it should gather the stats for all objects which are underlying that schema. In my case few of tables last_analyzed date is updated, while most of them remain unchanged.
|
|
|
|
| Re: Need help in 'Gather Stats' [message #480907 is a reply to message #480904] |
Thu, 28 October 2010 02:06 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
|
Presumably none of those objects qualify for analysis according to the criteria used by GATHER AUTO. The criteria are detailed in the description of DBMA_STATS on the PL/SQL Packages and Types Reference.
|
|
|
|
| Re: Need help in 'Gather Stats' [message #480910 is a reply to message #480907] |
Thu, 28 October 2010 02:11 |
jimit_shaili
Messages: 237
Registered: June 2006
Location: India, Ahmedabad
|
Senior Member |
|
|
Thank John for quick reply,
Should i need to change any of parameters or the way i gather the schema level statistics. I think i should have to continue what i have done earlier. please suggest if i'm wrong. Provide some goods note/links/sites/blogs for reference about gathering stats and impact of it on database in 9i. which will be helpful in understanding better the package.
Regards
Jimit
|
|
|
|
| Re: Need help in 'Gather Stats' [message #480912 is a reply to message #480910] |
Thu, 28 October 2010 02:16 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
|
Your database is not an important one. If it were important, it would not be running on release 9i. So if I were you, I would simply trust Oracle to get it right. The only documents you need right now are the PL/SQL Packages and Types reference and the Performance Tuning Guide.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
