shilpisingh-oracle/SQL-Optimization-RAG

Oracle SQL/PLSQL Query Optimization Tool

Overview

This project provides a Gradio-based web UI to help analyze and optimize Oracle SQL and PL/SQL queries using the OpenAI API for recommendations. It connects to an Oracle database (using thin mode), retrieves explain plans and execution timings, and suggests rewritten queries that aim to reduce execution time without changing results.

Important Security Notes

• —Never store real credentials in the repository. Use environment variables or Hugging Face Spaces Secrets.
• —The app blocks potentially destructive SQL statements (DROP, TRUNCATE, DELETE without WHERE, etc.) but you should still use a read-only or development DB for analysis.

Quick Start (Local)

1. 1.Copy .env.example to .env and fill in credentials or set environment variables:
2. 2.OPENAIAPIKEY
3. 3.ORACLEDSN (e.g. host:port/servicename)
4. 4.ORACLE_USER
5. 5.ORACLE_PASSWORD
6. 6.Create a virtualenv and install requirements:

   python -m venv venv
   venv\Scripts\Activate.ps1
   pip install -r requirements.txt

1. 1.Run the Gradio app:

   python app.py

Deployment to Hugging Face Spaces

1. 1.Create a new Space, choose Gradio as the SDK.
2. 2.Push this repository to the Space's Git URL.
3. 3.In the Space settings, add Secrets: OPENAI_API_KEY, ORACLE_DSN, ORACLE_USER, ORACLE_PASSWORD.
4. 4.The Space will install packages from requirements.txt and run app.py automatically.

Notes:

• —Hugging Face may not allow direct outbound connections to arbitrary databases. If connecting to an external Oracle DB fails, consider using an SSH tunnel or a hosted DB accessible from HF.
• —Keep credentials in Secrets only.

Files

• —app.py - Gradio frontend.
• —db.py - Oracle connection helpers and plan retrieval.
• —optimizer.py - Query optimization logic.
• —utils.py - Utility functions (normalization, hashing, safety checks, etc.).
• —requirements.txt - Python dependencies.
• —sample.sql - Example SQL queries.
• —tests/ - Unit and integration tests.

License

MIT