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>
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
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;doneIf 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
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
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
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
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 Types | Description | Expected Compression |
| Query low | LZO Algorithm,consume least CPU,Warehouse low | 4x |
| Query high | ZLIB(gzip),Warehouse high | 6x |
| Archive low | ZLIB(gzip),Query high | 7x |
| Archive high | BZIP2,Archive low | 12x |
| Compression | Type | min_target_size | max_target_size | alignment_target_size |
| Query | Low | 32000 | 32000 | 128000 |
| Query | High | 32000 | 64000 | 128000 |
| Archive | Low | 32000 | 256000 | 128000 |
| Archive | High | 256000 | 256000 | 128000 |
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 SALESCompress 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.85Check 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.43Check the final size
kish@exdbx<>select bytes/1073741824,segment_name from user_segments where segment_name='SALES3';
BYTES/1073741824 SEGMENT_NAME
---------------- ------------------------------------------------------------------------
.018432617 SALES3ARCHIVE 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:
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
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
Life Life
LifeLifeLifeLifeLife
LifeLifeLifeLifeLifeLife
LifeLifeLifeLifeLifeLife
LifeLifeLifeLifeLifeLife
LifeLifeLifeLifeLife
LifeLifeLifeLife
LifeLifeLife
LifeAre 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
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 !
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
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 offHTML OUTPUT:
Enjoy!
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;