目錄
前言
因為Vertica數據庫是列式存儲數據，經過壓縮并能夠很大程度上節約存儲空間，還支持大規模地并行處理，非常適合海量數據的分析和處理，所以我們今天這篇文章介紹的是將Oracle數據庫的LPS（Logons Per Sec，每秒登錄數）和TPS（User Transaction Per Sec，每秒用戶事務數）的性能查詢數據導入Vertica數據庫并用Tableau工具連接Vertica進行LPS和TPS的可視化操作。
將Oracle數據庫的LPS&TPS查詢導入Vertica數據庫
- 將Oracle數據庫的LPS&TPS查詢保存為CSV文件
- 將所有CSV文件上傳到Vertica服務器的/home/dbadmin目錄下
- 用vsql客戶端命令連接到Vertica數據庫
- 在Vertica數據庫中創建相關的LPS&TPS表
- 使用COPY命令將CSV文件導入剛創建的表中
將Oracle數據庫的LPS&TPS查詢保存為CSV文件
這里需要說明一下，LPS和TPS分別是Oracle度量的兩個指標，我們使用一個SQL查詢即可完成，但是我們需要從三個維度才能更詳細地闡述LPS&TPS。這三個維度分別是：最近1小時（按每分鐘間隔），最近24小時（按每小時間隔）和最近31天（按每小時間隔）。和上一篇博客文章類似的操作方法，我們在Oracle SQL Developer中以腳本方式查詢出LPS&TPS，然后將其保存為CSV文件。由于文件中的數據行過長，所以不便在這里貼出，現已上傳到我的GitHub，您可以從這里查看，它們分別是：oracle_lps_tps_in_last_1_hour.csv，oracle_lps_tps_in_last_24_hours.csv和oracle_lps_tps_in_last_31_days.csv。三個維度的SQL代碼分別如下所示：
-- Logons Per Sec & User Transaction Per Sec in Last 1 Hour (interval by each minute).
SET FEEDBACK off;
SET SQLFORMAT csv;
SET LINESIZE 200
SET PAGESIZE 200
COLUMN metric_name FORMAT a12
COLUMN snap_date_time FORMAT a20
COLUMN psn FORMAT 999,999.99
ALTER SESSION SET nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
SELECT TO_CHAR(end_time, 'yyyy-mm-dd hh24:mi:ss') snap_date_time -- the group column
, DECODE(metric_name, 'User Transaction Per Sec', 'Transactions', 'Logons Per Sec', 'Logons') metric_name -- the series column
, ROUND(value, 2) psn -- the value column
FROM v$sysmetric_history
WHERE metric_name IN ('User Transaction Per Sec', 'Logons Per Sec')
AND group_id = 2 -- just retrieve the name with "System Metrics Long Duration" in v$metricgroup
-- ORDER BY snap_date_time
-- , metric_name
ORDER BY metric_name
, snap_date_time
;-- Logons Per Sec & User Transaction Per Sec in Last 24 Hours (interval by each hour).
SET FEEDBACK off;
SET SQLFORMAT csv;
SET LINESIZE 200
SET PAGESIZE 200
COLUMN metric_name FORMAT a12
COLUMN snap_date_time FORMAT a20
COLUMN psn FORMAT 999,999.99
ALTER SESSION SET nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
SELECT TO_CHAR(end_time, 'yyyy-mm-dd hh24:mi:ss') snap_date_time -- the group column
, DECODE(metric_name, 'User Transaction Per Sec', 'Transactions', 'Logons Per Sec', 'Logons') metric_name -- the series column
, ROUND(average, 2) psn -- the value column
FROM dba_hist_sysmetric_summary
WHERE metric_name IN ('User Transaction Per Sec', 'Logons Per Sec')
AND end_time >= SYSDATE - 1
-- ORDER BY snap_date_time
-- , metric_name
ORDER BY metric_name
, snap_date_time
;-- Logons Per Sec & User Transaction Per Sec in Last 31 Days (interval by each hour).
SET FEEDBACK off;
SET SQLFORMAT csv;
SET LINESIZE 200
SET PAGESIZE 200
COLUMN metric_name FORMAT a12
COLUMN snap_date_time FORMAT a20
COLUMN psn FORMAT 999,999.99
ALTER SESSION SET nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
SELECT TO_CHAR(end_time, 'yyyy-mm-dd hh24:mi:ss') snap_date_time -- the group column
, DECODE(metric_name, 'User Transaction Per Sec', 'Transactions', 'Logons Per Sec', 'Logons') metric_name -- the series column
, ROUND(average, 2) psn -- the value column
FROM dba_hist_sysmetric_summary
WHERE metric_name IN ('User Transaction Per Sec', 'Logons Per Sec')
AND end_time >= SYSDATE - 30
-- ORDER BY snap_date_time
-- , metric_name
ORDER BY metric_name
, snap_date_time
;將所有CSV文件上傳到Vertica服務器的/home/dbadmin目錄下
這里，省略了上傳的過程和一些授權相關的步驟，最后的上傳結果詳見“<<==”所指向的三個CSV文件。
[dbadmin@test ~]$ ls -lrht
total 112K
drwxr-xr-x 5 dbadmin verticadba 134 Dec 15 14:06 vdb_oracle_perf
-rw-r--r-- 1 dbadmin verticadba 27K Dec 16 18:37 oracle_dbtime.csv
-rw-r--r-- 1 dbadmin verticadba 1.9K Dec 16 18:37 oracle_dbtime_2.csv
-rw-rw-r-- 1 dbadmin verticadba 390 Dec 16 18:44 imp.log
-rw-rw-r-- 1 dbadmin verticadba 393 Dec 16 18:45 imp_2.log
-rw-r--r-- 1 dbadmin verticadba 4.7K Dec 20 09:42 oracle_lps_tps_in_last_1_hour.csv <<==
-rw-r--r-- 1 dbadmin verticadba 2.0K Dec 20 09:45 oracle_lps_tps_in_last_24_hours.csv <<==
-rw-r--r-- 1 dbadmin verticadba 57K Dec 20 09:47 oracle_lps_tps_in_last_31_days.csv <<==用vsql客戶端命令連接到Vertica數據庫
這次我們不用DBeaver客戶端工具去連接Vertica數據庫了，哈哈！因為在Vertica中自帶了一個客戶端，vsql的Linux命令行工具（類似于Oracle的SQL*Plus），而且使用它在命令行接口上更加方便和快捷。連接過程詳見下面的操作步驟：
[root@test ~]# su - dbadmin
[dbadmin@test ~]$ /opt/vertica/bin/vsql -h 127.0.0.1 vdb_oracle_perf dbadmin
Password:
Welcome to vsql, the Vertica Analytic Database interactive terminal.
Type: \h or \? for help with vsql commands
\g or terminate with semicolon to execute query
\q to quit
vdb_oracle_perf=> 在Vertica數據庫中創建相關的LPS&TPS表
在vsql下分別創建三個表，表名依次為：oracle_lps_tps_in_last_1_hour，oracle_lps_tps_in_last_24_hours和oracle_lps_tps_in_last_31_days。其操作步驟分別如下所示：
vdb_oracle_perf=> CREATE TABLE public.oracle_lps_tps_in_last_1_hour
vdb_oracle_perf=> (snap_date_time TIMESTAMP,
vdb_oracle_perf=> metric_name VARCHAR2(20),
vdb_oracle_perf=> psn NUMBER(8,2)
vdb_oracle_perf=> );
CREATE TABLE
vdb_oracle_perf=>
vdb_oracle_perf=> CREATE TABLE public.oracle_lps_tps_in_last_24_hours
vdb_oracle_perf=> (snap_date_time TIMESTAMP,
vdb_oracle_perf=> metric_name VARCHAR2(20),
vdb_oracle_perf=> psn NUMBER(8,2)
vdb_oracle_perf=> );
CREATE TABLE
vdb_oracle_perf=>
vdb_oracle_perf=> CREATE TABLE public.oracle_lps_tps_in_last_31_days
vdb_oracle_perf=> (snap_date_time TIMESTAMP,
vdb_oracle_perf=> metric_name VARCHAR2(20),
vdb_oracle_perf=> psn NUMBER(8,2)
vdb_oracle_perf=> );
CREATE TABLE
vdb_oracle_perf=> 使用COPY命令將CSV文件導入剛創建的表中
導入過程請看下面的具體步驟：
vdb_oracle_perf=>
vdb_oracle_perf=> COPY public.oracle_lps_tps_in_last_1_hour FROM '/home/dbadmin/oracle_lps_tps_in_last_1_hour.csv' EXCEPTIONS '/home/dbadmin/imp_lps_tps_1.log' DELIMITER AS ',';
NOTICE 7850: In a multi-threaded load, rejected record data may be written to additional files
HINT: Exceptions may be written to files [/home/dbadmin/imp_lps_tps_1.log], [/home/dbadmin/imp_lps_tps_1.log.1], etc
Rows Loaded
-------------
122
(1 row)
vdb_oracle_perf=> COPY public.oracle_lps_tps_in_last_24_hours FROM '/home/dbadmin/oracle_lps_tps_in_last_24_hours.csv' EXCEPTIONS '/home/dbadmin/imp_lps_tps_2.log' DELIMITER AS ',';
NOTICE 7850: In a multi-threaded load, rejected record data may be written to additional files
HINT: Exceptions may be written to files [/home/dbadmin/imp_lps_tps_2.log], [/home/dbadmin/imp_lps_tps_2.log.1], etc
Rows Loaded
-------------
48
(1 row)
vdb_oracle_perf=> COPY public.oracle_lps_tps_in_last_31_days FROM '/home/dbadmin/oracle_lps_tps_in_last_31_days.csv' EXCEPTIONS '/home/dbadmin/imp_lps_tps_3.log' DELIMITER AS ',';
NOTICE 7850: In a multi-threaded load, rejected record data may be written to additional files
HINT: Exceptions may be written to files [/home/dbadmin/imp_lps_tps_3.log], [/home/dbadmin/imp_lps_tps_3.log.1], etc
Rows Loaded
-------------
1440
(1 row)
vdb_oracle_perf=> 用Tableau可視化Vertica數據庫的表
在Tableau中連接Vertica數據庫并選擇schema為public，將表oracle_lps_tps_in_last_1_hour用鼠標拖動到指定位置，然后轉到工作表，準備下一步的可視化操作。對應的兩個屏幕截圖分別是：
在工作表區域上，我們把度量名稱Snap Date Time用鼠標拖動到標簽名為“列”的標簽框里（默認是年，將其展開到秒），把度量值Psn拖動到標簽名為“行”的標簽框中，再將度量名稱Metric Name拖動到標簽名為“頁面”的標簽里。與此同時，我們會看到在整個頁面的右邊區域上新增了一個Metric Name的標簽。不幸的是，這兩個度量“Logons”和“Transactions”不能同時出現在一個“折線圖”中。我們需要在標簽Metric Name上選擇Logons或Transactions才能出現與其對應的折線圖。詳見如下兩圖的紅框標明位置：
回頭想想，造成這個問題的原因是表oracle_lps_tps_in_last_1_hour的列metric name的值Logons和Transactions在同一列，也就是其對應的度量值psn均在同一列，所以這兩個度量各稱無法出現在同一個折線圖上。因此我們需要把metric name的兩個值所在的行轉變為兩個列，在這兩個列中分別保存各自的度量值psn，這個需求我們稱之為行轉列操作。接著在Tableau中的Vertica數據庫表oracle_lps_tps_in_last_1_hour才會有兩個不同的度量。到這里，我們就暫先放棄了對表oracle_lps_tps_in_last_24_hours和oracle_lps_tps_in_last_31_days的可視化操作。
對原始SQL進行行轉列改造，再重復執行前兩步
1. 三個維度的行轉列部分SQL代碼如下所示（由于代碼過多，這里只貼出行轉列的關鍵部分，其余內容和前面展示的SQL代碼完全一致）：
-- Converting rows TO columns Based on Logons Per Sec & User Transaction Per Sec in Last 1 Hour (interval by each minute).
......
WITH lps_tps_in_last_1_hour AS
(
......
)
SELECT *
FROM lps_tps_in_last_1_hour
PIVOT ( MAX(psn)
FOR metric_name IN
( 'Logons' AS "Logons"
, 'Transactions' AS "Transactions"
)
)
ORDER BY snap_date_time
;-- Converting rows TO columns Based on Logons Per Sec & User Transaction Per Sec in Last 24 Hours (interval by each hour).
......
WITH lps_tps_in_last_24_hours AS
(
......
)
SELECT *
FROM lps_tps_in_last_24_hours
PIVOT ( MAX(psn)
FOR metric_name IN
( 'Logons' AS "Logons"
, 'Transactions' AS "Transactions"
)
)
ORDER BY snap_date_time
;-- Converting rows TO columsn Based on Logons Per Sec & User Transaction Per Sec in Last 31 Days (interval by each hour).
......
WITH lps_tps_in_last_31_days AS
(
......
)
SELECT *
FROM lps_tps_in_last_31_days
PIVOT ( MAX(psn)
FOR metric_name IN
( 'Logons' AS "Logons"
, 'Transactions' AS "Transactions"
)
)
ORDER BY snap_date_time
;2. 同樣，將上面的SQL代碼分別在Oracle SQL Developer里以腳本方式運行并保存為CSV文件，您可以從這里查看，它們分別是：crbc_oracle_lps_tps_in_last_1_hour.csv，crbc_oracle_lps_tps_in_last_24_hours.csv和crbc_oracle_lps_tps_in_last_31_days.csv。
3. 將三個CSV文件上傳到Vertica數據庫服務器的/home/dbadmin/目錄下，見“<<==”標明的位置。
[dbadmin@test ~]$ ls -lrht
total 156K
drwxr-xr-x 5 dbadmin verticadba 134 Dec 15 14:06 vdb_oracle_perf
-rw-r--r-- 1 dbadmin verticadba 27K Dec 16 18:37 oracle_dbtime.csv
-rw-r--r-- 1 dbadmin verticadba 1.9K Dec 16 18:37 oracle_dbtime_2.csv
-rw-rw-r-- 1 dbadmin verticadba 390 Dec 16 18:44 imp.log
-rw-rw-r-- 1 dbadmin verticadba 393 Dec 16 18:45 imp_2.log
-rw-r--r-- 1 dbadmin verticadba 4.7K Dec 20 09:42 oracle_lps_tps_in_last_1_hour.csv
-rw-r--r-- 1 dbadmin verticadba 2.0K Dec 20 09:45 oracle_lps_tps_in_last_24_hours.csv
-rw-r--r-- 1 dbadmin verticadba 57K Dec 20 09:47 oracle_lps_tps_in_last_31_days.csv
-rw-rw-r-- 1 dbadmin verticadba 425 Dec 20 10:06 imp_lps_tps_1.log
-rw-rw-r-- 1 dbadmin verticadba 428 Dec 20 10:06 imp_lps_tps_2.log
-rw-rw-r-- 1 dbadmin verticadba 428 Dec 20 10:07 imp_lps_tps_3.log
-rw-r--r-- 1 dbadmin verticadba 1.9K Dec 20 14:51 crbc_oracle_lps_tps_in_last_1_hour.csv <<==
-rw-r--r-- 1 dbadmin verticadba 831 Dec 20 14:52 crbc_oracle_lps_tps_in_last_24_hours.csv <<==
-rw-r--r-- 1 dbadmin verticadba 24K Dec 20 14:53 crbc_oracle_lps_tps_in_last_31_days.csv <<==4. 用vsql連接到Vertica數據庫創建新表（表名在原表的基礎上增加crbc前綴）并用COPY命令導入CSV文件中的數據。
vdb_oracle_perf=>
vdb_oracle_perf=> CREATE TABLE public.crbc_oracle_lps_tps_in_last_1_hour
vdb_oracle_perf=> (snap_date_time TIMESTAMP,
vdb_oracle_perf=> logons NUMBER(8,2),
vdb_oracle_perf=> transactions NUMBER(8,2)
vdb_oracle_perf=> );
CREATE TABLE
vdb_oracle_perf=>
vdb_oracle_perf=> CREATE TABLE public.crbc_oracle_lps_tps_in_last_24_hours
vdb_oracle_perf=> (snap_date_time TIMESTAMP,
vdb_oracle_perf=> logons NUMBER(8,2),
vdb_oracle_perf=> transactions NUMBER(8,2)
vdb_oracle_perf=> );
CREATE TABLE
vdb_oracle_perf=>
vdb_oracle_perf=> CREATE TABLE public.crbc_oracle_lps_tps_in_last_31_days
vdb_oracle_perf=> (snap_date_time TIMESTAMP,
vdb_oracle_perf=> logons NUMBER(8,2),
vdb_oracle_perf=> transactions NUMBER(8,2)
vdb_oracle_perf=> );
CREATE TABLE
vdb_oracle_perf=>
vdb_oracle_perf=> COPY public.crbc_oracle_lps_tps_in_last_1_hour FROM '/home/dbadmin/crbc_oracle_lps_tps_in_last_1_hour.csv' EXCEPTIONS '/home/dbadmin/imp_crbc_lps_tps_1.log' DELIMITER AS ',';
NOTICE 7850: In a multi-threaded load, rejected record data may be written to additional files
HINT: Exceptions may be written to files [/home/dbadmin/imp_crbc_lps_tps_1.log], [/home/dbadmin/imp_crbc_lps_tps_1.log.1], etc
Rows Loaded
-------------
61
(1 row)
vdb_oracle_perf=>
vdb_oracle_perf=> COPY public.crbc_oracle_lps_tps_in_last_24_hours FROM '/home/dbadmin/crbc_oracle_lps_tps_in_last_24_hours.csv' EXCEPTIONS '/home/dbadmin/imp_crbc_lps_tps_2.log' DELIMITER AS ',';
NOTICE 7850: In a multi-threaded load, rejected record data may be written to additional files
HINT: Exceptions may be written to files [/home/dbadmin/imp_crbc_lps_tps_2.log], [/home/dbadmin/imp_crbc_lps_tps_2.log.1], etc
Rows Loaded
-------------
24
(1 row)
vdb_oracle_perf=>
vdb_oracle_perf=> COPY public.crbc_oracle_lps_tps_in_last_31_days FROM '/home/dbadmin/crbc_oracle_lps_tps_in_last_31_days.csv' EXCEPTIONS '/home/dbadmin/imp_crbc_lps_tps_3.log' DELIMITER AS ',';
NOTICE 7850: In a multi-threaded load, rejected record data may be written to additional files
HINT: Exceptions may be written to files [/home/dbadmin/imp_crbc_lps_tps_3.log], [/home/dbadmin/imp_crbc_lps_tps_3.log.1], etc
Rows Loaded
-------------
720
(1 row)
vdb_oracle_perf=> 5. 在Tableau中連接Vertica數據庫并可視化新表
這次，我們終于可以將兩個度量Logons和Transactions在同一個折線圖中展示出來了，其中對新表crbc_oracle_lps_tps_in_last_1_hour的可視化操作依次見如下五個屏幕截圖：
表crbc_oracle_lps_tps_in_last_24_hours和crbc_oracle_lps_tps_in_last_31_days的可視化結果分別如下所示：
以上就是用Tableau可視化Vertica數據庫之“LPS&TPS”篇的所有內容。另外，您也可以在acquire_lps_union_tps.sql的第32-64行、第66-98行、第100-132行、第217-238行、第344-365行和第367-388行找到我前面提到的所有SQL源代碼。歡迎業界各位朋友在文章底部的評論區提出您的反饋意見。
參考內容
「喜歡這篇文章，您的關注和贊賞是給作者最好的鼓勵」
關注作者
【版權聲明】本文為墨天輪用戶原創內容，轉載時必須標注文章的來源（墨天輪），文章鏈接，文章作者等基本信息，否則作者和墨天輪有權追究責任。如果您發現墨天輪中有涉嫌抄襲或者侵權的內容，歡迎發送郵件至：contact@modb.pro進行舉報，并提供相關證據，一經查實，墨天輪將立刻刪除相關內容。
