Advanced Product Service
Oracle Database 10g :
SQL Tuning
glogin.sql
$ cat >> $ORACLE_HOME/sqlplus/admin/glogin.sql << EOF
SET LINESIZE 130
SET PAGESIZE 50
SET TIMING ON
SET FEEDBACK 1
SET SERVEROUTPUT ON SIZE 200000
DEFINE _editor=vi
EOF
Database Startup
$ lsnrctl start
$ sqlplus '/as sysdba' << EOF
startup
exit
EOF
$ emctl start dbconsole
High - Load SQL
$ sqlplus system/oracle
SQL>CREATE OR REPLACE PROCEDURE dy_sql IS
v_cnt number ;
BEGIN
FOR i IN 1..10000 LOOP
execute immediate 'select /* hard */ count(*) from dual where dummy = to_char('||i||')'
into v_cnt ;
END LOOP ;
END dy_sql ;
/
SQL>EXEC dbms_scheduler.create_job(job_name => 'DY_SQL_JOB', -
job_type => 'STORED_PROCEDURE', -
job_action => 'DY_SQL', -
start_date => SYSTIMESTAMP, -
repeat_interval => 'FREQ=HOURLY; INTERVAL=4', -
end_date => SYSTIMESTAMP + 6, -
enabled => TRUE)
SQL>EXEC dbms_scheduler.run_job('DY_SQL_JOB')
1.13 Automated SQL Execution Memory (PGA) Management
SQL>CONN /as sysdba
SQL>ALTER SYSTEM SET workarea_size_policy=MANUAL ;
SQL>ALTER SYSTEM FLUSH BUFFER_CACHE ;
SQL>SHOW PARAMETER sort_area_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sort_area_size integer 65536
SQL>SET AUTOTRACE TRACEONLY
SQL>SELECT * FROM sh.sales ORDER BY 1,2,3,4 ;
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 918K| 25M| | 19810 (2)| 00:03:58 | | |
| 1 | SORT ORDER BY | | 918K| 25M| 84M| 19810 (2)| 00:03:58 | | |
| 2 | PARTITION RANGE ALL| | 918K| 25M| | 426 (9)| 00:00:06 | 1 | 28 |
| 3 | TABLE ACCESS FULL | SALES | 918K| 25M| | 426 (9)| 00:00:06 | 1 | 28 |
------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
15210 recursive calls
770 db block gets
4847 consistent gets
18960 physical reads
0 redo size
36119714 bytes sent via SQL*Net to client
674201 bytes received via SQL*Net from client
61258 SQL*Net roundtrips to/from client
125 sorts (memory)
1 sorts (disk)
918843 rows processed
SQL>ALTER SYSTEM SET sort_area_size= 10485760 SCOPE=SPFILE ;
SQL>STARTUP FORCE
SQL>ALTER SYSTEM FLUSH BUFFER_CACHE ;
SQL>SET AUTOTRACE TRACEONLY
SQL>SELECT * FROM sh.sales ORDER BY 1,2,3,4 ;
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 918K| 25M| | 7353 (3)| 00:01:29 | | |
| 1 | SORT ORDER BY | | 918K| 25M| 84M| 7353 (3)| 00:01:29 | | |
| 2 | PARTITION RANGE ALL| | 918K| 25M| | 426 (9)| 00:00:06 | 1 | 28 |
| 3 | TABLE ACCESS FULL | SALES | 918K| 25M| | 426 (9)| 00:00:06 | 1 | 28 |
------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
39 recursive calls
6 db block gets
1718 consistent gets
6499 physical reads
0 redo size
36119714 bytes sent via SQL*Net to client
674201 bytes received via SQL*Net from client
61258 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
SQL>ALTER SYSTEM SET sort_area_size=104857600 SCOPE=SPFILE ;
SQL>STARTUP FORCE
SQL>ALTER SYSTEM FLUSH BUFFER_CACHE ;
SQL>SET AUTOTRACE TRACEONLY
SQL>SELECT * FROM sh.sales ORDER BY 1,2,3,4 ;
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 918K| 25M| 543 (29)| 00:00:07 | | |
| 1 | SORT ORDER BY | | 918K| 25M| 543 (29)| 00:00:07 | | |
| 2 | PARTITION RANGE ALL| | 918K| 25M| 426 (9)| 00:00:06 | 1 | 28 |
| 3 | TABLE ACCESS FULL | SALES | 918K| 25M| 426 (9)| 00:00:06 | 1 | 28 |
----------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
994 recursive calls
0 db block gets
2062 consistent gets
1661 physical reads
0 redo size
36119714 bytes sent via SQL*Net to client
674201 bytes received via SQL*Net from client
61258 SQL*Net roundtrips to/from client
11 sorts (memory)
0 sorts (disk)
918843 rows processed
SQL>ALTER SYSTEM SET workarea_size_policy=AUTO ;
SQL>ALTER SYSTEM SET pga_aggregate_target=100M ;
SQL>ALTER SYSTEM SET sort_area_size=65536 SCOPE=SPFILE ;
SQL>STARTUP FORCE
SQL>ALTER SYSTEM FLUSH BUFFER_CACHE ;
SQL>SET AUTOTRACE TRACEONLY
SQL>SELECT * FROM sh.sales ORDER BY 1,2,3,4 ;
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 918K| 25M| | 7845 (3)| 00:01:35 | | |
| 1 | SORT ORDER BY | | 918K| 25M| 84M| 7845 (3)| 00:01:35 | | |
| 2 | PARTITION RANGE ALL| | 918K| 25M| | 426 (9)| 00:00:06 | 1 | 28 |
| 3 | TABLE ACCESS FULL | SALES | 918K| 25M| | 426 (9)| 00:00:06 | 1 | 28 |
------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
39 recursive calls
3 db block gets
1718 consistent gets
6498 physical reads
0 redo size
36119714 bytes sent via SQL*Net to client
674201 bytes received via SQL*Net from client
61258 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
918843 rows processed
SQL>EXIT
1.17 Hard Parsing vs. Soft Parsing
SQL>CONN system/oracle
SQL>declare
v_cnt number ;
begin
for i in 1..10000 loop
execute immediate 'select /* hard */ count(*) from dual where dummy = to_char('||i||')'
into v_cnt ;
end loop ;
end ;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:17.79
SQL>SELECT sql_text FROM v$sql
WHERE sql_text LIKE '%hard%' ;
SQL_TEXT
----------------------------------------------------------------------------------------------------------------
select /* hard */ count(*) from dual where dummy = to_char(9728)
select /* hard */ count(*) from dual where dummy = to_char(9787)
select /* hard */ count(*) from dual where dummy = to_char(9400)
select /* hard */ count(*) from dual where dummy = to_char(9733)
select /* hard */ count(*) from dual where dummy = to_char(9375)
…
SQL>ALTER SYSTEM FLUSH SHARED_POOL ;
SQL>declare
v_cnt number ;
begin
for i in 1..10000 loop
select count(*) into v_cnt from dual where dummy = to_char(i) ;
end loop ;
end ;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.18
SQL>SELECT sql_text FROM v$sql
WHERE sql_text LIKE '%DUMMY = TO_CHAR%' ;
SQL_TEXT
----------------------------------------------------------------------------------------------------------------
SELECT COUNT(*) FROM DUAL WHERE DUMMY = TO_CHAR(:B1 )
SQL>EXIT
3.16 CURSOR_SHARING parameter
SQL>CONN system/oracle
SQL>ALTER SESSION SET cursor_sharing = exact ;
SQL>declare
v_cnt number ;
begin
for i in 1..10000 loop
execute immediate 'select /* hard */ count(*) from dual where dummy = to_char('||i||')'
into v_cnt ;
end loop ;
end ;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:17.78
SQL>ALTER SESSION SET cursor_sharing = force ;
SQL>declare
v_cnt number ;
begin
for i in 1..10000 loop
execute immediate 'select /* hard */ count(*) from dual where dummy = to_char('||i||')'
into v_cnt ;
end loop ;
end ;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:08.66
SQL> EXIT
4.7 Selectivity / Cardinality & Histogram
SQL>CONN sh/sh
SQL>CREATE TABLE test
(country VARCHAR2(20),
city VARCHAR2(10),
code CHAR(5)) ;
SQL>INSERT INTO test
SELECT 'Korea', DECODE(MOD(rownum,2),0, 'Seoul', 'Busan'), RPAD(rownum,5,' ')
FROM all_objects WHERE rownum <= 10000 ;
SQL>INSERT INTO test SELECT 'Korea', 'Jeju', '11111' FROM dual ;
SQL>INSERT INTO test
SELECT 'Japan', DECODE(MOD(rownum,3),0,'Tokyo', 1, 'Osaka', 'Kyoto'), RPAD(rownum,5,' ')
FROM all_objects WHERE rownum <= 10000 ;
SQL>INSERT INTO test SELECT 'Japan', 'Okinawa', '11111' FROM dual ;
SQL>CREATE INDEX test_idx ON test(country, city) ;
SQL>SELECT table_name, num_rows, blocks, empty_blocks, monitoring , last_analyzed
FROM dba_tables
WHERE owner = 'SH' AND table_name = 'TEST' ;
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS MON LAST_ANAL
------------------------------ ---------- ---------- ------------ --- ---------
TEST YES
SQL>SAVE tab_stat REPLACE
SQL>SELECT column_name, num_distinct, density, num_buckets, histogram
FROM dba_tab_col_statistics
WHERE owner = 'SH' AND table_name = 'TEST' ;
no rows selected
SQL>SAVE col_stat REPLACE
SQL>EXPLAIN PLAN FOR
SELECT * FROM test
WHERE country = 'Korea' AND city = 'Jeju' ;
SQL>SELECT * FROM TABLE(dbms_xplan.display);
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 26 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST_IDX | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
SQL>EXEC DBMS_STATS.GATHER_TABLE_STATS('SH', 'TEST', -
method_opt=>'for all columns size 1', cascade=>true)
SQL>@tab_stat
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS MON LAST_ANAL
------------------------------ ---------- ---------- ------------ --- ---------
TEST 20002 65 0 YES 21-FEB-09
SQL>@col_stat
COLUMN_NAME NUM_DISTINCT DENSITY NUM_BUCKETS HISTOGRAM
------------------------------ ------------ ---------- ----------- ---------------
COUNTRY 2 .5 1 NONE
CITY 7 .142857143 1 NONE
CODE 10001 .00009999 1 NONE
SQL>SELECT 1/2 , 1/7, 1/10001 FROM dual ; -- Density ( Selectivity )
1/2 1/7 1/10001
---------- ---------- ----------
.5 .142857143 .00009999
SQL>EXPLAIN PLAN FOR
SELECT * FROM test
WHERE country = 'Korea' AND city = 'Jeju' ;
SQL>SELECT * FROM TABLE(dbms_xplan.display);
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1429 | 25722 | 17 (6)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 1429 | 25722 | 17 (6)| 00:00:01 |
--------------------------------------------------------------------------
SQL>SELECT ROUND(20002 * 1/2 * 1/7) FROM dual ; -- Cardinality ( Rows )
ROUND(20002*1/2*1/7)
--------------------
1429
SQL>EXPLAIN PLAN FOR
SELECT * FROM test
WHERE country = 'Korea' OR city = 'Jeju' ;
SQL>SELECT * FROM TABLE(dbms_xplan.display) ;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11430 | 200K| 17 (6)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 11430 | 200K| 17 (6)| 00:00:01 |
--------------------------------------------------------------------------
SQL>SELECT ROUND(20002 *( (1/2 + 1/7 ) - ( 1/2 * 1/7 ) ) ) FROM dual ; -- Cardinality
ROUND(20002*((1/2+1/7)-(1/2*1/7)))
----------------------------------
11430
SQL>EXEC DBMS_STATS.GATHER_TABLE_STATS('SH', 'TEST', -
method_opt=>'for all columns size auto', cascade=>true)
SQL>@col_stat
COLUMN_NAME NUM_DISTINCT DENSITY NUM_BUCKETS HISTOGRAM
------------------------------ ------------ ---------- ----------- ---------------
COUNTRY 2 .000024998 2 FREQUENCY
CITY 7 .000024998 7 FREQUENCY
CODE 10001 .00009999 1 NONE
SQL>COLUMN column_name FORMAT A20
SQL>SELECT column_name, endpoint_number, endpoint_value
FROM dba_histograms
WHERE owner = 'SH' AND table_name = 'TEST'
ORDER BY column_name, endpoint_number ;
COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
-------------------- --------------- --------------
CITY 5000 3.4507E+35
CITY 5001 3.8629E+35
CITY 8334 3.9189E+35
CITY 8335 4.1237E+35
CITY 11669 4.1253E+35
CITY 16669 4.3302E+35
CITY 20002 4.3841E+35
CODE 0 2.5507E+35
CODE 1 2.9712E+35
COUNTRY 10001 3.8621E+35
COUNTRY 20002 3.9168E+35
SQL>EXPLAIN PLAN FOR
SELECT * FROM test
WHERE country = 'Korea' AND city = 'Jeju' ;
SQL>SELECT * FROM TABLE(dbms_xplan.display);
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 18 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST_IDX | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
SQL>EXPLAIN PLAN FOR
SELECT * FROM test
WHERE country = :b1 AND city = :b2 ;
SQL>SELECT * FROM TABLE(dbms_xplan.display);
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1429 | 25722 | 17 (6)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 1429 | 25722 | 17 (6)| 00:00:01 |
--------------------------------------------------------------------------
SQL>DROP TABLE test PURGE ;
4.11 Initialization Parameters for Optimizer
SQL>CONN sh/sh
SQL>SHOW PARAMETER optimizer_index_cost_adj
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_index_cost_adj integer 100
SQL>SET AUTOTRACE TRACEONLY EXPLAIN
SQL>SELECT c.cust_last_name, c.cust_main_phone_number, s.promo_id, s.amount_sold
FROM customers c, sales s
WHERE s.cust_id = c.cust_id
AND c.cust_city = 'New Castle'
AND s.amount_sold BETWEEN 3000 AND 3500 ;
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 80 | 4080 | 514 (9)| 00:00:07 | | |
| 1 | NESTED LOOPS | | 80 | 4080 | 514 (9)| 00:00:07 | | |
| 2 | PARTITION RANGE ALL | | 81 | 1134 | 433 (11)| 00:00:06 | 1 | 28 |
|* 3 | TABLE ACCESS FULL | SALES | 81 | 1134 | 433 (11)| 00:00:06 | 1 | 28 |
|* 4 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 1 | 37 | 1 (0)| 00:00:01 | | |
|* 5 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 1 | | 0 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------
SQL>SAVE query REPLACE
SQL>ALTER SESSION SET optimizer_index_cost_adj = 1 ;
SQL>@query
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 80 | 4080 | 102 (56)| 00:00:02 | | |
| 1 | NESTED LOOPS | | 80 | 4080 | 102 (56)| 00:00:02 | | |
| 2 | PARTITION RANGE ALL | | 81 | 1134 | 101 (57)| 00:00:02 | 1 | 28 |
|* 3 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 81 | 1134 | 101 (57)| 00:00:02 | 1 | 28 |
| 4 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
| 5 | BITMAP INDEX FULL SCAN | SALES_PROMO_BIX | | | | | 1 | 28 |
|* 6 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 1 | 37 | 1 (0)| 00:00:01 | | |
|* 7 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 1 | | 1 (0)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------------
SQL>ALTER SESSION SET optimizer_index_cost_adj = 1000 ;
SQL>@query
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 80 | 4080 | 764 (7)| 00:00:10 | | |
|* 1 | HASH JOIN | | 80 | 4080 | 764 (7)| 00:00:10 | | |
| 2 | PARTITION RANGE ALL| | 81 | 1134 | 433 (11)| 00:00:06 | 1 | 28 |
|* 3 | TABLE ACCESS FULL | SALES | 81 | 1134 | 433 (11)| 00:00:06 | 1 | 28 |
|* 4 | TABLE ACCESS FULL | CUSTOMERS | 90 | 3330 | 331 (2)| 00:00:04 | | |
--------------------------------------------------------------------------------------------------
SQL>ALTER SESSION SET optimizer_index_cost_adj = 100 ;
SQL>ALTER SESSION SET optimizer_mode = ALL_ROWS ;
SQL>@query
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 80 | 16720 | 514 (9)| 00:00:07 | | |
| 1 | NESTED LOOPS | | 80 | 16720 | 514 (9)| 00:00:07 | | |
| 2 | PARTITION RANGE ALL | | 81 | 2349 | 433 (11)| 00:00:06 | 1 | 28 |
|* 3 | TABLE ACCESS FULL | SALES | 81 | 2349 | 433 (11)| 00:00:06 | 1 | 28 |
|* 4 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 1 | 180 | 1 (0)| 00:00:01 | | |
|* 5 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 1 | | 0 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------
SQL>ALTER SESSION SET optimizer_mode = FIRST_ROWS_1 ;
SQL>@query
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 102 | 21 (10)| 00:00:01 | | |
| 1 | NESTED LOOPS | | 2 | 102 | 21 (10)| 00:00:01 | | |
| 2 | PARTITION RANGE ALL | | 60 | 840 | 18 (12)| 00:00:01 | 1 | 28 |
|* 3 | TABLE ACCESS FULL | SALES | 60 | 840 | 18 (12)| 00:00:01 | 1 | 28 |
|* 4 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 1 | 37 | 1 (0)| 00:00:01 | | |
|* 5 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 1 | | 0 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------
SQL>ALTER SESSION SET optimizer_mode = FIRST_ROWS_100 ;
SQL>@query
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 80 | 4080 | 514 (9)| 00:00:07 | | |
| 1 | NESTED LOOPS | | 80 | 4080 | 514 (9)| 00:00:07 | | |
| 2 | PARTITION RANGE ALL | | 81 | 1134 | 433 (11)| 00:00:06 | 1 | 28 |
|* 3 | TABLE ACCESS FULL | SALES | 81 | 1134 | 433 (11)| 00:00:06 | 1 | 28 |
|* 4 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 1 | 37 | 1 (0)| 00:00:01 | | |
|* 5 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 1 | | 0 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------
SQL>EXIT
5.4 Access Paths
SQL>CONN sh/sh
SQL>SET AUTOTRACE TRACEONLY EXPLAIN
SQL>SELECT /*+ FULL (sales) */ * FROM sales WHERE cust_id = 53050 ;
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 130 | 3770 | 419 (8)| 00:00:06 | | |
| 1 | PARTITION RANGE ALL| | 130 | 3770 | 419 (8)| 00:00:06 | 1 | 28 |
|* 2 | TABLE ACCESS FULL | SALES | 130 | 3770 | 419 (8)| 00:00:06 | 1 | 28 |
---------------------------------------------------------------------------------------------
SQL>SELECT /*+ ROWID */ *
FROM sales WHERE cust_id = 53050 AND rowid = 'AAAG27AAGAAAAEMAB7' ;
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 29 | 1 (0)| 00:00:01 | | |
|* 1 | TABLE ACCESS BY USER ROWID| SALES | 1 | 29 | 1 (0)| 00:00:01 | ROWID | ROWID |
----------------------------------------------------------------------------------------------------
SQL> SELECT /*+ INDEX_ASC (c customers_pk) */ *
FROM customers c WHERE cust_id > 60000 ;
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23634 | 4154K| 23244 (1)| 00:04:39 |
| 1 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 23634 | 4154K| 23244 (1)| 00:04:39 |
|* 2 | INDEX RANGE SCAN | CUSTOMERS_PK | 23634 | | 51 (2)| 00:00:01 |
--------------------------------------------------------------------------------------------
SQL>SELECT * FROM sales SAMPLE(10) ;
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 91884 | 2602K| 410 (6)| 00:00:05 | | |
| 1 | PARTITION RANGE ALL | | 91884 | 2602K| 410 (6)| 00:00:05 | 1 | 28 |
| 2 | TABLE ACCESS SAMPLE| SALES | 91884 | 2602K| 410 (6)| 00:00:05 | 1 | 28 |
----------------------------------------------------------------------------------------------
5.9 Index Scans
SQL> SELECT /*+ INDEX(c customers_pk) */ *
FROM customers c WHERE cust_id = 60000 ;
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 180 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 1 | 180 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
SQL>SELECT /*+ INDEX_DESC (c customers_pk) */ *
FROM customers c WHERE cust_id > 60000 ;
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23634 | 4154K| 23244 (1)| 00:04:39 |
| 1 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 23634 | 4154K| 23244 (1)| 00:04:39 |
|* 2 | INDEX RANGE SCAN DESCENDING| CUSTOMERS_PK | 23634 | | 51 (2)| 00:00:01 |
---------------------------------------------------------------------------------------------
SQL>CREATE INDEX cust_gend_birth_idx ON customers(cust_gender, cust_year_of_birth)
COMPUTE STATISTICS ;
SQL>SELECT /*+ INDEX_SS (c cust_gend_birth_idx) */ *
FROM customers c WHERE cust_year_of_birth BETWEEN 1930 AND 1935 ;
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5084 | 893K| 3337 (1)| 00:00:41 |
| 1 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 5084 | 893K| 3337 (1)| 00:00:41 |
|* 2 | INDEX SKIP SCAN | CUST_GEND_BIRTH_IDX | 4029 | | 12 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
SQL>SELECT /*+ INDEX(c customers_pk) */ count(*) FROM customers c ;
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 118 (2)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| CUSTOMERS_PK | 55500 | 118 (2)| 00:00:02 |
-------------------------------------------------------------------------
SQL>SELECT /*+ INDEX_FFS(c customers_pk) */ count(*) FROM customers c ;
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 (4)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| CUSTOMERS_PK | 55500 | 28 (4)| 00:00:01 |
------------------------------------------------------------------------------
SQL>SELECT /*+ INDEX_JOIN (p products_pk products_prod_cat_ix) */ prod_id, prod_category
FROM products p WHERE prod_id > 20 ;
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 68 | 1428 | 3 (34)| 00:00:01 |
|* 1 | VIEW | index$_join$_001 | 68 | 1428 | 3 (34)| 00:00:01 |
|* 2 | HASH JOIN | | | | | |
|* 3 | INDEX RANGE SCAN | PRODUCTS_PK | 68 | 1428 | 2 (50)| 00:00:01 |
| 4 | INDEX FAST FULL SCAN| PRODUCTS_PROD_CAT_IX | 68 | 1428 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
SQL>ALTER TABLE customers DROP PRIMARY KEY CASCADE ;
SQL>ALTER TABLE customers ADD CONSTRAINT customers_pk PRIMARY KEY(cust_id) ;
SQL>ALTER TABLE sales ADD FOREIGN KEY(cust_id) REFERENCES customers(cust_id) ;
SQL>CREATE BITMAP INDEX sales_cust_gender_bjix ON sales(c.cust_gender)
FROM sales s, customers c
WHERE s.cust_id = c.cust_id
LOCAL COMPUTE STATISTICS ;
SQL>SELECT /*+ index_combine (s sales_cust_gender_bjix) */ s.time_id, s.amount_sold
FROM sales s, customers c
WHERE s.cust_id = c.cust_id AND c.cust_gender = 'F' ;
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 459K| 8075K| 2420 (2)| 00:00:30 | | |
| 1 | PARTITION RANGE ALL | | 459K| 8075K| 2420 (2)| 00:00:30 | 1 | 28 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 459K| 8075K| 2420 (2)| 00:00:30 | 1 | 28 |
| 3 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
|* 4 | BITMAP INDEX SINGLE VALUE | SALES_CUST_GENDER_BJIX | | | | | 1 | 28 |
-----------------------------------------------------------------------------------------------------------------------------
SQL>SELECT s.time_id, s.amount_sold
FROM sales s, customers c
WHERE s.cust_id = c.cust_id AND c.cust_gender = 'F' ;
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 918K| 21M| 591 (10)| 00:00:08 | | |
|* 1 | HASH JOIN | | 918K| 21M| 591 (10)| 00:00:08 | | |
|* 2 | VIEW | index$_join$_002 | 27750 | 189K| 151 (3)| 00:00:02 | | |
|* 3 | HASH JOIN | | | | | | | |
| 4 | BITMAP CONVERSION TO ROWIDS| | 27750 | 189K| 2 (0)| 00:00:01 | | |
|* 5 | BITMAP INDEX SINGLE VALUE | CUSTOMERS_GENDER_BIX | | | | | | |
| 6 | INDEX FAST FULL SCAN | CUSTOMERS_PK | 27750 | 189K| 147 (2)| 00:00:02 | | |
| 7 | PARTITION RANGE ALL | | 918K| 15M| 426 (9)| 00:00:06 | 1 | 28 |
| 8 | TABLE ACCESS FULL | SALES | 918K| 15M| 426 (9)| 00:00:06 | 1 | 28 |
-----------------------------------------------------------------------------------------------------------------------
5.22 Join Methods
SQL>CONN hr/hr
SQL>SET AUTOTRACE TRACEONLY EXPLAIN
SQL>CREATE INDEX emp_dept_idx ON employees(department_id) ;
SQL>SELECT /*+ USE_NL( e d ) */ *
FROM departments d , employees e
WHERE e.department_id = d.department_id ;
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106 | 9328 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 106 | 9328 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | EMPLOYEES | 107 | 7276 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 20 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
SQL>SELECT /*+ ORDERED USE_NL( e d ) */ *
FROM departments d , employees e
WHERE e.department_id = d.department_id ;
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106 | 9328 | 10 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 4 | 272 | 1 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 106 | 9328 | 10 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | DEPARTMENTS | 27 | 540 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 10 | | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
SQL>SELECT /*+ USE_MERGE( e d ) */ *
FROM departments d , employees e
WHERE e.department_id = d.department_id ;
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106 | 9328 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 106 | 9328 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 540 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 107 | 7276 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMPLOYEES | 107 | 7276 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
SQL>SELECT /*+ USE_HASH( e d ) */ *
FROM departments d , employees e
WHERE e.department_id = d.department_id ;
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106 | 9328 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 106 | 9328 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPARTMENTS | 27 | 540 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMPLOYEES | 107 | 7276 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------
5.35 Subquery
SQL>CONN system/oracle
SQL>SET AUTOTRACE TRACEONLY EXPLAIN
SQL>SELECT c.* FROM sh.customers c
WHERE c.country_id IN ( SELECT country_id FROM sh.countries
WHERE country_subregion = 'Asia' ) ;
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8398 | 1623K| 337 (3)| 00:00:05 |
|* 1 | HASH JOIN | | 8398 | 1623K| 337 (3)| 00:00:05 |
|* 2 | TABLE ACCESS FULL| COUNTRIES | 3 | 54 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| CUSTOMERS | 55500 | 9755K| 333 (2)| 00:00:04 |
--------------------------------------------------------------------------------
SQL>SELECT p.* FROM sh.promotions p
WHERE p.promo_cost = ( SELECT MAX(promo_cost) FROM sh.promotions
WHERE promo_category = p.promo_category ) ;
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 1143 | 30 (7)| 00:00:01 |
|* 1 | HASH JOIN | | 9 | 1143 | 30 (7)| 00:00:01 |
| 2 | VIEW | VW_SQ_1 | 9 | 270 | 15 (7)| 00:00:01 |
| 3 | HASH GROUP BY | | 9 | 99 | 15 (7)| 00:00:01 |
| 4 | TABLE ACCESS FULL| PROMOTIONS | 503 | 5533 | 14 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | PROMOTIONS | 503 | 48791 | 14 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
SQL>SELECT c.* FROM sh.customers c
WHERE c.cust_income_level = 'F: 110,000 - 129,999'
AND c.country_id NOT IN ( SELECT country_id FROM sh.countries
WHERE country_subregion = 'Europe' ) ;
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3892 | 752K| 336 (2)| 00:00:05 |
|* 1 | HASH JOIN RIGHT ANTI| | 3892 | 752K| 336 (2)| 00:00:05 |
|* 2 | TABLE ACCESS FULL | COUNTRIES | 3 | 54 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | CUSTOMERS | 4622 | 812K| 332 (2)| 00:00:04 |
----------------------------------------------------------------------------------
SQL>SELECT e.* FROM hr.employees e
WHERE e.department_id = 80 AND e.job_id = 'SA_REP'
AND EXISTS ( SELECT 1 FROM oe.orders o
WHERE o.sales_rep_id = e.employee_id ) ;
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 355 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS SEMI | | 5 | 355 | 3 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| EMPLOYEES | 10 | 680 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | ORD_SALES_REP_IX | 35 | 105 | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
6.4 Execution Plan
SQL>CONN system/oracle
SQL>ALTER SYSTEM FLUSH SHARED_POOL ;
SQL>COLUMN object_name FORMAT A30
SQL>SELECT owner, object_name, object_type FROM dba_objects
WHERE object_name = 'PLAN_TABLE' ;
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------ -------------------
PUBLIC PLAN_TABLE SYNONYM
SQL>DESC PLAN_TABLE
SQL> EXPLAIN PLAN SET STATEMENT_ID = 'demo1' FOR
SELECT * FROM hr.employees WHERE department_id = 10 ;
SQL>SELECT * FROM TABLE(dbms_xplan.display) ;
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 68 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 68 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
SQL>SELECT * FROM TABLE(dbms_xplan.display('plan_table','demo1','serial')) ;
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 68 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 68 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
SQL>SELECT * FROM TABLE(dbms_xplan.display('plan_table','demo1','basic')) ;
---------------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES |
| 2 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX |
---------------------------------------------------------
SQL>SELECT * FROM hr.employees WHERE department_id = 10 ;
SQL>COLUMN sql_text FORMAT A60
SQL>SELECT sql_id, sql_text FROM v$sql
WHERE sql_text LIKE '% hr.employees%' ;
SQL_ID SQL_TEXT
------------- ------------------------------------------------------------
7wfgkxh8fr7ab SELECT * FROM hr.employees WHERE department_id = 10
SQL>SELECT * FROM TABLE(dbms_xplan.display_cursor('7wfgkxh8fr7ab')) ;
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 68 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
SQL>SELECT * FROM v$sql_plan_statistics ;
no rows selected
SQL>SHOW PARAMETER statistics_level
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
statistics_level string TYPICAL
SQL>ALTER SYSTEM SET statistics_level = ALL ;
SQL>SELECT * FROM v$sql_plan_statistics ; -- SQL Developer
SQL> SELECT * FROM v$sql_plan_statistics_all ; -- SQL Developer
SQL>SET AUTOTRACE ON
SQL>SELECT COUNT(*) FROM hr.employees ;
SQL>SET AUTOTRACE ON EXPLAIN
SQL>SELECT COUNT(*) FROM hr.employees ;
SQL>SET AUTOTRACE ON STATISTICS
SQL>SELECT COUNT(*) FROM hr.employees ;
SQL>SET AUTOTRACE TRACEONLY
SQL>SELECT COUNT(*) FROM hr.employees ;
SQL>SET AUTOTRACE TRACEONLY EXPLAIN
SQL>SELECT COUNT(*) FROM hr.employees ;
SQL>SET AUTOTRACE OFF
SQL>SELECT COUNT(*) FROM hr.employees ;
SQL>EXIT
7.15 DML Monitoring
SQL>CONN system/oracle
SQL>SHOW PARAMETER statistics_level
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
statistics_level string ALL
SQL>CREATE TABLE test
AS SELECT * FROM all_objects WHERE 1 = 0 ;
SQL> SELECT table_name, monitoring
FROM user_tables WHERE table_name = 'TEST' ;
TABLE_NAME MON
------------------------------ ---
TEST YES
SQL>INSERT INTO test SELECT * FROM all_objects WHERE rownum <= 100 ;
SQL>DELETE test WHERE rownum <= 20 ;
SQL> SELECT table_name, inserts, updates, deletes, truncated
FROM user_tab_modifications WHERE table_name = 'TEST' ;
no rows selected
SQL>EXEC dbms_stats.flush_database_monitoring_info ;
SQL> SELECT table_name, inserts, updates, deletes, truncated
FROM user_tab_modifications WHERE table_name = 'TEST' ;
TABLE_NAME INSERTS UPDATES DELETES TRU
------------------------------ ---------- ---------- ---------- ---
TEST 100 0 20 NO
7.26 Bind Variable Peeking
SQL>CONN system/oracle
SQL>CREATE TABLE student
AS SELECT object_id id, object_name name,
decode(object_type,'EVALUATION CONTEXT', 'A',
'CONSUMER GROUP','C',
'SEQUENCE','D',
'INDEX','F','B') grade
FROM all_objects ;
SQL> SELECT grade, count(*) FROM student
GROUP BY grade ORDER BY 1;
G COUNT(*)
- ----------
A 14
B 47839
C 3
D 141
F 1781
SQL>ALTER TABLE student ADD CONSTRAINT std_pk PRIMARY KEY(id) ;
SQL>CREATE INDEX std_grade_idx ON student(grade) ;
SQL>EXEC dbms_stats.gather_table_stats(user,'STUDENT', -
method_opt=>'for all indexed columns size auto', CASCADE=>true)
SQL>ALTER SYSTEM FLUSH SHARED_POOL ;
SQL>SET AUTOTRACE ON EXPLAIN
SQL>SELECT count(*), min(id) FROM student WHERE grade = 'A' ;
COUNT(*) MIN(ID)
---------- ----------
14 5077
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| STUDENT | 1 | 7 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | STD_GRADE_IDX | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
SQL>SELECT count(*), min(id) FROM student WHERE grade = 'B' ;
COUNT(*) MIN(ID)
---------- ----------
47839 2
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 58 (4)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | TABLE ACCESS FULL| STUDENT | 48610 | 332K| 58 (4)| 00:00:01 |
------------------------------------------------------------------------------
SQL>SET AUTOTRACE OFF
SQL>EXPLAIN PLAN FOR
SELECT count(*), min(id)
FROM student
WHERE grade = :x ;
SQL>SELECT * FROM TABLE(dbms_xplan.display) ;
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 58 (4)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | TABLE ACCESS FULL| STUDENT | 16846 | 115K| 58 (4)| 00:00:01 |
------------------------------------------------------------------------------
SQL>CONN /as sysdba
SQL>COLUMN "Parameter" FORMAT a40
SQL>COLUMN "Session Value" FORMAT a15
SQL>COLUMN "Instance Value" FORMAT a15
SQL>select a.ksppinm "Parameter",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value"
from x$ksppi a, x$ksppcv b, x$ksppsv c
where a.indx = b.indx and a.indx = c.indx
and a.ksppinm = '_optim_peek_user_binds' ;
Parameter Session Value Instance Value
---------------------------------------- --------------- ---------------
_optim_peek_user_binds TRUE TRUE
SQL>CONN system/oracle
SQL>ALTER SYSTEM FLUSH SHARED_POOL ;
SQL>ALTER SESSION SET SQL_TRACE = TRUE ;
SQL>variable x varchar2(1)
SQL>DECLARE
v_cnt NUMBER ;
v_min NUMBER ;
BEGIN
:x := 'A' ;
SELECT count(*), min(id) INTO v_cnt, v_min
FROM student
WHERE grade = :x ;
:x := 'B' ;
SELECT count(*), min(id) INTO v_cnt, v_min
FROM student
WHERE grade = :x ;
END;
/
SQL>ALTER SESSION SET SQL_TRACE = FALSE ;
SQL>SELECT d.value||'/'||lower(c.instance_name)||'_ora_'||b.spid||'.trc' "User Trace File"
FROM v$session a, v$process b, v$instance c, v$parameter d,
( select * from v$mystat where rownum = 1 ) e
WHERE a.paddr = b.addr AND a.sid = e.sid AND d.name = 'user_dump_dest' ;
User Trace File
----------------------------------------------------------------------------------------------------------------
/u01/app/oracle/admin/orcl/udump/orcl_ora_14011.trc
SQL>SAVE ck_trace REPLACE
SQL>host tkprof /u01/app/oracle/admin/orcl/udump/orcl_ora_14011.trc $HOME/report1.txt sys=no
SQL>host vi $HOME/report1.txt
SELECT COUNT(*), MIN(ID)
FROM
STUDENT WHERE GRADE = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.01 0.00 0 0 0 0
Fetch 2 1.22 1.25 0 346 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 1.23 1.26 0 346 0 2
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5 (SYSTEM) (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=336 pr=0 pw=0 time=1251136 us)
47839 TABLE ACCESS BY INDEX ROWID STUDENT (cr=336 pr=0 pw=0 time=1052443 us)
47839 INDEX RANGE SCAN STD_GRADE_IDX (cr=88 pr=0 pw=0 time=430543 us)(object id 52562)
SQL>CONN system/oracle
SQL>ALTER SYSTEM FLUSH SHARED_POOL ;
SQL>ALTER SESSION SET SQL_TRACE = TRUE ;
SQL>variable x varchar2(1)
SQL>DECLARE
v_cnt NUMBER ;
v_min NUMBER ;
BEGIN
:x := 'B' ;
SELECT count(*), min(id) INTO v_cnt, v_min
FROM student WHERE grade = :x ;
:x := 'A' ;
SELECT count(*), min(id) INTO v_cnt, v_min
FROM student WHERE grade = :x ;
END;
/
SQL>ALTER SESSION SET SQL_TRACE = FALSE ;
SQL>@ck_trace
User Trace File
---------------------------------------------------------------------------------------------------------------
/u01/app/oracle/admin/orcl/udump/orcl_ora_14119.trc
SQL>host tkprof /u01/app/oracle/admin/orcl/udump/orcl_ora_14119.trc $HOME/report2.txt sys=no
SQL>host vi $HOME/report2.txt
SELECT COUNT(*), MIN(ID)
FROM
STUDENT WHERE GRADE = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.01 0.02 0 0 0 0
Fetch 2 0.56 0.55 0 504 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.58 0.57 0 504 0 2
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=252 pr=0 pw=0 time=3885 us)
14 TABLE ACCESS FULL STUDENT (cr=252 pr=0 pw=0 time=3806 us)
SQL>ALTER SESSION SET "_optim_peek_user_binds"= false ;
SQL>ALTER SESSION SET "_optim_peek_user_binds"= true ;
SQL>DROP TABLE student PURGE ;
SQL>EXIT
8.6 DBMS_MONITOR
# Main Session
SQL>CONN system/oracle
SQL>SHOW PARAMETER user_dump_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest string /u01/app/oracle/admin/orcl/udump
SQL>host rm /u01/app/oracle/admin/orcl/udump/*
SQL>host ls /u01/app/oracle/admin/orcl/udump/
# New Session 1
SQL>CONN hr/hr
# New Session 2
SQL>CONN hr/hr
SQL>EXEC dbms_session.set_identifier('ORCL_HR')
# New Session 3
SQL>CONN hr/hr
SQL>EXEC dbms_session.set_identifier('ORCL_HR')
# Main Session
SQL>SELECT s.username, s.sid, s.serial#, s.client_identifier, p.spid
FROM v$session s, v$process p
WHERE s.paddr = p.addr AND s.username = 'HR' ;
USERNAME SID SERIAL# CLIENT_IDENTIFIER SPID
------------------------------ ---------- ---------- ------------------ -------------------------------------
HR 136 43 5606
HR 144 275 ORCL_HR 5639
HR 138 90 ORCL_HR 5674
SQL>EXEC dbms_monitor.session_trace_enable(session_id=>136, serial_num=>43, waits=>true )
# Session 1
SQL>SELECT * FROM employees WHERE department_id = 20 ;
SQL>SELECT * FROM departments ;
# Main Session
SQL> EXEC dbms_monitor.session_trace_disable(136, 43)
SQL>host ls /u01/app/oracle/admin/orcl/udump/
orcl_ora_5606.trc
SQL>EXEC dbms_monitor.client_id_stat_enable(client_id=>'ORCL_HR')
SQL>EXEC dbms_monitor.client_id_trace_enable('ORCL_HR',TRUE,TRUE)
# Session 2
SQL>SELECT * FROM employees WHERE department_id = 10 ;
# Session 3
SQL>SELECT * FROM departments WHERE department_id = 10 ;
# Main Session
SQL>COLUMN client_identifier FORMAT A10
SQL>SELECT * FROM v$client_stats ;
CLIENT_IDE STAT_ID STAT_NAME VALUE
---------- ---------- ---------------------------------------------------------------- ----------
ORCL_HR 2882015696 user calls 14
ORCL_HR 3649082374 DB time 204094
ORCL_HR 2748282437 DB CPU 171870
ORCL_HR 63887964 parse count (total) 21
ORCL_HR 1431595225 parse time elapsed 193504
ORCL_HR 2453370665 execute count 148
ORCL_HR 2821698184 sql execute elapsed time 59581
ORCL_HR 85052502 opened cursors cumulative 39
ORCL_HR 3143187968 session logical reads 173
…
SQL>EXEC dbms_monitor.client_id_stat_disable(client_id=>'ORCL_HR')
SQL> SELECT username, sid, serial#, client_identifier, sql_trace
FROM v$session WHERE username = 'HR' ;
USERNAME SID SERIAL# CLIENT_IDENTIFIER SQL_TRACE
------------------------------ ---------- ---------- ------------------ -------------------------------------
HR 136 43 DISABLED
HR 144 275 ORCL_HR DISABLED
HR 138 90 ORCL_HR DISABLED
SQL> SELECT trace_type, primary_id, waits, binds
FROM dba_enabled_traces ;
TRACE_TYPE PRIMARY_ID WAITS BINDS
--------------------- ---------------------------------------------------------------- ----- -----
CLIENT_ID ORCL_HR TRUE TRUE
SQL>EXEC dbms_monitor.client_id_trace_disable('ORCL_HR')
SQL>host ls /u01/app/oracle/admin/orcl/udump/
orcl_ora_5606.trc orcl_ora_5639.trc orcl_ora_5674.trc
SQL>host vi /u01/app/oracle/admin/orcl/udump/orcl_ora_5606.trc
SQL>host vi /u01/app/oracle/admin/orcl/udump/orcl_ora_5639.trc
SQL>host vi /u01/app/oracle/admin/orcl/udump/orcl_ora_5674.trc
SQL>EXIT -- All Sessions
8.9 TRCSESS & TKPROF
$ cd /u01/app/oracle/admin/orcl/udump/
$ trcsess clientid=ORCL_HR orcl_ora_5639.trc orcl_ora_5674.trc output=output.trc
$ vi output.trc
$ tkprof output.trc report.txt sys=no
$ vi report.txt
SELECT *
FROM
employees WHERE department_id = 10
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.04 0.04 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.04 0.04 0 3 0 1
…
SELECT *
FROM
departments WHERE department_id = 10
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.03 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.01 0.02 1 2 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.04 0.06 1 2 0 1
…
$ sqlplus sh/sh
SQL>ALTER SESSION SET tracefile_identifier = 'my_file' ;
SQL>ALTER SESSION SET EVENTS '10046 trace name context forever, level 8' ;
SQL> SELECT count(*)
FROM customers c, sales s
WHERE s.cust_id = c.cust_id
AND c.cust_city = 'New Castle'
AND s.amount_sold BETWEEN 3000 AND 3500 ;
SQL>ALTER SESSION SET EVENTS '10046 trace name context off' ;
SQL>host ls /u01/app/oracle/admin/orcl/udump/
orcl_ora_5606.trc orcl_ora_5639.trc orcl_ora_5674.trc orcl_ora_8986_my_file.trc output.trc report.txt
SQL>host tkprof orcl_ora_8986_my_file.trc report_my_file.txt sys=no
SQL>host vi report_my_file.txt
SQL>EXIT
9.6 Automatic Database Diagnostic Monitor
SQL>conn /as sysdba
SQL>@$HOME/scripts/print_table.sql
SQL>EXEC print_table('select * from dba_hist_wr_control')
-------------------------------------------------------
DBID : 1204738453
SNAP_INTERVAL : +00000 01:00:00.0
RETENTION : +00007 00:00:00.0
TOPNSQL : DEFAULT
-------------------------------------------------------
SQL> select snap_id,
to_char(startup_time, 'YYYY/MM/DD HH24:MI:SS') start_time,
to_char(begin_interval_time,'YYYY/MM/DD HH24:MI:SS') begin_time
from dba_hist_snapshot
order by snap_id ;
SNAP_ID START_TIME BEGIN_TIME
---------- ------------------- -------------------
14 2009/02/22 05:28:12 2009/02/22 05:28:12
15 2009/02/22 05:28:12 2009/02/22 05:39:27
16 2009/02/22 05:28:12 2009/02/22 07:00:32
SQL>SAVE dba_hist_snapshot REPLACE
SQL>EXEC dbms_workload_repository.create_snapshot
SQL>@dba_hist_snapshot
SNAP_ID START_TIME BEGIN_TIME
---------- ------------------- -------------------
14 2009/02/22 05:28:12 2009/02/22 05:28:12
15 2009/02/22 05:28:12 2009/02/22 05:39:27
16 2009/02/22 05:28:12 2009/02/22 07:00:32
17 2009/02/22 05:28:12 2009/02/22 08:00:37
SQL>@?/rdbms/admin/awrrpt.sql
SQL>host vi awrrpt.txt
SQL>@?/rdbms/admin/addmrpt.sql
SQL>host vi addmrpt.txt
FINDING 4: 4.3% impact (95 seconds)
-----------------------------------
SQL statements consuming significant database time were found.
RECOMMENDATION 1: SQL Tuning, 4.4% benefit (96 seconds)
ACTION: Investigate the SQL statement with SQL_ID "dfbju7gah4k6m" for
possible performance improvements.
RELEVANT OBJECT: SQL statement with SQL_ID dfbju7gah4k6m
call DY_SQL ( )
RATIONALE: SQL statement with SQL_ID "dfbju7gah4k6m" was executed 5
times and had an average elapsed time of 19 seconds.
SQL>EXIT
10.7 Automatic Tuning Advisor
SQL>CONN hr/hr
SQL>@$HOME/scripts/demobld.sql
SQL>EXEC DBMS_STATS.GATHER_SCHEMA_STATS('HR')
SQL>SET AUTOTRACE ON EXPLAIN
SQL>SELECT ename, sal * 12 FROM emp
WHERE sal * 12 = 15000 ;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 10 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
SQL>CONN /as sysdba
SQL>DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := 'SELECT ename, sal * 12 FROM emp WHERE sal*12 = 15000';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => my_sqltext,
user_name => 'HR',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'sql_tuning_task_1' );
END;
/
SQL>EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK (task_name =>'sql_tuning_task_1')
SQL>SET LONG 100000
SQL>SET LONGCHUNKSIZE 1000
SQL>SELECT dbms_sqltune.report_tuning_task('sql_tuning_task_1') FROM dual;
Schema Name: HR
SQL ID : bvqnwdcnkcs9n
SQL Text : SELECT ename, sal * 12 FROM emp WHERE sal*12 = 15000
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- Index Finding (see explain plans section below)
-------------------------------------------------------------------------------
The execution plan of this statement can be improved by creating one or more
indices.
Recommendation (estimated benefit: 100%)
----------------------------------------
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index HR.IDX$$_007B0001 on HR.EMP('SAL'*12);
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 3956160932
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_TUNING_TASK_1')
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 10 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SAL"*12=15000)
2- Using New Indices
--------------------
Plan hash value: 995899183
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 10 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX$$_007B0001 | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
-------------------------------------------------------------------------------
2 - access("EMP".???)
-------------------------------------------------------------------------------
10.10 SQL Profiling
SQL>CONN hr/hr
SQL>CREATE TABLE test (n number ) ;
SQL>begin
for i in 1 .. 10000 loop
insert into test values(i);
end loop;
commit ;
end;
/
SQL>CREATE INDEX test_idx ON test(n);
SQL>exec DBMS_STATS.GATHER_TABLE_STATS('HR','TEST',CASCADE=>TRUE)
SQL>SET AUTOTRACE ON EXPLAIN
SQL>select /*+ no_index(test test_idx) */ * from test where n=1 ;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 3 | 6 (0)| 00:00:01 |
--------------------------------------------------------------------------
SQL>CONN /as sysdba
SQL>DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := 'select /*+ no_index(test test_idx) */ * from test where n= 1';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => my_sqltext,
user_name => 'HR',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'sql_tuning_task_2') ;
END;
/
SQL>EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name =>'sql_tuning_task_2')
SQL>SET LONG 100000
SQL>SET LONGCHUNKSIZE 1000
SQL>SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'sql_tuning_task_2') from dual ;
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 84.11%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'sql_tuning_task_2',
replace => TRUE);
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
2- Using SQL Profile
--------------------
Plan hash value: 2882402178
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| TEST_IDX | 1 | 3 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------
SQL> DECLARE my_sqlprofile_name VARCHAR2(30);
BEGIN
my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
task_name => 'sql_tuning_task_2',
name => 'my_sql_profile');
END;
/
SQL>SELECT to_char(sql_text) FROM dba_sql_profiles;
TO_CHAR(SQL_TEXT)
------------------------------------------------------------------------------------------------------------
select /*+ no_index(test test_idx) */ * from test where n= 1
SQL>CONN hr/hr
SQL>SET AUTOTRACE ON EXPLAIN
SQL>select /*+ no_index(test test_idx) */ * from test where n=1;
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| TEST_IDX | 1 | 3 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------
SQL>DROP TABLE test PURGE ;
SQL>CONN /as sysdba
SQL>EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'my_sql_profile')
SQL>EXEC DBMS_ADVISOR.DELETE_TASK ('sql_tuning_task_1')
SQL>EXEC DBMS_ADVISOR.DELETE_TASK ('sql_tuning_task_2')
SQL>EXIT
big_table.sqlOracle Database 10g :
SQL Tuning
glogin.sql
$ cat >> $ORACLE_HOME/sqlplus/admin/glogin.sql << EOF
SET LINESIZE 130
SET PAGESIZE 50
SET TIMING ON
SET FEEDBACK 1
SET SERVEROUTPUT ON SIZE 200000
DEFINE _editor=vi
EOF
Database Startup
$ lsnrctl start
$ sqlplus '/as sysdba' << EOF
startup
exit
EOF
$ emctl start dbconsole
High - Load SQL
$ sqlplus system/oracle
SQL>CREATE OR REPLACE PROCEDURE dy_sql IS
v_cnt number ;
BEGIN
FOR i IN 1..10000 LOOP
execute immediate 'select /* hard */ count(*) from dual where dummy = to_char('||i||')'
into v_cnt ;
END LOOP ;
END dy_sql ;
/
SQL>EXEC dbms_scheduler.create_job(job_name => 'DY_SQL_JOB', -
job_type => 'STORED_PROCEDURE', -
job_action => 'DY_SQL', -
start_date => SYSTIMESTAMP, -
repeat_interval => 'FREQ=HOURLY; INTERVAL=4', -
end_date => SYSTIMESTAMP + 6, -
enabled => TRUE)
SQL>EXEC dbms_scheduler.run_job('DY_SQL_JOB')
1.13 Automated SQL Execution Memory (PGA) Management
SQL>CONN /as sysdba
SQL>ALTER SYSTEM SET workarea_size_policy=MANUAL ;
SQL>ALTER SYSTEM FLUSH BUFFER_CACHE ;
SQL>SHOW PARAMETER sort_area_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sort_area_size integer 65536
SQL>SET AUTOTRACE TRACEONLY
SQL>SELECT * FROM sh.sales ORDER BY 1,2,3,4 ;
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 918K| 25M| | 19810 (2)| 00:03:58 | | |
| 1 | SORT ORDER BY | | 918K| 25M| 84M| 19810 (2)| 00:03:58 | | |
| 2 | PARTITION RANGE ALL| | 918K| 25M| | 426 (9)| 00:00:06 | 1 | 28 |
| 3 | TABLE ACCESS FULL | SALES | 918K| 25M| | 426 (9)| 00:00:06 | 1 | 28 |
------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
15210 recursive calls
770 db block gets
4847 consistent gets
18960 physical reads
0 redo size
36119714 bytes sent via SQL*Net to client
674201 bytes received via SQL*Net from client
61258 SQL*Net roundtrips to/from client
125 sorts (memory)
1 sorts (disk)
918843 rows processed
SQL>ALTER SYSTEM SET sort_area_size= 10485760 SCOPE=SPFILE ;
SQL>STARTUP FORCE
SQL>ALTER SYSTEM FLUSH BUFFER_CACHE ;
SQL>SET AUTOTRACE TRACEONLY
SQL>SELECT * FROM sh.sales ORDER BY 1,2,3,4 ;
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 918K| 25M| | 7353 (3)| 00:01:29 | | |
| 1 | SORT ORDER BY | | 918K| 25M| 84M| 7353 (3)| 00:01:29 | | |
| 2 | PARTITION RANGE ALL| | 918K| 25M| | 426 (9)| 00:00:06 | 1 | 28 |
| 3 | TABLE ACCESS FULL | SALES | 918K| 25M| | 426 (9)| 00:00:06 | 1 | 28 |
------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
39 recursive calls
6 db block gets
1718 consistent gets
6499 physical reads
0 redo size
36119714 bytes sent via SQL*Net to client
674201 bytes received via SQL*Net from client
61258 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
SQL>ALTER SYSTEM SET sort_area_size=104857600 SCOPE=SPFILE ;
SQL>STARTUP FORCE
SQL>ALTER SYSTEM FLUSH BUFFER_CACHE ;
SQL>SET AUTOTRACE TRACEONLY
SQL>SELECT * FROM sh.sales ORDER BY 1,2,3,4 ;
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 918K| 25M| 543 (29)| 00:00:07 | | |
| 1 | SORT ORDER BY | | 918K| 25M| 543 (29)| 00:00:07 | | |
| 2 | PARTITION RANGE ALL| | 918K| 25M| 426 (9)| 00:00:06 | 1 | 28 |
| 3 | TABLE ACCESS FULL | SALES | 918K| 25M| 426 (9)| 00:00:06 | 1 | 28 |
----------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
994 recursive calls
0 db block gets
2062 consistent gets
1661 physical reads
0 redo size
36119714 bytes sent via SQL*Net to client
674201 bytes received via SQL*Net from client
61258 SQL*Net roundtrips to/from client
11 sorts (memory)
0 sorts (disk)
918843 rows processed
SQL>ALTER SYSTEM SET workarea_size_policy=AUTO ;
SQL>ALTER SYSTEM SET pga_aggregate_target=100M ;
SQL>ALTER SYSTEM SET sort_area_size=65536 SCOPE=SPFILE ;
SQL>STARTUP FORCE
SQL>ALTER SYSTEM FLUSH BUFFER_CACHE ;
SQL>SET AUTOTRACE TRACEONLY
SQL>SELECT * FROM sh.sales ORDER BY 1,2,3,4 ;
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 918K| 25M| | 7845 (3)| 00:01:35 | | |
| 1 | SORT ORDER BY | | 918K| 25M| 84M| 7845 (3)| 00:01:35 | | |
| 2 | PARTITION RANGE ALL| | 918K| 25M| | 426 (9)| 00:00:06 | 1 | 28 |
| 3 | TABLE ACCESS FULL | SALES | 918K| 25M| | 426 (9)| 00:00:06 | 1 | 28 |
------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
39 recursive calls
3 db block gets
1718 consistent gets
6498 physical reads
0 redo size
36119714 bytes sent via SQL*Net to client
674201 bytes received via SQL*Net from client
61258 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
918843 rows processed
SQL>EXIT
1.17 Hard Parsing vs. Soft Parsing
SQL>CONN system/oracle
SQL>declare
v_cnt number ;
begin
for i in 1..10000 loop
execute immediate 'select /* hard */ count(*) from dual where dummy = to_char('||i||')'
into v_cnt ;
end loop ;
end ;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:17.79
SQL>SELECT sql_text FROM v$sql
WHERE sql_text LIKE '%hard%' ;
SQL_TEXT
----------------------------------------------------------------------------------------------------------------
select /* hard */ count(*) from dual where dummy = to_char(9728)
select /* hard */ count(*) from dual where dummy = to_char(9787)
select /* hard */ count(*) from dual where dummy = to_char(9400)
select /* hard */ count(*) from dual where dummy = to_char(9733)
select /* hard */ count(*) from dual where dummy = to_char(9375)
…
SQL>ALTER SYSTEM FLUSH SHARED_POOL ;
SQL>declare
v_cnt number ;
begin
for i in 1..10000 loop
select count(*) into v_cnt from dual where dummy = to_char(i) ;
end loop ;
end ;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.18
SQL>SELECT sql_text FROM v$sql
WHERE sql_text LIKE '%DUMMY = TO_CHAR%' ;
SQL_TEXT
----------------------------------------------------------------------------------------------------------------
SELECT COUNT(*) FROM DUAL WHERE DUMMY = TO_CHAR(:B1 )
SQL>EXIT
3.16 CURSOR_SHARING parameter
SQL>CONN system/oracle
SQL>ALTER SESSION SET cursor_sharing = exact ;
SQL>declare
v_cnt number ;
begin
for i in 1..10000 loop
execute immediate 'select /* hard */ count(*) from dual where dummy = to_char('||i||')'
into v_cnt ;
end loop ;
end ;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:17.78
SQL>ALTER SESSION SET cursor_sharing = force ;
SQL>declare
v_cnt number ;
begin
for i in 1..10000 loop
execute immediate 'select /* hard */ count(*) from dual where dummy = to_char('||i||')'
into v_cnt ;
end loop ;
end ;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:08.66
SQL> EXIT
4.7 Selectivity / Cardinality & Histogram
SQL>CONN sh/sh
SQL>CREATE TABLE test
(country VARCHAR2(20),
city VARCHAR2(10),
code CHAR(5)) ;
SQL>INSERT INTO test
SELECT 'Korea', DECODE(MOD(rownum,2),0, 'Seoul', 'Busan'), RPAD(rownum,5,' ')
FROM all_objects WHERE rownum <= 10000 ;
SQL>INSERT INTO test SELECT 'Korea', 'Jeju', '11111' FROM dual ;
SQL>INSERT INTO test
SELECT 'Japan', DECODE(MOD(rownum,3),0,'Tokyo', 1, 'Osaka', 'Kyoto'), RPAD(rownum,5,' ')
FROM all_objects WHERE rownum <= 10000 ;
SQL>INSERT INTO test SELECT 'Japan', 'Okinawa', '11111' FROM dual ;
SQL>CREATE INDEX test_idx ON test(country, city) ;
SQL>SELECT table_name, num_rows, blocks, empty_blocks, monitoring , last_analyzed
FROM dba_tables
WHERE owner = 'SH' AND table_name = 'TEST' ;
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS MON LAST_ANAL
------------------------------ ---------- ---------- ------------ --- ---------
TEST YES
SQL>SAVE tab_stat REPLACE
SQL>SELECT column_name, num_distinct, density, num_buckets, histogram
FROM dba_tab_col_statistics
WHERE owner = 'SH' AND table_name = 'TEST' ;
no rows selected
SQL>SAVE col_stat REPLACE
SQL>EXPLAIN PLAN FOR
SELECT * FROM test
WHERE country = 'Korea' AND city = 'Jeju' ;
SQL>SELECT * FROM TABLE(dbms_xplan.display);
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 26 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST_IDX | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
SQL>EXEC DBMS_STATS.GATHER_TABLE_STATS('SH', 'TEST', -
method_opt=>'for all columns size 1', cascade=>true)
SQL>@tab_stat
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS MON LAST_ANAL
------------------------------ ---------- ---------- ------------ --- ---------
TEST 20002 65 0 YES 21-FEB-09
SQL>@col_stat
COLUMN_NAME NUM_DISTINCT DENSITY NUM_BUCKETS HISTOGRAM
------------------------------ ------------ ---------- ----------- ---------------
COUNTRY 2 .5 1 NONE
CITY 7 .142857143 1 NONE
CODE 10001 .00009999 1 NONE
SQL>SELECT 1/2 , 1/7, 1/10001 FROM dual ; -- Density ( Selectivity )
1/2 1/7 1/10001
---------- ---------- ----------
.5 .142857143 .00009999
SQL>EXPLAIN PLAN FOR
SELECT * FROM test
WHERE country = 'Korea' AND city = 'Jeju' ;
SQL>SELECT * FROM TABLE(dbms_xplan.display);
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1429 | 25722 | 17 (6)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 1429 | 25722 | 17 (6)| 00:00:01 |
--------------------------------------------------------------------------
SQL>SELECT ROUND(20002 * 1/2 * 1/7) FROM dual ; -- Cardinality ( Rows )
ROUND(20002*1/2*1/7)
--------------------
1429
SQL>EXPLAIN PLAN FOR
SELECT * FROM test
WHERE country = 'Korea' OR city = 'Jeju' ;
SQL>SELECT * FROM TABLE(dbms_xplan.display) ;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11430 | 200K| 17 (6)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 11430 | 200K| 17 (6)| 00:00:01 |
--------------------------------------------------------------------------
SQL>SELECT ROUND(20002 *( (1/2 + 1/7 ) - ( 1/2 * 1/7 ) ) ) FROM dual ; -- Cardinality
ROUND(20002*((1/2+1/7)-(1/2*1/7)))
----------------------------------
11430
SQL>EXEC DBMS_STATS.GATHER_TABLE_STATS('SH', 'TEST', -
method_opt=>'for all columns size auto', cascade=>true)
SQL>@col_stat
COLUMN_NAME NUM_DISTINCT DENSITY NUM_BUCKETS HISTOGRAM
------------------------------ ------------ ---------- ----------- ---------------
COUNTRY 2 .000024998 2 FREQUENCY
CITY 7 .000024998 7 FREQUENCY
CODE 10001 .00009999 1 NONE
SQL>COLUMN column_name FORMAT A20
SQL>SELECT column_name, endpoint_number, endpoint_value
FROM dba_histograms
WHERE owner = 'SH' AND table_name = 'TEST'
ORDER BY column_name, endpoint_number ;
COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
-------------------- --------------- --------------
CITY 5000 3.4507E+35
CITY 5001 3.8629E+35
CITY 8334 3.9189E+35
CITY 8335 4.1237E+35
CITY 11669 4.1253E+35
CITY 16669 4.3302E+35
CITY 20002 4.3841E+35
CODE 0 2.5507E+35
CODE 1 2.9712E+35
COUNTRY 10001 3.8621E+35
COUNTRY 20002 3.9168E+35
SQL>EXPLAIN PLAN FOR
SELECT * FROM test
WHERE country = 'Korea' AND city = 'Jeju' ;
SQL>SELECT * FROM TABLE(dbms_xplan.display);
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 18 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST_IDX | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
SQL>EXPLAIN PLAN FOR
SELECT * FROM test
WHERE country = :b1 AND city = :b2 ;
SQL>SELECT * FROM TABLE(dbms_xplan.display);
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1429 | 25722 | 17 (6)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 1429 | 25722 | 17 (6)| 00:00:01 |
--------------------------------------------------------------------------
SQL>DROP TABLE test PURGE ;
4.11 Initialization Parameters for Optimizer
SQL>CONN sh/sh
SQL>SHOW PARAMETER optimizer_index_cost_adj
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_index_cost_adj integer 100
SQL>SET AUTOTRACE TRACEONLY EXPLAIN
SQL>SELECT c.cust_last_name, c.cust_main_phone_number, s.promo_id, s.amount_sold
FROM customers c, sales s
WHERE s.cust_id = c.cust_id
AND c.cust_city = 'New Castle'
AND s.amount_sold BETWEEN 3000 AND 3500 ;
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 80 | 4080 | 514 (9)| 00:00:07 | | |
| 1 | NESTED LOOPS | | 80 | 4080 | 514 (9)| 00:00:07 | | |
| 2 | PARTITION RANGE ALL | | 81 | 1134 | 433 (11)| 00:00:06 | 1 | 28 |
|* 3 | TABLE ACCESS FULL | SALES | 81 | 1134 | 433 (11)| 00:00:06 | 1 | 28 |
|* 4 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 1 | 37 | 1 (0)| 00:00:01 | | |
|* 5 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 1 | | 0 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------
SQL>SAVE query REPLACE
SQL>ALTER SESSION SET optimizer_index_cost_adj = 1 ;
SQL>@query
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 80 | 4080 | 102 (56)| 00:00:02 | | |
| 1 | NESTED LOOPS | | 80 | 4080 | 102 (56)| 00:00:02 | | |
| 2 | PARTITION RANGE ALL | | 81 | 1134 | 101 (57)| 00:00:02 | 1 | 28 |
|* 3 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 81 | 1134 | 101 (57)| 00:00:02 | 1 | 28 |
| 4 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
| 5 | BITMAP INDEX FULL SCAN | SALES_PROMO_BIX | | | | | 1 | 28 |
|* 6 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 1 | 37 | 1 (0)| 00:00:01 | | |
|* 7 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 1 | | 1 (0)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------------
SQL>ALTER SESSION SET optimizer_index_cost_adj = 1000 ;
SQL>@query
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 80 | 4080 | 764 (7)| 00:00:10 | | |
|* 1 | HASH JOIN | | 80 | 4080 | 764 (7)| 00:00:10 | | |
| 2 | PARTITION RANGE ALL| | 81 | 1134 | 433 (11)| 00:00:06 | 1 | 28 |
|* 3 | TABLE ACCESS FULL | SALES | 81 | 1134 | 433 (11)| 00:00:06 | 1 | 28 |
|* 4 | TABLE ACCESS FULL | CUSTOMERS | 90 | 3330 | 331 (2)| 00:00:04 | | |
--------------------------------------------------------------------------------------------------
SQL>ALTER SESSION SET optimizer_index_cost_adj = 100 ;
SQL>ALTER SESSION SET optimizer_mode = ALL_ROWS ;
SQL>@query
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 80 | 16720 | 514 (9)| 00:00:07 | | |
| 1 | NESTED LOOPS | | 80 | 16720 | 514 (9)| 00:00:07 | | |
| 2 | PARTITION RANGE ALL | | 81 | 2349 | 433 (11)| 00:00:06 | 1 | 28 |
|* 3 | TABLE ACCESS FULL | SALES | 81 | 2349 | 433 (11)| 00:00:06 | 1 | 28 |
|* 4 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 1 | 180 | 1 (0)| 00:00:01 | | |
|* 5 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 1 | | 0 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------
SQL>ALTER SESSION SET optimizer_mode = FIRST_ROWS_1 ;
SQL>@query
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 102 | 21 (10)| 00:00:01 | | |
| 1 | NESTED LOOPS | | 2 | 102 | 21 (10)| 00:00:01 | | |
| 2 | PARTITION RANGE ALL | | 60 | 840 | 18 (12)| 00:00:01 | 1 | 28 |
|* 3 | TABLE ACCESS FULL | SALES | 60 | 840 | 18 (12)| 00:00:01 | 1 | 28 |
|* 4 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 1 | 37 | 1 (0)| 00:00:01 | | |
|* 5 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 1 | | 0 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------
SQL>ALTER SESSION SET optimizer_mode = FIRST_ROWS_100 ;
SQL>@query
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 80 | 4080 | 514 (9)| 00:00:07 | | |
| 1 | NESTED LOOPS | | 80 | 4080 | 514 (9)| 00:00:07 | | |
| 2 | PARTITION RANGE ALL | | 81 | 1134 | 433 (11)| 00:00:06 | 1 | 28 |
|* 3 | TABLE ACCESS FULL | SALES | 81 | 1134 | 433 (11)| 00:00:06 | 1 | 28 |
|* 4 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 1 | 37 | 1 (0)| 00:00:01 | | |
|* 5 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 1 | | 0 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------
SQL>EXIT
5.4 Access Paths
SQL>CONN sh/sh
SQL>SET AUTOTRACE TRACEONLY EXPLAIN
SQL>SELECT /*+ FULL (sales) */ * FROM sales WHERE cust_id = 53050 ;
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 130 | 3770 | 419 (8)| 00:00:06 | | |
| 1 | PARTITION RANGE ALL| | 130 | 3770 | 419 (8)| 00:00:06 | 1 | 28 |
|* 2 | TABLE ACCESS FULL | SALES | 130 | 3770 | 419 (8)| 00:00:06 | 1 | 28 |
---------------------------------------------------------------------------------------------
SQL>SELECT /*+ ROWID */ *
FROM sales WHERE cust_id = 53050 AND rowid = 'AAAG27AAGAAAAEMAB7' ;
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 29 | 1 (0)| 00:00:01 | | |
|* 1 | TABLE ACCESS BY USER ROWID| SALES | 1 | 29 | 1 (0)| 00:00:01 | ROWID | ROWID |
----------------------------------------------------------------------------------------------------
SQL> SELECT /*+ INDEX_ASC (c customers_pk) */ *
FROM customers c WHERE cust_id > 60000 ;
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23634 | 4154K| 23244 (1)| 00:04:39 |
| 1 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 23634 | 4154K| 23244 (1)| 00:04:39 |
|* 2 | INDEX RANGE SCAN | CUSTOMERS_PK | 23634 | | 51 (2)| 00:00:01 |
--------------------------------------------------------------------------------------------
SQL>SELECT * FROM sales SAMPLE(10) ;
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 91884 | 2602K| 410 (6)| 00:00:05 | | |
| 1 | PARTITION RANGE ALL | | 91884 | 2602K| 410 (6)| 00:00:05 | 1 | 28 |
| 2 | TABLE ACCESS SAMPLE| SALES | 91884 | 2602K| 410 (6)| 00:00:05 | 1 | 28 |
----------------------------------------------------------------------------------------------
5.9 Index Scans
SQL> SELECT /*+ INDEX(c customers_pk) */ *
FROM customers c WHERE cust_id = 60000 ;
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 180 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 1 | 180 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
SQL>SELECT /*+ INDEX_DESC (c customers_pk) */ *
FROM customers c WHERE cust_id > 60000 ;
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23634 | 4154K| 23244 (1)| 00:04:39 |
| 1 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 23634 | 4154K| 23244 (1)| 00:04:39 |
|* 2 | INDEX RANGE SCAN DESCENDING| CUSTOMERS_PK | 23634 | | 51 (2)| 00:00:01 |
---------------------------------------------------------------------------------------------
SQL>CREATE INDEX cust_gend_birth_idx ON customers(cust_gender, cust_year_of_birth)
COMPUTE STATISTICS ;
SQL>SELECT /*+ INDEX_SS (c cust_gend_birth_idx) */ *
FROM customers c WHERE cust_year_of_birth BETWEEN 1930 AND 1935 ;
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5084 | 893K| 3337 (1)| 00:00:41 |
| 1 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 5084 | 893K| 3337 (1)| 00:00:41 |
|* 2 | INDEX SKIP SCAN | CUST_GEND_BIRTH_IDX | 4029 | | 12 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
SQL>SELECT /*+ INDEX(c customers_pk) */ count(*) FROM customers c ;
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 118 (2)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| CUSTOMERS_PK | 55500 | 118 (2)| 00:00:02 |
-------------------------------------------------------------------------
SQL>SELECT /*+ INDEX_FFS(c customers_pk) */ count(*) FROM customers c ;
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 (4)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| CUSTOMERS_PK | 55500 | 28 (4)| 00:00:01 |
------------------------------------------------------------------------------
SQL>SELECT /*+ INDEX_JOIN (p products_pk products_prod_cat_ix) */ prod_id, prod_category
FROM products p WHERE prod_id > 20 ;
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 68 | 1428 | 3 (34)| 00:00:01 |
|* 1 | VIEW | index$_join$_001 | 68 | 1428 | 3 (34)| 00:00:01 |
|* 2 | HASH JOIN | | | | | |
|* 3 | INDEX RANGE SCAN | PRODUCTS_PK | 68 | 1428 | 2 (50)| 00:00:01 |
| 4 | INDEX FAST FULL SCAN| PRODUCTS_PROD_CAT_IX | 68 | 1428 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
SQL>ALTER TABLE customers DROP PRIMARY KEY CASCADE ;
SQL>ALTER TABLE customers ADD CONSTRAINT customers_pk PRIMARY KEY(cust_id) ;
SQL>ALTER TABLE sales ADD FOREIGN KEY(cust_id) REFERENCES customers(cust_id) ;
SQL>CREATE BITMAP INDEX sales_cust_gender_bjix ON sales(c.cust_gender)
FROM sales s, customers c
WHERE s.cust_id = c.cust_id
LOCAL COMPUTE STATISTICS ;
SQL>SELECT /*+ index_combine (s sales_cust_gender_bjix) */ s.time_id, s.amount_sold
FROM sales s, customers c
WHERE s.cust_id = c.cust_id AND c.cust_gender = 'F' ;
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 459K| 8075K| 2420 (2)| 00:00:30 | | |
| 1 | PARTITION RANGE ALL | | 459K| 8075K| 2420 (2)| 00:00:30 | 1 | 28 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 459K| 8075K| 2420 (2)| 00:00:30 | 1 | 28 |
| 3 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
|* 4 | BITMAP INDEX SINGLE VALUE | SALES_CUST_GENDER_BJIX | | | | | 1 | 28 |
-----------------------------------------------------------------------------------------------------------------------------
SQL>SELECT s.time_id, s.amount_sold
FROM sales s, customers c
WHERE s.cust_id = c.cust_id AND c.cust_gender = 'F' ;
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 918K| 21M| 591 (10)| 00:00:08 | | |
|* 1 | HASH JOIN | | 918K| 21M| 591 (10)| 00:00:08 | | |
|* 2 | VIEW | index$_join$_002 | 27750 | 189K| 151 (3)| 00:00:02 | | |
|* 3 | HASH JOIN | | | | | | | |
| 4 | BITMAP CONVERSION TO ROWIDS| | 27750 | 189K| 2 (0)| 00:00:01 | | |
|* 5 | BITMAP INDEX SINGLE VALUE | CUSTOMERS_GENDER_BIX | | | | | | |
| 6 | INDEX FAST FULL SCAN | CUSTOMERS_PK | 27750 | 189K| 147 (2)| 00:00:02 | | |
| 7 | PARTITION RANGE ALL | | 918K| 15M| 426 (9)| 00:00:06 | 1 | 28 |
| 8 | TABLE ACCESS FULL | SALES | 918K| 15M| 426 (9)| 00:00:06 | 1 | 28 |
-----------------------------------------------------------------------------------------------------------------------
5.22 Join Methods
SQL>CONN hr/hr
SQL>SET AUTOTRACE TRACEONLY EXPLAIN
SQL>CREATE INDEX emp_dept_idx ON employees(department_id) ;
SQL>SELECT /*+ USE_NL( e d ) */ *
FROM departments d , employees e
WHERE e.department_id = d.department_id ;
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106 | 9328 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 106 | 9328 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | EMPLOYEES | 107 | 7276 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 20 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
SQL>SELECT /*+ ORDERED USE_NL( e d ) */ *
FROM departments d , employees e
WHERE e.department_id = d.department_id ;
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106 | 9328 | 10 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 4 | 272 | 1 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 106 | 9328 | 10 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | DEPARTMENTS | 27 | 540 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 10 | | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
SQL>SELECT /*+ USE_MERGE( e d ) */ *
FROM departments d , employees e
WHERE e.department_id = d.department_id ;
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106 | 9328 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 106 | 9328 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 540 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 107 | 7276 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMPLOYEES | 107 | 7276 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
SQL>SELECT /*+ USE_HASH( e d ) */ *
FROM departments d , employees e
WHERE e.department_id = d.department_id ;
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106 | 9328 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 106 | 9328 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPARTMENTS | 27 | 540 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMPLOYEES | 107 | 7276 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------
5.35 Subquery
SQL>CONN system/oracle
SQL>SET AUTOTRACE TRACEONLY EXPLAIN
SQL>SELECT c.* FROM sh.customers c
WHERE c.country_id IN ( SELECT country_id FROM sh.countries
WHERE country_subregion = 'Asia' ) ;
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8398 | 1623K| 337 (3)| 00:00:05 |
|* 1 | HASH JOIN | | 8398 | 1623K| 337 (3)| 00:00:05 |
|* 2 | TABLE ACCESS FULL| COUNTRIES | 3 | 54 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| CUSTOMERS | 55500 | 9755K| 333 (2)| 00:00:04 |
--------------------------------------------------------------------------------
SQL>SELECT p.* FROM sh.promotions p
WHERE p.promo_cost = ( SELECT MAX(promo_cost) FROM sh.promotions
WHERE promo_category = p.promo_category ) ;
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 1143 | 30 (7)| 00:00:01 |
|* 1 | HASH JOIN | | 9 | 1143 | 30 (7)| 00:00:01 |
| 2 | VIEW | VW_SQ_1 | 9 | 270 | 15 (7)| 00:00:01 |
| 3 | HASH GROUP BY | | 9 | 99 | 15 (7)| 00:00:01 |
| 4 | TABLE ACCESS FULL| PROMOTIONS | 503 | 5533 | 14 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | PROMOTIONS | 503 | 48791 | 14 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
SQL>SELECT c.* FROM sh.customers c
WHERE c.cust_income_level = 'F: 110,000 - 129,999'
AND c.country_id NOT IN ( SELECT country_id FROM sh.countries
WHERE country_subregion = 'Europe' ) ;
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3892 | 752K| 336 (2)| 00:00:05 |
|* 1 | HASH JOIN RIGHT ANTI| | 3892 | 752K| 336 (2)| 00:00:05 |
|* 2 | TABLE ACCESS FULL | COUNTRIES | 3 | 54 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | CUSTOMERS | 4622 | 812K| 332 (2)| 00:00:04 |
----------------------------------------------------------------------------------
SQL>SELECT e.* FROM hr.employees e
WHERE e.department_id = 80 AND e.job_id = 'SA_REP'
AND EXISTS ( SELECT 1 FROM oe.orders o
WHERE o.sales_rep_id = e.employee_id ) ;
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 355 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS SEMI | | 5 | 355 | 3 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| EMPLOYEES | 10 | 680 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | ORD_SALES_REP_IX | 35 | 105 | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
6.4 Execution Plan
SQL>CONN system/oracle
SQL>ALTER SYSTEM FLUSH SHARED_POOL ;
SQL>COLUMN object_name FORMAT A30
SQL>SELECT owner, object_name, object_type FROM dba_objects
WHERE object_name = 'PLAN_TABLE' ;
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------ -------------------
PUBLIC PLAN_TABLE SYNONYM
SQL>DESC PLAN_TABLE
SQL> EXPLAIN PLAN SET STATEMENT_ID = 'demo1' FOR
SELECT * FROM hr.employees WHERE department_id = 10 ;
SQL>SELECT * FROM TABLE(dbms_xplan.display) ;
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 68 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 68 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
SQL>SELECT * FROM TABLE(dbms_xplan.display('plan_table','demo1','serial')) ;
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 68 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 68 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
SQL>SELECT * FROM TABLE(dbms_xplan.display('plan_table','demo1','basic')) ;
---------------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES |
| 2 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX |
---------------------------------------------------------
SQL>SELECT * FROM hr.employees WHERE department_id = 10 ;
SQL>COLUMN sql_text FORMAT A60
SQL>SELECT sql_id, sql_text FROM v$sql
WHERE sql_text LIKE '% hr.employees%' ;
SQL_ID SQL_TEXT
------------- ------------------------------------------------------------
7wfgkxh8fr7ab SELECT * FROM hr.employees WHERE department_id = 10
SQL>SELECT * FROM TABLE(dbms_xplan.display_cursor('7wfgkxh8fr7ab')) ;
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 68 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
SQL>SELECT * FROM v$sql_plan_statistics ;
no rows selected
SQL>SHOW PARAMETER statistics_level
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
statistics_level string TYPICAL
SQL>ALTER SYSTEM SET statistics_level = ALL ;
SQL>SELECT * FROM v$sql_plan_statistics ; -- SQL Developer
SQL> SELECT * FROM v$sql_plan_statistics_all ; -- SQL Developer
SQL>SET AUTOTRACE ON
SQL>SELECT COUNT(*) FROM hr.employees ;
SQL>SET AUTOTRACE ON EXPLAIN
SQL>SELECT COUNT(*) FROM hr.employees ;
SQL>SET AUTOTRACE ON STATISTICS
SQL>SELECT COUNT(*) FROM hr.employees ;
SQL>SET AUTOTRACE TRACEONLY
SQL>SELECT COUNT(*) FROM hr.employees ;
SQL>SET AUTOTRACE TRACEONLY EXPLAIN
SQL>SELECT COUNT(*) FROM hr.employees ;
SQL>SET AUTOTRACE OFF
SQL>SELECT COUNT(*) FROM hr.employees ;
SQL>EXIT
7.15 DML Monitoring
SQL>CONN system/oracle
SQL>SHOW PARAMETER statistics_level
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
statistics_level string ALL
SQL>CREATE TABLE test
AS SELECT * FROM all_objects WHERE 1 = 0 ;
SQL> SELECT table_name, monitoring
FROM user_tables WHERE table_name = 'TEST' ;
TABLE_NAME MON
------------------------------ ---
TEST YES
SQL>INSERT INTO test SELECT * FROM all_objects WHERE rownum <= 100 ;
SQL>DELETE test WHERE rownum <= 20 ;
SQL> SELECT table_name, inserts, updates, deletes, truncated
FROM user_tab_modifications WHERE table_name = 'TEST' ;
no rows selected
SQL>EXEC dbms_stats.flush_database_monitoring_info ;
SQL> SELECT table_name, inserts, updates, deletes, truncated
FROM user_tab_modifications WHERE table_name = 'TEST' ;
TABLE_NAME INSERTS UPDATES DELETES TRU
------------------------------ ---------- ---------- ---------- ---
TEST 100 0 20 NO
7.26 Bind Variable Peeking
SQL>CONN system/oracle
SQL>CREATE TABLE student
AS SELECT object_id id, object_name name,
decode(object_type,'EVALUATION CONTEXT', 'A',
'CONSUMER GROUP','C',
'SEQUENCE','D',
'INDEX','F','B') grade
FROM all_objects ;
SQL> SELECT grade, count(*) FROM student
GROUP BY grade ORDER BY 1;
G COUNT(*)
- ----------
A 14
B 47839
C 3
D 141
F 1781
SQL>ALTER TABLE student ADD CONSTRAINT std_pk PRIMARY KEY(id) ;
SQL>CREATE INDEX std_grade_idx ON student(grade) ;
SQL>EXEC dbms_stats.gather_table_stats(user,'STUDENT', -
method_opt=>'for all indexed columns size auto', CASCADE=>true)
SQL>ALTER SYSTEM FLUSH SHARED_POOL ;
SQL>SET AUTOTRACE ON EXPLAIN
SQL>SELECT count(*), min(id) FROM student WHERE grade = 'A' ;
COUNT(*) MIN(ID)
---------- ----------
14 5077
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| STUDENT | 1 | 7 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | STD_GRADE_IDX | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
SQL>SELECT count(*), min(id) FROM student WHERE grade = 'B' ;
COUNT(*) MIN(ID)
---------- ----------
47839 2
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 58 (4)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | TABLE ACCESS FULL| STUDENT | 48610 | 332K| 58 (4)| 00:00:01 |
------------------------------------------------------------------------------
SQL>SET AUTOTRACE OFF
SQL>EXPLAIN PLAN FOR
SELECT count(*), min(id)
FROM student
WHERE grade = :x ;
SQL>SELECT * FROM TABLE(dbms_xplan.display) ;
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 58 (4)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | TABLE ACCESS FULL| STUDENT | 16846 | 115K| 58 (4)| 00:00:01 |
------------------------------------------------------------------------------
SQL>CONN /as sysdba
SQL>COLUMN "Parameter" FORMAT a40
SQL>COLUMN "Session Value" FORMAT a15
SQL>COLUMN "Instance Value" FORMAT a15
SQL>select a.ksppinm "Parameter",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value"
from x$ksppi a, x$ksppcv b, x$ksppsv c
where a.indx = b.indx and a.indx = c.indx
and a.ksppinm = '_optim_peek_user_binds' ;
Parameter Session Value Instance Value
---------------------------------------- --------------- ---------------
_optim_peek_user_binds TRUE TRUE
SQL>CONN system/oracle
SQL>ALTER SYSTEM FLUSH SHARED_POOL ;
SQL>ALTER SESSION SET SQL_TRACE = TRUE ;
SQL>variable x varchar2(1)
SQL>DECLARE
v_cnt NUMBER ;
v_min NUMBER ;
BEGIN
:x := 'A' ;
SELECT count(*), min(id) INTO v_cnt, v_min
FROM student
WHERE grade = :x ;
:x := 'B' ;
SELECT count(*), min(id) INTO v_cnt, v_min
FROM student
WHERE grade = :x ;
END;
/
SQL>ALTER SESSION SET SQL_TRACE = FALSE ;
SQL>SELECT d.value||'/'||lower(c.instance_name)||'_ora_'||b.spid||'.trc' "User Trace File"
FROM v$session a, v$process b, v$instance c, v$parameter d,
( select * from v$mystat where rownum = 1 ) e
WHERE a.paddr = b.addr AND a.sid = e.sid AND d.name = 'user_dump_dest' ;
User Trace File
----------------------------------------------------------------------------------------------------------------
/u01/app/oracle/admin/orcl/udump/orcl_ora_14011.trc
SQL>SAVE ck_trace REPLACE
SQL>host tkprof /u01/app/oracle/admin/orcl/udump/orcl_ora_14011.trc $HOME/report1.txt sys=no
SQL>host vi $HOME/report1.txt
SELECT COUNT(*), MIN(ID)
FROM
STUDENT WHERE GRADE = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.01 0.00 0 0 0 0
Fetch 2 1.22 1.25 0 346 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 1.23 1.26 0 346 0 2
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5 (SYSTEM) (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=336 pr=0 pw=0 time=1251136 us)
47839 TABLE ACCESS BY INDEX ROWID STUDENT (cr=336 pr=0 pw=0 time=1052443 us)
47839 INDEX RANGE SCAN STD_GRADE_IDX (cr=88 pr=0 pw=0 time=430543 us)(object id 52562)
SQL>CONN system/oracle
SQL>ALTER SYSTEM FLUSH SHARED_POOL ;
SQL>ALTER SESSION SET SQL_TRACE = TRUE ;
SQL>variable x varchar2(1)
SQL>DECLARE
v_cnt NUMBER ;
v_min NUMBER ;
BEGIN
:x := 'B' ;
SELECT count(*), min(id) INTO v_cnt, v_min
FROM student WHERE grade = :x ;
:x := 'A' ;
SELECT count(*), min(id) INTO v_cnt, v_min
FROM student WHERE grade = :x ;
END;
/
SQL>ALTER SESSION SET SQL_TRACE = FALSE ;
SQL>@ck_trace
User Trace File
---------------------------------------------------------------------------------------------------------------
/u01/app/oracle/admin/orcl/udump/orcl_ora_14119.trc
SQL>host tkprof /u01/app/oracle/admin/orcl/udump/orcl_ora_14119.trc $HOME/report2.txt sys=no
SQL>host vi $HOME/report2.txt
SELECT COUNT(*), MIN(ID)
FROM
STUDENT WHERE GRADE = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.01 0.02 0 0 0 0
Fetch 2 0.56 0.55 0 504 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.58 0.57 0 504 0 2
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=252 pr=0 pw=0 time=3885 us)
14 TABLE ACCESS FULL STUDENT (cr=252 pr=0 pw=0 time=3806 us)
SQL>ALTER SESSION SET "_optim_peek_user_binds"= false ;
SQL>ALTER SESSION SET "_optim_peek_user_binds"= true ;
SQL>DROP TABLE student PURGE ;
SQL>EXIT
8.6 DBMS_MONITOR
# Main Session
SQL>CONN system/oracle
SQL>SHOW PARAMETER user_dump_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest string /u01/app/oracle/admin/orcl/udump
SQL>host rm /u01/app/oracle/admin/orcl/udump/*
SQL>host ls /u01/app/oracle/admin/orcl/udump/
# New Session 1
SQL>CONN hr/hr
# New Session 2
SQL>CONN hr/hr
SQL>EXEC dbms_session.set_identifier('ORCL_HR')
# New Session 3
SQL>CONN hr/hr
SQL>EXEC dbms_session.set_identifier('ORCL_HR')
# Main Session
SQL>SELECT s.username, s.sid, s.serial#, s.client_identifier, p.spid
FROM v$session s, v$process p
WHERE s.paddr = p.addr AND s.username = 'HR' ;
USERNAME SID SERIAL# CLIENT_IDENTIFIER SPID
------------------------------ ---------- ---------- ------------------ -------------------------------------
HR 136 43 5606
HR 144 275 ORCL_HR 5639
HR 138 90 ORCL_HR 5674
SQL>EXEC dbms_monitor.session_trace_enable(session_id=>136, serial_num=>43, waits=>true )
# Session 1
SQL>SELECT * FROM employees WHERE department_id = 20 ;
SQL>SELECT * FROM departments ;
# Main Session
SQL> EXEC dbms_monitor.session_trace_disable(136, 43)
SQL>host ls /u01/app/oracle/admin/orcl/udump/
orcl_ora_5606.trc
SQL>EXEC dbms_monitor.client_id_stat_enable(client_id=>'ORCL_HR')
SQL>EXEC dbms_monitor.client_id_trace_enable('ORCL_HR',TRUE,TRUE)
# Session 2
SQL>SELECT * FROM employees WHERE department_id = 10 ;
# Session 3
SQL>SELECT * FROM departments WHERE department_id = 10 ;
# Main Session
SQL>COLUMN client_identifier FORMAT A10
SQL>SELECT * FROM v$client_stats ;
CLIENT_IDE STAT_ID STAT_NAME VALUE
---------- ---------- ---------------------------------------------------------------- ----------
ORCL_HR 2882015696 user calls 14
ORCL_HR 3649082374 DB time 204094
ORCL_HR 2748282437 DB CPU 171870
ORCL_HR 63887964 parse count (total) 21
ORCL_HR 1431595225 parse time elapsed 193504
ORCL_HR 2453370665 execute count 148
ORCL_HR 2821698184 sql execute elapsed time 59581
ORCL_HR 85052502 opened cursors cumulative 39
ORCL_HR 3143187968 session logical reads 173
…
SQL>EXEC dbms_monitor.client_id_stat_disable(client_id=>'ORCL_HR')
SQL> SELECT username, sid, serial#, client_identifier, sql_trace
FROM v$session WHERE username = 'HR' ;
USERNAME SID SERIAL# CLIENT_IDENTIFIER SQL_TRACE
------------------------------ ---------- ---------- ------------------ -------------------------------------
HR 136 43 DISABLED
HR 144 275 ORCL_HR DISABLED
HR 138 90 ORCL_HR DISABLED
SQL> SELECT trace_type, primary_id, waits, binds
FROM dba_enabled_traces ;
TRACE_TYPE PRIMARY_ID WAITS BINDS
--------------------- ---------------------------------------------------------------- ----- -----
CLIENT_ID ORCL_HR TRUE TRUE
SQL>EXEC dbms_monitor.client_id_trace_disable('ORCL_HR')
SQL>host ls /u01/app/oracle/admin/orcl/udump/
orcl_ora_5606.trc orcl_ora_5639.trc orcl_ora_5674.trc
SQL>host vi /u01/app/oracle/admin/orcl/udump/orcl_ora_5606.trc
SQL>host vi /u01/app/oracle/admin/orcl/udump/orcl_ora_5639.trc
SQL>host vi /u01/app/oracle/admin/orcl/udump/orcl_ora_5674.trc
SQL>EXIT -- All Sessions
8.9 TRCSESS & TKPROF
$ cd /u01/app/oracle/admin/orcl/udump/
$ trcsess clientid=ORCL_HR orcl_ora_5639.trc orcl_ora_5674.trc output=output.trc
$ vi output.trc
$ tkprof output.trc report.txt sys=no
$ vi report.txt
SELECT *
FROM
employees WHERE department_id = 10
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.04 0.04 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.04 0.04 0 3 0 1
…
SELECT *
FROM
departments WHERE department_id = 10
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.03 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.01 0.02 1 2 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.04 0.06 1 2 0 1
…
$ sqlplus sh/sh
SQL>ALTER SESSION SET tracefile_identifier = 'my_file' ;
SQL>ALTER SESSION SET EVENTS '10046 trace name context forever, level 8' ;
SQL> SELECT count(*)
FROM customers c, sales s
WHERE s.cust_id = c.cust_id
AND c.cust_city = 'New Castle'
AND s.amount_sold BETWEEN 3000 AND 3500 ;
SQL>ALTER SESSION SET EVENTS '10046 trace name context off' ;
SQL>host ls /u01/app/oracle/admin/orcl/udump/
orcl_ora_5606.trc orcl_ora_5639.trc orcl_ora_5674.trc orcl_ora_8986_my_file.trc output.trc report.txt
SQL>host tkprof orcl_ora_8986_my_file.trc report_my_file.txt sys=no
SQL>host vi report_my_file.txt
SQL>EXIT
9.6 Automatic Database Diagnostic Monitor
SQL>conn /as sysdba
SQL>@$HOME/scripts/print_table.sql
SQL>EXEC print_table('select * from dba_hist_wr_control')
-------------------------------------------------------
DBID : 1204738453
SNAP_INTERVAL : +00000 01:00:00.0
RETENTION : +00007 00:00:00.0
TOPNSQL : DEFAULT
-------------------------------------------------------
SQL> select snap_id,
to_char(startup_time, 'YYYY/MM/DD HH24:MI:SS') start_time,
to_char(begin_interval_time,'YYYY/MM/DD HH24:MI:SS') begin_time
from dba_hist_snapshot
order by snap_id ;
SNAP_ID START_TIME BEGIN_TIME
---------- ------------------- -------------------
14 2009/02/22 05:28:12 2009/02/22 05:28:12
15 2009/02/22 05:28:12 2009/02/22 05:39:27
16 2009/02/22 05:28:12 2009/02/22 07:00:32
SQL>SAVE dba_hist_snapshot REPLACE
SQL>EXEC dbms_workload_repository.create_snapshot
SQL>@dba_hist_snapshot
SNAP_ID START_TIME BEGIN_TIME
---------- ------------------- -------------------
14 2009/02/22 05:28:12 2009/02/22 05:28:12
15 2009/02/22 05:28:12 2009/02/22 05:39:27
16 2009/02/22 05:28:12 2009/02/22 07:00:32
17 2009/02/22 05:28:12 2009/02/22 08:00:37
SQL>@?/rdbms/admin/awrrpt.sql
SQL>host vi awrrpt.txt
SQL>@?/rdbms/admin/addmrpt.sql
SQL>host vi addmrpt.txt
FINDING 4: 4.3% impact (95 seconds)
-----------------------------------
SQL statements consuming significant database time were found.
RECOMMENDATION 1: SQL Tuning, 4.4% benefit (96 seconds)
ACTION: Investigate the SQL statement with SQL_ID "dfbju7gah4k6m" for
possible performance improvements.
RELEVANT OBJECT: SQL statement with SQL_ID dfbju7gah4k6m
call DY_SQL ( )
RATIONALE: SQL statement with SQL_ID "dfbju7gah4k6m" was executed 5
times and had an average elapsed time of 19 seconds.
SQL>EXIT
10.7 Automatic Tuning Advisor
SQL>CONN hr/hr
SQL>@$HOME/scripts/demobld.sql
SQL>EXEC DBMS_STATS.GATHER_SCHEMA_STATS('HR')
SQL>SET AUTOTRACE ON EXPLAIN
SQL>SELECT ename, sal * 12 FROM emp
WHERE sal * 12 = 15000 ;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 10 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
SQL>CONN /as sysdba
SQL>DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := 'SELECT ename, sal * 12 FROM emp WHERE sal*12 = 15000';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => my_sqltext,
user_name => 'HR',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'sql_tuning_task_1' );
END;
/
SQL>EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK (task_name =>'sql_tuning_task_1')
SQL>SET LONG 100000
SQL>SET LONGCHUNKSIZE 1000
SQL>SELECT dbms_sqltune.report_tuning_task('sql_tuning_task_1') FROM dual;
Schema Name: HR
SQL ID : bvqnwdcnkcs9n
SQL Text : SELECT ename, sal * 12 FROM emp WHERE sal*12 = 15000
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- Index Finding (see explain plans section below)
-------------------------------------------------------------------------------
The execution plan of this statement can be improved by creating one or more
indices.
Recommendation (estimated benefit: 100%)
----------------------------------------
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index HR.IDX$$_007B0001 on HR.EMP('SAL'*12);
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 3956160932
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_TUNING_TASK_1')
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 10 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SAL"*12=15000)
2- Using New Indices
--------------------
Plan hash value: 995899183
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 10 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX$$_007B0001 | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
-------------------------------------------------------------------------------
2 - access("EMP".???)
-------------------------------------------------------------------------------
10.10 SQL Profiling
SQL>CONN hr/hr
SQL>CREATE TABLE test (n number ) ;
SQL>begin
for i in 1 .. 10000 loop
insert into test values(i);
end loop;
commit ;
end;
/
SQL>CREATE INDEX test_idx ON test(n);
SQL>exec DBMS_STATS.GATHER_TABLE_STATS('HR','TEST',CASCADE=>TRUE)
SQL>SET AUTOTRACE ON EXPLAIN
SQL>select /*+ no_index(test test_idx) */ * from test where n=1 ;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 3 | 6 (0)| 00:00:01 |
--------------------------------------------------------------------------
SQL>CONN /as sysdba
SQL>DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := 'select /*+ no_index(test test_idx) */ * from test where n= 1';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => my_sqltext,
user_name => 'HR',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'sql_tuning_task_2') ;
END;
/
SQL>EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name =>'sql_tuning_task_2')
SQL>SET LONG 100000
SQL>SET LONGCHUNKSIZE 1000
SQL>SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'sql_tuning_task_2') from dual ;
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 84.11%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'sql_tuning_task_2',
replace => TRUE);
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
2- Using SQL Profile
--------------------
Plan hash value: 2882402178
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| TEST_IDX | 1 | 3 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------
SQL> DECLARE my_sqlprofile_name VARCHAR2(30);
BEGIN
my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
task_name => 'sql_tuning_task_2',
name => 'my_sql_profile');
END;
/
SQL>SELECT to_char(sql_text) FROM dba_sql_profiles;
TO_CHAR(SQL_TEXT)
------------------------------------------------------------------------------------------------------------
select /*+ no_index(test test_idx) */ * from test where n= 1
SQL>CONN hr/hr
SQL>SET AUTOTRACE ON EXPLAIN
SQL>select /*+ no_index(test test_idx) */ * from test where n=1;
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| TEST_IDX | 1 | 3 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------
SQL>DROP TABLE test PURGE ;
SQL>CONN /as sysdba
SQL>EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'my_sql_profile')
SQL>EXEC DBMS_ADVISOR.DELETE_TASK ('sql_tuning_task_1')
SQL>EXEC DBMS_ADVISOR.DELETE_TASK ('sql_tuning_task_2')
SQL>EXIT
'oracle10R2' 카테고리의 다른 글
| Oreilly - Oracle PL SQL Programming 11gR2 (5th) (0) | 2011.06.09 |
|---|---|
| SQL Tuning 10g 정리 (0) | 2011.05.24 |
| isqlplus setting(10g) (0) | 2011.05.23 |
| 11g에서 EM접속 해보기 (0) | 2011.05.23 |
| oracle10g parameter (0) | 2011.02.13 |
