Showing posts with label Performance Tuning. Show all posts
Showing posts with label Performance Tuning. Show all posts

Monday, March 6, 2023

java.lang.UnsupportedClassVersionError: com/dom/benchmarking/swingbench/SwingBench

 java.lang.UnsupportedClassVersionError:



[oracle@10.1.0.2 bin]$

[oracle@10.1.0.2 bin]$ ./swingbench

java.lang.UnsupportedClassVersionError: com/dom/benchmarking/swingbench/SwingBench has been compiled by a more recent version of the Java Runtime (class file version 61.0), this version of the Java Runtime only recognizes class file versions up to 52.0

        at java.lang.ClassLoader.defineClass1(Native Method)

        at java.lang.ClassLoader.defineClass(ClassLoader.java:756)

        at java.security.SecureClassLoader.defineClass(SecureClassLoader.java:142)

        at java.net.URLClassLoader.defineClass(URLClassLoader.java:473)

        at java.net.URLClassLoader.access$100(URLClassLoader.java:74)

        at java.net.URLClassLoader$1.run(URLClassLoader.java:369)

        at java.net.URLClassLoader$1.run(URLClassLoader.java:363)

        at java.security.AccessController.doPrivileged(Native Method)

        at java.net.URLClassLoader.findClass(URLClassLoader.java:362)

        at java.lang.ClassLoader.loadClass(ClassLoader.java:418)

        at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:352)

        at java.lang.ClassLoader.loadClass(ClassLoader.java:351)

        at java.lang.Class.forName0(Native Method)

        at java.lang.Class.forName(Class.java:264)

        at org.apache.commons.launcher.ChildMain.run(ChildMain.java:183)

[oracle@10.1.0.2 bin]$

[oracle@10.1.0.2 bin]$

[oracle@10.1.0.2 bin]$




[root@10.1.0.2 ~]#

[root@10.1.0.2 ~]# wget https://download.oracle.com/java/19/latest/jdk-19_linux-x64_bin.rpm

--2023-03-06 14:49:06--  https://download.oracle.com/java/19/latest/jdk-19_linux-x64_bin.rpm

Resolving download.oracle.com (download.oracle.com)... 23.56.12.90

Connecting to download.oracle.com (download.oracle.com)|23.56.12.90|:443... connected.

HTTP request sent, awaiting response... 200 OK

Length: 189609546 (181M) [application/x-redhat-package-manager]

Saving to: ‘jdk-19_linux-x64_bin.rpm’


jdk-19_linux-x64_bin.rpm       100%[==================================================>] 180.83M  99.6MB/s    in 1.8s


2023-03-06 14:49:08 (99.6 MB/s) - ‘jdk-19_linux-x64_bin.rpm’ saved [189609546/189609546]


[root@10.1.0.2 ~]#

[root@10.1.0.2 ~]#

[root@10.1.0.2 ~]#

[root@10.1.0.2 ~]# ls -lrt

total 260760

-rw-------. 1 root root      6699 May  4  2021  original-ks.cfg

-rw-------. 1 root root      6953 May  4  2021  anaconda-ks.cfg

-rw-r--r--  1 root root 189609546 Dec  7 23:14  jdk-19_linux-x64_bin.rpm

-rw-r--r--  1 root root  77218345 Jan  9 18:30 'AutoDL?BundleId=247937_0ae14417abb444ebb02b9815e2103550'

-rw-r--r--  1 root root     25289 Feb  6 10:25  aws-replication-installer-init.py

drwxr-xr-x  3 root root       105 Feb 18 13:01  mgn18022023

-rw-r--r--  1 root root    137320 Feb 18 13:07  aws_replication_agent_installer.log

[root@10.1.0.2 ~]#

[root@10.1.0.2 ~]#

[root@10.1.0.2 ~]# rpm -Uvh jdk-19_linux-x64_bin.rpm

warning: jdk-19_linux-x64_bin.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY

Verifying...                          ################################# [100%]

Preparing...                          ################################# [100%]

Updating / installing...

   1:jdk-19-2000:19.0.2-7             ################################# [100%]

[root@10.1.0.2 ~]#

[root@10.1.0.2 ~]#

[root@10.1.0.2 ~]#

[root@10.1.0.2 ~]#

[root@10.1.0.2 ~]# which java

/usr/bin/java

[root@10.1.0.2 ~]#

[root@10.1.0.2 ~]#

[root@10.1.0.2 ~]# java -version

java version "19.0.2" 2023-01-17

Java(TM) SE Runtime Environment (build 19.0.2+7-44)

Java HotSpot(TM) 64-Bit Server VM (build 19.0.2+7-44, mixed mode, sharing)

[root@10.1.0.2 ~]#

[root@10.1.0.2 ~]#



workaround :

 We have to install compatible java version so that application software (swingbench)  run

Friday, February 24, 2023

CPU utilization analysis using Oracle report (Statspack/AWR)

