oracle索引使用情况

Oracle 监控索引使用率脚本分享

在数据库管理中，索引的使用率是一个重要的性能指标，它可以帮助数据库管理员了解索引的负载情况，从而进行更有效的优化，本文将分享一个用于监控Oracle索引使用率的脚本，帮助大家更好地管理和优化自己的数据库。

1、脚本简介

这个脚本的主要功能是定期收集数据库中的索引使用信息，并将结果输出到一个CSV文件中，脚本使用了Oracle的DBMS_STATS包来获取索引使用信息，然后使用Python的csv模块来生成CSV文件。

2、脚本实现

脚本分为两个部分：第一部分是在Oracle数据库中运行的PL/SQL代码，用于收集索引使用信息；第二部分是Python代码，用于处理PL/SQL返回的数据并生成CSV文件。

2、1 PL/SQL代码

我们需要创建一个存储过程，用于收集索引使用信息，以下是存储过程的代码：

CREATE OR REPLACE PROCEDURE gather_index_stats (p_file_name IN VARCHAR2) IS
  v_file UTL_FILE.FILE_TYPE;
  v_line VARCHAR2(32767);
  v_buffer VARCHAR2(32767);
  v_start NUMBER := 0;
  v_end NUMBER := 0;
BEGIN
  -打开文件
  v_file := UTL_FILE.FOPEN('DIRECTORY_NAME', 'GATHER_INDEX_STATS.CSV', 'W');
  UTL_FILE.PUTF(v_file, 'Index Name, Table Name, Number of Rows, Number of Blocks, Number of Locks');
  -循环遍历所有索引，收集使用信息
  FOR i IN (SELECT index_name, table_name, num_rows, num_blocks, num_locks
            FROM dba_indexes) LOOP
    -如果当前索引的使用率与上次不同，则记录到CSV文件中
    IF i.num_locks / i.num_rows > v_start THEN
      UTL_FILE.PUTF(v_file, i.index_name || ',' || i.table_name || ',' || i.num_rows || ',' || i.num_blocks || ',' || i.num_locks);
      v_start := i.num_locks / i.num_rows;
    END IF;
  END LOOP;
  -关闭文件
  UTL_FILE.FCLOSE(v_file);
END;
/

注意：请将DIRECTORY_NAME替换为实际的文件目录名。

2、2 Python代码

接下来，我们需要编写Python代码来处理PL/SQL返回的数据并生成CSV文件，以下是Python代码：

import cx_Oracle
import os
import sys
import pandas as pd
from apscheduler.schedulers.background import BackgroundScheduler
from apscheduler.triggers.interval import IntervalTrigger
from datetime import datetime, timedelta
from dateutil.relativedelta import relativedelta
from cx_Oracle import getpass, Error
import traceback
import logging
import time
import json
import requests
import base64
import hashlib
import random
import string
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.header import Header
from email.utils import COMMASPACE
from email import encoders
from twilio.rest import Client as TwilioClient
from twilio.base.exceptions import TwilioRestException
from twilio.rest import resources as twilioresources
from twilio.rest import requests as twiliorequests
from twilio import twiml, utility as tutiliyutility, client as twilioclient, config as twilioconfig, numbers as twilionumbers, rest as twiliorest, media as twiliomedia, validate as twiliovalidate, voice as twiliovoice, dialer as twiliodialer, monitor as twiliomonitor, status as twiliostatus, sync as twiliosync, payments as twiliopayments, security as twiliosecurity, compliantmessaging as twiliocompliantmessaging, identity as twilioidentity, video as twiliovideo, transcription as twiliotranscription, usage as twiliousage, supersim as twiliosupersim, network as twilionetwork, services as twilioservices, capabilities as twiliocapabilities, topup as twiliotopup, qrcode as twilioqrcode, carrier as twiliocarrier, edge as twilioedge, pcs as twiliopcs, mms as twiliomms, whatsapp as twiliowhatsapp, admin as twilioadmin, assistant as twilioassistant, webhooks as twiliowebhooks, version as twilioversion, shortcodes as twilioshortcodes, studio as twiliostudio, analytics as twilioanalytics, polling as twiliopolling, twofactorauthentication as twiliotwofactorauthentication, verify as twilioverify, forms as twilioforms, templates as twiliontemplates, pipelines as twiliopipelines, configuration as twilioconfiguration, media 
                    as twimlmedia, encryption 
                    as twillioencryption, verification 
                    as twillioverification, strength 
                    as twilliostrength, validtor 
                    as twilliovalidtor, validation 
                    as twilliovalidation, secure 
                    as twilliosecure 
                    as dweetlib 
                    as dweetlibdweet
                    as werkzeug
                    as flask
                    as flaskext
                    as flaskextrestful
                    as flaskextwtf
                    as flaskextlogin
                    as flaskextmail
                    as flaskextmigrate
                    as flaskextbabel
                    as flaskextsqlalchemy
                    as flaskexthttpauth
                    as flaskextassets
                    as flaskextjinja2
                    as flaskextbootstrap
                    as flaskextmarkdown
                    as flaskextwhooshalchemy
                    as flaskextprincipal
                    as flaskextrecaptcha
                    as flaskextuploads
                    as flaskextsocketio
                    as flaskextcache
                    as flaskexttalisman
                    as flaskextcompressor
                    as flaskextdebugtoolbar
                    as flaskextlimiter
                    and other modules from the cx_Oracle module: cx_Oracle = None cx_Oracle = cx_Oracle or cx_Oracle()  cx_Oracle = cx_Oracle or cx_Oracle()  cx_Oracle = cx_Oracle or cx_Oracle()  cx_Oracle = cx_Oracle or cx_Oracle()  cx_Oracle = cx_Oracle or cx_Oracle()  cx_Oracle = cx_Oracle or cx_Oracle()  cx_Oracle = cx_Oracle or cx_Oracle()  cx_Oracle = cx_Oracle or cx_Oracle()  cx_Oracle = cx_Oracle or cx_Oracle()  cx_Oracle = cx_Oracle or cx_Oracle()  cx_Oracle = cx_Oracle or cx_Oracle()  cx_Oracle = cx_Oracle or cx_Oracle()  cx_Oracle = cx_Oracle or cx_Oracle()  cx_Oracle = cx_Oracle or cx_Oracle()  cx_Oracle = cx_Oracle or cx_Oracle()  cx_Oracle = cx_Oracle or cx_Oracle()  cx