Oracle Performance Diagnostics Art-Reading notes

20170525
1537
Trcsess.awk tool, use similar to the following:
Awk-f Trcsess.awk xxx.trc > Xxx.more


1615
Tvdztat-i .... trc-o xxx.html

20170526
1604
Select T1.pname,t1.pval1,t1.pval2 from sys.aux_stats$ t1
where t1.sname= ' Sysstats_main ';

Select sum (t1. Singleblkrds) as Count,sum (t1. Singleblkrdtim) Time_ms from V$filestat T1;

CREATE TABLE T as
Select RowNum as ID,
Round (dbms_random.normal*1000) as Val1,
100+round (ln (rownum/3.25+2)) as Val2,
100+round (ln (rownum/3.25+2)) as Val3,
Dbms_random.string (' P ', ') as pad
From All_objects
where rownum<=1000
Order BY Dbms_random.value;

Update T set val1=null where val1<0;

ALTER TABLE t add constraints T_PK primary key (ID);

CREATE index t_val1_i on T (VAL1);

CREATE index t_val2_i on T (VAL2);

Begin
Dbms_stats.gather_table_stats (
Ownname=>user,
Tabname=> ' T ',
ESTIMATE_PERCENT=&GT;100,
Method_opt = ' For all columns size skewonly ',
Cascade=>true);
End

Select T1. Num_rows,t1. Blocks,t1. Empty_blocks,t1. Avg_space,t1. Chain_cnt,t1. Avg_row_len
From User_tab_statistics T1
where T1. Table_name= ' T ';




Num_rows BLOCKS empty_blocks avg_space chain_cnt Avg_row_len
---------- ---------- ------------ ---------- ---------- -----------
1000 44 0 0 0 266


/*
Num_rows rows of data in a table
BLOCKS the number of data blocks below the high watermark line
Empty_blocks the number of data blocks above the high water mark line
Average free space (in bytes) of data blocks in the Avg_space table
chain_cnt total rows involving row migration, row links
The average length of each record in the Avg_row_len table (units: bytes)
*/


Select T1.column_name name,t1.num_distinct "#dst", T1.low_value,t1.high_value,
T1.density dens,t1.num_nulls "#null", T1.avg_col_len avglen,t1. Histogram,t1.num_buckets "#bkt"
From User_tab_col_statistics T1
where t1.table_name= ' T ';

name                                  #dst low_value                                                           high_value                                                                dens      #null       Avglen histogram             #bkt
------------------------------ ---------- ---------------------------------------------------------------- ------- --------------------------------------------------------- ---------- ---------- ---------- --------------- --------- -
id                                    c102                                                                 c20b                                                                     . 001          0          4 none                      1
val1                                   457 c105                                                                 c22160                                                              .002353264         481&NBSP;&NBSP;&NBSP;       3 HEIGHT balanced        254
val2                                     6 c20202                                                              c20207                                                                   .0005           0          4 frequency                 6
val3                                     6 c20202                                                              c20207                                                                   .0005           0          4 frequency                 6
PAD 203f2c5d523371586e695d456775533c565a522a4f3128234f43502f77353026 7E7E7438735D7A4 64e77205b7b79454d517e384069784521344735457e2f2120.001 0 251 HEIGHT BALANCED 254


Select Utl_raw.cast_to_number (low_value) as Low_value,
Utl_raw.cast_to_number (High_value) as High_value
From User_tab_col_statistics
where Table_name= ' T '
and Column_name= ' VAL1 ';


/*
Low_value High_value
---------- ----------
4 3295
*/

Begin
Dbms_stats.gather_index_stats (Ownname =>user, indname = ' t_val1_i ', estimate_percent=>100);
End




Select T1. Index_name,t1.blevel,t1. Leaf_blocks,t1. Distinct_keys,
T1. Num_rows,t1. Clustering_factor,t1. Avg_leaf_blocks_per_key,t1. Avg_data_blocks_per_key
From User_ind_statistics T1
where T1. Table_name= ' T ';


/*
Index_name blevel leaf_blocks distinct_keys num_rows clustering_factor avg_leaf_blocks_per_key A Vg_data_blocks_per_key
------------------------------ ---------- ----------- ------------- ---------- ----------------- -------------------- --- -----------------------
T_PK 1 2 1000 1000 972 1 1
T_val1_i 1 2 457 519 508 1 1
T_val2_i 1 3 6 1000 174 1 29

Blevel the number of branch blocks that need to be read to access the leaf quickly, including the root block
Number of leaf blocks in the leaf_blocks index
Total number of unique key values in the Distinct_keys index
Num_rows the number of key values in the index, equivalent to Distinct_keys for the primary key
Clustering_factor indicates how many adjacent index entries know different blocks of data.
If the sorting of the data and indexes in the table is similar, then the clustering factor is small. Minimum value is the total number of data blocks for non-empty data in the table
If the data and indexes in the table are sorted in different order, the clustering factor is very large. The maximum value is the number of key values in the index

*/



1621
Create or Replace function Clustering_factor (
P_owner in Varchar2,
P_table_name in Varchar2,
P_column_name in VARCHAR2)
Return number is
L_cursor Sys_refcursor;
L_clustering_factor binary_integer:=0;
L_BLOCK_NR binary_integer:=0;
L_PREVIOUS_BLOCK_NR binary_integer:=0;
L_FILE_NR Binary_integer: = 0;
L_PREVIOUS_FILE_NR binary_integer:=0;
Begin
Open L_cursor for
' Select Dbms_rowid.rowid_block_number (ROWID) block_nr, ' | |
' Dbms_rowid.rowid_to_absolute_fno (rowid, ' ' | |
p_owner| | "," | |
p_table_name| | ") File_nr ' | |
' From ' | | p_owner| | '. ' | | p_table_name| | ' ' | |
' WHERE ' | | p_column_name| | ' is not NULL ' | |
' ORDER BY ' | | P_column_name;
Loop
Fetch l_cursor into L_BLOCK_NR,L_FILE_NR;
Exit when L_cursor%notfound;
if (l_previous_block_nr <> l_block_nr or L_previous_file_nr <>l_file_nr)
Then
l_clustering_factor:=l_clustering_factor+1;
End If;
L_PREVIOUS_BLOCK_NR:=L_BLOCK_NR;
L_PREVIOUS_FILE_NR:=L_FILE_NR;
End Loop;
Close l_cursor;
return l_clustering_factor;
End Clustering_factor;


1636
Select I.index_name,i.clustering_factor,clustering_factor (user,i.table_name,ic. column_name) as My_clstf
From User_indexes i,user_ind_columns IC
where I.table_name= ' T ' and i.index_name=ic. index_name;


Index_name Clustering_factor MY_CLSTF
------------------------------ ----------------- ----------
T_PK 972 972
T_val1_i 508 508
T_val2_i 174 174


1732
Lock Object Statistics
Dbms_stats.lock_table_stats (ownname=>user,tabname=> ' table_name ');
Dbms_stats.lock_schema_stats (Ownname=>user);

Unlocking Object Statistics
Dbms_stats.unlock_table_stats (ownname=>user,tabname=> ' table_name ');
Dbms_stats.unlock_schema_stats (Ownname=>user);

Oracle Performance Diagnostics Art-Reading notes