Hi,

I will explain SQLTXPLAIN (SQLT) Usage with Examples in Oracle in this article.

 

Performance Tuning with SQLTXPLAIN (SQLT)

 

You can read the previous article of SQLTXPLAIN (SQLT) with the following link.

Oracle SQLTXPLAIN (SQLT) Tips and Tricks | Oracle Database Performance Tuning Tutorial -18

 

 

 

SQLTXPLAIN (SQLT) with Examples in Oracle

 

Now Let’s use sqltxtract method of SQLTXPLAIN (SQLT) as follows.

You need to find the SQL_ID from v$sql or AWR reports for this method.

 

[oracle@msdbadbadm01 run]$ sqlplus mehmetsalih/deveci

SQL*Plus: Release 12.1.0.2.0 Production on Mon Aug 24 22:44:43 2020

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Last Successful login time: Fri Aug 07 2020 14:58:46 +03:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> START sqltxtract.sql 5hquy4x1r7a2n




PL/SQL procedure successfully completed.




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]: S

PL/SQL procedure successfully completed.




Paremeter 2:
SQLTXPLAIN password (required)

Enter value for 2: welcome1


PL/SQL procedure successfully completed.




PL/SQL procedure successfully completed.


Value passed:
SQL_ID_OR_HASH_VALUE: "5hquy4x1r7a2n"


PL/SQL procedure successfully completed.

***
*** NOTE:
*** If you get error below it means SQLTXPLAIN is not installed:
*** PLS-00201: identifier 'SQLTXADMIN.SQLT$A' must be declared.
*** In such case look for errors in NN_*.log files created during install.
***

***
*** NOTE:
*** If running as SYS in 12c make sure to review sqlt_instructions.html first
***


SQLT_VERSION
----------------------------------------
SQLT version number: 19.1.200226
SQLT version date : 2020-02-26
Installation date : 2020-08-24/15:31:52

... please wait ...
adding: alert_msdba1.log (deflated 97%)

NOTE:
You used the XTRACT method connected as MEHMETSALIH.

In case of a session disconnect please verify the following:
1. There are no errors in sqltxtract.log.
2. Your SQL 5hquy4x1r7a2n exists in memory or in AWR.
3. You connected as the application user that issued original SQL.
4. User MEHMETSALIH has been granted SQLT_USER_ROLE.

In case of errors ORA-03113, ORA-03114 or ORA-07445 please just
re-try this SQLT method. This tool handles some of the errors behind
a disconnect when executed a second time.

To actually diagnose the problem behind the disconnect, read ALERT
log and provide referenced traces to Support. After the root cause
of the disconnect is fixed then reset SQLT corresponding parameter.

To monitor progress, login into another session and execute:
SQL> SELECT * FROM SQLTXADMIN.sqlt$_log_v;

... collecting diagnostics details, please wait ...

In case of a disconnect review log file in current directory
If running as SYS in 12c make sure to review sqlt_instructions.html first

... getting sqlt_s67095_sql_monitor_active_driver.sql out of sqlt repository ...
adding: sqlt_s67095_sql_monitor_active_driver.sql (deflated 47%)
... getting sqlt_s67095_remote_driver.sql out of sqlt repository ...
adding: sqlt_s67095_remote_driver.sql (deflated 47%)
... getting missing_file.txt out of sqlt repository ...
... getting missing_file.txt out of sqlt repository ...
... getting missing_file.txt out of sqlt repository ...
... getting sqlt_s67095_perfhub_driver.sql out of sqlt repository ...
... generating sqlt_s67095_perfhub_0001_2020-06-29/15:59:19_2020-06-29/17:59:44.html ...
adding: sqlt_s67095_perfhub_driver.sql (deflated 54%)
... getting sqlt_s67095_main.html out of sqlt repository ...
.............................................................................................
.............................................................................................
.............................................................................................
adding: sqlt_s67095_tc.zip (stored 0%)
adding: sqlt_s67095_trc.zip (stored 0%)
adding: sqlt_s67095_xpand.sql (deflated 61%)
Archive: sqlt_s67095_xtract_5hquy4x1r7a2n.zip
Length Date Time Name
--------- ---------- ----- ----
109667 08-24-2020 23:03 sqlt_s67095_10053_i1_c0_extract.trc
20105 08-24-2020 23:04 sqlt_s67095_addmrpt_0007.zip
31482 08-24-2020 23:05 sqlt_s67095_ashrpt_0007.zip
785264 08-24-2020 23:04 sqlt_s67095_awrrpt_0007.zip
610 08-24-2020 23:06 sqlt_s67095_cell_state.zip
8737 08-24-2020 23:06 sqlt_s67095_driver.zip
25804 08-24-2020 23:03 sqlt_s67095_lite.html
1091293 08-24-2020 23:06 sqlt_s67095_log.zip
3264581 08-24-2020 23:03 sqlt_s67095_main.html
58502196 08-24-2020 23:06 sqlt_s67095_opatch.zip
20831 08-24-2020 23:03 sqlt_s67095_readme.html
10468 08-24-2020 23:03 sqlt_s67095_sql_detail_active.html
2054 08-24-2020 23:03 sqlt_s67095_sta_report_mem.txt
21574979 08-24-2020 23:05 sqlt_s67095_tcb.zip
872 08-24-2020 23:05 sqlt_s67095_tc_script.sql
144 08-24-2020 23:05 sqlt_s67095_tc_sql.sql
52588 08-24-2020 23:06 sqlt_s67095_tcx.zip
58558366 08-24-2020 23:06 sqlt_s67095_tc.zip
86413 08-24-2020 23:06 sqlt_s67095_trc.zip
1122 08-24-2020 23:05 sqlt_s67095_xpand.sql
--------- -------
144147576 20 files

File sqlt_s67095_xtract_5hquy4x1r7a2n.zip for 5hquy4x1r7a2n has been created.
sqlt_s67095_sqldx
T
CSV
5hquy4x1r7a2n

Parameter 1:
Oracle Pack License (Tuning or Diagnostics) [T|D] (required)




