Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Tuesday, October 13, 2015

Gather statistics on a specific table without invalidating cursors:

Gather statistics on a specific table without invalidating cursors:

no_invalidae
Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE. Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.


SHAIKDB>select parsing_schema_name,sql_id,invalidations from v$sql where parsing_schema_name='TEST1';

PARSING_SCHEMA_NAME           SQL_ID         INVALIDATIONS
------------------------------ ------------- -------------
TEST1                  g4y6nw3tts7cc        0
TEST1                  cf19zu91tn7mj        0
TEST1                  dyk4dprp70d74        0
TEST1                  gpp46471wp37k        0
TEST1                  5qgz1p0cut7mx        0
TEST1                  g3f3cw3zy5aat        0
TEST1                  fkh6yuk3jpayv        0
TEST1                  c0j6cx9kzjf7g        0
TEST1                  g93x9gquu9t13        0
TEST1                  99qa3zyarxvms        0
TEST1                  g72kdvcacxvtf        0
TEST1                  8wfgaknkskb14        0
TEST1                  d6vwqbw6r2ffk        0
TEST1                  cw6vxf0kbz3v1        0
TEST1                  7hys3h7ysgf9m        0

15 rows selected.

SHAIKDB>select PARSING_SCHEMA_NAME,sql_id,sql_text,invalidations from v$sql where PARSING_SCHEMA_NAME='TEST1' and upper(sql_text) like '%EXT_COLUMNS%';

PARSING_SC SQL_ID     SQL_TEXT                                     INVALIDATIONS
---------- ------------- -------------------------------------------------------------------------------- -------------
TEST1       0kmkdmr78n6rj select PARSING_SCHEMA_NAME,sql_id,sql_text,invalidations from v$sql where PARSIN          0
           G_SCHEMA_NAME='TEST1' and upper(sql_text) like 'EXT_COLUMNS'

TEST1       8bnh5pqv9t49q select PARSING_SCHEMA_NAME,sql_id,sql_text,invalidations from v$sql where PARSIN          0
           G_SCHEMA_NAME='TEST1' and sql_text like 'EXT_COLUMNS'

TEST1       fkh6yuk3jpayv select /*+ no_parallel_index(t, "EXT_COLUMNS_IDX") dbms_stats cursor_sharing_exa          0
           ct use_weak_name_resl dynamic_sampling(0) no_monitoring no_substrb_pad  no_expan
           d index(t,"EXT_COLUMNS_IDX") */ count(*) as nrw,count(distinct sys_op_lbid(78734
           ,'L',t.rowid)) as nlb,null as ndk,sys_op_countchg(substrb(t.rowid,1,15),1) as cl
           f from "TEST1"."EXT_COLUMNS" t where "NUM_ROWS" is not null

