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

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와 함께 살아가는 삶

,

CPU (Central Processing Unit)

1. IT Story/Basic Studies 2019. 4. 9. 18:22

CPU (Central Processing Unit)

1. CPU (Central Processing Unit)의 개요

1-1. CPU (Central Processing Unit)의 정의

- 컴퓨터의 가장 중요한 부분으로서 명령을 해독하고, 산술논리연산이나 데이터 처리를 실행하는 장치
- 프로그램의 실행과 데이터 처리하는 중추적 기능의 수행을 담당하는 요소

1-2. CPU가 수행하는 세부동작

1) 공통적 동작 :

- 명령어 인출 (Instruction Fetch), 기억장치로부터 명령어를 읽어옴

- 명령어 해독 (Instruction Decode), 수행해야 할 동작을 결정하기 위해서 인출된 명령어를 해독

2) 필요시 수행

- 데이터 인출 (Data Fetch) , 명령어 실행을 위하여 데이터가 필요한 경우에는 기억장치 또는 입출력장치로부터 그 데이터를 읽어옴
- 데이터 처리 (Data Process), 데이터에 대한 산술적 또는 논리적 연산을 수행
- 데이터 쓰기 (Data Store), 실행한 결과를 저장

2. CPU (Central Processing Unit) 구조와 구성요소

2-1. CPU (Central Processing Unit)의 구조

2-2. CPU (Central Processing Unit)의 구성요소

1) ALU
- Arithmetic and Logic Unit
- 각종 산출연산들과 논리연산을 수행하는 회로들로 이루어진 하드웨어 모듈
- 산술연산: 덧셈, 뺄셈, 곱셈, 나눗셈
- 논리연산: AND, OR, NOT, XOR 등

2) 레지스터
- CPU 내부에 위치한 엑세스 속도가 가장 빠른 기억장치
- PC(프로그램 카운터), IR(명령어 레지스터), AC(누산기), MAR(기억장치 주소 레지스터), MBR(기억장치 버퍼 레지스터), SP(스택포인터)

3) 제어유닛
- 프로그램 코드(명령어)를 해석하고, 그것을 실행하기 위해 제어 신호들(Control Signal)을 순차적으로 발생하는 하드웨어 모듈

4) CPU 내부버스
주소버스
- CPU가 외부로 발생하는 주소정보를 전송하는 신호 선들의 집합
데이터버스
- CPU가 기억장치 혹은 I/O장치와 사이에 데이터를 전송하기 위한 신호선의 집합
제어버스
- CPU가 시스템 내의 각종 요소들의 동작을 제어하는데 필요한 신호선의 집합

3. 명령어 사이클의 종류와 CPU 구성요소의 관계

1) 인출 사이클
- 프로그램 카운터(PC)가 가리키는 기억장치 위치로부터 명령어를 인출해옴
- 레지스터(PC, MAR, MBR, IR), CPU 내부버스

2) 실행사이클
- CPU가 인출된 명령어 코드를 해독(Decode)하고, 그 결과에 따라 필요한 연산을 수행
- 레지스터(AC), ALU, 제어유닛

3) 인터럽트사이클
- 인터럽트 요구신호를 검사하고, 현재의 PC 내용을 스택에 저장한 다음에 PC에 해당 ISR의 시작주소를 적재하는 과정
- 레지스터(MBR, PC, MAR, SP)

4) 간접사이클
- 실행 사이클이 시작되기 전에 그 데이터의 실제 주소를 기억장치로부터 읽어오는 과정
- 레지스터(MAR, IR, MBR), CPU 내부버스

저작자표시 동일조건

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

반복적 모델(Iterative Development Model) (0)	2019.04.23
나선형 모델(Spiral) (0)	2019.04.21
소프트웨어공학-프로토타입(Prototype)모델 (0)	2019.04.20
소프트웨어공학-폭포수 모델 (Waterfall) (0)	2019.04.19
BPM(Business Process Management) (0)	2019.04.04
BPEL(Business Process Execution Language) (0)	2019.04.03
BRE(Business Rule Engine) (0)	2019.04.02
BAM(Business Activity Monitoring) (0)	2019.04.01

운명을바꾸는자

IT와 함께 살아가는 삶

,

PostgreSQL Autovacuum 대상 테이블 목록 스크립트

1. IT Story/Scripts 2019. 4. 8. 19:13

PostgreSQL Autovacuum기준으로 vacuum 대상 테이블 목록 스크립트

