반응형
SQL을 분석하는 방법 중 하나입니다.
SQLHC와 SQLT입니다.
SQLHC
SQLHC(SQL Tuning Health-Check) script는 Oracle에서 개발한 도구로써, 단일 SQL문이 실행되는 것에 대한 체크를 하여 CBO(Cost Based Opimizer) 통계, 스키마 객체 메타데이터, 파라미터 및 단일 SQL의 성능에 영향을 미칠 수 있는 기타요소럴 검사합니다. SQLHC는 무료로 사용가능합니다. (Oracle Sr에서 확인 Doc ID 1366133.1)
zip 파일을 서버에 업로드하여 zip 파일에 있는 sqlhc.sql 파일을 수행하여 sql의 분석 파일들을 수집하는 방법입니다.
위의 파일의 압축을 풀어보면 아래와 같습니다.
$ ls -al
total 2200
drwxr-xr-x 3 oracle dba 4096 Sep 23 15:14 .
drwxr-xr-x 6 oracle dba 4096 Sep 23 14:13 ..
-rw-r--r-- 1 oracle dba 48747 Apr 6 13:05 sqldx.sql
-rw-r--r-- 1 oracle dba 306587 Apr 6 13:05 sqlhc.sql
-rw-r--r-- 1 oracle dba 3611 Apr 6 13:05 sqlhc_sta.sql
-rw-r--r-- 1 oracle dba 2082 Apr 6 13:05 sqlhc_tcb.sql
-rw-r--r-- 1 oracle dba 60394 Apr 6 13:05 util_planx.sql
drwxr-xr-x 2 oracle dba 256 Apr 6 13:05 utl이 중에서 sqlhc.sql 파일을 이용합니다.
사용방법
1.sqlplus / as sysdba 접속
2.START sqlhc.sql "T" [SQL_ID]
Oracle Pack License (Tuning, Diagnostics or None) [T|D|N] (required)
If site has both Tuning and Diagnostics licenses then specify T (Oracle Tuning pack includes Oracle Diagnostics)
Tuning and Diagnostics 라이센스가 모두 있을경우 T로 설정하면됩니다.
3.결과로 만들어지는 파일 확인
사용방법은 간단합니다. 하지만 수행시간은 좀 오래걸렸습니다.
# sqlplus / as sysdba
SQL> START sqlhc.sql "T" 05m7559uyv2vn
...생략
###
2022-09-23/15:14:20 DBA_HIST_SNAPSHOT
2022-09-23/15:14:20 GV$PARAMETER2
adding: sqlhc_20220923_1444_05m7559uyv2vn_8_sqldx_global_DBA_HIST_SNAPSHOT.csv (deflated 96%)
adding: sqlhc_20220923_1444_05m7559uyv2vn_8_sqldx_global_GVsPARAMETER2.csv (deflated 99%)
Archive: sqlhc_20220923_1444_05m7559uyv2vn_8_sqldx_global_csv.zip
Length Date Time Name
--------- ---------- ----- ----
210800 09-23-2022 15:14 sqlhc_20220923_1444_05m7559uyv2vn_8_sqldx_global_DBA_HIST_SNAPSHOT.csv
7519512 09-23-2022 15:14 sqlhc_20220923_1444_05m7559uyv2vn_8_sqldx_global_GVsPARAMETER2.csv
--------- -------
7730312 2 files
adding: sqlhc_20220923_1444_05m7559uyv2vn_8_sqldx_global_csv.zip (stored 0%)
Archive: sqlhc_20220923_1444_05m7559uyv2vn_8_sqldx.zip
Length Date Time Name
--------- ---------- ----- ----
183346 09-23-2022 15:14 sqlhc_20220923_1444_05m7559uyv2vn_8_sqldx_05m7559uyv2vn_csv.zip
174063 09-23-2022 15:14 sqlhc_20220923_1444_05m7559uyv2vn_8_sqldx_10012789688559492535_force_csv.zip
40731 09-23-2022 15:14 sqlhc_20220923_1444_05m7559uyv2vn_8_sqldx_table_csv.zip
68788 09-23-2022 15:14 sqlhc_20220923_1444_05m7559uyv2vn_8_sqldx_global_csv.zip
--------- -------
466928 4 files
sqlhc_20220923_1444_05m7559uyv2vn_8_sqldx_*.zip files have been created.
adding: sqlhc_20220923_1444_05m7559uyv2vn_8_sqldx_05m7559uyv2vn_driver.sql (deflated 93%)
adding: sqldx.log (deflated 59%)
Archive: sqlhc_20220923_1444_05m7559uyv2vn_8_sqldx_05m7559uyv2vn_log.zip
Length Date Time Name
--------- ---------- ----- ----
65689 09-23-2022 15:14 sqlhc_20220923_1444_05m7559uyv2vn_8_sqldx_05m7559uyv2vn_driver.sql
3327 09-23-2022 15:12 sqldx.log
--------- -------
69016 2 files
adding: sqlhc_20220923_1444_05m7559uyv2vn_8_sqldx_05m7559uyv2vn_log.zip (stored 0%)
SQLDX files have been created.
Archive: sqlhc_20220923_1444_05m7559uyv2vn_8_sqldx.zip
Length Date Time Name
--------- ---------- ----- ----
183346 09-23-2022 15:14 sqlhc_20220923_1444_05m7559uyv2vn_8_sqldx_05m7559uyv2vn_csv.zip
174063 09-23-2022 15:14 sqlhc_20220923_1444_05m7559uyv2vn_8_sqldx_10012789688559492535_force_csv.zip
40731 09-23-2022 15:14 sqlhc_20220923_1444_05m7559uyv2vn_8_sqldx_table_csv.zip
68788 09-23-2022 15:14 sqlhc_20220923_1444_05m7559uyv2vn_8_sqldx_global_csv.zip
6328 09-23-2022 15:14 sqlhc_20220923_1444_05m7559uyv2vn_8_sqldx_05m7559uyv2vn_log.zip
--------- -------
473256 5 files
adding: sqlhc_20220923_1444_05m7559uyv2vn_8_sqldx.zip (stored 0%)
SQLDX files have been added to sqlhc_20220923_1444_05m7559uyv2vn.zip
Archive: sqlhc_20220923_1444_05m7559uyv2vn.zip
Length Date Time Name
--------- ---------- ----- ----
8806 09-23-2022 15:09 sqlhc_20220923_1444_05m7559uyv2vn_1_health_check.html
160812 09-23-2022 15:12 sqlhc_20220923_1444_05m7559uyv2vn_2_diagnostics.html
6050 09-23-2022 15:12 sqlhc_20220923_1444_05m7559uyv2vn_3_execution_plans.html
17727 09-23-2022 15:12 sqlhc_20220923_1444_05m7559uyv2vn_4_sql_detail.html
29172 09-23-2022 15:12 sqlhc_20220923_1444_05m7559uyv2vn_9_log.zip
11552 09-23-2022 15:12 sqlhc_20220923_1444_05m7559uyv2vn_5_sql_monitor.zip
354 09-23-2022 15:12 sqlhc_20220923_1444_05m7559uyv2vn_10_sql_tuning_advisor.out
476 09-23-2022 15:12 sqlhc_20220923_1444_05m7559uyv2vn_11_tcb.zip
474544 09-23-2022 15:14 sqlhc_20220923_1444_05m7559uyv2vn_8_sqldx.zip
--------- -------
709493 9 files
생성된 파일 확인
$ unzip sqlhc_20220923_1444_05m7559uyv2vn.zip
Archive: sqlhc_20220923_1444_05m7559uyv2vn.zip
inflating: sqlhc_20220923_1444_05m7559uyv2vn_1_health_check.html
inflating: sqlhc_20220923_1444_05m7559uyv2vn_2_diagnostics.html
inflating: sqlhc_20220923_1444_05m7559uyv2vn_3_execution_plans.html
inflating: sqlhc_20220923_1444_05m7559uyv2vn_4_sql_detail.html
extracting: sqlhc_20220923_1444_05m7559uyv2vn_9_log.zip
extracting: sqlhc_20220923_1444_05m7559uyv2vn_5_sql_monitor.zip
inflating: sqlhc_20220923_1444_05m7559uyv2vn_10_sql_tuning_advisor.out
extracting: sqlhc_20220923_1444_05m7559uyv2vn_11_tcb.zip
extracting: sqlhc_20220923_1444_05m7559uyv2vn_8_sqldx.zip
SQLT
SQLT는 유저를 생성해야 합니다. SQLTXPLAIN,SQLTXADMIN 유저가 생성됩니다.
sqlt_10g_11g_12c_18c_19c_5th_June_2020.zip
1.60MB
0.이전버전이 있을 경우 uninstall(없으면 넘어감)
$ cd sqlt/install
$ sqlplus / as sysdba
SQL> START sqdrop.sql1.install
설치할 때 물어보는 항목에 대해서 정리하겠습니다.
1.
Optional Connect Identifier (ie: @PROD):
PDB 구조의 경우 구분하는 identifier를 입력합니다. 없는경우 enter
2.
Password for user SQLTXPLAIN:
Re-enter password:
SQLTXPLAIN 유저의 비밀번호 입력합니다.
3.
Do you want to see the free space of each tablespace [YES]
or is it ok just to show the list of tablespace [NO]?
Type YES or NO [Default NO]: YES
Default tablespace와 TEMP tablespace를 설정하기에 앞서 목록을 보여주는 항목입니다. YES 선택 시 free space가 표시되고 NO 선택 시 테이블스페이스 이름만 나옵니다.
4.
Default tablespace [UNKNOWN]: USERS
default tablespace를 설정하는 부분입니다.
5.
Temporary tablespace [UNKNOWN]: TEMP
TEMP tablespace를 설정하는 부분입니다.
6.
Main application user of SQLT: YSBAE
분석할 SQL을 소유한 유저를 입력해줍니다.
6번까지 입력하면 설치는 완료됩니다.
$ sqlplus / as sysdba
SQL> START sqcreate.sql
Optional Connect Identifier (ie: @PROD):
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Define SQLTXPLAIN password (hidden and case sensitive).
The system has a password complexity function defined, make sure to provide a valid password.
Password for user SQLTXPLAIN:
Re-enter password:
The next step is to choose the tablespaces to be used by SQLTXPLAIN
The Tablespace name is case sensitive.
Do you want to see the free space of each tablespace [YES]
or is it ok just to show the list of tablespace [NO]?
Type YES or NO [Default NO]: YES
... please wait
TABLESPACE FREE_SPACE_MB
------------------------------ -------------
MKPARK_TEST 99
TS_DBMON 10163
MY_DATA 24132
USERS 29295
TS_DATA01 29828
OLD_REORG 30719
6 rows selected.
Specify PERMANENT tablespace to be used by SQLTXPLAIN.
Tablespace name is case sensitive.
Default tablespace [UNKNOWN]: USERS
PL/SQL procedure successfully completed.
... please wait
TABLESPACE
------------------------------
TEMP
TEMP_BS
TEMP_YS
Specify TEMPORARY tablespace to be used by SQLTXPLAIN.
Tablespace name is case sensitive.
Temporary tablespace [UNKNOWN]: TEMP
PL/SQL procedure successfully completed.
The main application user of SQLT is the schema
owner that issued the SQL to be analyzed.
For example, on an EBS application you would
enter APPS.
You will not be asked to enter its password.
To add more SQLT users after this installation
is completed simply grant them the SQLT_USER_ROLE
role.
Main application user of SQLT: YSBAE
PL/SQL procedure successfully completed.
SQLT can make extensive use of licensed features
provided by the Oracle Diagnostic and the Oracle
Tuning Packs, including SQL Tuning Advisor (STA),
SQL Monitoring and Automatic Workload Repository
(AWR).
To enable or disable access to these features
from the SQLT tool enter one of the following
values when asked:
"T" if you have license for Diagnostic and Tuning
"D" if you have license only for Oracle Diagnostic
"N" if you do not have these two licenses
Oracle Pack license [T]: T
...생략
사용방법
1.cd /sqlt/run
해당 디렉토리로 이동합니다.
2.분석할 SQL을 소유한 유저로 접속합니다,
$sqlplus ysbae/[비밀번호]
3.
START sqltxtract.sql [SQL_ID] [SQLTXPLAIN의 비밀번호]
SQL_ID 를 알고 있는경우 XTRACT
SQL_ID 를 모르는 경우 XECUTE 를 사용합니다.
$sqlplus ysbae/비밀번호
SQL> START sqltxtract.sql 05m7559uyv2vn 비밀번호
Parameter 1:
SQL_ID or HASH_VALUE of the SQL to be extracted (required)
Describe the characteristic of this run
"F[AST]" if you have a FAST run
"S[LOW]" if you have a SLOW run (default)
"H[ASH]" if this is a run with a HASH JOIN
"N[L]" if this is a run with a NESTED LOOP
"C[OLUMN HISTOGRAM]" if this is a run with a Column Historgram in place
SQL Description [S]:
PL/SQL procedure successfully completed.
...생략
Archive: sqlt_s87054_sqldx.zip
Length Date Time Name
--------- ---------- ----- ----
164365 09-23-2022 15:25 sqlt_s87054_sqldx_05m7559uyv2vn_csv.zip
163905 09-23-2022 15:25 sqlt_s87054_sqldx_10012789688559492535_force_csv.zip
36184 09-23-2022 15:25 sqlt_s87054_sqldx_table_csv.zip
68484 09-23-2022 15:25 sqlt_s87054_sqldx_global_csv.zip
6100 09-23-2022 15:25 sqlt_s87054_sqldx_05m7559uyv2vn_log.zip
--------- -------
439038 5 files
adding: sqlt_s87054_sqldx.zip (stored 0%)
PL/SQL procedure successfully completed.
UNIQUE_TIMESTAMP_ID
--------------------------------
sqlt_20220923_1525
/usr/bin/ksh: RENAME: not found
#####
The SQLT has collected information and place it in a repository in the database, exported it and zip it.
The collected info can be purged from the database using the following file :
... getting sqlt_s87054_purge.sql out of sqlt repository ...
SQLTXTRACT completed.
SQL>
생성된 파일을 확인해보겠습니다.
$ ls -al
total 22720
drwxr-xr-x 2 oracle dba 4096 Sep 23 17:51 .
drwxr-xr-x 3 oracle dba 8192 Sep 23 17:50 ..
-rw-r--r-- 1 oracle dba 3325957 Sep 23 17:51 sqlt_20220923_1525_05m7559uyv2vn_S.zip
-rw-r--r-- 1 oracle dba 471800 Sep 23 17:51 sqlt_s87053_sqldx_05m7559uyv2vn_DBA_HIST_ACTIVE_SESS_HISTORY.csv
-rw-r--r-- 1 oracle dba 20455 Sep 23 17:51 sqlt_s87053_sqldx_05m7559uyv2vn_DBA_HIST_SQLTEXT.csv
-rw-r--r-- 1 oracle dba 356818 Sep 23 17:51 sqlt_s87053_sqldx_05m7559uyv2vn_DBA_HIST_SQL_PLAN.csv
-rw-r--r-- 1 oracle dba 389940 Sep 23 17:51 sqlt_s87053_sqldx_05m7559uyv2vn_DBA_SQLSET_PLANS.csv
-rw-r--r-- 1 oracle dba 16590 Sep 23 17:51 sqlt_s87053_sqldx_05m7559uyv2vn_DBA_SQLSET_STATEMENTS.csv
-rw-r--r-- 1 oracle dba 4481460 Sep 23 17:51 sqlt_s87053_sqldx_05m7559uyv2vn_GVsACTIVE_SESSION_HISTORY.csv
-rw-r--r-- 1 oracle dba 4935 Sep 23 17:51 sqlt_s87053_sqldx_05m7559uyv2vn_GVsSQLSTATS.csv
-rw-r--r-- 1 oracle dba 4683 Sep 23 17:51 sqlt_s87053_sqldx_05m7559uyv2vn_GVsSQLSTATS_PLAN_HASH.csv
-rw-r--r-- 1 oracle dba 680 Sep 23 17:51 sqlt_s87053_sqldx_05m7559uyv2vn_GVsSQLTEXT.csv
-rw-r--r-- 1 oracle dba 680 Sep 23 17:51 sqlt_s87053_sqldx_05m7559uyv2vn_GVsSQLTEXT_WITH_NEWLINES.csv
-rw-r--r-- 1 oracle dba 31809 Sep 23 17:51 sqlt_s87053_sqldx_05m7559uyv2vn_GVsSQL_MONITOR.csv
-rw-r--r-- 1 oracle dba 89082 Sep 23 17:51 sqlt_s87053_sqldx_05m7559uyv2vn_GVsSQL_PLAN_MONITOR.csv
-rw-r--r-- 1 oracle dba 57751 Sep 23 17:51 sqlt_s87053_sqldx_05m7559uyv2vn_driver.sql
-rw-r--r-- 1 oracle dba 2298024 Sep 23 17:51 sqlt_s87053_xtract_05m7559uyv2vn.zip
이상입니다.
출처 : SQL Tuning Health-Check Script (SQLHC) (Doc ID 1366133.1)
출처 : SQLTXPLAIN (SQLT) Tool that helps to diagnose SQL statements performing poorly (Doc ID 215187.1 )
반응형
'Oracle > 운영' 카테고리의 다른 글
| [12c 이상] 재기동 없이 패치정보가 alert log에 뜨는 경우? (0) | 2022.10.24 |
|---|---|
| Full Table Scan 시 Direct Path Read ? (0) | 2022.10.17 |
| 다른 세션에서 10046 trace 수행하기 (0) | 2022.09.06 |
| TABLE이 아닌 OBJECT RENAME TO (0) | 2022.09.05 |
| move tablespace parallel 옵션 사용 (0) | 2022.08.22 |
댓글