SQL Profile 总结（四）--使用示例，sqlprofile

SQL Profile 总结（四）--使用示例，sqlprofile

前提：sql profile工具的相关视图 dba_sql_profile 10g: sys.sqlprof$attr  &  sqlprof$ 11g: sys.sqlobj$data  &  sys.sqlobj$
1、主要完成四个示例，如下

• 使用dbms_sqltune.import_sql_profile过程手工指定提示的方式，这种方式要求非常高（查询块名等），一般不会使用
• 使用create_sql_profile.sql脚本固定内存中已经有的SQL的执行计划，通过指定sql_id
• 使用create_sql_profile_awr脚本来还原AWR里面保存的SQL语句的执行计划（暂时没环境测试）...
• 将提示集手工移入到另外一条SQL语句的sql profile中（通过move_sql_profile.sql脚本实现）；

示例： 一、使用dbms_sqltune.import_sql_profile过程手工指定提示的方式，这种方式要求非常高（查询块名等），一般不会使用 [oracle@192oracle ~]$ sqlplus dbmon/dbmon_123 SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 11 16:37:06 2014 Copyright (c) 1982, 2009, Oracle.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options SQL> create table dh_sql as select rownum id,object_name name ,object_type type from dba_objects; Table created. SQL> create index ind_dh_sql on dh_sql(id); Index created.
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'dbmon',TABNAME=>'dh_sql',ESTIMATE_PERCENT=>30,METHOD_OPT=>'FOR ALL COLUMNS SIZE 1',NO_INVALIDATE=>FALSE,CASCADE=>TRUE,DEGREE => 1);  PL/SQL procedure successfully completed.
SQL> set linesize 200 pagesize 9999 SQL> select /* test1 */ id,name from dh_sql where id=771;
        ID NAME ---------- --------------------------------------------------------------------------------------------------------------------------------        771 RULESET$
SQL> @sql_profiles.sql               --该脚本查看当前使用sql profile的语句 Enter value for sql_text:  old   3: where sql_text like nvl('&sql_text','%') new   3: where sql_text like nvl('','%') Enter value for name:  old   4: and name like nvl('&name',name) new   4: and name like nvl('',name) no rows selected
SQL> col name format a30  SQL> col type format a30 SQL> col sql_text format a40 SQL> select sql_text,sql_id,hash_value,child_number from v$sql a where sql_text like '%test1%' and sql_text not like '%v$sql%'; SQL_TEXT                                 SQL_ID        HASH_VALUE CHILD_NUMBER ---------------------------------------- ------------- ---------- ------------ select /* test1 */ id,name from dh_sql w 90nh2m7a3gsvf 3560432494            0
SQL> select * from table(dbms_xplan.display_cursor('90nh2m7a3gsvf','',''));
PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------- SQL_ID  90nh2m7a3gsvf, child number 0 ------------------------------------- select /* test1 */ id,name from dh_sql where id=:"SYS_B_0" Plan hash value: 1731829956 ------------------------------------------------------------------------------------------ | Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------------------ |   0 | SELECT STATEMENT            |            |       |       |     2 (100)|          | |   1 |  TABLE ACCESS BY INDEX ROWID| DH_SQL     |     1 |    30 |     2   (0)| 00:00:09 | |*  2 |   INDEX RANGE SCAN          | IND_DH_SQL |     1 |       |     1   (0)| 00:00:05 | ------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("ID"=:SYS_B_0) 19 rows selected.
SQL> @create_1_hint_sql_profile.sql Enter value for sql_id: 90nh2m7a3gsvf Enter value for profile_name (PROFILE_sqlid_MANUAL):  Enter value for category (DEFAULT):  Enter value for force_matching (false): true old  16: sql_id = '&&sql_id'; new  16: sql_id = '90nh2m7a3gsvf'; old  18: select decode('&&profile_name','X0X0X0X0','PROFILE_'||'&&sql_id'||'_MANUAL','&&profile_name') new  18: select decode('X0X0X0X0','X0X0X0X0','PROFILE_'||'90nh2m7a3gsvf'||'_MANUAL','X0X0X0X0') Enter value for hint: FULL(DH_SQL@SEL$1)           --手工输入指定提示 old  24: profile => sqlprof_attr('&hint'), new  24: profile => sqlprof_attr('FULL(DH_SQL@SEL$1)'), old  25: category => '&&category', new  25: category => 'DEFAULT', old  31: force_match => &&force_matching new  31: force_match => true PL/SQL procedure successfully completed.
SQL> select /* test1 */ id,name from dh_sql where id=771;                           ID NAME ---------- ------------------------------        771 RULESET$ SQL> select * from table(dbms_xplan.display_cursor('90nh2m7a3gsvf','',''));
PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------- SQL_ID  90nh2m7a3gsvf, child number 0 ------------------------------------- select /* test1 */ id,name from dh_sql where id=:"SYS_B_0" Plan hash value: 1575588977 ---------------------------------------------------------------------------- | Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |        |       |       |   112 (100)|          | |*  1 |  TABLE ACCESS FULL| DH_SQL |     1 |    30 |   112   (0)| 00:07:50 | ---------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("ID"=:SYS_B_0) Note -----    - SQL profile PROFILE_90nh2m7a3gsvf_MANUAL used for this statement 22 rows selected.
SQL> @sql_profiles.sql   --可以看到确实已经使用！ Enter value for sql_text:  old   3: where sql_text like nvl('&sql_text','%') new   3: where sql_text like nvl('','%') Enter value for name:  old   4: and name like nvl('&name',name) new   4: and name like nvl('',name) NAME                           CATEGORY        STATUS   SQL_TEXT                                                               FOR ------------------------------ --------------- -------- --------------------------------------------------------------------- PROFILE_90nh2m7a3gsvf_MANUAL   DEFAULT         ENABLED  select /* test1 */ id,name from dh_sql where id=:"SYS_B_0"             YES

