Python oracle sql 비교: 두 판 사이의 차이

2025년 5월 20일 (화) 06:56 기준 최신판

import cx_Oracle
import os
import datetime

# DB 접속 정보
db1_config = {
    "user": "user1",
    "password": "pass1",
    "dsn": cx_Oracle.makedsn("host1", 1521, service_name="service1")
}

db2_config = {
    "user": "user2",
    "password": "pass2",
    "dsn": cx_Oracle.makedsn("host2", 1521, service_name="service2")
}

# 비교할 SQL
target_sql = "SELECT * FROM employees WHERE department_id = 10"

def get_plan(cursor, sql):
    cursor.execute(sql)

    cursor.execute("""
        SELECT sql_id FROM v$sql 
        WHERE sql_text = :sql AND rownum = 1
    """, {"sql": sql})
    sql_id_row = cursor.fetchone()
    
    if not sql_id_row:
        return ["SQL_ID not found."]
    
    sql_id = sql_id_row[0]

    cursor.execute("""
        SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id => :sql_id, format => 'ALLSTATS LAST'))
    """, {"sql_id": sql_id})
    return [row[0] for row in cursor.fetchall()]

def generate_html(plan1_lines, plan2_lines, output_path="compare_plan.html"):
    html_template = f"""
<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <title>Oracle SQL Plan Comparison</title>
    <link rel="stylesheet" href="https://cdn.datatables.net/1.13.6/css/jquery.dataTables.min.css">
    <style>
        table {{
            width: 100%;
            white-space: pre;
            font-family: monospace;
        }}
        td {{
            white-space: pre;
        }}
        .container {{
            display: flex;
            justify-content: space-around;
            gap: 20px;
        }}
        .table-wrapper {{
            flex: 1;
        }}
    </style>
</head>
<body>
    <h2>Oracle SQL Plan Comparison</h2>
    <div class="container">
        <div class="table-wrapper">
            <h3>DB1 Plan</h3>
            <table id="table1" class="display">
                <thead><tr><th>Plan Line</th></tr></thead>
                <tbody>
                    {"".join([f"<tr><td>{line}</td></tr>" for line in plan1_lines])}
                </tbody>
            </table>
        </div>
        <div class="table-wrapper">
            <h3>DB2 Plan</h3>
            <table id="table2" class="display">
                <thead><tr><th>Plan Line</th></tr></thead>
                <tbody>
                    {"".join([f"<tr><td>{line}</td></tr>" for line in plan2_lines])}
                </tbody>
            </table>
        </div>
    </div>

    <script src="https://code.jquery.com/jquery-3.7.1.min.js"></script>
    <script src="https://cdn.datatables.net/1.13.6/js/jquery.dataTables.min.js"></script>
    <script>
        $(document).ready(function() {{
            $('#table1').DataTable({{ paging: false, searching: false, info: false }});
            $('#table2').DataTable({{ paging: false, searching: false, info: false }});
        }});
    </script>
</body>
</html>
    """
    with open(output_path, "w", encoding="utf-8") as f:
        f.write(html_template)
    print(f"\nHTML 비교 결과가 '{output_path}'에 저장되었습니다.")

def compare_and_generate_html(sql):
    with cx_Oracle.connect(**db1_config) as conn1, cx_Oracle.connect(**db2_config) as conn2:
        with conn1.cursor() as cur1, conn2.cursor() as cur2:
            plan1 = get_plan(cur1, sql)
            plan2 = get_plan(cur2, sql)

            timestamp = datetime.datetime.now().strftime("%Y%m%d_%H%M%S")
            filename = f"sql_plan_comparison_{timestamp}.html"
            generate_html(plan1, plan2, filename)

if __name__ == "__main__":
    compare_and_generate_html(target_sql)


def generate_html(plan1_lines, plan2_lines, output_path="compare_plan.html"):
    max_len = max(len(plan1_lines), len(plan2_lines))
    plan1_lines += [''] * (max_len - len(plan1_lines))
    plan2_lines += [''] * (max_len - len(plan2_lines))

    table1_rows = []
    table2_rows = []

    for idx, (l1, l2) in enumerate(zip(plan1_lines, plan2_lines), start=1):
        is_diff = l1 != l2
        class_name = "diff" if is_diff else ""
        row1 = f'<tr class="{class_name}"><td>{idx}</td><td>{l1}</td></tr>'
        row2 = f'<tr class="{class_name}"><td>{idx}</td><td>{l2}</td></tr>'
        table1_rows.append(row1)
        table2_rows.append(row2)

    html_template = f"""
<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <title>Oracle SQL Plan Comparison</title>
    <link rel="stylesheet" href="https://cdn.datatables.net/1.13.6/css/jquery.dataTables.min.css">
    <style>
        body {{
            font-family: Arial, sans-serif;
        }}
        table {{
            width: 100%;
            white-space: pre;
            font-family: monospace;
        }}
        td {{
            white-space: pre;
        }}
        .container {{
            display: flex;
            justify-content: space-around;
            gap: 20px;
        }}
        .table-wrapper {{
            flex: 1;
        }}
        .diff td {{
            background-color: #fff3cd;
        }}
        .button-wrapper {{
            margin: 10px 0;
            text-align: center;
        }}
    </style>
</head>
<body>
    <h2>Oracle SQL Plan Comparison</h2>
    <div class="button-wrapper">
        <button onclick="toggleDiffs()">다른 부분만 보기 / 전체 보기</button>
    </div>
    <div class="container">
        <div class="table-wrapper">
            <h3>DB1 Plan</h3>
            <table id="table1" class="display">
                <thead><tr><th>#</th><th>Plan Line</th></tr></thead>
                <tbody>
                    {''.join(table1_rows)}
                </tbody>
            </table>
        </div>
        <div class="table-wrapper">
            <h3>DB2 Plan</h3>
            <table id="table2" class="display">
                <thead><tr><th>#</th><th>Plan Line</th></tr></thead>
                <tbody>
                    {''.join(table2_rows)}
                </tbody>
            </table>
        </div>
    </div>

    <script src="https://code.jquery.com/jquery-3.7.1.min.js"></script>
    <script src="https://cdn.datatables.net/1.13.6/js/jquery.dataTables.min.js"></script>
    <script>
        let table1, table2;
        let showingDiffsOnly = false;

        $(document).ready(function() {{
            table1 = $('#table1').DataTable({{ paging: false, searching: false, info: false }});
            table2 = $('#table2').DataTable({{ paging: false, searching: false, info: false }});
        }});

        function toggleDiffs() {{
            showingDiffsOnly = !showingDiffsOnly;
            const table1Rows = document.querySelectorAll('#table1 tbody tr');
            const table2Rows = document.querySelectorAll('#table2 tbody tr');

            table1Rows.forEach((row, i) => {{
                const isDiff = row.classList.contains('diff');
                row.style.display = showingDiffsOnly && !isDiff ? 'none' : '';
            }});

            table2Rows.forEach((row, i) => {{
                const isDiff = row.classList.contains('diff');
                row.style.display = showingDiffsOnly && !isDiff ? 'none' : '';
            }});
        }}
    </script>
</body>
</html>
    """

    with open(output_path, "w", encoding="utf-8") as f:
        f.write(html_template)

    print(f"\n[완료] 실행 계획 비교 결과가 '{output_path}'에 저장되었습니다.")

