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
...