How to calculate CPU % used for Instance in Statspack report ?



Sample Statspack report :


Database    DB Id    Instance     Inst Num  Startup Time   Release     RAC

~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---

           11223344 ABCD              1 11-Aug-22 11:34 12.1.0.2.0  NO


Host Name             Platform                CPUs Cores Sockets   Memory (G)

~~~~ ---------------- ---------------------- ----- ----- ------- ------------

     test01      Solaris Operating Syst     4     4       2        320.0


Snapshot       Snap Id     Snap Time      Sessions Curs/Sess Comment

~~~~~~~~    ---------- ------------------ -------- --------- ------------------

Begin Snap:        113 11-Dec-22 02:00:05       44       1.1

  End Snap:        114 11-Dec-22 03:00:10       50       1.1

   Elapsed:      60.08 (mins) Av Act Sess:       1.1

   DB time:      63.19 (mins)      DB CPU:      63.11 (mins)

   





Top 5 Timed Events                                                    Avg %Total

~~~~~~~~~~~~~~~~~~                                                   wait   Call

Event                                            Waits    Time (s)   (ms)   Time

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

LGWR worker group idle                           2,592       7,632   2945   34.4

CPU time                                                     3,698          16.7

heartbeat redo informer                          3,604       3,605   1000   16.3

lreg timer                                       1,261       3,604   2858   16.3

AQPC idle                                          120       3,601  30010   16.2



CPU Time used  3698 s


Total CPU : NUM_cpu * elapsed_time *60 


num_cpu can be found from operating system statistics 


4*60.08*60= 14419.2s   for 3600s DB time 


total % of cpu used by Oracle process : 3698/14419 *100 = 25.64 %


Database only used approx. 26% of cpu 


calculate cpu consumption from OS perspective :

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


OS Statistics  DB/Inst: test/test  Snaps: 113-114

-> ordered by statistic type (CPU use, Virtual Memory, Hardware Config), Name


Statistic                                  Total

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

BUSY_TIME                                411,086

IDLE_TIME                              1,030,890

SYS_TIME                                  30,330

USER_TIME                                380,756

OS_CPU_WAIT_TIME                         784,200

RSRC_MGR_CPU_WAIT_TIME                         0

PHYSICAL_MEMORY_BYTES            343,560,974,336

NUM_CPUS                                       4

NUM_CPU_CORES                                  4

NUM_CPU_SOCKETS                                2

TCP_RECEIVE_SIZE_DEFAULT                 256,000

TCP_RECEIVE_SIZE_MAX                   1,048,576

TCP_SEND_SIZE_DEFAULT                     49,152

TCP_SEND_SIZE_MAX                      1,048,576






Host CPU  (CPUs: 4  Cores: 4  Sockets: 2)

~~~~~~~~              Load Average

                      Begin     End      User  System    Idle     WIO     WCPU

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

                       1.20    1.33     26.41    2.10   71.49    0.00   54.38



= user+ System 

26.41 + 2.10 = 28.51 



Instance CPU

~~~~~~~~~~~~                                       % Time (seconds)

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

                     Host: Total time (s):                 14,419.8

                  Host: Busy CPU time (s):                  4,110.9

                   % of time Host is Busy:      28.5

   

             Instance: Total CPU time (s):                  3,846.6

          % of Busy CPU used for Instance:      93.6

        Instance: Total Database time (s):                  3,889.2

  %DB time waiting for CPU (Resource Mgr):       0.0


16 cpu * 30/100 = 5  cpu 

