Oracle9i Reportsのチューニング

Size: px

Start display at page:

Download "Oracle9i Reportsのチューニング"

れいがののした
5 years ago
Views:

1 Oracle9i Reports

2 Oracle9i Reports Oracle9i Forms A...22 B...24 Oracle9i Reports 2

3 Oracle9i Reports Oracle9i Reports Oracle9i Oracle9i Reports 3

4 Oracle9i Reports 1 SQL SQL SQL SQL XML rdf jsp A B Oracle9i Reports 4

5 rwrun module=emp.rdf destype=file desformat=pdf desname=emp_pdf.pdf traceopts=trace_prf tracemode=trace_replace tracefile=emp_tr.txt emp_tr.txt Report Builder Profiler statistics Total Elapsed Time: 8.00 seconds Reports Time: 7.00 seconds (87.50% of TOTAL) ORACLE Time: 1.00 seconds (12.50% of TOTAL) UPI: SQL: 0.00 seconds 1.00 seconds TOTAL CPU Time used by process: N/A Total Elapsed Time: Reports Time: ORACLE Time: ORACLE SQL UPI SQL SQL SRW.DO_SQL() EXEC_SQL PL/SQL Cursors XML SQL ORACLE Time UPI SQL Oracle9i SQL Oracle9i Reports Structured Query Language SQL SQL SQL Oracle9i Reports 5

6 Oracle9i Reports SQL SQL SQL SQL Oracle SQL SQL SQL TKPROF EXPLAIN PLAN Oracle Optimizer EXPLAIN PLAN SQL-TRACE Reports Builder SQL SRW.DO_SQL('ALTER SESSION SET SQL_TRACE=TRUE'); return(1); : SQL TRACE Before Report Before Parameter Form EXPLAIN PLAN SQL Oracle9i SQL Language Select e.ename, d.dname From emp e, dept d Where e.deptno (+) = d.deptno EXPLAIN PLAN OPERATION OPTIONS OBJECT_NAME POSITION SELECT STATEMENT MERGE JOIN OUTER 1 SORT JOIN 1 TABLE ACCESS FULL DEPT 1 SORT JOIN 2 TABLE ACCESS FULL EMP 1 Oracle9i Reports Oracle9i RDBMS 2 Oracle9i Reports 6

7 SQL SQL DBMS_STATS COMPUTE STATISTICS ANALYZE SQL Oracle9i Server PL/SQL PL/SQL ORA_PROF ORA_PROF PROCEDURE timed_proc (test VARCHAR2) IS i PLS_INTEGER; BEGIN Ora_Prof.Create_Timer('loop2'); Ora_Prof.Start_Timer('loop2'); ColorBand_Program_Unit; Ora_Prof.Stop_Timer('loop2'); Text_IO.Putf('Loop executed in %s seconds. n', Ora_Prof.Elapsed_Time('loop2')); Ora_Prof.Destroy_Timer('loop2'); END; Oracle9i Reports 7

