It is very important to have 64bit unixODBC libraries path (/usr/lib64/libodbc.so) in LD_LIBRARY_PATH because we run RHEL x64 and Oracle 64-bit software.
Step 6 –
Create init<sid>.ora. In my case, I will call this dg4odbc (initdg4odbc.ora). The content should be like below.
HS_DB_NAME = H1X
HS_FDS_CONNECT_INFO = H1X <===== This is the DSN name that comes from step 2 /etc/odbc.ini
The Oracle session has requested and is waiting for multiple contiguous database blocks (up to DB_FILE_MULTIBLOCK_READ_COUNT) to be read into the SGA from the disk.
Full Table scans
Fast Full Index Scans
ACTIONS:
Optimize multi-block I/O by setting the parameter DB_FILE_MULTIBLOCK_READ_COUNT
Partition pruning to reduce the number of blocks visited
Consider the usage of multiple buffers pools and caches of frequently used indexes/tables in the KEEP pool
Optimize the SQL statement that initiated most of the waits. The goal is to minimize the number of physical and logical reads.
Should the statement access the data by a full table scan or index FFS? Would an index range or unique scan be more efficient?
Does the query use the right driving table?
Are the SQL predicates appropriate for hash or merge join?
If full scans are appropriate, can parallel query improve the response time?
The objective is to reduce the demands for both the logical and
physical I/Os, and this is best achieved through SQL and application tuning.
Make sure all statistics are representative of the actual data. Check the LAST_ANALYZED date.
REMARKS:
If an application that has been running fine for a while suddenly clocks a lot of time on the DB file scattered read event and there hasn’t been a code change, you might want to check to see if one or more indexes have been dropped or become unusable.
DB file scattered read Reference Note# 34558.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:
Index reads performance can be affected by slow I/O subsystem and/or poor database files layout, which results in a higher average
wait time
Actions:
Check indexes on the table to ensure that the right index is being used
Check the column order of the index with the WHERE clause of the Top SQL statements
Rebuild indexes with a high clustering factor
Use partitioning to reduce the number of blocks being visited
Make sure optimizer statistics are up to date
Relocate ‘hot’ datafiles
Consider the usage of multiple buffers pools and cache frequently used indexes/tables in the KEEP pool
Inspect the execution plans of the SQL statements that access data
through indexes
Is it appropriate for the SQL statements to access data through
index lookups?
Is the application an online transaction processing (OLTP) or decision support system (DSS)?
Would full table scans be more efficient?
Do the statements use the right driving table?
The optimization goal is to minimize both the number of logical and physical I/Os.
Remarks:
The Oracle process wants a block that is currently not in the SGA, and it is waiting for the database block to be read into the SGA from the disk.
Significant DB file sequential read wait time is most likely an application issue.
If the DBA_INDEXES.CLUSTERING_FACTOR of the index approaches the number of blocks in the table, then most of the rows in the table are ordered. This is desirable.
However, if the clustering factor approaches the number of rows in the table, it means the rows in the table are randomly ordered and thus it requires more I/Os to complete the operation. You can improve the index’s clustering factor by rebuilding the table so that rows are ordered according to the index key and rebuilding the index thereafter.
The OPTIMIZER_INDEX_COST_ADJ and OPTIMIZER_INDEX_CACHING initialization parameters can influence the optimizer to favor the operation of the nested loop and choose an index access path over a full table scan.
Tuning I/O-related waits for Note id 223117.1
DB file sequential read Reference Note# 34559.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:
The following columns are returned in the below query:
Owner schema.
Object name and type (TABLE, INDEX, etc.).
Name of the table this object is associated with. For E.g. indexes are associated with their parent tables.
Database space is occupied by the object in megabytes.
Tablespace where an object is stored.
The number of extents allocated for the object.
Size of the initial extent in bytes.
Total database size occupied by the parent table. E.g. for indexes, it will be the size of the parent * table plus the sizes of all the indexes on that table.
SELECT * FROM (
SELECT
owner, object_name, object_type, table_name, ROUND(bytes)/1024/1024 AS MB,
tablespace_name, extents, initial_extent,
ROUND(Sum(bytes/1024/1024) OVER (PARTITION BY table_name)) AS total_table_MB
FROM (
SELECT owner, segment_name AS object_name, 'TABLE' AS object_type,
segment_name AS table_name, bytes,
tablespace_name, extents, initial_extent
FROM dba_segments
WHERE segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
UNION ALL
SELECT i.owner, i.index_name AS object_name, 'INDEX' AS object_type,
i.table_name, s.bytes,
s.tablespace_name, s.extents, s.initial_extent
FROM dba_indexes i, dba_segments s
WHERE s.segment_name = i.index_name
AND s.owner = i.owner
AND s.segment_type IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')
UNION ALL
SELECT l.owner, l.column_name AS object_name, 'LOB_COLUMN' AS object_type,
l.table_name, s.bytes,
s.tablespace_name, s.extents, s.initial_extent
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.segment_name
AND s.owner = l.owner
AND s.segment_type = 'LOBSEGMENT'
UNION ALL
SELECT l.owner, l.column_name AS object_name, 'LOB_INDEX' AS object_type,
l.table_name, s.bytes,
s.tablespace_name, s.extents, s.initial_extent
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.index_name
AND s.owner = l.owner
AND s.segment_type = 'LOBINDEX'
)
WHERE owner in UPPER('&TABLE_NAME')
)
WHERE total_table_MB > 10
ORDER BY total_table_MB DESC, MB 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:
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:
