Here is an example when reading "COST" alone can be wrong.
SQL>
SQL> REM ===========================================================================
SQL> REM Discover the table TEST_TABLE
SQL> REM This is a copy of DBA_OBJECTS, inserted twice
SQL>
SQL> desc test_table;
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
SQL>
SQL> column index_name format a30
SQL> column column_name format a30
SQL> column column_position format 9999 hea 'Posn'
SQL>
SQL> REM Identify Indexes present
SQL> select index_name, column_name, column_position
2 from user_ind_columns
3 where table_name = 'TEST_TABLE'
4 order by index_name, column_position
5 /
INDEX_NAME COLUMN_NAME Posn
------------------------------ ------------------------------ -----
TEST_TABLE_NDX_1 CREATED 1
Elapsed: 00:00:00.39
SQL>
SQL> exec dbms_stats.gather_table_stats('HEMANT','TEST_TABLE',cascade=>TRUE);
Elapsed: 00:00:42.51
SQL> select count(*) from test_table;
COUNT(*)
----------
103114
Elapsed: 00:00:00.65
SQL> select blocks, num_rows from user_tables where table_name = 'TEST_TABLE';
BLOCKS NUM_ROWS
---------- ----------
1460 103114
Elapsed: 00:00:00.14
SQL> select leaf_blocks, num_rows from user_indexes where index_name = 'TEST_TABLE_NDX_1';
LEAF_BLOCKS NUM_ROWS
----------- ----------
273 103114
Elapsed: 00:00:00.17
SQL>
SQL> REM ===========================================================================
SQL> REM Get the query execution plan for MIN queries
SQL> set autotrace on
SQL>
SQL> select min(created) from test_table
2 /
MIN(CREAT
---------
30-AUG-05
Elapsed: 00:00:00.12
Execution Plan
----------------------------------------------------------
Plan hash value: 108964483
--------------------------------------------------------------------------------
---------------
Id Operation Name Rows Bytes Cost (%C
PU) Time
--------------------------------------------------------------------------------
---------------
0 SELECT STATEMENT 1 8 328
(3) 00:00:04
1 SORT AGGREGATE 1 8
2 INDEX FULL SCAN (MIN/MAX) TEST_TABLE_NDX_1 103K 805K
--------------------------------------------------------------------------------
---------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
420 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> REM --> that was only 2 Block Gets
SQL>
SQL> REM ===========================================================================
SQL> REM Get the query execution plan for MAX queries
SQL> set autotrace on
SQL>
SQL> select max(created) from test_table
2 /
MAX(CREAT
---------
12-AUG-07
Elapsed: 00:00:00.17
Execution Plan
----------------------------------------------------------
Plan hash value: 108964483
--------------------------------------------------------------------------------
---------------
Id Operation Name Rows Bytes Cost (%C
PU) Time
--------------------------------------------------------------------------------
---------------
0 SELECT STATEMENT 1 8 328
(3) 00:00:04
1 SORT AGGREGATE 1 8
2 INDEX FULL SCAN (MIN/MAX) TEST_TABLE_NDX_1 103K 805K
--------------------------------------------------------------------------------
---------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
420 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> REM --> Again, only 2 Block Gets
SQL> REM ===========================================================================
SQL> REM Get Min and Max together
SQL> set autotrace on
SQL>
SQL> select min(created), max(created) from test_table
2 /
MIN(CREAT MAX(CREAT
--------- ---------
30-AUG-05 12-AUG-07
Elapsed: 00:00:02.28
Execution Plan
----------------------------------------------------------
Plan hash value: 711311523
--------------------------------------------------------------------------------
-
Id Operation Name Rows Bytes Cost (%CPU) Time
--------------------------------------------------------------------------------
-
0 SELECT STATEMENT 1 8 328 (3) 00:00:04
1 SORT AGGREGATE 1 8
2 TABLE ACCESS FULL TEST_TABLE 103K 805K 328 (3) 00:00:04
--------------------------------------------------------------------------------
-
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1445 consistent gets
1440 physical reads
0 redo size
490 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> REM --> That took 1,445 Block Gets -- at the same "COST" of 328 !
SQL>
SQL>
SQL> REM ===========================================================================
SQL> REM Try using a UNION
SQL> set autotrace on
SQL>
SQL> select min(created) Min_date, NULL Max_date from test_table
2 union
3 select NULL Min_date, max(created) Max_date from test_table
4 /
MIN_DATE MAX_DATE
--------- ---------
30-AUG-05
12-AUG-07
Elapsed: 00:00:00.25
Execution Plan
----------------------------------------------------------
Plan hash value: 1754591915
--------------------------------------------------------------------------------
-------------------------
Id Operation Name Rows Bytes TempSpc
Cost (%CPU) Time
--------------------------------------------------------------------------------
-------------------------
0 SELECT STATEMENT 2 16
1426 (52) 00:00:18
1 SORT UNIQUE 2 16 6513K
1426 (52) 00:00:18
2 UNION-ALL
3 SORT AGGREGATE 1 8
713 (4) 00:00:09
4 INDEX FULL SCAN (MIN/MAX) TEST_TABLE_NDX_1 103K 805K
5 SORT AGGREGATE 1 8
713 (4) 00:00:09
6 INDEX FULL SCAN (MIN/MAX) TEST_TABLE_NDX_1 103K 805K
--------------------------------------------------------------------------------
-------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
520 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2 rows processed
SQL>
SQL> REM --> Only 4 Block Gets, although the "COST" is 1,426
SQL>
SQL> REM ===========================================================================
SQL> REM Try using Inline Views to get the two values together in single row
SQL> set autotrace on
SQL>
SQL> select a.Min_Date, b.Max_date from
2 (select min(created) Min_date from test_table) a,
3 (select max(created) Max_date from test_table) b
4 /
MIN_DATE MAX_DATE
--------- ---------
30-AUG-05 12-AUG-07
Elapsed: 00:00:00.06
Execution Plan
----------------------------------------------------------
Plan hash value: 1527082337
--------------------------------------------------------------------------------
-----------------
Id Operation Name Rows Bytes Cost (
%CPU) Time
--------------------------------------------------------------------------------
-----------------
0 SELECT STATEMENT 1 18 655
(2) 00:00:08
1 NESTED LOOPS 1 18 655
(2) 00:00:08
2 VIEW 1 9 328
(3) 00:00:04
3 SORT AGGREGATE 1 8
4 INDEX FULL SCAN (MIN/MAX) TEST_TABLE_NDX_1 103K 805K
5 VIEW 1 9 328
(3) 00:00:04
6 SORT AGGREGATE 1 8
7 INDEX FULL SCAN (MIN/MAX) TEST_TABLE_NDX_1 103K 805K
--------------------------------------------------------------------------------
-----------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
482 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> REM --> Only 4 Block Gets, although the "COST" is 655
SQL>
SQL>
SQL> spool off
No comments:
Post a Comment