PID.sql
SELECT a.sid,a.module,a.OSUSER FROM v$session a,V$process b
WHERE a.paddr = b.addr AND b.spid=&pid1;
WHERE a.paddr = b.addr AND b.spid=&pid1;
whowhat.sql
SELECT SQL_TEXT FROM V$SQLAREA WHERE (ADDRESS, HASH_VALUE) IN
(SELECT SQL_ADDRESS, SQL_HASH_VALUE FROM V$SESSION WHERE SID = &sid_number);
(SELECT SQL_ADDRESS, SQL_HASH_VALUE FROM V$SESSION WHERE SID = &sid_number);
To find the request ID associated to the PID:
SELECT ORACLE_PROCESS_ID,ORACLE_SESSION_ID, REQUEST_ID FROM apps.fnd_concurrent_requests WHERE ORACLE_PROCESS_ID=&orcl_proc_id;
Query to check the total number of rows in the memory
SELECT SUBSTR(sql_text, 1, 60) "SQL Text",
rows_processed "Total Rows Processed",
ROUND((SYSDATE - TO_DATE(first_load_time, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60, 1) "Total Time (Min)",
TRUNC(rows_processed /((SYSDATE - TO_DATE(first_load_time, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60)) "Rows/Min",
TRUNC(rows_processed /((SYSDATE - TO_DATE(first_load_time, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60 * 60)) "Rows/Sec"
FROM v$sqlarea
WHERE sql_text LIKE 'INSERT INTO TABLE T_BLAH VALUES 1,2,3%'
AND open_versions > 0
AND rows_processed > 0;
SELECT SUBSTR(sql_text, 1, 60) "SQL Text",
rows_processed "Total Rows Processed",
ROUND((SYSDATE - TO_DATE(first_load_time, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60, 1) "Total Time (Min)",
TRUNC(rows_processed /((SYSDATE - TO_DATE(first_load_time, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60)) "Rows/Min",
TRUNC(rows_processed /((SYSDATE - TO_DATE(first_load_time, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60 * 60)) "Rows/Sec"
FROM v$sqlarea
WHERE sql_text LIKE 'INSERT INTO TABLE T_BLAH VALUES 1,2,3%'
AND open_versions > 0
AND rows_processed > 0;
rows_processed "Total Rows Processed",
ROUND((SYSDATE - TO_DATE(first_load_time, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60, 1) "Total Time (Min)",
TRUNC(rows_processed /((SYSDATE - TO_DATE(first_load_time, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60)) "Rows/Min",
TRUNC(rows_processed /((SYSDATE - TO_DATE(first_load_time, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60 * 60)) "Rows/Sec"
FROM v$sqlarea
WHERE sql_text LIKE 'INSERT INTO TABLE T_BLAH VALUES 1,2,3%'
AND open_versions > 0
AND rows_processed > 0;
- Where “SQL Text” is the sql query that you wish to monitor or tune.
Query to find the Hash Value for the above SQL ID and compare the explain plan
SELECT DISTINCT plan_hash_value, TIMESTAMP, to_char(TIMESTAMP, 'DD-MON-YYYY HH24:MI') FROM dba_hist_sql_plan WHERE sql_id='6ccqy1k2as35d' ORDER BY TIMESTAMP;
SELECT plan_hash_value, operation,OBJECT_NAME , cost, cardinality FROM DBA_HIST_SQL_PLAN WHERE plan_hash_value LIKE 'value u get from above';
SELECT DISTINCT plan_hash_value, TIMESTAMP, to_char(TIMESTAMP, 'DD-MON-YYYY HH24:MI') FROM dba_hist_sql_plan WHERE sql_id='6ccqy1k2as35d' ORDER BY TIMESTAMP;
SELECT plan_hash_value, operation,OBJECT_NAME , cost, cardinality FROM DBA_HIST_SQL_PLAN WHERE plan_hash_value LIKE 'value u get from above';