%Busy_time = { busy_time/(busy_time+Idele_time)*100 


  411086  

+1030890 

==========

 1441976


           = (411086/1441976) *100 = 28.50 




Busy_time= SYS_time + USER_TIME


   30330

+ 380756

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

  411086


% SYS time =  (sys_time/busy_time ) *100 = (30330/411086) *100 = 7.3 out of %Busy_time : 28.51 

% User time=  (user_time/busy_time) * 100 = 92.62  out of %Busy_time : 28.51 





Sunday, February 19, 2023

How to install, configure, and use Statspack in Oracle database step by step ?

High Level steps for Statspack install and configure :



1. How to check Statspack installed or not ?

    select * from stats$level_description;


    *If no output and error "table doesn't exists" 


2. Create sperate tablespace for keep Statspack  objects 


create tablespace stat_pack datafile '/u01/app/oracle/oradata/INDIA/stat_pack01.dbf' size 500M autoextend on maxsize 30G;


#####################################################################

For Statspack installation, need to run below command using sysdba login 

#####################################################################


@/u01/app/oracle/product/19.0/dbhome1/rdbms/admin/spcreate.sql


SQL>

SQL> @/u01/app/oracle/product/19.0/dbhome1/rdbms/admin/spcreate.sql


Session altered.


Choose the PERFSTAT user's password

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

Not specifying a password will result in the installation FAILING


Enter value for perfstat_password: perfstat

perfstat



Choose the Default tablespace for the PERFSTAT user

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

Below is the list of online tablespaces in this database which can

store user data.  Specifying the SYSTEM tablespace for the user's

default tablespace will result in the installation FAILING, as

using SYSTEM for performance data is not supported.


Choose the PERFSTAT users's default tablespace.  This is the tablespace

in which the STATSPACK tables and indexes will be created.


TABLESPACE_NAME                CONTENTS

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

STATSPACK DEFAULT TABLESPACE

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

STAT_PACK                      PERMANENT



SYSAUX                         PERMANENT

*


USERS                          PERMANENT




Pressing <return> will result in STATSPACK's recommended default

tablespace (identified by *) being used.


Enter value for default_tablespace: STAT_PACK


Using tablespace STAT_PACK as PERFSTAT default tablespace.



Choose the Temporary tablespace for the PERFSTAT user

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

Below is the list of online tablespaces in this database which can

store temporary data (e.g. for sort workareas).  Specifying the SYSTEM

tablespace for the user's temporary tablespace will result in the

installation FAILING, as using SYSTEM for workareas is not supported.


Choose the PERFSTAT user's Temporary tablespace.


TABLESPACE_NAME                CONTENTS              DB DEFAULT TEMP TABLESPACE

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

TEMP                           TEMPORARY             *


Pressing <return> will result in the database's default Temporary

tablespace (identified by *) being used.


Enter value for temporary_tablespace: TEMP


Using tablespace TEMP as PERFSTAT temporary tablespace.



... Creating PERFSTAT user



... Installing required packages



... Creating views



... Granting privileges

.

.

.

…………will take 2 min to complete , need to check there is no error 



############################

Below parameter should true 

#############################


SQL> show parameter timed_statistic


NAME                                 TYPE        VALUE

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

timed_statistics                     boolean     TRUE

SQL>



##########################################

To check Statspack Level for take snapshot

############################################


SQL> l

  1* select * from stats$level_description

SQL> /


SNAP_LEVEL DESCRIPTION

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

         0 This level captures general statistics, including rollback s

           egment, row cache, SGA, system events, background events, se

           ssion events, system statistics, wait statistics, lock stati

           stics, and Latch information


         5 This level includes capturing high resource usage SQL Statem

           ents, along with all data captured by lower levels


         6 This level includes capturing SQL plan and SQL plan usage in

           formation for high resource usage SQL Statements, along with

            all data captured by lower levels


         7 This level captures segment level statistics, including logi

           cal and physical reads, row lock, itl and buffer busy waits,

            along with all data captured by lower levels


        10 This level includes capturing Child Latch statistics, along

           with all data captured by lower levels



############################################

Login with perfstat user and take snapshot 

##############################################



[oracle@10.22.3333.2222 admin]$ sqlplus perfstat/perfstat

SQL>

SQL> show user

USER is "PERFSTAT"

SQL>

SQL> select name,snap_id,to_char(snap_time,'DD-MON-YYYY:HH24:MI:SS')

     "Date/Time" from stats$snapshot,v$database;


SQL> execute statspack.snap(i_snap_level=>10);

PL/SQL procedure successfully completed.



########################

To run report 

######################


[oracle@10.22.3333.2222 admin]$

[oracle@10.22.3333.2222 admin]$ sqlplus perfstat/perfstat


SQL*Plus: Release 19.0.0.0.0 - Production on Tue Dec 6 07:45:00 2022

Version 19.3.0.0.0


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


Last Successful login time: Tue Dec 06 2022 07:41:01 +00:00


Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0


SQL>


SQL> @/u01/app/oracle/product/19.0/dbhome1/rdbms/admin/spreport.sql


Current Instance

~~~~~~~~~~~~~~~~


   DB Id    DB Name      Inst Num Instance

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

 3280227920 INDIA               1 india




Instances in this Statspack schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


   DB Id    Inst Num DB Name      Instance     Host

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

 3280227920        1 INDIA        india        ip-172-31-19

                                               -40.ec2.inte

                                               rnal


Using 3280227920 for database Id

Using          1 for instance number



Specify the number of days of snapshots to choose from

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Entering the number of days (n) will result in the most recent

(n) days of snapshots being listed.  Pressing <return> without

specifying a number lists all completed snapshots.




Listing all Completed Snapshots


                                                       Snap

Instance     DB Name        Snap Id   Snap Started    Level Comment

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

india        INDIA                1 06 Dec 2022 07:38    10

                                  2 06 Dec 2022 07:41    10

                                  3 06 Dec 2022 07:41    10

                                  4 06 Dec 2022 07:41    10

                                  5 06 Dec 2022 07:41    10




Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: 1

Begin Snapshot Id specified: 1


Enter value for end_snap: 5

End   Snapshot Id specified: 5




Specify the Report Name

~~~~~~~~~~~~~~~~~~~~~~~

The default report file name is sp_1_5.  To use this name,

press <return> to continue, otherwise enter an alternative.


Enter value for report_name: sp_1_5_report_india


Using the report name sp_1_5_report_india


Thursday, June 3, 2021

Difference between hash_value and plan_hash_value in oracle

 hash_value and plan_hash_value



select sql_text,sql_id,hash_value,plan_hash_value from v$sql where sql_text like '%last_name%'






Tuesday, May 18, 2021

Oracle Trace File Analyzer (TFA)

Oracle Trace File Analyzer (TFA)

Oracle Trace File Analyzer helps you perform real-time health monitoring, fault detection & diagnosis via a single interface. It will securely consolidate all distributed diagnostic data.


[oracle@ip-10.1.1.1.1 bin]$ ps -ef|grep tfa

root         687       1  0 05:50 ?        00:00:00 /bin/sh /etc/init.d/init.tfa run >/dev/null 2>&1 </dev/null

root         837       1  0 05:50 ?        00:00:16 /u01/app/oracle/tfa/ip-10.1.1.1.1/tfa_home/jre/bin/java -Xms128m -Xmx512m oracle.rat.tfa.TFAMain /u01/app/oracle/tfa/ip-10.1.1.1.1/tfa_home

oracle     22559    8133  0 07:05 pts/2    00:00:00 grep --color=auto tfa

[oracle@ip-10.1.1.1.1 bin]$ cd /u01/app/oracle/tfa

[root@ip-10.1.1.1.1 bin]#

[root@ip-10.1.1.1.1 bin]# ./tfactl status


.---------------------------------------------------------------------------------------------------.

| Host         | Status of TFA | PID | Port  | Version    | Build ID             | Inventory Status |

+--------------+---------------+-----+-------+------------+----------------------+------------------+

| ip-10.1.1.1.1 | RUNNING       | 837 | 31183 | 12.2.1.0.0 | 12210020161122170355 | COMPLETE         |

'--------------+---------------+-----+-------+------------+----------------------+------------------'

[root@ip-10.1.1.1.1 bin]#

[root@ip-10.1.1.1.1 bin]#

[root@ip-10.1.1.1.1 bin]#

[root@ip-10.1.1.1.1 bin]# ./tfactl toolstatus

.----------------------------------------.

|         External Support Tools         |

+--------------+--------------+----------+

| Host         | Tool         | Status   |

+--------------+--------------+----------+

| ip-10.1.1.1.1 | events       | DEPLOYED |

| ip-10.1.1.1.1 | orachk       | DEPLOYED |

| ip-10.1.1.1.1 | vi           | DEPLOYED |

| ip-10.1.1.1.1 | dbglevel     | DEPLOYED |

| ip-10.1.1.1.1 | exachk       | DEPLOYED |

| ip-10.1.1.1.1 | managelogs   | DEPLOYED |

| ip-10.1.1.1.1 | menu         | DEPLOYED |

| ip-10.1.1.1.1 | changes      | DEPLOYED |

| ip-10.1.1.1.1 | param        | DEPLOYED |

| ip-10.1.1.1.1 | history      | DEPLOYED |

| ip-10.1.1.1.1 | triage       | DEPLOYED |

| ip-10.1.1.1.1 | calog        | DEPLOYED |

| ip-10.1.1.1.1 | grep         | DEPLOYED |

| ip-10.1.1.1.1 | summary      | DEPLOYED |

| ip-10.1.1.1.1 | ls           | DEPLOYED |

| ip-10.1.1.1.1 | srdc         | DEPLOYED |

| ip-10.1.1.1.1 | pstack       | DEPLOYED |

| ip-10.1.1.1.1 | tail         | DEPLOYED |

| ip-10.1.1.1.1 | alertsummary | DEPLOYED |

| ip-10.1.1.1.1 | ps           | DEPLOYED |

'--------------+--------------+----------'

[root@ip-10.1.1.1.1 bin]#

[root@ip-10.1.1.1.1 bin]#

[root@ip-10.1.1.1.1 bin]# pwd

/u01/app/oracle/tfa/bin

[root@ip-10.1.1.1.1 bin]#



Friday, November 13, 2020

Wait Event : Log File Sync

 What Exactly Is A 'Log File Sync' Wait event?

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



Log file sync wait event, which in many cases is caused by poor storage performance.


user session issuing a commit command must wait until the LGWR (Log Writer) process writes the log entries associated with the user transaction to the log file on the disk.

Oracle must commit the transaction’s entries to disk (because it is a persistent layer) before acknowledging the transaction commit.

 

At commit time, a process creates a redo record (containing commit opcodes) and copies that redo record into the log buffer. Then, that process signals LGWR to write the contents of log buffer. LGWR writes from the log buffer to the log file and signals user process back completing a commit. A commit is considered successful after the LGWR write is successful.

 

 

A commit is not complete until LGWR writes the log buffers, including the commit redo records to the log files. In a nutshell, after posting LGWR to write, user or background processes wait for LGWR to signal back with a 1-second timeout. The User process charges this wait time as a ‘log file sync’ event.

 

The log file sync wait event represents the time the session is waiting for the log buffers to be written to disk.

 

For example, the following user transaction consists of Insert, Select and Update statements, and completes with a commit:


User Transaction:


Insert…

Select…

Update…

Commit…



Root Causes for 'Log File Sync' Waits

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


LGWR is unable to complete writes fast enough for one of the following reasons:


Disk I/O performance to log files is not good.

LGWR is starving for CPU resource. If the server is very busy, LGWR can starve for CPU too. 

LGWR is unable to complete writes fast enough due to file system or Unix buffer cache limitations.



Guidelines For Resolving 'Log File Sync' Waits

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

If CPU starvation is an issue, then reducing CPU starvation is the correct step to resolve it.


If commit rate is higher, then decreasing commits is correct step but, in a few cases, if that is not possible, increasing the priority of LGWR (using nice) or increasing the priority class of LGWR to RT might provide some relief.


Solid State Disk devices also can be used if the redo size is extreme. in some case , it is also preferable to decrease redo size.


check redo switch per hours , normally it should be 4-5 switch per hours , Sometimes redo logs are very less in size , Oracle recommends, maximum 8 switch per hour .It may be change as per environment to environment  . 


User commit %  and user rollback % can be seen from AWR report which could help to troubleshoot the issue . 


If Redo logs are undersized or huge size both are not good , so tune properly. 


Test in Non-Prod before do any changes in production .


 



Wednesday, June 10, 2020

How to check DML operations oracle


check dml operations oracle



SQL> exec dbms_stats.gather_table_stats(ownname=>'TEST',tabname=>'TEST_1')

PL/SQL procedure successfully completed.

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

SQL>
SQL>
SQL> select table_name,inserts,updates,deletes,truncated,timestamp from sys.dba_tab_modifications where table_owner='TEST' and table_name= 'TEST_1'
  2  ;

no rows selected

SQL>
SQL> insert  into test.test_1 select * from dba_objects;

73824 rows created.

SQL> /

73824 rows created.

SQL> commit;

Commit complete.

SQL> select table_name,inserts,updates,deletes,truncated,timestamp from sys.dba_tab_modifications where table_owner='TEST' and table_name= 'TEST_1';

TABLE_NAME                        INSERTS    UPDATES    DELETES TRU TIMESTAMP
------------------------------ ---------- ---------- ---------- --- ---------
TEST_1                             147648          0          0 NO

SQL>

Query to list Unindexed Foreign Keys



Query to get Unindexed Foreign Keys 


undefine schema_owner
set pagesize 5000
set linesize 350
column status format a10
column table_name format a30
column fk_name format a30
column fk_columns format a30
column index_name format a30
column index_columns format a30


select
      case
        when i.index_name is not null then
          'indexed'
        else
          'unindexed'
       end                  as status
      ,c.table_name         as table_name
      ,c.constraint_name    as fk_name
      ,c.fk_columns         as fk_columns
      ,i.index_name         as index_name
      ,i.index_columns      as index_columns
from
    (
      select  a.table_name
             ,a.constraint_name
             ,listagg(b.column_name, ' ' )
              within group (order by column_name) as fk_columns
      from
              dba_constraints a
             ,dba_cons_columns b
      where
              a.constraint_name = b.constraint_name
      and     a.constraint_type = 'R'
      and     a.owner           = b.owner
      and     a.owner           = '&&schema_owner'
      group by
              a.table_name
             ,a.constraint_name
    ) c
left outer join
  (
    select  table_name
           ,index_name
           ,cr
           ,listagg(column_name, ' ')
            within group (order by column_name) as index_columns
    from
        (
          select
                  table_name
                 ,index_name
                 ,column_position
                 ,column_name
                 ,connect_by_root(column_name) cr
          from
                  dba_ind_columns
          where
                  index_owner               = '&&schema_owner'
          connect
                 by prior column_position-1 = column_position
                 and prior index_name       = index_name
         )
    group by table_name
            ,index_name, cr
  ) i
      on c.fk_columns = i.index_columns
     and c.table_name = i.table_name;





SQL> undefine schema_owner
set pagesize 5000
set linesize 350
column status format a10
column table_name format a30
column fk_name format a30
SQL> SQL> SQL> SQL> SQL> SQL> column fk_columns format a30
column index_name format a30
column index_columns format a30


select
SQL> SQL> SQL> SQL> SQL>   2        case
        when i.index_name is not null then
          'indexed'
        else
          'unindexed'
       end                  as status
  3    4    5    6    7    8        ,c.table_name         as table_name
      ,c.constraint_name    as fk_name
      ,c.fk_columns         as fk_columns
      ,i.index_name         as index_name
      ,i.index_columns      as index_columns
  9   10   11   12   13  from
    (
      select  a.table_name
             ,a.constraint_name
             ,listagg(b.column_name, ' ' )
 14   15   16   17   18                within group (order by column_name) as fk_columns
      from
              dba_constraints a
 19   20   21               ,dba_cons_columns b
      where
              a.constraint_name = b.constraint_name
      and     a.constraint_type = 'R'
 22   23   24   25        and     a.owner           = b.owner
      and     a.owner           = '&&schema_owner'
 26   27        group by
 28                a.table_name
             ,a.constraint_name
    ) c
 29   30   31  left outer join
  (
    select  table_name
           ,index_name
           ,cr
 32   33   34   35   36             ,listagg(column_name, ' ')
            within group (order by column_name) as index_columns
    from
 37   38   39          (
 40            select
                  table_name
                 ,index_name
                 ,column_position
                 ,column_name
                 ,connect_by_root(column_name) cr
          from
 41   42   43   44   45   46   47                    dba_ind_columns
          where
                  index_owner               = '&&schema_owner'
          connect
                 by prior column_position-1 = column_position
                 and prior index_name       = index_name
 48           )
    group by table_name
            ,index_name, cr
 49   50   51   52   53   54   55   56    ) i
      on c.fk_columns = i.index_columns
     and c.table_name = i.table_name; 57   58
Enter value for schema_owner: HR
old  26:       and     a.owner           = '&&schema_owner'
new  26:       and     a.owner           = 'HR'
old  49:                   index_owner               = '&&schema_owner'
new  49:                   index_owner               = 'HR'

STATUS     TABLE_NAME                     FK_NAME                        FK_COLUMNS                     INDEX_NAME                     IN                       DEX_COLUMNS
---------- ------------------------------ ---------------  ------- ------------------------------ ----------------       ---------------        --------------                    
indexed    EMPLOYEES                      EMP_JOB_FK                     JOB_ID                         EMP_JOB_IX                     JO                       B_ID
indexed    EMPLOYEES                      EMP_MANAGER_FK                 MANAGER_ID                     EMP_MANAGER_IX                 MA                       NAGER_ID
indexed    EMPLOYEES                      EMP_DEPT_FK                    DEPARTMENT_ID                  EMP_DEPARTMENT_IX              DE                       PARTMENT_ID
indexed    LOCATIONS                      LOC_C_ID_FK                    COUNTRY_ID                     LOC_COUNTRY_IX                 CO                       UNTRY_ID
indexed    DEPARTMENTS                    DEPT_LOC_FK                    LOCATION_ID                    DEPT_LOCATION_IX               LO                       CATION_ID
indexed    JOB_HISTORY                    JHIST_JOB_FK                   JOB_ID                         JHIST_JOB_IX                   JO                       B_ID
indexed    JOB_HISTORY                    JHIST_EMP_FK                   EMPLOYEE_ID                    JHIST_EMPLOYEE_IX              EM                       PLOYEE_ID
indexed    JOB_HISTORY                    JHIST_DEPT_FK                  DEPARTMENT_ID                  JHIST_DEPARTMENT_IX            DE                       PARTMENT_ID
indexed    JOB_HISTORY                    JHIST_EMP_FK                   EMPLOYEE_ID                    JHIST_EMP_ID_ST_DATE_PK        EM                       PLOYEE_ID
unindexed  DEPARTMENTS                    DEPT_MGR_FK                    MANAGER_ID
unindexed  COUNTRIES                      COUNTR_REG_FK                  REGION_ID

11 rows selected.

SQL>

Friday, January 3, 2020

Slow SQL Query troubleshoot steps


Use below step to troubleshoot slow sql query


Tablespace free space 
==============================

set lines 200
set pages 5000
column "Tablespace" format a50
column "Used MB"    format 99,999,999
column "Free MB"    format 99,999,999
column "Total MB"   format 99,999,999
select
  fs.tablespace_name                          "Tablespace",
  (df.totalspace - fs.freespace)              "Used MB",
  fs.freespace                                "Free MB",
  df.totalspace                               "Total MB",
  round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
from
  (select
     tablespace_name,
     round(sum(bytes) / 1048576) TotalSpace
  from
     dba_data_files
  group by
     tablespace_name
  ) df,
  (select
     tablespace_name,
     round(sum(bytes) / 1048576) FreeSpace
  from
     dba_free_space
  group by
     tablespace_name
  ) fs
where
  df.tablespace_name = fs.tablespace_name order by 5;


Temp tablespace usages report 
======================================

SELECT 
   A.tablespace_name tablespace, 
   D.gb_total,
   SUM (A.used_blocks * D.block_size) / 1024 / 1024/1024 gb_used,
   D.gb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024/1024 gb_free
FROM 
   v$sort_segment A,
(
SELECT 
   B.name, 
   C.block_size, 
   SUM (C.bytes) / 1024 / 1024 /1024 gb_total
FROM 
   v$tablespace B, 
   v$tempfile C
WHERE 
   B.ts#= C.ts#
GROUP BY 
   B.name, 
   C.block_size
) D
WHERE 
   A.tablespace_name = D.name
GROUP by 
   A.tablespace_name, 
   D.gb_total
/

it will give you queries currently running for more than 60 seconds
 =============================================================================

select s.username,s.sid,s.serial#,s.last_call_et/60 mins_running,q.sql_text from v$session s 
join v$sqltext_with_newlines q
on s.sql_address = q.address
 where status='ACTIVE'
and type <>'BACKGROUND'
and last_call_et> 60
order by sid,serial#,q.piece

How to Identify SID Based on OS PID in Oracle
===================================================

col sid format 99999
col username format a20
col osuser format a15
select p.spid,s.sid, s.serial#,s.username, s.osuser
from gv$session s, gv$process p
where s.paddr= p.addr
and p.spid='&spid'
order by p.spid;

query completion percent :
===================================
COLUMN percent FORMAT 999.99 

SELECT sid, to_char(start_time,'hh24:mi:ss') stime, 
message,( sofar/totalwork)* 100 percent 
FROM v$session_longops
WHERE sofar/totalwork < 1
/


Query1 : using :  SYS_CONTEXT('USERENV','IP_ADDRESS')
=============================================================

SELECT DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, TRUNC(SYSDATE - LOGON_TIME) || ' Days' || ' + ') ||
TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE-LOGON_TIME,1) * 86400), 'SSSSS'), 'HH24:MI:SS') LOGON,
v$session.SID, v$session.SERIAL#, v$process.SPID spid, v$session.process CLPRID,
v$session.USERNAME, STATUS, OSUSER, MACHINE, 
SYS_CONTEXT('USERENV','IP_ADDRESS') IP, 
v$session.PROGRAM, MODULE, action, SQL_HASH_VALUE,
'alter system kill session ' || '''' || v$session.SID || ', ' || v$session.SERIAL# || '''' || ' immediate;' kill_sql
FROM v$session, v$process
WHERE v$session.paddr = v$process.addr
ORDER BY logon_time ASC;


Query2: using  UTL_INADDR.GET_HOST_ADDRESS (SUBSTR(machine,INSTR(machine,'')+1)) IP
=========================================================================================

SELECT DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, TRUNC(SYSDATE - LOGON_TIME) || ' Days' || ' + ') ||
TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE-LOGON_TIME,1) * 86400), 'SSSSS'), 'HH24:MI:SS') LOGON,
v$session.SID, v$session.SERIAL#, v$process.SPID spid, v$session.process CLPRID,
v$session.USERNAME, STATUS, OSUSER, MACHINE, 
UTL_INADDR.GET_HOST_ADDRESS (SUBSTR(machine,INSTR(machine,'')+1)) IP, 
v$session.PROGRAM, MODULE, action, SQL_HASH_VALUE,
'alter system kill session ' || '''' || v$session.SID || ', ' || v$session.SERIAL# || '''' || ' immediate;' kill_sql
FROM v$session, v$process
WHERE v$session.paddr = v$process.addr
ORDER BY logon_time ASC;


Check for any stale statistics.
=========================================

set lines 200 pages 500
col TABLE_NAME for a30
col PARTITION_NAME for a20
col SUBPARTITION_NAME for a20
select OWNER,TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,NUM_ROWS,LAST_ANALYZED from dba_TAB_STATISTICS where STALE_STATS='YES';



Check any invalid index/Partition
=======================================

col TABLE_NAME for a30
select owner,index_name,TABLE_NAME,NUM_ROWS,LAST_ANALYZED,STATUS from dba_indexes where status not in ('VALID','N/A');

select INDEX_OWNER,INDEX_NAME,PARTITION_NAME,SUBPARTITION_COUNT,LAST_ANALYZED,STATUS from dba_ind_partitions where status <> 'USABLE';


Check free memory shared pool area. Check too much hard parsing.
======================================================================

select * from (select SQL_ID,PARSING_SCHEMA_NAME, count(1) from v$sql group by SQL_ID,PARSING_SCHEMA_NAME order by  3 desc,2) where rownum<=10;

 Wait/ Blocking analysis.
 ============================


SELECT sid, serial#, blocking_session_status, blocking_session
FROM   v$session
WHERE  blocking_session IS NOT NULL;

-- Display the resource or event the session is waiting for more than 1 minutes

SELECT sid, serial#, event, (seconds_in_wait/1000000) seconds_in_wait
FROM   v$session
where (seconds_in_wait/1000000) > 60
ORDER BY sid;

select sid,seq#,event,state,SECONDS_IN_WAIT from v$session_wait where SECONDS_IN_WAIT > 60;

--Monitor Top Waiting Event Using Active Session History (ASH)

SELECT h.event,
SUM(h.wait_time + h.time_waited) "Total Wait Time (ms)"
FROM v$active_session_history h, v$sqlarea SQL, dba_users u, v$event_name e
WHERE h.sample_time BETWEEN sysdate - 1/24 AND sysdate --event in the last hour
AND h.sql_id = SQL.sql_id
AND h.user_id = u.user_id
AND h.event# = e.event#
GROUP BY h.event
ORDER BY SUM(h.wait_time + h.time_waited) DESC;


Tuning SQL using 
========================

@?/rdbms/admin/sqltrpt


AWR various Report.
=========================

@?/rdbms/admin/awrsqrpt.sql --> awr report for only single sql_id
@?/rdbms/admin/awrrpt.sql   --> Traditional awr report for instance.

RAC Related awr Report

@?/rdbms/admin/awrgrpt.sql -- AWR Global Report (RAC) (global report)
@?/rdbms/admin/awrrpti.sql -- Workload Repository Report Instance (RAC)


Long running jobs 
===========================


 SELECT SID, SERIAL#,OPNAME, CONTEXT, SOFAR, TOTALWORK,ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE" FROM V$SESSION_LONGOPS WHERE OPNAME NOT LIKE '%aggregate%' AND TOTALWORK != 0 AND SOFAR <> TOTALWORK;



