Thank you very much for your thoughts.
You are right with different SQL_IDs. I realized that I omitted one bind variable in "the fast select" which then appears in v$session as CLIENT_IDENTIFIER (in the query it is this part 'SWToolsObjMgr_enu,SES0,41943060,SADMIN,000000fe5e5f0697:0,,Repository Applet,WT Repository Applet List View').
Anyway, my issue still persists but now only with one SQL_ID. I tried to display the plan as you suggested but there is apparently something wrong (don't know what).
Also when I checked the SQL_ID of running SQL it is different (this time it must be the same I checked it several times). When I execute the query from SQL Developer or from the application both are running slow and both give me the same SQL_ID which is '8wk9vg1c32z0m'. When I ran the explain plan for the query it shows different SQL_ID as you can see.
SQL> ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_10;
ALTER SESSION SET "_HASH_JOIN_ENABLED" = FALSE;
ALTER SESSION SET "_OPTIMIZER_SORTMERGE_JOIN_ENABLED" = FALSE;
Session altered.
SQL>
Session altered.
SQL>
Session altered.
SQL>
SQL> set serveroutput off
SQL> explain plan for
SELECT
T8.CONFLICT_ID,
T8.LAST_UPD,
T8.CREATED,
T8.LAST_UPD_BY,
T8.CREATED_BY,
T8.MODIFICATION_NUM,
T8.WS_SRC_ID,
T8.ASSOC_APPLET_NAME,
T8.AUTO_QUERYMODE,
T8.BGND_BMAP_NAME,
T8.BGND_BMAP_STYLE,
T8.BROWSER_CLASS,
T8.BUSCOMP_NAME,
T8.CLASS_NAME,
T8.COMMENTS,
T8.HTML_NUM_ROWS,
T8.HTML_POPUP_DM,
T8.HEIGHT,
T8.HELP_ID,
T8.UPG_ICL_PATH_CD,
T8.INACTIVE_FLG,
T8.INS_APPLET_NAME,
T8.INSERT_POSITION,
T5.NAME,
T8.MAIL_ADD_FLD_NAME,
T8.MAIL_TEMPLATE,
T8.MODULE_NAME,
T8.NAME,
T8.NODELETE,
T8.NOINSERT,
T8.NOMERGE,
T8.NOUPDATE,
T8.OBJ_LOCKED_LANG,
T8.OBJ_LOCKED_FLG,
T8.OBJ_LOCKED_BY,
T6.LOGIN,
T8.OBJ_LOCKED_DATE,
T8.POPUP_DIMENSION,
T8.PROJECT_ID,
T7.LOCKED_FLG,
T7.LOCKED_BY,
T7.NAME,
T7.UIFREEZE_FLG,
T7.UIFREEZE_BY,
T8.REPOSITORY_ID,
T8.SCRIPTED_FLG,
T8.SRCHSPEC,
T8.TEXT_STYLE_NAME,
T2.STRING_VALUE,
T4.STRING_VALUE,
T1.TITLE,
T8.TITLE_REF,
T8.TYPE,
T8.UPGD_ANCESTOR,
T8.UPG_BEHAVIOR, 48
T8.WIDTH,
T8.ROW_ID,
T8.WS_ID,
T8.WS_OBJ_VER,
T8.WS_DELETE_FLG,
T3.WS_SRC_ID,
T3.PAR_ROW_ID,
T3.ROW_ID,
T3.WS_ID,
T3.WS_OBJ_VER,
T3.WS_DELETE_FLG,
'SWToolsObjMgr_enu,SES0,41943060,SADMIN,000000fe5e5f0697:0,,Repository Applet,WT Repository Applet List View'
FROM
(SELECT TBL1.* FROM (SELECT * FROM SIEBEL.S_APPLET_INTL WHERE ((WS_ID = '1@21'))) TBL1 LEFT OUTER JOIN (SELECT * FROM SIEBEL.S_APPLET_INTL WHERE ((WS_ID = '1@21'))) TBL2 ON (TBL1.WS_SRC_ID = TBL2.WS_SRC_ID AND TBL1.WS_OBJ_VER < TBL2.WS_OBJ_VER) WHERE TBL2.WS_SRC_ID IS NULL AND TBL1.WS_DELETE_FLG='N') T1,
(SELECT TBL1.* FROM (SELECT * FROM SIEBEL.S_SYM_STR_INTL WHE 70 RE ((WS_ID = '1@21'))) TBL1 LEFT OUTER JOIN (SELECT * FROM SIEBEL.S_SYM_STR_INTL WHERE ((WS_ID = '1@21'))) TBL2 ON (TBL1.WS_SRC_ID = TBL2.WS_SRC_ID AND TBL1.WS_OBJ_VER < TBL2.WS_OBJ_VER) WHERE TBL2.WS_SRC_ID IS NULL AND TBL1.WS_DELETE_FLG='N') T2,
(SELECT TBL1.* FROM (SELECT * FROM SIEBEL.S_APPLET_RX WHERE ((WS_ID = '1@21'))) TBL1 LEFT OUTER JOIN (SELECT * FROM SIEBEL.S_APPLET_RX WHERE ((WS_ID = '1@21'))) TBL2 ON (TBL1.WS_SRC_ID = TBL2.WS_SRC_ID AND TBL1.WS_OBJ_VER < TBL2.WS_OBJ_VER) WHERE TBL2.WS_SR 71 C_ID IS NULL AND TBL1.WS_DELETE_FLG='N') T3,
(SELECT TBL1.* FROM (SELECT * FROM SIEBEL.S_SYM_STR_INTL WHERE ((WS_ID = '1@21'))) TBL1 LEFT OUTER JOIN (SELECT * FROM SIEBEL.S_SYM_STR_INTL WHERE ((WS_ID = '1@21'))) TBL2 ON (TBL1.WS_SRC_ID = TBL2.WS_SRC_ID AND TBL1.WS_OBJ_VER < TBL2.WS_OBJ_VER) WHERE TBL2.WS_SRC_ID IS NULL AND TBL1.WS_DELETE_FLG='N') T4,
(SELECT TBL1.* FROM (SELECT * FROM SIEBEL.S_LIST WHERE ((WS_ID = '1@21'))) TBL1 LEFT OUTER JOIN (SELECT * FROM SIEBEL.S_LIST WHERE ((WS_ID = '1@21'))) TBL2 ON (TBL1.WS_SRC_ID = TBL2.WS_SRC_ID AND TBL1.WS_OBJ_VER < TBL2.WS_OBJ_VER) WHERE TBL2.WS_SRC_ID IS NULL AND TBL1.WS_DELETE_FLG='N') T5,
SIEBEL.S_USER T6,
SIEBEL.S_PROJECT T7,
(SELECT TBL1.* FROM (SELECT * FROM SIEB 72 EL.S_APPLET WHERE ((WS_ID = '1@21'))) TBL1 LEFT OUTER JOIN (SELECT * FROM SIEBEL.S_APPLET WHERE ((WS_ID = '1@21'))) TBL2 ON (TBL1.WS_SRC_ID = TBL2.WS_SRC_ID AND TBL1.WS_OBJ_VER < TBL2.WS_OBJ_VER) WHERE TBL2.WS_SRC_ID IS NULL AND TBL1.WS_DELETE_FLG='N') T8
WHERE
T8.WS_SRC_ID = T1.APPLET_ID (+) AND T1.APP_CD (+) = 'STD' AND T1.INACTIVE_FLG (+) = 'N' AND T1.LANG_CD (+) = 'ENU' AND
T8.OBJ_LOCKED_BY = T6.PAR_ROW_ID (+) AND
T8.PROJECT_ID = T7.ROW_ID (+) AND
T8.WS_SRC_ID = T5.APPLET_ID (+) AND
T8.REPOSITORY_ID = T2.REPOSITORY_ID (+) AND T8.TITLE_REF = T2.SYM_STR_KEY (+) AND T2.APP_CD (+) = 'STD' AND T2.INACTIVE_FLG (+) = 'N' AND T2.LANG_CD (+) = 'ENU' AND
T8.REPOSITORY_ID = T4.REPOSITORY_ID (+) AND T8.TITLE_REF 73 = T4.SYM_STR_KEY (+) AND T4.APP_CD (+) = 'STD' AND T4.INACTIVE_FLG (+) = 'N' AND T4.LANG_CD (+) = 'ENU' AND
T8.WS_SRC_ID = T3.PAR_ROW_ID (+) AND
(T8.INACTIVE_FLG = 'N')
ORDER BY
T8.NAME /*+ opt_param('_hash_join_enabled', 'true') */
/*+ opt_param('_optimizer_sortmerge_join_enabled', 'true') */;
Explained.
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'BASIC LAST +OUTLINE'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 6dmr45mry95hb, child number 0
explain plan for SELECT T8.CONFLICT_ID, T8.LAST_UPD,
T8.CREATED, T8.LAST_UPD_BY, T8.CREATED_BY,
T8.MODIFICATION_NUM, T8.WS_SRC_ID, T8.ASSOC_APPLET_NAME,
T8.AUTO_QUERYMODE, T8.BGND_BMAP_NAME,
T8.BGND_BMAP_STYLE, T8.BROWSER_CLASS, T8.BUSCOMP_NAME,
T8.CLASS_NAME, T8.COMMENTS, T8.HTML_NUM_ROWS,
T8.HTML_POPUP_DM, T8.HEIGHT, T8.HELP_ID,
T8.UPG_ICL_PATH_CD, T8.INACTIVE_FLG, T8.INS_APPLET_NAME,
T8.INSERT_POSITION, T5.NAME, T8.MAIL_ADD_FLD_NAME,
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
T8.MAIL_TEMPLATE, T8.MODULE_NAME, T8.NAME,
T8.NODELETE, T8.NOINSERT, T8.NOMERGE, T8.NOUPDATE,
T8.OBJ_LOCKED_LANG, T8.OBJ_LOCKED_FLG, T8.OBJ_LOCKED_BY,
T6.LOGIN, T8.OBJ_LOCKED_DATE, T8.POPUP_DIMENSION,
T8.PROJECT_ID, T7.LOCKED_FLG, T7.LOCKED_BY,
T7.NAME, T7.UIFREEZE_FLG, T7.UIFREEZE_BY, T8
NOTE: cannot fetch plan for SQL_ID: 6dmr45mry95hb, CHILD_NUMBER: 0
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_p
lan)
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
22 rows selected.
I ran explain for the query once again but then I used "format all" and then I got the plan. I give the output into the next comment as it doesn't fit into this frame.