Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: INDEX STATS??

RE: INDEX STATS??

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Tue, 10 Aug 2004 06:38:34 -0600
Message-Id: <6.1.0.6.2.20040810063119.02d128e8@pop.centrexcc.com>


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



Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--
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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US