Python oracle sql 비교 - 편집 역사

2025년 5월 19일 (월) 21:56에 Devcafe님의 편집

2025-05-19T21:56:35Z

← 이전 판		2025년 5월 20일 (화) 06:56 판
408번째 줄:		408번째 줄:
	compare_and_generate_html(sql, bind_values)		compare_and_generate_html(sql, bind_values)
	</source>		</source>

			[[category:python]]

2025년 4월 30일 (수) 04:24에 Devcafe님의 편집

2025-04-30T04:24:54Z

@@ 379번째 줄: / 379번째 줄: @@
      """, {"sql_id": sql_id})
      return [row[0] for row in cursor.fetchall()]
 </source>

2025년 4월 30일 (수) 04:23에 Devcafe님의 편집

2025-04-30T04:23:47Z

← 이전 판		2025년 4월 30일 (수) 13:23 판
354번째 줄:		354번째 줄:
	print(f"\n[완료] 실행 계획 비교 결과가 '{output_path}'에 저장되었습니다.")		print(f"\n[완료] 실행 계획 비교 결과가 '{output_path}'에 저장되었습니다.")

			</source>

			----
			<source lang=python>
			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()]
	</source>		</source>

2025년 4월 30일 (수) 04:23에 Devcafe님의 편집

2025-04-30T04:23:01Z

← 이전 판		2025년 4월 30일 (수) 13:23 판
236번째 줄:		236번째 줄:

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

			----
			<source lang=python>
			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}'에 저장되었습니다.")

	</source>		</source>

2025년 4월 30일 (수) 04:21에 Devcafe님의 편집

2025-04-30T04:21:59Z

@@ 1번째 줄: / 1번째 줄: @@
 <source lang=python>
 import cx_Oracle
 # DB 접속 정보
@@ 19번째 줄: / 21번째 줄: @@
 def get_plan(cursor, sql):
      cursor.execute(sql)
      cursor.execute("""
          SELECT sql_id FROM v$sql
@@ 30번째 줄: / 30번째 줄: @@
      if not sql_id_row:
-         return "SQL_ID not found."
+         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 "\n".join([row[0] for row in cursor.fetchall()])
+     return [row[0] for row in cursor.fetchall()]
-def compare_plans(sql):
+def generate_html(plan1_lines, plan2_lines, output_path="compare_plan.html"):
      with cx_Oracle.connect(**db1_config) as conn1, cx_Oracle.connect(**db2_config) as conn2:
          with conn1.cursor() as cur1, conn2.cursor() as cur2:
@@ 46번째 줄: / 110번째 줄: @@
              plan2 = get_plan(cur2, sql)
-             print("===== PLAN from DB1 =====")
+             timestamp = datetime.datetime.now().strftime("%Y%m%d_%H%M%S")
-            print(plan1)
+             filename = f"sql_plan_comparison_{timestamp}.html"
-            print("\n===== PLAN from DB2 =====")
+             generate_html(plan1, plan2, filename)
 if __name__ == "__main__":
-     compare_plans(target_sql)
+     compare_and_generate_html(target_sql)
 </source>

2025년 4월 30일 (수) 04:21에 Devcafe님의 편집

2025-04-30T04:21:31Z

← 이전 판		2025년 4월 30일 (수) 13:21 판
60번째 줄:		60번째 줄:
	compare_plans(target_sql)		compare_plans(target_sql)
	</source>		</source>


			----

	<source lang=python>		<source lang=python>

2025년 4월 30일 (수) 04:20에 Devcafe님의 편집

2025-04-30T04:20:08Z

@@ 1번째 줄: / 1번째 줄: @@
 <source lang=python>
 def generate_html(plan1_lines, plan2_lines, output_path="compare_plan.html"):
      max_len = max(len(plan1_lines), len(plan2_lines))

Devcafe: 새 문서: 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'
2025-04-30T04:18:42Z

새 문서: <source lang=python> 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...

새 문서
<source lang=python>
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}'에 저장되었습니다.")
</source>