Skip to content

postgresql query to find duplicate indexes

postgresql query to find duplicate indexes published on Комментариев к записи postgresql query to find duplicate indexes нет
     
SELECT sub.table, pg_size_pretty(SUM(pg_relation_size(idx))::BIGINT) AS SIZE,
     (array_agg(idx))[1] AS idx1, (array_agg(idx))[2] AS idx2,
     (array_agg(idx))[3] AS idx3, (array_agg(idx))[4] AS idx4 
            FROM (  SELECT indrelid::regclass as table,indexrelid::regclass AS idx, (indrelid::text ||E'\n'|| indclass::text ||E'\n'|| indkey::text ||E'\n'|| 
                    COALESCE(indexprs::text,'')||E'\n' || COALESCE(indpred::text,'')) AS KEY 
                    FROM pg_index
                     where  pg_relation_size(indexrelid::regclass)>100*1024*1024 ) sub 
GROUP BY sub.table, KEY HAVING COUNT(*)>1 
ORDER BY SUM(pg_relation_size(idx)) DESC;

sample output

       table       |  size  |            idx1             |                idx2                | idx3 | idx4
-------------------+--------+-----------------------------+------------------------------------+------+------
 mdm_record_column | 797 MB | mdm_record_column_column_fk | mdm_record_column_record_fk        |      |
 fin_bill_generate | 300 MB | fin_bill_generate_fk        | fin_bill_generate_spec_item_id_idx |      |

also usefull to look at usage stat on this indexes

SELECT 
    relid::regclass AS table, 
    indexrelid::regclass AS index, 
    pg_size_pretty(pg_relation_size(indexrelid::regclass)) AS index_size, 
    idx_tup_read, 
    idx_tup_fetch, 
    idx_scan,
    pg_get_indexdef(pg_index.indexrelid) as indexdef
FROM 
    pg_stat_user_indexes 
    JOIN pg_index USING (indexrelid) 
WHERE 
     (relid::regclass)::text ='mdm_record_column' order by idx_scan desc ;

       table       |              index              | index_size | idx_tup_read | idx_tup_fetch | idx_scan |                                         indexdef
-------------------+---------------------------------+------------+--------------+---------------+----------+-------------------------------------------------------------------------------------------
 mdm_record_column | mdm_record_column_record_fk     | 399 MB     |       758024 |        758024 |     2992 | CREATE INDEX mdm_record_column_record_fk ON mdm_record_column USING btree (column_id)
 mdm_record_column | mdm_record_column_record_id_idx | 399 MB     |         1922 |          1922 |      442 | CREATE INDEX mdm_record_column_record_id_idx ON mdm_record_column USING btree (record_id)
 mdm_record_column | mdm_record_column_pk            | 399 MB     |            0 |             0 |        0 | CREATE UNIQUE INDEX mdm_record_column_pk ON mdm_record_column USING btree (id)
 mdm_record_column | mdm_record_column_column_fk     | 399 MB     |            0 |             0 |        0 | CREATE INDEX mdm_record_column_column_fk ON mdm_record_column USING btree (column_id)

PgSQL Indexes and «LIKE»

PgSQL Indexes and «LIKE» published on Комментариев к записи PgSQL Indexes and «LIKE» нет

Original from Paul Ramsey

Hi all, because English is not my native language, that’s why I will write as little as possible =)
here is the case, when we write a query with like and get Seq Scan (full table scan), instead index scan this material can be usefull for you

create table tt as   select s, md5(random()::text) from generate_Series(1,990000) s;
 create index on tt(md5);
 show LC_COLLATE;
 lc_collate
-------------
 en_US.UTF-8

 explain analyze select * from tt where md5 like 'a6b90b58a652b8e1bd01bbe2%';
                                               QUERY PLAN
