| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: INDEX STATS??
At 12:43 AM 8/10/2004, you wrote:
>ACXSTATS is just the name of the stats table -
>exec dbms_stats.create_stat_table('&&SCHEMA','ACXSTATS');
>
>exec
>dbms_stats.export_table_stats('&USER','&TABLE',NULL,'ACXSTATS','&STID',&CASC
>ADE);
>
>The STATSID is month,day, day-of-week, timestamp (2000 = 8PM)
>
> I was really wondering:
>1) Why am I losing stats? I thought rebuilds would retain them in 9.
An "alter index ... rebuild" does not loose the statistics:
table index column
NDV DENS #LB lvl #LB/K #DB/K CLUF
--------------------- ------------------------------ ------------------
----------- ------------ ---------- --- ------- ------- -----------
PS_RETROPAY_EARNS PS2RETROPAY_EARNS EMPL_RCD#
1 1.0000E+00
PSARETROPAY_EARNS
0 1.0516E+02
AMOUNT_DIFF
99,860 1.0014E-05
COMPANY
5 2.0000E-01
PAYGROUP
7 1.4286E-01
PAY_END_DT
56 1.7857E-02
OFF_CYCLE
2 5.0000E-01
PAGE#
1 1.0000E+00
LINE#
1 1.0000E+00
ADDL#
1 1.0000E+00
EARNS_TYPE
7 1.4286E-01
ERNCD
1 1.0000E+00
SEQ_NO
1 1.0000E+00
RETROPAY_PRCS_FLAG
3 3.3333E-01
RETROPAY_LOAD_SW
2 5.0000E-01
RETROPAY_SEQ_NO
14,532 6.8814E-05
RETRO_ADDL_NO
7 1.4286E-01
RETROPAY_ERNCD
1 1.0000E+00
PSBRETROPAY_EARNS
658,932 1.3489E+01 13,952 3 1 1 574,411
COMPANY
5 2.0000E-01
PAYGROUP
7 1.4286E-01
PAY_END_DT
56 1.7857E-02
OFF_CYCLE
2 5.0000E-01
PAGE#
1 1.0000E+00
LINE#
1 1.0000E+00
ADDL#
1 1.0000E+00
EARNS_TYPE
7 1.4286E-01
ERNCD
1 1.0000E+00
SEQ_NO
1 1.0000E+00
RETROPAY_PRCS_FLAG
3 3.3333E-01
RETROPAY_LOAD_SW
2 5.0000E-01
RETROPAY_SEQ_NO
14,532 6.8814E-05
PS_RETROPAY_EARNS U
658,932 6.3836E-04 25,526 2 1 1 658,928
RETROPAY_SEQ_NO
14,532 6.8814E-05
COMPANY
5 2.0000E-01
PAYGROUP
7 1.4286E-01
PAY_END_DT
56 1.7857E-02
OFF_CYCLE
2 5.0000E-01
PAGE#
1 1.0000E+00
LINE#
1 1.0000E+00
ADDL#
1 1.0000E+00
EARNS_TYPE
7 1.4286E-01
ERNCD
1 1.0000E+00
SEQ_NO
1 1.0000E+00
PS_RETROPAY_EARNS_IDX1
131,514 1.7555E-02 4,453 2 1 5 658,822
EMPLID
41,481 2.4107E-05
EMPL_RCD#
1 1.0000E+00
106 rows selected.
06:29:06 ora92.scott> alter index PS_RETROPAY_EARNS rebuild 06:29:31 2 /
Index altered.
06:29:34 ora92.scott> @idxstats PS_RETROPAY_EARNS
table index column
NDV DENS #LB lvl #LB/K #DB/K CLUF
--------------------- ------------------------------ ------------------
----------- ------------ ---------- --- ------- ------- -----------
PS_RETROPAY_EARNS PS#RETROPAY_EARNS
0 1.0516E+02
RETROPAY_SEQ_NO
14,532 6.8814E-05
COMPANY
5 2.0000E-01
PAYGROUP
7 1.4286E-01
PAY_END_DT
56 1.7857E-02
OFF_CYCLE
2 5.0000E-01
PAGE#
1 1.0000E+00
LINE#
1 1.0000E+00
ADDL#
1 1.0000E+00
EARNS_TYPE
7 1.4286E-01
ERNCD
1 1.0000E+00
SEQ_NO
1 1.0000E+00
EMPLID
41,481 2.4107E-05
EMPL_RCD#
1 1.0000E+00
RETROPAY_EFFDT
56 1.7857E-02
PS0RETROPAY_EARNS
658,932 3.1918E-02 12,921 2 1 1 658,732
EMPLID
41,481 2.4107E-05
RETROPAY_SEQ_NO
14,532 6.8814E-05
COMPANY
5 2.0000E-01
PAYGROUP
7 1.4286E-01
PAY_END_DT
56 1.7857E-02
OFF_CYCLE
2 5.0000E-01
PAGE#
1 1.0000E+00
LINE#
1 1.0000E+00
ADDL#
1 1.0000E+00
EARNS_TYPE
7 1.4286E-01
ERNCD
1 1.0000E+00
SEQ_NO
1 1.0000E+00
EMPL_RCD#
1 1.0000E+00
RETROPAY_EFFDT
56 1.7857E-02
PS1RETROPAY_EARNS
0 1.0516E+02
EMPL_RCD#
1 1.0000E+00
RETROPAY_SEQ_NO
14,532 6.8814E-05
COMPANY
5 2.0000E-01
PAYGROUP
7 1.4286E-01
PAY_END_DT
56 1.7857E-02
OFF_CYCLE
2 5.0000E-01
PAGE#
1 1.0000E+00
LINE#
1 1.0000E+00
ADDL#
1 1.0000E+00
EARNS_TYPE
7 1.4286E-01
ERNCD
1 1.0000E+00
SEQ_NO
1 1.0000E+00
EMPLID
41,481 2.4107E-05
RETROPAY_EFFDT
56 1.7857E-02
PS2RETROPAY_EARNS
0 1.0516E+02
RETROPAY_EFFDT
56 1.7857E-02
RETROPAY_SEQ_NO
14,532 6.8814E-05
COMPANY
5 2.0000E-01
PAYGROUP
7 1.4286E-01
PAY_END_DT
56 1.7857E-02
OFF_CYCLE
2 5.0000E-01
PAGE#
1 1.0000E+00
LINE#
1 1.0000E+00
ADDL#
1 1.0000E+00
EARNS_TYPE
7 1.4286E-01
ERNCD
1 1.0000E+00
SEQ_NO
1 1.0000E+00
EMPLID
41,481 2.4107E-05
table index column
NDV DENS #LB lvl #LB/K #DB/K CLUF
--------------------- ------------------------------ ------------------
----------- ------------ ---------- --- ------- ------- -----------
PS_RETROPAY_EARNS PS2RETROPAY_EARNS EMPL_RCD#
1 1.0000E+00
PSARETROPAY_EARNS
0 1.0516E+02
AMOUNT_DIFF
99,860 1.0014E-05
COMPANY
5 2.0000E-01
PAYGROUP
7 1.4286E-01
PAY_END_DT
56 1.7857E-02
OFF_CYCLE
2 5.0000E-01
PAGE#
1 1.0000E+00
LINE#
1 1.0000E+00
ADDL#
1 1.0000E+00
EARNS_TYPE
7 1.4286E-01
ERNCD
1 1.0000E+00
SEQ_NO
1 1.0000E+00
RETROPAY_PRCS_FLAG
3 3.3333E-01
RETROPAY_LOAD_SW
2 5.0000E-01
RETROPAY_SEQ_NO
14,532 6.8814E-05
RETRO_ADDL_NO
7 1.4286E-01
RETROPAY_ERNCD
1 1.0000E+00
PSBRETROPAY_EARNS
658,932 1.3489E+01 13,952 3 1 1 574,411
COMPANY
5 2.0000E-01
PAYGROUP
7 1.4286E-01
PAY_END_DT
56 1.7857E-02
OFF_CYCLE
2 5.0000E-01
PAGE#
1 1.0000E+00
LINE#
1 1.0000E+00
ADDL#
1 1.0000E+00
EARNS_TYPE
7 1.4286E-01
ERNCD
1 1.0000E+00
SEQ_NO
1 1.0000E+00
RETROPAY_PRCS_FLAG
3 3.3333E-01
RETROPAY_LOAD_SW
2 5.0000E-01
RETROPAY_SEQ_NO
14,532 6.8814E-05
PS_RETROPAY_EARNS U
658,932 6.3836E-04 25,526 2 1 1 658,928
RETROPAY_SEQ_NO
14,532 6.8814E-05
COMPANY
5 2.0000E-01
PAYGROUP
7 1.4286E-01
PAY_END_DT
56 1.7857E-02
OFF_CYCLE
2 5.0000E-01
PAGE#
1 1.0000E+00
LINE#
1 1.0000E+00
ADDL#
1 1.0000E+00
EARNS_TYPE
7 1.4286E-01
ERNCD
1 1.0000E+00
SEQ_NO
1 1.0000E+00
PS_RETROPAY_EARNS_IDX1
131,514 1.7555E-02 4,453 2 1 5 658,822
EMPLID
41,481 2.4107E-05
EMPL_RCD#
1 1.0000E+00
106 rows selected.
06:29:42 ora92.scott>
Soemthing else must be losing the statistics. Maybe some of the indexes are actually dropped and re-created instead of rebuilt. Check object creation dates. Maybe that will give you a clue (and proof).
>2) What's happening to plans? I asked client to send me code and explains
>and they said there were too many. But batch application jobs have been
>running faster every day (almost, they sent me their figures)the last two
>weeks, since upgrade to 9.2.0.4
>
> I tried to generate all the plans in the shared pool and had some
>success, but also got a number of ORA-600s.
I don't understand what the question is. What do you mean by generating the plans in the shared pool. Are you getting all the sql and explaining them do you query v$sql_plan? Also, v$sql and v$sqlarea now contain the cpu and elapsed time of the sql, so you have some measurements there as well.
Regards
Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Tue Aug 10 2004 - 07:35:27 CDT
 |
 |