第三天3-5索引】【执行计划】

---3-5索引select * from v$archived_logselect * from v$flash_recovery_area_usage --可以查看归档日志用量百分比SQL> alter system set db_recovery_file_dest_size=3g;系统已更改。SQL> show parameter db_recoveryNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------db_recovery_file_dest                string      C:\oracle\product\10.2.0\flash                                                 _recovery_areadb_recovery_file_dest_size           big integer 3Gselect * from v$session_wait where wait_class<>'Idle'select * from v$streams_capture  --查看流捕获，流用于oracle数据库之间的数据同步exec dbms_capture_adm.stop_capture('TEST$CAP');   --停掉流捕获，TEST$CAP是流捕获的名字select * from dba_data_filesselect * from dba_tables where tablespace_name ='USERS'select count(*) from sys.ttTselect * from dba_tables where table_name='TTT'select * from ttt where rownum < 10;select  object_id,count(*) from ttt group by object_idSQL> select  object_id,count(*) from ttt group by object_id;已选择11334行。执行计划----------------------------------------------------------Plan hash value: 3008753727-----------------------------------------------------------------------------------| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |-----------------------------------------------------------------------------------|   0 | SELECT STATEMENT   |      | 11331 | 56655 |       |  3046   (2)| 00:00:37 ||   1 |  HASH GROUP BY     |      | 11331 | 56655 |  8704K|  3046   (2)| 00:00:37 ||   2 |   TABLE ACCESS FULL| TTT  |   727K|  3551K|       |  2222   (1)| 00:00:27 |-----------------------------------------------------------------------------------统计信息----------------------------------------------------------          1  recursive calls          0  db block gets       9362  consistent gets       9345  physical reads        116  redo size     199903  bytes sent via SQL*Net to client       8705  bytes received via SQL*Net from client        757  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)      11334  rows processed                  SQL> select * from ttt where object_id=25  2  ;已选择64行。执行计划----------------------------------------------------------Plan hash value: 2391132391-------------------------------------------------------------------------------| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Ti-------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |         |    64 |  5440 |    68   (0)| 00|   1 |  TABLE ACCESS BY INDEX ROWID| TTT     |    64 |  5440 |    68   (0)| 00|*  2 |   INDEX RANGE SCAN          | IDX_TTT |    64 |       |     3   (0)| 00-------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("OBJECT_ID"=25)统计信息----------------------------------------------------------          1  recursive calls          0  db block gets         72  consistent gets                          --逻辑读，评价sql性能，比看时间准确，它表示orcl从内存中读访问过的块的块数         63  physical reads          0  redo size       2126  bytes sent via SQL*Net to client        444  bytes received via SQL*Net from client          6  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)         64  rows processed--查询100万条数据时过慢，可以增加sga大小,修改完SGA大小需要重新启动SQL> show parameter sga; NAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------lock_sga                             boolean     FALSEpre_page_sga                         boolean     FALSEsga_max_size                         big integer 200Msga_target                           big integer 200MSQL> select /*+ no_index(ttt idx_ttt) */ * from ttt where object_id=25;      --注释作用是让查询语句不走索引，比较逻辑读，发现比走索引要慢的多已选择64行。执行计划----------------------------------------------------------Plan hash value: 774701505--------------------------------------------------------------------------| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------|   0 | SELECT STATEMENT  |      |    64 |  5440 |  2223   (1)| 00:00:27 ||*  1 |  TABLE ACCESS FULL| TTT  |    64 |  5440 |  2223   (1)| 00:00:27 |--------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("OBJECT_ID"=25)统计信息----------------------------------------------------------          1  recursive calls          0  db block gets       9366  consistent gets                   --明显比用索引慢很多       9243  physical reads          0  redo size       2126  bytes sent via SQL*Net to client        444  bytes received via SQL*Net from client          6  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)         64  rows processedselect cpu_time　from v$sql where sql_text like 'select /*+ no_index(ttt idx_ttt) */ * from ttt where object_id=25' --查询sql执行的时间，单位10的6次幂，微秒--elapsed_time是花费时间，cpu_time 是处理器处理时间SQL> set autotrace onSQL> set autotrace offSQL> select distinct sid from v$mystat;       SID----------       158select * from dba_hist_active_sess_history where sided=158  --本测试失败，没懂SQL> set timing onSQL> select cpu_time　from v$sql where sql_text like 'select /*+ no_index(ttt idx_ttt) */ * from ttt where object_id=25';  CPU_TIME----------    266268已用时间:  00: 00: 00.01            --不准确，也就是elapsed_time的四舍五入tahiti.oracle.com  --oracle 在线文档，可以查视图，表等select * from v$session_longops  -- sql 执行 cputime>6s 的信息，跟踪sql执行进度--SOFARTOTALWORKUNITS        --SOFAR 当前完成多少块13310109Blocks             --TOTALWORK 总共需要扫描多少块167910109Blocks           --UNITS 单位select  * from ttt where object_id=6779SQL> exec dbms_stats.gather_table_stats('SYS','TTT',cascade => TRUE)--优化系统统计信息，收集dba_tables 数据有多少块，每个列的数据的分布信息，凭此自动选择是否选择使用索引，来提高sql执行速度。--10g以后，oracle每天晚上10点以后自动执行这句，来收集各表列的统计信息，10g以前需要写成job,认为设定每晚自动执行PL/SQL 过程已成功完成。select * from dba_scheduler_jobs  --其中JOB_NAME为GATHER_STATS_JOB就是以上提到的行列信息统计select * from dba_tab_statistics where table_name='TTT'     --?? select * from dba_tab_col_statistics where table_name='TTT'OWNERTABLE_NAMECOLUMN_NAMENUM_DISTINCTLOW_VALUEHIGH_VALUEDENSITYNUM_NULLSNUM_BUCKETSLAST_ANALYZEDSAMPLE_SIZEGLOBAL_STATSUSER_STATSAVG_COL_LENHISTOGRAMSYSTTTOBJECT_ID11222C103C302231B8.911067545892E-5012013-9-26 19:37:126720YESNO5NONEset autotrace on--显示查询结果的跟踪 autotrace only 不显示查询的结果的跟踪select object_id,count(*) from ttt group by object_idselect count（*) from tt where object_id=25 --思考，通过直接查询索引而不访问表同样能得到结果，而且执行效率非常非常的快，所谓sql优化，这是一种更好的方案。create global temporary table gt1(id int); --基于事务的表inset into gt1 values(1);select * from gt1       --能查询到1commit；select * from gt1       --查询不到1，这就是基于事务的table的特点，事务结束数据就没了create global temporary table gt2(id int) on commit delete rows;  --实际上就省略了on commit delete rowscreate global temporary table gt3(id int) on commit preserve rows; --当session失效时，数据就没了，而且数据只在该session中可见。1.tab$,obj$ --基表 支撑视图的base table dba_  all_ user_ (data dictionary)  --来源于基表2.动态性能表 X$  --支持视图的3.动态性能视图  v$v$logv$logfileselect * from v$fixed_table   NAMEOBJECT_IDTYPETABLE_NUM1X$KQFTA4294950912TABLE0       --实力启动是创建的，用于跟踪性能，不需要掌握，oracle没公开的部分select * from X$KQFTAselect distinct type from v$fixed_tableselect * from dictionary  --查询视图