Generating Statspack, AWR and ADDM reports

Generating Statspack, AWR and ADDM reports

 

STATSPACK

The Statspack package is a set of SQL, PL/SQL, and SQL*Plus scripts that allow the collection, automation, storage, and viewing of performance data. Statspack stores the performance statistics permanently in Oracle tables, which can later be used for reporting and analysis.

The data collected can be analyzed using Statspack reports, which includes an instance health and load summary page, high resource SQL statements, and the traditional wait events and initialization parameters.

TO GENERATE STATSPACK REPORT , FOLLOW THE BELOW STEPS :

Step 1:-Connect to the db as sys user and check out for the value of the parameter timed_statistics as true

SQL> show parameter timed_statistics;

If the value  false set as true

SQL> alter system set timed_statistics=true scope=both;

 

Step 2;-Check whether the user perfstat user already exists. If the user is frequently used, don’t drop it.

SQL>select username from dba_users;

Step 3:- Otherwise run the script spdrop ($ORACLE_HOME\rdbms\admin\spdrop.sql) to drop the existing perfstat user.

SQL>@$ORACLE_HOME/rdbms/admin/spdrop.sql

Step 4:- Run the script spcreate to create the perfstat user.($ORACLE_HOME\rdbms\admin\spcreate.sql)

 

Step 5:- It will ask for the user’s password, default and temp tablespace names

SQL>@$ORACLE_HOME/rdbms/admin/spcreate.sql

SQL> set echo off verify off showmode off feedback off;

Choose the PERFSTAT user's password

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

Not specifying a password will result in the installation FAILING

Enter value for perfstat_password: oracle

oracle

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

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

SYSAUX                         PERMANENT

*

USERS                          PERMANENT

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

tablespace (identified by *) being used.

Enter value for default_tablespace: users

Using tablespace USERS 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.

SQL> set echo off;

Creating Package STATSPACK...

Package created.

No errors.

Creating Package Body STATSPACK...

Package body created.

No errors.

NOTE:

SPCPKG complete. Please check spcpkg.lis for any errors.

SQL>

SQL> -- Bug#25233027: xxx Set this parameter to FALSE for creating common objects in consolidated database

SQL> alter session set "_oracle_script" = FALSE;

Session altered.

Step 6:- Connect to the database as the perfstat user

SQL>conn perfstat/oracle

Step 7:- Run the PL/SQL procedure:

SQL>execute statspack.snap

Step 9:-Wait for 20 min’s before taking the second snapshot, i.e step-7 again.

SQL> conn perfstat/oracle

Connected.

SQL> execute statspack.snap

PL/SQL procedure successfully completed.

SQL>  execute statspack.snap

PL/SQL procedure successfully completed.

SQL> @$ORACLE_HOME/rdbms/admin/spreport.sql

Step 9:- Finally run the script spreport ($ORACLE_HOME\rdbms\admin\spreport.sql) to generate the report, where it will be asking for the snapshot ids (choose the appropriate ids and create the report).

SQL>@$ORACLE_HOME/rdbms/admin/spreport.sql

Listing all Completed Snapshots

                                                       Snap

Instance     DB Name        Snap Id   Snap Started    Level Comment

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

livedb       LIVEDB               1 24 Sep 2021 23:49     5

                                  2 25 Sep 2021 00:04     5

Specify the Begin and End Snapshot Ids

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

Enter value for begin_snap: 1

Begin Snapshot Id specified: 1

Enter value for end_snap: 2

Example of snap 1 & 2 differences

SQL Memory Statistics  DB/Inst: LIVEDB/livedb  Snaps: 1-2

                                   Begin            End         % Diff

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

   Avg Cursor Size (KB):           23.16          22.97           -.82

 Cursor to Parent ratio:            1.24           1.25            .73

          Total Cursors:           1,080          1,063          -1.60

          Total Parents:             872            852          -2.35

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

init.ora Parameters  DB/Inst: LIVEDB/livedb  Snaps: 1-2

***************************************************************************

TO GENERATE AWR REPORT , FOLLOW THE BELOW STEPS :

 

Step 1:- Login to the database as sys user

Step 2:-  Run the script : $ORACLE_HOME/rdbms/admin/awrrpt.sql

SQL>@$ORACLE_HOME/rdbms/admin/awrrpt.sql

Step 3:- Give the type of the report as HTML or TEXT

Specify the Report Type

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

AWR reports can be generated in the following formats.      Please enter the

name of the format at the prompt.  Default value is 'html'.

'html'               HTML format (default)

'text'                Text format

'active-html'     Includes Performance Hub active report

Enter value for report_type: text

Type Specified:  text