Check the sessions by username
================================================== 

column box format a30
column spid format a10
column username format a30 
column program format a30
column os_user format a20
col LOGON_TIME for a20  

select b.inst_id,b.sid,b.serial#,a.spid, substr(b.machine,1,30) box,to_char (b.logon_time, 'dd-mon-yyyy hh24:mi:ss') logon_time,
 substr(b.username,1,30) username,
 substr(b.osuser,1,20) os_user,
 substr(b.program,1,30) program,status,b.last_call_et AS last_call_et_secs,b.sql_id 
 from gv$session b,gv$process a 
 where b.paddr = a.addr 
 and a.inst_id = b.inst_id  
 and type='USER' and b.username='&username'
-- and b.status='ACTIVE'
-- and b.status='INACTIVE'
 order by logon_time;


Check the sessions by SID
==================================================
column box format a30
column spid format a10
column username format a30 
column program format a30
column os_user format a20
col LOGON_TIME for a20  

select b.inst_id,b.sid,b.serial#,a.spid, substr(b.machine,1,30) box,to_char (b.logon_time, 'dd-mon-yyyy hh24:mi:ss') logon_time,
 substr(b.username,1,30) username,
 substr(b.osuser,1,20) os_user,
 substr(b.program,1,30) program,status,b.last_call_et AS last_call_et_secs,b.sql_id 
 from gv$session b,gv$process a 
 where b.paddr = a.addr 
 and a.inst_id = b.inst_id  
 and type='USER' and b.SID='&SID'
-- and b.status='ACTIVE'
-- and b.status='INACTIVE'
 order by logon_time;


SQL response time 
=========================

select to_char(begin_time,'hh24:mi') time, round( value * 10, 2) "Response Time (ms)"
     from v$sysmetric
     where metric_name='SQL Service Response Time'
 
Completion Time remianing 
==============================

SELECT s.inst_id,
       s.sid,
       s.serial#,
       sl.qcsid,
       s.username,
       s.module,
       sl.opname,
       sl.time_remaining/60 time_remaining
FROM   gv$session s,
       gv$session_longops sl
WHERE  s.sid     = sl.sid
AND    s.inst_id = sl.inst_id
AND    s.serial# = sl.serial#
and sl.totalwork<>sl.sofar;


Blocking info
======================

col blocking_status for a100 
 select s1.inst_id,s2.inst_id,s1.username || '@' || s1.machine
 || ' ( SID=' || s1.sid || ' )  is blocking '
 || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
  from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
  where s1.sid=l1.sid and s2.sid=l2.sid and s1.inst_id=l1.inst_id and s2.inst_id=l2.inst_id
  and l1.BLOCK=1 and l2.request > 0
  and l1.id1 = l2.id1
  and l2.id2 = l2.id2
order by s1.inst_id;

 check active sql running in server 
========================================= 
   
   
 set echo off  linesize 200  pages 1000  head on  feedback on
 col username format a30
 col start_time format a15
 col curr_time format a15
 col osuser format a10
 col opname format a35
 col target format a25
 col tremain format 999999.99
 col elamin format 999999.99
 select a.sid,substr(b.username,1,10) username,substr(b.osuser,1,10) osuser,to_char(a.start_time,'dd-mon:hh24:mi:ss') start_time,
 to_char(sysdate,'dd-mon:hh24:mi:ss') curr_time,a.opname,a.target,a.totalwork,a.sofar,(a.elapsed_Seconds)/60 elamin,
 a.time_remaining/60 tremain
 from v$session_longops a,
 v$session b
 where a.totalwork<>a.sofar
 and a.sid=b.sid
 order by 3
 /




select *
  from v$sqlarea sqlarea, v$session sesion
 where sesion.sql_hash_value = sqlarea.hash_value
   and sesion.sql_address    = sqlarea.address
   and sesion.username is not null
   and sesion.sid='&sid'