The Server is not able to service this request: [Socket:000445]Connection rejected, filter blocked Socket, weblogic.security.net.FilterException: [Security:090220]rule 2
The Server is not able to service this request: [Socket:000445]Connection rejected, filter blocked Socket, weblogic.security.net.FilterException
cd $FMW_HOME/user_projects/domains/EBS_domain_PROD/config
cp config.xml config.xml_org
erpr12.appsdba.info * * allow
Update deny to allow in the file config.xml
old
0.0.0.0/0 * * deny
New
0.0.0.0/0 * * allow
Bounce the admin server
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
SELECT a.tablespace_name,ROUND((c.total_blocksb.block_size)/1024/1024/1024,2)
"Total Size [GB]",ROUND((a.used_blocksb.block_size)/1024/1024/1024,2) "Used_size[GB]",
ROUND(((c.total_blocks-a.used_blocks)b.block_size)/1024/1024/1024,2) "Free_size[GB]",
ROUND((a.max_blocksb.block_size)/1024/1024/1024,2) "Max_Size_Ever_Used[GB]",
ROUND((a.max_used_blocksb.block_size)/1024/1024/1024,2) "MaxSize_ever_Used_by_Sorts[GB]" ,
ROUND((a.used_blocks/c.total_blocks)100,2) "Used Percentage"
FROM V$sort_segment a,dba_tablespaces b,(SELECT tablespace_name,SUM(blocks)
total_blocks FROM dba_temp_files GROUP by tablespace_name) c
WHERE a.tablespace_name=b.tablespace_name AND a.tablespace_name=c.tablespace_name;
Query to check TEMP USAGE : —————————————–
col name for a20
SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management
"ExtManag",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)", TO_CHAR(NVL(t.bytes,
0)/1024/1024,'99999,999.999') ||'/'||TO_CHAR(NVL(a.bytes/1024/1024, 0),'99999,999.999') "Used (M)",
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %"
FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes from dba_temp_files group by
tablespace_name) a,
(select tablespace_name, sum(bytes_cached) bytes from
v$temp_extent_pool group by tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY';
To Check the Percentage Usage of Temp Tablespace: —————————————————————–
select (s.tot_used_blocks/f.total_blocks)*100 as "percent used"
from (select sum(used_blocks) tot_used_blocks
from v$sort_segment where tablespace_name='TEMP') s,
(select sum(blocks) total_blocks
from dba_temp_files where tablespace_name='TEMP') f;
To find Sort Segment Usage by a particular User: ————————————————————-
SELECT s.username,s.sid,s.serial#,u.tablespace, u.contents, u.extents, u.blocks
FROM v$session s, v$sort_usage u
WHERE s.saddr=u.session_addr
order by u.blocks desc;
To find Total Free space in Temp Tablespace : ———————————————————
select tablespace_name, (free_blocks8)/1024/1024 FreeSpaceInGB,
(used_blocks8)/1024/1024 UsedSpaceInGB,
(total_blocks*8)/1024/1024 TotalSpaceInGB
from v$sort_segment where tablespace_name like '%TEMP%'
Get 10 sessions with the largest temp usage : —————————————————
cursor bigtemp_sids is
select * from (
select s.sid,
s.status,
s.sql_hash_value sesshash,
u.SQLHASH sorthash,
s.username,
u.tablespace,
sum(u.blocks*p.value/1024/1024) mbused ,
sum(u.extents) noexts,
nvl(s.module,s.program) proginfo,
floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60) lastcallet
from v$sort_usage u,
v$session s,
v$parameter p
where u.session_addr = s.saddr
and p.name = 'db_block_size'
group by s.sid,s.status,s.sql_hash_value,u.sqlhash,s.username,u.tablespace,
nvl(s.module,s.program),
floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60)
order by 7 desc,3)
where rownum < 11;
Identifying WHO is currently using TEMP Segments : ——————————————————————
SELECT sysdate,a.username, a.sid, a.serial#, a.osuser, (b.blocksd.block_size)/1048576 MB_used, c.sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c,
(select block_size from dba_tablespaces where tablespace_name='TEMP') d
WHERE b.tablespace = 'TEMP'
and a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
AND (b.blocksd.block_size)/1048576 > 1024
ORDER BY b.tablespace, 6 desc;
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
To calculate the Datafile Growth on a yearly basis:
select to_char(CREATION_TIME,’RRRR’) year,to_char(CREATION_TIME,’MM’) month,round(sum(bytes)/1024/1024/1024) gb
from v$datafile group by to_char(CREATION_TIME,’RRRR’),to_char(CREATION_TIME,’MM’) order by 1,2;
Through OEM :
Here are the steps to know the Database growth pattern for the last month/year using OEM
1) log in to OEM and Click on the Reports Tab
2) Navigate to Reports–>Storage–>Oracle Database Space Usage path and Click on the Oracle Database Space Usage link.
3) Select the Target database and here we are getting Oracle Database space usage for the last month.
4) Also we can get one year of Database growth by setting Set Time Period Button.
5) Also we can find Oracle Database Tablespace Monthly Space Usage by Navigating Reports–>Storage–>Oracle Database Space Usage path and clicking on the Oracle Database Tablespace Monthly Space Usage link.
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
1. How to Determine Which Manager Ran a Specific Concurrent Request?
col USER_CONCURRENT_QUEUE_NAME for a100
select b.USER_CONCURRENT_QUEUE_NAME from fnd_concurrent_processes a,
fnd_concurrent_queues_vl b, fnd_concurrent_requests c
where a.CONCURRENT_QUEUE_ID = b.CONCURRENT_QUEUE_ID
and a.CONCURRENT_PROCESS_ID = c.controlling_manager
and c.request_id = '&conc_reqid';
2. Concurrent manager status for a given sid?
col MODULE for a20
col OSUSER for a10
col USERNAME for a10
set num 10
col MACHINE for a20
set lines 200
col SCHEMANAME for a10
select s.sid,s.serial#,p.spid os_pid,s.status, s.osuser,s.username, s.MACHINE,s.MODULE,
s.SCHEMANAME,
s.action from gv$session s, gv$process p WHERE s.paddr = p.addr and s.sid = '&oracle_sid';
3. Find out request-id from Oracle_Process Id:
select REQUEST_ID,ORACLE_PROCESS_ID,OS_PROCESS_Id from apps.fnd_concurrent_requests where
ORACLE_PROCESS_ID='&a';
4. To find sid, serial# for a given concurrent request id?
set lines 200
SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID ,d.inst_id
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
gv$process c,
gv$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id = &Request_ID
AND a.phase_code = 'R';
5. To find the concurrent program name, phase code, and status code for a given request id?
6. To find the SQL query for a given concurrent request sid?
select sid,sql_text from gv$session ses, gv$sqlarea sql where
ses.sql_hash_value = sql.hash_value(+) and ses.sql_address = sql.address(+) and
ses.sid='&oracle_sid'
/
7. To find child requests
set lines 200
col USER_CONCURRENT_PROGRAM_NAME for a40
col PHASE_CODE for a10
col STATUS_CODE for a10
col COMPLETION_TEXT for a20
SELECT sum.request_id,req.PARENT_REQUEST_ID,sum.user_concurrent_program_name, DECODE
(sum.phase_code,'C','Completed',sum.phase_code) phase_code, DECODE(sum.status_code,'D',
'Cancelled' ,
'E', 'Error' , 'G', 'Warning', 'H','On Hold' , 'T', 'Terminating', 'M', 'No Manager' , 'X',
'Terminated', 'C', 'Normal', sum.status_code) status_code, sum.actual_start_date,
sum.actual_completion_date, sum.completion_text FROM apps.fnd_conc_req_summary_v sum,
apps.fnd_concurrent_requests req where req.request_id=sum.request_id and req.PARENT_REQUEST_ID =
'&parent_concurrent_request_id';
8. Cancelling Concurrent requests:
update fnd_concurrent_requests
set status_code='D', phase_code='C'
where request_id=&req_id;
9. Kill sessions program-wise
select 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' immediate;' from v$session where
MODULE like '';
10 . Concurrent Request running by SID
SELECT a.request_id,
d.sid as Oracle_SID,
d.serial#,
d.osuser,
d.process,
c.SPID as OS_Process_ID
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
gv$process c,
gv$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND d.sid = &SID;
11. Find out request-id from Oracle_Process Id:
select REQUEST_ID,ORACLE_PROCESS_ID,OS_PROCESS_Id from fnd_concurrent_requests where
ORACLE_PROCESS_ID='&a';
12. Oracle Concurrent Request Error Script (requests which were errored out)
SELECT a.request_id "Req Id"
,a.phase_code,a.status_code
, actual_start_date
, actual_completion_date
,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name "program"
FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_concurrent_programs_tl ctl
WHERE a.controlling_manager = b.concurrent_process_id
AND a.concurrent_program_id = c.concurrent_program_id
AND a.program_application_id = c.application_id
AND a.status_code = 'E'
AND a.phase_code = 'C'
AND actual_start_date > sysdate - 2
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND ctl.concurrent_program_id = c.concurrent_program_id
AND ctl.LANGUAGE = 'US'
ORDER BY 5 DESC;
13. Request submitted by User
SELECT
user_concurrent_program_name,
request_date,
request_id,
phase_code,
status_code
FROM
fnd_concurrent_requests fcr,
fnd_concurrent_programs_tl fcp,
fnd_responsibility_tl fr,
fnd_user fu
WHERE
fcr.CONCURRENT_PROGRAM_ID = fcp.concurrent_program_id
and fcr.responsibility_id = fr.responsibility_id
and fcr.requested_by = fu.user_id
and user_name = '&user'
AND actual_start_date > sysdate - 1
ORDER BY REQUEST_DATE Asc;
14. Concurrent Program enables trace
col User_Program_Name for a40
col Last_Updated_By for a30
col DESCRIPTION for a30
SELECT A.CONCURRENT_PROGRAM_NAME "Program_Name",
SUBSTR(A.USER_CONCURRENT_PROGRAM_NAME,1,40) "User_Program_Name",
SUBSTR(B.USER_NAME,1,15) "Last_Updated_By",
SUBSTR(B.DESCRIPTION,1,25) DESCRIPTION
FROM APPS.FND_CONCURRENT_PROGRAMS_VL A, APPLSYS.FND_USER B
WHERE A.ENABLE_TRACE='Y'
AND A.LAST_UPDATED_BY=B.USER_ID;
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Oracle Concurrent Processing – Version 12.2 and later
Running fnd_conc_clone.setup_clean manually is unsupported.
This API was designed for use internally and although Development has documented running this API in some setup notes it is only where it has been explicitly tested and for that specific setup.
For example, you will find some references to FND_CONC_CLONE.SETUP_CLEAN in the upgrade guide and other development-created documents. However, these are strategically placed.
Running this API outside of the specific places found in the official Oracle documentation (upgrade guide and other development documents) will cause problems and will break the functionality of your Oracle E-Business Suite system.
If fnd_conc_clone.setup_clean has been run, the only option to bring back the system to a stable and safe state will be to restore from a backup taken previously to run the API.
R12.2: When To Run fnd_conc_clone.setup_clean?
Ref:Doc ID 2130750.1
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
7. scan and list the disk using oracleasm: [root@asm dev]# oracleasm scandisks Reloading disk partitions: done Cleaning any stale ASM disks… Scanning system for ASM disks… [root@asm dev]# oracleasm listdisks DISK1 DISK2 [root@asm dev]#
8. asmcmd tool:
[oracle@asm ~]$ asmcmd ASMCMD> ASMCMD>
I. list the disk group ASMCMD> lsdg State Type Rebal Sector Logical_Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name MOUNTED EXTERN N 512 512 4096 4194304 30712 25860 0 25860 0 N DATA/ ASMCMD>
II. list datafile,controlfile
ASMCMD> cd DATA/ ASMCMD> ls ASM/ ORAASM/ orapwasm ASMCMD> cd ORAASM ASMCMD> ls 86B637B62FE07A65E053F706E80A27CA/ CONTROLFILE/ D47CB418C2B91B66E053867EA8C0C5A0/ D47CEAB72C2F2513E053867EA8C0538D/ DATAFILE/ ONLINELOG/ PARAMETERFILE/ TEMPFILE/ ASMCMD> cd DATAFILE ASMCMD> ls SYSAUX.258.1092813853 SYSTEM.257.1092813819 UNDOTBS1.259.1092813869 USERS.260.1092813869 ASMCMD> pwd +DATA/ORAASM/DATAFILE ASMCMD>
9. check css,has status:
[oracle@asm ~]$ crsctl check has CRS-4638: Oracle High Availability Services is online [oracle@asm ~]$ crsctl check css CRS-4529: Cluster Synchronization Services is online [oracle@asm ~]$
10. check has version:
[oracle@asm ~]$ crsctl query has releaseversion Oracle High Availability Services release version on the local node is [19.0.0.0.0] [oracle@asm ~]$ crsctl query has softwareversion Oracle High Availability Services version on the local node is [19.0.0.0.0] [oracle@asm ~]$
11. asmca —> GUI for create and manage diskgroup:
12. KFOD – discovering disks with the help of asm disk strings: KFED – discovering disk headers
13. check olr and ocr location : [oracle@asm ~]$ ocrcheck Status of Oracle Cluster Registry is as follows : Version : 4 Total space (kbytes) : 491684 Used space (kbytes) : 82368 Available space (kbytes) : 409316 ID : 786336035 Device/File Name : /u01/app/grid/cdata/localhost/local.ocr Device/File integrity check succeeded
Device/File not configured
Device/File not configured
Device/File not configured
Device/File not configured
Cluster registry integrity check succeeded
Logical corruption check bypassed due to non-privileged user
[oracle@asm ~]$ [oracle@asm ~]$ ocrcheck -local Status of Oracle Local Registry is as follows : Version : 4 Total space (kbytes) : 491684 Used space (kbytes) : 83496 Available space (kbytes) : 408188 ID : 476403298 Device/File Name : /u01/app/oracle/crsdata/asm/olr/asm_19.olr Device/File integrity check succeeded
Local registry integrity check succeeded
Logical corruption check bypassed due to non-privileged user
[oracle@asm ~]$ ocrcheck -local -config Oracle Local Registry configuration is : Device/File Name : /u01/app/oracle/crsdata/asm/olr/asm_19.olr [oracle@asm ~]$
14. Check the asm status in sysasm admin:
[oracle@asm ~]$ sqlplus / as sysasm
SQL*Plus: Release 19.0.0.0.0 – Production on Sun Jan 2 17:57:52 2022 Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production Version 19.3.0.0.0
15. check instance type:
SQL> show parameter INSTANCE_TYPE
NAME TYPE VALUE ———————————— ———– —————————— instance_type string ASM
16. spfile location:
SQL> show parameter spfile
NAME TYPE VALUE ———————————— ———– —————————— spfile string +DATA/ASM/ASMPARAMETERFILE/reg istry.253.1092745689
17. pfile creation: SQL> create pfile from spfile;
File created.
SQL> exit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production Version 19.3.0.0.0 [oracle@asm ~]$ cd $ORACLE_HOME/dbs [oracle@asm dbs]$ ls ab_+ASM.dat hc_+ASM.dat init+ASM.ora init.ora [oracle@asm dbs]$ cat init+ASM.ora +ASM.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from in memory value .asm_diskstring=’/dev/oracleasm/disks’ .asm_power_limit=1 .large_pool_size=12M .remote_login_passwordfile=’EXCLUSIVE’ [oracle@asm dbs]$ sqlplus / as sysasm
SQL*Plus: Release 19.0.0.0.0 – Production on Sun Jan 2 18:01:05 2022 Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production Version 19.3.0.0.0
18. instance type and status:
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS —————- ———— +ASM STARTED
SQL> exit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production Version 19.3.0.0.0 [oracle@asm dbs]$ crsctl stat res -t ——————————————————————————– Name Target State Server State details ——————————————————————————– Local Resources ——————————————————————————– ora.DATA.dg ONLINE ONLINE asm STABLE ora.LISTENER.lsnr ONLINE ONLINE asm STABLE ora.asm ONLINE ONLINE asm Started,STABLE ora.ons OFFLINE OFFLINE asm STABLE ——————————————————————————– Cluster Resources ——————————————————————————– ora.cssd 1 ONLINE ONLINE asm STABLE ora.diskmon 1 OFFLINE OFFLINE STABLE ora.evmd 1 ONLINE ONLINE asm STABLE ora.oraasm.db 1 ONLINE ONLINE asm Open,HOME=/u01/app/o racle/product/19.0.0 /dbhome_1,STABLE ——————————————————————————– [oracle@asm dbs]$
19. ASMCMD check datafile and controlfile:
[oracle@asm dbs]$ asmcmd ASMCMD> ASMCMD> ASMCMD> ASMCMD> lsdg State Type Rebal Sector Logical_Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name MOUNTED EXTERN N 512 512 4096 4194304 30712 25852 0 25852 0 N DATA/ ASMCMD> cd +DATA/oraasm/datafile ASMCMD> ls SYSAUX.258.1092813853 SYSTEM.257.1092813819 UNDOTBS1.259.1092813869 USERS.260.1092813869 ASMCMD> ls -l Type Redund Striped Time Sys Name DATAFILE UNPROT COARSE JAN 02 17:00:00 Y SYSAUX.258.1092813853 DATAFILE UNPROT COARSE JAN 02 17:00:00 Y SYSTEM.257.1092813819 DATAFILE UNPROT COARSE JAN 02 17:00:00 Y UNDOTBS1.259.1092813869 DATAFILE UNPROT COARSE JAN 02 17:00:00 Y USERS.260.1092813869 ASMCMD>
ASMCMD> cd +DATA/oraasm/parameterfile
ASMCMD> ls -l Type Redund Striped Time Sys Name PARAMETERFILE UNPROT COARSE JAN 02 17:00:00 Y spfile.274.1092815069 ASMCMD>
ASMCMD> ls -l Type Redund Striped Time Sys Name ASMPARAMETERFILE UNPROT COARSE DEC 31 12:00:00 Y REGISTRY.253.1092745689 ASMCMD> pwd +DATA/ASM/ASMPARAMETERFILE ASMCMD>
20. query to check asm_disk and asm disk_group:
SQL> select disk_number,name,path,header_status,mode_status,state,total_mb,free_mb from v$asm_disk;
DISK_NUMBER NAME PATH HEADER_STATU MODE_ST STATE TOTAL_MB FREE_MB —————————————————————————————————- 0 DATA_0000 /dev/oracleasm/disks/DISK1 MEMBER ONLINE NORMAL 20476 17228
1 DATA_0001 /dev/oracleasm/disks/DISK2 MEMBER ONLINE NORMAL 10236 8624
SQL> select group_number,name,state,type from v$asm_diskgroup;
GROUP_NUMBER NAME STATE TYPE ———— —————————— ———– —— 1 DATA MOUNTED EXTERN
SQL>
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
1. Check the status of Notifications: ====================================
col RECIPIENT_ROLE format a20
col FROM_USER format a20
col TO_USER format a20
set lines 170
select NOTIFICATION_ID,MESSAGE_TYPE,MESSAGE_NAME,RECIPIENT_ROLE,STATUS,FROM_USER,TO_USER from wf_notifications where NOTIFICATION_ID=‘&1′;
2.Check the status of Workflow components: =========================================
SELECT component_name as Component, component_status as Status FROM fnd_svc_components
3. To check whether notification is present: =========================================
select recipient_role,notification_id,status,mail_status from wf_notifications where recipient_role like ‘&user_name’;
The e-mail notification is sent only if all of the following is true.
Notification status is OPEN or CANCELED
Notification mail_status is MAIL or INVALID
4. Check Recipient role has a valid e-mail address and notification preference MAIL% =========================================
SELECT email_address, nvl(WF_PREF.get_pref(name, ‘MAILTYPE’), notification_preference)
FROM wf_roles
WHERE name = ‘&recipient_role’;
Recipient can receive email notification only if
notification preference is not set ‘QUERY’ / ‘DISABLED’ / ‘SUMMARY’ / ‘SUMHTML’ &
recipient has valid email address
5.WF_DEFERRED Queue volume: ===========================
col corrid format a60
set lines 130
set pages 100
select NVL(substr(wfe.corrid,1,50),’NULL – No Value’) corrid, decode(wfe.state,0,’0 = Ready’,1,’1 = Delayed’,2,’2 = Retained’,
3,’3 = Exception’,to_char(substr(wfe.state,1,12))) State,count(*) COUNT
from applsys.wf_deferred wfe group by wfe.corrid, wfe.state;
6.WF_NOTIFICATION_OUT Queue volume: ===================================
col corrid format a60
set lines 130
set pages 100
select NVL(substr(wfe.corrid,1,50),’NULL – No Value’) corrid, decode(wfe.state,0,’0 = Ready’,1,’1 = Delayed’,2,’2 = Retained’,
3,’3 = Exception’,to_char(substr(wfe.state,1,12))) State,count(*)COUNT
from applsys.wf_notification_out wfe group by wfe.corrid, wfe.state;
Top 10 by Buffer Gets
=====================
set linesize 1000
set pagesize 1000
col sql for a70
set long 5000
col hash_value for 9999999999999999999999
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
buffer_gets, executions, buffer_gets/executions “Gets/Exec”,
hash_value,address
FROM GV$SQLAREA
WHERE buffer_gets > 10000
ORDER BY buffer_gets DESC)
WHERE rownum <=10
;
==================================================================================================
Top 10 by Physical Reads
========================
set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
disk_reads, executions, disk_reads/executions “Reads/Exec”,
hash_value,address
FROM V$SQLAREA
WHERE disk_reads > 1000
ORDER BY disk_reads DESC)
WHERE rownum <=10;
==================================================================================================
Top 10 by Executions
====================
set linesize 1000
set pagesize 1000
col sql for a70
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
executions, rows_processed, rows_processed/executions “Rows/Exec”,
hash_value,address
FROM GV$SQLAREA
WHERE executions > 100
ORDER BY executions DESC)
WHERE rownum <=10;
==================================================================================================
Top 10 by Parse Calls:
========================
set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
parse_calls, executions, hash_value,address
FROM V$SQLAREA
WHERE parse_calls > 1000
ORDER BY parse_calls DESC)
WHERE rownum <=10
;
=================================================================================================
Top 10 by Sharable Memory:
========================
set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
sharable_mem, executions, hash_value,address
FROM V$SQLAREA
WHERE sharable_mem > 1048576
ORDER BY sharable_mem DESC)
WHERE rownum <=10
;
==================================================================================================
Top 10 by Version Count:
========================
set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
version_count, executions, hash_value,address
FROM V$SQLAREA
WHERE version_count > 20
ORDER BY version_count DESC)
WHERE rownum <=10
;
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
How to find the bind variable values of the SQL statement using SQLID?
SELECT sql_id, b.name BIND_NAME, b.value_string BIND_STRING from gv$sql t
JOIN gv$sql_bind_capture b USING (sql_id) WHERE b.value_string IS NOT NULL
AND sql_id ='&SQLID';
Additional script which may be helpful at a later point in time:
To know the last executed query in the oracle and its hash value,sqlid
SELECT * FROM v$SQLTEXT_WITH_NEWLINES WHERE address =
(SELECT prev_sql_addr FROM v$session WHERE audsid = userenv('SESSIONID'))
ORDER BY piece;
To know the SQL plan history of a sql using the sqlid:
select * from TABLE(DBMS_XPLAN.DISPLAY_AWR('&sqlid'));
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
