See SQL executions, invalidations, parse_calls.

See SQL invalidations

First step.

Clear shared pool and Buffer Cache.

ALTER SYSTEM FLUSH SHARED_POOL;

ALTER SYSTEM FLUSH BUFFER_CACHE;

Second step.

Check current status

SELECT  sql_text, executions, parse_calls,  invalidations 

  FROM V$SQL 

 WHERE sql_text like 'MERGE into SGA_W_IPN_SUBSCRIBER%';

SELECT sql_text, 

       SUM(executions) executions, 

       SUM(parse_calls) parse_calls, 

       SUM(invalidations) invalidations

  FROM V$SQL 

 WHERE sql_text like 'MERGE into SGA_W_IPN_SUBSCRIBER%'

 GROUP BY sql_text

SELECT sql_id, sql_text, invalidations 

  FROM (

       SELECT sql_id, 

              SUBSTR(sql_text, 1, 80) sql_text, 

              invalidations

         FROM V$SQLAREA

        WHERE 1=1

        ORDER BY invalidations DESC 

       )

WHERE ROWNUM < 11;

Increase oracle memory

====================
General Oracle memory on Linux
====================

1. Increase /dev/shm in Linux
as root

vi  /etc/fstab

from

tmpfs /dev/shm tmpfs defaults,size=4096M 0 0

to

tmpfs /dev/shm tmpfs defaults,size=9216M 0 0

17408M for 17Gb

25600M for 25Gb

mount -o remount /dev/shm

df -hP | grep shm

tmpfs                          9.0G   39M  9.0G   1% /dev/shm

(or systemctl daemon-reload)

2. Backup oracle spfile

as oracle

cp /software/oracle/admin/igt/pfile/spfileigt.ora /software/oracle/admin/igt/pfile/spfileigt.ora_YYYYMMDD

sqlplus / as sysdba

SQL> show parameter spfile

NAME      TYPE      VALUE

--------- --------- -------------------------------------

spfile    string    /software/oracle/19/dbhome_1/dbs/spfileigt.ora

                                                 

CREATE PFILE='/software/oracle/admin/igt/pfile/pfileigt.ora' FROM SPFILE='/software/oracle/admin/igt/pfile/spfileigt.ora';

!cp /software/oracle/admin/igt/pfile/pfileigt.ora /software/oracle/admin/igt/pfile/pfilei!cp gt.ora_YYYYMMDD

3. Get current status

COL name for A30

COL value for A30
SELECT name, value FROM V$PARAMETER WHERE name like 'memory%';

NAME                           VALUE

------------------------------ ---------------

memory_target                  2147483648

memory_max_target              2147483648

Linux server has 64Gb memory

oracle@my_server:~>% free -m

      total    used    free   shared  buff/cache   available

Mem:  64265   15031    8500     4614       40733       44232

Swap: 16383       0   16383

4. Increase of Linux Cluster - scp the spfileigt.ora file to the other node

--4096M

ALTER SYSTEM SET MEMORY_MAX_TARGET=4096M scope=spfile;

ALTER SYSTEM SET MEMORY_TARGET=4096M scope=spfile;

--8192M

ALTER SYSTEM SET MEMORY_MAX_TARGET=8192M scope=spfile;

ALTER SYSTEM SET MEMORY_TARGET=8192M scope=spfile;

16384M - 16Gb

ALTER SYSTEM SET MEMORY_MAX_TARGET=16384M scope=spfile;

ALTER SYSTEM SET MEMORY_TARGET=16384M scope=spfile;

16384M - 16Gb

ALTER SYSTEM SET MEMORY_MAX_TARGET=16384M scope=spfile;

ALTER SYSTEM SET MEMORY_TARGET=16384M scope=spfile;

24576M - 32Gb

ALTER SYSTEM SET MEMORY_MAX_TARGET=32768M scope=spfile;

ALTER SYSTEM SET MEMORY_TARGET=32768M scope=spfile;

36864M - 36Gb

ALTER SYSTEM SET MEMORY_MAX_TARGET=36864M scope=spfile;

ALTER SYSTEM SET MEMORY_TARGET=36864M scope=spfile;

43008M - 42Gb

ALTER SYSTEM SET MEMORY_MAX_TARGET=43008M scope=spfile;

ALTER SYSTEM SET MEMORY_TARGET=43008M scope=spfile;

49152M - 48Gb

ALTER SYSTEM SET MEMORY_MAX_TARGET=49152M scope=spfile;

ALTER SYSTEM SET MEMORY_TARGET=49152M scope=spfile;

65536M - 64Gb

ALTER SYSTEM SET MEMORY_MAX_TARGET=65536M scope=spfile;

ALTER SYSTEM SET MEMORY_TARGET=65536M scope=spfile;

4. In case of a cluster - scp the spfileigt.ora file to the other node.

scp /software/oracle/admin/igt/pfile/spfileigt.ora oracle@srv-1-aps-1b:/software/oracle/admin/igt/pfile/spfileigt.ora 

5. Restart Oracle

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP;

============

Theory

============

MEMORY_MAX_TARGET - The max memory that can be allocated form host to Oracle. It is an initialization parameter. Changing this value requires a restart.

MEMORY_TARGET - This is a dynamic parameter, It can be changed without restart to the database.

Both are used for automatic PGA and SGA memory sizing.

For example:

By setting memory_max_target=20G you allow your instance to get hold of 20G RAM. 

At this stage if you set second parameter memory_target=15G that means you are instructing oracle instance to only use 15G out of 20G RAM. 

If you do not set memory_target parameter then oracle will use 20G or RAM.

The remaining 5G are still occupied by oracle and are not in use. OS can not use this until you shutdown the instance.

Why we need memory_max_target when we have memory_target?

Having this option to adjust memory_target dynamically you can find what is the suitable memory target for the particluar database, without restarts.

By Example:

ALTER SYSTEM SET MEMORY_MAX_TARGET=20G SCOPE=SPFILE;

SHUTDOWN IMMEDIATE;

STARTUP;

ALTER SYSTEM SET MEMORY_TARGET=10G SCOPE=BOTH;

Or

ALTER SYSTEM SET MEMORY_TARGET=10G;

Read Golden gate dirdat file by example

 =====================

General

=====================
Read Golden gate dirdat file by example using logdump utility

Run logdump utility

oracle@my_server:/software/ogg/191>% ./logdump

Oracle GoldenGate Log File Dump Utility for Oracle

Version 19.1.0.0.1 OGGCORE_19.1.0.0.0_PLATFORMS_190524.2201

Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.

2022-09-08 05:25:11  WARNING OGG-10173  (GLOBALS) line 3: Parsing error, [SYSLOG] is deprecated.

Open a file

Logdump 25 >open /software/ogg/191/dirdat/01/out/ei000000547

Current LogTrail is /software/ogg/191/dirdat/01/out/ei000000547

Set display details on

Logdump 26 >ghdr on

Logdump 27 >detail on

Logdump 32 >fileheader on

Logdump 34 >detail data

Read next record. It would be File Header

Logdump 36 >n

2022/09/08 02:11:48.956.534 FileHeader           Len  1779 RBA 0

Name: *FileHeader*

 3000 0343 3000 0008 4747 0d0a 544c 0a0d 3100 0002 | 0..C0...GG..TL..1...

 0007 3200 0004 2000 0000 3300 0008 02f3 0e54 6873 | ..2... ...3......Ths

 0976 3400 002e 002c 7572 693a 4445 5241 5456 4442 | .v4....,uri:DERATVDB

 3030 3030 313a 3a73 6f66 7477 6172 653a 6f67 673a | 00001::software:ogg:

 3139 313a 4558 545f 495f 3031 3600 002d 002b 2f73 | 191:EXT_I_016..-.+/s

 6f66 7477 6172 652f 6f67 672f 3139 312f 6469 7264 | oftware/ogg/191/dird

 6174 2f30 312f 6f75 742f 6569 3030 3030 3030 3534 | at/01/out/ei00000054

GroupID x30 '0' TrailInfo        Info x00  Length  835

 3000 0343 3000 0008 4747 0d0a 544c 0a0d 3100 0002 | 0..C0...GG..TL..1...

 0007 3200 0004 2000 0000 3300 0008 02f3 0e54 6873 | ..2... ...3......Ths

 0976 3400 002e 002c 7572 693a 4445 5241 5456 4442 | .v4....,uri:DERATVDB

 3030 3030 313a 3a73 6f66 7477 6172 653a 6f67 673a | 00001::software:ogg:

 3139 313a 4558 545f 495f 3031 3600 002d 002b 2f73 | 191:EXT_I_016..-.+/s

 6f66 7477 6172 652f 6f67 672f 3139 312f 6469 7264 | oftware/ogg/191/dird

 6174 2f30 312f 6f75 742f 6569 3030 3030 3030 3534 | at/01/out/ei00000054

 3737 0000 0101 3800 0004 0000 0223 3900 0008 0000 | 77....8......#9.....

 0000 1dcd 5e90 3a00 0081 0b32 3537 3735 3635 3835 | ....^.:....257756585

 3437 0000 0000 0000 0000 0000 0000 0000 0000 0000 | 47..................

 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 | ....................

 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 | ....................

 GroupID x32 '2' DatabaseInfo     Info x00  Length  358

 3200 0166 3000 0002 0007 3100 0005 0003 4947 5432 | 2..f0.....1.....IGT2

 0000 0500 0369 6774 3300 0004 0000 0000 3400 0002 | .....igt3.......4...

 000c 3500 0002 0002 3600 00e7 00e5 4f72 6163 6c65 | ..5.....6.....Oracle

 2044 6174 6162 6173 6520 3132 6320 456e 7465 7270 |  Database 12c Enterp

 7269 7365 2045 6469 7469 6f6e 2052 656c 6561 7365 | rise Edition Release

 2031 322e 322e 302e 312e 3020 2d20 3634 6269 7420 |  12.2.0.1.0 - 64bit

 5072 6f64 7563 7469 6f6e 0a50 4c2f 5351 4c20 5265 | Production.PL/SQL Re

 6c65 6173 6520 3132 2e32 2e30 2e31 2e30 202d 2050 | lease 12.2.0.1.0 - P

 726f 6475 6374 696f 6e0a 434f 5245 0931 322e 322e | roduction.CORE.12.2.

 302e 312e 3009 5072 6f64 7563 7469 6f6e 0a54 4e53 | 0.1.0.Production.TNS

 2066 6f72 204c 696e 7578 3a20 5665 7273 696f 6e20 |  for Linux: Version

 3132 2e32 2e30 2e31 2e30 202d 2050 726f 6475 6374 | 12.2.0.1.0 - Product

 696f 6e0a 4e4c 5352 544c 2056 6572 7369 6f6e 2031 | ion.NLSRTL Version 1

 322e 322e 302e 312e 3020 2d20 5072 6f64 7563 7469 | 2.2.0.1.0 - Producti

 6f6e 0a37 0000 0400 0000 0038 0000 0c00 0a31 322e | on.7.......8.....12.

 322e 302e 312e 3039 0000 0400 0000 013a 0000 0200 | 2.0.1.09.......:....

 003b 0000 0400 0000 013c 0000 1400 0000 1014 1414 | .;.......<..........

 1414 1414 1414 1414 1411 1414 143d 0000 0500      | .............=....

GroupID x33 '3' ProducerInfo     Info x00  Length  119

 3300 0077 3000 000a 0008 4558 545f 495f 3031 3100 | 3..w0.....EXT_I_011.

 0002 0003 3200 0002 0013 3300 0002 0001 3400 0002 | ....2.....3.....4...

 0000 3500 0002 0000 3600 0002 0001 3700 0041 003f | ..5.....6.....7..A.?

 5665 7273 696f 6e20 3139 2e31 2e30 2e30 2e31 204f | Version 19.1.0.0.1 O

 4747 434f 5245 5f31 392e 312e 302e 302e 305f 504c | GGCORE_19.1.0.0.0_PL

 4154 464f 524d 535f 3139 3035 3234 2e32 3230 31   | ATFORMS_190524.2201

Read next record. It would be File Metadata

Logdump 37 >n

2022/09/04 12:10:00.319.559 Metadata             Len 78 RBA 1787

Database Name:

*

DDR Version: 1

Database type: ORACLE

Character set ID: UTF-8

National character set ID: UTF-16

Locale: neutral

Case sensitivity: 14 14 14 14 14 14 14 14 14 14 14 14 11 14 14 14

TimeZone: GMT

Global name: IGT

*

Read next record. It would be Record Metadata

Logdump 38 >n

2022/09/04 14:57:26.998.524 Metadata             Len 10069 RBA 1916

Table Name: MY_SCHEMA.SFI_CUSTOMER_PROFILE

*

 1)Name          2)Data Type        3)External Length  4)Fetch Offset      5)Scale         6)Level

 7)Null          8)Bump if Odd      9)Internal Length 10)Binary Length    11)Table Length 12)Most Sig DT

13)Least Sig DT 14)High Precision  15)Low Precision   16)Elementary Item  17)Occurs       18)Key Column

19)Sub DataType 20)Native DataType 21)Character Set   22)Character Length 23)LOB Type     24)Partial Type

*

TDR version: 11

Definition for table MY_SCHEMA.SFI_CUSTOMER_PROFILE

Record Length: 68504

Columns: 132

KEY1                        64    120        0  0  0 1 0    120    120      0 0 0 0 0 1    0 1   0    1       -1    120 0 0

                            property: 0x0000000000800000

IMSI                        64    120      126  0  0 1 0    120    120      0 0 0 0 0 1    0 0   0    1       -1    120 0 0

                            property: 0x0000000000800000

ATTR1                       64    800      252  0  0 1 0    800    800      0 0 0 0 0 1    0 0   0    1       -1    800 0 0

                            property: 0x0000000000800000

ATTR2                       64    800     1058  0  0 1 0    800    800      0 0 0 0 0 1    0 0   0    1       -1    800 0 0

                            property: 0x0000000000800000

ATTR3                       64    800     1864  0  0 1 0    800    800      0 0 0 0 0 1    0 0   0    1       -1    800 0 0

                            property: 0x0000000000800000

ATTR4                       64    800     2670  0  0 1 0    800    800      0 0 0 0 0 1    0 0   0    1       -1    800 0 0

                            property: 0x0000000000800000

ATTR5                       64    800     3476  0  0 1 0    800    800      0 0 0 0 0 1    0 0   0    1       -1    800 0 0

                            property: 0x0000000000800000

ATTR6                       64    800     4282  0  0 1 0    800    800      0 0 0 0 0 1    0 0   0    1       -1    800 0 0

                            property: 0x0000000000800000

ATTR7                       64    800     5088  0  0 1 0    800    800      0 0 0 0 0 1    0 0   0    1       -1    800 0 0

                            property: 0x0000000000800000

ATTR8                       64    800     5894  0  0 1 0    800    800      0 0 0 0 0 1    0 0   0    1       -1    800 0 0

                            property: 0x0000000000800000

ATTR9                       64    800     6700  0  0 1 0    800    800      0 0 0 0 0 1    0 0   0    1       -1    800 0 0

                            property: 0x0000000000800000

ATTR10                      64    800     7506  0  0 1 0    800    800      0 0 0 0 0 1    0 0   0    1       -1    800 0 0

                            property: 0x0000000000800000

Read next record. It would be Record data

Logdump 39 >n

2022/09/08 02:11:47.000.000 GGSUnifiedUpdate     Len  1687 RBA 12072

Name: MY_SCHEMA.SFI_CUSTOMER_PROFILE  (TDR Index: 1)

After  Image:                                             Partition x0c   G  m

 d704 0000 0000 0f00 0000 0b00 3936 3837 3732 3436 | ............96877246

 3636 3901 0004 00ff ff00 0002 000d 0000 0009 0042 | 669................B

 6c75 652d 5041 5947 0300 0b00 0000 0700 7072 6570 | lue-PAYG........prep

 6169 6404 000c 0000 0008 0043 6f6e 7375 6d65 7205 | aid........Consumer.

 0006 0000 0002 0065 6e06 0004 00ff ff00 0007 0004 | .......en...........

 00ff ff00 0008 0004 00ff ff00 0009 0004 00ff ff00 | ....................

 000a 0004 00ff ff00 000b 0004 00ff ff00 000c 0004 | ....................

Before Image          Len  1243 (x000004db)

BeforeColumnLen   1239 (x000004d7)

Column     0 (x0000), Len    15 (x000f)

 0000 0b00 3936 3837 3732 3436 3636 39             | ....96877246669

Column     1 (x0001), Len     4 (x0004)

 ffff 0000                                         | ....

Column     2 (x0002), Len    13 (x000d)

 0000 0900 426c 7565 2d50 4159 47                  | ....Blue-PAYG

Column     3 (x0003), Len    11 (x000b)

 0000 0700 7072 6570 6169 64                       | ....prepaid

Column     4 (x0004), Len    12 (x000c)

 0000 0800 436f 6e73 756d 6572                     | ....Consumer

Column     5 (x0005), Len     6 (x0006)

 0000 0200 656e                                    | ....en

Column     6 (x0006), Len     4 (x0004)

 ffff 0000                                         | ....

Column     7 (x0007), Len     4 (x0004)

 ffff 0000                                         | ....

Column     8 (x0008), Len     4 (x0004)

 ffff 0000                                         | ....

Column     9 (x0009), Len     4 (x0004)

 ffff 0000                                         | ....

Column    10 (x000a), Len     4 (x0004)

 ffff 0000                                         | ....

After Image           Len   444 (x000001bc)

Column     0 (x0000), Len    15 (x000f)

 0000 0b00 3936 3837 3732 3436 3636 39             | ....96877246669

Column     2 (x0002), Len    13 (x000d)

 0000 0900 426c 7565 2d50 4159 47                  | ....Blue-PAYG

Column     3 (x0003), Len    11 (x000b)

 0000 0700 7072 6570 6169 64                       | ....prepaid

Column     4 (x0004), Len    12 (x000c)

 0000 0800 436f 6e73 756d 6572                     | ....Consumer

Column     5 (x0005), Len     6 (x0006)

 0000 0200 656e                                    | ....en

Column     6 (x0006), Len     4 (x0004)

 ffff 0000                                         | ....

Column     7 (x0007), Len     4 (x0004)

 ffff 0000                                         | ....

Column     8 (x0008), Len     4 (x0004)

 ffff 0000                                         | ....

Column     9 (x0009), Len     4 (x0004)

 ffff 0000                                         | ....

Column    10 (x000a), Len     4 (x0004)

 ffff 0000                          

Read next record. It would be another Record data

Logdump 40 >n

2022/09/08 02:11:47.000.000 GGSUnifiedUpdate     Len  1679 RBA 13839

Name: MY_SCHEMA.SFI_CUSTOMER_PROFILE  (TDR Index: 1)

After  Image:                                             Partition x0c   G  m

 d304 0000 0000 0f00 0000 0b00 3936 3837 3732 3436 | ............96877246

 3637 3001 0004 00ff ff00 0002 0009 0000 0005 0042 | 670................B

 6c61 636b 0300 0b00 0000 0700 7072 6570 6169 6404 | lack........prepaid.

 000c 0000 0008 0043 6f6e 7375 6d65 7205 0006 0000 | .......Consumer.....

 0002 0065 6e06 0004 00ff ff00 0007 0004 00ff ff00 | ...en...............

 0008 0004 00ff ff00 0009 0004 00ff ff00 000a 0004 | ....................

 00ff ff00 000b 0004 00ff ff00 000c 0004 00ff ff00 | ....................

Before Image          Len  1239 (x000004d7)

BeforeColumnLen   1235 (x000004d3)

Column     0 (x0000), Len    15 (x000f)

 0000 0b00 3936 3837 3732 3436 3637 30             | ....96877246670

Column     1 (x0001), Len     4 (x0004)

 ffff 0000                                         | ....

Column     2 (x0002), Len     9 (x0009)

 0000 0500 426c 6163 6b                            | ....Black

Column     3 (x0003), Len    11 (x000b)

 0000 0700 7072 6570 6169 64                       | ....prepaid

Column     4 (x0004), Len    12 (x000c)

 0000 0800 436f 6e73 756d 6572                     | ....Consumer

Column     5 (x0005), Len     6 (x0006)

 0000 0200 656e                                    | ....en

Column     6 (x0006), Len     4 (x0004)

 ffff 0000                                         | ....

Column     7 (x0007), Len     4 (x0004)

 ffff 0000                                         | ....

Column     8 (x0008), Len     4 (x0004)

 ffff 0000                                         | ....

Column     9 (x0009), Len     4 (x0004)

 ffff 0000                                         | ....

Column    10 (x000a), Len     4 (x0004)

 ffff 0000                                         | ....

Logdump 41 >exit

DBMS_STATS by Example

==============================
General
==============================
DBMS_STATS examples

BEGIN

  DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'USERNAME', 

  estimate_percent => 15);

END;

/

==============================
DBMS_STATS to gather histograms:
==============================
BEGIN
  DBMS_STATS.gather_table_stats
  ( USER, 'MY_TABLE', METHOD_OPT => 'FOR ALL COLUMNS SIZE 254' );
END;

==============================
DBMS_STATS to unlock statistics, gather stats, and lock statistics again.
==============================

Default setup

BEGIN

  DBMS_STATS.gather_table_stats

  (ownname=>i.owner, 

   tabname=>i.table_name, 

   estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

   cascade => DBMS_STATS.AUTO_CASCADE );

END;

/

--DBMS_STATS.AUTO_CASCADE - default to TRUE

--TRUE/FALSE to analyze tables indexes

BEGIN

  DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'USERNAME', 

  estimate_percent => 15);

END;

/

DECLARE

 v_owner VARCHAR2(30) := 'MY_OWNER';

 v_table_name VARCHAR2(30) := 'MY_TABLE';

BEGIN

  DBMS_STATS.unlock_table_stats(v_owner ,v_table_name);

  DBMS_STATS.gather_table_stats

  (ownname=>v_owner,

   tabname=>v_table_name, 

   estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

   cascade => DBMS_STATS.AUTO_CASCADE );  

  DBMS_STATS.lock_table_stats(v_owner ,v_table_name);

END;

/

See current statisctics

col TABLE_NAME for A30

SELECT table_name ,num_rows, sample_size, last_analyzed 

  FROM USER_TABLES 

WHERE table_name IN('AAA','BBB','CCC');

==============================

Use DBMS_STATS to rebuild index:
==============================

BEGIN
   DBMS_STATS.gather_index_stats('MY_OWNER','MY_INDEX');
END;

==============================
Working with Partitions
==============================

Step 1. - Gather basic stats for all the table

Step 2. - Gather detailed stats for current Partition.

Step 2. - Copy gathered detailed stats from current Partition to the next partition.

DBMS_STATS.gather_table_stats(ownname => v_user_name, 
                              tabname => p_table_name, 
                              estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE
                              );
                                        
DBMS_STATS.gather_table_stats(ownname => v_user_name, 
                              tabname => p_table_name, 
                              partname => v_from_partition,
                              estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
                              method_opt => 'FOR ALL COLUMNS');

DBMS_STATS.copy_table_stats(v_user_name, p_table_name, v_from_partition, v_to_partition);

==============================
DBMS_STATS on an Index after index creation. Needed or not?
==============================
Per Oracle Documentation, starting with Oracle 10.2.0.1, no need to manually gather index statistics after index creation, since this is done automatically.

==============================
METHOD_OPT
==============================
Short summary from how does the methodopt parameter work

The METHOD_OPT parameter controls the following,
- Which columns will or will not have base column statistics gathered on them
- Histogram creation,
- Creation of extended statistics

Histogram creation
METHOD_OPT default value is FOR ALL COLUMNS SIZE AUTO.
For most purposes, this should be the suitable value.


The METHOD_OPT parameter syntax is made up of two parts.
Part A - COLUMNS
Part B - SIZE

Part A - COLUMNS
COLUMNS part of the METHOD_OPT syntax controls which columns will have base column statistics (min, max, NDV, number of nulls, etc) gathered on them. 

Possible options:
FOR ALL COLUMNS - This is recommended, and default, value.
FOR ALL INDEXED COLUMNS - not recommended.

FOR ALL HIDDEN COLUMNS - to be used for case when a new virtual column was added, without affecting regular statistics.

Part B - SIZE

Possible options:
AUTO - This is default value.
Oracle will automatically determines the columns that need histograms based on the column usage information (SYS.COL_USAGE$), and the presence of a data skew. 
An integer value indicates that a histogram will be created with at most the specified number of buckets. Must be in the range [1,254]. To force histogram creation it is recommend that the number of buckets be left at 254. SIZE 1 means no histogram will be created.

REPEAT - A histogram will only be created for any column that already has one. And with the same SIZE value. If the table is a partitioned table, repeat ensures a histogram will be created for a column that already has one on the global level. 
However, this is not a recommended setting, as the number of buckets from past, might not be a good value for current data.


SKEWONLY automatically creates a histogram on any column that shows a skew in its data distribution.

A specific case I- gather base statistics for all columns, with no histograms and for one column (cust_id) gather detailed statistics.

DBMS_STATS.GATHER_TABLE_STATS('SH', 'SALES', method_opt => 'FOR ALL COLUMNS SIZE 1 FOR COLUMNS SIZE 254 CUST_ID'); 


A specific case II- gather statistics only for some of the columns.
 DBMS_STATS.GATHER_TABLE_STATS('SH', 'SALES', method_opt => 'FOR COLUMNS SIZE 254 CUST_ID TIME_ID CHANNEL_ID PROMO_ID QUANTITY_SOLD AMOUNT_SOLD'); 

A specific case III- delete column statistics for a specific column.

DBMS_STATS.DELETE_COLUMN_STATS('SH', 'SALES', 'PROD_ID'); 

Creation of extended statistics

Extended statistics encompasses two additional types of column statistics; column groups and expression statistics. 

In the example below, a column group will be automatically created on the PROD_ID and CUST_ID columns in the SALES table. It will be given a system-generated name and will have all of the base column statistics gathered on it.

DBMS_STATS.GATHER_TABLE_STATS('SH', 'SALES', method_opt => 'FOR ALL COLUMNS SIZE 254 FOR COLUMNS SIZE 254(PROD_ID, CUST_ID)');

DBMS_STATS.SET_TABLE_PREFS
In case of using a non default value for MOTHOD_OPT, rather than specifying the METHOD_OPT parameter in the statistics gathering command it is recommended that you specify value for the METHOD_OPT via DBMS_STATS.SET_TABLE_PREFS.

DBMS_STATS.SET_TABLE_PREFS('SH', 'SALES', 'METHOD_OPT',  'FOR ALL COLUMNS SIZE 254 FOR COLUMNS SIZE 1 PROD_ID');

Possible values for METHOD_OPT parameter.
In the GATHER_DICTIONARY_STATS, GATHER_DATABASE_STATS, and GATHER_SCHEMA_STATS procedures only accepts 'FOR ALL [INDEXED|HIDDEN] columns' syntax. No specific column names can be specified.

When used in the GATHER_TABLE_STATS procedure, the METHOD_OPT parameter can accept an additional argument in the form of ‘FOR columns …'. 

==============================
DBA_TAB_STATISTICS and DBA_TAB_COL_STATISTICS
==============================
DBA_TAB_STATISTICSThis table holds additional info regarding Table statistics, such as last_analyzed, sample_size, stale_stats and stattype_locked indicator.

DBA_TAB_COL_STATISTICS
See histogram data:
SELECT column_name, num_distinct, histogram 
FROM   USER_TAB_COL_STATISTICS

WHERE  table_name = 'SALES'; 

SELECT * FROM V$SESSION WHERE event = 'enq: TX - row lock contention'

SELECT sql_id,event#, event, p1,p2,p3,p1text,p2text,p3text, wait_class_id , wait_class

FROM V$SESSION WHERE event = 'enq: TX - row lock contention'

SELECT * FROM V$SQL WHERE sql_id = '1xf229v3ja890'

set lines 500

 select * from (

 select sql_id,p1,p2,p3,p1text,p2text,p3text,ASH.SQL_OPNAME,count(1)

 from gv$active_session_history ash

 where ASH.EVENT ='enq: TX - row lock contention'

 group by sql_id,p1,p2,p3,p1text,p2text,p3text,SQL_OPNAME

 order by 9 desc

 )

 where rownum<12

 ;

update SGA_SUBSCRIBER_SFI_ACTIVE set COMMUNITIES=:1 , TS_LAST_MODIFIED=SYSDATE where IMSI=:2  and AFFILIATE_ID = :3 

SELECT * FROM USER_TABLES WHERE table_name = 'SGA_SUBSCRIBER_SFI'

