Январь 2017 – bushmelev-aa.ru

oracle TM lock, how-to find who forgot index on FK ?

26.01.2017 Bushmelev Anton Комментариев нет

Lets do TM lock and find out what object should be indexed

create table tt ( id number ,fld varchar2(50) );
create table ttt ( id number ,id_tt number );
alter table tt add constraint pk_id primary key (id) ;
alter table ttt add foreign key ( id_tt ) references tt (id);
insert into tt select level ,rpad ('x',40) from dual connect by level <=100;
commit;

sess 1

 insert into ttt values ( 1,1);

sess 2

 delete from tt where id=1;

I use Tanel Poder sripts in this example
this one ( ashtop.sql )
and this one

oid.sql

— Script by Tanel Poder (http://www.tanelpoder.com)
—
— Look up object info by object id

col o_owner heading owner for a25
col o_object_name heading object_name for a30
col o_object_type heading object_type for a18
col o_status heading status for a9

select
owner o_owner,
object_name o_object_name,
object_type o_object_type,
subobject_name,
created,
last_ddl_time,
status o_status,
data_object_id
from
dba_objects
where
object_id in (&1)
order by
o_object_name,
o_owner,
o_object_type

[свернуть]

result :

or use this scipt to find nonindexed foreing keys:

fk_no_indx

select table_name, column_name
from ( select c.table_name, cc.column_name, cc.position column_position
from all_constraints c, all_cons_columns cc
where c.constraint_name = cc.constraint_name
and c.constraint_type = ‘R’ and c.owner=cc.owner and c.owner=’&1′
minus
select i.table_name, ic.column_name, ic.column_position
from all_indexes i, all_ind_columns ic
where i.index_name = ic.index_name and i.owner=ic.table_owner and i.owner=’&1′);

[свернуть]

18:13:51 (1)c##bushmelev_aa@u10pgp> @fk_no_indx C##BUSHMELEV_AA


TABLE_NAME | COLUMN_NAME
---------- | ---------------
TTT        | ID_TT

postgresql table index usage info

23.01.2017 Bushmelev Anton Комментариев нет

    
   SELECT 
            pg_stat_all_tables.schemaname,relid::regclass AS table, 
            indexrelid::regclass AS index, 
            pg_size_pretty(pg_relation_size(indexrelid::regclass)) AS index_size, 
            pg_stat_user_indexes.idx_tup_read,            
                round (100*(
                    sum( pg_stat_user_indexes.idx_tup_read)/
                    ( sum(pg_stat_user_indexes.idx_tup_read) over ()) ),1) as pct_idx_tup_read,
            pg_stat_user_indexes.idx_tup_fetch,
             round(100*(
                    sum(pg_stat_user_indexes.idx_tup_fetch)/
                    (sum(pg_stat_user_indexes.idx_tup_fetch) over ()) ),1) as pct_idx_tup_fetch,
            pg_stat_user_indexes.idx_scan,
            round(100*(
                    sum(   pg_stat_user_indexes.idx_scan)/
                    (     sum(pg_stat_user_indexes.idx_scan) over ()) ),1) as pct_idx_scan,
            pg_get_indexdef(pg_index.indexrelid) as indexdef
        FROM 
            pg_stat_user_indexes 
            JOIN pg_index USING (indexrelid)
            join pg_stat_all_tables using (relid) 
        WHERE 
             (relid::regclass)::text ='table_name' 
             group by idx_tup_read,pg_stat_all_tables.schemaname,relid,indexrelid,pg_stat_user_indexes.idx_tup_fetch,pg_stat_user_indexes.idx_scan,pg_index.indexrelid
             order by idx_scan desc ;

schemaname |     table      |                   index                    | index_size | idx_tup_read | pct_idx_tup_read | idx_tup_fetch | pct_idx_tup_fetch | idx_scan  | pct_idx_scan |                                                     indexdef
------------+----------------+--------------------------------------------+------------+--------------+------------------+---------------+-------------------+-----------+--------------+------------------------------------------------------------------------------------------------------------------
 public     | fin_bill_steps | fin_bill_steps_bill_id_customer_id_idx     | 1462 MB    |    395213398 |             83.7 |     197898196 |              88.7 | 158647275 |         89.8 | CREATE INDEX fin_bill_steps_bill_id_customer_id_idx ON fin_bill_steps USING btree (bill_id, customer_id)
 public     | fin_bill_steps | fin_bill_steps_bill_id_srv_rendered_id_idx | 5061 MB    |     66673035 |             14.1 |      23200795 |              10.4 |  16720920 |          9.5 | CREATE INDEX fin_bill_steps_bill_id_srv_rendered_id_idx ON fin_bill_steps USING btree (bill_id, srv_rendered_id)
 public     | fin_bill_steps | fin_bill_steps_spec_item_id_idx            | 4999 MB    |      1198864 |              0.3 |       1187406 |               0.5 |   1187537 |          0.7 | CREATE INDEX fin_bill_steps_spec_item_id_idx ON fin_bill_steps USING btree (spec_item_id)
 public     | fin_bill_steps | fin_bill_steps_bill_id_case_id_idx         | 5087 MB    |      8697564 |              1.8 |        748173 |               0.3 |     90822 |          0.1 | CREATE INDEX fin_bill_steps_bill_id_case_id_idx ON fin_bill_steps USING btree (bill_id, case_id)
 public     | fin_bill_steps | fin_bill_steps_bill_id_steps_id_idx        | 5692 MB    |       143034 |              0.0 |          4065 |               0.0 |      4055 |          0.0 | CREATE INDEX fin_bill_steps_bill_id_steps_id_idx ON fin_bill_steps USING btree (bill_id, step_id)
(5 rows)

postgresql top 10 tables by io operations

23.01.2017 Bushmelev Anton Комментариев нет

helps to keep focus on “hot” tables
top 10 tables by sum of io operatoins:

SELECT
st.schemaname||'.'||st.relname,
round (100*( 
		sum (coalesce(st.n_tup_ins,0)+coalesce(st.n_tup_upd,0)-coalesce(st.n_tup_hot_upd,0)+coalesce(st.n_tup_del,0)) 
) 
	/ 
(
		sum (coalesce(st.n_tup_ins,0)+coalesce(st.n_tup_upd,0)-coalesce(st.n_tup_hot_upd,0)+coalesce(st.n_tup_del,0)) over () 
),2) as pct_io_ops
 from pg_stat_user_tables st
JOIN pg_class c ON c.oid=st.relid 
LEFT JOIN pg_tablespace t ON t.oid=c.reltablespace
WHERE
coalesce(n_tup_ins,0)+coalesce(n_tup_upd,0)-coalesce(n_tup_hot_upd,0)+coalesce(n_tup_del,0)>100
group by st.schemaname||'.'||st.relname,st.n_tup_ins,st.n_tup_upd,st.n_tup_hot_upd,st.n_tup_del
order by pct_io_ops desc 
limit 10;

top 10 tables by index scans:

   SELECT 
        pg_stat_all_tables.schemaname||'.'||relid::regclass AS table, 
        round (100* (sum(pg_stat_user_indexes.idx_scan))
                      / (sum(pg_stat_user_indexes.idx_scan) over () )
                       ,2) pct_indx_scans
    FROM 
        pg_stat_user_indexes 
        JOIN pg_index USING (indexrelid)
        join pg_stat_all_tables using (relid)
        group by pg_stat_all_tables.schemaname||'.'||relid::regclass,pg_stat_user_indexes.idx_scan
        order by 2 desc 
        limit 10;

Primary Sidebar