Types of Sql Profiling

Tablespace Refresh/Re-org Steps

Steps 

Basic LOB files export using datapump

 How to export Basic LOB files export in parallel.

It is known limitation that LOBs of type basic files cannot be exported in parallel. 

Below is the syntax for executing concurrently in multiple threads for LOB Segments with multiple dumps. eg : 16 threads in background

Generate script as below for each and run in background from server.

expdp <username@<TNS> 

tables=<table name> 

dumpfile=Directory_name:dumpfile%U.dmp

logfile = Directory_name:logile_name1.log 

query=schema.tablename:' " where mod(dbms_rowid.rowid_block_number(rowid),16)=0" ' &

query=schema.tablename:' " where mod(dbms_rowid.rowid_block_number(rowid),16)=1" ' &

...

....

query=schema.tablename:' " where mod(dbms_rowid.rowid_block_number(rowid),16)=15" ' &

19c Perf Parameters

 For better performance Disable these parameters .

_optimizer_push_pred_cost_base  - FALSE

_push_join_predicate  - FALSE

 optimizer_adaptive_plans - FALSE

Statistics Information 

method_opt  -> ' For All Columns Size Auto'

granularity   ->    All

cascade => true

degree = AUTO

options => GATHER

Statistics related Bugs -19c

Bug 30664385 - High count of repetitive executions for sql_id 35c8afbgfm40c during incremental statistics gathering (Doc ID 30664385.8)

Excessive CPU Usage Hang (Process Spins) Performance Affected (General) Performance Of Query/ies Affected Waits for "ENQ: TX - ROW LOCK CONTENTION" Waits for "CPU + Wait for CPU"

DBMS_STATS

Description
This bug is only relevant when using Partitioned Tables
Large number of repetitive executionsfor sql_id 35c8afbgfm40c:
SELECT NVL(SUM(SPARE1), 0), COUNT(*) FROM WRI$_OPTSTAT_SYNOPSIS_HEAD$ H,
(SELECT MAX(ANALYZETIME) MA FROM WRI$_OPTSTAT_SYNOPSIS_HEAD$ WHERE BO# =
:B1
) V WHERE H.ANALYZETIME = V.MA AND H.BO# = :B1
during online incremental stats load.
Rediscovery:
High count of executions for sql_id 35c8afbgfm40c.

Workaround
There is no direct workaround.
Indirect one , sql_patch to improve plan for the query execution.

Alternate Sql Plan : 3690981172

Above Plan works even for non-incremental stats. Pleaes double check with Oracle before implementing in production.

Performance Analysis

Steps for performance analysis on Oracle Database - Manually using views ( V$/GV$/DBA_)

Oracle Version - 19c 

https://docs.oracle.com/database/121/ADMIN/cdb_mon.htm#ADMIN13719

To Collect information manually by views for Sys-metric related waits from PDB.

1. DBA_HIST_CON_SYSMETRIC_SUMM
2. V$CON_SYSMETRIC