Step 5:- From the list of snap IDs shown, enter value of the required begin ID and end ID. This should be depending on the peak hours of the application.

Specify the Begin and End Snapshot Ids

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

Enter value for begin_snap: 100

Begin Snapshot Id specified: 100

Enter value for end_snap: 105

Specify the Report Name

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

The default report file name is awrrpt_1_100_105.txt.  To use this name,

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

Enter value for report_name:

Using the report name awrrpt_1_100_105.txt

 

***********************************************************************

 

TO GENERATE ADDM REPORT , FOLLOW THE BELOW STEPS :

 

Step 1:- Login to the database as sys user

Step 2:- Run the script

SQL>@$ORACLE_HOME/rdbms/admin/addmrpt.sql

Step 3:- From the list of snap IDs shown, enter value of the required begin ID and end ID. This should be depending on the peak hours of the application.

SQL> @$ORACLE_HOME/rdbms/admin/addmrpt.sql

Current Instance

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

   DB Id    DB Name      Inst Num Instance

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

 3038906043 LIVEDB              1 livedb

Instances in this Workload Repository schema

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

  DB Id      Inst Num   DB Name      Instance     Host

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

* 3038906043     1      LIVEDB       livedb       vignesh.loca

Using 3038906043 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 the last 3 days of Completed Snapshots

Instance     DB Name      Snap Id       Snap Started    Snap Level

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

livedb       LIVEDB             110  24 Sep 2021 22:23    1

                                111  24 Sep 2021 23:30    1

                                112  25 Sep 2021 00:30    1

                                113  25 Sep 2021 01:30    1

                                114  25 Sep 2021 02:30    1

Specify the Begin and End Snapshot Ids

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

Enter value for begin_snap: 110

Begin Snapshot Id specified: 110

Enter value for end_snap: 112

End   Snapshot Id specified: 112

Specify the Report Name

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

The default report file name is addmrpt_1_110_112.txt.  To use this name,

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

Enter value for report_name:

Using the report name addmrpt_1_110_112.txt

Running the ADDM analysis on the specified pair of snapshots ...

Generating the ADDM report for this analysis ...

          ADDM Report for Task 'TASK_163'

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

Analysis Period

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

AWR snapshot range from 110 to 112.

Time period starts at 24-SEP-21 10.23.30 PM

Time period ends at 25-SEP-21 12.30.58 AM

Analysis Target

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

Database 'LIVEDB' with DB ID 3038906043.

Database version 19.0.0.0.0.

ADDM performed an analysis of instance livedb, numbered 1 and hosted at

vignesh.localdomain.

Activity During the Analysis Period

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

Total database time was 22 seconds.

The average number of active sessions was 0.

There are no findings to report.

          Additional Information

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

Miscellaneous Information

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

There was no significant database activity to run the ADDM.

The database's maintenance windows were active during 100% of the analysis

period.

End of Report

Report written to addmrpt_1_110_112.txt

SQL>

 *************************************************************************

 

 

 

 

Fragmentation in Oracle

 

                                                      Fragmentation in Oracle

What is Fragmentation:-

                If a table is only subject to inserts, there will not be any fragmentation. Fragmentation comes with when we update/delete data in table. The space which gets freed up during non-insert DML operations is not immediately re-used (or sometimes, may not get  reuse ever at all). This leaves behind holes in table which results in table fragmentation.

              To understand it more clearly, we need to be clear on how oracle manages space for tables.

            When rows are not stored contiguously, or if rows are split onto more than one block, performance decreases because these rows require additional block accesses.

How do you find fragmentation is accrued:-

SQL> select segment_name, bytes/(1024*1024*1024) "Size GB" from dba_segments where segment_name='VIGNESH';

 

SEGMENT_NAME                                                                                                                                                                               Size GB

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

VIGNESH                                                                                                                                                                                 .0703125

SQL> select table_name,avg_row_len,round(((blocks*16/1024)),2)||'MB' "TOTAL_SIZE",

round((num_rows*avg_row_len/1024/1024),2)||'Mb' "ACTUAL_SIZE",

round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB' "FRAGMENTED_SPACE",

(round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2)/round(((blocks*16/1024)),2))*100 "percentage"

from dba_tables WHERE table_name='&TABLE_NAME';  2    3    4    5 

Enter value for table_name: VIGNESH

old   5: from all_tables WHERE table_name='&TABLE_NAME'

new   5: from all_tables WHERE table_name='VIGNESH'

View to find fragmentation:-

In Oracle schema there are tables which has huge difference in actual size (size from User_segments) and expected size from user_tables (Num_rows*avg_row_length (in bytes)). This all is due to fragmentation in the table or stats for table are not updated into dba_tables.

