1.version:
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Solaris: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
2.table:
SQL> create table numa (b varchar2(3));
Table created.
SQL> begin
2 for x in 1..15 loop
3 insert into numa values('15');
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> begin
2 for x in 1..500 loop
3 insert into numa values('500');
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> select count(*),b from numa
2 group by b;
COUNT(*) B
---------- ------
15 15
500 500
SQL> create index ind_numa on numa (b);
Index created.
3.gather statistics
SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'NUMA',method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY',cascade => TRUE);
SQL> l
1* SELECT TABLE_NAME,COLUMN_NAME,ENDPOINT_NUMBER,ENDPOINT_VALUE from USER_TAB_HISTOGRAMS WHERE TABLE_NAME='NUMA'
SQL> /
TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE
---------- ---------- --------------- --------------
NUMA B 15 2.5550E+35
NUMA B 515 2.7617E+35
4.run script
SQL> var x number
SQL> exec :x := 15;
PL/SQL procedure successfully completed.
SQL> SELECT COUNT(*) FROM NUMA WHERE B=:X;
COUNT(*)
----------
15
SQL> select SQL_TEXT,hash_value from v$sql where sql_text LIKE '%SELECT COUNT(*) FROM NUMA WHERE B=%';
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------------------------
HASH_VALUE
----------
select SQL_TEXT,hash_value from v$sql where sql_text LIKE '%SELECT COUNT(*) FROM NUMA WHERE B=%'
439001856
SELECT COUNT(*) FROM NUMA WHERE B=:X
1334146738
SQL> SELECT operation,
2 options,
3 object_name,
4 id,
5 parent_id,
6 cost,
7 cardinality
8 FROM v$sql_plan where hash_value=1334146738;
OPERATION OPTIONS
------------------------------------------------------------ ------------------------------------------------------------
OBJECT_NAME ID PARENT_ID COST CARDINALITY
------------------------------------------------------------ ---------- ---------- ---------- -----------
SELECT STATEMENT
0 3
SORT AGGREGATE
1 0 1
TABLE ACCESS FULL
NUMA 2 1 3 258
CARDINALITY=258这个值不对吧, 请高手执教
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Solaris: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
2.table:
SQL> create table numa (b varchar2(3));
Table created.
SQL> begin
2 for x in 1..15 loop
3 insert into numa values('15');
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> begin
2 for x in 1..500 loop
3 insert into numa values('500');
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> select count(*),b from numa
2 group by b;
COUNT(*) B
---------- ------
15 15
500 500
SQL> create index ind_numa on numa (b);
Index created.
3.gather statistics
SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'NUMA',method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY',cascade => TRUE);
SQL> l
1* SELECT TABLE_NAME,COLUMN_NAME,ENDPOINT_NUMBER,ENDPOINT_VALUE from USER_TAB_HISTOGRAMS WHERE TABLE_NAME='NUMA'
SQL> /
TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE
---------- ---------- --------------- --------------
NUMA B 15 2.5550E+35
NUMA B 515 2.7617E+35
4.run script
SQL> var x number
SQL> exec :x := 15;
PL/SQL procedure successfully completed.
SQL> SELECT COUNT(*) FROM NUMA WHERE B=:X;
COUNT(*)
----------
15
SQL> select SQL_TEXT,hash_value from v$sql where sql_text LIKE '%SELECT COUNT(*) FROM NUMA WHERE B=%';
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------------------------
HASH_VALUE
----------
select SQL_TEXT,hash_value from v$sql where sql_text LIKE '%SELECT COUNT(*) FROM NUMA WHERE B=%'
439001856
SELECT COUNT(*) FROM NUMA WHERE B=:X
1334146738
SQL> SELECT operation,
2 options,
3 object_name,
4 id,
5 parent_id,
6 cost,
7 cardinality
8 FROM v$sql_plan where hash_value=1334146738;
OPERATION OPTIONS
------------------------------------------------------------ ------------------------------------------------------------
OBJECT_NAME ID PARENT_ID COST CARDINALITY
------------------------------------------------------------ ---------- ---------- ---------- -----------
SELECT STATEMENT
0 3
SORT AGGREGATE
1 0 1
TABLE ACCESS FULL
NUMA 2 1 3 258
CARDINALITY=258这个值不对吧, 请高手执教
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/507279/viewspace-624699/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/507279/viewspace-624699/