다른 명령
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)