Chien Chung Shen, profile picture
Uploaded byChien Chung Shen
2,457 views

Oracle Database SQL Tuning Concept

Oracle SQL tuning involves optimizing SQL statements for better performance. Key aspects of SQL tuning include identifying SQL statements with high resource consumption or response times using tools like ADDM, AWR, and V$SQL. Statements can then be tuned by gathering accurate optimizer statistics, adjusting the execution plan using hints, rewriting the SQL, or changing indexes and tables. Tuning is done at both the design and execution stages.

Embed presentation

Oracle SQL Tuning ⼀一天之旅 申建忠(9i/10g/11g OCM)
SQL Statements • DML • SELECT • INSERT/UPDATE/DELETE/MERGE • DDL • CREATE/ALTER/DROP/TRUNCATE • DML on Data Dictionary • DCL • GRANT/REVOKE • TCL • COMMIT/REVOKE/SAVEPOINT
SQL Execution Flow Parse Execute Fetch (Select only) Server Process $ sqlplus hr/hr@orcl SQL> select last_name,salary from employees where employee_id=100; User Process Instance Database Oracle Server SQL Shared Pool Buffer Cache Log Buffer DML only Data files LAST_NAME SALARY ------------------------- ---------- King 24000 Result
Row Source Generator Syntax analysis Semantic analysis Query Transformer Estimator Plan Generator Data Dictionary Parser Optimizer optimizer statistics Hash(SQL) Soft Parse Hard Parse Parse Execution Plan Execution Plan Execution Plan Execution Plan Execution Plan Execution Plan Execution Plan Execution Plan Shared Pool (Library Cache) Server Process
效能問題種類 Performanc e OS DB Application Code (SQL) Design & Architecture 20% 60% 15% 5%
Tuning Cost and Benefit Time Low High Design Development Production Cost Benefit
Response Time Throughput Time Response time Wait time Service time Response time=Service time+wait time
RT Breakdown User AP Server DB Server Disk T1 T2 T3 T4 T5 T6 T7 T8 T9 T10 T11 T12 Time Parse Execute Physical I/O Execute Execute Fetch Process User Request Generate SQL Process SQL Result Generate Web Page Request Response Memory Logical I/O Firewall Physical I/O Execute T13 T14 Time Network filter
Which SQL need Tune • SQL敘述句的執⾏行效率達不到之前所設 定的要求。 • Response Time • 單⼀一SQL敘述句的整體反應時間 • Throughput • 單位時間裡，同時執⾏行的SQL敘述句數 量
Why SQL need Tune • 執⾏行多餘或無謂的操作 • 不必要的SORT操作 • 不必要的Table Join操作 • 使⽤用不必要的函數 • 發⽣生⼀一些等待事件(Wait event) • 不必要的等待 • Lock wait
OLTP/DW • OLTP • 避免Hard Parse • Response Time • Throughput • Index Access • DW • 不介意Hard Parse • Response Time • Full Table Scan
OLTP/DW A Data Warehouse(DW) is a relational database that is designed for query and analysis rather than for transaction processing. It usually contains historical data derived from transaction data, but it can include data from other sources. It separates analysis workload from transaction workload and enables an organization to consolidate data from several sources. In addition to a relational database, a data warehouse environment includes an extraction, transportation, transformation, and loading (ETL) solution, an online analytical processing (OLAP) engine, client analysis tools, and other applications that manage the process of gathering data and delivering it to business users. An Online Transaction Processing (OLTP) systems are characterized by high throughput, many users, and a mix of DML operations (insert, update, and delete) and queries.
Difference between OLTP and DW OLTP DW 3rd Normal Form 表格設計 3NF/Star Schema 多 索引 少 多 Join 中 Normalized DB 資料重覆 DeNormalized DB 少 衍⽣生與彙總 資料 多 事先定義 SELECT Ad Hoc 次數多 每次資料量少 DML 次數少 每次資料量⼤大 Response Time⼩小 Throughput⼤大 系統要求 Response Time⼩小
OLTP OLTP OLTP External Source DML DML DML DML DML DML DML Extract Transform Load Data Mart Data Mart Data Mart Data Warehouse OLAP BI Ad Hoc Query Report Operational Data Source OLTP與DW的關聯
SQL Tuning on 設計階段 • 在OLTP環境下，可以考慮在WHERE⼦子句 中，使⽤用BindVariable取代Literal，以避免 過多的Hard Parse。 • 避免Table與View進⾏行 Join，以避免產⽣生 Cartesian Join。 • 使⽤用命名規則，以增加SQL的可讀性與 可維護性。 • 指令的⼤大⼩小寫、注解的使⽤用 • 使⽤用Hint前，需要審慎評估。
Hard Parse - 1 SQL> select count(*) from hr.emp where job_id='SA_REP'; COUNT(*) ---------- 30 SQL> select count(*) from hr.emp where JOB_ID='SA_REP'; COUNT(*) ---------- 30 SQL> select sql_id,hash_value,plan_hash_value,executions,sql_text 2 from v$sql 3 where sql_text like 'select count(*) from hr.emp where %'; SQL_ID HASH_VALUE PLAN_HASH_VALUE EXECUTIONS ------------- ---------- --------------- ---------- SQL_TEXT -------------------------------------------------------------------------------------- 8rtytfkcsw4wd 2576225165 2083865914 1 select count(*) from hr.emp where job_id='SA_REP' asax0q4n558hw 676504092 2083865914 1 select count(*) from hr.emp where JOB_ID='SA_REP'
Hard Parse-2 SQL> select count(*) from hr.emp --在hr.emp之後有⼀一個不可⾒見字元n 2 where job_id='SA_REP'; COUNT(*) ---------- 30 SQL> SELECT sql_id,hash_value,executions,plan_hash_value,sql_text 2 FROM v$sql 3 WHERE sql_text like 'select count(*) from hr.emp where %'; SQL_ID HASH_VALUE PLAN_HASH_VALUE EXECUTIONS ------------- ---------- --------------- ---------- SQL_TEXT -------------------------------------------------------------------------------- 8rtytfkcsw4wd 2576225165 2083865914 1 select count(*) from hr.emp where job_id='SA_REP' asax0q4n558hw 676504092 2083865914 1 select count(*) from hr.emp where JOB_ID='SA_REP' 0v9928mstgq0g 4053260303 2083865914 1 select count(*) from hr.emp where job_id='SA_REP'
Hard Parse - 3 SQL> select last_name from hr.employees where employee_id=100; LAST_NAME ------------------------- King SQL> select last_name from hr.employees where employee_id=200; LAST_NAME ------------------------- Whalen SQL> SELECT hash_value,plan_hash_value,executions,sql_text 2 FROM v$sql 3 WHERE sql_text like 'select last_name from hr.employees where employee_id=%'; HASH_VALUE PLAN_HASH_VALUE EXECUTIONS ---------- --------------- ---------- SQL_TEXT ------------------------------------------------------------------------------------ 2627784799 1833546154 1 select last_name from hr.employees where employee_id=200 280342537 1833546154 1 select last_name from hr.employees where employee_id=100
Hard Parse - 4 SQL> variable empid number --此⽅方式僅適⽤用在sqlplus,sql developer等.不過java/NET也有類似語法 SQL> execute :empid := 101; SQL> select last_name from hr.employees where employee_id=:empid; LAST_NAME ------------------------- Kochhar SQL> execute :empid := 201; SQL> select last_name from hr.employees where employee_id=:empid; LAST_NAME ------------------------- Hartstein SQL> SELECT hash_value,plan_hash_value,executions sql_text 2 FROM v$sql 3 WHERE sql_text like 'select last_name from hr.employees where employee_id=%' HASH_VALUE PLAN_HASH_VALUE EXECUTIONS ---------- --------------- ---------- SQL_TEXT ---------------------------------------------------------------------------------------------- 476476418 1833546154 2 select last_name from hr.employees where employee_id=:empid 2627784799 1833546154 1 select last_name from hr.employees where employee_id=200 280342537 1833546154 1 select last_name from hr.employees where employee_id=100
SQL Tuning on 執⾏行階段 Response time=CPU time+Wait time 必要的CPU time+ 不必要的CPU time 必要的Wait time+ 不必要的Wait time
Which SQL were High Loading ? • ADDM Report • AWR Report(Enterprise Edition) • STATSPACK(Standard Edition) • V$SQL
ADDM
AWR Report1
AWR Report2
Resource V$SQL Response Time ELAPSED_TIME Service Time CPU_TIME Sort SORTS Total I/Os BUFFER_GETS Physical I/Os DISK_READS Row Processed ROW_PROCESSED Memory RUNTIME_MEMORY Executions EXECUTIONS Critical Resources
V$SQL SQL> SELECT * 2 FROM 3 (SELECT sql_id,elapsed_time,cpu_time,buffer_gets,disk_reads,executions 4 FROM v$sql 5 ORDER BY elapsed_time DESC,cpu_time DESC, 6 buffer_gets DESC,disk_reads DESC,executions DESC) 7 WHERE ROWNUM<=10; SQL_ID ELAPSED_TIME CPU_TIME BUFFER_GETS DISK_READS EXECUTIONS ------------- ------------ ---------- ----------- ---------- ---------- 6qyqqyg14bm17 4722430672 4566883727 1171553448 1169941510 1 0a59tkkpx31vx 57267687 52511021 6414812 895 7 cj8ytxm5hhm4y 82778799 83355256 105 1 10100000 01uy9sb7w8a9g 37054402 41994 714 83 1 cvn54b7yz0s8u 25761776 942843 47660 6616 4142 3ktacv9r56b51 18381236 1381807 109780 1048 6350 39m4sx9k63ba2 15383617 449923 22739 1635 4142 ga9j9xk5cy9s0 14530703 493937 24296 752 4142 c6awqs517jpj0 9019830 340949 13858 512 4142 8swypbbr0m372 7280083 802874 100473 764 6350 10 rows selected.
V$SQL_MONITOR SQL> SELECT * 2 FROM 3 (SELECT sql_id,elapsed_time,cpu_time,queuing_time,buffer_gets,disk_reads 4 FROM v$sql_monitor 5 ORDER BY elapsed_time DESC,cpu_time DESC,queuing_time DESC 6 buffer_gets DESC,disk_reads DESC) 7 WHERE ROWNUM<=10; SQL_ID ELAPSED_TIME CPU_TIME QUEUING_TIME BUFFER_GETS DISK_READS ------------- ------------ ---------- ------------ ----------- ---------- 6qyqqyg14bm17 5396549468 5221542205 0 1337837043 17458050 ak3huf5xbg5f6 313934168 304642686 0 48 0 05s9358mm6vrr 14956086 5728129 0 195423 1744 6gvch1xu9ca3g 12560842 1741735 0 44884 1441 0a59tkkpx31vx 11246557 9412567 0 1111033 324 0a59tkkpx31vx 9080340 7986787 0 889924 494 0a59tkkpx31vx 8483972 7845806 0 886621 0 5zruc4v6y32f9 8478735 2486622 0 312062 2210 0a59tkkpx31vx 8175980 7829809 0 890517 0 f6cz4n8y72xdc 7293541 1506770 0 54905 636 10 rows selected.
找出有問題的SQL 相關table/index的 statistics是否正確 ⺫⽬目前的執⾏行計劃是 否不需要調整 調整後執⾏行計劃是 否⽐比之前更好 收集相關statistics 調整執⾏行計劃 調整執⾏行計劃之外的相關結構 完成SQL調校 平⾏行處理 使⽤用分區 改寫SQL No No No Yes Yes Yes 建⽴立索引 建⽴立實體化視觀表
Optimizer Statistics TABLE NUM_ROWS BLOCKS AVG_ROW_LEN STALE_STATS INDEX BELVEL LEAF_BLOCKS CLUSTERING_FACTOR DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY NUM_ROWS COLUMN NUM_DISTINCT LOW_VALUE HIGH_VALUE NUM_NULLS DENSITY NUM_BUCKETS HISTOGRAM *_TAB_STATISTICS *_IND_STATISTICS *_TAB_COL_STATISTICS
Empty/Stale Statistics SQL> SELECT num_rows,blocks,avg_row_len,stale_stats 2 FROM dba_tab_statistics 3 WHERE owner='HR' and table_name='EMP'; NUM_ROWS BLOCKS AVG_ROW_LEN STA ---------- ---------- ----------- --- SQL> SELECT num_rows,blocks,avg_row_len,stale_stats 2 FROM dba_tab_statistics 3 WHERE owner='HR' and table_name='EMP'; NUM_ROWS BLOCKS AVG_ROW_LEN STA ---------- ---------- ----------- --- 70 5 69 YES 變動量超過10% SQL> SELECT COUNT(*) FROM hr.emp; COUNT(*) ---------- 107 Staled statistics Empty statistics
Manual Gather Statistics SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS('HR','EMP'); SQL> SELECT num_rows,blocks,avg_row_len,stale_stats 2 FROM dba_tab_statistics 3 WHERE owner='HR' and table_name='EMP'; NUM_ROWS BLOCKS AVG_ROW_LEN STA ---------- ---------- ----------- --- 107 5 69 NO SQL> EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS('HR'); SQL> EXECUTE DBMS_STATS.GATHER_DATABASE_STATS; SQL> EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS; SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECT_STATS;
Gather Statistics Options • Estimate Percentage • Invalidate Execution Plan • Stale Percentage • Pending Publish SQL> DESC dba_tab_stat_prefs Name Null? Type ----------------------------------- -------- ---------------------------- OWNER NOT NULL VARCHAR2(30) TABLE_NAME NOT NULL VARCHAR2(30) PREFERENCE_NAME VARCHAR2(30) PREFERENCE_VALUE VARCHAR2(1000)
Finding Execution Plan • EXPLAIN PLAN(PLAN_TABLE) • AUTOTRACE(PLAN_TABLE) • Shared Pool:Library Cache(V$SQL_PLAN) • AWR(DBA_HIST_SQL_PLAN) • SQL Tuning Set • SQL Plan Baseline • Tracefile • EVENT:10046/10053
Display Execution Plan • DBMS_XPLAN.DISPLAY • DBMS_XPLAN.DISPLAY_CURSOR • DBMS_XPLAN.DISPLAY_AWR • DBMS_XPLAN.DISPLAY_SQLSET • DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE • SET AUTOTRACE ON • DBMS_SQLTUNE.REPORT_SQL_MONITOR
EXPLAIN PLAN SQL> EXPLAIN PLAN SET STATEMENT_ID='frank_demo1' FOR 2 SELECT last_name,salary FROM hr.emp WHERE employee_id=100; Explained. SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(statement_id=>'frank_demo1')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------- Plan hash value: 3956160932 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 16 | 3 (0)| 00:00:01| |* 1 | TABLE ACCESS FULL | EMP | 1 | 16 | 3 (0)| 00:00:01| -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("EMPLOYEE_ID"=100)
AUTOTRACE SQL> SET AUTOTRACE ON SQL> SELECT last_name,salary FROM hr.emp WHERE employee_id=100; LAST_NAME SALARY ------------------------- ---------- King 24000 Execution Plan ---------------------------------------------------------- Plan hash value: 3956160932 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 16 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| EMP | 1 | 16 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("EMPLOYEE_ID"=100)
V$SQL_PLAN SQL> SELECT sql_id,child_number 2 FROM v$sql 3 WHERE sql_text='SELECT last_name,salary FROM hr.emp WHERE employee_id=100'; SQL_ID CHILD_NUMBER ------------- ------------ cj8ytxm5hhm4y 0 SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('cj8ytxm5hhm4y',0)); PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------- SQL_ID cj8ytxm5hhm4y, child number 0 ------------------------------------- SELECT last_name,salary FROM hr.emp WHERE employee_id=100 Plan hash value: 3956160932 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | |* 1 | TABLE ACCESS FULL| EMP | 1 | 16 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("EMPLOYEE_ID"=100)
AWR SQL> SELECT sql_id 2 FROM dba_hist_sqltext 3 WHERE sql_text='SELECT last_name,salary FROM hr.emp WHERE employee_id=100'; SQL_ID ------------- cj8ytxm5hhm4y SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('cj8ytxm5hhm4y')); PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------- SQL_ID cj8ytxm5hhm4y, child number 0 ------------------------------------- SELECT last_name,salary FROM hr.emp WHERE employee_id=100 Plan hash value: 3956160932 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | |* 1 | TABLE ACCESS FULL| EMP | 1 | 16 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("EMPLOYEE_ID"=100)
SQL Tracefile(RAW) SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER,LEVEL 8'; SQL> SELECT last_name,salary FROM hr.emp WHERE employee_id=100; LAST_NAME SALARY ------------------------- ---------- King 24000 SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF'; orcl_ora_15492.trc ===================== PARSING IN CURSOR #140636401352696 len=57 dep=0 uid=0 oct=3 lid=0 tim=1357281071350293 hv=3406318750 ad='78fb4328' sqlid='cj8ytxm5hhm4y' SELECT last_name,salary FROM hr.emp WHERE employee_id=100 END OF STMT PARSE #140636401352696:c=20997,e=161413,p=3,cr=138,cu=0,mis=1,r=0,dep=0,og=1,plh=3956160932,tim=1357281071350286 EXEC #140636401352696:c=0,e=98,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3956160932,tim=1357281071350483 WAIT #140636401352696: nam='SQL*Net message to client' ela= 4 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1357281071350537 FETCH #140636401352696:c=0,e=76,p=0,cr=2,cu=0,mis=0,r=1,dep=0,og=1,plh=3956160932,tim=1357281071350647 WAIT #140636401352696: nam='SQL*Net message from client' ela= 5227 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1357281071355930 FETCH #140636401352696:c=0,e=48,p=0,cr=2,cu=0,mis=0,r=0,dep=0,og=1,plh=3956160932,tim=1357281071356062 STAT #140636401352696 id=1 cnt=1 pid=0 pos=1 obj=77179 op='TABLE ACCESS FULL EMP (cr=4 pr=0 pw=0 time=118 us cost=3 size=16 card=1)' WAIT #140636401352696: nam='SQL*Net message to client' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1357281071356153 $ tkprof orcl_ora_15492.trc output1.txt
SQL ID: cj8ytxm5hhm4y Plan Hash: 3956160932 SELECT last_name,salary FROM hr.emp WHERE employee_id=100 call count cpu elapsed disk query current rows ----------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.04 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 0 4 0 1 ----------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.04 0 4 0 1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: SYS Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- --------------------------------------------------- 1 1 1 TABLE ACCESS FULL EMP (cr=4 pr=0 pw=0 time=118 us cost=3 size=16 card=1) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 2 0.00 0.00 SQL*Net message from client 2 17.86 17.87
10053 Trace SQL> SELECT sql_id 2 FROM dba_hist_sqltext 3 WHERE sql_text='SELECT last_name,salary FROM hr.emp WHERE employee_id=100'; SQL_ID ------------- cj8ytxm5hhm4y SQL> EXECUTE dbms_sqldiag.dump_trace( 2> p_sql_id=>'3jb04z19uj8jf', 3> p_child_number=>0, 4> p_component=>'Compiler', 5> p_file_id=>'Hello_frank'); SQL> SELECT value FROM v$diag_info WHERE name='Default Trace File'; VALUE ------------------------------------------------------------------------- /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3704_Hello_frank.trc
10053 Trace Content ============ Plan Table ============ -------------------------------------+-----------------------------------+ | Id | Operation | Name | Rows | Bytes | Cost | Time | -------------------------------------+-----------------------------------+ | 0 | SELECT STATEMENT | | | | 3 | | | 1 | TABLE ACCESS FULL | EMP | 1 | 40 | 3 | 00:00:01 | -------------------------------------+-----------------------------------+ Predicate Information: ---------------------- 1 - filter("EMPLOYEE_ID"=100)
V$SQL_MONITOR SQL> set pagesize 0 echo off timing off SQL> set linesize 1000 trimspool on trim on SQL> set long 2000000 longchunksize 2000000 feedback off SQL> spool sqlmonrpt_6qyqqyg14bm17.html SQL> SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(type=>’EM’,sql_id=>’6qyqqyg14bm17’) FROM dual; SQL> spool off
執⾏行計劃 --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 370M(100)| | | 1 | SORT AGGREGATE | | 1 | 17 | | | | 2 | NESTED LOOPS | | 5557 | 94469 | 370M (1)|999:59:59 | | 3 | TABLE ACCESS FULL| SALES | 918K| 4486K| 1233 (1)| 00:00:15 | |* 4 | TABLE ACCESS FULL| CUSTOMERS | 1 | 12 | 403 (1)| 00:00:05 | --------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - filter(("C"."CUST_FIRST_NAME"='Dina' AND "S"."CUST_ID"="C"."CUST_ID")) 3 4 2 1 SELECT COUNT(*) FROM frank.sales s,frank.customers c WHERE s.cust_id=c.cust_id AND c.cust_first_name=‘Dina’
• Data Access Method • Join Order • Join Method • 每個Row Source應該盡可能減少傳給下 ⼀一個Row Source的資料量 執⾏行計劃的調整順序
Data Access Method 使⽤用者提供rowid Table Access By User Rowid 讀取所有位在High Water Mark之下的Table blocks。 Table Access Full 先透過Index找到某些rows 的ROWID，然後讀取 ROWID所指向的table blocks。 Index Range Scan Index Unique Scan Index Full Scan Index Fast Full Scan Index Skip Scan
ROWID rowid為row的相對位置資訊 rowid顯⽰示為18 characters(A-Z,a-z,0-9,+,/共64個character編碼) ABCDEFGHIJ K L M N O P Q R S T U V W X Y Z a b c d e f g.... 0123456789 1011121314151617181920212223242526272829303132.... 前6個character表⽰示為segment id(data object id) 前3個character表⽰示為relative file id(row位在哪個datafile之上) 前6個character表⽰示為block id(row位在哪個block之上) 前3個character表⽰示為row number(該block的第幾筆row) SQL> SELECT dbms_rowid.rowid_object('AAASYtAAEAAAAKnABI') data_object_id, 2 dbms_rowid.rowid_relative_fno('AAASYtAAEAAAAKnABI') relative_file_number, 3 dbms_rowid.rowid_block_number('AAASYtAAEAAAAKnABI') block_number, 4 dbms_rowid.rowid_row_number('AAASYtAAEAAAAKnABI') row_number 5 FROm dual; DATA_OBJECT_ID RELATIVE_FILE_NUMBER BLOCK_NUMBER ROW_NUMBER -------------- -------------------- ------------ ---------- 75309 4 679 72
1 A A1 10 J J10 17 Q Q 2 B B2 19 S S19 3 C C3 7 G G7 11 K K 4 D D4 12 L L 21 U U 6 F F6 13 M M 22 V V 8 H H8 15 O O 24 X X 5 E E5 14 N N 23 W W23 9 I I9 16 P P 20 T T 18 R R18 Table Access By User Rowid
Table Access By User Rowid SQL> SELECT object_id,object_name 2> FROM hr.big1 3> WHERE rowid='AAASYtAAEAAAAKnABI'; OBJECT_ID OBJECT_NAME ---------- ------------------------------ 12345 ALERT_QUE_N ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 42 | 1 (0)| 00:00 |* 1 | TABLE ACCESS BY USER ROWID| BIG1 | 1 | 42 | 1 (0)| 00:00 ------------------------------------------------------------------------------
Full Table Scan 1 A A1 10 J J10 17 Q Q 2 B B2 19 S S19 3 C C3 7 G G7 11 K K 4 D D4 12 L L 21 U U 6 F F6 13 M M 22 V V 8 H H8 15 O O 24 X X 5 E E5 14 N N 23 W W23 9 I I9 16 P P 20 T T 18 R R18
Full Table Scan SQL> SELECT rowid,object_id,object_name 2> FROM hr.big1 3> WHERE object_id=12345; ROWID OBJECT_ID OBJECT_NAME ------------------ ---------- ------------------------------ AAASYtAAEAAAAKnABI 12345 ALERT_QUE_N -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 30 | 289 (1)| 00:00:04 | |* 1 | TABLE ACCESS FULL| BIG1 | 1 | 30 | 289 (1)| 00:00:04 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_ID"=12345)
Index Unique Scan branch branch root 1 rowid 2 rowid 3 rowid 4 rowid 5 rowid 6 rowid 7 rowid 8 rowid 9 rowid 10 rowid 11 rowid 12 rowid 13 rowid 14 rowid 15 rowid 16 rowid 17 rowid 18 rowid 19 rowid 20 rowid 21 rowid 22 rowid 23 rowid 24 rowid 1 A A1 10 J J10 17 Q Q 2 B B2 19 S S19 3 C C3 7 G G7 11 K K 4 D D4 12 L L 21 U U 6 F F6 13 M M 22 V V 8 H H8 15 O O 24 X X 5 E E5 14 N N 23 W W23 9 I I9 16 P P 20 T T 18 R R18
Index Unique Scan SQL> SELECT rowid,object_id,object_name 2> FROM hr.big1 3> WHERE object_id=12345; ROWID OBJECT_ID OBJECT_NAME ------------------ ---------- ------------------------------ AAASYtAAEAAAAKnABI 12345 ALERT_QUE_N ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 42 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| BIG1 | 1 | 42 | 2 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | BIG1_IDX1 | 1 | | 1 (0)| 00:00:01 |　　---------------------------------------------------------------------------------------- - Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=12345)
Index Range Scan Root branch branch 1 rowid 2 rowid 3 rowid 4 rowid 5 rowid 6 rowid 7 rowid 8 rowid 9 rowid 10 rowid 11 rowid 12 rowid 13 rowid 14 rowid 15 rowid 16 rowid 17 rowid 18 rowid 19 rowid 20 rowid 21 rowid 22 rowid 23 rowid 24 rowid 6 F F6 13 M M 22 V V 8 H H8 15 O O 24 X X 5 E E5 14 N N 23 W W23 9 I I9 16 P P 20 T T 1 A A1 10 J J10 17 Q Q 2 B B2 19 S S19 3 C C3 7 G G7 11 K K 4 D D4 12 L L 21 U U 18 R R18
Index Range Scan SQL> SELECT rowid,object_id,object_name 2> FROM hr.big1 3> WHERE object_id=12345; ROWID OBJECT_ID OBJECT_NAME ------------------ ---------- ------------------------------ AAASYtAAEAAAAKnABI 12345 ALERT_QUE_N ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 42 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| BIG1 | 1 | 42 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | BIG1_IDX1 | 1 | | 1 (0)| 00:00:01 |　　---------------------------------------------------------------------------------------- - Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=12345)
Index Full Scan branch branch root 1 rowid 2 rowid 3 rowid 4 rowid 5 rowid 6 rowid 7 rowid 8 rowid 9 rowid 10 rowid 11 rowid 12 rowid 13 rowid 14 rowid 15 rowid 16 rowid 17 rowid 18 rowid 19 rowid 20 rowid 21 rowid 22 rowid 23 rowid 24 rowid
Index Full Scan SQL> select object_id from hr.big1 order by object_id; OBJECT_ID --------- 2 3 4 5 --其餘省略 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 72476 | 353K| 42 (0)| 00:00:01 | | 1 | INDEX FULL SCAN | BIG1_IDX1 | 72476 | 353K| 42 (0)| 00:00:01 | -----------------------------------------------------------------------------------
Index Fast Full Scan branch branch root 1 rowid 2 rowid 3 rowid 4 rowid 5 rowid 6 rowid 7 rowid 8 rowid 9 rowid 10 rowid 11 rowid 12 rowid 13 rowid 14 rowid 15 rowid 16 rowid 17 rowid 18 rowid 19 rowid 20 rowid 21 rowid 22 rowid 23 rowid 24 rowid root 1 rowid 2 rowid 3 rowid 4 rowid 5 rowid 6 rowid 7 rowid 8 rowid 9 rowid 10 rowid 11 rowid 12 rowid branch 22 rowid 23 rowid 24 rowid 16 rowid 17 rowid 18 rowid 13 rowid 14 rowid 15 rowid branch
Index Fast Full Scan SQL> select object_id from hr.big1; OBJECT_ID --------- 23456 12345 45678 34567 --其餘省略 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 72476 | 353K| 42 (0)| 00:00:01 | | 1 | INDEX FAST FULL SCAN| BIG1_IDX1 | 72476 | 353K| 42 (0)| 00:00:01 | -----------------------------------------------------------------------------------
Table Join Method • Nested Loop Join • Sort Merge Join • Hash Join • Cartesian Join
Table Join Order • ⼀一次Join操作只能結合兩個tables • 如果有4個table需要join • 則可能出現4!=24 join order • 盡量讓最⼩小的兩個table先進⾏行結合
A B 1 11 3 33 2 22 Nested Loop Join C D 33 10 44 20 55 30 11 40 11 50 22 60 A B C D 3 33 33 10 1 11 11 40 1 11 11 50 2 22 22 60 FROM t1 JOIN t2 ON (t1.b=t2.c); t1 t2
A B 1 11 3 33 2 22 Sort Merge Join C D 33 10 44 20 55 30 11 40 11 50 22 60 t1 t2 A B 1 11 2 22 3 33 C D 11 40 11 50 22 60 33 10 44 20 55 30 A B C D 1 11 11 40 1 11 11 50 2 22 22 60 3 33 33 10 FROM t1 JOIN t2 ON (t1.b=t2.c); sort sort
A B C D 1 11 11 40 1 11 11 50 2 22 22 60 3 33 33 10 A B 1 11 3 33 2 22 Hash Join C D 33 10 44 20 55 30 11 40 11 50 22 60 FROM t1 JOIN t2 ON (t1.b=t2.c); t1 t2 33 11 22 hash_area_size Build hash table Probing hash table1 2 3
A B 1 11 3 33 2 22 Cross Join C D 33 10 44 20 55 30 11 40 11 50 22 60 FROM t1 CROSS JOIN t2; t1 t2 A B C D 1 11 33 10 1 11 44 20 1 11 55 30 1 11 11 40 1 11 11 50 1 11 22 60 3 33 33 10 3 33 44 20 3 33 55 30 3 33 11 40 3 33 11 50 3 33 22 60 2 22 33 10 2 22 44 20 2 22 55 30 2 22 11 40 2 22 11 50 2 22 22 60
A B 1 11 3 33 2 22 4 66 Inner Join C D 33 10 44 20 55 30 11 40 11 50 22 60 FROM t1 JOIN t2 ON (t1.B=t2.C) t1 t2 A B C D 1 11 11 40 1 11 11 50 3 33 33 10 2 22 22 60
A B 1 11 3 33 2 22 4 66 Left Outer Join C D 33 10 44 20 55 30 11 40 11 50 22 60 FROM t1 LEFT OUTER JOIN t2 ON (t1.B=t2.C) t1 t2 A B C D 1 11 11 40 1 11 11 50 3 33 33 10 2 22 22 60 4 66
A B 1 11 3 33 2 22 4 66 Right Outer Join C D 33 10 44 20 55 30 11 40 11 50 22 60 FROM t1 RIGHT OUTER JOIN t2 ON (t1.B=t2.C) t1 t2 A B C D 1 11 11 40 1 11 11 50 3 33 33 10 2 22 22 60 44 20 55 30
A B 1 11 3 33 2 22 4 66 Full Outer Join C D 33 10 44 20 55 30 11 40 11 50 22 60 FROM t1 FULL OUTER JOIN t2 ON (t1.B=t2.C) t1 t2 A B C D 1 11 11 40 1 11 11 50 3 33 33 10 2 22 22 60 4 66 44 20 55 30
--------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 370M(100)| | | 1 | SORT AGGREGATE | | 1 | 17 | | | | 2 | NESTED LOOPS | | 5557 | 94469 | 370M (1)|999:59:59 | | 3 | TABLE ACCESS FULL| SALES | 918K| 4486K| 1233 (1)| 00:00:15 | |* 4 | TABLE ACCESS FULL| CUSTOMERS | 1 | 12 | 403 (1)| 00:00:05 | --------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - filter(("C"."CUST_FIRST_NAME"='Dina' AND "S"."CUST_ID"="C"."CUST_ID")) 解析執⾏行計劃 Data Access Method Join Method Join Order (SALES為driving table) 3 4 2 1 SELECT COUNT(*) FROM frank.sales s,frank.customers c WHERE s.cust_id=c.cust_id AND c.cust_first_name=‘Dina’
--------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows| Bytes |Cost (%CPU)|Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 6436K(100)| | | 1 | SORT AGGREGATE | | 1 | 17 | | | | 2 | NESTED LOOPS | | | | | | | 3 | NESTED LOOPS | |5557 | 94469 | 6436K (1)|21:27:13 | | 4 | TABLE ACCESS FULL | SALES | 918K| 4486K| 1233 (1)|00:00:15 | |* 5 | INDEX RANGE SCAN | CUST_FNAME_IDX |1 | | 1 (0)|00:00:01 | |* 6 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 1 | 12 | 2 (0)|00:00:01 | --------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("C"."CUST_FIRST_NAME"='Dina') 6 - filter("S"."CUST_ID"="C"."CUST_ID") 建⽴立index在WERE⼦子句 存取customers的⽅方法 由Full Table Scan改為 Index Scan Cost由 370M降到 6 1 2 3 4 5 Time由999:59:59 降為21:27:13
-------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows| Bytes|Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | |6435K(100) | | | 1 | SORT AGGREGATE | | 1 | 17| | | | 2 | NESTED LOOPS | | | | | | | 3 | NESTED LOOPS | |5557 | 94469|6435K (1) | 21:27:05 | | 4 | INDEX FAST FULL SCAN | SALES_CUSTID_IDX| 918K| 4486K| 535 (2) | 00:00:07 | |* 5 | INDEX RANGE SCAN | CUST_FNAME_IDX | 1 | | 1 (0) | 00:00:01 | |* 6 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 1 | 12| 2 (0) | 00:00:01 | --------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("C"."CUST_FIRST_NAME"='Dina') 6 - filter("S"."CUST_ID"="C"."CUST_ID") 建⽴立index在JOIN⼦子句 Cost由6436K降 到6435K Time由21:27:43 降為21:27:05 6 1 2 3 4 5
-------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows| Bytes|Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | |1838K(100) | | | 1 | SORT AGGREGATE | | 1 | 17| | | | 2 | NESTED LOOPS | | | | | | | 3 | NESTED LOOPS | |5557 | 94469|1838K (1) | 06:07:47 | | 4 | INDEX FAST FULL SCAN | SALES_CUSTID_IDX| 918K| 4486K| 535 (2) | 00:00:07 | |* 5 | INDEX RANGE SCAN | CUST_CUSTID_IDX | 1 | | 1 (0) | 00:00:01 | |* 6 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 1 | 12| 2 (0) | 00:00:01 | --------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("S"."CUST_ID"="C"."CUST_ID") 6 - filter("C"."CUST_FIRST_NAME"='Dina') 建⽴立index在JOIN⼦子句 Cost由6436K降 到1838K Time由21:27:43 降為06:07:47 6 1 2 3 4 5 不使⽤用CUST_FNAME_IDX
--------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | |54544 (100)| | | 1 | SORT AGGREGATE | | 1 | 17 | | | | 2 | NESTED LOOPS | | 5557 | 94469 |54544 (1)| 00:10:55| |* 3 | TABLE ACCESS BY INDEX ROWID|CUSTOMERS | 43 | 516 |54489 (1)| 00:10:54| | 4 | INDEX FULL SCAN |CUST_CUSTID_IDX |55500 | | 125 (1)| 00:00:02| |* 5 | INDEX RANGE SCAN |SALES_CUSTID_IDX| 130 | 650 | 2 (0)| 00:00:01| --------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("C"."CUST_FIRST_NAME"='Dina') 5 - access("S"."CUST_ID"="C"."CUST_ID") 使⽤用customers當做driving table Cost由 1838K降為54544 Time由06:07:47 降為00:10:55 5 1 2 3 4 Join Order (CUSTOMERS為driving table) 不使⽤用CUST_FNAME_IDX
同時變更Data Access Method與Join Order ---------------------------------------------------------------------------------------------- | Id | Operation |Name | Rows| Bytes| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 93 (100)| | | 1 | SORT AGGREGATE | | 1| 17 | | | | 2 | NESTED LOOPS | | 5557| 94469| 93 (0)|00:00:02 | | 3 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 43| 516 | 7 (0)|00:00:01 | |* 4 | INDEX RANGE SCAN |CUSTOMERS_FNAME_IDX| 43| | 1 (0)|00:00:01 | |* 5 | INDEX RANGE SCAN |SALES_CUSTID_IDX | 130| 650| 2 (0)|00:00:01 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("C"."CUST_FIRST_NAME"='Dina') 5 - access("S"."CUST_ID"="C"."CUST_ID") SELECT COUNT(*) FROM frank.sales s,frank.customers c WHERE s.cust_id=c.cust_id AND c.cust_first_name=‘Dina’ 3 5 2 1 4 Cost由1838K 減為93 Time由06:07:47 減為00:00:02 Join Order (CUSTOMERS為driving table)
---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 547 (100)| | | 1 | SORT AGGREGATE | | 1 | 17 | | | |* 2 | HASH JOIN | | 5557 | 94469 | 547 (1)| 00:00:07 | | 3 | TABLE ACCESS BY INDEX ROWID|CUSTOMERS | 43 | 516 | 7 (1)| 00:00:01 | |* 4 | INDEX FULL SCAN |CUST_FNAME_IDX | 43 | | 1 (1)| 00:00:01 | | 5 | INDEX FULL SCAN |SALES_CUSTID_IDX| 918K| 4486K| 1958 (1)| 00:00:07 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("S"."CUST_ID"="C"."CUST_ID") 4 - access("C"."CUST_FIRST_NAME"='Dina') 變更Join Method-hash join 由Nested Loop 改為Hash Join Cost由93增 加到547 Time由00:00:02 增加到00:00:07 5 1 2 3 4
變更Join Method-sort merge ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes| Cost (%CPU)|Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1966 (100)| | | 1 | SORT AGGREGATE | | 1 | 17 | | | | 2 | MERGE JOIN | | 5557 | 94469| 1966 (1)|00:00:24 | | 3 | INDEX FULL SCAN |SALES_CUSTID_IDX| 918K| 4486K| 1958 (1)|00:00:24 | |* 4 | SORT JOIN | | 43 | 516 | 8 (13)|00:00:01 | | 5 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 43 | 516 | 7 (1)|00:00:01 | |* 6 | INDEX FULL SCAN | CUST_FNAME_IDX |55500 | | 1 (1)|00:00:01 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("S"."CUST_ID"="C"."CUST_ID") filter("S"."CUST_ID"="C"."CUST_ID") 6 - access("C"."CUST_FIRST_NAME"='Dina') 改為Sort Merge Join Cost由93增加 到1966 Time由00:00:02 增加到00:00:24 4 1 2 3 5 6
使⽤用Index的建議 • 是否需要建⽴立 • 建⽴立在哪些欄位 • 單⼀一欄位或複合欄位 • 建⽴立何種形態 • B-Tree • Bitmap • Unique • Non-Unique
What is INDEX • An index is an optional structure, associated with a table or table cluster, that can sometimes speed data access. By creating an index on one or more columns of a table, you gain the ability in some cases to retrieve a small set of randomly distributed rows from the table. Indexes are one of many means of reducing disk I/O. Oracle® Database Concepts 11g Release 2 (11.2) -3 Indexes and Index-Organized Tables
• Index or Not Index • Index Cost • Low Selectivity(BTree Index) • Which columns • FROM/WHERE clause • Single/Multiple Column • What Type • BTree • BitMap • Uniqueness • Non-Unique • Unique
其他Data Access Method • Partitioned Table • Cluster Table • Index Cluster • Hash Cluster • Single Hash Cluster • Index Organized Table • Materialized view • Materialized view log
Q&A

