Resolving common Oracle Wait Events using the Wait Interface

Wait Event:

DB file scattered reads

Possible Causes:

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:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Resolving common Oracle Wait Events using the Wait Interface

Wait Event:

DB file sequential reads

Possible Causes:

Use of an unselective index
Fragmented Indexes
High I/O on a particular disk or mount point
Bad application design
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:

Oracle Zero Downtime Migration 21c

Zero Downtime Migration (ZDM) 21c is available for download! ZDM is Oracle’s premier solution for moving your on-premises Oracle Database workloads to Oracle Cloud, supporting a variety of Oracle Database versions as the source and most Oracle Cloud Database Services as targets. Zero Downtime Migration 21c enhances the existing functionality by adding the long-requested Logical Migration workflow, which provides even more zero downtime migration choices!

For more reference: blogs.oracle.com click the below link

Oracle Zero Downtime Migration 21c

Logical Migration

In addition to the Physical Migration workflow which has been supported since the first version of ZDM (see for example: https://blogs.oracle.com/maa/announcing-oracle-zero-downtime-migration-release-197), ZDM 21c offers a Logical Migration to the Oracle Cloud in eight simple steps, which can be scheduled and monitored as needed. The Logical Migration workflow allows you to migrate to Oracle Autonomous Database leveraging ZDM automation.

You can leverage two logical migration workflows with different methodologies:

Logical Offline Migration
- Data Pump and Database Links based methodology
- Data Pump and Backup Location based methodology
Logical Online Migration
- GoldenGate, Data Pump and Database Links based methodology
- GoldenGate, Data Pump and Backup Location based methodology

Exadata On-Premises Support

Oracle ZDM 21c includes Exadata Database Machine as a new target. You can now migrate your current database deployments to an on-premises Exadata Database Machine, leveraging the full automation and power of ZDM. Migration to Exadata On-Premises is only supported via ZDM’s physical migration workflow.

Autonomous Database Support

Oracle ZDM 21c can now migrate databases directly to Autonomous Database using any of the logical migration workflows. Supported targets are:

Autonomous Transaction Processing (Shared & Dedicated)
Autonomous Data Warehouse (Shared & Dedicated)
Autonomous Database on Exadata Cloud at Customer

Non-CDB to CDB Migration Support

Non-CDB to CDB Migration is now supported in both Oracle ZDM’s physical and logical migration workflows, providing you with more migration flexibility via a simplified migration process that includes the Non-CDB to PDB conversion and hence, a wider choice of cloud targets.

Pre-Migration Advisor

A pre-migration advisor that evaluates the source database for feature compatibility and other limits is now included in the logical migration workflow only. You should leverage this advisor in order to better prepare for your database migration journey.

Support for Database Links

Logical Database Migration offers the possibility to establish direct Database Links between the source and the target, thus avoiding the need for a backup location as part of the migration workflow. Support for Database Links is optional, as you can also leverage a backup location such as Object Storage, NFS or the Recovery Appliance for your migration. Database Links based migration is not supported for Autonomous Database Dedicated.

Lighter ZDM Home

The Oracle ZDM software home is now lighter and more compact. Users will notice the difference already when downloading the binaries. ZDM installation and deployment has also been optimized and trimmed, allowing for an even faster instantiation of your first migration.

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:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

DATABASE INFORMATION SCRIPTS

1)Get ACL details in the database.

2)How to Find the optimal undo retention size in the database.

3)Find who locked your account.

4)Find active transactions in db.

5)Archive generation per hour.

6)Find duplicate rows in the table.

7)Database growth per month.

8)Generate resize datafile script.

9)Get database uptime.

10)Scn to Timestamp and Timestamp to Scn.

11)Get Database incarnation information in the database.

12)How to get CPU memory info in the server.

13)Get redo log member info.

14)Get DDL of all tablespaces.

15)How to purge AWR old snapshot details in database.

16)Generate Bulk AWR reports in the database.

17)How to find current redo log size in database.

18)View hidden parameters.

19)Get database link details in the database.

20)How to Modify window_size/ window baseline size.

21)Get DDL of privileges granted to user.

22)Get timezone information in the database.

23)How to View / Modify AWR retention period.

24)Check the Character set info of the database.

25)Check Installed RDBMS components.

26)How to Get Alert log location in db.

27)Get installed SQL patches in the database.

28)GET INDEX USAGE.

29)Get sql query to html format.

