가. SQL 커서에 대한 작업 요청에 따른 구분
select cust_nm, birthday from customer where cust_id = :cust_id
call count cpu elapsed disk query current rows
----- ------ ----- ------- ---- ----- ------ -----
Parse 1 0.00 0.00 0 0 0 0
Execute 5000 0.18 0.14 0 0 0 0
Fetch 5000 0.21 0.25 0 20000 0 50000
----- ------ ----- ------- ---- ----- ------ -----
total 10001 0.39 0.40 0 20000 0 50000
나. Call 발생 위치에 따른 구분
1) User Call
2) Recursive Call
가. One SQL 구현의 중요성
public class JavaLoopQuery{
public static void insertData( Connection con
, String param1
, String param2
, String param3
, long param4) throws Exception{
String SQLStmt = "INSERT INTO 납입방법별_월요금집계 "
+ "(고객번호, 납입월, 납입방법코드, 납입금액) "
+ "VALUES(?, ?, ?, ?)";
PreparedStatement st = con.prepareStatement(SQLStmt);
st.setString(1, param1);
st.setString(2, param2);
st.setString(3, param3);
st.setLong(4, param4);
st.execute();
st.close();
}
public static void execute(Connection con, String input_month) throws Exception {
String SQLStmt = "SELECT 고객번호, 납입월, 지로, 자동이체, 신용카드, 핸드폰, 인터넷 "
+ "FROM 월요금납부실적 "
+ "WHERE 납입월 = ?";
PreparedStatement stmt = con.prepareStatement(SQLStmt);
stmt.setString(1, input_month);
ResultSet rs = stmt.executeQuery();
while(rs.next()){
String 고객번호 = rs.getString(1);
String 납입월 = rs.getString(2);
long 지로 = rs.getLong(3);
long 자동이체 = rs.getLong(4);
long 신용카드 = rs.getLong(5);
long 핸드폰 = rs.getLong(6);
long 인터넷 = rs.getLong(7);
if(지로 > 0) insertData (con, 고객번호, 납입월, "A", 지로);
if(자동이체 > 0) insertData (con, 고객번호, 납입월, "B", 자동이체);
if(신용카드 > 0) insertData (con, 고객번호, 납입월, "C", 신용카드);
if(핸드폰 > 0) insertData (con, 고객번호, 납입월, "D", 핸드폰);
if(인터넷 > 0) insertData (con, 고객번호, 납입월, "E", 인터넷);
}
rs.close();
stmt.close();
}
static Connection getConnection() throws Exception { ...... }
static void releaseConnection(Connection con) throws Exception { ...... }
public static void main(String[] args) throws Exception{
Connection con = getConnection();
execute(con, "200903");
releaseConnection(con);
}
}
나. 데이터베이스 Call과 시스템 확장성
void insertWishList ( String p_custid , String p_goods_no ) {
SQLStmt = "insert into wishlist "
+ "select custid, goods_no "
+ "from cart "
+ "where custid = ? "
+ "and goods_no = ? " ;
stmt = con.preparedStatement(SQLStmt);
stmt.setString(1, p_custid);
stmt.setString(2, p_goods_no); stmt.execute();
}
void insertWishList ( String p_custid , String[] p_goods_no ) {
SQLStmt = "insert into wishlist "
+ "select custid, goods_no "
+ "from cart "
+ "where custid = ? "
+ "and goods_no in ( ?, ?, ?, ?, ? )" ;
stmt = con.preparedStatement(SQLStmt);
stmt.setString(1, p_custid);
for(int i=0; i < 5; i++){
stmt.setString(i+2, p_goods_no[i]);
}
stmt.execute();
}
DECLARE
l_fetch_size NUMBER DEFAULT 1000; -- 1,000건씩 Array 처리
CURSOR c IS
SELECT empno, ename, job, sal, deptno, hiredate
FROM emp;
...
BEGIN
OPEN C;
LOOP
FETCH c BULK COLLECT
INTO p_empno, p_ename, p_job, p_sal, p_deptno, p_hiredate
LIMIT l_fetch_size;
FORALL i IN p_empno.first..p_empno.last
INSERT INTO emp2
VALUES ( p_empno (i)
, p_ename (i)
, p_job (i)
, p_sal (i)
, p_deptno (i)
, p_hiredate (i) );
EXIT WHEN c%NOTFOUND;
END LOOP;
CLOSE C;
4. FETCH CALL 최소화
가. 부분범위처리 원리
set arraysize 100
call count cpu elapsed disk query current rows
----- ----- ----- ------- ----- ----- ----- ------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.02 2 2 0 0
Fetch 301 0.14 0.18 9 315 0 30000
----- ----- ----- ------- ----- ----- ----- ------
total 303 0.14 0.20 11 317 0 30000
나. ArraySize 조정에 의한 Fetch Call 감소 및 블록 I/O 감소 효과
* ArraySize와 Fetch Count 및 블록 I/O 관계 : 반비례
SQL*Plus 이외의 프로그램 언어에서 Array 단위 Fetch 기능 활용 방법
1) Oracle PL/SQL(커서를 열고 레코드를 Fetch)
for item in cursor
loop
......
end loop;
2) JAVA(FetchSize를 100으로 설정했을때 데이터를 Fetch 해오는 매커니즘)
String sql = "select custid, name from customer";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setFetchSize(100); -- Statement에서 조정
ResultSet rs = stmt.executeQuery();
// rs.setFetchSize(100); -- ResultSet에서 조정할 수도 있다.
while( rs.next() ) {
int empno = rs.getInt(1);
String ename = rs.getString(2);
System.out.println(empno + ":" + ename);
}
rs.close();
stmt.close();
select channel_id, sum(quantity_sold) auantity_cold
from order a, sales@lk_sales b
where a.order_date between :1 and :2
and b.order_no = a.order no
group by channel_id
Rows Row Source Operation
----- ---------------------------------------------
5 SORT GROUP BY
10981 NESTED LOOPS
500000 REMOTE
10981 TABLE ACCESS BY INDEX ROWID ORDER
500000 INDEX UNIQUE SCAN (ORDER_PK)
select /*+ driving_site(b) */
channel_id, sum(quantity_sold) auantity_cold
from order a, sales@lk_sales b
where a.order_date between :1 and :2
and b.order_no = a.order_no
group by channel_id
Rows Row Source Operation
---- ---------------------------------------------
5 SORT GROUP BY
10981 NESTED LOOPS
939 TABLE ACCESS (BY INDEX ROWID) OF 'ORDER'
939 INDEX (RANGE SCAN) OF 'ORDER_IDX2' (NON-UNIQUE)
10981 REMOTE
가. 사용자 정의 함수/프로시저의 특징
create or replace function date_to_char(p_dt date) return varchar2 as
begin
return to_char(p_dt, 'yyyy/mm/dd hh24:mi:ss');
end;
/
나. 사용자 정의 함수/프로시저에 의한 성능 저하 해소 방안
[J STORY] SQLP - LOCK (0) | 2021.04.26 |
---|---|
[J STORY] SQLP - 데이터베이스 I/O 원리 (0) | 2021.04.26 |
[J STORY] SQLP - SQL 파싱 부하 (0) | 2021.04.25 |
SQLP - 인덱스 기본 (0) | 2021.04.24 |
SQLP - 데이터베이스 아키텍처 (0) | 2021.04.24 |
댓글 영역