'1. IT Story/Scripts' 카테고리의 글 목록 (2 Page)

Oracle Awr Generator

1. IT Story/Scripts 2019. 4. 22. 21:04

REM AWR-Generator.sql: Script for creating multiple consecutive Oracle AWR Reports

REM

set feedback off

set echo off

set verify off

set timing off

-- Set AWR_FORMAT to "text" or "html"

define AWR_FORMAT = 'text'

define DEFAULT_OUTPUT_FILENAME = 'awr-generate.sql'

define NO_ADDM = 0

-- Get values for dbid and inst_num before calling awrinput.sql

set echo off heading on

column inst_num heading "Inst Num" new_value inst_num format 99999;

column inst_name heading "Instance" new_value inst_name format a12;

column db_name heading "DB Name" new_value db_name format a12;

column dbid heading "DB Id" new_value dbid format 9999999999 just c;

prompt

prompt Current Instance

prompt ~~~~~~~~~~~~~~~~

select d.dbid dbid

, d.name db_name

, i.instance_number inst_num

, i.instance_name inst_name

from v$database d,

v$instance i;

-- Call the Oracle common input script to setup start and end snap ids

@@?/rdbms/admin/awrinput.sql

-- Ask the user for the name of the output script

prompt

prompt Specify output script name

prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~

prompt This script produces output in the form of another SQL script

prompt The output script contains the commands to generate the AWR Reports

prompt

prompt The default output file name is &DEFAULT_OUTPUT_FILENAME

prompt To accept this name, press <return> to continue, otherwise enter an alternative

prompt

set heading off

column outfile_name new_value outfile_name noprint;

select 'Using the output file name ' || nvl('&&outfile_name','&DEFAULT_OUTPUT_FILENAME')

, nvl('&&outfile_name','&DEFAULT_OUTPUT_FILENAME') outfile_name

from sys.dual;

set linesize 800

set serverout on

set termout off

-- spool to outputfile

spool &outfile_name

-- write script header comments

prompt REM Temporary script created by awr-generator.sql

prompt REM Used to create multiple AWR reports between two snapshots

select 'REM Created by user '||user||' on '||sys_context('userenv', 'host')||' at '||to_char(sysdate, 'DD-MON-YYYY HH24:MI') from dual;

set heading on

-- Begin iterating through snapshots and generating reports

DECLARE

c_dbid CONSTANT NUMBER := :dbid;

c_inst_num CONSTANT NUMBER := :inst_num;

c_start_snap_id CONSTANT NUMBER := :bid;

c_end_snap_id CONSTANT NUMBER := :eid;

c_awr_options CONSTANT NUMBER := &&NO_ADDM;

c_report_type CONSTANT CHAR(4):= '&&AWR_FORMAT';

v_awr_reportname VARCHAR2(100);

v_report_suffix CHAR(5);

CURSOR c_snapshots IS

select inst_num, start_snap_id, end_snap_id

from (

select s.instance_number as inst_num,

s.snap_id as start_snap_id,

lead(s.snap_id,1,null) over (partition by s.instance_number order by s.snap_id) as end_snap_id

from dba_hist_snapshot s

where s.dbid = c_dbid

and s.snap_id >= c_start_snap_id

and s.snap_id <= c_end_snap_id

)

where end_snap_id is not null

order by inst_num, start_snap_id;

BEGIN

dbms_output.put_line('');

dbms_output.put_line('prompt Beginning AWR Generation...');

dbms_output.put_line('set heading off feedback off lines 800 pages 5000 trimspool on trimout on');

-- Determine report type (html or text)

IF c_report_type = 'html' THEN

v_report_suffix := '.html';

ELSE

v_report_suffix := '.txt';

END IF;

-- Iterate through snapshots

FOR cr_snapshot in c_snapshots

LOOP

-- Construct filename for AWR report

v_awr_reportname := 'awrrpt_'||cr_snapshot.inst_num||'_'||cr_snapshot.start_snap_id||'_'||cr_snapshot.end_snap_id||v_report_suffix;

dbms_output.put_line('prompt Creating AWR Report '||v_awr_reportname

||' for instance number '||cr_snapshot.inst_num||' snapshots '||cr_snapshot.start_snap_id||' to '||cr_snapshot.end_snap_id);

dbms_output.put_line('prompt');

-- Disable terminal output to stop AWR text appearing on screen

dbms_output.put_line('set termout off');

-- Set spool to create AWR report file

dbms_output.put_line('spool '||v_awr_reportname);

-- call the table function to generate the report

IF c_report_type = 'html' THEN

dbms_output.put_line('select output from table(dbms_workload_repository.awr_report_html('

||c_dbid||','||cr_snapshot.inst_num||','||cr_snapshot.start_snap_id||','||cr_snapshot.end_snap_id||','||c_awr_options||'));');

ELSE

dbms_output.put_line('select output from table(dbms_workload_repository.awr_report_text('

||c_dbid||','||cr_snapshot.inst_num||','||cr_snapshot.start_snap_id||','||cr_snapshot.end_snap_id||','||c_awr_options||'));');

END IF;

dbms_output.put_line('spool off');

-- Enable terminal output having finished generating AWR report

dbms_output.put_line('set termout on');

END LOOP;

dbms_output.put_line('set heading on feedback 6 lines 100 pages 45');

dbms_output.put_line('prompt AWR Generation Complete');

-- EXCEPTION HANDLER?

END;

/

spool off

set termout on

prompt

prompt Script written to &outfile_name - check and run in order to generate AWR reports...

prompt

--clear columns sql

undefine outfile_name

undefine AWR_FORMAT

undefine DEFAULT_OUTPUT_FILENAME

undefine NO_ADDM

undefine OUTFILE_NAME

set feedback 6 verify on lines 100 pages 45

저작자표시 동일조건

'1. IT Story > Scripts' 카테고리의 다른 글

Oracle 실시간 Session 조회 스크립트 (0)	2019.04.29
Oracle ASH TOP Activity (0)	2019.04.28
Oracle Oradebug Ashdump를 통한 Trace 생성 스크립트 (0)	2019.04.27
PostgreSQL Vaccum(XID) 관리 스크립트 (0)	2019.04.25
PostgreSQL Backup/Recovery 요약 스크립트 (0)	2019.04.18
Oracle Character Set Scanner (CSSCAN)변경 대상 추출 스크립트 (0)	2019.04.17
Oracle 테스트 데이터 생성 스크립트 (0)	2019.04.16
Oracle SQL SysMetric 조회 스크립트 (0)	2019.04.13

운명을바꾸는자

IT와 함께 살아가는 삶

,

PostgreSQL Backup/Recovery 요약 스크립트

1. IT Story/Scripts 2019. 4. 18. 13:17

1) Hot Backup, Cold Backup
- Hot Backup에 경우에 오라클과 동일하게 Begin, End를 찍어 그사이에 OS에서 복사를 통하여 백업하는 형태로 수행.
- Cold Backup에 경우에 오라클과 동일하게 DB를 내려놓고 OS에서 복사를 통하여 백업을 하는 형태로 수행.

2) Pg_dump, Pg_dumpall Backup
- pg_dump 백업
$ pg_dump -U username -Fp dbname  >  filename.txt
$ pg_dump -U username dbname -f  filename.txt
$ pg_dump -Fp -U username dbname -f  filename.txt

--Cluster Level Dump
$ pg_dumpall -p portnumber > filename.txt

- restore
--For restoring plain text format
$ psql -U username -f filename db_name
$ psql -C -U username -f filename db_name

--For restoring custom format
$ pg_restore -Fc -U username -d dbname filename.bak
$ pg_restore -Fc -C -U username -d dbname filename.bak

--For restoring tar format
$ pg_restore -U username -d dbname filename.tar
$ pg_restore -C -U username -d dbname filename.tar

--Cluster Level Dump
$ psql -f filename.txt