TEST1       g93x9gquu9t13 select min(minbkt),maxbkt,substrb(dump(min(val),16,0,32),1,120) minval,substrb(d          0
           ump(max(val),16,0,32),1,120) maxval,sum(rep) sumrep, sum(repsq) sumrepsq, max(re
           p) maxrep, count(*) bktndv, sum(case when rep=1 then 1 else 0 end) unqrep from (
           select val,min(bkt) minbkt, max(bkt) maxbkt, count(val) rep, count(val)*count(va
           l) repsq from (select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_
           sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_substrb_pa
           d */"NUM_ROWS" val, ntile(254) over (order by "NUM_ROWS") bkt    from "TEST1"."EXT
           _COLUMNS" t  where "NUM_ROWS" is not null) group by val) group by maxbkt order b
           y maxbkt

TEST1       9tu5t6fm8f34k select PARSING_SCHEMA_NAME,sql_id,sql_text,invalidations from v$sql where PARSIN          0
           G_SCHEMA_NAME='TEST1' and upper(sql_text) like '%EXT_COLUMNS%'

TEST1       29188am21rjc4 select * from ext_columns where num_rows=0                             0

6 rows selected.



SHAIKDB>exec dbms_stats.gather_table_stats('TEST1','EXT_COLUMNS',NO_INVALIDATE=>TRUE);

PL/SQL procedure successfully completed.

SHAIKDB>select PARSING_SCHEMA_NAME,sql_id,sql_text,invalidations from v$sql where PARSING_SCHEMA_NAME='TEST1' and upper(sql_text) like '%EXT_COLUMNS%';

PARSING_SC SQL_ID     SQL_TEXT                                     INVALIDATIONS
---------- ------------- -------------------------------------------------------------------------------- -------------
TEST1       0kmkdmr78n6rj select PARSING_SCHEMA_NAME,sql_id,sql_text,invalidations from v$sql where PARSIN          0
           G_SCHEMA_NAME='TEST1' and upper(sql_text) like 'EXT_COLUMNS'

TEST1       d78xb1kzg8h3u select * from ext_columns where segment_created='NO'                         0
TEST1       8bnh5pqv9t49q select PARSING_SCHEMA_NAME,sql_id,sql_text,invalidations from v$sql where PARSIN          0
           G_SCHEMA_NAME='TEST1' and sql_text like 'EXT_COLUMNS'

TEST1       fvxzppp6dda2d BEGIN dbms_stats.gather_table_stats('TEST1','EXT_COLUMNS',NO_INVALIDATE=>TRUE);          0
           END;

TEST1       fkh6yuk3jpayv select /*+ no_parallel_index(t, "EXT_COLUMNS_IDX") dbms_stats cursor_sharing_exa          0
           ct use_weak_name_resl dynamic_sampling(0) no_monitoring no_substrb_pad  no_expan
           d index(t,"EXT_COLUMNS_IDX") */ count(*) as nrw,count(distinct sys_op_lbid(78734
           ,'L',t.rowid)) as nlb,null as ndk,sys_op_countchg(substrb(t.rowid,1,15),1) as cl
           f from "TEST1"."EXT_COLUMNS" t where "NUM_ROWS" is not null

TEST1       fa216vary5rhd select substrb(dump(val,16,0,32),1,120) ep, cnt from (select /*+ no_parallel(t)          0
           no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_
           sampling(0) no_monitoring no_substrb_pad */max("SEGMENT_CREATED") val,count(*) c
           nt  from "TEST1"."EXT_COLUMNS" t  where "SEGMENT_CREATED" is not null    group by
           nlssort("SEGMENT_CREATED", 'NLS_SORT = binary')) order by nlssort(val,'NLS_SORT
           = binary')

TEST1       g93x9gquu9t13 select min(minbkt),maxbkt,substrb(dump(min(val),16,0,32),1,120) minval,substrb(d          0
           ump(max(val),16,0,32),1,120) maxval,sum(rep) sumrep, sum(repsq) sumrepsq, max(re
           p) maxrep, count(*) bktndv, sum(case when rep=1 then 1 else 0 end) unqrep from (
           select val,min(bkt) minbkt, max(bkt) maxbkt, count(val) rep, count(val)*count(va
           l) repsq from (select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_
           sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_substrb_pa
           d */"NUM_ROWS" val, ntile(254) over (order by "NUM_ROWS") bkt    from "TEST1"."EXT
           _COLUMNS" t  where "NUM_ROWS" is not null) group by val) group by maxbkt order b
           y maxbkt

TEST1       46qvsy7cg230g select substrb(dump(val,16,0,32),1,120) ep, cnt from (select /*+ no_parallel(t)          0
           no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_
           sampling(0) no_monitoring no_substrb_pad */max("DROPPED") val,count(*) cnt  from
            "TEST1"."EXT_COLUMNS" t  where "DROPPED" is not null    group by nlssort("DROPPED
           ", 'NLS_SORT = binary')) order by nlssort(val,'NLS_SORT = binary')

TEST1       9tu5t6fm8f34k select PARSING_SCHEMA_NAME,sql_id,sql_text,invalidations from v$sql where PARSIN          0
           G_SCHEMA_NAME='TEST1' and upper(sql_text) like '%EXT_COLUMNS%'

TEST1       3n4rprac5usmj select min(minbkt),maxbkt,substrb(dump(min(val),16,0,32),1,120) minval,substrb(d          0
           ump(max(val),16,0,32),1,120) maxval,sum(rep) sumrep, sum(repsq) sumrepsq, max(re
           p) maxrep, count(*) bktndv, sum(case when rep=1 then 1 else 0 end) unqrep from (
           select val,min(bkt) minbkt, max(bkt) maxbkt, count(val) rep, count(val)*count(va
           l) repsq from (select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_
           sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_substrb_pa
           d */mod("SYS_STUEXUYKWMBP9B#CB$NYP#P8I5",9999999999) val, ntile(254) over (order
            by mod("SYS_STUEXUYKWMBP9B#CB$NYP#P8I5",9999999999)) bkt  from "TEST1"."EXT_COL
           UMNS" t  where mod("SYS_STUEXUYKWMBP9B#CB$NYP#P8I5",9999999999) is not null) gro
           up by val) group by maxbkt order by maxbkt

TEST1       1hgggnzr9v7ks select * from ext_columns where num_rows=0 and DROPPED='NO'                     0
TEST1       29188am21rjc4 select * from ext_columns where num_rows=0                             0

12 rows selected.


SHAIKDB>insert into ext_columns select * from ext_columns ;

2795 rows created.

SHAIKDB>/

5590 rows created.

SHAIKDB>/

11180 rows created.

SHAIKDB>/

22360 rows created.

SHAIKDB>commit;

Commit complete.

No comments: