Sameer Shaik. B.E,M.S,M.B.A,P.M.P,C.S.M: Performance Management

SHAIKDB>create table part1 (i int,j int ,k int,l number,

constraint iunique unique(i),

constraint junique unique(j))

partition by range(i)

( partition p1 values less than (10),

partition p2 values less than (100),

partition p3 values less than (10000));

Table created.

SHAIKDB>begin

2 for i in 1..1000 loop

3 insert into part1 values (i,i,1,0);

4 commit;

5 end loop;

6 end;

7 /

PL/SQL procedure successfully completed.

SHAIKDB>select count(*) from part1 partition (p1);

COUNT(*)

----------

SHAIKDB>select count(*) from part1 partition (p2);

COUNT(*)

----------

SHAIKDB>select count(*) from part1 partition (p3);

COUNT(*)

----------

901

create table part2 (a int not null,b int,c number, constraint afkey foreign key(a) references part1(i))

partition by reference(afkey);

Table created.

create table part3 (x int,y number,z number not null,constraint zfkey foreign key(z) references part1(j))

partition by reference (zfkey);

Table created

SHAIKDB>select table_name,partition_name,high_value from dba_tab_partitions where table_name like 'PART_';

TABLE_NAME PARTITION_NAME HIGH_VALUE

---------- ------------------------------ ----------

PART1 P1 10

PART1 P2 100

PART1 P3 10000

PART2 P1

PART2 P2

PART2 P3

PART3 P1

PART3 P2

PART3 P3

.SHAIKDB>begin

for i in 1..1000 loop

insert into part2 values(i,i,i);

commit;

end loop;

end;

/ 2 3 4 5 6 7

PL/SQL procedure successfully completed.

SHAIKDB>begin

for i in 1..1000 loop

insert into part3 values(i,i,i);

commit;

end loop;

end;

/ 2 3 4 5 6 7

PL/SQL procedure successfully completed.

select table_name,partition_name,high_value from dba_tab_partitions where table_name like 'PART_';

TABLE_NAME PARTITION_NAME HIGH_VALUE

---------- ------------------------------ ----------

PART1 P1 10

PART1 P2 100

PART1 P3 10000

PART2 P1

PART2 P2

PART2 P3

PART3 P1

PART3 P2

PART3 P3

9 rows selected

SHAIKDB>create index part1idx on part1(k);

Index created.

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')