Parameter 2:
Output Type (HTML or CSV or Both) [H|C|B] (required)




Parameter 3:
SQL_ID of the SQL to be analyzed (required)


Values passed:
License: "T"
Output : "CSV"
SQL_ID : "5hquy4x1r7a2n"


### ... getting SQL text ...


### ... getting signature ...


### ... getting tables ...


### ... generating dynamic script, please wait ...


sqlt_s67095_sqldx_5hquy4x1r7a2n_driver.sql file has been created.

###
### by sql_id
###
2020-08-24/23:09:51 DBA_HIST_ACTIVE_SESS_HISTORY
2020-08-24/23:09:52 DBA_HIST_SQLBIND
2020-08-24/23:09:52 DBA_HIST_SQLSTAT
2020-08-24/23:09:52 DBA_HIST_SQLTEXT
2020-08-24/23:09:52 DBA_HIST_SQL_BIND_METADATA
2020-08-24/23:09:52 DBA_HIST_SQL_PLAN
2020-08-24/23:09:52 DBA_SQLSET_PLANS
2020-08-24/23:09:53 DBA_SQLSET_STATEMENTS
2020-08-24/23:09:53 GV$ACTIVE_SESSION_HISTORY
2020-08-24/23:09:54 GV$SQL
2020-08-24/23:09:55 GV$SQLAREA
2020-08-24/23:09:55 GV$SQLAREA_PLAN_HASH
2020-08-24/23:09:55 GV$SQLSTATS
2020-08-24/23:09:55 GV$SQLSTATS_PLAN_HASH
2020-08-24/23:09:56 GV$SQLTEXT
2020-08-24/23:09:56 GV$SQLTEXT_WITH_NEWLINES
2020-08-24/23:09:56 GV$SQL_BIND_CAPTURE
2020-08-24/23:09:56 GV$SQL_OPTIMIZER_ENV
2020-08-24/23:09:56 GV$SQL_PLAN
2020-08-24/23:09:56 GV$SQL_PLAN_STATISTICS_ALL
2020-08-24/23:09:57 GV$SQL_REDIRECTION
2020-08-24/23:09:58 GV$SQL_SHARED_CURSOR
2020-08-24/23:09:58 GV$SQL_WORKAREA
adding: sqlt_s67095_sqldx_5hquy4x1r7a2n_DBA_HIST_ACTIVE_SESS_HISTORY.csv (deflated 96%)
adding: sqlt_s67095_sqldx_5hquy4x1r7a2n_DBA_HIST_SQLBIND.csv (deflated 100%)
adding: sqlt_s67095_sqldx_5hquy4x1r7a2n_DBA_HIST_SQL_BIND_METADATA.csv (deflated 81%)
adding: sqlt_s67095_sqldx_5hquy4x1r7a2n_DBA_HIST_SQL_PLAN.csv (deflated 99%)
adding: sqlt_s67095_sqldx_5hquy4x1r7a2n_DBA_HIST_SQLSTAT.csv (deflated 95%)
adding: sqlt_s67095_sqldx_5hquy4x1r7a2n_DBA_HIST_SQLTEXT.csv (deflated 98%)
adding: sqlt_s67095_sqldx_5hquy4x1r7a2n_DBA_SQLSET_PLANS.csv (deflated 99%)
adding: sqlt_s67095_sqldx_5hquy4x1r7a2n_DBA_SQLSET_STATEMENTS.csv (deflated 96%)
adding: sqlt_s67095_sqldx_5hquy4x1r7a2n_GVsACTIVE_SESSION_HISTORY.csv (deflated 97%)
adding: sqlt_s67095_sqldx_5hquy4x1r7a2n_GVsSQLAREA.csv (deflated 94%)
adding: sqlt_s67095_sqldx_5hquy4x1r7a2n_GVsSQLAREA_PLAN_HASH.csv (deflated 85%)
adding: sqlt_s67095_sqldx_5hquy4x1r7a2n_GVsSQL_BIND_CAPTURE.csv (deflated 98%)
adding: sqlt_s67095_sqldx_5hquy4x1r7a2n_GVsSQL.csv (deflated 85%)
adding: sqlt_s67095_sqldx_5hquy4x1r7a2n_GVsSQL_OPTIMIZER_ENV.csv (deflated 94%)
adding: sqlt_s67095_sqldx_5hquy4x1r7a2n_GVsSQL_PLAN.csv (deflated 99%)
adding: sqlt_s67095_sqldx_5hquy4x1r7a2n_GVsSQL_PLAN_STATISTICS_ALL.csv (deflated 99%)
adding: sqlt_s67095_sqldx_5hquy4x1r7a2n_GVsSQL_REDIRECTION.csv (deflated 96%)
adding: sqlt_s67095_sqldx_5hquy4x1r7a2n_GVsSQL_SHARED_CURSOR.csv (deflated 98%)
adding: sqlt_s67095_sqldx_5hquy4x1r7a2n_GVsSQLSTATS.csv (deflated 86%)
adding: sqlt_s67095_sqldx_5hquy4x1r7a2n_GVsSQLSTATS_PLAN_HASH.csv (deflated 86%)
adding: sqlt_s67095_sqldx_5hquy4x1r7a2n_GVsSQLTEXT.csv (deflated 84%)
adding: sqlt_s67095_sqldx_5hquy4x1r7a2n_GVsSQLTEXT_WITH_NEWLINES.csv (deflated 84%)
adding: sqlt_s67095_sqldx_5hquy4x1r7a2n_GVsSQL_WORKAREA.csv (deflated 82%)
Archive: sqlt_s67095_sqldx_5hquy4x1r7a2n_csv.zip
Length Date Time Name
--------- ---------- ----- ----
10281390 08-24-2020 23:09 sqlt_s67095_sqldx_5hquy4x1r7a2n_DBA_HIST_ACTIVE_SESS_HISTORY.csv
1467636 08-24-2020 23:09 sqlt_s67095_sqldx_5hquy4x1r7a2n_DBA_HIST_SQLBIND.csv
1107 08-24-2020 23:09 sqlt_s67095_sqldx_5hquy4x1r7a2n_DBA_HIST_SQL_BIND_METADATA.csv
280357 08-24-2020 23:09 sqlt_s67095_sqldx_5hquy4x1r7a2n_DBA_HIST_SQL_PLAN.csv
523944 08-24-2020 23:09 sqlt_s67095_sqldx_5hquy4x1r7a2n_DBA_HIST_SQLSTAT.csv
12297 08-24-2020 23:09 sqlt_s67095_sqldx_5hquy4x1r7a2n_DBA_HIST_SQLTEXT.csv
549200 08-24-2020 23:09 sqlt_s67095_sqldx_5hquy4x1r7a2n_DBA_SQLSET_PLANS.csv
22040 08-24-2020 23:09 sqlt_s67095_sqldx_5hquy4x1r7a2n_DBA_SQLSET_STATEMENTS.csv
7987338 08-24-2020 23:09 sqlt_s67095_sqldx_5hquy4x1r7a2n_GVsACTIVE_SESSION_HISTORY.csv
29574 08-24-2020 23:09 sqlt_s67095_sqldx_5hquy4x1r7a2n_GVsSQLAREA.csv
8944 08-24-2020 23:09 sqlt_s67095_sqldx_5hquy4x1r7a2n_GVsSQLAREA_PLAN_HASH.csv
25408 08-24-2020 23:09 sqlt_s67095_sqldx_5hquy4x1r7a2n_GVsSQL_BIND_CAPTURE.csv
10276 08-24-2020 23:09 sqlt_s67095_sqldx_5hquy4x1r7a2n_GVsSQL.csv
27328 08-24-2020 23:09 sqlt_s67095_sqldx_5hquy4x1r7a2n_GVsSQL_OPTIMIZER_ENV.csv
274835 08-24-2020 23:09 sqlt_s67095_sqldx_5hquy4x1r7a2n_GVsSQL_PLAN.csv
281380 08-24-2020 23:09 sqlt_s67095_sqldx_5hquy4x1r7a2n_GVsSQL_PLAN_STATISTICS_ALL.csv
9016 08-24-2020 23:09 sqlt_s67095_sqldx_5hquy4x1r7a2n_GVsSQL_REDIRECTION.csv
16960 08-24-2020 23:09 sqlt_s67095_sqldx_5hquy4x1r7a2n_GVsSQL_SHARED_CURSOR.csv
6412 08-24-2020 23:09 sqlt_s67095_sqldx_5hquy4x1r7a2n_GVsSQLSTATS.csv
6244 08-24-2020 23:09 sqlt_s67095_sqldx_5hquy4x1r7a2n_GVsSQLSTATS_PLAN_HASH.csv
1088 08-24-2020 23:09 sqlt_s67095_sqldx_5hquy4x1r7a2n_GVsSQLTEXT.csv
1088 08-24-2020 23:09 sqlt_s67095_sqldx_5hquy4x1r7a2n_GVsSQLTEXT_WITH_NEWLINES.csv
2265 08-24-2020 23:09 sqlt_s67095_sqldx_5hquy4x1r7a2n_GVsSQL_WORKAREA.csv
--------- -------
21826127 23 files
adding: sqlt_s67095_sqldx_5hquy4x1r7a2n_csv.zip (stored 0%)
Archive: sqlt_s67095_sqldx.zip
Length Date Time Name
--------- ---------- ----- ----
679244 08-24-2020 23:09 sqlt_s67095_sqldx_5hquy4x1r7a2n_csv.zip
--------- -------
679244 1 file
###
### by exact signature
###
###
### by force signature
###
###
### by table
###
2020-08-24/23:09:59 DBA_ADVISOR_SQLA_TABLES
2020-08-24/23:09:59 DBA_ALL_TABLES
2020-08-24/23:10:04 DBA_CATALOG
2020-08-24/23:10:04 DBA_COL_COMMENTS
2020-08-24/23:10:04 DBA_CONSTRAINTS
2020-08-24/23:10:08 DBA_CONS_COLUMNS
2020-08-24/23:10:09 DBA_INDEXES
2020-08-24/23:10:10 DBA_IND_COLUMNS
2020-08-24/23:10:10 DBA_IND_STATISTICS
2020-08-24/23:10:15 DBA_LOBS
2020-08-24/23:10:17 DBA_OBJ_COLATTRS
2020-08-24/23:10:18 DBA_ROLLING_UNSUPPORTED
2020-08-24/23:10:23 DBA_STREAMS_COLUMNS
2020-08-24/23:10:24 DBA_STREAMS_UNSUPPORTED
2020-08-24/23:10:25 DBA_SYNONYMS
2020-08-24/23:10:26 DBA_TABLES
2020-08-24/23:10:28 DBA_TAB_COLS
2020-08-24/23:10:31 DBA_TAB_COLUMNS
2020-08-24/23:10:34 DBA_TAB_COL_STATISTICS
2020-08-24/23:10:36 DBA_TAB_COMMENTS
2020-08-24/23:10:36 DBA_TAB_HISTOGRAMS
2020-08-24/23:10:37 DBA_TAB_PRIVS
2020-08-24/23:10:40 DBA_TAB_STATISTICS
2020-08-24/23:10:44 DBA_TRIGGERS
2020-08-24/23:10:46 DBA_TRIGGER_COLS
2020-08-24/23:10:50 DBA_UPDATABLE_COLUMNS
adding: sqlt_s67095_sqldx_table_DBA_ADVISOR_SQLA_TABLES.csv (deflated 95%)
adding: sqlt_s67095_sqldx_table_DBA_ALL_TABLES.csv (deflated 89%)
adding: sqlt_s67095_sqldx_table_DBA_CATALOG.csv (deflated 91%)
adding: sqlt_s67095_sqldx_table_DBA_COL_COMMENTS.csv (deflated 99%)
adding: sqlt_s67095_sqldx_table_DBA_CONS_COLUMNS.csv (deflated 99%)
adding: sqlt_s67095_sqldx_table_DBA_CONSTRAINTS.csv (deflated 99%)
adding: sqlt_s67095_sqldx_table_DBA_IND_COLUMNS.csv (deflated 99%)
adding: sqlt_s67095_sqldx_table_DBA_INDEXES.csv (deflated 94%)
adding: sqlt_s67095_sqldx_table_DBA_IND_STATISTICS.csv (deflated 92%)
adding: sqlt_s67095_sqldx_table_DBA_LOBS.csv (deflated 98%)
adding: sqlt_s67095_sqldx_table_DBA_OBJ_COLATTRS.csv (deflated 99%)
adding: sqlt_s67095_sqldx_table_DBA_ROLLING_UNSUPPORTED.csv (deflated 76%)
adding: sqlt_s67095_sqldx_table_DBA_STREAMS_COLUMNS.csv (deflated 96%)
adding: sqlt_s67095_sqldx_table_DBA_STREAMS_UNSUPPORTED.csv (deflated 88%)
adding: sqlt_s67095_sqldx_table_DBA_SYNONYMS.csv (deflated 94%)
adding: sqlt_s67095_sqldx_table_DBA_TAB_COLS.csv (deflated 99%)
adding: sqlt_s67095_sqldx_table_DBA_TAB_COL_STATISTICS.csv (deflated 97%)
adding: sqlt_s67095_sqldx_table_DBA_TAB_COLUMNS.csv (deflated 99%)
adding: sqlt_s67095_sqldx_table_DBA_TAB_COMMENTS.csv (deflated 99%)
adding: sqlt_s67095_sqldx_table_DBA_TAB_HISTOGRAMS.csv (deflated 99%)
adding: sqlt_s67095_sqldx_table_DBA_TABLES.csv (deflated 88%)
adding: sqlt_s67095_sqldx_table_DBA_TAB_PRIVS.csv (deflated 94%)
adding: sqlt_s67095_sqldx_table_DBA_TAB_STATISTICS.csv (deflated 89%)
adding: sqlt_s67095_sqldx_table_DBA_TRIGGER_COLS.csv (deflated 99%)
adding: sqlt_s67095_sqldx_table_DBA_TRIGGERS.csv (deflated 100%)
adding: sqlt_s67095_sqldx_table_DBA_UPDATABLE_COLUMNS.csv (deflated 96%)
Archive: sqlt_s67095_sqldx_table_csv.zip
Length Date Time Name
--------- ---------- ----- ----
29112 08-24-2020 23:09 sqlt_s67095_sqldx_table_DBA_ADVISOR_SQLA_TABLES.csv
6638 08-24-2020 23:10 sqlt_s67095_sqldx_table_DBA_ALL_TABLES.csv
1067 08-24-2020 23:10 sqlt_s67095_sqldx_table_DBA_CATALOG.csv
105816 08-24-2020 23:10 sqlt_s67095_sqldx_table_DBA_COL_COMMENTS.csv
35152 08-24-2020 23:10 sqlt_s67095_sqldx_table_DBA_CONS_COLUMNS.csv
72424 08-24-2020 23:10 sqlt_s67095_sqldx_table_DBA_CONSTRAINTS.csv
13754 08-24-2020 23:10 sqlt_s67095_sqldx_table_DBA_IND_COLUMNS.csv
12956 08-24-2020 23:10 sqlt_s67095_sqldx_table_DBA_INDEXES.csv
4364 08-24-2020 23:10 sqlt_s67095_sqldx_table_DBA_IND_STATISTICS.csv
18920 08-24-2020 23:10 sqlt_s67095_sqldx_table_DBA_LOBS.csv
12823 08-24-2020 23:10 sqlt_s67095_sqldx_table_DBA_OBJ_COLATTRS.csv
449 08-24-2020 23:10 sqlt_s67095_sqldx_table_DBA_ROLLING_UNSUPPORTED.csv
11342 08-24-2020 23:10 sqlt_s67095_sqldx_table_DBA_STREAMS_COLUMNS.csv
905 08-24-2020 23:10 sqlt_s67095_sqldx_table_DBA_STREAMS_UNSUPPORTED.csv
1998 08-24-2020 23:10 sqlt_s67095_sqldx_table_DBA_SYNONYMS.csv
305868 08-24-2020 23:10 sqlt_s67095_sqldx_table_DBA_TAB_COLS.csv
31920 08-24-2020 23:10 sqlt_s67095_sqldx_table_DBA_TAB_COL_STATISTICS.csv
139514 08-24-2020 23:10 sqlt_s67095_sqldx_table_DBA_TAB_COLUMNS.csv
17168 08-24-2020 23:10 sqlt_s67095_sqldx_table_DBA_TAB_COMMENTS.csv
240436 08-24-2020 23:10 sqlt_s67095_sqldx_table_DBA_TAB_HISTOGRAMS.csv
5834 08-24-2020 23:10 sqlt_s67095_sqldx_table_DBA_TABLES.csv
2893 08-24-2020 23:10 sqlt_s67095_sqldx_table_DBA_TAB_PRIVS.csv
3412 08-24-2020 23:10 sqlt_s67095_sqldx_table_DBA_TAB_STATISTICS.csv
68028 08-24-2020 23:10 sqlt_s67095_sqldx_table_DBA_TRIGGER_COLS.csv
2004488 08-24-2020 23:10 sqlt_s67095_sqldx_table_DBA_TRIGGERS.csv
9576 08-24-2020 23:10 sqlt_s67095_sqldx_table_DBA_UPDATABLE_COLUMNS.csv
--------- -------
3156857 26 files
adding: sqlt_s67095_sqldx_table_csv.zip (stored 0%)
Archive: sqlt_s67095_sqldx.zip
Length Date Time Name
--------- ---------- ----- ----
679244 08-24-2020 23:09 sqlt_s67095_sqldx_5hquy4x1r7a2n_csv.zip
27517 08-24-2020 23:10 sqlt_s67095_sqldx_table_csv.zip
--------- -------
706761 2 files
###
### by global
###
2020-08-24/23:10:50 DBA_HIST_SNAPSHOT
2020-08-24/23:10:50 GV$PARAMETER2
adding: sqlt_s67095_sqldx_global_DBA_HIST_SNAPSHOT.csv (deflated 96%)
adding: sqlt_s67095_sqldx_global_GVsPARAMETER2.csv (deflated 99%)
Archive: sqlt_s67095_sqldx_global_csv.zip
Length Date Time Name
--------- ---------- ----- ----
1519868 08-24-2020 23:10 sqlt_s67095_sqldx_global_DBA_HIST_SNAPSHOT.csv
6800864 08-24-2020 23:10 sqlt_s67095_sqldx_global_GVsPARAMETER2.csv
--------- -------
8320732 2 files
adding: sqlt_s67095_sqldx_global_csv.zip (stored 0%)
Archive: sqlt_s67095_sqldx.zip
Length Date Time Name
--------- ---------- ----- ----
679244 08-24-2020 23:09 sqlt_s67095_sqldx_5hquy4x1r7a2n_csv.zip
27517 08-24-2020 23:10 sqlt_s67095_sqldx_table_csv.zip
117992 08-24-2020 23:10 sqlt_s67095_sqldx_global_csv.zip
--------- -------
824753 3 files