SQL> conn /as sysdba Connected. SQL> set serveroutput on size 9999 SQL> @profile_hint.sql Enter value for profile_name: PROFILE_90nh2m7a3gsvf_MANUAL old  19:    'and name like (''&&profile_name'') '|| new  19:    'and name like (''PROFILE_90nh2m7a3gsvf_MANUAL'') '|| old  38:    'and p.name like (''&&profile_name'')) '|| new  38:    'and p.name like (''PROFILE_90nh2m7a3gsvf_MANUAL'')) '|| HINT ----------------------------------------------------------------------------------------------------------------------------- FULL(DH_SQL@SEL$1)               --可以看到sql profile的基表里面保存了我们指定的提示




二、使用create_sql_profile.sql脚本固定内存中已经有的SQL的执行计划，通过指定sql_id SQL> conn dbmon/dbmon_123  Connected. SQL> select * from dh_sql where name='DBA_TABLES';         ID NAME                           TYPE ---------- ------------------------------ ------------------------------       3167 DBA_TABLES                     VIEW       3168 DBA_TABLES                     SYNONYM 2 rows selected. SQL> select /* test2 */ * from dh_sql where name='DBA_TABLES';         ID NAME                           TYPE ---------- ------------------------------ ------------------------------       3167 DBA_TABLES                     VIEW       3168 DBA_TABLES                     SYNONYM 2 rows selected. SQL> select sql_text,sql_id,hash_value,child_number from v$sql a where sql_text like '%test2%' and sql_text not like '%v$sql%'; SQL_TEXT                                                               SQL_ID        HASH_VALUE CHILD_NUMBER ---------------------------------------------------------------------- ------------- ---------- ------------ select /* test2 */ * from dh_sql where name=:"SYS_B_0"                 0xy0uj562r893 1277927715            0 1 row selected. SQL> select * from table(dbms_xplan.display_cursor('0xy0uj562r893','','')); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------- SQL_ID  0xy0uj562r893, child number 0 ------------------------------------- select /* test2 */ * from dh_sql where name=:"SYS_B_0" Plan hash value: 1575588977 ---------------------------------------------------------------------------- | Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |        |       |       |   112 (100)|          | |*  1 |  TABLE ACCESS FULL| DH_SQL |     2 |    76 |   112   (0)| 00:07:50 | ---------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("NAME"=:SYS_B_0) 18 rows selected.
SQL> @create_sql_profile.sql    --使用这个脚本固定内存中指定SQL_ID的执行计划 Enter value for sql_id: 0xy0uj562r893 Enter value for child_no (0):  Enter value for profile_name (PROF_sqlid_planhash):  Enter value for category (DEFAULT):  Enter value for force_matching (FALSE): TRUE old  19: sql_id = '&&sql_id' new  19: sql_id = '0xy0uj562r893' old  20: and child_number = &&child_no new  20: and child_number = 0 old  27: decode('&&profile_name','X0X0X0X0','PROF_&&sql_id'||'_'||plan_hash_value,'&&profile_name') new  27: decode('X0X0X0X0','X0X0X0X0','PROF_0xy0uj562r893'||'_'||plan_hash_value,'X0X0X0X0') old  33: sql_id = '&&sql_id' new  33: sql_id = '0xy0uj562r893' old  34: and child_number = &&child_no; new  34: and child_number = 0; old  39: category => '&&category', new  39: category => 'DEFAULT', old  41: force_match => &&force_matching new  41: force_match => TRUE old  52:   dbms_output.put_line('ERROR: sql_id: '||'&&sql_id'||' Child: '||'&&child_no'||' not found in v$sql.'); new  52:   dbms_output.put_line('ERROR: sql_id: '||'0xy0uj562r893'||' Child: '||'0'||' not found in v$sql.'); SQL>  SQL> select /* test2 */ * from dh_sql where name='DBA_TABLES';
        ID NAME                           TYPE ---------- ------------------------------ ------------------------------       3167 DBA_TABLES                     VIEW       3168 DBA_TABLES                     SYNONYM 2 rows selected. SQL> select * from table(dbms_xplan.display_cursor('0xy0uj562r893','','')); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------- SQL_ID  0xy0uj562r893, child number 0 ------------------------------------- select /* test2 */ * from dh_sql where name=:"SYS_B_0" Plan hash value: 1575588977 ---------------------------------------------------------------------------- | Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |        |       |       |   112 (100)|          | |*  1 |  TABLE ACCESS FULL| DH_SQL |     2 |    76 |   112   (0)| 00:07:50 | ---------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("NAME"=:SYS_B_0) Note -----    - SQL profile PROF_0xy0uj562r893_1575588977 used for this statement   --可以看到sql profile确实已经生效 22 rows selected. --新建一个索引，确认已经固定执行计划的语句不会因为访问路径而改变执行计划 SQL> create index ind_dh_sql2 on dh_sql(name) compute statistics;    Index created. SQL> select /* test2 */ * from dh_sql a where name='DBA_TABLES';         ID NAME                           TYPE ---------- ------------------------------ ------------------------------       3167 DBA_TABLES                     VIEW       3168 DBA_TABLES                     SYNONYM 2 rows selected. SQL> select sql_text,sql_id,hash_value,child_number from v$sql a where sql_text like '%test2%' and sql_text not like '%v$sql%'; SQL_TEXT                                                               SQL_ID        HASH_VALUE CHILD_NUMBER ---------------------------------------------------------------------- ------------- ---------- ------------ select /* test2 */ * from dh_sql a where name=:"SYS_B_0"               bp7gpwq6w88nv 2378441371            0 select /* test2 */ * from dh_sql where name=:"SYS_B_0"                 0xy0uj562r893 1277927715            0 2 rows selected. SQL> select * from table(dbms_xplan.display_cursor('bp7gpwq6w88nv','','')); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------- SQL_ID  bp7gpwq6w88nv, child number 0 ------------------------------------- select /* test2 */ * from dh_sql a where name=:"SYS_B_0" Plan hash value: 3828038811 ------------------------------------------------------------------------------------------- | Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |             |       |       |     4 (100)|          | |   1 |  TABLE ACCESS BY INDEX ROWID| DH_SQL      |     2 |    76 |     4   (0)| 00:00:17 | |*  2 |   INDEX RANGE SCAN          | IND_DH_SQL2 |     2 |       |     3   (0)| 00:00:13 |  --没有固定的语句使用索引计划 ------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("NAME"=:SYS_B_0) 19 rows selected.