Check actual table size, fragmented size and percentage of fragmentation in a table.

select table_name,avg_row_len,round(((blocks*16/1024)),2)||'MB' "TOTAL_SIZE",
round((num_rows*avg_row_len/1024/1024),2)||'Mb' "ACTUAL_SIZE",
round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB' "FRAGMENTED_SPACE",
(round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2)/round(((blocks*16/1024)),2))*100 "percentage"
from all_tables WHERE table_name='&TABLE_NAME';

check  table size.

SQL> select sum(bytes)/1024/1024/1024 from user_segments where segment_name='&TABLE_NAME';

Enter value for table_name: REV

old   1: select sum(bytes)/1024/1024/1024 from user_segments where segment_name='&TABLE_NAME'

new   1: select sum(bytes)/1024/1024/1024 from user_segments where segment_name='REV'

SUM(BYTES)/1024/1024/1024

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

                          .015625

SQL> select count(*)from rev;

  COUNT(*)

----------

   1048576

Delete some rows in the table.

SQL> delete from rev where id=2;

1048576 rows deleted.

SQL> commit;

Commit complete.

After deletion the size of the table are same.

SQL> select sum(bytes)/1024/1024/1024 from user_segments where segment_name='&TABLE_NAME';

Enter value for table_name: REV

old   1: select sum(bytes)/1024/1024/1024 from user_segments where segment_name='&TABLE_NAME'

new   1: select sum(bytes)/1024/1024/1024 from user_segments where segment_name='REV'

SUM(BYTES)/1024/1024/1024

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

                          .015625

Recreate the table in the new tablespace are move the table.

SQL> alter table rev move tablespace users;

Table altered.

After table move the size can be reduced fragmentation will be cleared.

SQL> select sum(bytes)/1024/1024/1024 from user_segments where segment_name='&TABLE_NAME';

Enter value for table_name: REV

old   1: select sum(bytes)/1024/1024/1024 from user_segments where segment_name='&TABLE_NAME'

new   1: select sum(bytes)/1024/1024/1024 from user_segments where segment_name='REV'

SUM(BYTES)/1024/1024/1024

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

                   .000061035

 

***************************************************************************

INDEX IN ORACLE

 

INDEX IN ORACLE

What is an index:-

Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Simply put, an index is a pointer to data in a table. An index in a database is very similar to an index in the back of a book.

For example, if you want to reference all pages in a book that discusses a certain topic, you first refer to the index, which lists all the topics alphabetically and are then referred to one or more specific page numbers.