sqlt_s67095_sqldx_*.zip files have been created.
adding: sqlt_s67095_sqldx_5hquy4x1r7a2n_driver.sql (deflated 93%)
adding: sqldx.log (deflated 58%)

Archive: sqlt_s67095_sqldx_5hquy4x1r7a2n_log.zip
Length Date Time Name
--------- ---------- ----- ----
60215 08-24-2020 23:09 sqlt_s67095_sqldx_5hquy4x1r7a2n_driver.sql
3135 08-24-2020 23:06 sqldx.log
--------- -------
63350 2 files

adding: sqlt_s67095_sqldx_5hquy4x1r7a2n_log.zip (stored 0%)


SQLDX files have been created.

Archive: sqlt_s67095_sqldx.zip
Length Date Time Name
--------- ---------- ----- ----
679244 08-24-2020 23:09 sqlt_s67095_sqldx_5hquy4x1r7a2n_csv.zip
27517 08-24-2020 23:10 sqlt_s67095_sqldx_table_csv.zip
117992 08-24-2020 23:10 sqlt_s67095_sqldx_global_csv.zip
6007 08-24-2020 23:10 sqlt_s67095_sqldx_5hquy4x1r7a2n_log.zip
--------- -------
830760 4 files

adding: sqlt_s67095_sqldx.zip (stored 0%)


