Author: kishan

You will know about me.

ORA-15032: not all alterations performed – ORA-15177: cannot operate on system aliases (DBD ERROR: OCIStmtExecute)

Cause:

Sometimes when you delete files under ASMCMD, files not get delete

ASMCMD> rm *
ORA-15032: not all alterations performed
ORA-15177: cannot operate on system aliases (DBD ERROR: OCIStmtExecute)

Workaround:

Use -rf with rm forces deletion

ASMCMD> ls
2021_05_11/
ASMCMD> rm -rf *

ASMCMD> ASMCMD>

REGEX SQL

As you know regex is a powerful string manipulation function used to filter the strings,numbers,characters etc very effectively. Below are the useful functions to pull desired filter

REGEXP_SUBSTR

REGEXP_SUBSTR(source_string,pattern [,position[,occurence[,return_option[,match_parameter]]]]

kish@exdbx<>select regexp_substr('MG-KISHAN-SKJFLS-JDS-ADLS','-[^-]+-') as WHOAMI from dual;

WHOAMI
--------
-KISHAN-

[[:digit:]] is a parameter used in regex_substr to filter only digits from string

kish@exdbx<>select regexp_substr('i was born on 1990','[[:digit:]]+') from dual;

REGE
----
1990

REGEXP_LIKE is a function which can be used in where clause of the select query. Below is an example.

kish@exdbx<>@colfrmt



Procedure created.

Elapsed: 00:00:00.88

Procedure created.

Elapsed: 00:00:00.10

kish@exdbx<>select country,order_priority,order_date from sales_tab where regexp_like(ORDER_DATE,'MAR-26+');

......
"Marshall Islan C               01-MAR-26
ds

"Niger          H               01-MAR-26
"Montenegro     H               01-MAR-26
"Norway         M               01-MAR-26
"Burundi        L               01-MAR-26
"Belarus        H               01-MAR-26
"Sri Lanka      H               01-MAR-26
"The Gambia     H               01-MAR-26
"East Timor     H               01-MAR-26
"Vanuatu        C               01-MAR-26
"Senegal        H               01-MAR-26
"Belgium        C               01-MAR-26

880 rows selected.

[[:punct:]] is a parameter used in regexp_substr to filter all the characters like punctuation marks in the select query

kish@exdbx<>select regexp_substr('Be persistent and perseverant : Be consistent :369','[[:punct:]]') from dual;

R
-
:

[[:punct:]] parameter usage in regexp_like function

kish@exdbx<>select country from sales_tab where regexp_like(country,'[[:punct:]]')
;

COUNTRY
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
"Grenada
"Turkey
"Laos
"Guinea
"Tonga
"Cote d'Ivoire
"Uganda
"Mali
"Nicaragua
..............

REGEXP_INSTR

Syntax:

REGEXP_INSTR(source_string,pattern [,position[,occurence[,return_option[,match_parameter]]]]

To find the position of a number and string

kish@exdbx<>select regexp_instr('Second world war started from 1939 till 1945','[[:digit:]]') as POSITION from dual;

                                POSITION
----------------------------------------
                                      31

Elapsed: 00:00:00.01

To find the next position of the number

1939 is the number and 9 is the second position of the first number

select regexp_instr('Second world war started from 1939 till 1945','[[:digit:]]',1,2,1) as POSITION from dual;

                                POSITION
----------------------------------------
                                      33

To find the next position of the next number

1945 is the number and 1 is the next position of the number

kish@exdbx<>select regexp_instr('Second world war started from 1939 till 1945','[[:digit:]]',1,5,1) as POSITION from dual;

                                POSITION
----------------------------------------
                                      42

SHELL SCRIPT TO MONITOR LOAD AVERAGE IN LINUX

Its too critical to monitor the load in linux system where multiple process spawn to bring down the performance and in the worst case, collapse the server

A custom shell script to monitor the load for every 30 seconds

Script:

One liner for load average monitor per 30 seconds

We get two output per every minute.So in a day there are 24 hours (1440 min) which gives us 1440 * 2 = 2880 outputs

So for 7 days we get 10080 min * 2 = 20160 outputs

while true;do date;top -b -n 1 |awk '/load average/ {printf "5m -> %s 10m -> %s 15m -> %s\n",$10,$11,$12}';sleep 30;done

If you want to delete the old lines on the report,then you can use this script

#!/bin/bash


#load average per 30 seconds 

while true
  do
   date
   top -b -n 1 |awk '/load average/ {printf "5m - %s 10m - %s 15m - %s\n",$10,$11,$12}'
  sleep 30
done > ldavg


#delete old lines greater than 7 days

if [ `grep 'IST' ldavg |wc -l` -gt 20160 ]
  then
    sed -i '1,100d' ldavg
  else
    break
fi

Output:

[oracle@exdbadm01 ~]$ while true;do date;top -b -n 1 |awk '/load average/ {printf "5m -> %s 10m -> %s 15m -> %s\n",$10,$11,$12}';sleep 30;done



Sun May  9 13:55:25 IST 2021
5m -> 0.03, 10m -> 0.04, 15m -> 0.16
Sun May  9 13:55:56 IST 2021
5m -> 0.02, 10m -> 0.04, 15m -> 0.15
Sun May  9 13:56:26 IST 2021
5m -> 0.01, 10m -> 0.03, 15m -> 0.14
Sun May  9 13:56:57 IST 2021

NOTE: This script is an alternative for crontab schedule which runs in a loop. Run the script using nohup mode in background to initiate the script

[oracle@exdbadm01 ~]$ nohup sh ldavg.sh &
[1] 11723
[oracle@exdbadm01 ~]$ nohup: appending output to `nohup.out'

Monitor the output using tail

[oracle@exdbadm01 ~]$ tail -f ldavg
Tue May 11 13:44:59 IST 2021
5m - 0.05, 10m - 0.07, 15m - 0.04
Tue May 11 13:45:30 IST 2021
5m - 0.03, 10m - 0.06, 15m - 0.04
Tue May 11 13:46:01 IST 2021
5m - 0.02, 10m - 0.06, 15m - 0.04
Tue May 11 13:46:31 IST 2021
5m - 0.01, 10m - 0.05, 15m - 0.03
Tue May 11 13:47:02 IST 2021
5m - 0.00, 10m - 0.04, 15m - 0.03

IO HOT SPOT EXADATA STORAGE LAYOUT

Default allocation policy:

In exadata celldisks ,outer region is faster and inner region is slower. So oracle recommends to create griddisks which require high IOPS on the edge of the disk platter and other diskgroups which are non critical just for storing historical archive data or non critical data which are touched not too often from disk.

By default,oracle creates the griddisks based on FIFO (First in first out). If you create griddisk for DATA diskgroup allocation first , then DATA diskgroup lie on the fastest disk spindle.

To get maximum performance out of exadata, the objects which are frequently touched from the disk like tables,indexes,redologs etc should be kept in the high priority disks

Low priority disks can be used for FRA purpose,backups and reporting objects etc

DEFAULT POLICY
INTERLEAVE POLICY

There are two strategy for disk provisioning and layout

Based on the objective of application end goal and objective, these two layouts can be chosen

  • Default policy
  • Interleave policy

Unless you have a reason to choose Interleave policy, dont choose it for regular performance. As every applications objective would be faster response time and from database perception, tables,indexes and redologs should have faster IO response rather than archives,backup and other obsoleted data.

Default policy gives faster access and priority to only one diskgroup which is created on top.

Interleave policy gives faster priority to atleast two diskgroups like DATA,RECO which is somewhat balanced speed for data retrieve and write

Check the diskgroup,celldisk and griddisk

SQL> select dg.name as DISKGROUP,regexp_substr(d.name,'_\S+') as CELLDISK,d.name as GRIDDISK from v$asm_diskgroup dg,v$asm_disk d where dg.group_number = d.group_number and dg.name = 'DATA' order by 1,2;

DISKGROUP            CELLDISK             GRIDDISK
-------------------- -------------------- --------------------
DATA                 _CD_CELL1_STOCELL    DATA_CD_CELL1_STOCEL
                                          L

DATA                 _CD_CELL2_STOCELL    DATA_CD_CELL2_STOCEL
                                          L

DATA                 _CD_CELL3_STOCELL    DATA_CD_CELL3_STOCEL
                                          L

DATA                 _CD_CELL4_STOCELL    DATA_CD_CELL4_STOCEL
                                          L

DISKGROUP            CELLDISK             GRIDDISK
-------------------- -------------------- --------------------

DATA                 _CD_CELL5_STOCELL    DATA_CD_CELL5_STOCEL
                                          L

DATA                 _CD_CELL6_STOCELL    DATA_CD_CELL6_STOCEL

                                          L



6 rows selected.

You have all the cell stats in your hands to diagnose or analyse the performance which can be used to detect the IOPS from disk

These are the important metrics which should be noted to detect IO’s per read and write

If you observe all IO read metrics for DATA diskgroup, which is located at the faster corner track of the disk has huge IO requests with turbo spin 🙂

Number of requests to write small blocks to celldisk

CellCLI> list metriccurrent attributes name,alertState,collectionTime,metricObjectName,metricValue where name='CD_IO_RQ_W_SM';
         CD_IO_RQ_W_SM   normal  2021-04-29T20:28:07+05:30       CD_cell01_stocell       11,462 IO requests
         CD_IO_RQ_W_SM   normal  2021-04-29T20:28:07+05:30       CD_cell02_stocell       3,996 IO requests
         CD_IO_RQ_W_SM   normal  2021-04-29T20:28:07+05:30       CD_cell03_stocell       20,298 IO requests
         CD_IO_RQ_W_SM   normal  2021-04-29T20:28:07+05:30       CD_cell04_stocell       3,864 IO requests
         CD_IO_RQ_W_SM   normal  2021-04-29T20:28:07+05:30       CD_cell05_stocell       3,840 IO requests
         CD_IO_RQ_W_SM   normal  2021-04-29T20:28:07+05:30       CD_cell06_stocell       3,484 IO requests
         CD_IO_RQ_W_SM   normal  2021-04-29T20:28:07+05:30       CD_cell07_stocell       1,105 IO requests
         CD_IO_RQ_W_SM   normal  2021-04-29T20:28:07+05:30       CD_cell08_stocell       1,751 IO requests
         CD_IO_RQ_W_SM   normal  2021-04-29T20:28:07+05:30       CD_cell09_stocell       1,147 IO requests
         CD_IO_RQ_W_SM   normal  2021-04-29T20:28:07+05:30       CD_cell10_stocell       10,688 IO requests
         CD_IO_RQ_W_SM   normal  2021-04-29T20:28:07+05:30       CD_cell11_stocell       18,439 IO requests
         CD_IO_RQ_W_SM   normal  2021-04-29T20:28:07+05:30       CD_cell12_stocell       61,334 IO requests
         CD_IO_RQ_W_SM   normal  2021-04-29T20:28:07+05:30       FD_00_stocell           3,834 IO requests
         CD_IO_RQ_W_SM   normal  2021-04-29T20:28:07+05:30       FD_01_stocell           3,639 IO requests
         CD_IO_RQ_W_SM   normal  2021-04-29T20:28:07+05:30       FD_02_stocell           11,625 IO requests
         CD_IO_RQ_W_SM   normal  2021-04-29T20:28:07+05:30       FD_03_stocell           3,473 IO requests
         CD_IO_RQ_W_SM   normal  2021-04-29T20:28:07+05:30       FD_04_stocell           11,769 IO requests
         CD_IO_RQ_W_SM   normal  2021-04-29T20:28:07+05:30       FD_05_stocell           11,545 IO requests
         CD_IO_RQ_W_SM   normal  2021-04-29T20:28:07+05:30       FD_06_stocell           3,541 IO requests
         CD_IO_RQ_W_SM   normal  2021-04-29T20:28:07+05:30       FD_07_stocell           4,152 IO requests
         CD_IO_RQ_W_SM   normal  2021-04-29T20:28:07+05:30       FD_08_stocell           11,994 IO requests

Number of MB read in large blocks from celldisk

CellCLI>  list metriccurrent attributes name,alertState,collectionTime,metricObjectName,metricType,metricValue,objectType where name='GD_IO_BY_R_LG';
         GD_IO_BY_R_LG   normal  2021-04-29T23:01:09+05:30       DATA_CD_cell1_stocell   Cumulative      124 MB       GRIDDISK
         GD_IO_BY_R_LG   normal  2021-04-29T23:01:09+05:30       DATA_CD_cell2_stocell   Cumulative      133 MB       GRIDDISK
         GD_IO_BY_R_LG   normal  2021-04-29T23:01:09+05:30       DATA_CD_cell3_stocell   Cumulative      125 MB       GRIDDISK
         GD_IO_BY_R_LG   normal  2021-04-29T23:01:09+05:30       DATA_CD_cell4_stocell   Cumulative      153 MB       GRIDDISK
         GD_IO_BY_R_LG   normal  2021-04-29T23:01:09+05:30       DATA_CD_cell5_stocell   Cumulative      118 MB       GRIDDISK
         GD_IO_BY_R_LG   normal  2021-04-29T23:01:09+05:30       DATA_CD_cell6_stocell   Cumulative      143 MB       GRIDDISK
         GD_IO_BY_R_LG   normal  2021-04-29T23:01:09+05:30       FRA_CD_cell08_stocell   Cumulative      0.000 MB     GRIDDISK
         GD_IO_BY_R_LG   normal  2021-04-29T23:01:09+05:30       FRA_CD_cell09_stocell   Cumulative      0.000 MB     GRIDDISK
         GD_IO_BY_R_LG   normal  2021-04-29T23:01:09+05:30       FRA_CD_cell7_stocell    Cumulative      0.000 MB     GRIDDISK
         GD_IO_BY_R_LG   normal  2021-04-29T23:01:09+05:30       FRA_CD_cell8_stocell    Cumulative      0.609 MB     GRIDDISK
         GD_IO_BY_R_LG   normal  2021-04-29T23:01:09+05:30       FRA_CD_cell9_stocell    Cumulative      0.000 MB     GRIDDISK
         GD_IO_BY_R_LG   normal  2021-04-29T23:01:09+05:30       MGMT_CD_cell09_stocell  Cumulative      71.664 MB    GRIDDISK

Number of MB to read small blocks to griddisk

CellCLI> list metriccurrent attributes name,alertState,collectionTime,metricObjectName,metricType,metricValue,objectType where name='GD_IO_BY_R_SM';
         GD_IO_BY_R_SM   normal  2021-04-29T23:04:10+05:30       DATA_CD_cell1_stocell   Cumulative      111 MB       GRIDDISK
         GD_IO_BY_R_SM   normal  2021-04-29T23:04:10+05:30       DATA_CD_cell2_stocell   Cumulative      93.603 MB    GRIDDISK
         GD_IO_BY_R_SM   normal  2021-04-29T23:04:10+05:30       DATA_CD_cell3_stocell   Cumulative      79.551 MB    GRIDDISK
         GD_IO_BY_R_SM   normal  2021-04-29T23:04:10+05:30       DATA_CD_cell4_stocell   Cumulative      95.796 MB    GRIDDISK
         GD_IO_BY_R_SM   normal  2021-04-29T23:04:10+05:30       DATA_CD_cell5_stocell   Cumulative      151 MB       GRIDDISK
         GD_IO_BY_R_SM   normal  2021-04-29T23:04:10+05:30       DATA_CD_cell6_stocell   Cumulative      116 MB       GRIDDISK
         GD_IO_BY_R_SM   normal  2021-04-29T23:04:10+05:30       FRA_CD_cell08_stocell   Cumulative      0.378 MB     GRIDDISK
         GD_IO_BY_R_SM   normal  2021-04-29T23:04:10+05:30       FRA_CD_cell09_stocell   Cumulative      0.499 MB     GRIDDISK
         GD_IO_BY_R_SM   normal  2021-04-29T23:04:10+05:30       FRA_CD_cell7_stocell    Cumulative      0.857 MB     GRIDDISK
         GD_IO_BY_R_SM   normal  2021-04-29T23:04:10+05:30       FRA_CD_cell8_stocell    Cumulative      0.817 MB     GRIDDISK
         GD_IO_BY_R_SM   normal  2021-04-29T23:04:10+05:30       FRA_CD_cell9_stocell    Cumulative      0.999 MB     GRIDDISK
         GD_IO_BY_R_SM   normal  2021-04-29T23:04:10+05:30       MGMT_CD_cell09_stocell  Cumulative      91.108 MB    GRIDDISK

Number of requests to write large blocks to griddisk

CellCLI> list metriccurrent attributes name,alertState,collectionTime,metricObjectName,metricValue,objectType where name='GD_IO_BY_W_LG';
         GD_IO_BY_W_LG   normal  2021-04-29T23:08:11+05:30       DATA_CD_cell1_stocell   58.683 MB       GRIDDISK
         GD_IO_BY_W_LG   normal  2021-04-29T23:08:11+05:30       DATA_CD_cell2_stocell   56.470 MB       GRIDDISK
         GD_IO_BY_W_LG   normal  2021-04-29T23:08:11+05:30       DATA_CD_cell3_stocell   58.226 MB       GRIDDISK
         GD_IO_BY_W_LG   normal  2021-04-29T23:08:11+05:30       DATA_CD_cell4_stocell   55.497 MB       GRIDDISK
         GD_IO_BY_W_LG   normal  2021-04-29T23:08:11+05:30       DATA_CD_cell5_stocell   56.906 MB       GRIDDISK
         GD_IO_BY_W_LG   normal  2021-04-29T23:08:11+05:30       DATA_CD_cell6_stocell   71.672 MB       GRIDDISK
         GD_IO_BY_W_LG   normal  2021-04-29T23:08:11+05:30       FRA_CD_cell08_stocell   82.925 MB       GRIDDISK
         GD_IO_BY_W_LG   normal  2021-04-29T23:08:11+05:30       FRA_CD_cell09_stocell   89.471 MB       GRIDDISK
         GD_IO_BY_W_LG   normal  2021-04-29T23:08:11+05:30       FRA_CD_cell7_stocell    89.895 MB       GRIDDISK
         GD_IO_BY_W_LG   normal  2021-04-29T23:08:11+05:30       FRA_CD_cell8_stocell    95.932 MB       GRIDDISK
         GD_IO_BY_W_LG   normal  2021-04-29T23:08:11+05:30       FRA_CD_cell9_stocell    89.391 MB       GRIDDISK
         GD_IO_BY_W_LG   normal  2021-04-29T23:08:11+05:30       MGMT_CD_cell09_stocell  0.000 MB        GRIDDISK

Cumulative number of large read requests to Grid Disks:

CellCLI> list metriccurrent attributes name,alertState,collectionTime,metricObjectName,metricValue where name='GD_IO_RQ_R_LG';
         GD_IO_RQ_R_LG   normal  2021-04-29T23:09:11+05:30       DATA_CD_cell1_stocell   148 IO requests
         GD_IO_RQ_R_LG   normal  2021-04-29T23:09:11+05:30       DATA_CD_cell2_stocell   152 IO requests
         GD_IO_RQ_R_LG   normal  2021-04-29T23:09:11+05:30       DATA_CD_cell3_stocell   148 IO requests
         GD_IO_RQ_R_LG   normal  2021-04-29T23:09:11+05:30       DATA_CD_cell4_stocell   169 IO requests
         GD_IO_RQ_R_LG   normal  2021-04-29T23:09:11+05:30       DATA_CD_cell5_stocell   151 IO requests
         GD_IO_RQ_R_LG   normal  2021-04-29T23:09:11+05:30       DATA_CD_cell6_stocell   160 IO requests
         GD_IO_RQ_R_LG   normal  2021-04-29T23:09:11+05:30       FRA_CD_cell08_stocell   0 IO requests
         GD_IO_RQ_R_LG   normal  2021-04-29T23:09:11+05:30       FRA_CD_cell09_stocell   0 IO requests
         GD_IO_RQ_R_LG   normal  2021-04-29T23:09:11+05:30       FRA_CD_cell7_stocell    0 IO requests
         GD_IO_RQ_R_LG   normal  2021-04-29T23:09:11+05:30       FRA_CD_cell8_stocell    1 IO requests
         GD_IO_RQ_R_LG   normal  2021-04-29T23:09:11+05:30       FRA_CD_cell9_stocell    0 IO requests
         GD_IO_RQ_R_LG   normal  2021-04-29T23:09:11+05:30       MGMT_CD_cell09_stocell  211 IO requests

Number of requests to read small blocks to griddisk

CellCLI> list metriccurrent attributes name,alertState,collectionTime,metricObjectName,metricValue where name='GD_IO_RQ_R_SM'; GD_IO_RQ_R_SM   normal  2021-04-29T23:10:11+05:30       DATA_CD_cell1_stocell   1,859 IO requests
         GD_IO_RQ_R_SM   normal  2021-04-29T23:10:11+05:30       DATA_CD_cell2_stocell   1,616 IO requests
         GD_IO_RQ_R_SM   normal  2021-04-29T23:10:11+05:30       DATA_CD_cell3_stocell   1,284 IO requests
         GD_IO_RQ_R_SM   normal  2021-04-29T23:10:11+05:30       DATA_CD_cell4_stocell   1,537 IO requests
         GD_IO_RQ_R_SM   normal  2021-04-29T23:10:11+05:30       DATA_CD_cell5_stocell   2,448 IO requests
         GD_IO_RQ_R_SM   normal  2021-04-29T23:10:11+05:30       DATA_CD_cell6_stocell   1,863 IO requests
         GD_IO_RQ_R_SM   normal  2021-04-29T23:10:11+05:30       FRA_CD_cell08_stocell   90 IO requests
         GD_IO_RQ_R_SM   normal  2021-04-29T23:10:11+05:30       FRA_CD_cell09_stocell   74 IO requests
         GD_IO_RQ_R_SM   normal  2021-04-29T23:10:11+05:30       FRA_CD_cell7_stocell    64 IO requests
         GD_IO_RQ_R_SM   normal  2021-04-29T23:10:11+05:30       FRA_CD_cell8_stocell    46 IO requests
         GD_IO_RQ_R_SM   normal  2021-04-29T23:10:11+05:30       FRA_CD_cell9_stocell    75 IO requests
         GD_IO_RQ_R_SM   normal  2021-04-29T23:10:11+05:30       MGMT_CD_cell09_stocell  148,062 IO requests

Number of requests to write small blocks to griddisk

CellCLI> list metriccurrent attributes name,alertState,collectionTime,metricObjectName,metricValue where name='GD_IO_RQ_W_SM';
         GD_IO_RQ_W_SM   normal  2021-04-29T23:13:12+05:30       DATA_CD_cell1_stocell   15,844 IO requests
         GD_IO_RQ_W_SM   normal  2021-04-29T23:13:12+05:30       DATA_CD_cell2_stocell   5,965 IO requests
         GD_IO_RQ_W_SM   normal  2021-04-29T23:13:12+05:30       DATA_CD_cell3_stocell   28,588 IO requests
         GD_IO_RQ_W_SM   normal  2021-04-29T23:13:12+05:30       DATA_CD_cell4_stocell   5,069 IO requests
         GD_IO_RQ_W_SM   normal  2021-04-29T23:13:12+05:30       DATA_CD_cell5_stocell   5,103 IO requests
         GD_IO_RQ_W_SM   normal  2021-04-29T23:13:12+05:30       DATA_CD_cell6_stocell   4,722 IO requests
         GD_IO_RQ_W_SM   normal  2021-04-29T23:13:12+05:30       FRA_CD_cell08_stocell   14,115 IO requests
         GD_IO_RQ_W_SM   normal  2021-04-29T23:13:12+05:30       FRA_CD_cell09_stocell   25,661 IO requests
         GD_IO_RQ_W_SM   normal  2021-04-29T23:13:12+05:30       FRA_CD_cell7_stocell    1,859 IO requests
         GD_IO_RQ_W_SM   normal  2021-04-29T23:13:12+05:30       FRA_CD_cell8_stocell    2,506 IO requests
         GD_IO_RQ_W_SM   normal  2021-04-29T23:13:12+05:30       FRA_CD_cell9_stocell    1,523 IO requests
         GD_IO_RQ_W_SM   normal  2021-04-29T23:13:12+05:30       MGMT_CD_cell09_stocell  83,533 IO requests

Number of requests to write large blocks to griddisk

CellCLI> list metriccurrent attributes name,alertState,collectionTime,metricObjectName,metricValue where name='GD_IO_RQ_W_LG';
         GD_IO_RQ_W_LG   normal  2021-04-29T23:13:12+05:30       DATA_CD_cell1_stocell   84 IO requests
         GD_IO_RQ_W_LG   normal  2021-04-29T23:13:12+05:30       DATA_CD_cell2_stocell   97 IO requests
         GD_IO_RQ_W_LG   normal  2021-04-29T23:13:12+05:30       DATA_CD_cell3_stocell   85 IO requests
         GD_IO_RQ_W_LG   normal  2021-04-29T23:13:12+05:30       DATA_CD_cell4_stocell   88 IO requests
         GD_IO_RQ_W_LG   normal  2021-04-29T23:13:12+05:30       DATA_CD_cell5_stocell   92 IO requests
         GD_IO_RQ_W_LG   normal  2021-04-29T23:13:12+05:30       DATA_CD_cell6_stocell   107 IO requests
         GD_IO_RQ_W_LG   normal  2021-04-29T23:13:12+05:30       FRA_CD_cell08_stocell   113 IO requests
         GD_IO_RQ_W_LG   normal  2021-04-29T23:13:12+05:30       FRA_CD_cell09_stocell   129 IO requests
         GD_IO_RQ_W_LG   normal  2021-04-29T23:13:12+05:30       FRA_CD_cell7_stocell    123 IO requests
         GD_IO_RQ_W_LG   normal  2021-04-29T23:13:12+05:30       FRA_CD_cell8_stocell    126 IO requests
         GD_IO_RQ_W_LG   normal  2021-04-29T23:13:12+05:30       FRA_CD_cell9_stocell    120 IO requests
         GD_IO_RQ_W_LG   normal  2021-04-29T23:13:12+05:30       MGMT_CD_cell09_stocell  0 IO requests

HYBRID COLUMNAR COMPRESSION (EXADATA)

Standard oracle block format and row format:

Rows are stored in no specific order,but columns are generally stored in the order in which they were defined in table.

For each row in a block there will be a row header and column data for each column.

These rows are stored in standard oracle block format called row piece, rows data may be stored in more than one chunk.A pointer will be pointed towards the next row piece

Compression TypesDescriptionExpected Compression
Query lowLZO Algorithm,consume least CPU,Warehouse low4x
Query highZLIB(gzip),Warehouse high6x
Archive lowZLIB(gzip),Query high7x
Archive highBZIP2,Archive low12x
Types of HCC compression types

CompressionTypemin_target_sizemax_target_sizealignment_target_size
QueryLow3200032000128000
QueryHigh3200064000128000
ArchiveLow32000256000128000
ArchiveHigh256000256000128000
Compression unit sizes for each algorithm

ARCHIVE HIGH:

Check the segment size

kish@exdbx<>select bytes/1073741824,segment_name from user_segments where segmen                                      t_name='SALES';

BYTES/1073741824 SEGMENT_NAME
---------------- ---------------------------------------------------------------                                      
      .092041016 SALES

Compress the table using archive high clause

kish@exdbx<>alter table sales compress for archive high;

Table altered.

Compress the existing data in table using archive high

kish@exdbx<>alter table sales move compress for query low parallel 2;

Table altered.

Elapsed: 00:00:11.85

Check the reduced size now

kish@exdbx<>select bytes/1073741824,segment_name from user_segments where segmen                                    t_name='SALES';

BYTES/1073741824 SEGMENT_NAME
---------------- ---------------------------------------------------------------                                      
      .037597656 SALES

QUERY HIGH:

kish@exdbx<>select bytes/1073741824,segment_name from user_segments where segment_name='SALES3';

BYTES/1073741824 SEGMENT_NAME
---------------- --------------------------------------------------------------------------
          .09375 SALES3

Compress the table using query high clause

kish@exdbx<>alter session set tracefile_identifier='HCC_TEST';

Session altered.

kish@exdbx<>alter session set events '10046 trace name context forever,level 12';

Session altered.

Compress the existing data in table using query high clause

kish@exdbx<>alter table sales3 move compress for query high parallel 4;

Table altered.

Elapsed: 00:00:13.43

Check the final size

kish@exdbx<>select bytes/1073741824,segment_name from user_segments where segment_name='SALES3';

BYTES/1073741824 SEGMENT_NAME
---------------- ------------------------------------------------------------------------
      .018432617 SALES3

ARCHIVE LOW

kish@exdbx<>alter table sales2 compress for archive low;

Table altered.

Elapsed: 00:00:00.03
kish@exdbx<>alter table sales2 move compress for archive low parallel 2;

Table altered.

Elapsed: 00:00:14.89

Check the presize of table

kish@exdbx<>select bytes/1073741824,segment_name from user_segments where segment_name='SALES2';

BYTES/1073741824 SEGMENT_NAME
---------------- ---------------------------------------------------------------------------------
      .081420898 SALES2

Elapsed: 00:00:00.01

Now we can identify the tables and their compression type.As you can see the different tables compressed with different types of compression.

kish@exdbx<>select owner,table_name,compress_for from dba_tables where compression='ENABLED';

OWNER                          TABLE_NAME                     COMPRESS_FOR
------------------------------ ------------------------------ ------------
SYS                            SALES                          ARCHIVE HIGH
TEST                           SALES2                         ARCHIVE HIGH
SYS                            T                              ARCHIVE HIGH
SYS                            T1                             ARCHIVE LOW
SYS                            T2                             QUERY HIGH
SYS                            T3                             QUERY LOW
SYS                            T11                            QUERY HIGH
SYS                            T12                            QUERY LOW
SYS                            T13                            ARCHIVE HIGH
SYS                            T14                            ARCHIVE LOW
SYS                            T33                            ARCHIVE HIGH
SYS                            T31                            ARCHIVE HIGH
SYS                            T30                            ARCHIVE HIGH
SYSMAN                         MGMT_POLICIES                  OLTP
SYSMAN                         MGMT_METRICS                   OLTP
TEST                           SALES3                         ARCHIVE HIGH
SYS                            SALES2                         ARCHIVE LOW
TEST                           SALES                          ARCHIVE HIGH

lets test with sys.sales table which is currently having archive high compression

There are two ways to compress the data:

  • Compress existing data
  • Compress future incoming data

To compress existing data,


kish@exdbx<>select bytes/1024 from dba_segments where segment_name='SALES' and owner='SYS';

BYTES/1024
----------
     98304

98MB of data should be compressed

kish@exdbx<>alter table sys.sales move compress for query high;

Table altered.

Elapsed: 00:00:21.51

It took 21 seconds for the data to get compressed for 98MB and 8MB of storage is saved with query high for instance

kish@exdbx<>select bytes/1024 from dba_segments where segment_name='SALES' and owner='SYS';

BYTES/1024
----------
     90112

Elapsed: 00:00:00.24

To compress future incoming data not the existing data,we literally issue >alter table .. compress for < syntax

kish@exdbx<>select bytes/1024 from dba_segments where segment_name='SALES' and owner='SYS';

BYTES/1024
----------
     98304

Elapsed: 00:00:00.09
kish@exdbx<>alter table sys.SALES compress for query high;

Table altered.

Elapsed: 00:00:00.40
kish@exdbx<>select bytes/1024 from dba_segments where segment_name='SALES' and owner='SYS';

BYTES/1024
----------
     98304

Elapsed: 00:00:00.10
kish@exdbx<>select owner,table_name,compress_for from dba_tables where compression='ENABLED' and table_name='SALES' and owner='SYS';

OWNER                          TABLE_NAME                     COMPRESS_FOR
------------------------------ ------------------------------ ------------
SYS                            SALES                          QUERY HIGH

Elapsed: 00:00:00.25
kish@exdbx<>

In the above output,the compression factor has been changed from archive high to query high but the size of data remains same without any compressed size of segment

create a table with three partitions P1(uncompressed),P2(query high) and P3(archive low) compression

CREATE TABLE "TEST"."TABX"
( "TRANS_ID" VARCHAR2(30),
"UNIT_ID" NUMBER(30,0),
"DAY" NUMBER(30,0), 
"TRANS_SEQ" VARCHAR2(30),
"END_DATE" DATE,
"BEGIN_DATE" DATE,
"UNIT_TYPE" VARCHAR2(30),
"CUST_TYPE" VARCHAR2(1),
"LOAD_DATE" DATE,
"CURRENCY_TYPE" CHAR(1)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOLOGGING
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
PARTITION BY RANGE ("BEGIN_DATE")
(PARTITION "P1" VALUES LESS THAN (TO_DATE
(' 2008-09-06 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ,
PARTITION "P2" VALUES LESS THAN (TO_DATE
(' 2008-09-07 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
SEGMENT CREATION IMMEDIATE
PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS FOR QUERY HIGH NOLOGGING
STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ,
PARTITION "P3" VALUES LESS THAN (TO_DATE
(' 2008-09-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
SEGMENT CREATION IMMEDIATE
PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS FOR ARCHIVE LOW NOLOGGING
STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ) ;

Below is a compression advisor script by oracle provided by which can used to get compression ratio of objects with HCC

set serveroutput on
declare

    m_scratchtbsname    varchar2(32) := 'USERS';
    m_ownname       varchar2(32) := 'TEST';
    m_tabname       varchar2(32) := 'TABX';
	m_partname      varchar2(32) := 'P1';
    m_comptype      number(10,0) := dbms_compression.comp_for_archive_high;
--    m_comptype      number(10,0) := dbms_compression.comp_for_archive_low;
--    m_comptype      number(10,0) := dbms_compression.comp_for_query_high;
--    m_comptype      number(10,0) := dbms_compression.comp_for_query_low;
    m_blkcnt_cmp        pls_integer;
    m_blkcnt_uncmp      pls_integer;
    m_row_cmp       pls_integer;
    m_row_uncmp         pls_integer;
    m_cmp_ratio         number;
    m_comptype_str      varchar2(4000);

begin

    DBMS_COMPRESSION.GET_COMPRESSION_RATIO (
        scratchtbsname  => m_scratchtbsname,
        ownname     => m_ownname,
        tabname     => m_tabname,
		partname    => m_partname,
        comptype    => m_comptype,
        blkcnt_cmp  => m_blkcnt_cmp,
        blkcnt_uncmp    => m_blkcnt_uncmp,
        row_cmp     => m_row_cmp,
        row_uncmp   => m_row_uncmp,
        cmp_ratio   => m_cmp_ratio,
        comptype_str    => m_comptype_str
    );

    dbms_output.put_line('Blocks compressed:           ' || m_blkcnt_cmp);
    dbms_output.put_line('Blocks uncompressed:         ' || m_blkcnt_uncmp);
    dbms_output.put_line('Rows per block compressed:   ' || m_row_cmp);
    dbms_output.put_line('Rows per block uncompressed: ' || m_row_uncmp);
    dbms_output.put_line('Compression Ratio:           ' || m_cmp_ratio);
    dbms_output.put_line('Comment:                     ' || m_comptype_str);

end;
/

Active partitions with mixed compression ratios

kish@exdbx<>set lines 155
compute sum of totalsize_megs on report
break on report on owner on segment_name
col owner for a20
col segment_name for a20
col part_name for a20
col segment_type for a10
col totalsize_megs for 999,999.9
select s.owner, segment_name, t.partition_name part_name,
sum(bytes/1024/1024) as totalsize_megs, compress_for
from dba_segments s, dba_tab_partitions t
where s.owner = t.table_owner
and t.table_name = s.segment_name
and s.owner like nvl('&owner',s.owner)
and segment_name like nvl('&table_name',segment_name)
group by s.owner, segment_name, t.partition_name, compress_for
order by 3;kish@exdbx<>kish@exdbx<>kish@exdbx<>kish@exdbx<>kish@exdbx<>kish@exdbx<>kish@exdbx<>kish@exdbx<>  2    3    4    5    6    7    8    9
Enter value for owner: TEST
old   6: and s.owner like nvl('&owner',s.owner)
new   6: and s.owner like nvl('TEST',s.owner)
Enter value for table_name: TABX
old   7: and segment_name like nvl('&table_name',segment_name)
new   7: and segment_name like nvl('TABX',segment_name)

OWNER                SEGMENT_NAME         PART_NAME            TOTALSIZE_MEGS COMPRESS_FOR
-------------------- -------------------- -------------------- -------------- ------------
TEST                 TABX                 P1                             24.0 <null>
                                          P2                             24.0 QUERY HIGH
                                          P3                             24.0 ARCHIVE LOW
******************** ********************                      --------------
sum                                                                      72.0

Elapsed: 00:00:00.96

TRACING HYBRID COLUMNAR COMPRESSION:

Lets trace EHCC using ADVCMP and examine the kernel stack behind the compression algorithms

kish@exdbx<>oradebug doc component ADVCMP


Components in library ADVCMP:
--------------------------
  ADVCMP_MAIN                  Archive Compression (kdz)
    ADVCMP_COMP                Archive Compression: Compression (kdzc, kdzh, kdza)
    ADVCMP_DECOMP              Archive Compression: Decompression (kdzd, kdzs)

There are different level of trace like low,lowest,high and highest used in ADVCMP utility

kish@exdbx<>alter session set tracefile_identifier='HCC_CMP';

Session altered.

Elapsed: 00:00:00.00
kish@exdbx<>alter session set events 'trace[ADVCMP_MAIN.*] disk=high';

Session altered.

Elapsed: 00:00:00.00
kish@exdbx<>alter table test.sales_tab move compress for query high;

Table altered.

Elapsed: 00:01:47.62
kish@exdbx<>alter session set events 'trace[ADVCMP_MAIN.*] off';

Session altered.

Elapsed: 00:00:00.00

Warning !! – Tracing HCC generate huge trace file size which you never expect .So keep a monitoring system to monitor your tracefile growth.

[oracle@exdbadm01 ~]$ du -sh /data/base/diag/rdbms/exdbx/exdbx1/trace/exdbx1_ora_8172_HCC_CMP_ARC_HIGH.trc
444M    /data/base/diag/rdbms/exdbx/exdbx1/trace/exdbx1_ora_8172_HCC_CMP_ARC_HIGH.trc

Take a look at kdzhDecideAlignment () routine in the trace output which shows the compression unit (CU) sizes with min and max target size .Interesting isnt it?

kdzcinit(): ctx: 0x2b650b5eff40  actx: (nil)  zca: (nil)  ulevel: 2  ncols: 14 totalcols: 14
kdzainit(): ctx: 2b650b5ef890 ulevel 2 amt 1048576 row 16384 min 5
kdzalcd(): objn: 88695 ulevel: 2
kdzalcd(): topalgo: -1 err: 100
kdza_init_eq(): objn: 88695  ulevel: 2  enqueue state:0


kdzhDecideAlignment(): pnum: 0 min_target_size: 32000 max_target_size: 64000 alignment_target_size: 128000 ksepec: 0 postallocmode: 0 hcc_flags: 0
 <===========================


kdzh_datasize(): freesz: 0 blkdtsz: 8168 flag: 1 initrans: 3 dbidl: 8050 dbhsz: 22 dbhszz: 14 drhsz: 9 maxmult: 1176
kdzh_datasize(): pnum: 0 ds: 8016 bs: 8192 ov: 20 alloc_num: 12 min_targetsz: 32000 max_targetsz: 64000 maxunitsz: 80000 delvec_size: 7904
kdzhbirc(): pnum: 0 buffer 1 rows soff: 0
kdblai(): klptrld: 0 kltbrld: 0 kltbnrp: 0 rowcnt: 1 flags: 131
kdzhbirc(): pnum: 0 buffer 1 rows soff: 0
kdblai(): klptrld: 1 kltbrld: 1 kltbnrp: 1 rowcnt: 1 flags: 131
kdzhbirc(): pnum: 0 buffer 1 rows soff: 0
kdblai(): klptrld: 2 kltbrld: 2 kltbnrp: 2 rowcnt: 1 flags: 131
kdzhbirc(): pnum: 0 buffer 1 rows soff: 0
kdblai(): klptrld: 3 kltbrld: 3 kltbnrp: 3 rowcnt: 1 flags: 131
kdzhbirc(): pnum: 0 buffer 1 rows soff: 0
kdblai(): klptrld: 4 kltbrld: 4 kltbnrp: 4 rowcnt: 1 flags: 131
kdzhbirc(): pnum: 0 buffer 1 rows soff: 0
kdblai(): klptrld: 5 kltbrld: 5 kltbnrp: 5 rowcnt: 1 flags: 131
kdzhbirc(): pnum: 0 buffer 1 rows soff: 0
kdblai(): klptrld: 6 kltbrld: 6 kltbnrp: 6 rowcnt: 1 flags: 131

kernel stack trace logs

data_block_dump,data header at 0x2b650bc8f07c
===============
tsiz: 0x1f80
hsiz: 0x1c
pbl: 0x2b650bc8f07c
     76543210
flag=-0------
ntab=1
nrow=1
frre=-1
fsbo=0x1c
fseo=0x1f
avsp=0x3
tosp=0x3
        r0_9ir2=0x0
        mec_kdbh9ir2=0x0
                      76543210
        shcf_kdbh9ir2=----------
                  76543210
        flag_9ir2=--R-----      Archive compression: Y
                fcls_9ir2[0]={ }
0x16:pti[0]     nrow=1  offs=0
0x1a:pri[0]     offs=0x1f
block_row_dump:
tab 0, row 0, @0x1f
tl: 8033 fb: ------PN lb: 0x0  cc: 1
nrid:  0x01005af0.0
col  0: [8021]
Compression level: 02 (Query High)
 Length of CU row: 8021

Decompression stack trace:

END_CU
bindmp: 03 00 01 01 00 5a f0 00 00 fe 55 1f 00 00 1f 55 10 b8 27 73 14 89 21 a4 1c db 82 28 7d 05 51 25 17 01 9c 6d 5c 57 ca 69 f2 07 0c 5a 8a 41 3a 56 4a 39 64 42 a6 4a 62 0f 53 f2 89 b6 f4 e8 66 a2 43 95 23 6d a6 a1 61 99 a7 6c 44 bb 61 b3 e3 2c 40 25 84

end_of_block_dump
kdzdinit(): initializing decompression context 0x2b650b726110 fco: 0x0 fcount: 0
kdzsInitialize(): context 2b650b726230 initialized
kdzdend(): freeing decompression context 0x2B650B726110
kdzsClose(): context 2b650b726230 destroyed
kdzhGetBlock():  pnum: 0 dba: 16800496 flags: 643 flag: 4 curblkidx_kdzh: 0 maxblkidx_kdzh: 2
kdzhailseb(): Normal new block avsp: 8036 fsbo: 28 fseo: 8064 nrow: 1 flags: 643 flag: 4 newed: 1 fscm: 8064 remsize: 8016
kdzhailseb(): pnum: 0 remsize : 8016 advcompress->remsize_kdzh: 6169 pdbh->kdbhavsp: 8036, cusize:23266 last_block: 0 first_block: 0
kdzhailseb(): pnum: 0 len_copied : 6169 crheader_len: 5 collen: 3, header_len: 3 curblk: 3 fscm: 8064 dba: 16800496
kdzh_copy_updptr(): numbufs:1 size: 6169 remlen: 6169 curoff: 17097 curidx: 0 culen[curidx]:23266 cusize: 23266 remsize: 6169
kdzhailseb(): Block loaded avsp: 1856 tosp: 1856 fsbo: 28 fseo: 1884 nrow: 1 flags: 643 flag: 4 fscm: 1884 remsize: 8016
bdba: 0x01005af0
data_block_dump,data header at 0x2b650bc9107c
===============
tsiz: 0x1f80
hsiz: 0x1c
pbl: 0x2b650bc9107c
     76543210
flag=-0------
ntab=1

Query high trace in log file which shows construction of compression unit

nrow=1
frre=-1
fsbo=0x1c
fseo=0x75c
avsp=0x740
tosp=0x740
        r0_9ir2=0x0
        mec_kdbh9ir2=0x0
                      76543210
        shcf_kdbh9ir2=----------
                  76543210
        flag_9ir2=--R-----      Archive compression: Y
                fcls_9ir2[0]={ }
0x16:pti[0]     nrow=1  offs=0
0x1a:pri[0]     offs=0x75c
block_row_dump:
tab 0, row 0, @0x75c
tl: 6180 fb: -----LP- lb: 0x0  cc: 1
col  0: [6174]
Compression level: 02 (Query High)
 Length of CU row: 6174
kdzhrh: --------START_CU:
 00 00 18 1e 10 24 31 6b 44 9d 50 55 6f 15 58 57 4f 7e 74 16 36 2f a6 9f 2a
 3f 51 73 ac 71 2c 72 c4 bd 57 39 e9 0d ea 07 9f 03 5c 30 32 c7 b2 34 11 31
 f7 36 dd 4c 12 4e ba 67 e0 0a 35 27 4c a1 2b 23 52 77 f9 4b c4 6a 31 c2 fc
 50 c5 ba 46 6e ee a1 46 aa ad 54 14 99 51 0d d1 68 99 33 be 62 59 b9 98 64
 ba ca 6f 99 d8 9b 3f 51 0b eb 6a ed 3d f8 f8 fb 23 81 61 4f 26 9d b9 0b 41

kdzcompress() algorithm for compression of objects

kdzcompress(): first CU, analyzed target input size: 152516
kdzcompress(): algo = 0, grouping = 2
kdzcompress(): ctx: 2b650b5eff40  CU: 0  Heap: 2b650b58e300  Heap size: 4535840  Allocated space: 4228984
Buffered length: 1048629  Rows: 13333  Columns: 14
kdzc_init_colgrps(): column-major
kdzc_finish_unit(): target output size: 32000  output size: 34683
kdzc_calc_new_target_input_size(): target size this unit: 140629, wtnew: 0.750000
 old target size: 152516, new target size: 143600
 input size: 152420, cu length: 34683
kdzc_finish_unit(): total units: 1 avg size: 34683
kdzc_finish_unit(): number of rows buffered: 13333  rows to unbuffer: 1934
 input size: 152420  buffered_size: 896209  new target_input_size: 143600
kdzhcl(): Invoking kdzhailseb klptpnum : 0 klptrld: 13332 kltbrld: 13332 kltbnrp: 13332 num_rows: 1934 flags: 131 *comp_state: 3
kdzhcl(): Invoking kdzhailseb pnum: 0 cusize:34683 num_bufs:1 num_rows:1934 expected_max_cusize: 0 max_delvec_size: 0 flags: 131 flag: 0
kdzhailseb(): Hybrid Columnar compression loading 1934 rows pnum: 0
kdzhailseb(): pnum: 0 context : 0 CUheader_sz: 268 CURHpmap_sz: 62 advcompress->remsize_kdzh: 34683 advcompress->cusize_kdzh: 34683 rstubsiz: 9 approx_blks: 6
kdzhailseb(): pnum: 0 flags: 131 kdobjflg: 0
kdzhGetBlockDba(): kdblGet pnum: 0 dba: 16791691
kdzhailseb(): Got dba for pnum: 0 blk: 0 lens: 7954 remcusz: 26729
kdzhGetBlockDba(): kdblGet pnum: 0 dba: 16791692
kdzhAUCrossed(): pnum: 0 computed alignsz: 1048576 divalign: 128
kdzhailseb(): Got dba for pnum: 0 blk: 1 lens: 8016 remcusz: 18713
kdzhGetBlockDba(): kdblGet pnum: 0 dba: 16791693
kdzhailseb(): Got dba for pnum: 0 blk: 2 lens: 8016 remcusz: 10697
kdzhGetBlockDba(): kdblGet pnum: 0 dba: 16791694
kdzhailseb(): Got dba for pnum: 0 blk: 3 lens: 8016 remcusz: 2681

Check the CPU utilization while compression for all four algorithms

kish@exdbx<>alter session set tracefile_identifier='HCC_CMP_ARC_LOW';

Session altered.

Elapsed: 00:00:00.00
kish@exdbx<>alter session set events 'trace[ADVCMP_MAIN.*] disk=high';

Session altered.

Elapsed: 00:00:00.04
kish@exdbx<>alter table test.sales_tab move compress for archive low;

Table altered.

Elapsed: 00:01:56.63



kish@exdbx<>alter session set events 'trace[ADVCMP_MAIN.*] off';

Session altered.


kish@exdbx<>select username,terminal,program,tracefile from v$process where spid=8172;

USERNAME        TERMINAL                       PROGRAM
--------------- ------------------------------ ------------------------------------------------
TRACEFILE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
oracle          UNKNOWN                        oracle@exdbadm01 (TNS V1-V3)
/data/base/diag/rdbms/exdbx/exdbx1/trace/exdbx1_ora_8172_HCC_CMP_ARC_LOW.trc


  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 
8172 oracle    25   0 2124m  72m  57m R 92.6  1.6   0:43.09 oracle

Archive low CPU usage()

kish@exdbx<>alter session set tracefile_identifier='HCC_CMP_ARC_HIGH';

Session altered.

Elapsed: 00:00:00.00

kish@exdbx<>alter session set events 'trace[ADVCMP_MAIN.*] disk=high';

Session altered.

Elapsed: 00:00:00.00
kish@exdbx<>alter table test.sales_tab move compress for archive high;

Table altered.

Elapsed: 00:01:59.35

kish@exdbx<>select username,terminal,program,tracefile from v$process where spid=8172;

USERNAME        TERMINAL                       PROGRAM
--------------- ------------------------------ ------------------------------------------------
TRACEFILE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
oracle          UNKNOWN                        oracle@exdbadm01 (TNS V1-V3)
/data/base/diag/rdbms/exdbx/exdbx1/trace/exdbx1_ora_8172_HCC_CMP_ARC_HIGH.trc

 
 PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 8172 oracle    25   0 2157m 113m  72m R 98.7  2.5   2:03.88 oracle

Compression Analyzer Context Dump End
-------------------------------------
kdzalcd(): topalgo: 0 err: 0
kdza_init_eq(): objn: 88695  ulevel: 4  state: 0  already analyzed
kdzhDecideAlignment(): pnum: 0 min_target_size: 256000 max_target_size: 256000 alignment_target_size: 128000 ksepec: 0 postallocmode: 0 hcc_flags: 0
kdzh_datasize(): freesz: 0 blkdtsz: 8168 flag: 1 initrans: 3 dbidl: 8050 dbhsz: 22 dbhszz: 14 drhsz: 9 maxmult: 1
kdzh_datasize(): pnum: 0 ds: 8016 bs: 8192 ov: 20 alloc_num: 42 min_targetsz: 256000 max_targetsz: 256000 maxunitsz: 320000 delvec_size: 7604
kdzhbirc(): pnum: 0 buffer 1 rows soff: 0

kdzhGetBlockDba(): FETCHDBAS pnum: 0 numdbas_kdzh: 13, curdbidx_kdzh: 10 dbas_kdzh[0]:16777867 dbas_kdzh[curdbidx]:16777877 dba_kdzh[0]:16777867
kdzhailseb(): Got dba for pnum: 0 blk: 10 lens: 8016 remcusz: 6230
kdzhGetBlockDba(): FETCHDBAS pnum: 0 numdbas_kdzh: 13, curdbidx_kdzh: 11 dbas_kdzh[0]:16777867 dbas_kdzh[curdbidx]:16777878 dba_kdzh[0]:16777867
kdzhailseb(): Got dba for pnum: 0 blk: 11 lens: 6230 remcusz: 0
kdzhailseb(): Start loading loop pnum: 0 nblks: 12 ctx_restored: 0 flags: 139 flag: 0 dbas_contiguous: 11
kdzhGetBlock():  pnum: 0 dba: 16777867 flags: 139 flag: 0 curblkidx_kdzh: 0 maxblkidx_kdzh: 14
kdzhailseb(): Normal new block avsp: 8036 fsbo: 28 fseo: 8064 nrow: 1 flags: 139 flag: 0 newed: 1 fscm: 8064 remsize: 8016
kdzhailseb(): pnum: 0 remsize : 8016 advcompress->remsize_kdzh: 94274 pdbh->kdbhavsp: 8036, cusize:94274 last_block: 0 first_block: 1
kdzhailseb(): pnum: 0 len_copied : 7884 crheader_len: 120 collen: 3, header_len: 9 curblk: 0 fscm: 8064 dba: 16777867
kdzh_copy_updptr(): numbufs:1 size: 7884 remlen: 94274 curoff: 0 curidx: 0 culen[curidx]:94274 cusize: 94274 remsize: 94274
kdzhailseb(): Block loaded avsp: 20 tosp: 20 fsbo: 28 fseo: 48 nrow: 1 flags: 139 flag: 4 fscm: 48 remsize: 8016
bdba: 0x0100028b
data_block_dump,data header at 0x2b58079f907c
===============


 PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 8172 oracle    25   0 2129m  88m  73m R 97.0  1.9   4:52.14 oracle

Query low CPU usage()

kish@exdbx<>select username,terminal,program,tracefile from v$process where spid=3276;

USERNAME        TERMINAL                       PROGRAM
--------------- ------------------------------ ------------------------------------------------
TRACEFILE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
oracle          UNKNOWN                        oracle@exdbadm01 (TNS V1-V3)
/data/base/diag/rdbms/exdbx/exdbx1/trace/exdbx1_ora_3276_HCC_QUERY_LOW.trc

kish@exdbx<>alter session set tracefile_identifier='HCC_QUERY_LOW';

Session altered.

Elapsed: 00:00:00.02
kish@exdbx<>alter session set events 'trace[ADVCMP_MAIN.*] disk=high';

Session altered.

Elapsed: 00:00:00.23
kish@exdbx<>alter table test.sales_tab move compress for query low;
Elapsed: 00:02:38.51


  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 3276 oracle    25   0 2116m  51m  43m R 96.2  1.1   0:17.97 oracle

HDFS not starting : java.io.IOException: the path component: ‘/var’ is owned by a user who is not root and not you. Your effective user id is 993; the path is owned by user id 977, and its permissions are 0755. Please fix this or select a different socket path. at org.apache.hadoop.net.unix.DomainSocket.validateSocketPathSecurity0(Native Method)

Cause:

HDFS is not starting from cloudera because of permission error after installing spark. After spark is installed, the ownership of /var mountpoint change to spark:spark

[hdfs@ip-xx /]$ ls -lrt
total 32
drwxr-xr-x.  20 spark spark  278 Jun 14  2018 var

Workaround:

Change the owner to root

chown root:root /var

Check the ownership

drwxr-xr-x.  20 root root  278 Jun 14  2018 var

ORACLE DATABASE STATIC HEALTH CHECK SCRIPT

   Life          Life
  LifeLifeLifeLifeLife
LifeLifeLifeLifeLifeLife
LifeLifeLifeLifeLifeLife
LifeLifeLifeLifeLifeLife
  LifeLifeLifeLifeLife
    LifeLifeLifeLife
      LifeLifeLife
          Life

Are you looking for a basic health check report for your oracle database ? Hope this consolidated script might help you in your project for a high level peek on behaviour of database.

Below are the parameters included in this script

--PROCESS AND SESSION USAGE
--LONG RUNNING SESSION
--CPU USAGE 
--SGA FREE MEMORY
--IO USAGE ON DATAFILE
--IO USAGE BY SESSION
--NETWORK STATS
--TABLESPACE USAGE
--LOG SWITCH FREQUENCY PER HOUR
--LOCK INFO
--TOP RESOURCE CONSUMING SQL
--TOP LATCHES
--RMAN BACKUP STATUS
--TEMP FREE SPACE
--STALE AND UNUSABLE TABLE AND INDEX STATS
--FRA USAGE AND AVAILABLE SPACE
--ORA ERRORS REPORTED RECENT
HEALTH CHECK REPORT IN HTML

Oops ! It looks like too much of information in one report. Some of the trivial queries can be removed from the report for brevity


#!/bin/bash -x


#Set Variables
_set_var(){
LOGDIR=/tmp/log;
TMPDIR=/tmp/output;
HOST=`hostname`
DT=`date`
export LOGDIR TMPDIR
}
#Set Environment for database
_set_env(){
ORACLE_HOME=/data/oracle/product/11.2.0/db
ORACLE_SID=exdbx1
PATH=${ORACLE_HOME}/bin
export ORACLE_HOME ORACLE_SID PATH
}

#verify if you execute the script using oracle user.Note that UID may differ for oracle user.
_verify_user()
{
   if [ $(id -u) != 1100 ]; then
       _log "Run as oracle user"
       exit 1
   fi
}
#Database health check
_sql_report(){
#touch /tmp/Health_checkup.sql
sqlplus -S / as sysdba <<EOF
set pagesize 1100
set feedback off
set trimout on
set termout on
set term off
set linesize 200;
set pages 50
set lines 200
set pages 1000
set heading on
set timing off
SET MARKUP HTML ON SPOOL ON ENTMAP OFF -
head '<title> DAILY HEALTH CHECK </title> -
<style type="text/css"> -
table {background: #FFFFE0; font-size: 99%;} -
  th { background-color: DarkBlue; color:White} -
  td { padding: 0px; } -
</style>' -
body 'text=black bgcolor=FAFAD2 align=left' -
table 'align=center width=99% border=3 bordercolor=black bgcolor=grey'
spool /tmp/health_check.html

prompt
PROMPT

ttitle center  '<span style="background-color:DarkBlue;color:#ff3f3d;border:1px solid black;font-size:20pt;">DAILY HEALTH CHECK</span>'
PROMPT
PROMPT
col Report_Date format a30 justify center
select to_char(sysdate,'DD-MON-YYYY:HH:MI') "Report_Date" from dual;
ttitle left  '<span style="background-color:DarkBlue;color:#ff3f3d;border:1px solid black;font-size:15pt;">PROCESS AND SESSION USAGE</span>'

col RESOURCE_NAME format a20
col LIMIT_VALUE format 9999999
col CURRENT_UTILIZATION format 9999999
col MAX_UTILIZATION format 9999999
select RESOURCE_NAME,LIMIT_VALUE,CURRENT_UTILIZATION,MAX_UTILIZATION from v\$resource_limit where RESOURCE_NAME in ('processes','sessions');
PROMPT

ttitle left  '<span style="background-color:DarkBlue;color:#ff3f3d;border:1px solid black;font-size:15pt;">LONG RUNNING SESSION</span>'
PROMPT
COLUMN sid FORMAT 999 justify center
COLUMN serial# FORMAT 9999999 justify center
COLUMN machine FORMAT A30 justify center
COLUMN progress_pct FORMAT 99999999.00 justify center
COLUMN elapsed FORMAT A10 justify center
COLUMN remaining FORMAT A10 justify center
COLUMN sql_id FORMAT A10
COLUMN sql_text FORMAT A10
set numwidth 40
set long 100000000
SELECT s.sid,
s.serial#,
s.machine,
sq.sql_id,
sq.sql_text,
ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
ROUND(sl.sofar/sl.totalwork*100, 2) progress
FROM   v\$session s
inner join v\$session_longops sl on s.sid = sl.sid
inner join v\$sql sq on s.sql_id = sq.sql_id
and s.serial# = sl.serial#;

ttitle left  '<span style="background-color:DarkBlue;color:#ff3f3d;border:1px solid black;font-size:15pt;">CPU USAGE BY SESSION</span>'
PROMPT
set lines 250
set pages 2000
col username format a15 justify center
col program format a20 justify center
col event format a30 justify center
col sid format 99999 justify center
col SESSION_CPU_USAGE format 99999 justify center
select * from (select z.sid,nvl(z.username,'oracle-bg') as username,nvl(z.SQL_ID,'non-SQL') as SQL_ID,z.EVENT,z.program,round(sum(y.value)/100,6) as "SESSION_CPU_USAGE"
from v\$statname x
inner join v\$sesstat y on x.STATISTIC# = y.STATISTIC#
inner join v\$session z on y.SID = z.SID
where x.name in ('CPU used by this session') group by z.sid,z.username,z.SQL_ID,z.EVENT,z.program order by SESSION_CPU_USAGE desc)
where rownum < 6;

PROMPT
PROMPT

ttitle left  '<span style="background-color:DarkBlue;color:#ff3f3d;border:1px solid black;font-size:15pt;">SGA FREE MEMORY</span>'
PROMPT

select POOL,NAME,round(BYTES/1073741824,2) as MEMORY_SIZE_GB  from v\$sgastat where name like '%free memory%';

PROMPT

ttitle left  '<span style="background-color:DarkBlue;color:#ff3f3d;border:1px solid black;font-size:15pt;">IO USAGE ON DATAFILES</span>'
PROMPT

col name format a20
col DFSIZE_GB format 999999
col BLOCKS format 99999999
col PHYRDS format 99999999
col PHYWRTS format 99999999
col PHYBLKRD format 99999999
col PHYBLKWRT format 99999999
col SINGLEBLKRDS format 99999999
col AVGIOTIM format 99999999
select df.NAME,round(sum(df.bytes/1073741824),2) as DFSIZE_GB,df.BLOCKS,fs.PHYRDS,fs.PHYWRTS,fs.PHYBLKRD,fs.PHYBLKWRT,fs.SINGLEBLKRDS,fs.AVGIOTIM
from v\$datafile df
inner join v\$filestat fs on df.file#=fs.file#
group by df.NAME,df.BLOCKS,fs.PHYRDS,fs.PHYWRTS,fs.PHYBLKRD,fs.PHYBLKWRT,fs.SINGLEBLKRDS,fs.AVGIOTIM
order by fs.PHYRDS desc;

ttitle left  '<span style="background-color:DarkBlue;color:#ff3f3d;border:1px solid black;font-size:15pt;">IO USAGE BY SESSION</span>'
PROMPT

col SID format 99999
col EVENT format a20
col PROGRAM format a20
col BLOCK_GETS format 99999
col CONSISTENT_GETS format 99999
col PHYSICAL_READS format 99999
col USERNAME format a10
col process format a10
col serial# format 99999
col pid format 99999
col spid format 99999
select * from (select s.sid,s.serial#,s.EVENT,s.PROGRAM,nvl(s.USERNAME,'oracle') as USERNAME,si.BLOCK_GETS,si.CONSISTENT_GETS,si.PHYSICAL_READS
from v\$session s
inner join v\$sess_io si on s.sid=si.sid
order by si.PHYSICAL_READS desc) where rownum < 6;

ttitle left  '<span style="background-color:DarkBlue;color:#ff3f3d;border:1px solid black;font-size:15pt;">NETWORK STATS</span>'
PROMPT

select n.name,round(sum(s.value/1073741824),6) from v\$sesstat s
inner join v\$statname n on s.STATISTIC#=n.STATISTIC#
and n.name like '%SQL*Net%'
group by n.name;


ttitle left  '<span style="background-color:DarkBlue;color:#ff3f3d;border:1px solid black;font-size:15pt;">TABLESPACE USAGE > 50%</span>'
PROMPT
col TOTAL_SPACE format 999999
col TABLESPACE_NAME format a20
col TOTAL_FREE_SPACE format 999999
col UTIL_PCT format 999999
select x.TABLESPACE_NAME,round((x.bytes/1073741824),2) as TOTAL_SPACE_GB,
round(x.bytes/1073741824,2) - round(sum(y.bytes/1073741824),2) as TOTAL_FREE_SPACE_GB,
case when to_number(round((round(sum(y.bytes/1073741824),2)/round(x.bytes/1073741824,2))*100,2)) > 50 then '<font color=red>' || to_char(round((round(sum(y.bytes/1073741824),2)/round(x.bytes/1073741824,2))*100,2)) || '</font>'
when to_number(round((round(sum(y.bytes/1073741824),2)/round(x.bytes/1073741824,2))*100,2)) < 50 then '<font color=green>' || to_char(round((round(sum(y.bytes/1073741824),2)/round(x.bytes/1073741824,2))*100,2)) || '</font>'
else to_char(round((round(sum(y.bytes/1073741824),2)/round(x.bytes/1073741824,2))*100,2))
end UTIL_PCT
from dba_data_files x
inner join dba_free_space y on x.TABLESPACE_NAME = y.TABLESPACE_NAME
group by x.TABLESPACE_NAME,x.bytes/1073741824;

ttitle left  '<span style="background-color:DarkBlue;color:#ff3f3d;border:1px solid black;font-size:15pt;">REDO LOGS SWITCH FREQUENCY PER HOUR</span>'
PROMPT
col Day form a3
col h0 format 99
col h1 format 99
col h2 format 99
col h3 format 99
col h4 format 99
col h5 format 999
col h6 format 999
col h7 format 999
col h8 format 999
col h9 format 999
col h10 format 999
col h11 format 999
col h12 format 999
col h13 format 999
col h14 format 999
col h15 format 999
col h16 format 999
col h17 format 999
col h18 format 999
col h19 format 999
col h20 format 999
col h21 format 999
col h22 format 999
col h23 format 999
select to_char(first_time,'DY') as DAY,
sum(case to_char(FIRST_TIME,'hh24') when '00' then 1 else 0 end ) as h0,
sum(case to_char(FIRST_TIME,'hh24') when '01' then 1 else 0 end ) as h1,
sum(case to_char(FIRST_TIME,'hh24') when '02' then 1 else 0 end) as h2,
sum(case to_char(FIRST_TIME,'hh24') when '03' then 1 else 0 end) as h3,
sum(case to_char(FIRST_TIME,'hh24') when '04' then 1 else 0 end) as h4,
sum(case to_char(FIRST_TIME,'hh24') when '05' then 1 else 0 end) as h5,
sum(case to_char(FIRST_TIME,'hh24') when '06' then 1 else 0 end) as h6,
sum(case to_char(FIRST_TIME,'hh24') when '07' then 1 else 0 end) as h7,
sum(case to_char(FIRST_TIME,'hh24') when '08' then 1 else 0 end) as h8,
sum(case to_char(FIRST_TIME,'hh24') when '09' then 1 else 0 end) as h9,
sum(case to_char(FIRST_TIME,'hh24') when '10' then 1 else 0 end) as h10,
sum(case to_char(FIRST_TIME,'hh24') when '11' then 1 else 0 end) as h11,
sum(case to_char(FIRST_TIME,'hh24') when '12' then 1 else 0 end) as h12,
sum(case to_char(FIRST_TIME,'hh24') when '13' then 1 else 0 end) as h13,
sum(case to_char(FIRST_TIME,'hh24') when '14' then 1 else 0 end) as h14,
sum(case to_char(FIRST_TIME,'hh24') when '15' then 1 else 0 end) as h15,
sum(case to_char(FIRST_TIME,'hh24') when '16' then 1 else 0 end) as h16,
sum(case to_char(FIRST_TIME,'hh24') when '17' then 1 else 0 end) as h17,
sum(case to_char(FIRST_TIME,'hh24') when '18' then 1 else 0 end) as h18,
sum(case to_char(FIRST_TIME,'hh24') when '19' then 1 else 0 end) as h19,
sum(case to_char(FIRST_TIME,'hh24') when '20' then 1 else 0 end) as h20,
sum(case to_char(FIRST_TIME,'hh24') when '21' then 1 else 0 end) as h21,
sum(case to_char(FIRST_TIME,'hh24') when '22' then 1 else 0 end) as h22,
sum(case to_char(FIRST_TIME,'hh24') when '23' then 1 else 0 end) as h23
from v\$log_history
group by to_char(first_time,'DY')
order by case to_char(first_time,'DY')
when 'SUN' then 0
when 'MON' then 1
when 'TUE' then 2
when 'WED' then 3
when 'THU' then 4
when 'FRI' then 5
when 'SAT' then 6
end ASC;

ttitle left  '<span style="background-color:DarkBlue;color:#ff3f3d;border:1px solid black;font-size:15pt;">LOCK INFORMATION</span>'
PROMPT
col sid format 999999
col username format a15
col sql_id format a15
col event format a15
col lmode format 99 justify center
col block format 999999
col object_id format 999999
col sql_text format a20
col BLKINST format 99
col blocking_session format 99
select x.sid,x.username,x.sql_id,x.BLOCKING_INSTANCE as BLKINST,x.blocking_session,x.event,y.lmode,z.object_id,a.sql_text
from v\$session x
inner join v\$lock y on x.sid=y.sid
inner join v\$locked_object z on y.sid = z.session_id
inner join v\$sql a on x.sql_id = a.sql_id
where x.status='ACTIVE' and x.blocking_session is NOT NULL;

ttitle left  '<span style="background-color:DarkBlue;color:#ff3f3d;border:1px solid black;font-size:15pt;">HIGH RESOURCE CONSUMING SQL</span>'
PROMPT
col EVENT format a20
col AVERAGE_WAIT format 99999999
col TIME_WAITED format 99999999
column WAIT_CLASS format a15
col sid format 999999
col sql_text format a40
select * from (select se.sid,se.event,se.AVERAGE_WAIT,se.TIME_WAITED,se.WAIT_CLASS,sq.sql_text
from v\$session_event se
inner join v\$session s on s.sid = se.sid
inner join v\$sql sq on sq.sql_id = s.sql_id
where sq.sql_text not like '%select se.sid,se.event,se .AVERAGE_WAIT%'
order by AVERAGE_WAIT desc)
where rownum < 6;
ttitle left  '<span style="background-color:DarkBlue;color:#ff3f3d;border:1px solid black;font-size:15pt;">TOP LATCHES</span>'

col NAME format a20
col MISSES format 9999999
col SLEEPS format 9999999
col GETS format 9999999
col IMMEDIATE_GETS format 999999999
col IMMEDIATE_MISSES format 999999999
select * from (select name,gets,misses,sleeps,immediate_gets,immediate_misses from v\$latch order by sleeps desc) where rownum < 6;
PROMPT
ttitle left  '<span style="background-color:DarkBlue;color:#ff3f3d;border:1px solid black;font-size:15pt;">RMAN BACKUP STATUS</span>'
PROMPT
col STATUS format a15
col hrs format 999.99
select
INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
elapsed_seconds/3600 hrs
from V\$RMAN_BACKUP_JOB_DETAILS
where start_time > sysdate -1
order by session_key desc;


ttitle left '<span style="background-color:DarkBlue;color:#ff3f3d;border:1px solid black;font-size:15pt;">TEMP FREE SPACE</span>'
col TABLESPACE_NAME format a20
col TABLESPACE_SIZE format 9999999
col ALLOCATED_SPACE format 9999999
col FREE_SPACE format 9999999
select TABLESPACE_NAME,TABLESPACE_SIZE/1048576 as TABLESPACE_SIZE,ALLOCATED_SPACE/1048576 as ALLOCATED_SPACE,FREE_SPACE/1048576 as FREE_SPACE from dba_temp_free_space;


ttitle left  '<span style="background-color:DarkBlue;color:#ff3f3d;border:1px solid black;font-size:15pt;">TABLE STATS LAST ANALYZED</span>'
PROMPT
select owner,count(table_name) stale_stats from all_tables where last_analyzed > sysdate - 7 group by owner order by stale_stats desc;

ttitle left  '<span style="background-color:DarkBlue;color:#ff3f3d;border:1px solid black;font-size:15pt;">INDEX STATS LAST ANALYZED</span>'
PROMPT
select table_owner,count(index_name) as stale_stats from all_indexes where last_analyzed > sysdate - 7 group by table_owner order by stale_stats desc;

ttitle left  '<span style="background-color:DarkBlue;color:#ff3f3d;border:1px solid black;font-size:15pt;">UNUSABLE INDEXES ON THE DATABASE</span>'
prompt
select index_name,table_owner from DBA_INDEXES where status='UNUSABLE';


ttitle left '<span style="background-color:DarkBlue;color:#ff3f3d;border:1px solid black;font-size:15pt;">FRA SPACE USAGE</span>'

PROMPT
set lines 200
set pages 1000
col PERCENT_SPACE_USED format 99999
col PERCENT_SPACE_RECLAIMABLE format 99999
col NUMBER_OF_FILES format 99999
select * from v\$flash_recovery_area_usage;

ttitle left '<span style="background-color:DarkBlue;color:#ff3f3d;border:1px solid black;font-size:15pt;"> FRA SPACE AVAILABLE</span>'
col NAME format a10
col SPACE_LIMIT_MB format 9999999
col SPACE_USED_MB format 9999999
col SPACE_RECLAIMABLE_MB format 9999999
select name,SPACE_LIMIT/1048576 as SPACE_LIMIT_MB,SPACE_USED/1048576 as SPACE_USED_MB,SPACE_RECLAIMABLE/1048576 as SPACE_RECLAIMABLE_MB from v\$recovery_file_dest;

ttitle left '<span style="background-color:DarkBlue;color:#ff3f3d;border:1px solid black;font-size:15pt;">ORA ERRORS</span>'
set lines 200
set pages 1000
col check_error format a20
col message_text format a50
select message_text,to_char(ORIGINATING_TIMESTAMP,'dd-mon-yy hh24:mi:ss'),
case when regexp_like(message_text,'ORA-') then 'ORA errors found'
else 'No errors'
end as check_error
from x\$dbgalertext where message_text like '%ORA-%' and ORIGINATING_TIMESTAMP > sysdate - 7 order by 1;


spool off
set markup html off
exit;
EOF
}
#Send mail 
mailx -a /tmp/health_check.html -s "DB_HEALTH_CHECK" xxx.gmail.com  
_set_var
_set_env
_verify_user
_sql_report

Enjoy !

SQL QUERY TO FIND PROCESS AND SESSION RESOURCE LIMIT ORACLE

If there concurrent sessions and process connections overflow in your database,then you might get into trouble of customer complaint because of performance or connectivity issues. This query will help you to get limit compared with current and max utilization of processes and sessions

SQL Query:

col RESOURCE_NAME format a20
col LIMIT_VALUE format 9999999
col CURRENT_UTILIZATION format 9999999
col MAX_UTILIZATION format 9999999
select RESOURCE_NAME,LIMIT_VALUE,CURRENT_UTILIZATION,MAX_UTILIZATION from v$resource_limit where RESOURCE_NAME in ('processes','sessions');

Output:

kish@exdbx<>col RESOURCE_NAME format a20
col LIMIT_VALUE format 9999999
col CURRENT_UTILIZATION format 9999999
col MAX_UTILIZATION format 9999999
select RESOURCE_NAME,LIMIT_VALUE,CURRENT_UTILIZATION,MAX_UTILIZATION from v$resource_limit where RESOURCE_NAME in ('processes','sessions');kish@exdbx<>kish@exdbx<>kish@exdbx<>kish@exdbx<>

RESOURCE_NAME        LIMIT_VALUE                              CURRENT_UTILIZATION MAX_UTILIZATION
-------------------- ---------------------------------------- ------------------- ---------------
processes                   150                                                53              57
sessions                    247                                                58              67

SQL QUERY TO GET TABLESPACE USAGE IN HTML FORMAT ORACLE

Everyone like colourful output which looks beautiful.If you ask tablespace usage report in html format to me and i have one for you.

SQL Query:

col TOTAL_SPACE format 999999
col TABLESPACE_NAME format a20
col TOTAL_FREE_SPACE format 999999
col UTIL_PCT format 999999
select x.TABLESPACE_NAME,round((x.bytes/1073741824),2) as TOTAL_SPACE_GB,
round(x.bytes/1073741824,2) - round(sum(y.bytes/1073741824),2) as TOTAL_FREE_SPACE_GB,
to_number(round((round(sum(y.bytes/1073741824),2)/round(x.bytes/1073741824,2))*100,2)) as UTIL_PCT
from dba_data_files x
inner join dba_free_space y on x.TABLESPACE_NAME = y.TABLESPACE_NAME
group by x.TABLESPACE_NAME,x.bytes/1073741824;

Normal output:

kish@exdbx<>kish@exdbx<>col TOTAL_SPACE format 999999
col TABLESPACE_NAME format a20
col TOTAL_FREE_SPACE format 999999
col UTIL_PCT format 999999
select x.TABLESPACE_NAME,round((x.bytes/1073741824),2) as TOTAL_SPACE_GB,
round(x.bytes/1073741824,2) - round(sum(y.bytes/1073741824),2) as TOTAL_FREE_SPACE_GB,
to_number(round((round(sum(y.bytes/1073741824),2)/round(x.bytes/1073741824,2))*100,2)) as UTIL_PCT
from dba_data_files x
inner join dba_free_space y on x.TABLESPACE_NAME = y.TABLESPACE_NAME
group by x.TABLESPACE_NAME,x.bytes/1073741824;kish@exdbx<>kish@exdbx<>kish@exdbx<>kish@exdbx<>  2    3    4    5    6 

TABLESPACE_NAME                                TOTAL_SPACE_GB                      TOTAL_FREE_SPACE_GB UTIL_PCT
-------------------- ---------------------------------------- ---------------------------------------- --------
SYSTEM                                                    .97                                      .93        4
EXAMPLE                                                   .34                                       .3       12
UNDOTBS2                                                  .05                                      .01       80
USERS                                                    3.02                                     2.82        7
SYSAUX                                                    .81                                      .76        6
UNDOTBS1                                                  .47                                      .05       89

6 rows selected.

SQL Query for HTML:

SET MARKUP HTML ON SPOOL ON ENTMAP OFF -
head '<title> DAILY HEALTH CHECK </title> -
<style type="text/css"> -
table {background: #FFFFE0; font-size: 99%;} -
  th { background-color: DarkBlue; color:White} -
  td { padding: 0px; } -
</style>' -
body 'text=black bgcolor=FAFAD2 align=left' -
table 'align=center width=99% border=3 bordercolor=black bgcolor=grey'
spool /tmp/tb_check.html


col TOTAL_SPACE format 999999
col TABLESPACE_NAME format a20
col TOTAL_FREE_SPACE format 999999
col UTIL_PCT format 999999
select x.TABLESPACE_NAME,round((x.bytes/1073741824),2) as TOTAL_SPACE_GB,
round(x.bytes/1073741824,2) - round(sum(y.bytes/1073741824),2) as TOTAL_FREE_SPACE_GB,
case when to_number(round((round(sum(y.bytes/1073741824),2)/round(x.bytes/1073741824,2))*100,2)) > 50 then '<font color=red>' || to_char(round((round(sum(y.bytes/1073741824),2)/round(x.bytes/1073741824,2))*100,2)) || '</font>'
when to_number(round((round(sum(y.bytes/1073741824),2)/round(x.bytes/1073741824,2))*100,2)) < 50 then '<font color=green>' || to_char(round((round(sum(y.bytes/1073741824),2)/round(x.bytes/1073741824,2))*100,2)) || '</font>'
else to_char(round((round(sum(y.bytes/1073741824),2)/round(x.bytes/1073741824,2))*100,2))
end UTIL_PCT
from dba_data_files x
inner join dba_free_space y on x.TABLESPACE_NAME = y.TABLESPACE_NAME
group by x.TABLESPACE_NAME,x.bytes/1073741824;
spool off
set markup html off

HTML OUTPUT:

Enjoy!

SQL QUERY TO FIND TOP SQL ORACLE

My job was at stake due to performance issue from a financial application database.Client was bleeding money due to report deadline with in financial year.

There should be a quick way to identify the top SQL which eats up the database bit by bit and crash the database. Thats why its always keep a handy query like this often in your script list

The below sql will identify the top sql which burns CPU and have high elapsed time

SQL Query:

col EVENT format a20
col AVERAGE_WAIT format 99999999
col TIME_WAITED format 99999999
column WAIT_CLASS format a15
col sid format 999999
col sql_text format a40
select * from (select se.sid,se.event,se.AVERAGE_WAIT,se.TIME_WAITED,se.WAIT_CLASS,sq.sql_text
from v$session_event se
inner join v$session s on s.sid = se.sid
inner join v$sql sq on sq.sql_id = s.sql_id
where sq.sql_text not like '%select se.sid,se.event,se .AVERAGE_WAIT%'
order by AVERAGE_WAIT desc)
where rownum < 6;