OBIA on Teradata - Part 2 Teradata DB Utilization for ETL
Posted
by Mohan Ramanuja
on Oracle Blogs
See other posts from Oracle Blogs
or by Mohan Ramanuja
Published on Thu, 6 Sep 2012 04:24:10 +0000
Indexed on
2012/09/06
9:44 UTC
Read the original article
Hit count: 320
Filed under:
/Oracle/OBI
Techniques to Monitor Queries and ETL Load
CPU and Disk I/O
Select username, processor, sum(cputime), sum(diskio) from dbc.ampusage where processor ='1-0' order by 2,3 descgroup by 1,2;
UserName Vproc Sum(CpuTime) Sum(DiskIO)
AC00916 10 6.71 24975
List Hardware Errors
There is a possibility that the system might have adequate disk space but out of free cylinders. In order to monitor hardware errors, the following query was used:Select * from dbc.Software_Event_Log where Text like '%restart%' order by thedate, thetime;
For active users, usage of CPU and analysis of bad CPU to I/O ratios
Select * from DBC.AMPUSAGE where username='CRMSTGC_DEV_ID'; AND SUBSTR(ACCOUNTNAME,6,3)='006';
Usage By I/O
Select AccountName, UserName, sum(CpuTime), sum(DiskIO) from DBC.AMPUSAGE group by AccountName, UserName Order by Sum(DiskIO) desc;AccountName UserName Sum(CpuTime) Sum(DiskIO)
$M1$10062209 AB89487 374628.612 7821847
$M1$10062210 AB89487 186692.244 2799412
$M1$10062213 COC_ETL_ID 119531.068 331100426
$M1$10062200 AB63472 118973.316 109881984
$M1$10062204 AB63472 110825.356 94666986
$M1$10062201 AB63472 110797.976 75016994
$M1$10062202 AC06936 100924.448 407839702
$M1$10062204 AB67963 0 4
$M1$10062207 AB91990 0 2
$M1$10062208 AB63461 0 24
$M1$10062211 AB84332 0 6
$M1$10062214 AB65484 0 8
$M1$10062205 AB77529 0 58
$M1$10062210 AC04768 0 36
$M1$10062206 AB54940 0 22
Usage By CPU
Select AccountName, UserName, sum(CpuTime), sum(DiskIO) from DBC.AMPUSAGE group by AccountName, UserName Order by Sum(CpuTime) desc;AccountName UserName Sum(CpuTime) Sum(DiskIO)
$M1$10062209 AB89487 374628.612 7821847
$M1$10062210 AB89487 186692.244 2799412
$M1$10062213 COC_ETL_ID 119531.068 331100426
$M1$10062200 AB63472 118973.316 109881984
$M1$10062204 AB63472 110825.356 94666986
$M1$10062201 AB63472 110797.976 75016994
$M2$100622105813004760047LOAD T23_ETLPROC_ENT 0 6
$M1$10062215 AA37720 0 180
$M1$10062209 AB81670 0 6
Select count(distinct vproc) from dbc.ampusage;
432
select * from dbc.dbcinfo;
AccountName UserName CpuTime DiskIO CpuTimeNorm Vproc VprocType Model
$M1$10062205 CRM_STGC_DEV_ID 0.32 1764 12.7423999023438 0 AMP 2580
$M1$10062205 CRM_STGC_DEV_ID 0.28 1730 11.1495999145508 3 AMP 2580
$M1$10062205 CRM_STGC_DEV_ID 0.304 1736 12.1052799072266 4 AMP 2580
$M1$10062205 CRM_STGC_DEV_ID 0.248 1731 9.87535992431641 7 AMP 2580
$M1$10062205 CRM_STGC_DEV_ID 0.332 1731 13.2202398986816 8 AMP 2580
$M1$10062205 CRM_STGC_DEV_ID 0.284 1712 11.3088799133301 11 AMP 2580
$M1$10062205 CRM_STGC_DEV_ID 0.24 1757 9.55679992675781 12 AMP 2580
$M1$10062205 CRM_STGC_DEV_ID 0.292 1737 11.6274399108887 15 AMP 2580
$M1$10062205 CRM_STGC_DEV_ID 0.268 1753 10.6717599182129 16 AMP 2580
$M1$10062205 CRM_STGC_DEV_ID 0.276 1732 10.9903199157715 19 AMP 2580
select * from dbc.dbcinfo;
InfoKey InfoData
LANGUAGE SUPPORT MODE Standard
RELEASE 12.00.03.03
VERSION 12.00.03.01a
© Oracle Blogs or respective owner