PL/SQL procedure successfully completed.


UNIQUE_TIMESTAMP_ID
--------------------------------
sqlt_20200824_2310

/bin/bash: RENAME: command 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_s67095_purge.sql out of sqlt repository ...

SQLTXTRACT completed.
SQL>

 

 

 

 

 

Now download this sqlt_20200824_2310_5hquy4x1r7a2n_S.zip file and unzip this file.

Go to sqlt_20200824_2310_5hquy4x1r7a2n_S directory, you can review the diagnostic and performance log files related with the 5hquy4x1r7a2n SQL_ID.

 

You can open sqlt_s67095_main.html file and review the Execution Plans, Plan Control, Tables and other findings related with the SQL_ID.

 

 

 

Now Use sqltxtrxec method and run sqltxtrxec.sql to review for 00hzxfpq19uu8 SQL_ID as follows.

 

[oracle@msdbadbadm01 run]$ sqlplus mehmetsalih/deveci

SQL*Plus: Release 12.1.0.2.0 Production on Tue Aug 25 18:48:58 2020

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Last Successful login time: Tue Aug 25 2020 18:48:45 +03:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> START sqltxtrxec.sql 00hzxfpq19uu8


PL/SQL procedure successfully completed.


Parameter 1:
SQL_ID or HASH_VALUE of the SQL to be extracted (required)