def generate_html(plan1_lines, plan2_lines, output_path="compare_plan.html"):
    max_len = max(len(plan1_lines), len(plan2_lines))
    plan1_lines += [''] * (max_len - len(plan1_lines))
    plan2_lines += [''] * (max_len - len(plan2_lines))

    table1_rows = []
    table2_rows = []

    for idx, (l1, l2) in enumerate(zip(plan1_lines, plan2_lines), start=1):
        is_diff = l1 != l2
        class_name = "diff" if is_diff else ""
        row1 = f'<tr class="{class_name}"><td>{idx}</td><td>{l1}</td></tr>'
        row2 = f'<tr class="{class_name}"><td>{idx}</td><td>{l2}</td></tr>'
        table1_rows.append(row1)
        table2_rows.append(row2)

    html_template = f"""
<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <title>Oracle SQL Plan Comparison</title>
    <link rel="stylesheet" href="https://cdn.datatables.net/1.13.6/css/jquery.dataTables.min.css">
    <style>
        body {{
            font-family: Arial, sans-serif;
        }}
        table {{
            width: 100%;
            white-space: pre;
            font-family: monospace;
        }}
        td {{
            white-space: pre;
        }}
        .container {{
            display: flex;
            justify-content: space-around;
            gap: 20px;
        }}
        .table-wrapper {{
            flex: 1;
        }}
        .diff td {{
            background-color: #fff3cd;
        }}
        .button-wrapper {{
            margin: 10px 0;
            text-align: center;
        }}
    </style>
</head>
<body>
    <h2>Oracle SQL Plan Comparison</h2>
    <div class="button-wrapper">
        <button onclick="toggleDiffs()">다른 부분만 보기 / 전체 보기</button>
    </div>
    <div class="container">
        <div class="table-wrapper">
            <h3>DB1 Plan</h3>
            <table id="table1" class="display">
                <thead><tr><th>#</th><th>Plan Line</th></tr></thead>
                <tbody>
                    {''.join(table1_rows)}
                </tbody>
            </table>
        </div>
        <div class="table-wrapper">
            <h3>DB2 Plan</h3>
            <table id="table2" class="display">
                <thead><tr><th>#</th><th>Plan Line</th></tr></thead>
                <tbody>
                    {''.join(table2_rows)}
                </tbody>
            </table>
        </div>
    </div>

    <script src="https://code.jquery.com/jquery-3.7.1.min.js"></script>
    <script src="https://cdn.datatables.net/1.13.6/js/jquery.dataTables.min.js"></script>
    <script>
        let table1, table2;
        let showingDiffsOnly = false;

        $(document).ready(function() {{
            table1 = $('#table1').DataTable({{ paging: false, searching: false, info: false }});
            table2 = $('#table2').DataTable({{ paging: false, searching: false, info: false }});
        }});

        function toggleDiffs() {{
            showingDiffsOnly = !showingDiffsOnly;
            const table1Rows = document.querySelectorAll('#table1 tbody tr');
            const table2Rows = document.querySelectorAll('#table2 tbody tr');

            table1Rows.forEach((row, i) => {{
                const isDiff = row.classList.contains('diff');
                row.style.display = showingDiffsOnly && !isDiff ? 'none' : '';
            }});

            table2Rows.forEach((row, i) => {{
                const isDiff = row.classList.contains('diff');
                row.style.display = showingDiffsOnly && !isDiff ? 'none' : '';
            }});
        }}
    </script>
</body>
</html>
    """

    with open(output_path, "w", encoding="utf-8") as f:
        f.write(html_template)

    print(f"\n[완료] 실행 계획 비교 결과가 '{output_path}'에 저장되었습니다.")

def get_plan(cursor, sql, binds):
    # SQL 실행 (바인드 포함)
    cursor.execute(sql, binds)

    # SQL_ID 가져오기
    cursor.execute("""
        SELECT sql_id FROM v$sql 
        WHERE sql_text LIKE :sql_text AND rownum = 1
    """, {"sql_text": sql.strip()[:100] + "%"})  # 일부 매칭 방식 보완
    sql_id_row = cursor.fetchone()

    if not sql_id_row:
        return ["SQL_ID not found."]
    
    sql_id = sql_id_row[0]

    # 실제 실행 계획 조회
    cursor.execute("""
        SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id => :sql_id, format => 'ALLSTATS LAST'))
    """, {"sql_id": sql_id})
    return [row[0] for row in cursor.fetchall()]

def compare_and_generate_html(sql, bind_values):
    with cx_Oracle.connect(**db1_config) as conn1, cx_Oracle.connect(**db2_config) as conn2:
        with conn1.cursor() as cur1, conn2.cursor() as cur2:
            plan1 = get_plan(cur1, sql, bind_values)
            plan2 = get_plan(cur2, sql, bind_values)

            timestamp = datetime.datetime.now().strftime("%Y%m%d_%H%M%S")
            filename = f"sql_plan_comparison_{timestamp}.html"
            generate_html(plan1, plan2, filename)

예제 실행

if __name__ == "__main__":
    # 예시: 바인드 변수 포함 SQL
    sql = "SELECT * FROM employees WHERE department_id = :dept_id"
    bind_values = { "dept_id": 10 }

    compare_and_generate_html(sql, bind_values)

Comments

@@ 408번째 줄: / 408번째 줄: @@
      compare_and_generate_html(sql, bind_values)
 </source>
+[[category:python]]