SELECT * FROM USER_TAB_STATISTICS WHERE table_name = 'SGA_SUBSCRIBER_SFI'

SELECT USER FROM DUAL

BEGIN

  DBMS_STATS.unlock_table_stats('VRS_GROUP_SHARE','SGA_SUBSCRIBER_SFI');

END;

/

SELECT *

FROM dba_jobs

WHERE what like '%DB_STATISTICS_MANAGER.MAIN%' ;

BEGIN

DBMS_JOB.next_date(4,SYSDATE+10000);

commit;

END;

/

BEGIN    

  DBMS_STATS.gather_table_stats(ownname=>'VRS_GROUP_SHARE', tabname=>'SGA_SUBSCRIBER_SFI', estimate_percent => 5, cascade => DBMS_STATS.AUTO_CASCADE, degree=>16,GRANULARITY=> 'GLOBAL AND PARTITION');

END;

/

SELECT * FROM dba_tab_statistics WHERE table_name ='SGA_SUBSCRIBER_SFI'

=====================================

Example to gather stats for Partitioned table

=====================================

BEGIN    

  DBMS_STATS.gather_table_stats

  (ownname=>'SCHEMA_NAME', 

   tabname=>'TABLE_NAME', 

   estimate_percent => 5, 

   cascade => DBMS_STATS.AUTO_CASCADE, 

   degree=>16,

   GRANULARITY=> 'GLOBAL AND PARTITION');

END;

/

 

BEGIN

  DBMS_STATS.unlock_table_stats

  ('SCHEMA_NAME','TABLE_NAME');

END;

/

==============================
DBMS_STATS Copy from Table to Table:
==============================

Consider Example:

Same type of data is loaded to daily tables. Need to copy DBMS_STATS from table to table:

DBMS_STATS.gather_table_stats (ownname=>'USER_A', tabname=>'TABLE_A_20200101', estimate_percent => 5, cascade => DBMS_STATS.AUTO_CASCADE);

DBMS_STATS.create_stat_table  (ownname => 'USER_A' , stattab => 'temp_stat_20201109') ;

DBMS_STATS.export_table_stats (ownname => 'USER_A' , stattab => 'temp_stat_20201109', tabname =>'TABLE_A_20200101', statid => 'stats_20200101') ;

DBMS_STATS.import_table_stats (ownname => 'USER_A' , stattab => 'temp_stat', tabname => 'TABLE_A_20200102');

DBMS_STATS.import_table_stats (ownname => 'USER_A' , stattab => 'temp_stat', tabname => 'TABLE_A_20200103');

DBMS_STATS.import_table_stats (ownname => 'USER_A' , stattab => 'temp_stat', tabname => 'TABLE_A_20200104');

Appendix
DBMS_STATS Oracle reference.
GATHER_TABLE_STATS Procedure Oracle Reference
GATHER_INDEX_STATS Procedure Oracle Reference
Burlson Reference
Good block about DBMS_STATS usage and options

===================================

Example:

Copy updated stats from one partition 

to all partitions

===================================

BEGIN

DBMS_STATS.UNLOCK_TABLE_STATS(ownname => 'USER_NAME', tabname => 'GA_W_COUNTERS_HISTORY');

END;

/

BEGIN

DBMS_STATS.gather_table_stats(ownname => 'USER_NAME', 

                              tabname => 'GA_W_COUNTERS_HISTORY', 

                              partname => 'P_30',

                              estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

                              method_opt => 'FOR ALL COLUMNS');

END;

/