Paremeter 2:
SQLTXPLAIN password (required)

Enter value for 2: welcome1

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.


Value passed to sqltxtrxec:
SQL_ID_OR_HASH_VALUE: "00hzxfpq19uu8"


string beginning "TERM OFF E..." is too long. maximum size is 1 character.


USER
------------------------------
MEHMETSALIH


CURRENT_TIME
-------------------
2020-08-25 18:49:08


PRODUCT
--------------------------------------------------------------------------------
VERSION
--------------------------------------------------------------------------------
STATUS
--------------------------------------------------------------------------------
NLSRTL
12.1.0.2.0
Production

Oracle Database 12c Enterprise Edition
12.1.0.2.0
64bit Production

PL/SQL
12.1.0.2.0
Production

TNS for Linux:
12.1.0.2.0
Production




BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0
PL/SQL Release 12.1.0.2.0 - Production 0
CORE 12.1.0.2.0 Production 0
TNS for Linux: Version 12.1.0.2.0 - Production 0
NLSRTL Version 12.1.0.2.0 - Production 0


INSTANCE_NUMBER INSTANCE_NAME HOST_NAME
--------------- ---------------- ----------------------------------------------------------------
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT LOGINS SHU
----------------- --------- ------------ --- ---------- ------- --------------- ---------- ---
DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO CON_ID INSTANCE_MO EDITION
----------------- ------------------ --------- --- ---------- ----------- -------
FAMILY
--------------------------------------------------------------------------------
1 msdba1 msdbadbadm01.deveci.com.tr
12.1.0.2.0 07-AUG-20 OPEN YES 1 STARTED ALLOWED NO
ACTIVE PRIMARY_INSTANCE NORMAL NO 0 REGULAR EE



NAME
--------------------------------------------------------------------------------
VALUE
----------------------------------------------------------------------------------------------------
background_dump_dest
/u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/log

user_dump_dest
/u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/log

core_dump_dest
/u01/app/oracle/diag/rdbms/msdba/msdba1/cdump