`           An index helps to speed up SELECT queries and WHERE clauses, but it slows down data input, with the UPDATE and the INSERT statements. Indexes can be created or dropped with no effect on the data.

Creating an index involves the CREATE INDEX statement, which allows you to name the index, to specify the table and which column or columns to index, and to indicate whether the index is in an ascending or descending order.

Indexes can also be unique, like the UNIQUE constraint, in that the index prevents duplicate entries in the column or combination of columns on which there is an index.

Why we need to use index:-

Indexes are used to quickly locate data without having to search every row in a database table every time a database table is accessed. Indexes can be created using one or more columns of a database table, providing the basis for both rapid random lookups and efficient access of ordered records.

What is the use of Index:-

·         It issused by most of Relational Database Management Systems such as MySQL, SQL Server and Oracle

·         It can improve the performance of most types of SQL queries, rather than specific types

·         Indexes are used in Oracle to provide quick access to rows in a table. Indexes provide faster access to data for operations that return a small portion of a table's rows.

·         Although Oracle allows an unlimited number of indexes on a table, the indexes only help if they are used to speed up queries.

How to find index being used:-

create table and insert records

create table india(id int,name varchar2(20));

Table created.

SQL> insert into india values (1,'tn');

1 row created.

SQL>  insert into india values (2,'kl');

1 row created.

SQL> insert into india select * from india;

SQL> select count(*)from india;

  COUNT(*)

----------

       512

 

SQL>

create index on table india

SQL> create index st_ind on india (name);

Index created.

check index usage its shows no usage on index

SQL> select index_name, table_name, used from v$object_usage;

INDEX_NAME                            TABLE_NAME                    USE

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

ST_IND                                         INDIA                                   NO

do any operation like select insert update on table

SQL> select count(*)from india where name='tn';

  COUNT(*)

----------

       256

now check index usage in object usage views its shows yes

SQL> select index_name, table_name, used from v$object_usage;

 

INDEX_NAME                            TABLE_NAME                    USE

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

ST_IND                                         INDIA                                   YES

 

SQL>

How to find index size:-

Dba segment view used to identified the index size mention the segment name/index name to get size of the index

SQL>  select sum(bytes)/1024/1024 as "Index Size (MB)" from dba_segments where segment_name='ST_IND';  

Index Size (MB)

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

               .125

 

SQL>

How to create index:-

Create index as syntax after that we give index name and mention the table and column name

SQL> create index st_ind on india (name);

Index created.

Types of index:-

 

(1)B-Tree

(2)Compressed B-Tree

(3)Bitmap

(4)Function-Based

(5)Reverse Key (RKI)

(6)Index organized table (IOT).

B – Tree Index:

B-Tree Indexes (balanced tree) are the most common type of index.

B-Tree index stored the ROWID and the index key value in a tree structure.

When creating an index, a ROOT block is created, then BRANCH blocks are created and finally LEAF blocks.

Each branch holds the range of data its leaf blocks hold, and each root holds the range of data its branches hold:

B-Tree indexes are most useful on columns that appear in the where clause (SELECT … WHERE EMPNO=1).

Syntax

 

CREATE <UNIQUE|NON UNIQUE>  INDEX <index_name>

ON <table_name> (<column_name>,<column_name>…)

TABLESPACE <tablespace_name>;

Example

Create index   vbt.btree_idx   on  vbt.btree(  name) Tablespace users;

Compressed B-Tree:

Compressed B-Tree Indexes are built on large tables, in a data warehouse environment. In this type of index, duplicate occurrences of the same value are eliminated, thus reducing the amount of storage space, the index requires.

In a compressed B-Tree index, for each key value, a list of ROWIDs are kept

Specifying the COMPRESS keyword when creating an index (CREATE INDEX … COMPRESS) will create a compressed B-Tree index.

A regular B-Tree index can be rebuilt using the COMPRESS keyword to compress it.

Syntax:

CREATE <UNIQUE|NON UNIQUE>  INDEX <index_name>

ON <table_name> (<column_name>,<column_name>…)

PCTFREE <integer>

TABLESPACE <tablespace_name>

Compress  <column number>

Example :

SQL> create index vbt.cbtree_idx  on vbt.cbtree (id) tablespace users compress 1;

Index created.

Bitmap Indexes:

Bitmap Indexes are most appropriate on low distinct cardinality data (as opposed to B-Tree indexes).

This type of index, creates a binary map of all index values, and store that map in the index blocks, this means that the index will require less space than B-Tree index.

Each bit in the bitmap corresponds to a possible rowid. If the bit is set, then it means that the row with the corresponding rowid contains the key value. A mapping function converts the bit position to an actual rowid, so the bitmap index provides the same functionality as a regular index even though it uses a different representation internally. If the number of different key values is small, then bitmap indexes are very space efficient

When there are bitmap indexes on tables then updates will take out full table locks.So, Bitmap index is useful on large columns with low-DML(infrequent updates) activity or read only tables. This is reason you often find bitmap indexes are extensively used in the data warehouse environment(DWH).

Bitmap Index structure contain a map of bits which indicate the value in the column, for example, for the name column, the index block will hold the starting id, the ending id and the bit map:

Bitmap indexes are very useful when created on columns with low cardinality, used with the AND & OR operator in the query condition:

Syntax:

CREATE BITMAP  INDEX <index_name>

ON <table_name> (<column_name>,<column_name>…)

PCTFREE <integer>

TABLESPACE <tablespace_name>

Example :

SQL> CREATE BITMAP INDEX vbt.bmap_idx ON vbt.bitmap(name);

Index created.

SQL> SELECT COUNT(*) FROM vbt.bitmap WHERE NAME='node';

  COUNT(*)

----------

             1

 

Function Based Indexes:

Function-Based Indexes are indexes created on columns that a function is usually applied on.

When using a function on an indexed column, the index is ignored, therefore a function-based index is very useful for these operation

SQL> CREATE INDEX vbt.fun_idx on vbt.fun(UPPER(name));

Index created.

SQL> SELECT * FROM vbt.fun WHERE UPPER(name) like 'node';

no rows selected

SQL> SELECT * FROM vbt.fun WHERE UPPER(name) like 'NODE';

 

            ID NAME

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

             2 node

 

Reverse-Key Indexes:

They  are special types of B-Tree indexes and are very useful when created on columns contain sequential numbers.

When using a regular B-Tree, the index will grow to have many branches and perhaps several levels, thus causing performance degradation, the RKI solve the problem by reversing the bytes of each column key and indexing the new data.

This method distributes the data evenly in the index. Creating a RKI is done using the REVERSE keyword:

CREATE INDEX … ON … REVERSE;

Example :

SQL> create index vbt.rev_idx on vbt.rev(id,name) reverse;

Index created.

SQL> select * from vbt.rev;

 

            ID NAME

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

             1 parent

             2 node