SQL> select /* test2 */ * from dh_sql where name='DBA_TABLES';
        ID NAME                           TYPE ---------- ------------------------------ ------------------------------       3167 DBA_TABLES                     VIEW       3168 DBA_TABLES                     SYNONYM 2 rows selected. SQL> select * from table(dbms_xplan.display_cursor('0xy0uj562r893','','')); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------- SQL_ID  0xy0uj562r893, child number 0 ------------------------------------- select /* test2 */ * from dh_sql where name=:"SYS_B_0" Plan hash value: 1575588977 ---------------------------------------------------------------------------- | Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |        |       |       |   112 (100)|          | |*  1 |  TABLE ACCESS FULL| DH_SQL |     2 |    76 |   112   (0)| 00:07:50 |   --已经固定的执行计划还是使用全表扫描 ---------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("NAME"=:SYS_B_0) Note -----    - SQL profile PROF_0xy0uj562r893_1575588977 used for this statement 22 rows selected.
SQL> conn /as sysdba Connected. SQL> set serveroutput on size 99999 SQL> @sql_profile_hints.sql Enter value for profile_name: PROF_0xy0uj562r893_1575588977 old  19:    'and name like (''&&profile_name'') '|| new  19:    'and name like (''PROF_0xy0uj562r893_1575588977'') '|| old  38:    'and p.name like (''&&profile_name'')) '|| new  38:    'and p.name like (''PROF_0xy0uj562r893_1575588977'')) '|| HINT ----------------------------------------------------------------------------------------------------------------------------- IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.1') DB_VERSION('11.2.0.1') OPT_PARAM('_optim_peek_user_binds' 'false') OPT_PARAM('_optimizer_null_aware_antijoin' 'false') OPT_PARAM('_bloom_filter_enabled' 'false') OPT_PARAM('_optimizer_extended_cursor_sharing' 'none') OPT_PARAM('_gby_hash_aggregation_enabled' 'false') OPT_PARAM('_bloom_pruning_enabled' 'false') OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none') OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false') OPT_PARAM('_optimizer_use_feedback' 'false') ALL_ROWS OUTLINE_LEAF(@"SEL$1") FULL(@"SEL$1" "DH_SQL"@"SEL$1") 15 rows selected.