저작자표시 동일조건

'1. IT Story > Scripts' 카테고리의 다른 글

Oracle ASH TOP Activity (0)	2019.04.28
Oracle Oradebug Ashdump를 통한 Trace 생성 스크립트 (0)	2019.04.27
PostgreSQL Vaccum(XID) 관리 스크립트 (0)	2019.04.25
Oracle Awr Generator (0)	2019.04.22
Oracle Character Set Scanner (CSSCAN)변경 대상 추출 스크립트 (0)	2019.04.17
Oracle 테스트 데이터 생성 스크립트 (0)	2019.04.16
Oracle SQL SysMetric 조회 스크립트 (0)	2019.04.13
Oracle Session UNDO, TEMP 사용 모니터링 (0)	2019.04.12

운명을바꾸는자

IT와 함께 살아가는 삶

,

Oracle Character Set Scanner (CSSCAN)변경 대상 추출 스크립트

1. IT Story/Scripts 2019. 4. 17. 21:35

CSSCAN이 설치된 상태에서 아래 쿼리문을 통하여 테이블별 타입 수정 스크립트를 일괄 추출할 수 있다.

SQL> @?/rdbms/admin/csminst.sql

SELECT 'alter table '

|| owner

|| '.'

|| table_name

|| ' modify ('

|| column_name

|| ' '

|| data_type

|| '('

|| CEIL (LENGTH / 2 * 3)

|| '))'

FROM (SELECT d.name AS owner,

b.name AS table_name,

c.name AS column_name,

DECODE (

c.type#,

1, DECODE (c.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'),

2, DECODE (

c.scale,

NULL, DECODE (c.precision#, NULL, 'NUMBER', 'FLOAT'),

'NUMBER'),

8, 'LONG',

9, DECODE (c.charsetform, 2, 'NCHAR VARYING', 'VARCHAR'),

12, 'DATE',

23, 'RAW',

24, 'LONG RAW',

69, 'ROWID',

96, DECODE (c.charsetform, 2, 'NCHAR', 'CHAR'),

105, 'MLSLABEL',

106, 'MLSLABEL',

112, DECODE (c.charsetform, 2, 'NCLOB', 'CLOB'),

113, 'BLOB',

114, 'BFILE',

115, 'CFILE',

178, 'TIME(' || c.scale || ')',

179, 'TIME(' || c.scale || ')' || ' WITH TIME ZONE',

180, 'TIMESTAMP(' || c.scale || ')',

181, 'TIMESTAMP(' || c.scale || ')' || ' WITH TIME ZONE',

231, 'TIMESTAMP('

|| c.scale

|| ')'

|| ' WITH LOCAL TIME ZONE',

182, 'INTERVAL YEAR(' || c.precision# || ') TO MONTH',

183, 'INTERVAL DAY('

|| c.precision#

|| ') TO SECOND('

|| c.scale

|| ')',

208, 'UROWID',

'UNDEFINED')

AS data_type,

c.LENGTH AS LENGTH

FROM (select distinct usr#,obj#,col# from csmig.CSM$ERRORS) a,

sys.col$ c,

sys.obj$ b,

sys.user$ d

WHERE A.OBJ# = b.obj#

AND a.col# = c.col#

AND c.obj# = B.OBJ#

and b.owner#=d.user#

AND a.usr# = d.user#);

저작자표시 동일조건

'1. IT Story > Scripts' 카테고리의 다른 글

Oracle Oradebug Ashdump를 통한 Trace 생성 스크립트 (0)	2019.04.27
PostgreSQL Vaccum(XID) 관리 스크립트 (0)	2019.04.25
Oracle Awr Generator (0)	2019.04.22
PostgreSQL Backup/Recovery 요약 스크립트 (0)	2019.04.18
Oracle 테스트 데이터 생성 스크립트 (0)	2019.04.16
Oracle SQL SysMetric 조회 스크립트 (0)	2019.04.13
Oracle Session UNDO, TEMP 사용 모니터링 (0)	2019.04.12
PostgreSQL Top SQL 추출 스크립트 (0)	2019.04.11

운명을바꾸는자

IT와 함께 살아가는 삶

,

Oracle 테스트 데이터 생성 스크립트

1. IT Story/Scripts 2019. 4. 16. 21:38

DROP TABLE EMP;
DROP TABLE DEPT;
DROP TABLE BONUS;
DROP TABLE SALGRADE;
DROP TABLE DUMMY;
DROP TABLE ITEM;
DROP TABLE ORD;
DROP TABLE PRICE;
DROP TABLE PRODUCT;
DROP TABLE CUSTOMER;
DROP VIEW SALES;
DROP SEQUENCE ORDID;
DROP SEQUENCE CUSTID;
DROP SEQUENCE PRODID;

CREATE TABLE DEPT (
DEPTNO              NUMBER(2) NOT NULL,
DNAME               VARCHAR2(14),
LOC                 VARCHAR2(13),
CONSTRAINT DEPT_PRIMARY_KEY PRIMARY KEY (DEPTNO));

INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');

CREATE TABLE EMP (
EMPNO               NUMBER(4) NOT NULL,
ENAME               VARCHAR2(10),
JOB                 VARCHAR2(9),
MGR                 NUMBER(4) CONSTRAINT EMP_SELF_KEY REFERENCES EMP (EMPNO),
HIREDATE            DATE,
SAL                 NUMBER(7,2),
COMM                NUMBER(7,2),
DEPTNO              NUMBER(2) NOT NULL,
CONSTRAINT EMP_FOREIGN_KEY FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO),
CONSTRAINT EMP_PRIMARY_KEY PRIMARY KEY (EMPNO));

INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,'17-NOV-81',5000,NULL,10);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,'1-MAY-81',2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,'9-JUN-81',2450,NULL,10);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,'2-APR-81',2975,NULL,20);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,'3-DEC-81',3000,NULL,20);
INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,'22-FEB-81',1250,500,30);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,'28-SEP-81',1250,1400,30);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,'09-DEC-82',3000,NULL,20);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,'8-SEP-81',1500,0,30);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,'12-JAN-83',1100,NULL,20);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,'3-DEC-81',950,NULL,30);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,'23-JAN-82',1300,NULL,10);

CREATE TABLE BONUS (
ENAME               VARCHAR2(10),
JOB                 VARCHAR2(9),
SAL                 NUMBER,
COMM                NUMBER);

CREATE TABLE SALGRADE (
GRADE               NUMBER,
LOSAL               NUMBER,
HISAL               NUMBER);

INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);

CREATE TABLE DUMMY (
DUMMY               NUMBER );

INSERT INTO DUMMY VALUES (0);

CREATE TABLE CUSTOMER (
CUSTID              NUMBER (6) NOT NULL,
NAME                VARCHAR2 (45),
ADDRESS             VARCHAR2 (40),
CITY                VARCHAR2 (30),
STATE               VARCHAR2 (2),
ZIP                 VARCHAR2 (9),
AREA                NUMBER (3),
PHONE               VARCHAR2 (9),
REPID               NUMBER (4) NOT NULL,
CREDITLIMIT         NUMBER (9,2),
COMMENTS            LONG,
CONSTRAINT CUSTOMER_PRIMARY_KEY PRIMARY KEY (CUSTID),
CONSTRAINT CUSTID_ZERO CHECK (CUSTID > 0));

INSERT INTO CUSTOMER (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT,
  CITY, AREA, ADDRESS, COMMENTS)
VALUES ('96711', 'CA', '7844', '598-6609',
'JOCKSPORTS',
'100', '5000', 'BELMONT', '415', '345 VIEWRIDGE',
'Very friendly people to work with -- sales rep likes to be called Mike.');

INSERT INTO CUSTOMER (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT,
  CITY, AREA, ADDRESS, COMMENTS)