--------------------------------------------------------------------------------------------------------
 Seq Scan on tt  (cost=0.00..20625.00 rows=4950 width=36) (actual time=132.559..132.559 rows=0 loops=1)
   Filter: (md5 ~~ 'a6b90b58a652b8e1bd01bbe2%'::text)
   Rows Removed by Filter: 990000
 Planning time: 0.203 ms
 Execution time: 132.583 ms

if we create same index but with text_pattern_ops we can get good improvement:

 create index on tt(md5 text_pattern_ops);

 explain analyze select * from tt where md5 like 'a6b90b58a652b8e1bd01bbe2%';
                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Index Scan using tt_md5_idx1 on tt  (cost=0.42..8.45 rows=99 width=37) (actual time=0.022..0.022 rows=0 loops=1)
   Index Cond: ((md5 >= 'a6b90b58a652b8e1bd01bbe2'::text) AND (md5 < 'a6b90b58a652b8e1bd01bbe3'::text))
   Filter: (md5 ~~ 'a6b90b58a652b8e1bd01bbe2%'::text)
 Planning time: 0.403 ms
 Execution time: 0.043 ms

ORA-42016: shape of interim table does not match specified column mapping

ORA-42016: shape of interim table does not match specified column mapping published on 1 комментарий к записи ORA-42016: shape of interim table does not match specified column mapping

try to make table partitioned:
create dummy table:

  CREATE TABLE USER."PERS_DATA_OPERATION_LOG_REDEF" 
   (	"ID" NUMBER NOT NULL ENABLE, 
	"OBJECT_OID" NUMBER NOT NULL ENABLE, 
	"OBJECT_SNILS" VARCHAR2(256), 
	"OBJECT_FULL_NAME" VARCHAR2(4000), 
	"SUBJECT_OID" NUMBER, 
	"SUBJECT_SNILS" VARCHAR2(256), 
	"SUBJECT_FULL_NAME" VARCHAR2(4000), 
	"CRT_ON" TIMESTAMP (6) , 
	"OPERATION_TYPE" VARCHAR2(256), 
	 CHECK (OPERATION_TYPE IN ('VIEW','PASS_RESET','REMOVE','BGIR_RESTART')) ENABLE, 
	 CONSTRAINT "PERS_DATA_OPERATION_LOG_PK1" PRIMARY KEY ("ID")
   ) 
     PARTITION BY RANGE ("CRT_ON")    INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
     (partition p0 VALUES LESS THAN (TIMESTAMP' 2011-01-01 00:00:00') ) ;

start redef and get error:

exec dbms_redefinition.can_redef_table ('USER','PERS_DATA_OPERATION_LOG',dbms_redefinition.cons_use_pk);
   
exec  DBMS_REDEFINITION.start_redef_table ('USER','PERS_DATA_OPERATION_LOG','PERS_DATA_OPERATION_LOG_REDEF',NULL,DBMS_REDEFINITION.cons_use_pk);

ORA-42016: shape of interim table does not match specified column mapping

this is because original table have a timestamp with local timezone format
how to fix it:

select listagg(column_name,',') within  group  ( order by column_id) from dba_tab_columns where table_name='PERS_DATA_OPERATION_LOG';

begin 
DBMS_REDEFINITION.start_redef_table(
    uname        => 'USER',
    orig_table   => 'PERS_DATA_OPERATION_LOG',
    int_table    => 'PERS_DATA_OPERATION_LOG_REDEF',
    col_mapping =>'ID,OBJECT_OID,OBJECT_SNILS,OBJECT_FULL_NAME,SUBJECT_OID,SUBJECT_SNILS,SUBJECT_FULL_NAME,to_timestamp(crt_on) crt_on,OPERATION_TYPE',
    options_flag => DBMS_REDEFINITION.cons_use_pk);
end;



SET SERVEROUTPUT ON
DECLARE
 l_num_errors PLS_INTEGER;