三、使用create_sql_profile_awr脚本来还原AWR里面保存的SQL语句的执行计划（暂时没环境测试）...
四、将提示集手工移入到另外一条SQL语句的sql profile中（通过move_sql_profile.sql脚本实现）；方法如下： 1、执行一条与需要固定执行计划的SQL语句结构一致的语句 2、通过各种方法来实现将第一步运行的SQL语句，得到自己预期的执行计划（添加提示，修改参数等等） 3、通过这个语句运行产生的提示集合（v$sql_plan.other_xml列），来为需要固定的SQL语句创建sql profile从而固定执行计划 SQL> select * from table(dbms_xplan.display_cursor('bp7gpwq6w88nv','','')); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------- SQL_ID  bp7gpwq6w88nv, child number 0 ------------------------------------- select /* test2 */ * from dh_sql a where name=:"SYS_B_0" Plan hash value: 3828038811 ------------------------------------------------------------------------------------------- | Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |             |       |       |     4 (100)|          | |   1 |  TABLE ACCESS BY INDEX ROWID| DH_SQL      |     2 |    76 |     4   (0)| 00:00:17 | |*  2 |   INDEX RANGE SCAN          | IND_DH_SQL2 |     2 |       |     3   (0)| 00:00:13 |  --需要固定的SQL的执行计划 ------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("NAME"=:SYS_B_0) 19 rows selected. SQL> select /* test2 */ /*+ full(a) */ *from dh_sql a where name='DBA_TABLES';
        ID NAME                           TYPE ---------- ------------------------------ ------------------------------       3167 DBA_TABLES                     VIEW       3168 DBA_TABLES                     SYNONYM 2 rows selected. SQL> select sql_text,sql_id,hash_value,child_number from v$sql a where sql_text like '%test2%' and sql_text not like '%v$sql%'; SQL_TEXT                                                               SQL_ID        HASH_VALUE CHILD_NUMBER ---------------------------------------------------------------------- ------------- ---------- ------------ select /* test2 */ * from dh_sql a where name=:"SYS_B_0"               bp7gpwq6w88nv 2378441371            0 select /* test2 */ * from dh_sql where name=:"SYS_B_0"                 0xy0uj562r893 1277927715            0 select /* test2 */ /*+ full(a) */ *from dh_sql a where name=:"SYS_B_0" 6vq4tjw38m8hk  109683218            0 3 rows selected. SQL> select * from table(dbms_xplan.display_cursor('6vq4tjw38m8hk','','')); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------- SQL_ID  6vq4tjw38m8hk, child number 0 ------------------------------------- select /* test2 */ /*+ full(a) */ *from dh_sql a where name=:"SYS_B_0" Plan hash value: 1575588977