More Related Content

PPTX
Oracle sql high performance tuning
byGuy Harrison
 
PPTX
Oracle database performance tuning
byYogiji Creations
 
PDF
Performance Stability, Tips and Tricks and Underscores
byJitendra Singh
 
PPTX
SQL Tuning 101
byCarlos Sierra
 
PPTX
Explain the explain_plan
byMaria Colgan
 
PDF
Exploring Oracle Database Performance Tuning Best Practices for DBAs and Deve...
byAaron Shilo
 
PDF
Oracle db performance tuning
bySimon Huang
 
PPT
Oracle Applications R12 Architecture
byViveka Solutions
 
Oracle sql high performance tuning
byGuy Harrison
 
Oracle database performance tuning
byYogiji Creations
 
Performance Stability, Tips and Tricks and Underscores
byJitendra Singh
 
SQL Tuning 101
byCarlos Sierra
 
Explain the explain_plan
byMaria Colgan
 
Exploring Oracle Database Performance Tuning Best Practices for DBAs and Deve...
byAaron Shilo
 
Oracle db performance tuning
bySimon Huang
 
Oracle Applications R12 Architecture
byViveka Solutions
 

What's hot

PDF
Oracle Database Performance Tuning Advanced Features and Best Practices for DBAs
byZohar Elkayam
 
PDF
Oracle Performance Tuning Fundamentals
byEnkitec
 
PPT
Your tuning arsenal: AWR, ADDM, ASH, Metrics and Advisors
byJohn Kanagaraj
 
PDF
Tanel Poder - Performance stories from Exadata Migrations
byTanel Poder
 
PDF
Oracle Database Management - Backup/Recovery
byChien Chung Shen
 
PPTX
Understanding SQL Trace, TKPROF and Execution Plan for beginners
byCarlos Sierra
 
PDF
Analyzing and Interpreting AWR
bypasalapudi
 
PDF
Oracle Database Performance Tuning Concept
byChien Chung Shen
 
PDF
Oracle statistics by example
byMauro Pagano
 
PDF
Average Active Sessions RMOUG2007
byJohn Beresniewicz
 
PDF
Oracle使用者安全設定
byChien Chung Shen
 
PDF
Tanel Poder - Troubleshooting Complex Oracle Performance Issues - Part 2
byTanel Poder
 
PDF
Same plan different performance
byMauro Pagano
 
PDF
Awr + 12c performance tuning
byAiougVizagChapter
 
PPTX
All of the Performance Tuning Features in Oracle SQL Developer
byJeff Smith
 
PDF
Oracle 表格介紹
byChien Chung Shen
 
PDF
Oracle管理藝術第1章 在Linux作業體統安裝Oracle 11g
byChien Chung Shen
 
PPTX
Understanding my database through SQL*Plus using the free tool eDB360
byCarlos Sierra
 
PPTX
SQL Plan Directives explained
byMauro Pagano
 
PDF
Oracle 資料庫建立
byChien Chung Shen
 
Oracle Database Performance Tuning Advanced Features and Best Practices for DBAs
byZohar Elkayam
 
Oracle Performance Tuning Fundamentals
byEnkitec
 
Your tuning arsenal: AWR, ADDM, ASH, Metrics and Advisors
byJohn Kanagaraj
 
Tanel Poder - Performance stories from Exadata Migrations
byTanel Poder
 
Oracle Database Management - Backup/Recovery
byChien Chung Shen
 
Understanding SQL Trace, TKPROF and Execution Plan for beginners
byCarlos Sierra
 
Analyzing and Interpreting AWR
bypasalapudi
 
Oracle Database Performance Tuning Concept
byChien Chung Shen
 
Oracle statistics by example
byMauro Pagano
 
Average Active Sessions RMOUG2007
byJohn Beresniewicz
 
Oracle使用者安全設定
byChien Chung Shen
 
Tanel Poder - Troubleshooting Complex Oracle Performance Issues - Part 2
byTanel Poder
 
Same plan different performance
byMauro Pagano
 
Awr + 12c performance tuning
byAiougVizagChapter
 
All of the Performance Tuning Features in Oracle SQL Developer
byJeff Smith
 
Oracle 表格介紹
byChien Chung Shen
 
Oracle管理藝術第1章 在Linux作業體統安裝Oracle 11g
byChien Chung Shen
 
Understanding my database through SQL*Plus using the free tool eDB360
byCarlos Sierra
 
SQL Plan Directives explained
byMauro Pagano
 
Oracle 資料庫建立
byChien Chung Shen
 

Viewers also liked

PDF
Oracle Index
byJongwon
 
PDF
Oracle SQL 1 Day Tutorial
byChien Chung Shen
 
PDF
10g rac asm
byVictor
 
PDF
Understanding index
byChien Chung Shen
 
PPTX
Hadoop Essential for Oracle Professionals
byChien Chung Shen
 
PDF
Oracle Database Undo Segment Operation Concept
byChien Chung Shen
 
PDF
Oracle Database Management Basic 1
byChien Chung Shen
 
PPT
Hfm intro v2
byAmit Sharma
 
PPT
Hyperion analyzer 31 july
byAmit Sharma
 
PDF
Bisp training calendar jan 2012
byAmit Sharma
 
PPT
Hyperion Planning Class
byAmit Sharma
 
PDF
Getting started-with-oracle-so a- lab 11
byAmit Sharma
 
PPT
Hfm intro v2
byAmit Sharma
 
PPTX
Reaching Out From PL/SQL (OPP 2010)
byLucas Jellema
 
PPT
02 Essbase
byAmit Sharma
 
PDF
Olap fundamentals
byAmit Sharma
 
PPT
Expert talk
byAmit Sharma
 
Oracle Index
byJongwon
 
Oracle SQL 1 Day Tutorial
byChien Chung Shen
 
10g rac asm
byVictor
 
Understanding index
byChien Chung Shen
 
Hadoop Essential for Oracle Professionals
byChien Chung Shen
 
Oracle Database Undo Segment Operation Concept
byChien Chung Shen
 
Oracle Database Management Basic 1
byChien Chung Shen
 
Hfm intro v2
byAmit Sharma
 
Hyperion analyzer 31 july
byAmit Sharma
 
Bisp training calendar jan 2012
byAmit Sharma
 
Hyperion Planning Class
byAmit Sharma
 
Getting started-with-oracle-so a- lab 11
byAmit Sharma
 
Hfm intro v2
byAmit Sharma
 
Reaching Out From PL/SQL (OPP 2010)
byLucas Jellema
 
02 Essbase
byAmit Sharma
 
Olap fundamentals
byAmit Sharma
 
Expert talk
byAmit Sharma
 

Similar to Oracle Database SQL Tuning Concept

PDF
Mssql to oracle
byChien Chung Shen
 
PPTX
Oracle performance tuning_sfsf
byMao Geng
 
PDF
Microsoft SQL Server Query Tuning
byMark Ginnebaugh
 
PPTX
SQL Server Performance Tuning with DMVs
byFranklin Yamamoto
 
PPTX
Ten query tuning techniques every SQL Server programmer should know
byKevin Kline
 
PDF
Advanced tips for making Oracle databases faster
bySolarWinds
 
PDF
Performance Tuning Tool01-Statspack
byZhaoyang Wang
 
PDF
SQL Server Performance Analysis
byEduardo Castro
 
PDF
Ajuste (tuning) del rendimiento de SQL Server 2008
byEduardo Castro
 
PPT
Performance Tuning With Oracle ASH and AWR. Part 1 How And What
byudaymoogala
 
PPSX
Barun_Practical_and_Efficient_SQL_Performance_Tuning
byVlado Barun
 
PPTX
Sql and PL/SQL Best Practices I
byCarlos Oliveira
 
PPTX
Perth APAC Groundbreakers tour - SQL Techniques
byConnor McDonald
 
PDF
An Approach to Sql tuning - Part 1
byNavneet Upneja
 
PPTX
Oracle Database Performance Tuning Basics
bynitin anjankar
 
PPTX
Qure Tech Presentation
bysqlserver.co.il
 
PDF
SQL Tuning04-Interpreting Execution Plans
byZhaoyang Wang
 
PPT
261197832 8-performance-tuning-part i
byNaviSoft
 
PPTX
Hidden Gems of Performance Tuning: Hierarchical Profiler and DML Trigger Opti...
byMichael Rosenblum
 
PDF
Practical SQL query monitoring and optimization
byIvo Andreev
 
Mssql to oracle
byChien Chung Shen
 
Oracle performance tuning_sfsf
byMao Geng
 
Microsoft SQL Server Query Tuning
byMark Ginnebaugh
 
SQL Server Performance Tuning with DMVs
byFranklin Yamamoto
 
Ten query tuning techniques every SQL Server programmer should know
byKevin Kline
 
Advanced tips for making Oracle databases faster
bySolarWinds
 
Performance Tuning Tool01-Statspack
byZhaoyang Wang
 
SQL Server Performance Analysis
byEduardo Castro
 
Ajuste (tuning) del rendimiento de SQL Server 2008
byEduardo Castro
 
Performance Tuning With Oracle ASH and AWR. Part 1 How And What
byudaymoogala
 
Barun_Practical_and_Efficient_SQL_Performance_Tuning
byVlado Barun
 
Sql and PL/SQL Best Practices I
byCarlos Oliveira
 
Perth APAC Groundbreakers tour - SQL Techniques
byConnor McDonald
 
An Approach to Sql tuning - Part 1
byNavneet Upneja
 
Oracle Database Performance Tuning Basics
bynitin anjankar
 
Qure Tech Presentation
bysqlserver.co.il
 
SQL Tuning04-Interpreting Execution Plans
byZhaoyang Wang
 
261197832 8-performance-tuning-part i
byNaviSoft
 
Hidden Gems of Performance Tuning: Hierarchical Profiler and DML Trigger Opti...
byMichael Rosenblum
 
Practical SQL query monitoring and optimization
byIvo Andreev
 

More from Chien Chung Shen

PDF
Databases on AWS
byChien Chung Shen
 
PDF
Awsomeday ntc
byChien Chung Shen
 
PDF
Oracle 索引介紹
byChien Chung Shen
 
PDF
Oracle Tablespace介紹
byChien Chung Shen
 
PDF
Oracle 資料庫檔案介紹
byChien Chung Shen
 
PDF
Oracle SGA 介紹
byChien Chung Shen
 
PDF
Oracle Instance 介紹
byChien Chung Shen
 
PDF
MySQL SQL Tutorial
byChien Chung Shen
 
PDF
Oracle 12cR1 In-Memory Column Store
byChien Chung Shen
 
Databases on AWS
byChien Chung Shen
 
Awsomeday ntc
byChien Chung Shen
 
Oracle 索引介紹
byChien Chung Shen
 
Oracle Tablespace介紹
byChien Chung Shen
 
Oracle 資料庫檔案介紹
byChien Chung Shen
 
Oracle SGA 介紹
byChien Chung Shen
 
Oracle Instance 介紹
byChien Chung Shen
 
MySQL SQL Tutorial
byChien Chung Shen
 
Oracle 12cR1 In-Memory Column Store
byChien Chung Shen
 

Recently uploaded

PDF
HTML-Unit 4 css introduction hi my name is css and i am from html - CSS.pdf
byrssharma6396
 
PPTX
Exploring Quantum Computing Simulation Tools and Fundamentals Algorithms.pptx
byfarhanafayk
 
PDF
BCS501 Module-05 Software Engineering and Project Management.pdf
byjaadu6918
 
PPTX
Harshita-Puri PORTFOLIO AN ASPIRING DATA ANALYST
bySaloniNarwal
 
PDF
The Best Data Pipeline Tools in 2025: Automating Your Data Stack
bySkyvia
 
PDF
Kate Scrivens-OECD-Community well-being and climate risks.pdf
byStatsCommunications
 
PPTX
[DSC Europe 25] Stan Sokorac - New Generation of AI Computers.pptx
byDataScienceConferenc1
 
PPTX
[DSC Europe 25] Andy Cotgreave - Nothing is new in analytics.pptx
byDataScienceConferenc1
 
PPTX
[DSC Europe 25] Max Talanov - Non digital NNs.pptx
byDataScienceConferenc1
 
PPT
How To Write A Scientific Paper- IMRAD pattern
bydrmeenajain
 
PDF
Data+Security+Masterclass Presentation.pdf
byckurt9676
 
PDF
[DSC Europe 25] Nikola Rajovic - Hardware Technologies Under the Hood: RISC-V...
byDataScienceConferenc1
 
PPTX
[DSC Europe 25] Dusan Jovicic - AI Story: From on-prem to cloud and back agai...
byDataScienceConferenc1
 
PPTX
INTRODUCTION-TO-PHILOSOPHY-OF-HUMAN-PERSON (1).pptx
byHaydenKasey
 
PPTX
GRADE 8 MATH - Q3 - LESSON 1 -LINEAR EQUATION IN ONE VARIABLE.pptx
byEdlynMalangPanti
 
PPTX
Arrays (Lists) in PythonaSasaSAsASAsaSA.pptx
bybinarybombers1978
 
PPT
Perceptron and Activation Function in deep learning
byShrideviS7
 
PPTX
CC-105-Semi-Final-Topicdsgsdvsdgsdb.pptx
bykhajezjhozhua131
 
PDF
OPPOTUS - Malaysians on Malaysia (MOM) Q3 2025
byOppotus
 
PPTX
Orientation-on-ARAL-Check-in-Assessment.pptx
byJenTeruel1
 
HTML-Unit 4 css introduction hi my name is css and i am from html - CSS.pdf
byrssharma6396
 
Exploring Quantum Computing Simulation Tools and Fundamentals Algorithms.pptx
byfarhanafayk
 
BCS501 Module-05 Software Engineering and Project Management.pdf
byjaadu6918
 
Harshita-Puri PORTFOLIO AN ASPIRING DATA ANALYST
bySaloniNarwal
 
The Best Data Pipeline Tools in 2025: Automating Your Data Stack
bySkyvia
 
Kate Scrivens-OECD-Community well-being and climate risks.pdf
byStatsCommunications
 
[DSC Europe 25] Stan Sokorac - New Generation of AI Computers.pptx
byDataScienceConferenc1
 
[DSC Europe 25] Andy Cotgreave - Nothing is new in analytics.pptx
byDataScienceConferenc1
 
[DSC Europe 25] Max Talanov - Non digital NNs.pptx
byDataScienceConferenc1
 
How To Write A Scientific Paper- IMRAD pattern
bydrmeenajain
 
Data+Security+Masterclass Presentation.pdf
byckurt9676
 
[DSC Europe 25] Nikola Rajovic - Hardware Technologies Under the Hood: RISC-V...
byDataScienceConferenc1
 
[DSC Europe 25] Dusan Jovicic - AI Story: From on-prem to cloud and back agai...
byDataScienceConferenc1
 
INTRODUCTION-TO-PHILOSOPHY-OF-HUMAN-PERSON (1).pptx
byHaydenKasey
 
GRADE 8 MATH - Q3 - LESSON 1 -LINEAR EQUATION IN ONE VARIABLE.pptx
byEdlynMalangPanti
 
Arrays (Lists) in PythonaSasaSAsASAsaSA.pptx
bybinarybombers1978
 
Perceptron and Activation Function in deep learning
byShrideviS7
 
CC-105-Semi-Final-Topicdsgsdvsdgsdb.pptx
bykhajezjhozhua131
 
OPPOTUS - Malaysians on Malaysia (MOM) Q3 2025
byOppotus
 
Orientation-on-ARAL-Check-in-Assessment.pptx
byJenTeruel1
 

Oracle Database SQL Tuning Concept

  • 1.
  • 2.
    SQL Statements • DML •SELECT • INSERT/UPDATE/DELETE/MERGE • DDL • CREATE/ALTER/DROP/TRUNCATE • DML on Data Dictionary • DCL • GRANT/REVOKE • TCL • COMMIT/REVOKE/SAVEPOINT
  • 3.
    SQL Execution Flow Parse Execute Fetch (Selectonly) Server Process $ sqlplus hr/hr@orcl SQL> select last_name,salary from employees where employee_id=100; User Process Instance Database Oracle Server SQL Shared Pool Buffer Cache Log Buffer DML only Data files LAST_NAME SALARY ------------------------- ---------- King 24000 Result
  • 4.
    Row Source Generator Syntaxanalysis Semantic analysis Query Transformer Estimator Plan Generator Data Dictionary Parser Optimizer optimizer statistics Hash(SQL) Soft Parse Hard Parse Parse Execution Plan Execution Plan Execution Plan Execution Plan Execution Plan Execution Plan Execution Plan Execution Plan Shared Pool (Library Cache) Server Process
  • 5.
  • 6.
    Tuning Cost andBenefit Time Low High Design Development Production Cost Benefit
  • 7.
    Response Time Throughput Time Response time Waittime Service time Response time=Service time+wait time
  • 8.
    RT Breakdown User APServer DB Server Disk T1 T2 T3 T4 T5 T6 T7 T8 T9 T10 T11 T12 Time Parse Execute Physical I/O Execute Execute Fetch Process User Request Generate SQL Process SQL Result Generate Web Page Request Response Memory Logical I/O Firewall Physical I/O Execute T13 T14 Time Network filter
  • 9.
    Which SQL needTune • SQL敘述句的執⾏行效率達不到之前所設 定的要求。 • Response Time • 單⼀一SQL敘述句的整體反應時間 • Throughput • 單位時間裡，同時執⾏行的SQL敘述句數 量
  • 10.
    Why SQL needTune • 執⾏行多餘或無謂的操作 • 不必要的SORT操作 • 不必要的Table Join操作 • 使⽤用不必要的函數 • 發⽣生⼀一些等待事件(Wait event) • 不必要的等待 • Lock wait
  • 11.
    OLTP/DW • OLTP • 避免HardParse • Response Time • Throughput • Index Access • DW • 不介意Hard Parse • Response Time • Full Table Scan
  • 12.
    OLTP/DW A Data Warehouse(DW)is a relational database that is designed for query and analysis rather than for transaction processing. It usually contains historical data derived from transaction data, but it can include data from other sources. It separates analysis workload from transaction workload and enables an organization to consolidate data from several sources. In addition to a relational database, a data warehouse environment includes an extraction, transportation, transformation, and loading (ETL) solution, an online analytical processing (OLAP) engine, client analysis tools, and other applications that manage the process of gathering data and delivering it to business users. An Online Transaction Processing (OLTP) systems are characterized by high throughput, many users, and a mix of DML operations (insert, update, and delete) and queries.
  • 13.
    Difference between OLTP andDW OLTP DW 3rd Normal Form 表格設計 3NF/Star Schema 多 索引 少 多 Join 中 Normalized DB 資料重覆 DeNormalized DB 少 衍⽣生與彙總 資料 多 事先定義 SELECT Ad Hoc 次數多 每次資料量少 DML 次數少 每次資料量⼤大 Response Time⼩小 Throughput⼤大 系統要求 Response Time⼩小
  • 14.
  • 15.
    SQL Tuning on設計階段 • 在OLTP環境下，可以考慮在WHERE⼦子句 中，使⽤用BindVariable取代Literal，以避免 過多的Hard Parse。 • 避免Table與View進⾏行 Join，以避免產⽣生 Cartesian Join。 • 使⽤用命名規則，以增加SQL的可讀性與 可維護性。 • 指令的⼤大⼩小寫、注解的使⽤用 • 使⽤用Hint前，需要審慎評估。
  • 16.
    Hard Parse -1 SQL> select count(*) from hr.emp where job_id='SA_REP'; COUNT(*) ---------- 30 SQL> select count(*) from hr.emp where JOB_ID='SA_REP'; COUNT(*) ---------- 30 SQL> select sql_id,hash_value,plan_hash_value,executions,sql_text 2 from v$sql 3 where sql_text like 'select count(*) from hr.emp where %'; SQL_ID HASH_VALUE PLAN_HASH_VALUE EXECUTIONS ------------- ---------- --------------- ---------- SQL_TEXT -------------------------------------------------------------------------------------- 8rtytfkcsw4wd 2576225165 2083865914 1 select count(*) from hr.emp where job_id='SA_REP' asax0q4n558hw 676504092 2083865914 1 select count(*) from hr.emp where JOB_ID='SA_REP'
  • 17.
    Hard Parse-2 SQL> selectcount(*) from hr.emp --在hr.emp之後有⼀一個不可⾒見字元n 2 where job_id='SA_REP'; COUNT(*) ---------- 30 SQL> SELECT sql_id,hash_value,executions,plan_hash_value,sql_text 2 FROM v$sql 3 WHERE sql_text like 'select count(*) from hr.emp where %'; SQL_ID HASH_VALUE PLAN_HASH_VALUE EXECUTIONS ------------- ---------- --------------- ---------- SQL_TEXT -------------------------------------------------------------------------------- 8rtytfkcsw4wd 2576225165 2083865914 1 select count(*) from hr.emp where job_id='SA_REP' asax0q4n558hw 676504092 2083865914 1 select count(*) from hr.emp where JOB_ID='SA_REP' 0v9928mstgq0g 4053260303 2083865914 1 select count(*) from hr.emp where job_id='SA_REP'
  • 18.
    Hard Parse -3 SQL> select last_name from hr.employees where employee_id=100; LAST_NAME ------------------------- King SQL> select last_name from hr.employees where employee_id=200; LAST_NAME ------------------------- Whalen SQL> SELECT hash_value,plan_hash_value,executions,sql_text 2 FROM v$sql 3 WHERE sql_text like 'select last_name from hr.employees where employee_id=%'; HASH_VALUE PLAN_HASH_VALUE EXECUTIONS ---------- --------------- ---------- SQL_TEXT ------------------------------------------------------------------------------------ 2627784799 1833546154 1 select last_name from hr.employees where employee_id=200 280342537 1833546154 1 select last_name from hr.employees where employee_id=100
  • 19.
    Hard Parse -4 SQL> variable empid number --此⽅方式僅適⽤用在sqlplus,sql developer等.不過java/NET也有類似語法 SQL> execute :empid := 101; SQL> select last_name from hr.employees where employee_id=:empid; LAST_NAME ------------------------- Kochhar SQL> execute :empid := 201; SQL> select last_name from hr.employees where employee_id=:empid; LAST_NAME ------------------------- Hartstein SQL> SELECT hash_value,plan_hash_value,executions sql_text 2 FROM v$sql 3 WHERE sql_text like 'select last_name from hr.employees where employee_id=%' HASH_VALUE PLAN_HASH_VALUE EXECUTIONS ---------- --------------- ---------- SQL_TEXT ---------------------------------------------------------------------------------------------- 476476418 1833546154 2 select last_name from hr.employees where employee_id=:empid 2627784799 1833546154 1 select last_name from hr.employees where employee_id=200 280342537 1833546154 1 select last_name from hr.employees where employee_id=100
  • 20.
    SQL Tuning on執⾏行階段 Response time=CPU time+Wait time 必要的CPU time+ 不必要的CPU time 必要的Wait time+ 不必要的Wait time
  • 21.
    Which SQL wereHigh Loading ? • ADDM Report • AWR Report(Enterprise Edition) • STATSPACK(Standard Edition) • V$SQL
  • 22.
  • 23.
  • 24.
  • 25.
    Resource V$SQL Response TimeELAPSED_TIME Service Time CPU_TIME Sort SORTS Total I/Os BUFFER_GETS Physical I/Os DISK_READS Row Processed ROW_PROCESSED Memory RUNTIME_MEMORY Executions EXECUTIONS Critical Resources
  • 26.
    V$SQL SQL> SELECT * 2FROM 3 (SELECT sql_id,elapsed_time,cpu_time,buffer_gets,disk_reads,executions 4 FROM v$sql 5 ORDER BY elapsed_time DESC,cpu_time DESC, 6 buffer_gets DESC,disk_reads DESC,executions DESC) 7 WHERE ROWNUM<=10; SQL_ID ELAPSED_TIME CPU_TIME BUFFER_GETS DISK_READS EXECUTIONS ------------- ------------ ---------- ----------- ---------- ---------- 6qyqqyg14bm17 4722430672 4566883727 1171553448 1169941510 1 0a59tkkpx31vx 57267687 52511021 6414812 895 7 cj8ytxm5hhm4y 82778799 83355256 105 1 10100000 01uy9sb7w8a9g 37054402 41994 714 83 1 cvn54b7yz0s8u 25761776 942843 47660 6616 4142 3ktacv9r56b51 18381236 1381807 109780 1048 6350 39m4sx9k63ba2 15383617 449923 22739 1635 4142 ga9j9xk5cy9s0 14530703 493937 24296 752 4142 c6awqs517jpj0 9019830 340949 13858 512 4142 8swypbbr0m372 7280083 802874 100473 764 6350 10 rows selected.
  • 27.
    V$SQL_MONITOR SQL> SELECT * 2FROM 3 (SELECT sql_id,elapsed_time,cpu_time,queuing_time,buffer_gets,disk_reads 4 FROM v$sql_monitor 5 ORDER BY elapsed_time DESC,cpu_time DESC,queuing_time DESC 6 buffer_gets DESC,disk_reads DESC) 7 WHERE ROWNUM<=10; SQL_ID ELAPSED_TIME CPU_TIME QUEUING_TIME BUFFER_GETS DISK_READS ------------- ------------ ---------- ------------ ----------- ---------- 6qyqqyg14bm17 5396549468 5221542205 0 1337837043 17458050 ak3huf5xbg5f6 313934168 304642686 0 48 0 05s9358mm6vrr 14956086 5728129 0 195423 1744 6gvch1xu9ca3g 12560842 1741735 0 44884 1441 0a59tkkpx31vx 11246557 9412567 0 1111033 324 0a59tkkpx31vx 9080340 7986787 0 889924 494 0a59tkkpx31vx 8483972 7845806 0 886621 0 5zruc4v6y32f9 8478735 2486622 0 312062 2210 0a59tkkpx31vx 8175980 7829809 0 890517 0 f6cz4n8y72xdc 7293541 1506770 0 54905 636 10 rows selected.
  • 28.
  • 29.
  • 30.
    Empty/Stale Statistics SQL> SELECTnum_rows,blocks,avg_row_len,stale_stats 2 FROM dba_tab_statistics 3 WHERE owner='HR' and table_name='EMP'; NUM_ROWS BLOCKS AVG_ROW_LEN STA ---------- ---------- ----------- --- SQL> SELECT num_rows,blocks,avg_row_len,stale_stats 2 FROM dba_tab_statistics 3 WHERE owner='HR' and table_name='EMP'; NUM_ROWS BLOCKS AVG_ROW_LEN STA ---------- ---------- ----------- --- 70 5 69 YES 變動量超過10% SQL> SELECT COUNT(*) FROM hr.emp; COUNT(*) ---------- 107 Staled statistics Empty statistics
  • 31.
    Manual Gather Statistics SQL>EXECUTE DBMS_STATS.GATHER_TABLE_STATS('HR','EMP'); SQL> SELECT num_rows,blocks,avg_row_len,stale_stats 2 FROM dba_tab_statistics 3 WHERE owner='HR' and table_name='EMP'; NUM_ROWS BLOCKS AVG_ROW_LEN STA ---------- ---------- ----------- --- 107 5 69 NO SQL> EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS('HR'); SQL> EXECUTE DBMS_STATS.GATHER_DATABASE_STATS; SQL> EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS; SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECT_STATS;
  • 32.
    Gather Statistics Options •Estimate Percentage • Invalidate Execution Plan • Stale Percentage • Pending Publish SQL> DESC dba_tab_stat_prefs Name Null? Type ----------------------------------- -------- ---------------------------- OWNER NOT NULL VARCHAR2(30) TABLE_NAME NOT NULL VARCHAR2(30) PREFERENCE_NAME VARCHAR2(30) PREFERENCE_VALUE VARCHAR2(1000)
  • 34.
    Finding Execution Plan •EXPLAIN PLAN(PLAN_TABLE) • AUTOTRACE(PLAN_TABLE) • Shared Pool:Library Cache(V$SQL_PLAN) • AWR(DBA_HIST_SQL_PLAN) • SQL Tuning Set • SQL Plan Baseline • Tracefile • EVENT:10046/10053
  • 35.
    Display Execution Plan •DBMS_XPLAN.DISPLAY • DBMS_XPLAN.DISPLAY_CURSOR • DBMS_XPLAN.DISPLAY_AWR • DBMS_XPLAN.DISPLAY_SQLSET • DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE • SET AUTOTRACE ON • DBMS_SQLTUNE.REPORT_SQL_MONITOR
  • 36.
    EXPLAIN PLAN SQL> EXPLAINPLAN SET STATEMENT_ID='frank_demo1' FOR 2 SELECT last_name,salary FROM hr.emp WHERE employee_id=100; Explained. SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(statement_id=>'frank_demo1')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------- Plan hash value: 3956160932 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 16 | 3 (0)| 00:00:01| |* 1 | TABLE ACCESS FULL | EMP | 1 | 16 | 3 (0)| 00:00:01| -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("EMPLOYEE_ID"=100)
  • 37.
    AUTOTRACE SQL> SET AUTOTRACEON SQL> SELECT last_name,salary FROM hr.emp WHERE employee_id=100; LAST_NAME SALARY ------------------------- ---------- King 24000 Execution Plan ---------------------------------------------------------- Plan hash value: 3956160932 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 16 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| EMP | 1 | 16 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("EMPLOYEE_ID"=100)
  • 38.
    V$SQL_PLAN SQL> SELECT sql_id,child_number 2FROM v$sql 3 WHERE sql_text='SELECT last_name,salary FROM hr.emp WHERE employee_id=100'; SQL_ID CHILD_NUMBER ------------- ------------ cj8ytxm5hhm4y 0 SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('cj8ytxm5hhm4y',0)); PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------- SQL_ID cj8ytxm5hhm4y, child number 0 ------------------------------------- SELECT last_name,salary FROM hr.emp WHERE employee_id=100 Plan hash value: 3956160932 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | |* 1 | TABLE ACCESS FULL| EMP | 1 | 16 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("EMPLOYEE_ID"=100)
  • 39.
    AWR SQL> SELECT sql_id 2FROM dba_hist_sqltext 3 WHERE sql_text='SELECT last_name,salary FROM hr.emp WHERE employee_id=100'; SQL_ID ------------- cj8ytxm5hhm4y SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('cj8ytxm5hhm4y')); PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------- SQL_ID cj8ytxm5hhm4y, child number 0 ------------------------------------- SELECT last_name,salary FROM hr.emp WHERE employee_id=100 Plan hash value: 3956160932 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | |* 1 | TABLE ACCESS FULL| EMP | 1 | 16 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("EMPLOYEE_ID"=100)
  • 40.
    SQL Tracefile(RAW) SQL> ALTERSESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER,LEVEL 8'; SQL> SELECT last_name,salary FROM hr.emp WHERE employee_id=100; LAST_NAME SALARY ------------------------- ---------- King 24000 SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF'; orcl_ora_15492.trc ===================== PARSING IN CURSOR #140636401352696 len=57 dep=0 uid=0 oct=3 lid=0 tim=1357281071350293 hv=3406318750 ad='78fb4328' sqlid='cj8ytxm5hhm4y' SELECT last_name,salary FROM hr.emp WHERE employee_id=100 END OF STMT PARSE #140636401352696:c=20997,e=161413,p=3,cr=138,cu=0,mis=1,r=0,dep=0,og=1,plh=3956160932,tim=1357281071350286 EXEC #140636401352696:c=0,e=98,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3956160932,tim=1357281071350483 WAIT #140636401352696: nam='SQL*Net message to client' ela= 4 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1357281071350537 FETCH #140636401352696:c=0,e=76,p=0,cr=2,cu=0,mis=0,r=1,dep=0,og=1,plh=3956160932,tim=1357281071350647 WAIT #140636401352696: nam='SQL*Net message from client' ela= 5227 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1357281071355930 FETCH #140636401352696:c=0,e=48,p=0,cr=2,cu=0,mis=0,r=0,dep=0,og=1,plh=3956160932,tim=1357281071356062 STAT #140636401352696 id=1 cnt=1 pid=0 pos=1 obj=77179 op='TABLE ACCESS FULL EMP (cr=4 pr=0 pw=0 time=118 us cost=3 size=16 card=1)' WAIT #140636401352696: nam='SQL*Net message to client' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1357281071356153 $ tkprof orcl_ora_15492.trc output1.txt
  • 41.
    SQL ID: cj8ytxm5hhm4yPlan Hash: 3956160932 SELECT last_name,salary FROM hr.emp WHERE employee_id=100 call count cpu elapsed disk query current rows ----------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.04 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 0 4 0 1 ----------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.04 0 4 0 1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: SYS Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- --------------------------------------------------- 1 1 1 TABLE ACCESS FULL EMP (cr=4 pr=0 pw=0 time=118 us cost=3 size=16 card=1) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 2 0.00 0.00 SQL*Net message from client 2 17.86 17.87
  • 42.
    10053 Trace SQL> SELECTsql_id 2 FROM dba_hist_sqltext 3 WHERE sql_text='SELECT last_name,salary FROM hr.emp WHERE employee_id=100'; SQL_ID ------------- cj8ytxm5hhm4y SQL> EXECUTE dbms_sqldiag.dump_trace( 2> p_sql_id=>'3jb04z19uj8jf', 3> p_child_number=>0, 4> p_component=>'Compiler', 5> p_file_id=>'Hello_frank'); SQL> SELECT value FROM v$diag_info WHERE name='Default Trace File'; VALUE ------------------------------------------------------------------------- /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3704_Hello_frank.trc
  • 43.
    10053 Trace Content ============ PlanTable ============ -------------------------------------+-----------------------------------+ | Id | Operation | Name | Rows | Bytes | Cost | Time | -------------------------------------+-----------------------------------+ | 0 | SELECT STATEMENT | | | | 3 | | | 1 | TABLE ACCESS FULL | EMP | 1 | 40 | 3 | 00:00:01 | -------------------------------------+-----------------------------------+ Predicate Information: ---------------------- 1 - filter("EMPLOYEE_ID"=100)
  • 44.
    V$SQL_MONITOR SQL> set pagesize0 echo off timing off SQL> set linesize 1000 trimspool on trim on SQL> set long 2000000 longchunksize 2000000 feedback off SQL> spool sqlmonrpt_6qyqqyg14bm17.html SQL> SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(type=>’EM’,sql_id=>’6qyqqyg14bm17’) FROM dual; SQL> spool off
  • 45.
    執⾏行計劃 --------------------------------------------------------------------------------- | Id |Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 370M(100)| | | 1 | SORT AGGREGATE | | 1 | 17 | | | | 2 | NESTED LOOPS | | 5557 | 94469 | 370M (1)|999:59:59 | | 3 | TABLE ACCESS FULL| SALES | 918K| 4486K| 1233 (1)| 00:00:15 | |* 4 | TABLE ACCESS FULL| CUSTOMERS | 1 | 12 | 403 (1)| 00:00:05 | --------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - filter(("C"."CUST_FIRST_NAME"='Dina' AND "S"."CUST_ID"="C"."CUST_ID")) 3 4 2 1 SELECT COUNT(*) FROM frank.sales s,frank.customers c WHERE s.cust_id=c.cust_id AND c.cust_first_name=‘Dina’
  • 46.
    • Data AccessMethod • Join Order • Join Method • 每個Row Source應該盡可能減少傳給下 ⼀一個Row Source的資料量 執⾏行計劃的調整順序
  • 47.
    Data Access Method 使⽤用者提供rowidTable Access By User Rowid 讀取所有位在High Water Mark之下的Table blocks。 Table Access Full 先透過Index找到某些rows 的ROWID，然後讀取 ROWID所指向的table blocks。 Index Range Scan Index Unique Scan Index Full Scan Index Fast Full Scan Index Skip Scan
  • 48.
    ROWID rowid為row的相對位置資訊 rowid顯⽰示為18 characters(A-Z,a-z,0-9,+,/共64個character編碼) ABCDEFGHIJ KL M N O P Q R S T U V W X Y Z a b c d e f g.... 0123456789 1011121314151617181920212223242526272829303132.... 前6個character表⽰示為segment id(data object id) 前3個character表⽰示為relative file id(row位在哪個datafile之上) 前6個character表⽰示為block id(row位在哪個block之上) 前3個character表⽰示為row number(該block的第幾筆row) SQL> SELECT dbms_rowid.rowid_object('AAASYtAAEAAAAKnABI') data_object_id, 2 dbms_rowid.rowid_relative_fno('AAASYtAAEAAAAKnABI') relative_file_number, 3 dbms_rowid.rowid_block_number('AAASYtAAEAAAAKnABI') block_number, 4 dbms_rowid.rowid_row_number('AAASYtAAEAAAAKnABI') row_number 5 FROm dual; DATA_OBJECT_ID RELATIVE_FILE_NUMBER BLOCK_NUMBER ROW_NUMBER -------------- -------------------- ------------ ---------- 75309 4 679 72
  • 49.
    1 A A1 10J J10 17 Q Q 2 B B2 19 S S19 3 C C3 7 G G7 11 K K 4 D D4 12 L L 21 U U 6 F F6 13 M M 22 V V 8 H H8 15 O O 24 X X 5 E E5 14 N N 23 W W23 9 I I9 16 P P 20 T T 18 R R18 Table Access By User Rowid
  • 50.
    Table Access ByUser Rowid SQL> SELECT object_id,object_name 2> FROM hr.big1 3> WHERE rowid='AAASYtAAEAAAAKnABI'; OBJECT_ID OBJECT_NAME ---------- ------------------------------ 12345 ALERT_QUE_N ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 42 | 1 (0)| 00:00 |* 1 | TABLE ACCESS BY USER ROWID| BIG1 | 1 | 42 | 1 (0)| 00:00 ------------------------------------------------------------------------------
  • 51.
    Full Table Scan 1A A1 10 J J10 17 Q Q 2 B B2 19 S S19 3 C C3 7 G G7 11 K K 4 D D4 12 L L 21 U U 6 F F6 13 M M 22 V V 8 H H8 15 O O 24 X X 5 E E5 14 N N 23 W W23 9 I I9 16 P P 20 T T 18 R R18
  • 52.
    Full Table Scan SQL>SELECT rowid,object_id,object_name 2> FROM hr.big1 3> WHERE object_id=12345; ROWID OBJECT_ID OBJECT_NAME ------------------ ---------- ------------------------------ AAASYtAAEAAAAKnABI 12345 ALERT_QUE_N -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 30 | 289 (1)| 00:00:04 | |* 1 | TABLE ACCESS FULL| BIG1 | 1 | 30 | 289 (1)| 00:00:04 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_ID"=12345)
  • 53.
    Index Unique Scan branchbranch root 1 rowid 2 rowid 3 rowid 4 rowid 5 rowid 6 rowid 7 rowid 8 rowid 9 rowid 10 rowid 11 rowid 12 rowid 13 rowid 14 rowid 15 rowid 16 rowid 17 rowid 18 rowid 19 rowid 20 rowid 21 rowid 22 rowid 23 rowid 24 rowid 1 A A1 10 J J10 17 Q Q 2 B B2 19 S S19 3 C C3 7 G G7 11 K K 4 D D4 12 L L 21 U U 6 F F6 13 M M 22 V V 8 H H8 15 O O 24 X X 5 E E5 14 N N 23 W W23 9 I I9 16 P P 20 T T 18 R R18
  • 54.
    Index Unique Scan SQL>SELECT rowid,object_id,object_name 2> FROM hr.big1 3> WHERE object_id=12345; ROWID OBJECT_ID OBJECT_NAME ------------------ ---------- ------------------------------ AAASYtAAEAAAAKnABI 12345 ALERT_QUE_N ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 42 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| BIG1 | 1 | 42 | 2 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | BIG1_IDX1 | 1 | | 1 (0)| 00:00:01 |　　---------------------------------------------------------------------------------------- - Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=12345)
  • 55.
    Index Range Scan Root branchbranch 1 rowid 2 rowid 3 rowid 4 rowid 5 rowid 6 rowid 7 rowid 8 rowid 9 rowid 10 rowid 11 rowid 12 rowid 13 rowid 14 rowid 15 rowid 16 rowid 17 rowid 18 rowid 19 rowid 20 rowid 21 rowid 22 rowid 23 rowid 24 rowid 6 F F6 13 M M 22 V V 8 H H8 15 O O 24 X X 5 E E5 14 N N 23 W W23 9 I I9 16 P P 20 T T 1 A A1 10 J J10 17 Q Q 2 B B2 19 S S19 3 C C3 7 G G7 11 K K 4 D D4 12 L L 21 U U 18 R R18
  • 56.
    Index Range Scan SQL>SELECT rowid,object_id,object_name 2> FROM hr.big1 3> WHERE object_id=12345; ROWID OBJECT_ID OBJECT_NAME ------------------ ---------- ------------------------------ AAASYtAAEAAAAKnABI 12345 ALERT_QUE_N ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 42 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| BIG1 | 1 | 42 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | BIG1_IDX1 | 1 | | 1 (0)| 00:00:01 |　　---------------------------------------------------------------------------------------- - Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=12345)
  • 57.
    Index Full Scan branchbranch root 1 rowid 2 rowid 3 rowid 4 rowid 5 rowid 6 rowid 7 rowid 8 rowid 9 rowid 10 rowid 11 rowid 12 rowid 13 rowid 14 rowid 15 rowid 16 rowid 17 rowid 18 rowid 19 rowid 20 rowid 21 rowid 22 rowid 23 rowid 24 rowid
  • 58.
    Index Full Scan SQL>select object_id from hr.big1 order by object_id; OBJECT_ID --------- 2 3 4 5 --其餘省略 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 72476 | 353K| 42 (0)| 00:00:01 | | 1 | INDEX FULL SCAN | BIG1_IDX1 | 72476 | 353K| 42 (0)| 00:00:01 | -----------------------------------------------------------------------------------
  • 59.
    Index Fast FullScan branch branch root 1 rowid 2 rowid 3 rowid 4 rowid 5 rowid 6 rowid 7 rowid 8 rowid 9 rowid 10 rowid 11 rowid 12 rowid 13 rowid 14 rowid 15 rowid 16 rowid 17 rowid 18 rowid 19 rowid 20 rowid 21 rowid 22 rowid 23 rowid 24 rowid root 1 rowid 2 rowid 3 rowid 4 rowid 5 rowid 6 rowid 7 rowid 8 rowid 9 rowid 10 rowid 11 rowid 12 rowid branch 22 rowid 23 rowid 24 rowid 16 rowid 17 rowid 18 rowid 13 rowid 14 rowid 15 rowid branch
  • 60.
    Index Fast FullScan SQL> select object_id from hr.big1; OBJECT_ID --------- 23456 12345 45678 34567 --其餘省略 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 72476 | 353K| 42 (0)| 00:00:01 | | 1 | INDEX FAST FULL SCAN| BIG1_IDX1 | 72476 | 353K| 42 (0)| 00:00:01 | -----------------------------------------------------------------------------------
  • 61.
    Table Join Method •Nested Loop Join • Sort Merge Join • Hash Join • Cartesian Join
  • 62.
    Table Join Order •⼀一次Join操作只能結合兩個tables • 如果有4個table需要join • 則可能出現4!=24 join order • 盡量讓最⼩小的兩個table先進⾏行結合
  • 63.
    A B 1 11 333 2 22 Nested Loop Join C D 33 10 44 20 55 30 11 40 11 50 22 60 A B C D 3 33 33 10 1 11 11 40 1 11 11 50 2 22 22 60 FROM t1 JOIN t2 ON (t1.b=t2.c); t1 t2
  • 64.
    A B 1 11 333 2 22 Sort Merge Join C D 33 10 44 20 55 30 11 40 11 50 22 60 t1 t2 A B 1 11 2 22 3 33 C D 11 40 11 50 22 60 33 10 44 20 55 30 A B C D 1 11 11 40 1 11 11 50 2 22 22 60 3 33 33 10 FROM t1 JOIN t2 ON (t1.b=t2.c); sort sort
  • 65.
    A B CD 1 11 11 40 1 11 11 50 2 22 22 60 3 33 33 10 A B 1 11 3 33 2 22 Hash Join C D 33 10 44 20 55 30 11 40 11 50 22 60 FROM t1 JOIN t2 ON (t1.b=t2.c); t1 t2 33 11 22 hash_area_size Build hash table Probing hash table1 2 3
  • 66.
    A B 1 11 333 2 22 Cross Join C D 33 10 44 20 55 30 11 40 11 50 22 60 FROM t1 CROSS JOIN t2; t1 t2 A B C D 1 11 33 10 1 11 44 20 1 11 55 30 1 11 11 40 1 11 11 50 1 11 22 60 3 33 33 10 3 33 44 20 3 33 55 30 3 33 11 40 3 33 11 50 3 33 22 60 2 22 33 10 2 22 44 20 2 22 55 30 2 22 11 40 2 22 11 50 2 22 22 60
  • 67.
    A B 1 11 333 2 22 4 66 Inner Join C D 33 10 44 20 55 30 11 40 11 50 22 60 FROM t1 JOIN t2 ON (t1.B=t2.C) t1 t2 A B C D 1 11 11 40 1 11 11 50 3 33 33 10 2 22 22 60
  • 68.
    A B 1 11 333 2 22 4 66 Left Outer Join C D 33 10 44 20 55 30 11 40 11 50 22 60 FROM t1 LEFT OUTER JOIN t2 ON (t1.B=t2.C) t1 t2 A B C D 1 11 11 40 1 11 11 50 3 33 33 10 2 22 22 60 4 66
  • 69.
    A B 1 11 333 2 22 4 66 Right Outer Join C D 33 10 44 20 55 30 11 40 11 50 22 60 FROM t1 RIGHT OUTER JOIN t2 ON (t1.B=t2.C) t1 t2 A B C D 1 11 11 40 1 11 11 50 3 33 33 10 2 22 22 60 44 20 55 30
  • 70.
    A B 1 11 333 2 22 4 66 Full Outer Join C D 33 10 44 20 55 30 11 40 11 50 22 60 FROM t1 FULL OUTER JOIN t2 ON (t1.B=t2.C) t1 t2 A B C D 1 11 11 40 1 11 11 50 3 33 33 10 2 22 22 60 4 66 44 20 55 30
  • 71.
    --------------------------------------------------------------------------------- | Id |Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 370M(100)| | | 1 | SORT AGGREGATE | | 1 | 17 | | | | 2 | NESTED LOOPS | | 5557 | 94469 | 370M (1)|999:59:59 | | 3 | TABLE ACCESS FULL| SALES | 918K| 4486K| 1233 (1)| 00:00:15 | |* 4 | TABLE ACCESS FULL| CUSTOMERS | 1 | 12 | 403 (1)| 00:00:05 | --------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - filter(("C"."CUST_FIRST_NAME"='Dina' AND "S"."CUST_ID"="C"."CUST_ID")) 解析執⾏行計劃 Data Access Method Join Method Join Order (SALES為driving table) 3 4 2 1 SELECT COUNT(*) FROM frank.sales s,frank.customers c WHERE s.cust_id=c.cust_id AND c.cust_first_name=‘Dina’
  • 72.
    --------------------------------------------------------------------------------------------- | Id |Operation | Name | Rows| Bytes |Cost (%CPU)|Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 6436K(100)| | | 1 | SORT AGGREGATE | | 1 | 17 | | | | 2 | NESTED LOOPS | | | | | | | 3 | NESTED LOOPS | |5557 | 94469 | 6436K (1)|21:27:13 | | 4 | TABLE ACCESS FULL | SALES | 918K| 4486K| 1233 (1)|00:00:15 | |* 5 | INDEX RANGE SCAN | CUST_FNAME_IDX |1 | | 1 (0)|00:00:01 | |* 6 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 1 | 12 | 2 (0)|00:00:01 | --------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("C"."CUST_FIRST_NAME"='Dina') 6 - filter("S"."CUST_ID"="C"."CUST_ID") 建⽴立index在WERE⼦子句 存取customers的⽅方法 由Full Table Scan改為 Index Scan Cost由 370M降到 6 1 2 3 4 5 Time由999:59:59 降為21:27:13
  • 73.
    -------------------------------------------------------------------------------------------- | Id |Operation | Name | Rows| Bytes|Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | |6435K(100) | | | 1 | SORT AGGREGATE | | 1 | 17| | | | 2 | NESTED LOOPS | | | | | | | 3 | NESTED LOOPS | |5557 | 94469|6435K (1) | 21:27:05 | | 4 | INDEX FAST FULL SCAN | SALES_CUSTID_IDX| 918K| 4486K| 535 (2) | 00:00:07 | |* 5 | INDEX RANGE SCAN | CUST_FNAME_IDX | 1 | | 1 (0) | 00:00:01 | |* 6 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 1 | 12| 2 (0) | 00:00:01 | --------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("C"."CUST_FIRST_NAME"='Dina') 6 - filter("S"."CUST_ID"="C"."CUST_ID") 建⽴立index在JOIN⼦子句 Cost由6436K降 到6435K Time由21:27:43 降為21:27:05 6 1 2 3 4 5
  • 74.
    -------------------------------------------------------------------------------------------- | Id |Operation | Name | Rows| Bytes|Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | |1838K(100) | | | 1 | SORT AGGREGATE | | 1 | 17| | | | 2 | NESTED LOOPS | | | | | | | 3 | NESTED LOOPS | |5557 | 94469|1838K (1) | 06:07:47 | | 4 | INDEX FAST FULL SCAN | SALES_CUSTID_IDX| 918K| 4486K| 535 (2) | 00:00:07 | |* 5 | INDEX RANGE SCAN | CUST_CUSTID_IDX | 1 | | 1 (0) | 00:00:01 | |* 6 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 1 | 12| 2 (0) | 00:00:01 | --------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("S"."CUST_ID"="C"."CUST_ID") 6 - filter("C"."CUST_FIRST_NAME"='Dina') 建⽴立index在JOIN⼦子句 Cost由6436K降 到1838K Time由21:27:43 降為06:07:47 6 1 2 3 4 5 不使⽤用CUST_FNAME_IDX
  • 75.
    --------------------------------------------------------------------------------------------- | Id |Operation | Name | Rows | Bytes |Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | |54544 (100)| | | 1 | SORT AGGREGATE | | 1 | 17 | | | | 2 | NESTED LOOPS | | 5557 | 94469 |54544 (1)| 00:10:55| |* 3 | TABLE ACCESS BY INDEX ROWID|CUSTOMERS | 43 | 516 |54489 (1)| 00:10:54| | 4 | INDEX FULL SCAN |CUST_CUSTID_IDX |55500 | | 125 (1)| 00:00:02| |* 5 | INDEX RANGE SCAN |SALES_CUSTID_IDX| 130 | 650 | 2 (0)| 00:00:01| --------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("C"."CUST_FIRST_NAME"='Dina') 5 - access("S"."CUST_ID"="C"."CUST_ID") 使⽤用customers當做driving table Cost由 1838K降為54544 Time由06:07:47 降為00:10:55 5 1 2 3 4 Join Order (CUSTOMERS為driving table) 不使⽤用CUST_FNAME_IDX
  • 76.
    同時變更Data Access Method與Join Order ---------------------------------------------------------------------------------------------- |Id | Operation |Name | Rows| Bytes| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 93 (100)| | | 1 | SORT AGGREGATE | | 1| 17 | | | | 2 | NESTED LOOPS | | 5557| 94469| 93 (0)|00:00:02 | | 3 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 43| 516 | 7 (0)|00:00:01 | |* 4 | INDEX RANGE SCAN |CUSTOMERS_FNAME_IDX| 43| | 1 (0)|00:00:01 | |* 5 | INDEX RANGE SCAN |SALES_CUSTID_IDX | 130| 650| 2 (0)|00:00:01 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("C"."CUST_FIRST_NAME"='Dina') 5 - access("S"."CUST_ID"="C"."CUST_ID") SELECT COUNT(*) FROM frank.sales s,frank.customers c WHERE s.cust_id=c.cust_id AND c.cust_first_name=‘Dina’ 3 5 2 1 4 Cost由1838K 減為93 Time由06:07:47 減為00:00:02 Join Order (CUSTOMERS為driving table)
  • 77.
    ---------------------------------------------------------------------------------------------- | Id |Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 547 (100)| | | 1 | SORT AGGREGATE | | 1 | 17 | | | |* 2 | HASH JOIN | | 5557 | 94469 | 547 (1)| 00:00:07 | | 3 | TABLE ACCESS BY INDEX ROWID|CUSTOMERS | 43 | 516 | 7 (1)| 00:00:01 | |* 4 | INDEX FULL SCAN |CUST_FNAME_IDX | 43 | | 1 (1)| 00:00:01 | | 5 | INDEX FULL SCAN |SALES_CUSTID_IDX| 918K| 4486K| 1958 (1)| 00:00:07 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("S"."CUST_ID"="C"."CUST_ID") 4 - access("C"."CUST_FIRST_NAME"='Dina') 變更Join Method-hash join 由Nested Loop 改為Hash Join Cost由93增 加到547 Time由00:00:02 增加到00:00:07 5 1 2 3 4
  • 78.
    變更Join Method-sort merge ---------------------------------------------------------------------------------------------- |Id | Operation | Name | Rows | Bytes| Cost (%CPU)|Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1966 (100)| | | 1 | SORT AGGREGATE | | 1 | 17 | | | | 2 | MERGE JOIN | | 5557 | 94469| 1966 (1)|00:00:24 | | 3 | INDEX FULL SCAN |SALES_CUSTID_IDX| 918K| 4486K| 1958 (1)|00:00:24 | |* 4 | SORT JOIN | | 43 | 516 | 8 (13)|00:00:01 | | 5 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 43 | 516 | 7 (1)|00:00:01 | |* 6 | INDEX FULL SCAN | CUST_FNAME_IDX |55500 | | 1 (1)|00:00:01 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("S"."CUST_ID"="C"."CUST_ID") filter("S"."CUST_ID"="C"."CUST_ID") 6 - access("C"."CUST_FIRST_NAME"='Dina') 改為Sort Merge Join Cost由93增加 到1966 Time由00:00:02 增加到00:00:24 4 1 2 3 5 6
  • 79.
    使⽤用Index的建議 • 是否需要建⽴立 • 建⽴立在哪些欄位 •單⼀一欄位或複合欄位 • 建⽴立何種形態 • B-Tree • Bitmap • Unique • Non-Unique
  • 80.
    What is INDEX •An index is an optional structure, associated with a table or table cluster, that can sometimes speed data access. By creating an index on one or more columns of a table, you gain the ability in some cases to retrieve a small set of randomly distributed rows from the table. Indexes are one of many means of reducing disk I/O. Oracle® Database Concepts 11g Release 2 (11.2) -3 Indexes and Index-Organized Tables
  • 81.
    • Index orNot Index • Index Cost • Low Selectivity(BTree Index) • Which columns • FROM/WHERE clause • Single/Multiple Column • What Type • BTree • BitMap • Uniqueness • Non-Unique • Unique
  • 82.
    其他Data Access Method •Partitioned Table • Cluster Table • Index Cluster • Hash Cluster • Single Hash Cluster • Index Organized Table • Materialized view • Materialized view log
  • 83.