BEGIN
 DBMS_REDEFINITION.copy_table_dependents(
   uname             => 'USER',
   orig_table        => 'PERS_DATA_OPERATION_LOG',
   int_table         => 'PERS_DATA_OPERATION_LOG_REDEF',
   copy_indexes      => DBMS_REDEFINITION.cons_orig_params, -- Non-Default
   copy_triggers     => TRUE,  -- Default
   copy_constraints  => TRUE,  -- Default
   copy_privileges   => TRUE,  -- Default
   ignore_errors     => true,
   num_errors        => l_num_errors);
 DBMS_OUTPUT.put_line('l_num_errors=' || l_num_errors);
END;
/
exec DBMS_REDEFINITION.SYNC_INTERIM_TABLE('USER','PERS_DATA_OPERATION_LOG','PERS_DATA_OPERATION_LOG_REDEF');

EXEC DBMS_REDEFINITION.finish_redef_table('USER', 'PERS_DATA_OPERATION_LOG', 'PERS_DATA_OPERATION_LOG_REDEF' );

Oracle 12c Adaptive Query Optimization overhead

Oracle 12c Adaptive Query Optimization overhead published on Комментариев к записи Oracle 12c Adaptive Query Optimization overhead нет

Trying to find out overhead of new Adaptive Query Optimization feature

use perf to find out process cpu usage ( eg:perf stat -e task-clock -p 9576 sleep 5 )
Screen Shot 2016-08-24 at 21.45.07

Screen Shot 2016-08-24 at 21.46.40
to disable adaptive features I used

alter system set optimizer_features_enable='11.2.0.1';

so the result is:

3000 hpares

3000 hpares

3000 hpares

3000 hpares

3000 hpares

avg

adaptive off

1724,46

1786,04

1696,22

1687,89

1690,89

1717,10

adaptive on

1803,79

1815,98

1966,07

1804,35

1819,35

1841,91

overhead

4,40

1,65

13,73

6,45

7,06

6,78

ps: lotshparses.sql link

How to resolve SQL object and column names all the way to base tables and columns in Oracle?

How to resolve SQL object and column names all the way to base tables and columns in Oracle? published on Комментариев к записи How to resolve SQL object and column names all the way to base tables and columns in Oracle? нет

original from Tanel Poder

was looking for source of v$restore point view, helped article from Tanel =)

 alter session set "_dump_qbc_tree"=1;

result is :

 SELECT  "ADDR","INDX","INST_ID","CON_ID","NRSNAME","NRSINCARN","NRSSCN","NRSTIME","NRSFLAGS","NRSRSPTIME","NRSRID","NRSSTM" FROM "SYS"."X$KCCNRS"   "X$KCCNRS" WHERE (con_id IN (0, 3) );

upd: also it can be done with:

 select view_definition from v$fixed_view_definition where view_name='GV$RESTORE_POINT';

script to find tables with nonindexed FK

script to find tables with nonindexed FK published on Комментариев к записи script to find tables with nonindexed FK нет

need to pass schema name as firs script argument

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

Troubleshooting ‘enq: TX — index contention’ Waits (Doc ID 873243.1)

Troubleshooting ‘enq: TX — index contention’ Waits (Doc ID 873243.1) published on Комментариев к записи Troubleshooting ‘enq: TX — index contention’ Waits (Doc ID 873243.1) нет

today faced with ‘enq: TX — index contention’

Screen Shot 2016-08-05 at 21.08.15

awr shows

Screen Shot 2016-08-05 at 20.01.41

helped to make partition index on nonpartition table:

drop index ARADMIN.IT3060;
CREATE UNIQUE INDEX ARADMIN.IT3060_P ON "ARADMIN"."T3060" ("C1") global partition by hash (C1) partitions 16 parallel 16 ;
drop index ARADMIN.T3060_IDX_12;
CREATE index ARADMIN.T3060_IDX_12 ON "ARADMIN"."T3060"  ("C536870937", "C7", "C536870938", "C1") global partition by hash ("C536870937", "C7", "C536870938", "C1")  partitions 16 parallel 16 TABLESPACE "ARSYSTEM" ;
drop index ARADMIN.T3060_IDX_11;
CREATE index ARADMIN.T3060_IDX_11 ON "ARADMIN"."T3060"  ("C536870922", "C7", "C536870941") global partition by hash ("C536870922", "C7", "C536870941")  partitions 16 parallel 16 TABLESPACE "ARSYSTEM" ;

CRS-6706: Oracle Clusterware Release patch level (‘2222840392’) does not match Software patch level (‘0’). Oracle Clusterware cannot be started.

CRS-6706: Oracle Clusterware Release patch level (‘2222840392’) does not match Software patch level (‘0’). Oracle Clusterware cannot be started. published on Комментариев к записи CRS-6706: Oracle Clusterware Release patch level (‘2222840392’) does not match Software patch level (‘0’). Oracle Clusterware cannot be started. нет

faced with

COMMAND EXECUTION FAILURE :
Using configuration parameter file: /ora01/app/grid/12.1.0.2/OPatch/opatchautotemp_rac2/patchwork/crs/install/crsconfig_params
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'rac2'
CRS-2673: Attempting to stop 'ora.crf' on 'rac2'
CRS-2673: Attempting to stop 'ora.mdnsd' on 'rac2'
CRS-2673: Attempting to stop 'ora.gpnpd' on 'rac2'
CRS-2677: Stop of 'ora.mdnsd' on 'rac2' succeeded
CRS-2677: Stop of 'ora.gpnpd' on 'rac2' succeeded
CRS-2677: Stop of 'ora.crf' on 'rac2' succeeded
CRS-2673: Attempting to stop 'ora.gipcd' on 'rac2'
CRS-2677: Stop of 'ora.gipcd' on 'rac2' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'rac2' has completed
CRS-4133: Oracle High Availability Services has been stopped.
CRS-6706: Oracle Clusterware Release patch level ('2222840392') does not match Software patch level ('0'). Oracle Clusterware cannot be started.
CRS-4000: Command Start failed, or completed with errors.
2016/08/01 10:21:54 CLSRSC-117: Failed to start Oracle Clusterware stack

helped solution from:
CRS-1153: There was an error setting Oracle Clusterware to rolling patch mode. (Doc ID 1943498.1)
CRS-6706: Oracle Clusterware Release patch level (‘nnn’) does not match Software patch level (‘mmm’) (Doc ID 1639285.1)

first one:

[root@rac2 ~]# crsctl stop rollingpatch
CRS-1171: Rejecting rolling patch mode change because the patch level is not consistent across all nodes in the cluster. The patch level on nodes rac1 is not the same as the patch level [2369764000] found on nodes rac2.
CRS-4000: Command Stop failed, or completed with errors.

with no luck

second is successfull:

[root@rac1 ~]# kfod op=patchlvl
-------------------
Current Patch level
===================
2369764000
[root@rac1 ~]# kfod op=patches
---------------
List of Patches
===============
19396455
19769480
20299023
20831110
21359755
21436941
21948341
21948344
21948354


[root@rac2 ~]# kfod op=patches
---------------
List of Patches
===============
19396455
[root@rac2 ~]# kfod op=patchlvl
-------------------
Current Patch level
===================
2222840392

[root@rac1 ~]# sh /ora01/app/grid/12.1.0.2/crs/install/rootcrs.sh -patch
Using configuration parameter file: /ora01/app/grid/12.1.0.2/crs/install/crsconfig_params
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'rac1'
...
2016/08/01 10:57:54 CLSRSC-4015: Performing install or upgrade action for Oracle Trace File Analyzer (TFA) Collector.

2016/08/01 10:59:46 CLSRSC-4005: Failed to patch Oracle Trace File Analyzer (TFA) Collector. Grid Infrastructure operations will continue.
.....
Oracle Clusterware active version on the cluster is [12.1.0.2.0]. The cluster upgrade state is [NORMAL]. The cluster active patch level is [2369764000].

Primary Sidebar

Яндекс.Метрика