第三天3-5索引】【执行计划】_索引【3:5】包括3和5吗-优快云博客

本文链接：https://blog.youkuaiyun.com/hughwang1216/article/details/12927737
本文探讨了如何优化SQL查询，包括调整参数设置、利用流捕获、分析执行计划、评估逻辑读次数以及通过注释避免索引使用，进而提高数据库性能。重点介绍了通过调整sga大小、收集统计信息、使用全局临时表等方法实现性能优化。
摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >
---3-5索引
select * from v$archived_log
select * from v$flash_recovery_area_usage --可以查看归档日志用量百分比

SQL> alter system set db_recovery_file_dest_size=3g;

系统已更改。

SQL> show parameter db_recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      C:\oracle\product\10.2.0\flash
                                                 _recovery_area
db_recovery_file_dest_size           big integer 3G

select * 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_files
select * from dba_tables where tablespace_name ='USERS'
select count(*) from sys.ttT
select * from dba_tables where table_name='TTT'
select * from ttt where rownum < 10;
select  object_id,count(*) from ttt group by object_id

SQL> 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     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 200M
sga_target                           big integer 200M

SQL> 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 processed


select 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 on
SQL> set autotrace off
SQL> select distinct sid from v$mystat;

       SID
----------
       158
select * from dba_hist_active_sess_history where sided=158  --本测试失败，没懂

SQL> set timing on
SQL> 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执行进度
--
SOFAR	TOTALWORK	UNITS        --SOFAR 当前完成多少块
133	10109	Blocks             --TOTALWORK 总共需要扫描多少块
1679	10109	Blocks           --UNITS 单位




select  * from ttt where object_id=6779

SQL> 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'

OWNER	TABLE_NAME	COLUMN_NAME	NUM_DISTINCT	LOW_VALUE	HIGH_VALUE	DENSITY	NUM_NULLS	NUM_BUCKETS	LAST_ANALYZED	SAMPLE_SIZE	GLOBAL_STATS	USER_STATS	AVG_COL_LEN	HISTOGRAM
SYS	TTT	OBJECT_ID	11222	C103	C302231B	8.911067545892E-5	0	1	2013-9-26 19:37:12	6720	YES	NO	5	NONE

set autotrace on--显示查询结果的跟踪 autotrace only 不显示查询的结果的跟踪
select object_id,count(*) from ttt group by object_id

select count（*) from tt where object_id=25 --思考，通过直接查询索引而不访问表同样能得到结果，而且执行效率非常非常的快，所谓sql优化，这是一种更好的方案。

create global temporary table gt1(id int); --基于事务的表
inset into gt1 values(1);
select * from gt1       --能查询到1
commit；
select * from gt1       --查询不到1，这就是基于事务的table的特点，事务结束数据就没了

create global temporary table gt2(id int) on commit delete rows;  --实际上就省略了on commit delete rows
create 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$log
v$logfile

select * from v$fixed_table
   	NAME	OBJECT_ID	TYPE	TABLE_NUM
1	X$KQFTA	4294950912	TABLE	0       --实力启动是创建的，用于跟踪性能，不需要掌握，oracle没公开的部分
select * from X$KQFTA
select distinct type from v$fixed_table
select * from dictionary  --查询视图