untitled - PDF 無料ダウンロード

Oracle Direct Seminar <Insert Picture Here> SQL

Agenda SQL SQL SQL SQL 11g SQL FAQ Oracle Direct SQL Server MySQL PostgreSQL Access Application Server Oracle Database Oracle Developer/2000 Web Oracle Database http://www.oracle.com/lang/jp/direct/services.html Copyright 2009, Oracle. All rights reserved. 2

Top 5 Timed Events ~~~~~~~~~~~~~~~~~~ % Total Event Waits Time (s) Ela Time ---------------------------------- ------------ ----------- -------- db file sequential read 51,925 8,172 91.49 log file sync 29,367 386 4.32 db file parallel write 614 172 1.93 CPU time 141 1.58 log file parallel write 20,158 53.59 SQL / Copyright 2009, Oracle. All rights reserved. 4

< > SELECT SUM(getmisses) / SUM(gets) FROM v$rowcache 10 SHARED_POOL_SIZE < DB > physical reads physical reads direct physical reads direct(lob) Session logical reads 90 % DB_CACHE_SIZE Copyright 2009, Oracle. All rights reserved. 6

SQL B-tree Direct Seminar!! - - B Aoki rowid Baba rowid F B rowid rowid Fujita rowid Hiraga rowid rowid rowid M Morita rowid Nagao rowid rowid rowid S Sato rowid Suzuki rowid rowid rowid Copyright 2009, Oracle. All rights reserved. 10

SQL SQL SELECT,,sum( ) FROM GROUP BY CUBE(, ); ---------------------------------------------------- Id Operation Name Cost (%CPU) ---------------------------------------------------- 0 SELECT STATEMENT 4 (25) 1 SORT GROUP BY 4 (25) 2 GENERATE CUBE 4 (25) 3 SORT GROUP BY 4 (25) 4 TABLE ACCESS FULL ORDERS 3 (0) ---------------------------------------------------- SELECT,,sum( ) FROM GROUP BY, UNION SELECT null,,sum( ) FROM GROUP BY UNION SELECT,null,sum( ) FROM GROUP BY UNION SELECT null,null,sum( ) FROM ; ---------------------------------------------------- Id Operation Name Cost (%CPU) ---------------------------------------------------- 0 SELECT STATEMENT 19 (85) 1 SORT UNIQUE 19 (85) 2 UNION-ALL 3 HASH GROUP BY 5 (40) 4 TABLE ACCESS FULL ORDERS 3 (0) 5 HASH GROUP BY 5 (40) 6 TABLE ACCESS FULL ORDERS 3 (0) 7 HASH GROUP BY 5 (40) 8 TABLE ACCESS FULL ORDERS 3 (0) 9 SORT AGGREGATE 4 (25) 10 TABLE ACCESS FULL ORDERS 3 (0) ---------------------------------------------------- Copyright 2009, Oracle. All rights reserved. 11

SQL SELECT name FROM emp; SELECT name FROM EMP; SELECT name FROM emp; / / SELECT name FROM emp WHERE id = 1023 SELECT name FROM emp WHERE id = 3074 variable b1 number begin :b1 := 300; SQL end; / SELECT name FROM emp where id = :b1; Copyright 2009, Oracle. All rights reserved. 13

SQL SQL*PLUS AUTOTRACE Explain plan for <SQL> SQL V$SQL V$SQL_PLAN(9i ) Enterprise Manager 10g SQL*Plus AUTOTRACE 1. SYS PLUSTRACE SQL SQL> @%ORACLE_HOME% sqlplus admin plustrce.sql SQL> GRANT plustrace TO scott; 2. SQL (PLAN_TABLE) SQL> connect scott/tiger SQL> @%ORACLE_HOME% rdbms admin utlxplan.sql 3. AUTOTRACE ON SQL SQL> SET AUTOTRACE ON SQL> SELECT... Copyright 2009, Oracle. All rights reserved. 17

SQL Index FIRST_ROWS or ALL_ROWS /*+ */ SQL sales customer_id cust_id_indx Index SELECT /*+ INDEX(sales cust_id_indx) */ sales_date, sales_amount FROM sales WHERE customer_id=100; customers sales SELECT /*+ USE_HASH(s c) LEADING(c s) */ * FROM sales s, customers c WHERE s.customer_id=c.customer_id AND s.sales_amount > 1000; Copyright 2009, Oracle. All rights reserved. 18

SQL Q1. SQL SQL A1. SQL SQL 1 SQL 1 SQL SQL SQL SQL force_match TRUE Where SQL SQL SQL SQL> exec DBMS_SQLTUNE.ACCEPT_SQL_PROFILE - > (task_name => SQL_TUNING_1191230007828 - >,name => TEST_PROFILE - >,force_match => TRUE ); SQL Copyright 2009, Oracle. All rights reserved. 43

SQL Q6. SQL A6. Oracle Database 10g R2 1. dbms_sqltune.create_stgtab_sqlprof SQL 2. dbms_sqltune.pack_stgtab_sqlprof SQL 3. DataPump Export Dump 4. Dump Data Pump 5. dbms_sqltune.unpack_stgtab_sqlprof SQL Oracle Database 10g R1 SQL Oracle Index Export / Import SQL Copyright 2009, Oracle. All rights reserved. 46

SQL Q7. SQL A7. 2 SQL*Plus Autotrace 1. Autotrace ON SQL> set autotrace on 2. SQL Note SQL SQL Note SQL profile "SYS_SQLPROF_014564deb351c000" used for this statement Explain Plan 1. SQL Explain Plan SQL> EXPLAIN PLAN FOR SELECT * FROM emp; 2. SQL SQL> select plan_table_output from table(dbms_xplan.display()); Note SQL profile "SYS_SQLPROF_014564deb351c000" used for this statement Copyright 2009, Oracle. All rights reserved. 47

SQL Oracle Database Enterprise Edition 2 Option Database Diagnostics Pack Database Tuning Pack SQL AWR ( ) ADDM ( 1( SQL Access Advisor SQL SQL Tuning Advisor Diagnostics Pack Copyright 2009, Oracle. All rights reserved. 48

Oracle Direct Seminar 100 Wendy2010 11 12 2!! a. b. c. d. a,b,c,d http://www.oracle.com/technology/global/jp/membership/index.html ORD_SEMINAR_JP@ORACLE.COM 2009 11 12 Oracle Direct Copyright 2009, Oracle. All rights reserved. 50

OTN!! Oracle Technology Network(OTN) http://otn.oracle.co.jp/forum/index.jspa?categoryid=2 OTN OTN Oracle 100% http://www.oracle.com/technology/global/jp/ondemand/otn-seminar/index.html OTN OTN OTN Copyright 2009, Oracle. All rights reserved. 51