WITH vbt AS (SELECT setting AS autovacuum_vacuum_threshold FROM
pg_settings WHERE name = 'autovacuum_vacuum_threshold')
, vsf AS (SELECT setting AS autovacuum_vacuum_scale_factor FROM
pg_settings WHERE name = 'autovacuum_vacuum_scale_factor')
, fma AS (SELECT setting AS autovacuum_freeze_max_age FROM
pg_settings WHERE name = 'autovacuum_freeze_max_age')
, sto AS (select opt_oid, split_part(setting, '=', 1) as param,
split_part(setting, '=', 2) as value from (select oid opt_oid,
unnest(reloptions) setting from pg_class) opt)
SELECT
    '"'||ns.nspname||'"."'||c.relname||'"' as relation
    , pg_size_pretty(pg_table_size(c.oid)) as table_size
    , age(relfrozenxid) as xid_age
    , coalesce(cfma.value::float, autovacuum_freeze_max_age::float)
autovacuum_freeze_max_age
    , (coalesce(cvbt.value::float, autovacuum_vacuum_threshold::float)
+ coalesce(cvsf.value::float,autovacuum_vacuum_scale_factor::float) *
pg_table_size(c.oid)) as autovacuum_vacuum_tuples
    , n_dead_tup as dead_tuples
FROM pg_class c join pg_namespace ns on ns.oid = c.relnamespace
join pg_stat_all_tables stat on stat.relid = c.oid
join vbt on (1=1) join vsf on (1=1) join fma on (1=1)
left join sto cvbt on cvbt.param = 'autovacuum_vacuum_threshold' and
c.oid = cvbt.opt_oid
left join sto cvsf on cvsf.param = 'autovacuum_vacuum_scale_factor' and
c.oid = cvsf.opt_oid
left join sto cfma on cfma.param = 'autovacuum_freeze_max_age' and
c.oid = cfma.opt_oid
WHERE c.relkind = 'r' and nspname <> 'pg_catalog'
and (
    age(relfrozenxid) >= coalesce(cfma.value::float,
autovacuum_freeze_max_age::float)
    or
    coalesce(cvbt.value::float, autovacuum_vacuum_threshold::float) +
coalesce(cvsf.value::float,autovacuum_vacuum_scale_factor::float) *
pg_table_size(c.oid) <= n_dead_tup
   -- or 1 = 1
)
ORDER BY age(relfrozenxid) DESC LIMIT 50;

저작자표시 동일조건

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

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
PostgreSQL 통계(analyze) 상태 확인 스크립트 (0)	2019.04.07
Oracle 통계정보 백업, 복구(export/import) (0)	2019.04.06
Oracle 통계정보 추출 스크립트 (0)	2019.04.05
Oracle 특정 테이블 통계정보 확인 스크립트 (0)	2019.03.31

운명을바꾸는자

IT와 함께 살아가는 삶

,

PostgreSQL 통계(analyze) 상태 확인 스크립트

1. IT Story/Scripts 2019. 4. 7. 16:37

PostgreSQL 통계(analyze) 상태 확인 스크립트

SELECT
    n.nspname AS schema_name,
    c.relname AS table_name,
    pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid) as total_tuple,
    pg_stat_get_live_tuples(c.oid) AS live_tuple,
    pg_stat_get_dead_tuples(c.oid) AS dead_tupple,
    round(100*pg_stat_get_live_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid)

+ pg_stat_get_dead_tuples(c.oid)),2) as live_tuple_rate,
round(100*pg_stat_get_dead_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid)

+ pg_stat_get_dead_tuples(c.oid)),2) as dead_tuple_rate,
pg_size_pretty(pg_total_relation_size(c.oid)) as total_relation_size,
pg_size_pretty(pg_relation_size(c.oid)) as relation_size
FROM pg_class AS c
JOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace
WHERE pg_stat_get_live_tuples(c.oid) > 0
AND c.relname NOT LIKE 'pg_%'
ORDER BY dead_tupple DESC;

저작자표시 동일조건

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

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
Oracle 통계정보 백업, 복구(export/import) (0)	2019.04.06
Oracle 통계정보 추출 스크립트 (0)	2019.04.05
Oracle 특정 테이블 통계정보 확인 스크립트 (0)	2019.03.31
Oracle ADDM을 활용한 성능 분석 조회 스크립트 (0)	2019.03.30

운명을바꾸는자

IT와 함께 살아가는 삶

,

'1. IT Story'에 해당되는 글 397건

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' 카테고리의 다른 글

CPU (Central Processing Unit)

CPU (Central Processing Unit)

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

PostgreSQL Autovacuum 대상 테이블 목록 스크립트

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

PostgreSQL 통계(analyze) 상태 확인 스크립트

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

공지사항

카테고리

태그목록

글 보관함

달력

링크

운명을바꾸는자

LATEST FROM OUR BLOG

LATEST COMMENTS

티스토리툴바