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

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

****************I am authoring a Book on Oracle Database Cloud Services.......More details to follow.***************

Title : Oracle Database Cloud Revealed
Publisher : Apress
Release Date : Jan-2019

**********************************************************************************


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]


My Cloud Certifications:

AWS Certified Solutions Architect Associate

Oracle Cloud Infrastructure 2018 Certified Architect Associate.

Oracle Cloud Infrastructure Classic 2018 Certified Architect Associate.

Oracle Database Cloud Administrator Certified Professional.

Oracle Database Cloud Service Operations Certified Associate.

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: