How to connect with Oracle database using Azure Data Studio ?

 

check the Data Source section :

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 

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

Checking swap space: 0 MB available, 150 MB required. Failed

Checking swap space: 0 MB available, 150 MB required.    Failed 

[oracle@oracle12c database]$ ./runInstaller

Starting Oracle Universal Installer...

Checking Temp space: must be greater than 500 MB.   Actual 1991 MB    Passed

Checking swap space: 0 MB available, 150 MB required.    Failed 

Checking monitor: must be configured to display at least 256 colors.    Actual 16777216    Passed

Some requirement checks failed. You must fulfill these requirements before

continuing with the installation,

User Selected: No

Solution :

Need to add swap space 

https://anuragkumarjoy.blogspot.com/2020/04/adding-swap-space-in-linux.html

[root@oracle12c ~]# free -g

              total        used        free      shared  buff/cache   available

Mem:              7           0           2           0           4           6

Swap:             1           0           1

[root@oracle12c ~]#