fbtracein.py

import fdb
import argparse
import os
import datetime

def get_indices(con):
    c = con.cursor()
    c.execute('SELECT RDB$INDEX_NAME FROM RDB$INDICES')
    result = [x[0].strip() for x in c.fetchall()]
    c.close()
    return frozenset(result)

def process_indices(indices, plans, out):
    used_indices = set()
    for plan in plans.keys():
        step_1 = plan.partition(' INDEX (')
        while step_1[1]:
            step_2 = step_1[2].partition(')')
            index_names = set([x.strip() for x in step_2[0].split(',')])
            used_indices = used_indices.union(index_names)
            step_1 = step_1[2].partition(' INDEX (')

    out.write('\nUsed indices:\n')
    out.write('-------------\n\n')

    for index in used_indices:
        out.write(index)
        out.write('\n')

    out.write('\nUnused indices:\n')
    out.write('---------------\n\n')

    for index in indices.difference(used_indices):
        out.write(index)
        out.write('\n')

def main():

    parser = argparse.ArgumentParser(description="Tool to analyze index usage.")

    parser.add_argument('database',
                        help="Full path to database.")
    parser.add_argument('output', type=argparse.FileType('w'),
                        metavar='FILENAME',
                        help="File to store trace report.")
    parser.add_argument('-o','--host',
                        help="Server host.")
    parser.add_argument('-u','--user',
                        default=os.environ.get('ISC_USER', 'sysdba'),
                        help="User name")
    parser.add_argument('-p','--password',
                        default=os.environ.get('ISC_PASSWORD', None),
                        help="User password")

    args = parser.parse_args()

    # We need password
    if not args.password:
        print "A password is required to use the Services Manager."
        print
        parser.print_help()
        return

    trace_config = """<database %s>
  enabled true
  log_statement_finish true
  print_plan true
  include_filter %%SELECT%%
  exclude_filter %%RDB$%%
  time_threshold 0
  max_sql_length 2048
</database>
"""
    try:
        if args.host:
            dsn = args.host + ':' + args.database
            svc_host = args.host + ':service_mgr'
        else:
            dsn = args.database
            svc_host = 'service_mgr'
        con = fdb.connect(dsn=dsn,user=args.user,password=args.password)
        indices = get_indices(con)
        con.close()
        svc = fdb.services.connect(host=svc_host,user=args.user,password=args.password)

        trace_id = svc.trace_start(config=trace_config % args.database)
        trace_start = datetime.datetime.now()
        print "Trace session %i started." % trace_id
    except Exception as e:
        print e
        exit

    print "Use tracex.py to stop this trace session and produce report."

    plans = {}
    end_msg = 'Trace ended normally.'
    try:
        for line in svc:
            if line.startswith('PLAN ('):
                line = line.strip()
                if line in plans:
                    plans[line] += 1
                else:
                    plans[line] = 1
    except Exception as e:
        end_msg = 'Ended with exception:\n%s\n' % str(e)
        print end_msg
    else:
        print "Trace session stopped"

    trace_stop = datetime.datetime.now()

    args.output.write('Database: %s\n\n' % args.database)

    args.output.write('Trace start: %s\n' % trace_start.strftime('%c'))
    args.output.write('Trace end:   %s\n\n' % trace_stop.strftime('%c'))

    args.output.write(end_msg)

    args.output.write('\n\nPlans for executed statements:\n')
    args.output.write('------------------------------\n\n')

    for key,value in plans.items():
        args.output.write('%7dx: %s\n' % (value,key))
    args.output.write('\n')
    process_indices(indices,plans,args.output)
    args.output.close()

    print 'Processing finished.'
    raw_input('press any key...')
    svc.close()


if __name__ == '__main__':
    main()

Example trace session:

fbtracein.py -p masterkey somedb somedb.report
Trace session 5 started.
Use tracex.py to stop this trace session and produce report.
Trace session stopped
Processing finished.
press any key...

Report Example:

Database: SOMEDATABASE

Trace start: Mon Sep 17 14:54:04 2012
Trace end:   Mon Sep 17 18:34:32 2012

Trace ended normally.