DIRECTORIES
----------------------------------------------------------------------------------------------------
SQLT$BDUMP /u01/app/oracle/diag/rdbms/msdba/msdba1/trace
SQLT$DIAG /u01/app/oracle/diag/rdbms/msdba/msdba1/trace
SQLT$STAGE /u01/app/oracle/diag/rdbms/msdba/msdba1/trace
SQLT$UDUMP /u01/app/oracle/diag/rdbms/msdba/msdba1/trace
TRCA$INPUT1 /u01/app/oracle/diag/rdbms/msdba/msdba1/trace
TRCA$INPUT2 /u01/app/oracle/diag/rdbms/msdba/msdba1/trace
TRCA$STAGE /u01/app/oracle/diag/rdbms/msdba/msdba1/trace

adding: sqlt_s67097_sqldx_6448282933961516002_force_DBA_SQLSET_PLANS.csv (deflated 99%)
adding: sqlt_s67097_sqldx_6448282933961516002_force_DBA_SQLSET_STATEMENTS.csv (deflated 95%)
adding: sqlt_s67097_sqldx_6448282933961516002_force_GVsACTIVE_SESSION_HISTORY.csv (deflated 94%)
adding: sqlt_s67097_sqldx_6448282933961516002_force_GVsSQL_MONITOR.csv (deflated 96%)
adding: sqlt_s67097_sqldx_6448282933961516002_force_GVsSQLSTATS.csv (deflated 87%)
adding: sqlt_s67097_sqldx_6448282933961516002_force_GVsSQLSTATS_PLAN_HASH.csv (deflated 87%)
Archive: sqlt_s67097_sqldx_6448282933961516002_force_csv.zip
Length Date Time Name
--------- ---------- ----- ----
109872 08-25-2020 19:10 sqlt_s67097_sqldx_6448282933961516002_force_DBA_SQLSET_PLANS.csv
16530 08-25-2020 19:10 sqlt_s67097_sqldx_6448282933961516002_force_DBA_SQLSET_STATEMENTS.csv
76849 08-25-2020 19:10 sqlt_s67097_sqldx_6448282933961516002_force_GVsACTIVE_SESSION_HISTORY.csv
30192 08-25-2020 19:10 sqlt_s67097_sqldx_6448282933961516002_force_GVsSQL_MONITOR.csv
6412 08-25-2020 19:10 sqlt_s67097_sqldx_6448282933961516002_force_GVsSQLSTATS.csv
6244 08-25-2020 19:10 sqlt_s67097_sqldx_6448282933961516002_force_GVsSQLSTATS_PLAN_HASH.csv
--------- -------
246099 6 files
adding: sqlt_s67097_sqldx_6448282933961516002_force_csv.zip (stored 0%)
Archive: sqlt_s67097_sqldx.zip
Length Date Time Name
--------- ---------- ----- ----
13271 08-25-2020 19:10 sqlt_s67097_sqldx_65hnajabppy8m_csv.zip
11171 08-25-2020 19:10 sqlt_s67097_sqldx_6448282933961516002_force_csv.zip
--------- -------
24442 2 files
###
### by table
###
zip warning: name not matched: sqlt_s67097_sqldx_table_*.csv

zip error: Nothing to do! (sqlt_s67097_sqldx_table_csv.zip)
unzip: cannot find or open sqlt_s67097_sqldx_table_csv, sqlt_s67097_sqldx_table_csv.zip or sqlt_s67097_sqldx_table_csv.ZIP.
zip warning: name not matched: sqlt_s67097_sqldx_table_*.zip

zip error: Nothing to do! (sqlt_s67097_sqldx.zip)
Archive: sqlt_s67097_sqldx.zip
Length Date Time Name
--------- ---------- ----- ----
13271 08-25-2020 19:10 sqlt_s67097_sqldx_65hnajabppy8m_csv.zip
11171 08-25-2020 19:10 sqlt_s67097_sqldx_6448282933961516002_force_csv.zip
--------- -------
24442 2 files
###
### by global
###
2020-08-25/19:10:22 DBA_HIST_SNAPSHOT
2020-08-25/19:10:22 GV$PARAMETER2
adding: sqlt_s67097_sqldx_global_DBA_HIST_SNAPSHOT.csv (deflated 96%)
adding: sqlt_s67097_sqldx_global_GVsPARAMETER2.csv (deflated 99%)
Archive: sqlt_s67097_sqldx_global_csv.zip
Length Date Time Name
--------- ---------- ----- ----
1540948 08-25-2020 19:10 sqlt_s67097_sqldx_global_DBA_HIST_SNAPSHOT.csv
6800864 08-25-2020 19:10 sqlt_s67097_sqldx_global_GVsPARAMETER2.csv
--------- -------
8341812 2 files
adding: sqlt_s67097_sqldx_global_csv.zip (stored 0%)
Archive: sqlt_s67097_sqldx.zip
Length Date Time Name
--------- ---------- ----- ----
13271 08-25-2020 19:10 sqlt_s67097_sqldx_65hnajabppy8m_csv.zip
11171 08-25-2020 19:10 sqlt_s67097_sqldx_6448282933961516002_force_csv.zip
118866 08-25-2020 19:10 sqlt_s67097_sqldx_global_csv.zip
--------- -------
143308 3 files

sqlt_s67097_sqldx_*.zip files have been created.
adding: sqlt_s67097_sqldx_65hnajabppy8m_driver.sql (deflated 91%)
adding: sqldx.log (deflated 57%)

Archive: sqlt_s67097_sqldx_65hnajabppy8m_log.zip
Length Date Time Name
--------- ---------- ----- ----
37732 08-25-2020 19:10 sqlt_s67097_sqldx_65hnajabppy8m_driver.sql
2961 08-25-2020 19:09 sqldx.log
--------- -------
40693 2 files

adding: sqlt_s67097_sqldx_65hnajabppy8m_log.zip (stored 0%)


SQLDX files have been created.

Archive: sqlt_s67097_sqldx.zip
Length Date Time Name
--------- ---------- ----- ----
13271 08-25-2020 19:10 sqlt_s67097_sqldx_65hnajabppy8m_csv.zip
11171 08-25-2020 19:10 sqlt_s67097_sqldx_6448282933961516002_force_csv.zip
118866 08-25-2020 19:10 sqlt_s67097_sqldx_global_csv.zip
5023 08-25-2020 19:10 sqlt_s67097_sqldx_65hnajabppy8m_log.zip
--------- -------
148331 4 files