VALUES ('94061', 'CA', '7521', '368-1223',
'TKB SPORT SHOP',
'101', '10000', 'REDWOOD CITY', '415', '490 BOLI RD.',
'Rep called 5/8 about change in order - contact shipping.');

INSERT INTO CUSTOMER (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT,
  CITY, AREA, ADDRESS, COMMENTS)
VALUES ('95133', 'CA', '7654', '644-3341',
'VOLLYRITE',
'102', '7000', 'BURLINGAME', '415', '9722 HAMILTON',
'Company doing heavy promotion beginning 10/89. Prepare for large orders during
winter.');

INSERT INTO CUSTOMER (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT,
  CITY, AREA, ADDRESS, COMMENTS)
VALUES ('97544', 'CA', '7521', '677-9312',
'JUST TENNIS',
'103', '3000', 'BURLINGAME', '415', 'HILLVIEW MALL',
'Contact rep about new line of tennis rackets.');

INSERT INTO CUSTOMER (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT,
  CITY, AREA, ADDRESS, COMMENTS)
VALUES ('93301', 'CA', '7499', '996-2323',
'EVERY MOUNTAIN',
'104', '10000', 'CUPERTINO', '408', '574 SURRY RD.',
'Customer with high market share (23%) due to aggressive advertising.');

INSERT INTO CUSTOMER (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT,
  CITY, AREA, ADDRESS, COMMENTS)
VALUES ('91003', 'CA', '7844', '376-9966',
'K + T SPORTS',
'105', '5000', 'SANTA CLARA', '408', '3476 EL PASEO',
'Tends to order large amounts of merchandise at once. Accounting is considering
raising their credit limit. Usually pays on time.');

INSERT INTO CUSTOMER (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT,
  CITY, AREA, ADDRESS, COMMENTS)
VALUES ('94301', 'CA', '7521', '364-9777',
'SHAPE UP',
'106', '6000', 'PALO ALTO', '415', '908 SEQUOIA',
'Support intensive. Orders small amounts (< 800) of merchandise at a time.');

INSERT INTO CUSTOMER (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT,
  CITY, AREA, ADDRESS, COMMENTS)
VALUES ('93301', 'CA', '7499', '967-4398',
'WOMENS SPORTS',
'107', '10000', 'SUNNYVALE', '408', 'VALCO VILLAGE',
'First sporting goods store geared exclusively towards women. Unusual promotion
al style and very willing to take chances towards new products!');

INSERT INTO CUSTOMER (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT,
  CITY, AREA, ADDRESS, COMMENTS)
VALUES ('55649', 'MN', '7844', '566-9123',
'NORTH WOODS HEALTH AND FITNESS SUPPLY CENTER',
'108', '8000', 'HIBBING', '612', '98 LONE PINE WAY', '');

CREATE TABLE ORD  (
ORDID               NUMBER (4) NOT NULL,
ORDERDATE           DATE,
COMMPLAN            VARCHAR2 (1),
CUSTID              NUMBER (6) NOT NULL,
SHIPDATE            DATE,
TOTAL               NUMBER (8,2) CONSTRAINT TOTAL_ZERO CHECK (TOTAL >= 0),
CONSTRAINT ORD_FOREIGN_KEY FOREIGN KEY (CUSTID) REFERENCES CUSTOMER (CUSTID),
CONSTRAINT ORD_PRIMARY_KEY PRIMARY KEY (ORDID));

CREATE TABLE ITEM  (
ORDID               NUMBER (4) NOT NULL,
ITEMID              NUMBER (4) NOT NULL,
PRODID              NUMBER (6),
ACTUALPRICE         NUMBER (8,2),
QTY                 NUMBER (8),
ITEMTOT             NUMBER (8,2),
CONSTRAINT ITEM_FOREIGN_KEY FOREIGN KEY (ORDID) REFERENCES ORD (ORDID),
CONSTRAINT ITEM_PRIMARY_KEY PRIMARY KEY (ORDID,ITEMID));

CREATE TABLE PRODUCT (
PRODID              NUMBER (6) CONSTRAINT PRODUCT_PRIMARY_KEY PRIMARY KEY,
DESCRIP             VARCHAR2 (30));

CREATE TABLE PRICE (
PRODID              NUMBER (6) NOT NULL,
STDPRICE            NUMBER (8,2),
MINPRICE            NUMBER (8,2),
STARTDATE           DATE,
ENDDATE             DATE);

INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN)
VALUES ('101.4', '08-JAN-87', '610', '07-JAN-87', '101', 'A');

INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN)
VALUES ('45', '11-JAN-87', '611', '11-JAN-87', '102', 'B');

INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN)
VALUES ('5860', '20-JAN-87', '612', '15-JAN-87', '104', 'C');

INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN)
VALUES ('2.4', '30-MAY-86', '601', '01-MAY-86', '106', 'A');

INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN)
VALUES ('56', '20-JUN-86', '602', '05-JUN-86', '102', 'B');

INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN)
VALUES ('698', '30-JUN-86', '604', '15-JUN-86', '106', 'A');

INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN)
VALUES ('8324', '30-JUL-86', '605', '14-JUL-86', '106', 'A');

INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN)
VALUES ('3.4', '30-JUL-86', '606', '14-JUL-86', '100', 'A');

INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN)
VALUES ('97.5', '15-AUG-86', '609', '01-AUG-86', '100', 'B');

INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN)
VALUES ('5.6', '18-JUL-86', '607', '18-JUL-86', '104', 'C');

INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN)
VALUES ('35.2', '25-JUL-86', '608', '25-JUL-86', '104', 'C');

INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN)
VALUES ('224', '05-JUN-86', '603', '05-JUN-86', '102', '');

INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN)
VALUES ('4450', '12-MAR-87', '620', '12-MAR-87', '100', '');

INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN)
VALUES ('6400', '01-FEB-87', '613', '01-FEB-87', '108', '');

INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN)
VALUES ('23940', '05-FEB-87', '614', '01-FEB-87', '102', '');

INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN)
VALUES ('764', '10-FEB-87', '616', '03-FEB-87', '103', '');

INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN)
VALUES ('1260', '04-FEB-87', '619', '22-FEB-87', '104', '');

INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN)
VALUES ('46370', '03-MAR-87', '617', '05-FEB-87', '105', '');

INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN)
VALUES ('710', '06-FEB-87', '615', '01-FEB-87', '107', '');

INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN)
VALUES ('3510.5', '06-MAR-87', '618', '15-FEB-87', '102', 'A');

INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN)
VALUES ('730', '01-JAN-87', '621', '15-MAR-87', '100', 'A');