Plans for executed statements:
------------------------------

  13337x: PLAN (IRQ INDEX (INSERTIONS_READ_QUEUE_IDX1))
      1x: PLAN (GJ NATURAL)
      6x: PLAN (WC NATURAL)
     28x: PLAN (P NATURAL)JOIN (RSA INDEX (PK_REALITY_SERVER_AREAS), RS INDEX (PK_REALITY_SERVERS))(SP_IDENTIFY_VILLAGE_FULL NATURAL)(SP_IDENTIFY_VILLAGE_FULL NATURAL)(SP_IDENTIFY_VILLAGE NATURAL)(SP_IDENTIFY_VILLAGE_IN_TEXT NATURAL)(SP_IDENTIFY_VILLAGE_IN_TEXT NATURAL)JOIN (RSA INDEX (PK_REALITY_SERVER_AREAS), RSA_NEW INDEX (REALITY_SERVER_AREAS_IDX1))(L INDEX (LOCALITIES_IDX1))(SP_IDENTIFY_VILLAGE NATURAL)JOIN (V INDEX (PK_VILLAGES), D INDEX (DISTRICTS_IDX2, PK_DISTRICTS))(V INDEX (PK_VILLAGES))(SP_IDENTIFY_VILLAGE_FROM_ZIP NATURAL)(SP_IDENTIFY_VILLAGE_IN_TEXT NATURAL)(D INDEX (PK_DISTRICTS))(SP_GET_REALITY_ORDER_AREA NATURAL)(CATX INDEX (UNQ1_CENTRAL_ACTIVITY_TYPES))(SP_IDENTIFY_VILLAGE_IN_TEXT NATURAL)(SP_IDENTIFY_VILLAGE_IN_TEXT NATURAL)(SP_IDENTIFY_VILLAGE_IN_TEXT NATURAL)(SP_GET_REALTY_DISPOSITION NATURAL)(SP_GET_REALTY_PRAGUE_LOCALITY NATURAL)(SP_IS_INSERTION_PERSONAL3 NATURAL)(I INDEX (PK_INSERTIONS))(ITV INDEX (INSERTIONS_TO_VERIFY_IDX1))
  13337x: PLAN (IRQF INDEX (INSERTIONS_READ_QUEUE_FAIL_IDX1))
      1x: PLAN (FSS_INDEXING_QUEUE INDEX (PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE))
      3x: PLAN (RS INDEX (REALITY_SERVERS_IDX2))
      1x: PLAN (REM INDEX (WEB_REMINDERS_IX_USER_ADV))
  13337x: PLAN (I INDEX (INSERTIONS_IDX4))
     67x: PLAN (RMS INDEX (WEB_REMINDERS_IX_USER_ADV))
      2x: PLAN (FSS_INDEXING_QUEUE INDEX (PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE))
      1x: PLAN (FSS_INDEXING_QUEUE INDEX (PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE))
      1x: PLAN (I INDEX (INSERTIONS_IDX5))
    241x: PLAN (IAN INDEX (INSERTION_ATTRIB_NAMES_IDX1))(IAV INDEX (INSERTION_ATTRIB_VALUES_IDX1))(IA INDEX (INSERTION_ATTRIBS_IDX1))(IA INDEX (PK_INSERTION_ATTRIBS))
    264x: PLAN (SIQ INDEX (SUBJECT_USER_INSERTION_Q_IDX1))(SIQ INDEX (SUBJECT_USER_INSERTION_Q_IDX1))(SWI INDEX (SUBJECT_WEB_INSERTIONS_IDX10))(SIQ INDEX (FK_SUBJECT_USER_INSERTION_Q_4))(SISB INDEX (PK_SUBJECT_INS_SEND_BATCHES))
      1x: PLAN (FSS_INDEXING_QUEUE INDEX (PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE))
      1x: PLAN (SIQX INDEX (FK_SUBJECT_USER_INSERTION_Q_2))
      4x: PLAN (STAT INDEX (PK_WEB_PROC_STATES))
      3x: PLAN (FSS_INDEXING_QUEUE INDEX (PK_FSS_INDEXING_QUEUE))
      2x: PLAN (WFA NATURAL)
      2x: PLAN (FSS_INDEXING_QUEUE INDEX (PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE
      6x: PLAN (DEMO_REQUESTS INDEX (FK_DEMO_REQUESTS_4))
      2x: PLAN (FSS_INDEXING_QUEUE INDEX (PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE))
      1x: PLAN (FSS_INDEXING_QUEUE INDEX (PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE))
      2x: PLAN (CRT NATURAL)
     67x: PLAN (SWI INDEX (SUBJECT_WEB_INSERTIONS_IDX1))
      1x: PLAN (FSS_INDEXING_QUEUE INDEX (PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE))
      6x: PLAN (L INDEX (WEB_USER_SESSION_LISTS_IDX1))
      9x: PLAN (ADV INDEX (SUBJECT_WEB_INSERTIONS_IDX1))
      2x: PLAN (KY INDEX (FK_KY_CITY_DISTRICTS_1))
      2x: PLAN (L INDEX (FK_WEB_USER_SESSION_LISTS_1))
      2x: PLAN (R INDEX (PK_REGIONS))
      3x: PLAN (L INDEX (PK_WEB_GET_INSERTIONS_LOG))
      3x: PLAN (INB NATURAL)
      2x: PLAN (S INDEX (PK_SUBJECTS))
    112x: PLAN (I INDEX (PK_INSERTIONS))(C INDEX (CONTACTS_IDX1))(SC INDEX (SELLER_CONTACTS_IDX1))(ISC INDEX (INSERTION_SELLER_CONTACTS_IDX2))
     75x: PLAN (OCA ORDER_REPAYMENT_SCHEDULES INDEX (FK_ORDER_REPAYMENT_SCHEDULES_3))(OCA ORDER_REPAYMENT_SCHEDULES INDEX (FK_ORDER_REPAYMENT_SCHEDULES_3))(OCA ORDER_REPAYMENT_SCHEDULES INDEX (FK_ORDER_REPAYMENT_SCHEDULES_3))(OCA ORDER_REPAYMENT_SCHEDULES INDEX (FK_ORDER_REPAYMENT_SCHEDULES_3))(OCA ORDER_REPAYMENT_SCHEDULES INDEX (FK_ORDER_REPAYMENT_SCHEDULES_3))(OCA ORDER_REPAYMENT_SCHEDULES INDEX (FK_ORDER_REPAYMENT_SCHEDULES_3))JOIN (O INDEX (FK_ORDERS_1), OCA O INDEX (PK_ORDERS))
  13337x: PLAN (AI INDEX (UNQ1_ARCHIVED_INSERTIONS))
     27x: PLAN (S INDEX (SELLERS_IDX1))
      1x: PLAN (FSS_INDEXING_QUEUE INDEX (PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE, PK_FSS_INDEXING_QUEUE))
     20x: PLAN (F INDEX (FIRMS_IDX1))
      4x: PLAN (SU INDEX (PK_SUBJECT_USERS))
     32x: PLAN (SRL INDEX (FK_SR_LOCALITIES_5, FK_SR_LOCALITIES_1))(I INDEX (INSERTIONS_IDX10))
      7x: PLAN (F INDEX (FK_WEB_FILTERS_SUBJECT_USERS))
      2x: PLAN (CAT NATURAL)


Used indices:
-------------

WEB_REMINDERS_IX_USER_ADV
PK_DISTRICTS
SUBJECT_USER_INSERTION_Q_IDX1
FK_SUBJECT_USER_INSERTION_Q_2
FK_SUBJECT_USER_INSERTION_Q_4
INSERTIONS_IDX10
UNQ1_CENTRAL_ACTIVITY_TYPES
FK_SR_LOCALITIES_5
UNQ1_ARCHIVED_INSERTIONS
FK_DEMO_REQUESTS_4
PK_INSERTIONS
FK_WEB_FILTERS_SUBJECT_USERS
SELLERS_IDX1
DISTRICTS_IDX2
INSERTION_ATTRIB_NAMES_IDX1
FK_KY_CITY_DISTRICTS_1
INSERTIONS_READ_QUEUE_FAIL_IDX1
PK_REGIONS
...

Unused indices:
---------------

INSERTIONS_READ_QUEUE_DONE_IDX3
INSERTIONS_READ_QUEUE_DONE_IDX2
INSERTIONS_READ_QUEUE_DONE_IDX1
INSERTIONS_READ_QUEUE_DONE_IDX7
INSERTIONS_READ_QUEUE_DONE_IDX6
INSERTIONS_READ_QUEUE_DONE_IDX5
INSERTIONS_READ_QUEUE_DONE_IDX4
ACTIVE_LINKS_ERRORS_IDX2
ACTIVE_LINKS_ERRORS_IDX1
...

Previous topic

fbtracex.py

Next topic

PowerConsole