---------------------------------------------------------------------------- | Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |        |       |       |   112 (100)|          | |*  1 |  TABLE ACCESS FULL| DH_SQL |     2 |    76 |   112   (0)| 00:07:50 |   --我们指定的预期执行计划 ---------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("NAME"=:SYS_B_0) 18 rows selected.
SQL> @create_sql_profile.sql     ---为这个预期的SQL执行计划创建sql profile Enter value for sql_id: 6vq4tjw38m8hk Enter value for child_no (0):  Enter value for profile_name (PROF_sqlid_planhash):  Enter value for category (DEFAULT):  Enter value for force_matching (FALSE): TRUE old  19: sql_id = '&&sql_id' new  19: sql_id = '6vq4tjw38m8hk' old  20: and child_number = &&child_no new  20: and child_number = 0 old  27: decode('&&profile_name','X0X0X0X0','PROF_&&sql_id'||'_'||plan_hash_value,'&&profile_name') new  27: decode('X0X0X0X0','X0X0X0X0','PROF_6vq4tjw38m8hk'||'_'||plan_hash_value,'X0X0X0X0') old  33: sql_id = '&&sql_id' new  33: sql_id = '6vq4tjw38m8hk' old  34: and child_number = &&child_no; new  34: and child_number = 0; old  39: category => '&&category', new  39: category => 'DEFAULT', old  41: force_match => &&force_matching new  41: force_match => TRUE old  52:   dbms_output.put_line('ERROR: sql_id: '||'&&sql_id'||' Child: '||'&&child_no'||' not found in v$sql.'); new  52:   dbms_output.put_line('ERROR: sql_id: '||'6vq4tjw38m8hk'||' Child: '||'0'||' not found in v$sql.'); SQL> select /* test2 */ /*+ full(a) */ *from dh_sql a where name='DBA_TABLES';         ID NAME                           TYPE ---------- ------------------------------ ------------------------------       3167 DBA_TABLES                     VIEW       3168 DBA_TABLES                     SYNONYM 2 rows selected. SQL> select * from table(dbms_xplan.display_cursor('6vq4tjw38m8hk','','')); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------- SQL_ID  6vq4tjw38m8hk, child number 0 ------------------------------------- select /* test2 */ /*+ full(a) */ *from dh_sql a where name=:"SYS_B_0" Plan hash value: 1575588977 ---------------------------------------------------------------------------- | Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |        |       |       |   112 (100)|          | |*  1 |  TABLE ACCESS FULL| DH_SQL |     2 |    76 |   112   (0)| 00:07:50 | ---------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("NAME"=:SYS_B_0) Note -----    - SQL profile PROF_6vq4tjw38m8hk_1575588977 used for this statement 22 rows selected.
SQL> @move_sql_profile.sql           --需要一些权限，建议用SYS用户执行 Enter value for profile_name: PROF_6vq4tjw38m8hk_1575588977 Enter value for sql_id: bp7gpwq6w88nv Enter value for category (DEFAULT):  Enter value for force_matching (false): TRUE old  18:    'and name like (''&&profile_name'') '|| new  18:    'and name like (''PROF_6vq4tjw38m8hk_1575588977'') '|| old  36:    'and p.name like (''&&profile_name'')) '|| new  36:    'and p.name like (''PROF_6vq4tjw38m8hk_1575588977'')) '|| old  55: and name like ('&&profile_name') new  55: and name like ('PROF_6vq4tjw38m8hk_1575588977') old  66: sql_id = '&&sql_id'; new  66: sql_id = 'bp7gpwq6w88nv'; old  71: , category => '&&category' new  71: , category => 'DEFAULT' old  72: , name => 'PROFILE_'||'&&sql_id'||'_moved' new  72: , name => 'PROFILE_'||'bp7gpwq6w88nv'||'_moved' old  77: , force_match => &&force_matching new  77: , force_match => TRUE declare * ERROR at line 1: ORA-00942: table or view does not exist ORA-06512: at line 26

