oracle實驗記錄 （FTS的cost與基數計算）

SQL> ed~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~FTS
Wrote file afiedt.buf

  1  explain plan for select
  2     small_vc
  3  from
  4     t1
  5  where
  6     n1      = 2
  7* and        n2      = 3
SQL> /

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 1931397137

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    20 |   340 |   113   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |    20 |   340 |   113   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("N1"=2 AND "N2"=3)

13 rows selected.

 

SQL> select  column_name ,num_distinct from user_tab_col_statistics where table_
name='T1';

COLUMN_NAME                    NUM_DISTINCT
------------------------------ ------------
N1                                       25
IND_PAD                                   1
N2                                       20
SMALL_VC                              10000
PADDING                                   1

SQL> select  num_rows from user_tab_statistics where table_name='T1';

  NUM_ROWS
----------
     10000  
CARD=10000 *(1/25*1/20)~~雙謂詞=20

SQL> show parameter db_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count        integer     16
db_file_name_convert                 string
db_files                             integer     200
SQL>

SQL> show parameter db_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count        integer     16
db_file_name_convert                 string
db_files                             integer     200
SQL>
SQL> select pname ,pval1 from aux_stats$ where sname='SYSSTATS_MAIN';

PNAME                               PVAL1
------------------------------ ----------
CPUSPEEDNW                       1212.002
IOSEEKTIM                              10
IOTFRSPEED                           4096
SREADTIM                            8.621
MREADTIM                           18.125
CPUSPEED                             1498
MBRC                                    7
MAXTHR
SLAVETHR
SQL> select blocks from user_segments where segment_name='T1';

    BLOCKS
----------
       384

SQL>
SQL> select (384/7)*(18.125/8.621) from dual;

(384/7)*(18.125/8.621)
----------------------
            115.332991

 

 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~關於card

SQL> create table t2 (a int,b int ,c int);

Table created.

  1  declare
  2  begin
  3  for i  in 1..10000 loop
  4  insert into t2 values(i,i+1,i+2);
  5  end loop;
  6  commit;
  7* end;
SQL> /
SQL> exec dbms_stats.gather_table_stats('sys','t2');

PL/SQL procedure successfully completed.
SQL> /

SQL> explain plan for select count(*) from t2 where a=2 and b=3 and c=4;

Explained.

SQL> select * from  table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
----------------------------------------------------
Plan hash value: 720591907

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    11 |    10   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |    11 |            |          |
|*  2 |   TABLE ACCESS FULL| T2   |     1 |    11 |    10   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
----------------------------------------------------
---------------------------------------------------

   2 - filter("A"=2 AND "B"=3 AND "C"=4)

14 rows selected.

SQL> select pname ,pval1 from aux_stats$ where sname='SYSSTATS_MAIN';

PNAME                               PVAL1
------------------------------ ----------
CPUSPEEDNW                       1212.002
IOSEEKTIM                              10
IOTFRSPEED                           4096
SREADTIM                            8.621
MREADTIM                           18.125
CPUSPEED                             1498
MBRC                                    7
MAXTHR
SLAVETHR

9 rows selected.

SQL> select  column_name ,num_distinct,density from user_tab_col_statistics wher
e table_name='T2';

COLUMN_NAME                    NUM_DISTINCT    DENSITY
------------------------------ ------------ ----------
A                                     10000      .0001
B                                     10000      .0001
C                                     10000      .0001

SQL>

card=10000*(1/10000)*1/10000*1/10000)~太小了只能等於1最小 也得 1吧,不然搜尋啥呢

SQL> select blocks from user_segments where segment_name='T2';

    BLOCKS
----------
        32
cost (IO)=(32/7)*( 18.125/8.621)

SQL> select (32/7)*( 18.125/8.621) from dual;

(32/7)*(18.125/8.621)
---------------------
           9.61108257
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~`

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-611427/，如需轉載，請註明出處，否則將追究法律責任。