Search My Oracle Blog

Custom Search

28 August, 2007

When "COST" doesn't indicate true load

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:

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016