INSERT INTO ITEM (QTY, PRODID, ORDID, ITEMTOT, ITEMID, ACTUALPRICE)
VALUES ('1', '100890', '610', '58', '3', '58');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ( '1', '100861', '611', '45', '1', '45');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ( '100', '100860', '612', '3000', '1', '30');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ( '1', '200376', '601', '2.4', '1', '2.4');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ( '20', '100870', '602', '56', '1', '2.8');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ( '3', '100890', '604', '174', '1', '58');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ( '2', '100861', '604', '84', '2', '42');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ( '10', '100860', '604', '440', '3', '44');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ( '4', '100860', '603', '224', '2', '56');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ( '1', '100860', '610', '35', '1', '35');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ( '3', '100870', '610', '8.4', '2', '2.8');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ( '200', '200376', '613', '440', '4', '2.2');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ( '444', '100860', '614', '15540', '1', '35');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ( '1000', '100870', '614', '2800', '2', '2.8');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ( '20', '100861', '612', '810', '2', '40.5');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ('150', '101863', '612', '1500', '3', '10');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ('10', '100860', '620', '350', '1', '35');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ('1000', '200376', '620', '2400', '2', '2.4');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ('500', '102130', '620', '1700', '3', '3.4');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ( '100', '100871', '613', '560', '1', '5.6');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ('200', '101860', '613', '4800', '2', '24');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ('150', '200380', '613', '600', '3', '4');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ('100', '102130', '619', '340', '3', '3.4');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ('50', '100860', '617', '1750', '1', '35');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ('100', '100861', '617', '4500', '2', '45');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ('1000', '100871', '614', '5600', '3', '5.6');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ('10', '100861', '616', '450', '1', '45');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ('50', '100870', '616', '140', '2', '2.8');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ('2', '100890', '616', '116', '3', '58');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ('10', '102130', '616', '34', '4', '3.4');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ('10', '200376' , '616', '24', '5', '2.4');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ('100', '200380', '619', '400', '1', '4');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ('100', '200376', '619', '240', '2', '2.4');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ('4', '100861', '615', '180', '1', '45');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ('1', '100871', '607', '5.6', '1', '5.6');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ('100', '100870', '615', '280', '2', '2.8');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ('500', '100870', '617', '1400', '3', '2.8');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ('500', '100871', '617', '2800', '4', '5.6');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ('500', '100890', '617', '29000', '5', '58');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ('100', '101860', '617', '2400', '6', '24');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ('200', '101863', '617', '2500', '7', '12.5');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ('100', '102130', '617', '340', '8', '3.4');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ('200', '200376', '617', '480', '9', '2.4');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ('300', '200380', '617', '1200', '10', '4');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ('5', '100870', '609', '12.5', '2', '2.5');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ('1', '100890', '609', '50', '3', '50');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ('23', '100860', '618', '805', '1', '35');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ('50', '100861', '618', '2255.5', '2', '45.11');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ('10', '100870', '618', '450', '3', '45');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ('10', '100861', '621', '450', '1', '45');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ('100', '100870', '621', '280', '2', '2.8');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ('50', '100871', '615', '250', '3', '5');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ('1', '101860', '608', '24', '1', '24');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ('2', '100871', '608', '11.2', '2', '5.6');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ('1', '100861', '609', '35', '1', '35');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ('1', '102130', '606', '3.4', '1', '3.4');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ('100', '100861', '605', '4500', '1', '45');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ('500', '100870', '605', '1400', '2', '2.8');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ('5', '100890', '605', '290', '3', '58');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ('50', '101860', '605', '1200', '4', '24');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ('100', '101863', '605', '900', '5', '9');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ('10', '102130', '605', '34', '6', '3.4');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ('100', '100871', '612', '550', '4', '5.5');

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES ('50', '100871', '619', '280', '4', '5.6');

INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE)
VALUES ('4.8', '01-JAN-85', '100871', '3.2', '01-DEC-85');

INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE)
VALUES ('58', '01-JAN-85', '100890', '46.4', '');

INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE)
VALUES ('54', '01-JUN-84', '100890', '40.5', '31-MAY-84');

INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE)
VALUES ('35', '01-JUN-86', '100860', '28', '');

INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE)
VALUES ('32', '01-JAN-86', '100860', '25.6', '31-MAY-86');

INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE)
VALUES ('30', '01-JAN-85', '100860', '24', '31-DEC-85');

INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE)
VALUES ('45', '01-JUN-86', '100861', '36', '');

INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE)
VALUES ('42', '01-JAN-86', '100861', '33.6', '31-MAY-86');

INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE)
VALUES ('39', '01-JAN-85', '100861', '31.2', '31-DEC-85');

INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE)
VALUES ('2.8', '01-JAN-86', '100870', '2.4', '');

INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE)
VALUES ('2.4', '01-JAN-85', '100870', '1.9', '01-DEC-85');

INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE)
VALUES ('5.6', '01-JAN-86', '100871', '4.8', '');

INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE)
VALUES ('24', '15-FEB-85', '101860', '18', '');

INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE)
VALUES ('12.5', '15-FEB-85', '101863', '9.4', '');

INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE)
VALUES ('3.4', '18-AUG-85', '102130', '2.8', '');

INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE)
VALUES ('2.4', '15-NOV-86', '200376', '1.75', '');

INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE)
VALUES ('4', '15-NOV-86', '200380', '3.2', '');

INSERT INTO PRODUCT (PRODID, DESCRIP)
VALUES ('100860', 'ACE TENNIS RACKET I');

INSERT INTO PRODUCT (PRODID, DESCRIP)
VALUES ('100861', 'ACE TENNIS RACKET II');

INSERT INTO PRODUCT (PRODID, DESCRIP)
VALUES ('100870', 'ACE TENNIS BALLS-3 PACK');

INSERT INTO PRODUCT (PRODID, DESCRIP)
VALUES ('100871', 'ACE TENNIS BALLS-6 PACK');

INSERT INTO PRODUCT (PRODID, DESCRIP)
VALUES ('100890', 'ACE TENNIS NET');

INSERT INTO PRODUCT (PRODID, DESCRIP)
VALUES ('101860', 'SP TENNIS RACKET');

INSERT INTO PRODUCT (PRODID, DESCRIP)
VALUES ('101863', 'SP JUNIOR RACKET');

INSERT INTO PRODUCT (PRODID, DESCRIP)
VALUES ('102130', 'RH: "GUIDE TO TENNIS"');

INSERT INTO PRODUCT (PRODID, DESCRIP)
VALUES ('200376', 'SB ENERGY BAR-6 PACK');

INSERT INTO PRODUCT (PRODID, DESCRIP)
VALUES ('200380', 'SB VITA SNACK-6 PACK');

CREATE SEQUENCE ORDID
INCREMENT BY 1
START WITH 622
NOCACHE;

CREATE SEQUENCE PRODID
INCREMENT BY 1
START WITH 200381
NOCACHE;

CREATE SEQUENCE CUSTID
INCREMENT BY 1
START WITH 109
NOCACHE;

CREATE VIEW SALES AS
SELECT REPID, ORD.CUSTID, CUSTOMER.NAME CUSTNAME, PRODUCT.PRODID,
DESCRIP PRODNAME, SUM(ITEMTOT) AMOUNT
FROM ORD, ITEM, CUSTOMER, PRODUCT
WHERE ORD.ORDID = ITEM.ORDID
AND ORD.CUSTID = CUSTOMER.CUSTID
AND ITEM.PRODID = PRODUCT.PRODID
GROUP BY REPID, ORD.CUSTID, NAME, PRODUCT.PRODID, DESCRIP;

저작자표시 동일조건

'1. IT Story > Scripts' 카테고리의 다른 글

PostgreSQL Vaccum(XID) 관리 스크립트 (0)	2019.04.25
Oracle Awr Generator (0)	2019.04.22
PostgreSQL Backup/Recovery 요약 스크립트 (0)	2019.04.18
Oracle Character Set Scanner (CSSCAN)변경 대상 추출 스크립트 (0)	2019.04.17
Oracle SQL SysMetric 조회 스크립트 (0)	2019.04.13
Oracle Session UNDO, TEMP 사용 모니터링 (0)	2019.04.12
PostgreSQL Top SQL 추출 스크립트 (0)	2019.04.11
Oracle CPU Time, Waited Time 조회 스크립트 (0)	2019.04.10

운명을바꾸는자

IT와 함께 살아가는 삶

,

Oracle SQL SysMetric 조회 스크립트

1. IT Story/Scripts 2019. 4. 13. 23:22

SELECT TO_CHAR (MIN (begin_time), 'MM.DD HH24:MI') BEGIN_TIME,

TO_CHAR (MAX (end_time), 'MM.DD HH24:MI') END_TIME,

TO_CHAR (

SUM (

CASE metric_name

WHEN 'Background Checkpoints Per Sec' THEN average

END),

'FM999,999,999.99')

"Background Checkpoints/Sec",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'CR Blocks Created Per Sec' THEN average

END),

'FM999,999,999.99')

"CR Blocks Created/Sec",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'CR Undo Records Applied Per Sec' THEN average

END),

'FM999,999,999.99')

"CR Undo Records Applied/Sec",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'Consistent Read Changes Per Sec' THEN average

END),