adding: sqlt_s67097_sqldx.zip (stored 0%)

#####
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_s67097_purge.sql out of sqlt repository ...

SQLTXECUTE completed.
updating: sqlt_s67096_tc_script.sql (deflated 34%)

adding: sqlt_s67096_xtract_00hzxfpq19uu8.zip (stored 0%)
adding: sqlt_s67097_xecute.zip (stored 0%)
adding: sqltxtrxec.log (deflated 74%)


PL/SQL procedure successfully completed.


SQLTXTRXEC completed.
SQL>
SQL>
SQL>

 

 

 

 

 

 

Download the sqlt_s67096_sqlt_s67097_xtrxec.zip and unzip the files and review them.

 

 

Open sqlt_s67096_main.html file and review the diagnostic data, Performance statistics and Plans.

 

 

For example: To tune the SQL and set new Profile or New SQL Plan, Click and open the SQL Tuning Advisor section.

SQL Tuning Advisor report is as follows.

 

* SQL> EXEC SQLTXADMIN.sqlt$a.set_param('sql_tuning_advisor', 'N');
*****************************************************************/

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : sqlt_s67096_mem
Tuning Task Owner : MEHMETSALIH
Tuning Task ID : 117719
Workload Type : Single SQL Statement
Execution Count : 1
Current Execution : EXEC_121364
Execution Type : TUNE SQL
Scope : COMPREHENSIVE
Time Limit(seconds): 900
Completion Status : COMPLETED
Started at : 08/25/2020 18:50:24
Completed at : 08/25/2020 18:50:29

-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID : 00hzxfpq19uu8
SQL Text : select * from MSDBA.TKGM_SERVIS_LOG where
vatandaslik_no=32216345040

-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.

Recommendation (estimated benefit: 95.88%)
------------------------------------------
- Consider accepting the recommended SQL profile to use parallel execution
for this statement.
execute dbms_sqltune.accept_sql_profile(task_name => 'sqlt_s67096_mem',
task_owner => 'MEHMETSALIH', replace => TRUE, profile_type =>
DBMS_SQLTUNE.PX_PROFILE);

Executing this query parallel with DOP 27 will improve its response time
95.89% over the original plan. However, there is some cost in enabling
parallel execution. It will increase the statement's resource consumption by
an estimated 11.04% which may result in a reduction of system throughput.
Also, because these resources are consumed over a much smaller duration, the
response time of concurrent statements might be negatively impacted if
sufficient hardware capacity is not available.

The following data shows some sampled statistics for this SQL from the past
week and projected weekly values when parallel execution is enabled.

Past week sampled statistics for this SQL
-----------------------------------------
Number of executions 0
Percent of total activity 0
Percent of samples with #Active Sessions > 2*CPU 0
Weekly DB time (in sec) 0

Projected statistics with Parallel Execution
--------------------------------------------
Weekly DB time (in sec) 0

2- Index Finding (see explain plans section below)
--------------------------------------------------
The execution plan of this statement can be improved by creating one or more
indices.