8 ORA_PROF Reports Builder PL/SQL Oracle PL/SQL PL/SQL SQL PL/SQL PL/SQL Object Navigator PL/SQL PL/SQL PL/SQL PL/SQL SRW.DO_SQL() SRW.DO_SQL() SRW.DO_SQL() PL/SQL SRW.DO_SQL() SQL DDL 1 SRW.DO_SQL() SRW.DO_SQL DDL Runtime Parameter Form SRW.DO_SQL SRW.DO_SQL (`CREATE TABLE' :tname `(ACCOUNT NUMBER NOT NULL PRIMARY KEY, COMP NUMBER (10,2))'); Oracle9i Reports 8

9 WITH Oracle9i SQL Java Oracle9i PL/SQL Java SQL PL/SQL Oracle Java Java Java Java Oracle9i Java Java Importer Oracle PL/SQL Java Oracle9i Reports Oracle Java Importer Java Java Importer PL/SQL PL/SQL Java Java PL/SQL PL/SQL Java PL/SQL Java Importer PL/SQL Java JOBJECT JVM Java Object persistence Oracle9i Reports 9

10 SQL Oracle9i Reports (Pluggable) Oracle9i Reports XML JDBC SQL XML URL URL XML XML XML Query XML DTD XML XML XML XML DTD XML DTD XML XSL extensible style sheet language XSL JDBC JDBC Thin Thick JDBC Oracle9i Reports 10

11 SQL WHERE 1 SQL Oracle9i JDBC PL/SQL Oracle9i CREATE OR REPLACE FUNCTION CityState ( p_location_id world_cities.location_id%type) RETURN VARCHAR2 is v_result VARCHAR2(100); BEGIN SELECT city ',' state INTO v_result FROM world_cities WHERE location_id = p_location_id; RETURN v_result; END CityState; SQL Select location_id, citystate(location_id)"city & State" from world_cities Oracle9i Reports 11

LOCATION_ID CITY & STATE ----------- ------------------------- 1 Redwood Shores, California 2 Seattle, Washington 3 Los Angeles, California 4 New York, New York 1 2 1 SRW.

12 LOCATION_ID CITY & STATE Redwood Shores, California 2 Seattle, Washington 3 Los Angeles, California 4 New York, New York SRW.SET_MAXROW() Query_Emp Query_Dept Before Report IF :Parameter_1 = 'A' then SRW.SET_MAXROW('Query_Emp',0); ELSE SRW.SET_MAX_ROW('Query_Dept',0); END IF; SRW.SET_MAXROW() Before Report SRW.SET_MAXROW() SRW.MAXROW_UNSET XML Oracle9i Reports 12

13 Oracle9i Reports Oracle9i SQL ORDER BY ORDER BY ORDER BY ORDER BY Reports Builder 1 1 Oracle9i Reports 5 WHERE dept emp Oracle9i Reports 13

14 Select d.dname, e.ename From emp e, dept d Where e.deptno(+) = d.deptno 2 deptno Select deptno, dname from dept Select deptno, ename from emp Oracle9i Reports 1 2 Oracle9i Reports Oracle9i Reports Web Web JSP ' ' Oracle9i Reports Oracle9i Reports 14

15 Oracle9i Reports Oracle9i Reports Reports Builder 1 SUBSTR Reports Builder 1 Report Builder SUBSTR Oracle9i Reports 15

16 .RDF.REP.JSP.RDF.REP.REP.RDF Oracle9i Reports DML INSERT 2 Oracle9i Reports 16

17 Oracle9i Reports PL/SQL SRW.SET_TEXT_COLOR PL/SQL PL/SQL Web JSP Oracle9i Reports JavaServer Pages JSP Web Web Web Oracle9i Reports Web 1 Oracle9i Reports Web Web Java JSP Java JSP Web Oracle9i Reports Oracle9i Reports 17

18 Java PL/SQL.JSP Web JSP Web <rw:include> Oracle9i Reports JSP SUPPRESLAYOUT JSP Oracle Technology Network ( Oracle Technology Network Oracle9iAS Oracle9i Reports Oracle9i Reports 1 1 Oracle9i Reports 18

19 Oracle9i Forms Forms Builder Reports Builder Oracle9i Forms Oracle9i Forms Oracle9i Reports Oracle9i Reports Oracle9i Forms Reports Run_Report_Object Oracle9i Forms Oracle Technology Network ( Oracle9i Forms Server Report Integration Reports Builder RUNDEBUG NO JSP Reports Builder Reports Services http VALIDATTAG YES RWRUN RWSERVLET RECURSIVE_LOAD Oracle9i Reports 19

20 YES PL/SQL Oracle9i SQL Oracle9i Reports Oracle 1 ARRAYSIZE LONG CLOB BLOB LONGCHUNK Oracle9i Reports Oracle8i Oracle9i Server LONG LONG RAW CLOB BLOB PARAMFORM NO PostScript COPIES PostScript COPIES 1 Oracle9i Reports Oracle9i Reports / Web Reports Server Oracle9i Reports 20

21 Oracle9i Reports 21

22 A Reports Builder Builder rwbuilder.conf <trace tracefile="trace_file_name" traceopts="trace_all" tracemode="trace_replace"/> <ORACLE_HOME>/reports/log <hostname>-rwbuilder.trc RWBUILDER RWRUN tracefile=trace_file_name traceopts=trace_all tracemode=trace_replace rwbuilder.conf RWSERVER <servername.conf> <trace tracefile="trace_file_name" traceopts="trace_all" tracemode="trace_replace"/> <ORACLE_HOME>/reports/log <servername.trc> <severname>-<enginename>-<engineno>.trc RWSERVLET rwservlet.properties TRACEOPTS=TRACE_ALL TRACEFILE=rwservlet.trc TRACEMODE=TRACE_REPLACE TRACEFILE TRACEMODE TRACEOPTS TRACEFILE=<tracefile> tracefile Oracle9i Reports 22

23 TRACEMODE=<TRACE_APPEND TRACE_REPLACE> TRACE_APPEND TRACEOPTS=<TRACE_APP TRACE_BRK TRACE_DBG TRACE_DST TRACE ERR TRACE INF TRACE LOG TRACE_PLS TRACE_PRF TRACE_SQL TRACE_STA TRACE_TMS TRACE_WRN TRACE_ALL> TRACE_ERR - TRACE_PRF - TRACE_APP - TRACE_PLS - PL/SQL TRACE_SQL - SQL TRACE_TMS - TRACE_DST - TRACE_ALL - TRACEOPTS=(TRACE_APP, TRACE_PRF) Oracle9i Reports 23

24 B traceopts=all 14 emp LOG : Report: MODULE2 Logged onto server: Username: scott 16:15:59 APP ( Frame 16:15:59 APP. ( Text Boilerplate B_OR$BODY_SECTION 16:15:59 APP. ) Text Boilerplate B_OR$BODY_SECTION 16:15:59 APP. ( Generic Graphical Object B_1_SEC2 16:15:59 APP. ) Generic Graphical Object B_1_SEC2 16:15:59 APP. ( Text Boilerplate B_DATE1_SEC2 16:15:59 APP. ) Text Boilerplate B_DATE1_SEC2 16:15:59 APP. ( Text Boilerplate B_PAGENUM1_SEC2 16:15:59 APP. ) Text Boilerplate B_PAGENUM1_SEC2 16:15:59 APP. ( Text Field F_DATE1_SEC2 16:15:59 APP.. ( Database Column Name unknown 16:15:59 APP.. ) Database Column Name unknown 16:15:59 APP. ) Text Field F_DATE1_SEC2 16:15:59 APP ) Frame 16:15:59 APP ( Frame 16:15:59 APP. ( Frame M_G_DNAME_GRPFR 16:15:59 APP.. ( Repeating Frame R_G_DNAME 16:15:59 APP... ( Group G_dname Local Break: 0 Global Break: 0 16:15:59 APP... ( Query Q_1 16:15:59 SQL EXECUTE QUERY : select d.dname, e.ename from emp e, dept d where e.deptno(+)=d.deptno 16:15:59 APP... ) Query Q_1 16:15:59 APP... ) Group G_dname 16:15:59 APP... ( Text Field F_ENAME 16:15:59 APP... ( Database Column ename 16:15:59 APP... ) Database Column ename 16:15:59 APP... ) Text Field F_ENAME 16:15:59 APP... ( Text Field F_DNAME 16:15:59 APP... ( Database Column dname 16:15:59 APP... ) Database Column dname 16:15:59 APP... ) Text Field F_DNAME 16:15:59 APP... ( Group G_dname Local Break: 1 Global Break: 1 16:15:59 APP... ) Group G_dname 16:15:59 APP... ( Text Field F_ENAME 16:15:59 APP... ( Database Column ename 16:15:59 APP... ) Database Column ename 16:15:59 APP... ) Text Field F_ENAME Oracle9i Reports 24

25 Report Builder Profiler statistics Total Elapsed Time: 8.00 seconds Reports Time: 7.00 seconds (87.50% of TOTAL) ORACLE Time: 1.00 seconds (12.50% of TOTAL) UPI: 0.00 seconds SQL: 1.00 seconds TOTAL CPU Time used by process: N/A Oracle9i Reports 25

26 Oracle9i Reports : Christian Bauwens : Barry Hiern Danny Richardson James Safcik Oracle Corporation Tuning Oracle9i Reports Oracle Corporation World Headquarters 500 Oracle Parkway Redwood Shores, CA U.S.A. : : : Oracle Copyright 2002 Oracle Corporation All rights reserved.

Oracle9i

Oracle9i 2002 2 Oracle9i... 4... 4... 4 Oracle... 4 SQL... 6... 6... 6... 7... 7... 9... 9... 9 CUBE... 10... 11... 11... 11 OR... 12... 12... 14... 14... 15... 15... 16... 16... 18... 18... 18... 19...