'FM999,999,999.99')

"Consistent Read Changes/Sec",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'Consistent Read Gets Per Sec' THEN average

END),

'FM999,999,999,999.99')

"Consistent Read Gets/Sec",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'DB Block Changes Per Sec' THEN average

END),

'FM999,999,999.99')

"DB Block Changes/Sec",

TO_CHAR (

SUM (

CASE metric_name WHEN 'DB Block Gets Per Sec' THEN average END),

'FM999,999,999,999.99')

"DB Block Gets/Sec",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'DBWR Checkpoints Per Sec' THEN average

END),

'FM999,999,999.99')

"DBWR Checkpoints/Sec",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'Host CPU Utilization (%)' THEN average

END),

'FM999,999,999.99')

"Host CPU Utilization (%)",

TO_CHAR (

SUM (CASE metric_name WHEN 'CPU Usage Per Sec' THEN average END),

'FM999,999,999.99')

"CPU Usage(cs)/Sec",

TO_CHAR (

SUM (

CASE metric_name WHEN 'Database Time Per Sec' THEN average END),

'FM999,999,999.99')

"Database Time(cs)/Sec",

TO_CHAR (

SUM (

CASE metric_name WHEN 'Response Time Per Txn' THEN average END),

'FM999,999,999.99')

"Response Time(cs)/Txn",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'SQL Service Response Time' THEN average

END),

'FM999,999,999.99')

"SQL Service Response Time(cs)",

TO_CHAR (

SUM (CASE metric_name WHEN 'Disk Sort Per Sec' THEN average END),

'FM999,999,999.99')

"Disk Sort/Sec",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'Enqueue Deadlocks Per Sec' THEN average

END),

'FM999,999,999.99')

"Enqueue Deadlocks/Sec",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'Enqueue Requests Per Sec' THEN average

END),

'FM999,999,999.99')

"Enqueue Requests/Sec",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'Enqueue Timeouts Per Sec' THEN average

END),

'FM999,999,999.99')

"Enqueue Timeouts/Sec",

TO_CHAR (

SUM (

CASE metric_name WHEN 'Enqueue Waits Per Sec' THEN average END),

'FM999,999,999.99')

"Enqueue Waits/Sec",

TO_CHAR (

SUM (CASE metric_name WHEN 'Executions Per Sec' THEN average END),

'FM999,999,999.99')

"Executions/Sec",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'Full Index Scans Per Sec' THEN average

END),

'FM999,999,999.99')

"Full Index Scans/Sec",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'GC CR Block Received Per Second' THEN average

END),

'FM999,999,999.99')

"GC CR Block Received/Sec",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'GC Current Block Received Per Second' THEN average

END),

'FM999,999,999.99')

"GC Current Block Received/Sec",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'Hard Parse Count Per Sec' THEN average

END),

'FM999,999,999.99')

"Hard Parse Count/Sec",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'Branch Node Splits Per Sec' THEN average

END),

'FM999,999,999.99')

"Branch Node Splits/Sec",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'Leaf Node Splits Per Sec' THEN average

END),

'FM999,999,999.99')

"Leaf Node Splits/Sec",

TO_CHAR (

SUM (

CASE metric_name WHEN 'Logical Reads Per Sec' THEN average END),

'FM999,999,999.99')

"Logical Reads/Sec",

TO_CHAR (

SUM (CASE metric_name WHEN 'Logons Per Sec' THEN average END),

'FM999,999,999.99')

"Logons/Sec",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'Long Table Scans Per Sec' THEN average

END),

'FM999,999,999.99')

"Long Table Scans/Sec",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'Network Traffic Volume Per Sec' THEN average

END),

'FM999,999,999.99')

"Network Traffic(bytes)/Sec",

TO_CHAR (

SUM (CASE metric_name WHEN 'Open Cursors Per Sec' THEN average END),

'FM999,999,999.99')

"Open Cursors/Sec",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'PX downgraded 1 to 25% Per Sec' THEN average

END),

'FM999,999,999.99')

"PX downgraded 1 to 25%/Sec",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'PX downgraded 25 to 50% Per Sec' THEN average

END),

'FM999,999,999.99')

"PX downgraded 25 to 50%/Sec",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'PX downgraded 50 to 75% Per Sec' THEN average

END),

'FM999,999,999.99')

"PX downgraded 50 to 75%/Sec",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'PX downgraded 75 to 99% Per Sec' THEN average

END),

'FM999,999,999.99')

"PX downgraded 75 to 99%/Sec",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'PX operations not downgraded Per Sec' THEN average

END),

'FM999,999,999.99')

"PX operations not downgr./Sec",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'PX downgraded to serial Per Sec' THEN average

END),

'FM999,999,999.99')

"PX downgraded to serial/Sec",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'Parse Failure Count Per Sec' THEN average

END),

'FM999,999,999.99')

"Parse Failure Count/Sec",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'Physical Read Bytes Per Sec' THEN average

END),

'FM999,999,999.99')

"Physical Read Bytes/Sec",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'Physical Read IO Requests Per Sec' THEN average

END),

'FM999,999,999.99')

"Physical Read IO Requests/Sec",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'Physical Read Total Bytes Per Sec' THEN average

END),

'FM999,999,999.99')

"Physical Read Total Bytes/Sec",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'Physical Read Total IO Requests Per Sec' THEN average

END),

'FM999,999,999.99')

"Phy Read Total IO Requests/Sec",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'Physical Reads Direct Lobs Per Sec' THEN average

END),

'FM999,999,999.99')

"Physical Reads Direct Lobs/Sec",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'Physical Reads Direct Per Sec' THEN average

END),

'FM999,999,999.99')

"Physical Reads Direct/Sec",

TO_CHAR (

SUM (

CASE metric_name WHEN 'Physical Reads Per Sec' THEN average END),

'FM999,999,999.99')

"Physical Reads/Sec",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'Physical Write Bytes Per Sec' THEN average

END),

'FM999,999,999.99')

"Physical Write Bytes/Sec",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'Physical Write IO Requests Per Sec' THEN average

END),

'FM999,999,999.99')

"Physical Write IO Requests/Sec",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'Physical Write Total Bytes Per Sec' THEN average

END),

'FM999,999,999.99')

"Physical Write Total Bytes/Sec",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'Physical Write Total IO Requests Per Sec' THEN average

END),

'FM999,999,999.99')

"Phy Write Tot IO Requests/Sec",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'Physical Writes Direct Lobs Per Sec' THEN average

END),

'FM999,999,999.99')

"Phy Writes Direct Lobs/Sec",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'Physical Writes Direct Per Sec' THEN average

END),

'FM999,999,999.99')

"Physical Writes Direct/Sec",

TO_CHAR (

SUM (

CASE metric_name WHEN 'Physical Writes Per Sec' THEN average END),

'FM999,999,999.99')

"Physical Writes/Sec",

TO_CHAR (

SUM (

CASE metric_name WHEN 'Recursive Calls Per Sec' THEN average END),

'FM999,999,999.99')

"Recursive Calls/Sec",

TO_CHAR (

SUM (

CASE metric_name WHEN 'Redo Generated Per Sec' THEN average END),

'FM999,999,999.99')

"Redo Generated(bytes)/Sec",

TO_CHAR (

SUM (CASE metric_name WHEN 'Redo Writes Per Sec' THEN average END),

'FM999,999,999.99')

"Redo Writes/Sec",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'Total Index Scans Per Sec' THEN average

END),

'FM999,999,999.99')

"Total Index Scans/Sec",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'Total Parse Count Per Sec' THEN average

END),

'FM999,999,999.99')

"Total Parse Count/Sec",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'Total Table Scans Per Sec' THEN average

END),

'FM999,999,999.99')

"Total Table Scans/Sec",

TO_CHAR (

SUM (CASE metric_name WHEN 'User Calls Per Sec' THEN average END),

'FM999,999,999.99')