SELECT 'DBMS_STATS.copy_table_stats(''USER_NAME'', ''GA_W_COUNTERS_HISTORY'', ''P_30'', '''||partition_name||'''); ' FROM  USER_TAB_PARTITIONS WHERE table_name = 'GA_W_COUNTERS_HISTORY'

BEGIN

DBMS_STATS.copy_table_stats('USER_NAME', 'GA_W_COUNTERS_HISTORY', 'P_30', 'P_1'); 

DBMS_STATS.copy_table_stats('USER_NAME', 'GA_W_COUNTERS_HISTORY', 'P_30', 'P_10'); 

DBMS_STATS.copy_table_stats('USER_NAME', 'GA_W_COUNTERS_HISTORY', 'P_30', 'P_11'); 

DBMS_STATS.copy_table_stats('USER_NAME', 'GA_W_COUNTERS_HISTORY', 'P_30', 'P_12'); 

DBMS_STATS.copy_table_stats('USER_NAME', 'GA_W_COUNTERS_HISTORY', 'P_30', 'P_13'); 

DBMS_STATS.copy_table_stats('USER_NAME', 'GA_W_COUNTERS_HISTORY', 'P_30', 'P_14'); 

DBMS_STATS.copy_table_stats('USER_NAME', 'GA_W_COUNTERS_HISTORY', 'P_30', 'P_15'); 

DBMS_STATS.copy_table_stats('USER_NAME', 'GA_W_COUNTERS_HISTORY', 'P_30', 'P_16'); 

DBMS_STATS.copy_table_stats('USER_NAME', 'GA_W_COUNTERS_HISTORY', 'P_30', 'P_17'); 

DBMS_STATS.copy_table_stats('USER_NAME', 'GA_W_COUNTERS_HISTORY', 'P_30', 'P_18'); 

DBMS_STATS.copy_table_stats('USER_NAME', 'GA_W_COUNTERS_HISTORY', 'P_30', 'P_19'); 

DBMS_STATS.copy_table_stats('USER_NAME', 'GA_W_COUNTERS_HISTORY', 'P_30', 'P_2'); 

DBMS_STATS.copy_table_stats('USER_NAME', 'GA_W_COUNTERS_HISTORY', 'P_30', 'P_20'); 

DBMS_STATS.copy_table_stats('USER_NAME', 'GA_W_COUNTERS_HISTORY', 'P_30', 'P_21'); 

DBMS_STATS.copy_table_stats('USER_NAME', 'GA_W_COUNTERS_HISTORY', 'P_30', 'P_22'); 

DBMS_STATS.copy_table_stats('USER_NAME', 'GA_W_COUNTERS_HISTORY', 'P_30', 'P_23'); 

DBMS_STATS.copy_table_stats('USER_NAME', 'GA_W_COUNTERS_HISTORY', 'P_30', 'P_24'); 

DBMS_STATS.copy_table_stats('USER_NAME', 'GA_W_COUNTERS_HISTORY', 'P_30', 'P_25'); 

DBMS_STATS.copy_table_stats('USER_NAME', 'GA_W_COUNTERS_HISTORY', 'P_30', 'P_26'); 

DBMS_STATS.copy_table_stats('USER_NAME', 'GA_W_COUNTERS_HISTORY', 'P_30', 'P_27'); 

DBMS_STATS.copy_table_stats('USER_NAME', 'GA_W_COUNTERS_HISTORY', 'P_30', 'P_28'); 

DBMS_STATS.copy_table_stats('USER_NAME', 'GA_W_COUNTERS_HISTORY', 'P_30', 'P_29'); 

DBMS_STATS.copy_table_stats('USER_NAME', 'GA_W_COUNTERS_HISTORY', 'P_30', 'P_3'); 

DBMS_STATS.copy_table_stats('USER_NAME', 'GA_W_COUNTERS_HISTORY', 'P_30', 'P_31'); 

DBMS_STATS.copy_table_stats('USER_NAME', 'GA_W_COUNTERS_HISTORY', 'P_30', 'P_4'); 

DBMS_STATS.copy_table_stats('USER_NAME', 'GA_W_COUNTERS_HISTORY', 'P_30', 'P_5'); 

DBMS_STATS.copy_table_stats('USER_NAME', 'GA_W_COUNTERS_HISTORY', 'P_30', 'P_6'); 

DBMS_STATS.copy_table_stats('USER_NAME', 'GA_W_COUNTERS_HISTORY', 'P_30', 'P_7'); 

DBMS_STATS.copy_table_stats('USER_NAME', 'GA_W_COUNTERS_HISTORY', 'P_30', 'P_8'); 

DBMS_STATS.copy_table_stats('USER_NAME', 'GA_W_COUNTERS_HISTORY', 'P_30', 'P_9'); 

END;

/

==========================
Purge single sql_id from shared pool
==========================

SELECT address, hash_value FROM V$SQLAREA 

 WHERE sql_id='1qkunjg7g6twp';

BEGIN

 DBMS_SHARED_POOL.PURGE ('00000005DAAB9130, 6845332021', 'C');

END;

/

And check the explain plan Using DBMS_XPLAN.display

SET LINESIZE 200

SET PAGESIZE 0

SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR('1qkunjg7g6twp'));

==========================
Gather system stats
==========================

--Regular STATS collection for SYS schema
BEGIN

 DBMS_STATS.GATHER_SCHEMA_STATS ('SYS');

END;

/

--Gather STATS for ALL Database Objects!!

BEGIN

 DBMS_STATS.GATHER_DATABASE_STATS (gather_sys=>TRUE);

END;

/

--Collect STATS for DICTIONARY Objects

BEGIN

 DBMS_STATS.GATHER_DICTIONARY_STATS;

END;

/

--Collect STATS for in-memory Objects

BEGIN

 DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

END;

/

To rollback Statistics: (How to Restore Oracle Optimizer Statistics (Doc ID 452011.1) )

BEGIN

 DBMS_STATS.RESTORE_SCHEMA_STATS('SYS',SYSDATE-1);

END;

/

BEGIN

DBMS_STATS.RESTORE_FIXED_OBJECTS_STATS(SYSDATE-1);

END;

/

BEGIN

 DBMS_STATS.RESTORE_DICTIONARY_STATS(SYSDATE-1);

END;

/