30)Row count of all the tables of a schema.

31)Disable or enable all triggers of schema.

32)Get the size of the database.

33)How to clean up orphaned data pump jobs.

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:

DATABASE INFORMATION SCRIPTS

1)Get ACL details in the database.

2)How to Find the optimal undo retention size in the database.

3)Find who locked your account.

4)Find active transactions in db.

5)Archive generation per hour.

6)Find duplicate rows in the table.

7)Database growth per month.

8)Generate resize datafile script.

9)Get database uptime.

10)Scn to Timestamp and Timestamp to Scn.

11)Get Database incarnation information in the database.

12)How to get CPU memory info in the server.

13)Get redo log member info.

14)Get DDL of all tablespaces.

15)How to purge AWR old snapshot details in database.

16)Generate Bulk AWR reports in the database.

17)How to find current redo log size in database.

18)View hidden parameters.

19)Get database link details in the database.

20)How to Modify window_size/ window baseline size.

21)Get DDL of privileges granted to user.

22)Get timezone information in the database.

23)How to View / Modify AWR retention period.

24)Check the Character set info of the database.

25)Check Installed RDBMS components.

26)How to Get Alert log location in db.

27)Get installed SQL patches in the database.

28)GET INDEX USAGE.

29)Get sql query to html format.

30)Row count of all the tables of a schema.

31)Disable or enable all triggers of schema.

32)Get the size of the database.

33)How to clean up orphaned data pump jobs.

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:

Archive generation per hour

set lines 299
SELECT TO_CHAR(TRUNC(FIRST_TIME),’Mon DD’) “DG Date”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’00’,1,0)),’9999′) “12AM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’01’,1,0)),’9999′) “01AM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’02’,1,0)),’9999′) “02AM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’03’,1,0)),’9999′) “03AM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’04’,1,0)),’9999′) “04AM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’05’,1,0)),’9999′) “05AM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’06’,1,0)),’9999′) “06AM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’07’,1,0)),’9999′) “07AM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’08’,1,0)),’9999′) “08AM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’09’,1,0)),’9999′) “09AM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’10’,1,0)),’9999′) “10AM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’11’,1,0)),’9999′) “11AM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’12’,1,0)),’9999′) “12PM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’13’,1,0)),’9999′) “1PM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’14’,1,0)),’9999′) “2PM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’15’,1,0)),’9999′) “3PM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’16’,1,0)),’9999′) “4PM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’17’,1,0)),’9999′) “5PM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’18’,1,0)),’9999′) “6PM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’19’,1,0)),’9999′) “7PM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’20’,1,0)),’9999′) “8PM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’21’,1,0)),’9999′) “9PM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’22’,1,0)),’9999′) “10PM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’23’,1,0)),’9999′) “11PM”
FROM V$LOG_HISTORY
GROUP BY TRUNC(FIRST_TIME)
ORDER BY TRUNC(FIRST_TIME) DESC
/

Archive generation per hour

Find duplicate rows in table

— Reference metalink id – 332494.1
— Save as duplicate.sql and run as @duplicate.sql
REM This is an example SQL*Plus Script to detect duplicate rows from
REM a table.
REM
set echo off
set verify off heading off
undefine t
undefine c
prompt
prompt
prompt Enter name of table with duplicate rows
prompt
accept t prompt ‘Table: ‘
prompt
select ‘Table ‘||upper(‘&&t’) from dual;
describe &&t
prompt
prompt Enter name(s) of column(s) which should be unique. If more than
prompt one column is specified, you MUST separate with commas.
prompt
accept c prompt ‘Column(s): ‘
prompt
select &&c from &&t
where rowid not in (select min(rowid) from &&t group by &&c)
/

Database growth per month

select to_char(creation_time, ‘MM-RRRR’) “Month”, sum(bytes)/1024/1024/1024 “Growth in GB
from sys.v_$datafile
where to_char(creation_time,’RRRR’)=’&YEAR_IN_YYYY_FORMAT’
group by to_char(creation_time, ‘MM-RRRR’)
order by to_char(creation_time, ‘MM-RRRR’);

Tag Archives: Zero Downtime Migration 21c

DB file scattered reads

DB file sequential reads

Actions:

Remarks:

Logical Migration

Exadata On-Premises Support

Autonomous Database Support

Non-CDB to CDB Migration Support

Pre-Migration Advisor

Support for Database Links

Lighter ZDM Home