"User Calls/Sec",

TO_CHAR (

SUM (CASE metric_name WHEN 'User Commits Per Sec' THEN average END),

'FM999,999,999.99')

"User Commits/Sec",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'User Rollback UndoRec Applied Per Sec' THEN average

END),

'FM999,999,999.99')

"User UndoRecord Applied/Sec",

TO_CHAR (

SUM (

CASE metric_name WHEN 'User Rollbacks Per Sec' THEN average END),

'FM999,999,999.99')

"User Rollbacks/Sec",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'User Transaction Per Sec' THEN average

END),

'FM999,999,999.99')

"User Transaction/Sec",

TO_CHAR (

SUM (

CASE metric_name WHEN 'Buffer Cache Hit Ratio' THEN average END),

'FM999,999,999.99')

"Buffer Cache Hit Ratio",

TO_CHAR (

SUM (

CASE metric_name WHEN 'Cursor Cache Hit Ratio' THEN average END),

'FM999,999,999.99')

"Cursor Cache Hit Ratio",

TO_CHAR (

SUM (

CASE metric_name WHEN 'Database CPU Time Ratio' THEN average END),

'FM999,999,999.99')

"Database CPU Time Ratio",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'Database Wait Time Ratio' THEN average

END),

'FM999,999,999.99')

"Database Wait Time Ratio",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'Execute Without Parse Ratio' THEN average

END),

'FM999,999,999.99')

"Execute Without Parse Ratio",

TO_CHAR (

SUM (

CASE metric_name WHEN 'Library Cache Hit Ratio' THEN average END),

'FM999,999,999.99')

"Library Cache Hit Ratio",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'Library Cache Miss Ratio' THEN average

END),

'FM999,999,999.99')

"Library Cache Miss Ratio",

TO_CHAR (

SUM (CASE metric_name WHEN 'Row Cache Hit Ratio' THEN average END),

'FM999,999,999.99')

"Row Cache Hit Ratio",

TO_CHAR (

SUM (CASE metric_name WHEN 'Row Cache Miss Ratio' THEN average END),

'FM999,999,999.99')

"Row Cache Miss Ratio",

TO_CHAR (

SUM (CASE metric_name WHEN 'Shared Pool Free %' THEN average END),

'FM999,999,999.99')

"Shared Pool Free %",

TO_CHAR (

SUM (CASE metric_name WHEN 'Memory Sorts Ratio' THEN average END),

'FM999,999,999.99')

"Memory Sorts Ratio",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'Redo Allocation Hit Ratio' THEN average

END),

'FM999,999,999.99')

"Redo Allocation Hit Ratio",

TO_CHAR (

SUM (CASE metric_name WHEN 'Soft Parse Ratio' THEN average END),

'FM999,999,999.99')

"Soft Parse Ratio",

TO_CHAR (

SUM (CASE metric_name WHEN 'User Calls Ratio' THEN average END),

'FM999,999,999.99')

"User Calls Ratio",

TO_CHAR (

SUM (

CASE metric_name WHEN 'User Commits Percentage' THEN average END),

'FM999,999,999.99')

"User Commits Percentage",

TO_CHAR (

SUM (CASE metric_name WHEN 'Session Limit %' THEN average END),

'FM999,999,999.99')

"Session Limit %",

TO_CHAR (SUM (CASE metric_name WHEN 'User Limit %' THEN average END),

'FM999,999,999.99')

"User Limit %",

TO_CHAR (

SUM (CASE metric_name WHEN 'Process Limit %' THEN average END),

'FM999,999,999.99')

"Process Limit %",

TO_CHAR (

SUM (CASE metric_name WHEN 'PGA Cache Hit %' THEN average END),

'FM999,999,999.99')

"PGA Cache Hit %",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'User Rollbacks Percentage' THEN average

END),

'FM999,999,999.99')

"User Rollbacks Percentage",

TO_CHAR (

SUM (CASE metric_name WHEN 'Current Logons Count' THEN average END),

'FM999,999,999')

"Current Logons Count",

TO_CHAR (

SUM (CASE metric_name WHEN 'Current OS Load' THEN average END),

'FM999,999,999.99')

"Current OS Load(# of Process)",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'Current Open Cursors Count' THEN average

END),

'FM999,999,999')

"Current Open Cursors Count",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'Branch Node Splits Per Txn' THEN average

END),

'FM999,999,999.99')

"Branch Node Splits/Txn",

TO_CHAR (

SUM (CASE metric_name WHEN 'CPU Usage Per Txn' THEN average END),

'FM999,999,999.99')

"CPU Usage(cs)/Txn",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'CR Blocks Created Per Txn' THEN average

END),

'FM999,999,999.99')

"CR Blocks Created/Txn",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'CR Undo Records Applied Per Txn' THEN average

END),

'FM999,999,999.99')

"CR Undo Records Applied/Txn",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'Consistent Read Changes Per Txn' THEN average

END),

'FM999,999,999.99')

"Consistent Read Changes/Txn",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'Consistent Read Gets Per Txn' THEN average

END),

'FM999,999,999.99')

"Consistent Read Gets/Txn",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'DB Block Changes Per Txn' THEN average

END),

'FM999,999,999.99')

"DB Block Changes/Txn",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'DB Block Changes Per User Call' THEN average

END),

'FM999,999,999.99')

"DB Block Changes/User Call",

TO_CHAR (

SUM (

CASE metric_name WHEN 'DB Block Gets Per Txn' THEN average END),

'FM999,999,999,999.99')

"DB Block Gets/Txn",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'DB Block Gets Per User Call' THEN average

END),

'FM999,999,999,999.99')

"DB Block Gets/User Call",

TO_CHAR (

SUM (CASE metric_name WHEN 'Disk Sort Per Txn' THEN average END),

'FM999,999,999.99')

"Disk Sort/Txn",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'Enqueue Deadlocks Per Txn' THEN average

END),

'FM999,999,999.99')

"Enqueue Deadlocks/Txn",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'Enqueue Requests Per Txn' THEN average

END),

'FM999,999,999.99')

"Enqueue Requests/Txn",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'Enqueue Timeouts Per Txn' THEN average

END),

'FM999,999,999.99')

"Enqueue Timeouts/Txn",

TO_CHAR (

SUM (

CASE metric_name WHEN 'Enqueue Waits Per Txn' THEN average END),

'FM999,999,999.99')

"Enqueue Waits/Txn",

TO_CHAR (

SUM (CASE metric_name WHEN 'Executions Per Txn' THEN average END),

'FM999,999,999.99')

"Executions/Txn",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'Executions Per User Call' THEN average

END),

'FM999,999,999.99')

"Executions/User Call",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'Full Index Scans Per Txn' THEN average

END),

'FM999,999,999.99')

"Full Index Scans/Txn",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'GC CR Block Received Per Txn' THEN average

END),

'FM999,999,999.99')

"GC CR Block Received/Txn",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'GC Current Block Received Per Txn' THEN average

END),

'FM999,999,999.99')

"GC Current Block Received/Txn",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'Global Cache Average CR Get Time' THEN average

END),

'FM999,999,999.99')

"Global Cache Avg CR Get Tm(cs)",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'Global Cache Average Current Get Time' THEN average

END),

'FM999,999,999.99')

"GlobalCache Avg Cur Get Tm(cs)",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'Global Cache Blocks Corrupted' THEN average

END),

'FM999,999,999.99')

"Global Cache Blocks Corrupted",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'Global Cache Blocks Lost' THEN average

END),

'FM999,999,999.99')

"Global Cache Blocks Lost",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'Hard Parse Count Per Txn' THEN average

END),

'FM999,999,999.99')

"Hard Parse Count/Txn",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'Leaf Node Splits Per Txn' THEN average

END),

'FM999,999,999.99')

"Leaf Node Splits/Txn",

TO_CHAR (

SUM (

CASE metric_name WHEN 'Logical Reads Per Txn' THEN average END),

'FM999,999,999.99')

"Logical Reads/Txn",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'Logical Reads Per User Call' THEN average

END),

'FM999,999,999.99')

"Logical Reads/User Call",

TO_CHAR (

SUM (CASE metric_name WHEN 'Logons Per Txn' THEN average END),

'FM999,999,999.99')

"Logons/Txn",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'Long Table Scans Per Txn' THEN average

END),

'FM999,999,999.99')

"Long Table Scans/Txn",

TO_CHAR (

SUM (CASE metric_name WHEN 'Open Cursors Per Txn' THEN average END),

'FM999,999,999.99')

"Open Cursors/Txn",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'Parse Failure Count Per Txn' THEN average

END),

'FM999,999,999.99')

"Parse Failure Count/Txn",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'Physical Reads Direct Lobs Per Txn' THEN average

END),

'FM999,999,999.99')

"Physical Reads Direct Lobs/Txn",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'Physical Reads Direct Per Txn' THEN average

END),

'FM999,999,999.99')

"Physical Reads Direct/Txn",

TO_CHAR (

SUM (

CASE metric_name WHEN 'Physical Reads Per Txn' THEN average END),

'FM999,999,999.99')

"Physical Reads/Txn",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'Physical Writes Direct Lobs Per Txn' THEN average

END),

'FM999,999,999.99')

"Phy Writes Direct Lobs/Txn",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'Physical Writes Direct Per Txn' THEN average

END),

'FM999,999,999.99')

"Physical Writes Direct/Txn",

TO_CHAR (

SUM (

CASE metric_name WHEN 'Physical Writes Per Txn' THEN average END),

'FM999,999,999.99')

"Physical Writes/Txn",

TO_CHAR (

SUM (

CASE metric_name WHEN 'Recursive Calls Per Txn' THEN average END),

'FM999,999,999.99')

"Recursive Calls/Txn",

TO_CHAR (

SUM (

CASE metric_name WHEN 'Redo Generated Per Txn' THEN average END),

'FM999,999,999.99')

"Redo Generated(bytes)/Txn",

TO_CHAR (

SUM (CASE metric_name WHEN 'Redo Writes Per Txn' THEN average END),

'FM999,999,999.99')

"Redo Writes/Txn",

TO_CHAR (SUM (CASE metric_name WHEN 'Rows Per Sort' THEN average END),

'FM999,999,999.99')

"Rows/Sort",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'Total Index Scans Per Txn' THEN average

END),

'FM999,999,999.99')

"Total Index Scans/Txn",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'Total Parse Count Per Txn' THEN average

END),

'FM999,999,999.99')

"Total Parse Count/Txn",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'Total Sorts Per User Call' THEN average

END),

'FM999,999,999.99')

"Total Sorts/User Call",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'Total Table Scans Per Txn' THEN average

END),

'FM999,999,999.99')

"Total Table Scans/Txn",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'Total Table Scans Per User Call' THEN average

END),

'FM999,999,999.99')

"Total Table Scans/User Call",

TO_CHAR (

SUM (CASE metric_name WHEN 'Txns Per Logon' THEN average END),

'FM999,999,999.99')

"Txns/Logon",

TO_CHAR (

SUM (CASE metric_name WHEN 'User Calls Per Txn' THEN average END),

'FM999,999,999.99')

"User Calls/Txn",

TO_CHAR (

SUM (

CASE metric_name

WHEN 'User Rollback Undo Records Applied Per Txn'

THEN

average

END),

'FM999,999,999.99')

"User Undo Records Applied/Txn"

FROM DBA_HIST_SYSMETRIC_SUMMARY DHSS

WHERE DHSS.DBID = (select dbid from v$database) --변경

AND DHSS.INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance) --변경

AND DHSS.SNAP_ID >= :3 + 1

AND DHSS.SNAP_ID <= :4 + 1

GROUP BY snap_id

ORDER BY snap_id

저작자표시 동일조건

'1. IT Story > Scripts' 카테고리의 다른 글

Oracle Awr Generator (0)	2019.04.22
PostgreSQL Backup/Recovery 요약 스크립트 (0)	2019.04.18
Oracle Character Set Scanner (CSSCAN)변경 대상 추출 스크립트 (0)	2019.04.17
Oracle 테스트 데이터 생성 스크립트 (0)	2019.04.16
Oracle Session UNDO, TEMP 사용 모니터링 (0)	2019.04.12
PostgreSQL Top SQL 추출 스크립트 (0)	2019.04.11
Oracle CPU Time, Waited Time 조회 스크립트 (0)	2019.04.10
PostgreSQL Autovacuum 대상 테이블 목록 스크립트 (0)	2019.04.08

운명을바꾸는자

IT와 함께 살아가는 삶

,

Oracle Session UNDO, TEMP 사용 모니터링

1. IT Story/Scripts 2019. 4. 12. 18:41

Oracle Session UNDO, TEMP 사용 모니터링

1. SESSION 별 UNDO 사용 모니터링

SELECT
        A.*
      , B.SQL_TEXT
