Oninit cdi / Archive
Talk to an expert →

sql to know indexes access

Posted in 2012

Topics: General Discussion

henrique lima - yahoo — — source: IIUG Forums & Mailing Lists

Hi, I'd like a .sql to know how are the access to table and indexes of tables. Depending on the access I'll delete some indexes. tks henrique_lima_2000@yahoo.com.br henriquelima@uol.com.br

↪ replying to henrique lima - yahoo
Celso Cabral Coimbra — — source: IIUG Forums & Mailing Lists

Maybe this query can help you select tabname, dbsname, pagesize, sysptnhdr.partnum partnum, rowsize, sum(nrows) nrows, sum(nptotal) nptotal, sum(npused) npused, sum(npdata) npdata, sum(bufreads) bufreads,sum(bufwrites) bufwrites,sum(pagreads) pagreads, sum(pagwrites) pagwrites,sum(seqscans) seqscans, sum(lockreqs) lockreqs,sum(lockwts) lockwts,sum(isreads) isreads, sum(iswrites) iswrites,sum(isrewrites) isrewrites,sum(isdeletes) isdeletes from sysmaster:sysptnhdr, sysmaster:sysptprof where sysptnhdr.partnum = sysptprof.partnum and dbsname in 'databasename' group by 1,2,3,4,5 regards, Celso Cabral Coimbra Administrador de Banco de Dados ClearTech Ltda "Trust at the heart of Communications" Tel. (11) 3576-4509 -----Mensagem original----- De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Em nome de henrique lima - yahoo Enviada em: terça-feira, 21 de fevereiro de 2012 22:31 Para: ids@iiug.org Assunto: sql to know indexes access [26296] Hi, I'd like a .sql to know how are the access to table and indexes of tables. Depending on the access I'll delete some indexes. tks henrique_lima_2000@yahoo.com.br henriquelima@uol.com.br ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to henrique lima - yahoo
Art Kagel — — source: IIUG Forums & Mailing Lists

Join sysmaster.sysptprof to sysmaster.systabnames and look for indexes that have no more reads than writes as candidates to drop. Just don't drop that index you added to support that once a year report. Art On Feb 21, 2012 8:32 PM, "henrique lima - yahoo" < henrique_lima_2000@yahoo.com.br> wrote: > Hi, > I'd like a .sql to know how are the access to table and indexes of tables. > Depending on the access I'll delete some indexes. > tks > > henrique_lima_2000@yahoo.com.br > henriquelima@uol.com.br > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e89a8f83a12f485da204b9852867