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