FROM    (

            SELECT
                    TO_CHAR(S.SID)||','||TO_CHAR(S.SERIAL#) SID_SERIAL
                  , NVL(S.USERNAME, 'NONE') ORAUSER
                  , S.SQL_ID
                  , S.PROGRAM
                  , R.NAME UNDOSEG
                  , T.USED_UBLK * TO_NUMBER(X.VALUE)/1024||'K' "UNDO"
            FROM
                    SYS.V_$ROLLNAME R
                  , SYS.V_$SESSION S
                  , SYS.V_$TRANSACTION T
                  , SYS.V_$PARAMETER X
            WHERE   1           = 1
            AND     S.TADDR     = T.ADDR
            AND     R.USN       = T.XIDUSN(+)
            AND     X.NAME      = 'db_block_size'
        )   A
      , V_$SQL B
WHERE   1     =   1
AND     A.SQL_ID    =   B.SQL_ID;

2. SESSION 별 템프 사용 모니터링

SELECT
        S.SID, S.SERIAL#, S.USERNAME, S.STATUS, S.SQL_ID
      , U.TABLESPACE, S.MACHINE, U.CONTENTS, U.SEGTYPE, U.EXTENTS, U.BLOCKS
      , S.LOGON_TIME
      , ROUND(((U.BLOCKS*P.VALUE)/1024/1024), 2) MB
      , ROUND(SUM(U.BLOCKS*P.VALUE) OVER()/1024/1024, 2) TOT_MB
      , ROUND(SUM(U.BLOCKS*P.VALUE) OVER()/1024/1024/1024, 2) TOT_GB
FROM    gV$SESSION S
      , V$SORT_USAGE U
      , SYS.V_$SYSTEM_PARAMETER P
WHERE   S.SADDR         = U.SESSION_ADDR
AND     UPPER(P.NAME)   = 'DB_BLOCK_SIZE'
AND     STATUS          = 'ACTIVE'
--AND     S.SQL_ID        = 'c8z3uzvgx74gy'
--AND     U.SEGTYPE       = 'LOB_DATA'
ORDER BY
        U.TABLESPACE DESC , MB DESC, S.MACHINE DESC, S.LOGON_TIME DESC

저작자표시 동일조건

'1. IT Story > Scripts' 카테고리의 다른 글

PostgreSQL Backup/Recovery 요약 스크립트 (0)	2019.04.18
Oracle Character Set Scanner (CSSCAN)변경 대상 추출 스크립트 (0)	2019.04.17
Oracle 테스트 데이터 생성 스크립트 (0)	2019.04.16
Oracle SQL SysMetric 조회 스크립트 (0)	2019.04.13
PostgreSQL Top SQL 추출 스크립트 (0)	2019.04.11
Oracle CPU Time, Waited Time 조회 스크립트 (0)	2019.04.10
PostgreSQL Autovacuum 대상 테이블 목록 스크립트 (0)	2019.04.08
PostgreSQL 통계(analyze) 상태 확인 스크립트 (0)	2019.04.07

운명을바꾸는자

IT와 함께 살아가는 삶

,

PostgreSQL Top SQL 추출 스크립트

1. IT Story/Scripts 2019. 4. 11. 21:41

PostgreSQL Top SQL 추출 스크립트

do $$
declare
   has_pg_stat_statements boolean;
begin
   select count(*)>0 from pg_extension where extname = 'pg_stat_statements' into has_pg_stat_statements;
   if has_pg_stat_statements = false then
        create extension pg_stat_statements;
        raise notice 'create extension pg_stat_statements';
   end if;
end
$$;

select
        queryid,
        sum( calls ) as calls,
        round(( sum( total_time )/ 1000 )::numeric, 2 ) as total_time,
        round(( min( min_time )/ 1000 )::numeric, 2 ) as min_time,
        round(( max( max_time )/ 1000 )::numeric, 2 ) as max_time,
        round((( sum( mean_time )/ 1000 )/ count(*))::numeric, 2 ) as mean_time,
        (
                sum( rows )/ count(*)
        )::int as rows,
        (
                sum( shared_blks_hit )/ count(*)
        )::int as shared_blks_hit,
        max( query ) as query
from
        pg_stat_statements ptt
where
        dbid in(
                select
                        oid
                from
                        pg_database
                where
                        datname = current_database()
        )
        and strpos(
                query,
                'create'
        )= 0
        and strpos(
                query,
                'drop'
        )= 0
        and strpos(
                query,
                'copy'
        )= 0
        and strpos(
                query,
                'pg_stat_user'
        )= 0
        and strpos(
                query,
                'vacuum'
        )= 0
group by
        queryid
order by
        total_time desc,
        mean_time desc limit 50;

저작자표시 동일조건

'1. IT Story > Scripts' 카테고리의 다른 글

Oracle Character Set Scanner (CSSCAN)변경 대상 추출 스크립트 (0)	2019.04.17
Oracle 테스트 데이터 생성 스크립트 (0)	2019.04.16
Oracle SQL SysMetric 조회 스크립트 (0)	2019.04.13
Oracle Session UNDO, TEMP 사용 모니터링 (0)	2019.04.12
Oracle CPU Time, Waited Time 조회 스크립트 (0)	2019.04.10
PostgreSQL Autovacuum 대상 테이블 목록 스크립트 (0)	2019.04.08
PostgreSQL 통계(analyze) 상태 확인 스크립트 (0)	2019.04.07
Oracle 통계정보 백업, 복구(export/import) (0)	2019.04.06

운명을바꾸는자

IT와 함께 살아가는 삶

,

Oracle CPU Time, Waited Time 조회 스크립트

1. IT Story/Scripts 2019. 4. 10. 18:15

Oracle CPU Time, Waited Time 조회 스크립트

SET HEADING OFF

Set lines 200 pages 200

with AASSTAT as (

select

decode(n.wait_class,'User I/O','User I/O',

'Commit','Commit',

'Wait') CLASS,

sum(round(m.time_waited/m.INTSIZE_CSEC,3)) AAS

from v$waitclassmetric m,

v$system_wait_class n

where m.wait_class_id=n.wait_class_id

and n.wait_class != 'Idle'

group by decode(n.wait_class,'User I/O','User I/O', 'Commit','Commit', 'Wait')

union

select 'CPU_ORA_CONSUMED' CLASS,

round(value/100,3) AAS

from v$sysmetric

where metric_name='CPU Usage Per Sec'

and group_id=2

union

select 'CPU_OS' CLASS ,

round((prcnt.busy*parameter.cpu_count)/100,3) AAS

from

( select value busy from v$sysmetric where metric_name='Host CPU Utilization (%)' and group_id=2 ) prcnt,

( select value cpu_count from v$parameter where name='cpu_count' ) parameter

union

select

'CPU_ORA_DEMAND' CLASS,

nvl(round( sum(decode(session_state,'ON CPU',1,0))/60,2),0) AAS

from v$active_session_history ash

where SAMPLE_TIME > sysdate - (60/(24*60*60))

)

select

( decode(sign(CPU_OS-CPU_ORA_CONSUMED), -1, 0, (CPU_OS - CPU_ORA_CONSUMED )) +

CPU_ORA_CONSUMED +

decode(sign(CPU_ORA_DEMAND-CPU_ORA_CONSUMED), -1, 0, (CPU_ORA_DEMAND - CPU_ORA_CONSUMED ))) CPU_TOTAL,

decode(sign(CPU_OS-CPU_ORA_CONSUMED), -1, 0, (CPU_OS - CPU_ORA_CONSUMED )) CPU_OS,

CPU_ORA_CONSUMED CPU_ORA,

decode(sign(CPU_ORA_DEMAND-CPU_ORA_CONSUMED), -1, 0, (CPU_ORA_DEMAND - CPU_ORA_CONSUMED )) CPU_ORA_WAIT,

COMMIT,

USER_IO,

WAIT

from (

select

sum(decode(CLASS,'CPU_ORA_CONSUMED',AAS,0)) CPU_ORA_CONSUMED,

sum(decode(CLASS,'CPU_ORA_DEMAND' ,AAS,0)) CPU_ORA_DEMAND,

sum(decode(CLASS,'CPU_OS' ,AAS,0)) CPU_OS,

sum(decode(CLASS,'Commit' ,AAS,0)) COMMIT,

sum(decode(CLASS,'User I/O' ,AAS,0)) USER_IO,

sum(decode(CLASS,'Wait' ,AAS,0)) WAIT

from AASSTAT)

/

저작자표시 동일조건

'1. IT Story > Scripts' 카테고리의 다른 글

Oracle 테스트 데이터 생성 스크립트 (0)	2019.04.16
Oracle SQL SysMetric 조회 스크립트 (0)	2019.04.13
Oracle Session UNDO, TEMP 사용 모니터링 (0)	2019.04.12
PostgreSQL Top SQL 추출 스크립트 (0)	2019.04.11
PostgreSQL Autovacuum 대상 테이블 목록 스크립트 (0)	2019.04.08
PostgreSQL 통계(analyze) 상태 확인 스크립트 (0)	2019.04.07
Oracle 통계정보 백업, 복구(export/import) (0)	2019.04.06
Oracle 통계정보 추출 스크립트 (0)	2019.04.05

운명을바꾸는자

IT와 함께 살아가는 삶

,

'1. IT Story/Scripts'에 해당되는 글 75건

Oracle Awr Generator

'1. IT Story > Scripts' 카테고리의 다른 글

PostgreSQL Backup/Recovery 요약 스크립트

'1. IT Story > Scripts' 카테고리의 다른 글

Oracle Character Set Scanner (CSSCAN)변경 대상 추출 스크립트

'1. IT Story > Scripts' 카테고리의 다른 글

Oracle 테스트 데이터 생성 스크립트

'1. IT Story > Scripts' 카테고리의 다른 글

Oracle SQL SysMetric 조회 스크립트

'1. IT Story > Scripts' 카테고리의 다른 글

Oracle Session UNDO, TEMP 사용 모니터링

Oracle Session UNDO, TEMP 사용 모니터링

'1. IT Story > Scripts' 카테고리의 다른 글

PostgreSQL Top SQL 추출 스크립트

PostgreSQL Top SQL 추출 스크립트

'1. IT Story > Scripts' 카테고리의 다른 글

Oracle CPU Time, Waited Time 조회 스크립트

Oracle CPU Time, Waited Time 조회 스크립트

'1. IT Story > Scripts' 카테고리의 다른 글

공지사항

카테고리

태그목록

글 보관함

달력

링크

운명을바꾸는자

LATEST FROM OUR BLOG

LATEST COMMENTS

티스토리툴바