微课sql优化(8)、统计信息收集(6)-统计信息查询
发表于:2024-09-21 作者:千家信息网编辑
千家信息网最后更新 2024年09月21日,1、关于查询统计信息dba_tablesprompt | ----------------1 dba_tables-------------------------------------------
千家信息网最后更新 2024年09月21日微课sql优化(8)、统计信息收集(6)-统计信息查询
prompt | ----------------2 dba_tab_partitoins------------------------------------+^M column p_name format a10 heading 'p_NAME' print entmap off select tp .table_owner owner , tp .table_name table_name , tp .partition_name p_name , tp .subpartition_count sp_count , tp .num_rows NUM_ROWS , blocks , avg_row_len , tp .last_analyzed from dba_tab_partitions tp where tp .table_owner = upper ( '&TABLE_OWNER' ) and tp .TABLE_NAME = upper ( '&TABLE_NAME' ) / |----------------2 dba_tab_partitoins------------------------------------+^M Ownerprompt | ----------------3 dba_tab_subpartitions---------------------------------+ column sp_name format a20 heading 'sp_NAME' print entmap off select sp .table_owner owner , sp .table_name table_name , sp .partition_name p_name , sp .subpartition_name sp_name , sp .num_rows NUM_ROWS , blocks , avg_row_len , sp .last_analyzed from dba_tab_subpartitions sp where sp .table_owner = upper ( '&TABLE_OWNER' ) and sp .TABLE_NAME = upper ( '&TABLE_NAME' ) / |----------------3 dba_tab_subpartitions---------------------------------+prompt | ----------------5 dba_indexes---------------------------------+ column BL format 99 heading 'BL' print entmap off column cr format a4 heading 'cr' print entmap off column IDX_KEY format a20 heading 'IDX_KEY' print entmap off column uniq format a4 heading 'uniq' print entmap off column INDEX_NAME format a20 heading 'INDEX_NAME' print entmap off column par format a3 heading 'par' print entmap off select d .OWNER , d .INDEX_NAME , substr (d .uniqueness ,1 ,4 ) uniq , d .blevel bl , d .leaf_blocks , d .clustering_factor c_factor , d .num_rows , round ( nvl (d .clustering_factor ,1 ) / nvl (d .num_rows ,1 ) ,2 ) *100 | | '%' cr , d .distinct_keys d_keys , ( select m .COLUMN_NAME from dba_ind_columns m where m .INDEX_OWNER = d .OWNER and m .INDEX_NAME = d .INDEX_NAME and m .COLUMN_POSITION = 1 ) | | ( select ',' | | m .COLUMN_NAME from dba_ind_columns m where m .INDEX_OWNER = d .OWNER and m .INDEX_NAME = d .INDEX_NAME and m .COLUMN_POSITION = 2 ) | | ( select ',' | | m .COLUMN_NAME from dba_ind_columns m where m .INDEX_OWNER = d .OWNER and m .INDEX_NAME = d .INDEX_NAME and m .COLUMN_POSITION = 3 ) | | ( select ',' | | m .COLUMN_NAME from dba_ind_columns m where m .INDEX_OWNER = d .OWNER and m .INDEX_NAME = d .INDEX_NAME and m .COLUMN_POSITION = 4 ) idx_key , d .partitioned par from dba_indexes d where d .table_owner = upper ( '&TABLE_OWNER' ) and d .TABLE_NAME = upper ( '&TABLE_NAME' ) order by 1 , 2 desc / |----------------5 dba_indexes---------------------------------+ Ownerprompt | ----------------6 dba_tab_modifications----------------------------------+^M select table_owner , table_name , partition_name p_name , subpartition_name sp_name , inserts , updates , deletes from dba_tab_modifications where table_owner = upper ( '&TABLE_OWNER' ) and TABLE_NAME = upper ( '&TABLE_NAME' ) / |----------------6 dba_tab_modifications----------------------------------+^M* * * * * * * Oracle 10G parallel 8 HP - UX nopartitioned * * * * * * * * * * * * * * * * * * * * * * table_name table_size (M ) index_size (M ) total (M ) time --------- ------------ ------------ ------- -------------------- tab1 488 0 488 Elapsed : 00 :00 :27 .99 tab2 1 ,115 331 1 ,446 Elapsed : 00 :00 :06 .28 tab3 2 ,019 243 2 ,262 Elapsed : 00 :00 :44 .30 tab4 3 ,171 1 ,221 4 ,392 Elapsed : 00 :02 :17 .08 tab5 4 ,756 0 4 ,756 Elapsed : 00 :05 :42 .85 tab6 15 ,146 16 ,059 31 ,205 Elapsed : 00 :29 :59 .14 tab7 8 ,105 4 ,820 12 ,925 Elapsed : 00 :26 :12 .52
1、关于查询统计信息
- dba_tables
- prompt | ----------------1 dba_tables--------------------------------------------+^M
- column owner format a10 heading 'Owner' print entmap off
- column table_name format a15 heading 'Table_Name' print entmap off
- column NUM_ROWS format 999 ,999 ,999 ,999 heading 'Num_Rows' print entmap off
- column blocks format 999 ,999 ,999 heading 'Blocks' print entmap off
- column avg_row_len format 999 ,999 heading 'Avg_Row_len' print entmap off
- column LAST_ANALYZED format a20 heading 'Last_Analyzed' print entmap off
- column PARTITIONED format a5 heading 'Par' print entmap off
- column par_key format a10 heading 'Par_Key' print entmap off
- column subpar_key format a10 heading 'Subpar_Key' print entmap off
- column "ESTIMATE_PERCENT%" format a4 heading 'ESTIMATE_PERCENT%' print entmap off
- select t .OWNER ,
- t .TABLE_NAME ,
- t .NUM_ROWS ,
- blocks ,
- avg_row_len ,
- t .LAST_ANALYZED ,
- round ( nvl (t .SAMPLE_SIZE ,1 ) / nvl (t .NUM_ROWS ,1 ) ,2 ) *100 | | '%' "ESTIMATE_PERCENT%" ,
- t .PARTITIONED ,
- ( select nvl (m .column_name , 'null' )
- from dba_part_key_columns m
- where m .owner = t .OWNER
- and m .name = t .TABLE_NAME ) "par_key" ,
- ( select nvl (sm .column_name , 'null' )
- from dba_subpart_key_columns sm
- where sm .owner = t .OWNER
- and sm .name = t .TABLE_NAME ) "subpar_key"
- from dba_tables t
- where t .OWNER = upper ( '&TABLE_OWNER' )
- and t .TABLE_NAME = upper ( '&TABLE_NAME' )
- /
- dba_tab_columns
- prompt | ----------------4 dba_tab_columns---------------------------------+
- column COLUMN_NAME format a20 heading 'COLUMN_NAME' print entmap off
- column HISTOGRAM format a10 heading 'HISTOGRAM' print entmap off
- select m .OWNER ,
- m .TABLE_NAME ,
- m .COLUMN_NAME ,
- m .NUM_DISTINCT ,
- m .HISTOGRAM ,
- m .NUM_NULLS ,
- m .LAST_ANALYZED
- from dba_tab_columns m
- where m .OWNER = upper ( '&TABLE_OWNER' )
- and m .TABLE_NAME = upper ( '&TABLE_NAME' )
- ORDER BY NUM_DISTINCT DESC ;
- dba_tab_statistics
- prompt | ----------------7 dba_tab_statistics------------------------------------+^M
- column object_type format a15 heading 'object_type' print entmap off
- select owner , table_name , object_type , stale_stats ,
- num_rows ,
- sample_size ,
- trunc (sample_size / num_rows * 100 ) estimate_percent ,
- last_analyzed
- from dba_tab_statistics
- where OWNER = upper ( '&TABLE_OWNER' )
- and TABLE_NAME = upper ( '&TABLE_NAME' )
- /
信息
查询
统计
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
现在做软件开发的多少钱一个月
wos数据库趋势提取
华为无线终端物理层软件开发
数据库配置中哪些是隐藏
网络安全 纵深防御原则
泛互联网科技
学习手机软件开发零基础
深圳中金众联互联网科技集团
最常用数据库模型
2万台服务器
间歇泉出现别人的服务器
如何降服务器噪音
加强网络安全意识图画
网络安全架构与设计与实现
大学生网络安全教学设计
怎么同步数据库数据同步
信息系统数据库安全解决措施
美企网络安全
aceess 数据库合并
扩展maxdb数据库空间
北京什么是网络技术推广
成都栋格互联网科技有限公司
网络安全视频课程百度云
泰科网络技术廊坊有限公司
哪些数据库是免费的
数据库分库分表什么意思
百年互联网科技
台州数据网络技术有哪些
系统软件开发的项目沟通管理
混沌骑士数据库