Spaces:
Sleeping
Sleeping
| # === Imports === | |
| import os | |
| import oci | |
| import re | |
| import gradio as gr | |
| import openai | |
| from datetime import datetime | |
| from bs4 import BeautifulSoup | |
| from reportlab.platypus import SimpleDocTemplate, Paragraph, Spacer | |
| from reportlab.lib.styles import getSampleStyleSheet, ParagraphStyle | |
| from reportlab.lib.pagesizes import letter | |
| from reportlab.lib.enums import TA_CENTER | |
| from reportlab.lib import colors | |
| import tempfile | |
| # --- API Keys --- | |
| openai_api_key = os.environ.get("OPENAI_API_KEY") | |
| if not openai_api_key: | |
| raise ValueError("OPENAI_API_KEY environment variable is not set.") | |
| client = openai.OpenAI(api_key=openai_api_key) | |
| openrouter_key = os.environ.get("OPENROUTER") | |
| openrouter = openai.OpenAI(api_key=openrouter_key, base_url="https://openrouter.ai/api/v1") | |
| # --- OCI Object Storage: Explicit Fixed Configuration --- | |
| oci_config = { | |
| "user": os.environ.get("OCI_USER"), | |
| "tenancy": os.environ.get("OCI_TENANCY"), | |
| "fingerprint": os.environ.get("OCI_FINGERPRINT"), | |
| "region": os.environ.get("OCI_REGION"), | |
| "key_content": os.environ.get("OCI_PRIVATE_KEY") | |
| } | |
| namespace = os.environ.get("OCI_NAMESPACE") | |
| bucket_name = os.environ.get("OCI_BUCKET_NAME") | |
| try: | |
| object_storage = oci.object_storage.ObjectStorageClient(oci_config) | |
| except Exception as e: | |
| print("Failed to initialize OCI Object Storage client:", e) | |
| # --- Exadata Specs --- | |
| exadata_specs = { | |
| "X7": {"Quarter Rack": {"max_iops": 350000, "max_throughput": 25}, "Half Rack": {"max_iops": 700000, "max_throughput": 50}, "Full Rack": {"max_iops": 1400000, "max_throughput": 100}}, | |
| "X8": {"Quarter Rack": {"max_iops": 380000, "max_throughput": 28}, "Half Rack": {"max_iops": 760000, "max_throughput": 56}, "Full Rack": {"max_iops": 1520000, "max_throughput": 112}}, | |
| "X9": {"Quarter Rack": {"max_iops": 450000, "max_throughput": 30}, "Half Rack": {"max_iops": 900000, "max_throughput": 60}, "Full Rack": {"max_iops": 1800000, "max_throughput": 120}}, | |
| "X10": {"Quarter Rack": {"max_iops": 500000, "max_throughput": 35}, "Half Rack": {"max_iops": 1000000, "max_throughput": 70}, "Full Rack": {"max_iops": 2000000, "max_throughput": 140}}, | |
| "X11M": {"Quarter Rack": {"max_iops": 600000, "max_throughput": 40}, "Half Rack": {"max_iops": 1200000, "max_throughput": 80}, "Full Rack": {"max_iops": 2400000, "max_throughput": 160}}, | |
| } | |
| # --- Supported LLM Models --- | |
| supported_llms = { | |
| "gpt-3.5-turbo": "Fastest / Lowest Cost - General AWR Healthcheck", | |
| "gpt-4-turbo": "Balanced - Production Performance Analysis", | |
| "gpt-4o": "Deepest Analysis - Exadata, RAC, Smart Scan, Critical Issues", | |
| "gpt-4.1": "Great for quick coding and analysis", | |
| } | |
| # --- Utils --- | |
| def clean_awr_content(content): | |
| if "<html" in content.lower(): | |
| soup = BeautifulSoup(content, "html.parser") | |
| return soup.get_text() | |
| return content | |
| def awr_file_to_text(file_obj): | |
| if not file_obj: | |
| return "" | |
| filename = file_obj.name if hasattr(file_obj, "name") else str(file_obj) | |
| try: | |
| content = file_obj.read() if hasattr(file_obj, "read") else open(file_obj, "rb").read() | |
| except Exception: | |
| with open(file_obj, "rb") as f: | |
| content = f.read() | |
| try: | |
| text = content.decode() | |
| except Exception: | |
| text = content.decode("latin-1") | |
| return clean_awr_content(text) | |
| def upload_awr_file(file_obj): | |
| filename = os.path.basename(file_obj) | |
| with open(file_obj, "rb") as f: | |
| content = f.read() | |
| object_storage.put_object(namespace, bucket_name, filename, content) | |
| return f"\u2705 Uploaded {filename}" | |
| def list_awr_files(): | |
| try: | |
| objects = object_storage.list_objects(namespace, bucket_name) | |
| return [obj.name for obj in objects.data.objects if obj.name.endswith(".html") or obj.name.endswith(".txt")] | |
| except Exception as e: | |
| return [f"Error listing objects: {str(e)}"] | |
| def get_awr_file_text(filename): | |
| try: | |
| response = object_storage.get_object(namespace, bucket_name, filename) | |
| raw = response.data.content.decode() | |
| return clean_awr_content(raw) | |
| except Exception as e: | |
| return f"Error loading file: {str(e)}" | |
| def generate_pdf(analysis_text, health_text, rating_text, retry_status_text): | |
| temp_file = tempfile.NamedTemporaryFile(delete=False, suffix=".pdf") | |
| pdf_path = temp_file.name | |
| doc = SimpleDocTemplate(pdf_path, pagesize=letter) | |
| styles = getSampleStyleSheet() | |
| elements = [] | |
| header_style = ParagraphStyle(name="HeaderStyle", fontSize=16, alignment=TA_CENTER, textColor=colors.darkblue, spaceAfter=14) | |
| section_style = ParagraphStyle(name="SectionHeader", fontSize=14, textColor=colors.darkred, spaceAfter=8) | |
| body_style = ParagraphStyle(name="BodyStyle", fontSize=10, leading=14, spaceAfter=10) | |
| elements.append(Paragraph("Oracle AWR Analyzer Report", header_style)) | |
| elements.append(Spacer(1, 12)) | |
| sections = [ | |
| ("AWR Analysis", analysis_text), | |
| ("Health Agent Findings", health_text), | |
| ("Rater Output", rating_text), | |
| ("Retry Status", retry_status_text) | |
| ] | |
| for title, content in sections: | |
| elements.append(Paragraph(title, section_style)) | |
| elements.append(Paragraph(content.replace("\n", "<br/>"), body_style)) | |
| elements.append(Spacer(1, 12)) | |
| doc.build(elements) | |
| return pdf_path | |
| def compare_awrs(file_list, llm_model): | |
| if not file_list: | |
| return "No files selected." | |
| combined_text = "" | |
| for fname in file_list: | |
| content = get_awr_file_text(fname) | |
| combined_text += f"\n=== AWR: {fname} ===\n{content[:3000]}...\n" | |
| prompt = f"""You are a senior Oracle performance engineer. You will compare multiple AWR reports and highlight: | |
| - Key differences in workload or system behavior | |
| - Major trends or anomalies | |
| - Which report shows better performance and why | |
| - Exadata-specific metrics like Smart Scan, Flash I/O | |
| - Suggestions to unify or improve system behavior | |
| AWR Reports: | |
| {combined_text} | |
| """ | |
| response = client.chat.completions.create( | |
| model=llm_model, | |
| messages=[ | |
| {"role": "system", "content": "You are a comparative AWR analysis expert."}, | |
| {"role": "user", "content": prompt} | |
| ] | |
| ) | |
| return response.choices[0].message.content.strip() | |
| def toggle_visibility(mode): | |
| return gr.update(visible=mode), gr.update(visible=mode) | |
| # === AGENTS === | |
| class CriticalAnalyzerAgent: | |
| def analyze(self, content, performance_test_mode, exadata_model, rack_size, llm_model): | |
| cleaned_content = clean_awr_content(content) | |
| if len(cleaned_content) > 128000: | |
| cleaned_content = cleaned_content[:128000] + "\n\n[TRUNCATED]..." | |
| prompt = f"""You are an expert Oracle DBA performance analyst specialized in AWR + Exadata. | |
| Please perform advanced analysis on the following report: | |
| ======== AWR REPORT START ======== | |
| {cleaned_content} | |
| ======== AWR REPORT END ======== | |
| Required Output: | |
| - Performance Summary (with metric values) | |
| - Detailed Bottlenecks + Risks (quantified) | |
| - Forecast + Predictions | |
| - Monitoring Recommendations | |
| - Exadata Statistics (IO, Flash Cache, Smart Scan) | |
| - Recommended Next Steps to Bridge Gaps | |
| """ | |
| if performance_test_mode and exadata_model and rack_size: | |
| specs = exadata_specs.get(exadata_model, {}).get(rack_size, {}) | |
| if specs: | |
| prompt += f""" | |
| This was a PERFORMANCE TEST on Oracle Exadata {exadata_model} {rack_size}. | |
| Theoretical Max: | |
| - IOPS: {specs['max_iops']} | |
| - Throughput: {specs['max_throughput']} GB/s | |
| Compare observed vs theoretical. Recommend actions to close the performance gap. | |
| """ | |
| response = client.chat.completions.create( | |
| model=llm_model, | |
| messages=[ | |
| {"role": "system", "content": "You are an expert Oracle DBA."}, | |
| {"role": "user", "content": prompt} | |
| ] | |
| ) | |
| return response.choices[0].message.content.strip() | |
| class HealthAgent: | |
| def check_health(self, content, llm_model): | |
| cleaned_content = clean_awr_content(content) | |
| if len(cleaned_content) > 128000: | |
| cleaned_content = cleaned_content[:128000] + "\n\n[TRUNCATED]..." | |
| prompt = f"""You are the Oracle AWR Health Analysis Agent. | |
| Your primary responsibility is to detect and report ANY and ALL database health risks, alerts, warnings, or failures in the AWR report. | |
| You MUST: | |
| - Identify all issues marked as CRITICAL, WARNING, ALERT, FAILED, OFFLINE, CONFINED, DROPPED, or ERROR. | |
| - Never omit or generalize. If something appears important, call it out. | |
| - Classify each issue into: 🚨 CRITICAL / ⚠️ WARNING / ✅ INFO | |
| - For CRITICAL and WARNING, provide suggested actions or considerations. | |
| - Always confirm at the end if no CRITICAL or WARNING issues were found. | |
| Special Attention Areas: | |
| - Flash Cache or Flash Disk Failures | |
| - I/O Subsystem stalls or errors | |
| - ASM/Grid Disk issues | |
| - Smart Scan failures | |
| - Redo Log issues | |
| - RAC Interconnect issues | |
| AWR CONTENT: | |
| {cleaned_content} | |
| """ | |
| response = client.chat.completions.create( | |
| model=llm_model, | |
| messages=[ | |
| {"role": "system", "content": "You are the strict Oracle AWR Health Analysis Agent."}, | |
| {"role": "user", "content": prompt} | |
| ] | |
| ) | |
| return response.choices[0].message.content.strip() | |
| class RaterAgent: | |
| def rate(self, content): | |
| prompt = f"Rate the following analysis from 1-5 stars and explain:\n\n{content}" | |
| response = openrouter.chat.completions.create( | |
| model="mistralai/Mixtral-8x7B-Instruct", | |
| messages=[{"role": "user", "content": prompt}] | |
| ) | |
| return response.choices[0].message.content.strip() | |
| # === MAIN AWR PROCESS === | |
| def process_awr(awr_text, threshold, performance_test_mode, exadata_model, rack_size, llm_model): | |
| analyzer = CriticalAnalyzerAgent() | |
| health = HealthAgent() | |
| rater = RaterAgent() | |
| if not awr_text.strip(): | |
| return "No AWR text provided", "", "", "" | |
| analysis = analyzer.analyze(awr_text, performance_test_mode, exadata_model, rack_size, llm_model) | |
| health_status = health.check_health(awr_text, llm_model) | |
| rating_text = rater.rate(analysis) | |
| stars = 0 | |
| match = re.search(r"(\d+)", rating_text) | |
| if match: | |
| stars = int(match.group(1)) | |
| retry_status = "✅ Accepted" | |
| if stars < threshold: | |
| analysis = analyzer.analyze(awr_text, performance_test_mode, exadata_model, rack_size, llm_model) | |
| rating_text = rater.rate(analysis) | |
| retry_status = "✅ Retry Occurred" | |
| return analysis, health_status, rating_text, retry_status | |
| # === Gradio UI === | |
| with gr.Blocks() as demo: | |
| with gr.Tab("Manual AWR Analysis"): | |
| gr.Markdown("# Multi-Agent Oracle AWR Analyzer (Version 3.1)") | |
| awr_file = gr.File(label="Upload AWR Report (.html or .txt)", file_types=[".html", ".txt"]) | |
| awr_text = gr.Textbox(label="AWR Report (pasted or loaded)", lines=30) | |
| awr_file.upload(awr_file_to_text, inputs=awr_file, outputs=awr_text) | |
| threshold = gr.Slider(0, 5, value=3, step=1, label="Correctness Threshold (Stars)") | |
| performance_test_mode = gr.Checkbox(label="Performance Test Mode") | |
| exadata_model = gr.Dropdown(choices=list(exadata_specs.keys()), label="Exadata Model", visible=False) | |
| rack_size = gr.Dropdown(choices=["Quarter Rack", "Half Rack", "Full Rack"], label="Rack Size", visible=False) | |
| llm_selector = gr.Dropdown(choices=list(supported_llms.keys()), value="gpt-4.1", label="LLM Model") | |
| performance_test_mode.change(toggle_visibility, inputs=performance_test_mode, outputs=[exadata_model, rack_size]) | |
| analyze_btn = gr.Button("Analyze AWR Report") | |
| output = gr.Textbox(label="AWR Analysis", lines=20) | |
| health = gr.Textbox(label="Health Agent Findings", lines=10) | |
| rating = gr.Textbox(label="Rater", lines=3) | |
| retry_status = gr.Textbox(label="Retry Status") | |
| analyze_btn.click(process_awr, inputs=[awr_text, threshold, performance_test_mode, exadata_model, rack_size, llm_selector], outputs=[output, health, rating, retry_status]) | |
| pdf_button = gr.Button("📄 Generate PDF") | |
| pdf_file = gr.File(label="Download PDF", type="filepath") # ✅ fixed | |
| pdf_button.click( | |
| fn=generate_pdf, | |
| inputs=[output, health, rating, retry_status], | |
| outputs=pdf_file | |
| ) | |
| with gr.Tab("Compare AWRs"): | |
| upload_file = gr.File(label="Upload AWR Report", file_types=[".html", ".txt"]) | |
| upload_status = gr.Textbox(label="Upload Status") | |
| upload_file.upload(fn=upload_awr_file, inputs=upload_file, outputs=upload_status) | |
| refresh_button = gr.Button("🔃 Refresh File List") | |
| file_multiselect = gr.Dropdown(choices=[], label="Select AWR Files", multiselect=True) | |
| refresh_button.click(fn=lambda: gr.update(choices=list_awr_files()), outputs=file_multiselect) | |
| llm_compare = gr.Dropdown(choices=list(supported_llms.keys()), value="gpt-4.1", label="LLM Model for Comparison") | |
| compare_output = gr.Textbox(label="Comparison Output", lines=20) | |
| compare_btn = gr.Button("Compare Selected AWRs") | |
| compare_btn.click(fn=compare_awrs, inputs=[file_multiselect, llm_compare], outputs=compare_output) | |
| # PDF Export for Compare tab | |
| pdf_compare_button = gr.Button("📄 Generate Comparison PDF") | |
| pdf_compare_file = gr.File(label="Download Comparison PDF", type="filepath") | |
| pdf_compare_button.click( | |
| fn=lambda comparison_text: generate_pdf(comparison_text, "", "", ""), | |
| inputs=[compare_output], | |
| outputs=pdf_compare_file | |
| ) | |
| if __name__ == "__main__": | |
| demo.launch(debug=True) | |