SQL> conn /as sysdba Connected. SQL> set verify off SQL> @move_sql_profile.sql Enter value for profile_name: PROF_6vq4tjw38m8hk_1575588977 Enter value for sql_id: bp7gpwq6w88nv Enter value for category (DEFAULT):  Enter value for force_matching (false):  PL/SQL procedure successfully completed. SQL> conn dbmon/dbmon_123 Connected. SQL> select /* test2 */ * from dh_sql a where name='DBA_TABLES';
        ID NAME                           TYPE ---------- ------------------------------ ------------------------------       3167 DBA_TABLES                     VIEW       3168 DBA_TABLES                     SYNONYM 2 rows selected. SQL> select * from table(dbms_xplan.display_cursor('bp7gpwq6w88nv','','')); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------- SQL_ID  bp7gpwq6w88nv, child number 0 ------------------------------------- select /* test2 */ * from dh_sql a where name=:"SYS_B_0" Plan hash value: 1575588977 ---------------------------------------------------------------------------- | Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |        |       |       |   112 (100)|          | |*  1 |  TABLE ACCESS FULL| DH_SQL |     2 |    76 |   112   (0)| 00:07:50 | ---------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("NAME"=:SYS_B_0) Note -----    - SQL profile PROFILE_bp7gpwq6w88nv_moved used for this statement   --目的达成！ 22 rows selected.

1、说明：创建数据库
CREATE DATABASE database-name
2、说明：删除数据库
drop database dbname
3、说明：备份sql server
--- 创建 备份数据的 device
USE master
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat'
--- 开始 备份
BACKUP DATABASE pubs TO testBack
4、说明：创建新表
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)
根据已有的表创建新表：
A：create table tab_new like tab_old (使用旧表创建新表)
B：create table tab_new as select col1,col2… from tab_old definition only
5、说明：删除新表
drop table tabname
6、说明：增加一个列
Alter table tabname add column col type
注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。
7、说明：添加主键： Alter table tabname add primary key(col)
说明：删除主键： Alter table tabname drop primary key(col)
8、说明：创建索引：create [unique] index idxname on tabname(col….)
删除索引：drop index idxname
注：索引是不可更改的，想更改必须删除重新建。
9、说明：创建视图：create view viewname as select statement
删除视图：drop view viewname
10、说明：几个简单的基本的sql语句
选择：select * from table1 where 范围
插入：insert into table1(field1,field2) values(value1,value2)
删除：delete from table1 where 范围
更新：update table1 set field1=value1 where 范围
查找：select * from table1 where field1 like ’%value1%’ ---like的语法很精妙，查资料!
排序：select * from table1 order by field1,field2 [desc]
总数：select count as totalcount from table1
求和：select sum(field1) as sumvalue from table1
平均：select avg(field1) as avgvalue from table1
最大：select max(field1) as maxvalue from table1
最小：select min(field1) as minvalue from table1
11、说明：几个高级查询运算词
A： UNION 运算符
UNION 运算符通过组......余下全文>>
 

是MS SQL Server用来实时进行SQL语句跟踪的工具。