oratop and orachk powerful tool to monitor and troubleshoot the database/cluster issues

oratop:

-------

Provides near real-time database monitoring.

For more information, see My Oracle Support note 1500864.1.

• oratop is an interface that takes data from top and oracle databases on a host.
• The utility combines the data to OS data and session data.
• It allows the viewer to see how multiple databases are interacting with the host and identify high CPU consumers, memory consumers, and IO consumers.

orachk:

-------

Health Checks For The Oracle Stack Using ORAchk

Oracle ORAchk and Oracle EXAchk provide a lightweight and non-intrusive health check framework for the Oracle stack of software and hardware components.

Oracle ORAchk and Oracle EXAchk:

• Automates risk identification and proactive notification before your business is impacted
• Runs health checks based on critical and reoccurring problems
• Presents high-level reports about your system health risks and vulnerabilities to known issues
• Enables you to drill-down specific problems and understand their resolutions
• Enables you to schedule recurring health checks at regular intervals
• Sends email notifications and diff reports while running in daemon mode
• Integrates the findings into Oracle Health Check Collections Manager and other tools of your choice
• Runs in your environment with no need to send anything to Oracle

 

$ export ORACLE_HOME=<path>

$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib

$ export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/suptools/oratop:$PATH

/u01/app/oracle/product/19.0.0.0/dbhome_1/suptools/oratop/oratop / as sysdba

/u01/app/oracle/product/19.0.0.0/dbhome_1/suptools/orachk/orachk

More details are available on this Oracle Documentation

https://www.oracle.com/a/otn/docs/database/ahf-performance-tuning-using-oratop.pdf

Installation of oratop:

Download the oratop utility:

Use the metalink Doc ID 1500864.1 for download:

Rename to downloaded file to proper name

cd /u01/app/oracle/product/19.0.0.0/dbhome_1/suptools

mv oratop* oratop

provide execute permission:

chmod 755 oratop

Installation of oracheck:

[root@node1 ~]# cp -r /home/oracle/Desktop/orachk.zip /u01/app/oracle/product/19.0.0.0/dbhome_1/suptools/orachk

[root@node1 ~]# unzip orachk.zip

[root@node1 ~]# cd /u01/app/oracle/product/19.0.0.0/dbhome_1/suptools/

[root@node1 ~]# chwon oracle:oinstall oracheck

[oracle@node1 ~]$ ./orachk -v

[oracle@node1 ~]$ ./orachk -debug 

[root@node1 ~]#  ./orachk

Example 1: Running the oratop 

[oracle@node1 ~]$ /u01/app/oracle/product/19.0.0.0/dbhome_1/suptools/oratop/oratop / as sysdba

oratop: Release 15.0.0 Production on Mon May 12 19:47:24 2025

Copyright (c) 2011, Oracle.  All rights reserved.

Connecting ..

Processing ...

Oracle 19c - DEV 01:17:17 up:  24d,  2 ins,    0 sn,   0 us, 3.4G sga,     0%db

ID %CPU %DCP LOAD  AAS  ASC  ASI  ASW  IDL  MBPS  %FR  PGA UTPS  RT/X DCTR DWTR

 2  6.5  0.0  0.4  0.0    0    0    0    0   0.1    5 1.2G    0  3.2m  108    0

 1  6.8    0  0.1    0    0    0    0    0   0.1    8 1.2G    0     0    0    0

EVENT (C)                        TOT WAITS   TIME(s)  AVG_MS  PCT    WAIT_CLASS

DB CPU                                         30418           75

control file sequential read      19574633      5909     0.3   15    System I/O

IMR slave acknowledgement msg     12738390      2899     0.2    7         Other

control file parallel write        1575061       691     0.4    2    System I/O

ASM file metadata operation        3841939       690     0.2    2         Other

ID   SID     SPID USR PROG S  PGA SQLID/BLOCKER OPN  E/T STA STE EVENT/*LA  W/T

[oracle@node1 ~]$

Example 2: Running the orachk

[oracle@node1 ~]$ /u01/app/oracle/product/19.0.0.0/dbhome_1/suptools/orachk/orachk

Running orachk

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

PATH                             : /u01/app/oracle/product/19.0.0.0/dbhome_1/suptools/orachk

VERSION                          : 18.4.0_20181129

COLLECTIONS DATA LOCATION        : /u01/app/oracle/orachk/

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

This version of orachk was released on 29-Nov-2018 and its older than 180 days. No new version of orachk is available in RAT_UPGRADE_LOC. It is highly recommended that you download the latest version of orachk from my oracle support to ensure the highest level of accuracy of the data contained within the report.

Do you want to download latest version from my oracle support? [y/n] [y] n

orachk cannot be use as its older than a year.

Exiting...

[oracle@node1 ~]$

Query taking more time? 

1. DML Query (Insert, Update,)

Cause: locks / deadlocks 

Fix/Solution: kill / Ask user to do commit/rollback  

2. Select Query 

- OS side - OS analysis 

- top, vmstat, iosts, memory, sar (OEM, OS watcher, Exa watcher, Nagios)

- DB Side - DB analysis 

- Query dynamic perf view (v$session, v$longops, v$sql etc...)

- AWR report (ASH report, ADDM report, SQL advisory report)

To identify:

- SQL text (select * from emp;) - We can identify what all tables involved in the query:

Recommendations

- Latest Patch (n-1) (Jan, Apr, Jul, Oct) 

- Gather stats (Table/Index/Schema) 

- Validation / rebuild Index 

- Table move 

- Table shrink 

- SQL ID (1a1a1a1) - What is the execution plan associated to this SQL ID 

- Check execution plan (Plan change)

- SQL Profiling 

How to generate and display the execution plan

How to generate and display the execution plan:

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

Option 1: Generating and displaying the execution plan for the last SQL statement executed in your session:

select * from MALLIK.STUDENT;

SET LINESIZE 150 

SET PAGESIZE 2000 

SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR); 

select plan_table_output from table(dbms_xplan.display_cursor(null,null,'basic'));

Option 2: Generating and displaying the execution plan from SQL_ID

SET LINESIZE 150 

SET PAGESIZE 2000 

SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR('atfg645km3ykp'));

SELECT * FROM table(dbms_xplan.display_cursor('fnrtqw9c233tt',null,'basic'));

Regards,

Mallik

Row chaining Vs Row migration

Row chaining Vs Row migration

Chained rows:

- Chained rows are records stored over multiple data blocks due to their excessive size. 

- Row piece size is grater than DB_BLOCK_SIZE then row piece will be spitted and spead accross the multiple blicks.

- Mainly caused due to INSERT statement

Migrated rows:

- Migrated rows occur when an UPDATE DML causes the rows to expand onto another data block.

- Mainly caused due to UPDATE statement

A migrated row is a special case of a chained row. 

A migrated row is a chained row, a chained row may or may not be a migrated row.

Chained rows:

Questions:

1. What is row chaining?

2. What is row migration?

3. How to identify the row chaining and row migration?

4. How to fix this row chaining and row migration?

Analyze the table to refresh the statistics:

analyze table EMP compute statistics;

This query will show how many chained (and migrated) rows each table has:

SELECT owner, table_name, chain_cnt FROM dba_tables WHERE chain_cnt > 0;

Check for chained rows for perticuler table:

select chain_cnt from all_tables where owner='MALLIK' and TABLE_NAME='EMP';

In order to a avoide the row chaining we need to create a tablespace with bigger block size and move the table:

SQL> select TABLESPACE_NAME,BLOCK_SIZE from dba_tablespaces;

TABLESPACE_NAME  BLOCK_SIZE

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

SYSTEM                 8192

SYSAUX                 8192

UNDOTBS1           8192

TEMP                    8192

USERS                  8192

TEST_16K_TS      16384 >>> 16K block size

alter table MALLIK.EMP move tablespace TEST_16K_TS;

After table move we should rebuild the index since index become unusable:

alter index EMP.PK_EMPID rebuild;

After moved table analyze the table to refresh the statistics and and check for row chaining which will be resolved. 

analyze table EMP compute statistics;

select chain_cnt from all_tables where owner='MALLIK' and TABLE_NAME='EMP';

Migrated rows:

This will put the rows into the CHAINED_ROWS table which is created by the utlchain.sql script 

($ORACLE_HOME/rdbms/admin).

They create a table named CHAINED_ROWS in the schema of the user submitting the script.

SELECT * FROM chained_rows;

Example:

create table CHAINED_ROWS (

owner_name varchar2(30),

table_name varchar2(30),

cluster_name varchar2(30),

partition_name varchar2(30),

subpartition_name varchar2(30),

head_rowid rowid,

analyze_timestamp date

);

To see which rows are chained:

ANALYZE TABLE EMP LIST CHAINED ROWS;

select owner_name, table_name, head_rowid from chained_rows;

The following query can be used to identify tables with chaining problems:

TTITLE 'Tables Experiencing Chaining'

SELECT owner, table_name,

       NVL(chain_cnt,0) "Chained Rows"

  FROM all_tables

 WHERE owner NOT IN ('SYS', 'SYSTEM')

       AND NVL(chain_cnt,0) > 0

ORDER BY owner, table_name;

Conclusion

- Row migration is typically caused by UPDATE operation

- Row chaining is typically caused by INSERT operation.

- SQL statements which are creating/querying these chained/migrated rows will degrade the performance due to more I/O work.

- To diagnose chained/migrated rows use ANALYZE command , query V$SYSSTAT view

- To remove chained/migrated rows use higher PCTFREE using ALTER TABLE MOVE

Regards,

Mallik

Is flushing shared pool and buffer cache is good?

Flushing shared pool and buffer cache is not recommended and we should not perform on PROD environment

Flush Shared pool meaning flushing cached execution plan and SQL Queries from memory.

Flush buffer cache meaning flushing cached data from memory.

Database restart which internally flush both shared pool and buffer cache.

Flushing the data buffer cache & Shared pool is not recommend on Production Environment.

It may lead to increase the performance overhead, especially on RAC databases.

Flush buffer cache may lead to disk I/0 overhead.

https://docs.oracle.com/database/121/ARPLS/d_result_cache.htm#ARPLS202

1. Before flushing shared pool and buffer cache 

[root@oraclelab1 ~]# su - oracle

Last login: Fri Aug 26 18:47:01 IST 2022 on pts/1

[oracle@oraclelab1 ~]$

[oracle@oraclelab1 ~]$ ps -ef|grep smon

oracle    4467     1  0 Aug09 ?        00:00:19 ora_smon_DEVDB

[oracle@oraclelab1 ~]$

[oracle@oraclelab1 ~]$ . oraenv

ORACLE_SID = [oracle] ? DEVDB

The Oracle base has been set to /u01/app/oracle

[oracle@oraclelab1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Aug 27 01:56:44 2022

Version 19.14.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.14.0.0.0

SQL> select count(*) from v$sql;

  COUNT(*)

----------

      7049

SQL> exec dbms_result_cache.memory_report;

PL/SQL procedure successfully completed.

SQL> set serveroutput on;

SQL> exec dbms_result_cache.memory_report;

R e s u l t   C a c h e   M e m o r y   R e p o r t

[Parameters]

Block Size          = 1K bytes

Maximum Cache Size  = 18048K bytes (18048 blocks)

Maximum Result Size = 902K bytes (902 blocks)

[Memory]

Total Memory = 800200 bytes [0.085% of the Shared Pool]

... Fixed Memory = 12424 bytes [0.001% of the Shared Pool]

... Dynamic Memory = 787776 bytes [0.084% of the Shared Pool]

....... Overhead = 165184 bytes

....... Cache Memory = 608K bytes (608 blocks)

........... Unused Memory = 12 blocks

........... Used Memory = 596 blocks

............... Dependencies = 11 blocks (11 count)

............... Results = 585 blocks

................... SQL     = 3 blocks (3 count)

................... PLSQL   = 6 blocks (6 count)

................... Invalid = 576 blocks (576 count)

PL/SQL procedure successfully completed.

2. After flushing shared pool and buffer cache

SQL> startup force;

ORACLE instance started.

Total System Global Area 3690985848 bytes

Fixed Size                  8903032 bytes

Variable Size             989855744 bytes

Database Buffers         2684354560 bytes

Redo Buffers                7872512 bytes

Database mounted.

Database opened.

SQL> select count(*) from v$sql;

  COUNT(*)

----------

       445

SQL> set serveroutput on;

SQL> exec dbms_result_cache.memory_report;

R e s u l t   C a c h e   M e m o r y   R e p o r t

[Parameters]

Block Size          = 1K bytes

Maximum Cache Size  = 18048K bytes (18048 blocks)

Maximum Result Size = 902K bytes (902 blocks)

[Memory]

Total Memory = 202648 bytes [0.022% of the Shared Pool]

... Fixed Memory = 5848 bytes [0.001% of the Shared Pool]

... Dynamic Memory = 196800 bytes [0.021% of the Shared Pool]

....... Overhead = 164032 bytes

....... Cache Memory = 32K bytes (32 blocks)

........... Unused Memory = 25 blocks

........... Used Memory = 7 blocks

............... Dependencies = 5 blocks (5 count)

............... Results = 2 blocks

................... Invalid = 2 blocks (2 count)

PL/SQL procedure successfully completed.

SQL>

Manually flush buffer cache & shared pool cache without bouncing the database:

Standalone Database:

alter system flush buffer_cache;

alter system flush shared_pool;

RAC Database:

alter system flush buffer_cache global;

alter system flush shared_pool global;

Regards,

Mallik

Check or get the Execution plan from SQL ID

Get the Execution plan from SQL ID!!!

Check the Execution plan from SQL ID!!!

High level steps

1. Get the SQL ID for the SQL Statement
2. Get the explain plain or execution plan for the SQL ID.

1. Get the SQL ID for the SQL Statement

- First you need to execute the SQL statement to get the SQL ID from v$sql view.

- You can get the SQL ID from AWR report or ASH report or you can get from the current session 

SQL> set pages 1000 lines 1000

SQL> SELECT * FROM EMP;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

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

      7839 KING                    17-NOV-81       5000                    10

      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30

      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10

      7566 JONES      MANAGER         7839 02-APR-81       2975                    20

      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20

      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

      7369 SMITH      CLERK           7902 17-DEC-80        800                    20

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30

      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30

      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30

      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

      7900 JAMES      CLERK           7698 03-DEC-81        950                    30

      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

SQL> 

SQL> select sql_id from v$sql where sql_text like 'SELECT * FROM EMP';

SQL_ID

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

4ttqgu8uu8fus

2. Get the explain plain or execution plan for the SQL

SQL> select sql_id from v$sql where sql_text like 'SELECT * FROM EMP';

SQL_ID

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

4ttqgu8uu8fus

SQL> set line 200 pages 200

SQL> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR ('&sqlid'));

SQL> Enter value for sqlid: 4ttqgu8uu8fus

old   1: SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR ('&sqlid'))

new   1: SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR ('4ttqgu8uu8fus'))

PLAN_TABLE_OUTPUT

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

SQL_ID  4ttqgu8uu8fus, child number 0

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

SELECT * FROM EMP

Plan hash value: 3956160932

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

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |

|   1 |  TABLE ACCESS FULL| EMP  |    14 |   532 |     3   (0)| 00:00:01 |

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

13 rows selected.

SQL>

Demo 2: (Best Query)

SQL> SELECT * FROM EMP where EMPNO=7839;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

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

      7839 KING       PRESIDENT            17-NOV-81       5000                    10

SQL> select sql_id from v$sql where sql_text like 'SELECT * FROM EMP where EMPNO=7839';

SQL_ID

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

dm0ndwh8c28aj

SQL> set line 200 pages 200

SQL> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR ('&sqlid'));

Enter value for sqlid: dm0ndwh8c28aj

old   1: SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR ('&sqlid'))

new   1: SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR ('dm0ndwh8c28aj'))

PLAN_TABLE_OUTPUT

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

SQL_ID  dm0ndwh8c28aj, child number 0

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

SELECT * FROM EMP where EMPNO=7839

Plan hash value: 2949544139

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

| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |        |       |       |     1 (100)|          |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    38 |     1   (0)| 00:00:01 |

|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)|          |

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

Predicate Information (identified by operation id):

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

   2 - access("EMPNO"=7839)

19 rows selected.

SQL>

Demo 3: (Bad Query)

SQL> set line 200 pages 200

SQL> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR ('&sqlid'));

Enter value for sqlid: 071z8v8jqhyx4

old   1: SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR ('&sqlid'))

new   1: SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR ('071z8v8jqhyx4'))

PLAN_TABLE_OUTPUT

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

SQL_ID  071z8v8jqhyx4, child number 0

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

SELECT * FROM EMP where ENAME='KING'

Plan hash value: 3956160932

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

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |

|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    38 |     3   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   1 - filter("ENAME"='KING')

18 rows selected.

SQL>

Regards,

Mallik

Automatic SQL Tuning Adviser

Automatic SQL Tuning Adviser:

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

Optimizer --- Will generate and pick execution plan

Suppose Stale Table or Wrong statistics of a table 

1. Statistical Analysis 
2. Accessing Path (Using Index or not)

High Level steps for SQL Tuning Adviser:

How to find the SQL ID:

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

select sql_id from v$sql where sql_text like 'select * from student';

Create Tuning Task:

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

DECLARE

l_sql_tune_task_id VARCHAR2(100);

BEGIN

l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

sql_id => 'cdq26qbcmz1hc',

scope => DBMS_SQLTUNE.scope_comprehensive,

time_limit => 500,

task_name => 'my_tuning_task_3',

description => 'Tuning task1 for statement cdq26qbcmz1hc');

DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

END;

/

Execute Tuning Task:

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

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'my_tuning_task_3');

Status of Tuning Task:

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

SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME='my_tuning_task_3';

Display the Recommendation:

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

set long 65536

set longchunksize 65536

set linesize 100

select dbms_sqltune.report_tuning_task('my_tuning_task_3') from dual;

Drop the Tuning Task:

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

execute dbms_sqltune.drop_tuning_task('my_tuning_task_1');

Find out state Tables:

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

set lines 160 pages 2000

col owner format a15

col table_name format a35

col last_analyzed format a35

col num_rows for 999999999999

SELECT RPAD(owner,15,' ') Owner, RPAD(table_name,35,' ') Table_Name, num_rows, RPAD(TO_CHAR(last_analyzed,'DD-MON-YYYY HH24:MI:SS'),35,' ') last_analyzed

FROM dba_tab_statistics

WHERE owner IN ('MALLIK')

AND stale_stats='YES'

ORDER BY owner;

Gather table state:

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

execute dbms_stats.gather_table_stats(ownname =>'MALLIK',tabname =>'STUDENT',estimate_percent =>100);

Execution logs from SQL Tuning Adviser:

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

1. Run SQL statement and capture the SQL ID:

[oracle@oraclelab3 ~]$ ps -ef|grep smon

oracle    4941     1  0 Nov12 ?        00:00:16 ora_smon_TESTDB

oracle    8631  5466  0 00:55 pts/1    00:00:00 grep --color=auto smon

[oracle@oraclelab3 ~]$ sqlplus mallik/mallik

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Nov 24 00:55:21 2021

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Wed Nov 24 2021 00:16:14 +05:30

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> select * from student;

      STNO STNAME

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

         1 Mallik

         2 John

        10 AAA

        10 AAA

        10 AAA

        10 AAA

        10 AAA

        10 AAA

        10 AAA

9 rows selected.

2. Validate the table and make sure there table is not in stale state:

SQL> set lines 160 pages 2000

col owner format a15

col table_name format a35

col last_analyzed format a35

col num_rows for 999999999999

SELECT RPAD(owner,15,' ') Owner, RPAD(table_name,35,' ') Table_Name, num_rows, RPAD(TO_CHAR(last_analyzed,'DD-MON-YYYY HH24:MI:SS'),35,' ') last_analyzed

FROM dba_tab_statistics

WHERE owner IN ('MALLIK')

AND stale_stats='YES'

ORDER BY owner;SQL> SQL> SQL> SQL> SQL>   2    3    4    5

no rows selected

SQL> select sql_id from v$sql where sql_text like 'select * from student';

SQL_ID

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

cdq26qbcmz1hc

SQL>

3. Create Tuning Task for the SQL ID:

[oracle@oraclelab3 ~]$ ps -ef|grep smon

oracle    4941     1  0 Nov12 ?        00:00:16 ora_smon_TESTDB

oracle    8049  5048  0 00:48 pts/0    00:00:00 grep --color=auto smon

[oracle@oraclelab3 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Nov 24 00:58:04 2021

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> DECLARE

  2  l_sql_tune_task_id VARCHAR2(100);

BEGIN

l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

sql_id => 'cdq26qbcmz1hc',

scope => DBMS_SQLTUNE.scope_comprehensive,

time_limit => 500,

task_name => 'my_tuning_task_1',

  3    4    5    6    7    8    9  description => 'Tuning task1 for statement cdq26qbcmz1hc');

DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

END;

/ 10   11   12

PL/SQL procedure successfully completed.

SQL> 

4. Run the Tuning Task and check the status:

SQL> EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'my_tuning_task_1');

PL/SQL procedure successfully completed.

SQL>

SQL> SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME='my_tuning_task_1';

TASK_NAME

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

STATUS

-----------

my_tuning_task_1

COMPLETED

SQL> 

5. Review the recommendation provided by Tuning Task:

SQL> set long 65536

set longchunksize 65536

set linesize 100

select dbms_sqltune.report_tuning_task('my_tuning_task_1') from dual;

SQL> SQL> SQL>

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TUNING_TASK_1')

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

GENERAL INFORMATION SECTION

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

Tuning Task Name   : my_tuning_task_1

Tuning Task Owner  : SYS

Workload Type      : Single SQL Statement

Scope              : COMPREHENSIVE

Time Limit(seconds): 500

Completion Status  : COMPLETED

Started at         : 11/24/2021 00:58:28

Completed at       : 11/24/2021 00:58:28

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TUNING_TASK_1')

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

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

Schema Name: MALLIK

SQL ID     : cdq26qbcmz1hc

SQL Text   : select * from student

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

There are no recommendations to improve the statement.

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

SQL>

6. Delete Some rows from table and table will become in stale state:

SQL> select * from student;

      STNO STNAME

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

         1 Mallik

         2 John

        10 AAA

        10 AAA

        10 AAA

        10 AAA

        10 AAA

        10 AAA

        10 AAA

9 rows selected.

SQL>

SQL> delete from student where STNO=10;

7 rows deleted.

SQL> commit;

Commit complete.

SQL> select * from student;

      STNO STNAME

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

         1 Mallik

         2 John

SQL> set lines 160 pages 2000

col owner format a15

SQL> SQL> col table_name format a35

col last_analyzed format a35

col num_rows for 999999999999

SELECT RPAD(owner,15,' ') Owner, RPAD(table_name,35,' ') Table_Name, num_rows, RPAD(TO_CHAR(last_analyzed,'DD-MON-YYYY HH24:MI:SS'),35,' ') last_analyzed

FROM dba_tab_statistics

WHERE owner IN ('MALLIK')

AND stale_stats='YES'

ORDER BY owner;SQL> SQL> SQL>   2    3    4    5

OWNER           TABLE_NAME                               NUM_ROWS LAST_ANALYZED

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

MALLIK          STUDENT                                         9 24-NOV-2021 00:28:55

SQL>

7. Now you can generate the new Tuning Task and see the recommendation from it:

SQL> DECLARE

l_sql_tune_task_id VARCHAR2(100);

BEGIN

l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

sql_id => 'cdq26qbcmz1hc',

scope => DBMS_SQLTUNE.scope_comprehensive,

time_limit => 500,

task_name => 'my_tuning_task_2',

description => 'Tuning task1 for statement cdq26qbcmz1hc');

DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

END;

/  2    3    4    5    6    7    8    9   10   11   12

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'my_tuning_task_2');

PL/SQL procedure successfully completed.

SQL> SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME='my_tuning_task_2';

TASK_NAME

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

STATUS

-----------

my_tuning_task_2

COMPLETED

SQL> set long 65536

set longchunksize 65536

set linesize 100

select dbms_sqltune.report_tuning_task('my_tuning_task_2') from dual;SQL> SQL> SQL>

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TUNING_TASK_2')

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

GENERAL INFORMATION SECTION

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

Tuning Task Name   : my_tuning_task_2

Tuning Task Owner  : SYS

Workload Type      : Single SQL Statement

Scope              : COMPREHENSIVE

Time Limit(seconds): 500

Completion Status  : COMPLETED

Started at         : 11/24/2021 01:02:18

Completed at       : 11/24/2021 01:02:18

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TUNING_TASK_2')

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

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

Schema Name: MALLIK

SQL ID     : cdq26qbcmz1hc

SQL Text   : select * from student

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

FINDINGS SECTION (1 finding)

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

1- Statistics Finding

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

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TUNING_TASK_2')

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

  Optimizer statistics for table "MALLIK"."STUDENT" are stale.

  Recommendation

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

  - Consider collecting optimizer statistics for this table.

    execute dbms_stats.gather_table_stats(ownname => 'MALLIK', tabname =>

            'STUDENT', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

            method_opt => 'FOR ALL COLUMNS SIZE AUTO');

  Rationale

  ---------

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TUNING_TASK_2')

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

    The optimizer requires up-to-date statistics for the table in order to

    select a good execution plan.

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

EXPLAIN PLANS SECTION

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

1- Original

-----------

Plan hash value: 2356778634

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TUNING_TASK_2')

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

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

| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |         |     9 |    63 |     3   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| STUDENT |     9 |    63 |     3   (0)| 00:00:01 |

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

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

SQL>

8. In this above Tuning task has given recommendation that table is in state stat and gather stats on the table

SQL> execute dbms_stats.gather_table_stats(ownname => 'MALLIK', tabname =>'STUDENT', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'FOR ALL COLUMNS SIZE AUTO');

PL/SQL procedure successfully completed.

SQL>

9. Now the table is not in stale state then you can generate the new tuning task which will not give any recommendation since table is upto date.

SQL> DECLARE

  2  l_sql_tune_task_id VARCHAR2(100);

BEGIN

l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

  3    4    5  sql_id => 'cdq26qbcmz1hc',

scope => DBMS_SQLTUNE.scope_comprehensive,

time_limit => 500,

task_name => 'my_tuning_task_3',

description => 'Tuning task1 for statement cdq26qbcmz1hc');

DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

  6  END;

/  7    8    9   10   11   12

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'my_tuning_task_3');

PL/SQL procedure successfully completed.

SQL> SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME='my_tuning_task_3';

TASK_NAME

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

STATUS

-----------

my_tuning_task_3

COMPLETED

SQL> set long 65536

set longchunksize 65536

set linesize 100

select dbms_sqltune.report_tuning_task('my_tuning_task_3') from dual;

SQL> SQL> SQL>

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TUNING_TASK_3')

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

GENERAL INFORMATION SECTION

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

Tuning Task Name   : my_tuning_task_3

Tuning Task Owner  : SYS

Workload Type      : Single SQL Statement

Scope              : COMPREHENSIVE

Time Limit(seconds): 500

Completion Status  : COMPLETED

Started at         : 11/24/2021 01:06:05

Completed at       : 11/24/2021 01:06:05

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TUNING_TASK_3')

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

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

Schema Name: MALLIK

SQL ID     : cdq26qbcmz1hc

SQL Text   : select * from student

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

There are no recommendations to improve the statement.

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

SQL>

10. Drop those tuning tasks:

SQL> execute dbms_sqltune.drop_tuning_task('my_tuning_task_1');

PL/SQL procedure successfully completed.

SQL> execute dbms_sqltune.drop_tuning_task('my_tuning_task_2');

PL/SQL procedure successfully completed.

SQL> execute dbms_sqltune.drop_tuning_task('my_tuning_task_3');

PL/SQL procedure successfully completed.

SQL>

Regards,

Mallik

Database Query Performance Issue After Database Upgraded to 12.2.0.1 from 11.2.0.4

Issue: 
Database Query Performance Issue After database Upgradated to 12.2.0.1 from 11.2.0.4

Solution: After troubleshooting we came up with the below 5 sections or scenarios to address the above query performance issue.


Section 1:
12c oracle home is at base level and not applied any patches.

Cause: No Patch applied at 12c oracle home, as per the oracle recommendation any oracle home should not be at base level since base release contain lots of bugs.

Steps:
1. Below are the patches recommended by oracle 

Database Oct 2019 Update 12.2.0.1.191015 Patch 30116802 for UNIX
OJVM Update 12.2.0.1.191015 Patch 30133625 for UNIX

2. Download the patches from MOS.

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=33968877088394&id=2568292.1&displayIndex=1&_afrWindowMode=0&_adf.ctrl-state=95g599v2j_496

3. Apply as per the readme 

30138470 - Apply this DB patch it will sub-patch of patch 30116802
30122814 - Apply this OCW patch it will sub-patch of patch 30116802
30133625 - Apply this OJVM patch

Section 2: 
Dictionary stats and fixed objects stats.

Cause: It seems Dictionary stats and fixed objects stats are not performed after database upgradation, Oracle strongly recommends to do dictionary stats and fixed objects stats after any database upgradation or migration.

Steps:
1. Please prepare the below sql script as Fixed_Dictionary_stats.sql

spool Fixed_Dictionary_stats.log
set time on
set timing on

exec DBMS_STATS.GATHER_DICTIONARY_STATS;
exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
execute dbms_stats.gather_schema_stats('SYS', method_opt=>'for all columns size 1', degree=>30,estimate_percent=>100,cascade=>true);
exec dbms_stats.gather_system_stats ('NOWORKLOAD');

2. Stop if any application running or connected 

3. The above script in nohup 

nohup sh sqlplus / as sysdba @Fixed_Dictionary_stats.sql &

Section 3:
Table move and Table shrink.

Cause: After any DML or after any database upgradation data inside the table is not aligned sequentially, it is recommended to do Table move and Table shrink on certain major Tables for better query performance.

Steps:
1. Please prepare the below sql script as Table_Move.sql

spool Table_move.log
set time on
set timing on

alter table XXLG.XLG_FAH_BULK_UPLOAD_TRANS move parallel nologging;
alter table XXLG.XLG_FAH_BULK_UPLOAD_TRANS noparallel logging;

alter table PROD_DW.WC_GL_BALANCE_F_S1 move parallel nologging;
alter table PROD_DW.WC_GL_BALANCE_F_S1 noparallel logging;

alter table PROD_DW.WC_ACCT_BUDGET_F_S1 move parallel nologging;
alter table PROD_DW.WC_ACCT_BUDGET_F_S1 noparallel logging;

alter table PROD_DW.WC_GL_GROUP_AACCOUNT_DH move parallel nologging;
alter table PROD_DW.WC_GL_GROUP_AACCOUNT_DH noparallel logging;

alter table PROD_DW.WC_HIERARCHY_DH move parallel nologging;
alter table PROD_DW.WC_HIERARCHY_DH noparallel logging;

alter table PROD_DW.WC_MFR_DATASECURITY_G move parallel nologging;
alter table PROD_DW.WC_MFR_DATASECURITY_G noparallel logging;

alter table PROD_DW.W_GL_ACCOUNT_D move parallel nologging;
alter table PROD_DW.W_GL_ACCOUNT_D noparallel logging;

alter table PROD_DW.W_MCAL_DAY_D move parallel nologging;
alter table PROD_DW.W_MCAL_DAY_D noparallel logging;

alter table PROD_DW.W_BUDGET_D move parallel nologging;
alter table PROD_DW.W_BUDGET_D noparallel logging;

2. Stop if any application running or connected 

3. The above script in nohup 

nohup sh sqlplus / as sysdba @Table_move.sql &

4. Please prepare the below sql script as Table_Shrink.sql

spool Table_shrink.log
set time on
set timing on

alter table XXLG.XLG_FAH_BULK_UPLOAD_TRANS ENABLE ROW MOVEMENT;
alter table XXLG.XLG_FAH_BULK_UPLOAD_TRANS SHRINK SPACE cascade;
alter table XXLG.XLG_FAH_BULK_UPLOAD_TRANS DISABLE ROW MOVEMENT;

alter table PROD_DW.WC_GL_BALANCE_F_S1 ENABLE ROW MOVEMENT;
alter table PROD_DW.WC_GL_BALANCE_F_S1 SHRINK SPACE cascade;
alter table PROD_DW.WC_GL_BALANCE_F_S1 DISABLE ROW MOVEMENT;

alter table PROD_DW.WC_ACCT_BUDGET_F_S1 ENABLE ROW MOVEMENT;
alter table PROD_DW.WC_ACCT_BUDGET_F_S1 SHRINK SPACE cascade;
alter table PROD_DW.WC_ACCT_BUDGET_F_S1 DISABLE ROW MOVEMENT;

alter table PROD_DW.WC_GL_GROUP_AACCOUNT_DH ENABLE ROW MOVEMENT;
alter table PROD_DW.WC_GL_GROUP_AACCOUNT_DH SHRINK SPACE cascade;
alter table PROD_DW.WC_GL_GROUP_AACCOUNT_DH DISABLE ROW MOVEMENT;

alter table PROD_DW.WC_HIERARCHY_DH ENABLE ROW MOVEMENT;
alter table PROD_DW.WC_HIERARCHY_DH SHRINK SPACE cascade;
alter table PROD_DW.WC_HIERARCHY_DH DISABLE ROW MOVEMENT;

alter table PROD_DW.WC_MFR_DATASECURITY_G ENABLE ROW MOVEMENT;
alter table PROD_DW.WC_MFR_DATASECURITY_G SHRINK SPACE cascade;
alter table PROD_DW.WC_MFR_DATASECURITY_G DISABLE ROW MOVEMENT;

alter table PROD_DW.W_GL_ACCOUNT_D ENABLE ROW MOVEMENT;
alter table PROD_DW.W_GL_ACCOUNT_D SHRINK SPACE cascade;
alter table PROD_DW.W_GL_ACCOUNT_D DISABLE ROW MOVEMENT;

alter table PROD_DW.W_MCAL_DAY_D ENABLE ROW MOVEMENT;
alter table PROD_DW.W_MCAL_DAY_D SHRINK SPACE cascade;
alter table PROD_DW.W_MCAL_DAY_D DISABLE ROW MOVEMENT;

alter table PROD_DW.W_BUDGET_D ENABLE ROW MOVEMENT;
alter table PROD_DW.W_BUDGET_D SHRINK SPACE cascade;
alter table PROD_DW.W_BUDGET_D DISABLE ROW MOVEMENT;

5. Stop if any application running or connected 

6. The above script in nohup 

nohup sh sqlplus / as sysdba @Table_shrink.sql &

Section 4: 
Rebuild index.

Cause: Recommendation to rebuild the index after any database upgradation or migration as per the SQL tunneling adviser.

Steps:
1. Please prepare the below sql script as Index_rebuild.sql

Assuming WC_GL_GROUP_AACCOUNT_DH_N1 as one index, we need to list all index and keep in the below script

spool Index_rebuild.log
set time on
set timing on

alter index PROD_DW.WC_GL_GROUP_AACCOUNT_DH_N1 coalesce;
alter index PROD_DW.WC_GL_GROUP_AACCOUNT_DH_N1 rebuild parallel nologging;
alter index PROD_DW.WC_GL_GROUP_AACCOUNT_DH_N1 noparallel logging;

2. Stop if any application running or connected 

3. The above script in nohup 

nohup sh sqlplus / as sysdba @Index_rebuil.sql &

Section 5:
Collect stats on the table with 100%.

Cause: One time we need to collect the stats with 100% on all major tables.

Steps:
1. Please prepare the below sql script as Table_stats.sql

spool Table_stats.log
set time on
set timing on

execute dbms_stats.gather_table_stats(ownname =>'PROD_DW',tabname =>'WC_GL_BALANCE_F_S1',estimate_percent =>100, method_opt =>'FOR ALL COLUMNS SIZE AUTO',CASCADE=>TRUE);
execute dbms_stats.gather_table_stats(ownname =>'PROD_DW',tabname =>'WC_ACCT_BUDGET_F_S1',estimate_percent =>100, method_opt =>'FOR ALL COLUMNS SIZE AUTO',CASCADE=>TRUE);
execute dbms_stats.gather_table_stats(ownname =>'PROD_DW',tabname =>'WC_GL_GROUP_AACCOUNT_DH',estimate_percent =>100, method_opt =>'FOR ALL COLUMNS SIZE AUTO',CASCADE=>TRUE);
execute dbms_stats.gather_table_stats(ownname =>'PROD_DW',tabname =>'WC_HIERARCHY_DH',estimate_percent =>100, method_opt =>'FOR ALL COLUMNS SIZE AUTO',CASCADE=>TRUE);
execute dbms_stats.gather_table_stats(ownname =>'PROD_DW',tabname =>'WC_MFR_DATASECURITY_G',estimate_percent =>100, method_opt =>'FOR ALL COLUMNS SIZE AUTO',CASCADE=>TRUE);
execute dbms_stats.gather_table_stats(ownname =>'PROD_DW',tabname =>'W_GL_ACCOUNT_D',estimate_percent =>100, method_opt =>'FOR ALL COLUMNS SIZE AUTO',CASCADE=>TRUE);
execute dbms_stats.gather_table_stats(ownname =>'PROD_DW',tabname =>'W_MCAL_DAY_D',estimate_percent =>100, method_opt =>'FOR ALL COLUMNS SIZE AUTO',CASCADE=>TRUE);
execute dbms_stats.gather_table_stats(ownname =>'PROD_DW',tabname =>'W_BUDGET_D',estimate_percent =>100, method_opt =>'FOR ALL COLUMNS SIZE AUTO',CASCADE=>TRUE);

2. Stop if any application running or connected 

3. The above script in nohup 

nohup sh sqlplus / as sysdba @Table_stats.sql &

Regards,

Mallik