Recommendation (estimated benefit: 65.25%)
------------------------------------------
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index MSDBA.IDX$$_1CBD70001 on MSDBA.TKGM_SERVIS_LOG(TO_NUMBER("VATAND
ASLIK_NO"));

Rationale
---------
Creating the recommended indices significantly improves the execution plan
of this statement. However, it might be preferable to run "Access Advisor"
using a representative SQL workload as opposed to a single statement. This
will allow to get comprehensive index recommendations which takes into
account index maintenance overhead and additional space consumption.

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original
-----------
Plan hash value: 3417633012


---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 323K| 688M| 1879K (1)| 00:01:14 |
|* 1 | TABLE ACCESS STORAGE FULL| TKGM_SERVIS_LOG | 323K| 688M| 1879K (1)| 00:01:14 |
---------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1 / TKGM_SERVIS_LOG@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

1 - storage(TO_NUMBER("VATANDASLIK_NO")=32216345040)
filter(TO_NUMBER("VATANDASLIK_NO")=32216345040)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - "TKGM_SERVIS_LOG"."ID"[NUMBER,22], "TKGM_SERVIS_LOG"."METOD_ADI"[VARCHAR2,100]
, "TKGM_SERVIS_LOG"."PARAMETRELER"[VARCHAR2,4000],
"TKGM_SERVIS_LOG"."KAYIT_TARIHI"[DATE,7], "TKGM_SERVIS_LOG"."KAYDEDEN"[VARCHAR2,15],
"TKGM_SERVIS_LOG"."HATA_MESAJI"[VARCHAR2,4000], "TKGM_SERVIS_LOG"."SURE"[NUMBER,22],
"VATANDASLIK_NO"[VARCHAR2,15], "TKGM_SERVIS_LOG"."SERVIS_TALEP"[LOB,4000],
"TKGM_SERVIS_LOG"."SERVIS_YANIT"[LOB,4000]

2- Using New Indices
--------------------
Plan hash value: 1643209997


-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 323K| 688M| 653K (1)| 00:00:26 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TKGM_SERVIS_LOG | 323K| 688M| 653K (1)| 00:00:26 |
|* 2 | INDEX RANGE SCAN | IDX$$_1CBD70001 | 1056K| | 2732 (1)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1 / TKGM_SERVIS_LOG@SEL$1
2 - SEL$1 / TKGM_SERVIS_LOG@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("TKGM_SERVIS_LOG"."SYS_QSMMIX_VCOL_5001"=32216345040)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - "TKGM_SERVIS_LOG"."ID"[NUMBER,22], "TKGM_SERVIS_LOG"."METOD_ADI"[VARCHAR2,100],
"TKGM_SERVIS_LOG"."PARAMETRELER"[VARCHAR2,4000], "TKGM_SERVIS_LOG"."KAYIT_TARIHI"[DATE,7],
"TKGM_SERVIS_LOG"."KAYDEDEN"[VARCHAR2,15], "TKGM_SERVIS_LOG"."HATA_MESAJI"[VARCHAR2,4000],
"TKGM_SERVIS_LOG"."SURE"[NUMBER,22], "TKGM_SERVIS_LOG"."VATANDASLIK_NO"[VARCHAR2,15],
"TKGM_SERVIS_LOG"."SERVIS_TALEP"[LOB,4000], "TKGM_SERVIS_LOG"."SERVIS_YANIT"[LOB,4000]
2 - "TKGM_SERVIS_LOG".ROWID[ROWID,10]

3- Using Parallel Execution
---------------------------
Plan hash value: 1948207318


-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 121K| 259M| 77309 (1)| 00:00:04 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 121K| 259M| 77309 (1)| 00:00:04 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 121K| 259M| 77309 (1)| 00:00:04 | Q1,00 | PCWC | |
|* 4 | TABLE ACCESS STORAGE FULL| TKGM_SERVIS_LOG | 121K| 259M| 77309 (1)| 00:00:04 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1
4 - SEL$1 / TKGM_SERVIS_LOG@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

4 - storage(TO_NUMBER("VATANDASLIK_NO")=32216345040)
filter(TO_NUMBER("VATANDASLIK_NO")=32216345040)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - "TKGM_SERVIS_LOG"."ID"[NUMBER,22], "TKGM_SERVIS_LOG"."METOD_ADI"[VARCHAR2,100],
"TKGM_SERVIS_LOG"."PARAMETRELER"[VARCHAR2,4000], "TKGM_SERVIS_LOG"."KAYIT_TARIHI"[DATE,7],
"TKGM_SERVIS_LOG"."KAYDEDEN"[VARCHAR2,15], "TKGM_SERVIS_LOG"."HATA_MESAJI"[VARCHAR2,4000],
"TKGM_SERVIS_LOG"."SURE"[NUMBER,22], "VATANDASLIK_NO"[VARCHAR2,15], "TKGM_SERVIS_LOG"."SERVIS_TALEP"[LOB,4000],
"TKGM_SERVIS_LOG"."SERVIS_YANIT"[LOB,4000]
2 - (#keys=0) "TKGM_SERVIS_LOG"."ID"[NUMBER,22], "TKGM_SERVIS_LOG"."METOD_ADI"[VARCHAR2,100],
"TKGM_SERVIS_LOG"."PARAMETRELER"[VARCHAR2,4000], "TKGM_SERVIS_LOG"."KAYIT_TARIHI"[DATE,7],
"TKGM_SERVIS_LOG"."KAYDEDEN"[VARCHAR2,15], "TKGM_SERVIS_LOG"."HATA_MESAJI"[VARCHAR2,4000],
"TKGM_SERVIS_LOG"."SURE"[NUMBER,22], "VATANDASLIK_NO"[VARCHAR2,15], "TKGM_SERVIS_LOG"."SERVIS_TALEP"[LOB,4000],
"TKGM_SERVIS_LOG"."SERVIS_YANIT"[LOB,4000]
3 - "TKGM_SERVIS_LOG"."ID"[NUMBER,22], "TKGM_SERVIS_LOG"."METOD_ADI"[VARCHAR2,100],
"TKGM_SERVIS_LOG"."PARAMETRELER"[VARCHAR2,4000], "TKGM_SERVIS_LOG"."KAYIT_TARIHI"[DATE,7],
"TKGM_SERVIS_LOG"."KAYDEDEN"[VARCHAR2,15], "TKGM_SERVIS_LOG"."HATA_MESAJI"[VARCHAR2,4000],
"TKGM_SERVIS_LOG"."SURE"[NUMBER,22], "VATANDASLIK_NO"[VARCHAR2,15], "TKGM_SERVIS_LOG"."SERVIS_TALEP"[LOB,4000],
"TKGM_SERVIS_LOG"."SERVIS_YANIT"[LOB,4000]
4 - "TKGM_SERVIS_LOG"."ID"[NUMBER,22], "TKGM_SERVIS_LOG"."METOD_ADI"[VARCHAR2,100],
"TKGM_SERVIS_LOG"."PARAMETRELER"[VARCHAR2,4000], "TKGM_SERVIS_LOG"."KAYIT_TARIHI"[DATE,7],
"TKGM_SERVIS_LOG"."KAYDEDEN"[VARCHAR2,15], "TKGM_SERVIS_LOG"."HATA_MESAJI"[VARCHAR2,4000],
"TKGM_SERVIS_LOG"."SURE"[NUMBER,22], "VATANDASLIK_NO"[VARCHAR2,15], "TKGM_SERVIS_LOG"."SERVIS_TALEP"[LOB,4000],
"TKGM_SERVIS_LOG"."SERVIS_YANIT"[LOB,4000]

Note
-----
- dynamic statistics used: dynamic sampling (level=AUTO)
- automatic DOP: Computed Degree of Parallelism is 27

-------------------------------------------------------------------------------

/*****************************************************************
* Be aware that using SQL Tuning Advisor (STA) DBMS_SQLTUNE
* requires a license for the Oracle Tuning Pack.
* If you need to disable SQLT access to this functionality
* execute the following command connected as sysdba:
* SQL> EXEC SQLTXADMIN.sqlt$a.set_param('sql_tuning_advisor', 'N');
*****************************************************************/

 

 

You can Consider accepting the recommended SQL profile to use parallel execution for this statement and you can improve its Performance with this way.

execute dbms_sqltune.accept_sql_profile(task_name => 'sqlt_s67096_mem',task_owner => 'MEHMETSALIH', replace=> TRUE, profile_type =>DBMS_SQLTUNE.PX_PROFILE);

 

Or you can review other plans and apply them, if you observe the good performance, you can keep them, if not, you can drop or delete them again.

 

 

Do you want to learn Oracle Database Performance Tuning detailed, then read the following articles.

Performance Tuning and SQL Tuning Tutorial in the Oracle Database