Perf Scripts info

Perf Query info:

SQL id and object name which is having full table access


select sql_id,object_owner,object_name from V$SQL_PLAN where
operation='TABLE ACCESS' and
options='FULL' and
object_owner not in ('SYS','SYSTEM','DBSNMP');


SQL Queries which is having full table scans

SELECT s.object_owner, s.object_name,
(SELECT sql_text
FROM v$sqlarea sa
WHERE sa.address = s.address
AND sa.hash_value = s.hash_value) sqltext,
(SELECT executions
FROM v$sqlarea sa
WHERE sa.address = s.address
AND sa.hash_value = s.hash_value) no_of_full_scans
FROM v$sql_plan s
WHERE operation = 'TABLE ACCESS'
AND options = 'FULL'
AND object_owner not in ('SYS','SYSTEM','DBSNMP');

Partitioning & Types

 High Level Steps to consider Partitioning on a table:

• What is the kind of data and purpose ?
• What is the size of partition ?
• What type of partition would be optimal for this data ? Range/Hash/List etc
• Do we have to look for Sub Partitioning if each Partition size is Large

Partitioning Strategies: 2643743.1

(Oracle Database - Enterprise Edition - Version 11.2.0.1 and later

Information in this document applies to any platform.

PURPOSE

 Provides Navigation to Topics related to Types of Partitioning.

SCOPE

 This Document is to be available for those using Oracle Partitioning Feature. 

DETAILS

RANGE Partitioning

Range partitioning maps data to partitions based on ranges of values of the partitioning key that you establish for each partition. Range partitioning is the most common type of partitioning and is often used with dates.

Example of Script to Create a Range Partition Table (Doc ID 164874.1)
Example for Creating And Adding Table and Index Partitions To Ranged Partitioned Table (Doc ID 69715.1)

INTERVAL Partitioning

Interval partitioning is an extension of range partitioning.Interval partitioning instructs the database to automatically create partitions of a specified interval when data inserted into the table exceeds all of the existing range partitions.

Interval Partitioning Essentials - Common Questions - Top Issues (Doc ID 1479115.1)
How to Use Interval Partitioning with a Rolling Partition Window and Avoid ORA-14758 (Doc ID 1590833.1)

LIST Partitioning

List partitioning enables you to explicitly control how rows map to partitions by specifying a list of discrete values for the partitioning key in the description for each partition

LIST Partitioning Examples in Oracle 9i and above (Doc ID 149116.1)

HASH Partitioning

Hash partitioning maps data to partitions based on a hashing algorithm that Oracle applies to the partitioning key that you identify. The hashing algorithm evenly distributes rows among partitions, giving partitions approximately the same size.

Example of Script to Create a Hash Partition Table (Doc ID 164873.1)
How to Implement Hash Partitioning on IOT Tables in 9i & Above (Doc ID 165701.1) 
Rows Per Partition Skewed in Hash Partitioned Table (Doc ID 1068430.1)

REFERENCE Partitioning

Reference partitioning enables the partitioning of two tables that are related to one another by referential constraints. The partitioning key is resolved through an existing parent-child relationship, enforced by enabled and active primary key and foreign key constraints. The benefit of this extension is that tables with a parent-child relationship can be logically equipartitioned by inheriting the partitioning key from the parent table without duplicating the key columns.

Reference Partitioning Examples in Oracle 11G and above (Doc ID 761251.1)
How to Create Interval-Reference Partitioned Tables in Oracle 12c (Doc ID 1519042.1)

SYSTEM Partitioning

System partitioning enables application-controlled partitioning without having the database controlling the data placement.The database simply provides the ability to break down a table into partitions without knowing what the individual partitions are going to be used for. All aspects of partitioning have to be controlled by the application.

System Partitioning Examples in Oracle 11G and above (Doc ID 785462.1)

COMPOSITE Partitioning

Composite partitioning is a combination of the basic data distribution methods. A table is partitioned by one data distribution method and then each partition is further subdivided into subpartitions using a second data distribution method. All subpartitions for a given partition represent a logical subset of the data.Composite partitioning supports historical operations, such as adding new range partitions, but also provides higher degrees of potential partition pruning and finer granularity of data placement through subpartitioning.

11g new feature: Extended Composite Partitioning (Overview, Example and Use) (Doc ID 943567.1)
How to Create Interval-Reference Partitioned Tables in Oracle 12c (Doc ID 1519042.1)

Oracle Database - Enterprise Edition - Version 11.2.0.1 and later
Information in this document applies to any platform.

PURPOSE

 Provides Navigation to Topics related to Types of Partitioning.

SCOPE

 This Document is to be available for those using Oracle Partitioning Feature. 

DETAILS

RANGE Partitioning

Range partitioning maps data to partitions based on ranges of values of the partitioning key that you establish for each partition. Range partitioning is the most common type of partitioning and is often used with dates.

Example of Script to Create a Range Partition Table (Doc ID 164874.1)
Example for Creating And Adding Table and Index Partitions To Ranged Partitioned Table (Doc ID 69715.1)

INTERVAL Partitioning

Interval partitioning is an extension of range partitioning.Interval partitioning instructs the database to automatically create partitions of a specified interval when data inserted into the table exceeds all of the existing range partitions.

Interval Partitioning Essentials - Common Questions - Top Issues (Doc ID 1479115.1)
How to Use Interval Partitioning with a Rolling Partition Window and Avoid ORA-14758 (Doc ID 1590833.1)

LIST Partitioning

List partitioning enables you to explicitly control how rows map to partitions by specifying a list of discrete values for the partitioning key in the description for each partition

LIST Partitioning Examples in Oracle 9i and above (Doc ID 149116.1)

HASH Partitioning

Hash partitioning maps data to partitions based on a hashing algorithm that Oracle applies to the partitioning key that you identify. The hashing algorithm evenly distributes rows among partitions, giving partitions approximately the same size.

Example of Script to Create a Hash Partition Table (Doc ID 164873.1)
How to Implement Hash Partitioning on IOT Tables in 9i & Above (Doc ID 165701.1) 
Rows Per Partition Skewed in Hash Partitioned Table (Doc ID 1068430.1)

REFERENCE Partitioning

Reference partitioning enables the partitioning of two tables that are related to one another by referential constraints. The partitioning key is resolved through an existing parent-child relationship, enforced by enabled and active primary key and foreign key constraints. The benefit of this extension is that tables with a parent-child relationship can be logically equipartitioned by inheriting the partitioning key from the parent table without duplicating the key columns.

Reference Partitioning Examples in Oracle 11G and above (Doc ID 761251.1)
How to Create Interval-Reference Partitioned Tables in Oracle 12c (Doc ID 1519042.1)

SYSTEM Partitioning

System partitioning enables application-controlled partitioning without having the database controlling the data placement.The database simply provides the ability to break down a table into partitions without knowing what the individual partitions are going to be used for. All aspects of partitioning have to be controlled by the application.

System Partitioning Examples in Oracle 11G and above (Doc ID 785462.1)

COMPOSITE Partitioning

Composite partitioning is a combination of the basic data distribution methods. A table is partitioned by one data distribution method and then each partition is further subdivided into subpartitions using a second data distribution method. All subpartitions for a given partition represent a logical subset of the data.Composite partitioning supports historical operations, such as adding new range partitions, but also provides higher degrees of potential partition pruning and finer granularity of data placement through subpartitioning.

11g new feature: Extended Composite Partitioning (Overview, Example and Use) (Doc ID 943567.1)
How to Create Interval-Reference Partitioned Tables in Oracle 12c (Doc ID 1519042.1)

12c :  https://docs.oracle.com/database/121/VLDBG/GUID-6CE884AF-84A4-4E6A-A3EF-DCCEBCAB2DB2.htm#VLDBG00202

18c : https://docs.oracle.com/en/database/oracle/oracle-database/18/vldbg/partition-concepts.html#GUID-6CE884AF-84A4-4E6A-A3EF-DCCEBCAB2DB2

19c : https://docs.oracle.com/en/database/oracle/oracle-database/19/vldbg/partition-concepts.html#GUID-6CE884AF-84A4-4E6A-A3EF-DCCEBCAB2DB2

Master Note for Partitioning (Doc ID 1312352.1)

Purpose and Benefits/Advantages/Uses of Table Partitioning (Doc ID 1390871.1)

How Should I Partition My Tables? (Doc ID 1563143.1)

Oracle12C: Using SQLAccess Advisor (DBMS_ADVISOR) -- Partition Advisor (Doc ID 1567962.1)

12c Partitioning Enhancements, New Features (Doc ID 1568010.1)

11g Partitioning Enhancements (Doc ID 452447.1)

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.