微课sql优化(8)、统计信息收集(6)-统计信息查询
1、关于查询统计信息
dba_tablesprompt|----------------1 dba_tables--------------------------------------------+^Mcolumnownerformata10 heading'Owner'printentmapoffcolumntable_nameformata15 heading'Table_Name'printentmapoffcolumnNUM_ROWSformat999,999,999,999 heading'Num_Rows'printentmapoffcolumnblocksformat999,999,999 heading'Blocks'printentmapoffcolumnavg_row_lenformat999,999 heading'Avg_Row_len'printentmapoffcolumnLAST_ANALYZEDformata20 heading'Last_Analyzed'printentmapoffcolumnPARTITIONEDformata5 heading'Par'printentmapoffcolumnpar_keyformata10 heading'Par_Key'printentmapoffcolumnsubpar_keyformata10 heading'Subpar_Key'printentmapoffcolumn"ESTIMATE_PERCENT%"formata4 heading'ESTIMATE_PERCENT%'printentmapoffselectt.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,(selectnvl(m.column_name,'null')fromdba_part_key_columns mwherem.owner=t.OWNERandm.name=t.TABLE_NAME)"par_key",(selectnvl(sm.column_name,'null')fromdba_subpart_key_columns smwheresm.owner=t.OWNERandsm.name=t.TABLE_NAME)"subpar_key"fromdba_tables twheret.OWNER=upper('&TABLE_OWNER')andt.TABLE_NAME=upper('&TABLE_NAME')/|----------------1 dba_tables--------------------------------------------+^MOwnerprompt|----------------2 dba_tab_partitoins------------------------------------+^Mcolumnp_nameformata10 heading'p_NAME'printentmapoffselecttp.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_analyzedfromdba_tab_partitions tpwheretp.table_owner=upper('&TABLE_OWNER')andtp.TABLE_NAME=upper('&TABLE_NAME')/|----------------2 dba_tab_partitoins------------------------------------+^MOwnerprompt|----------------3 dba_tab_subpartitions---------------------------------+columnsp_nameformata20 heading'sp_NAME'printentmapoffselectsp.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_analyzedfromdba_tab_subpartitions spwheresp.table_owner=upper('&TABLE_OWNER')andsp.TABLE_NAME=upper('&TABLE_NAME')/|----------------3 dba_tab_subpartitions---------------------------------+dba_tab_columnsprompt|----------------4 dba_tab_columns---------------------------------+columnCOLUMN_NAMEformata20 heading'COLUMN_NAME'printentmapoffcolumnHISTOGRAMformata10 heading'HISTOGRAM'printentmapoffselectm.OWNER,m.TABLE_NAME,m.COLUMN_NAME,m.NUM_DISTINCT,m.HISTOGRAM,m.NUM_NULLS,m.LAST_ANALYZEDfromdba_tab_columns mwherem.OWNER=upper('&TABLE_OWNER')andm.TABLE_NAME=upper('&TABLE_NAME')ORDERBYNUM_DISTINCTDESC;|----------------4 dba_tab_columns---------------------------------+Ownerprompt|----------------5 dba_indexes---------------------------------+columnBLformat99 heading'BL'printentmapoffcolumncrformata4 heading'cr'printentmapoffcolumnIDX_KEYformata20 heading'IDX_KEY'printentmapoffcolumnuniqformata4 heading'uniq'printentmapoffcolumnINDEX_NAMEformata20 heading'INDEX_NAME'printentmapoffcolumnparformata3 heading'par'printentmapoffselectd.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,(selectm.COLUMN_NAMEfromdba_ind_columns mwherem.INDEX_OWNER=d.OWNERandm.INDEX_NAME=d.INDEX_NAMEandm.COLUMN_POSITION=1)||(select','||m.COLUMN_NAMEfromdba_ind_columns mwherem.INDEX_OWNER=d.OWNERandm.INDEX_NAME=d.INDEX_NAMEandm.COLUMN_POSITION=2)||(select','||m.COLUMN_NAMEfromdba_ind_columns mwherem.INDEX_OWNER=d.OWNERandm.INDEX_NAME=d.INDEX_NAMEandm.COLUMN_POSITION=3)||(select','||m.COLUMN_NAMEfromdba_ind_columns mwherem.INDEX_OWNER=d.OWNERandm.INDEX_NAME=d.INDEX_NAMEandm.COLUMN_POSITION=4)idx_key,d.partitioned parfromdba_indexes dwhered.table_owner=upper('&TABLE_OWNER')andd.TABLE_NAME=upper('&TABLE_NAME')orderby1,2desc/|----------------5 dba_indexes---------------------------------+Ownerprompt|----------------6 dba_tab_modifications----------------------------------+^Mselecttable_owner,table_name,partition_name p_name,subpartition_name sp_name,inserts,updates,deletesfromdba_tab_modificationswheretable_owner=upper('&TABLE_OWNER')andTABLE_NAME=upper('&TABLE_NAME')/|----------------6 dba_tab_modifications----------------------------------+^Mdba_tab_statisticsprompt|----------------7 dba_tab_statistics------------------------------------+^Mcolumnobject_typeformata15 heading'object_type'printentmapoffselectowner,table_name,object_type,stale_stats,num_rows,sample_size,trunc(sample_size/num_rows*100)estimate_percent,last_analyzedfromdba_tab_statisticswhereOWNER=upper('&TABLE_OWNER')andTABLE_NAME=upper('&TABLE_NAME')/|----------------7 dba_tab_statistics------------------------------------+^MOwner*******Oracle 10G parallel 8 HP-UX nopartitioned**********************table_nametable_size(M)index_size(M)total(M)time--------- ------------ ------------ ------- --------------------tab1488 0 488 Elapsed:00:00:27.99tab2 1,115 331 1,446 Elapsed:00:00:06.28tab3 2,019 243 2,262 Elapsed:00:00:44.30tab4 3,171 1,221 4,392 Elapsed:00:02:17.08tab5 4,756 0 4,756 Elapsed:00:05:42.85tab6 15,146 16,059 31,205Elapsed:00:29:59.14tab7 8,105 4,820 12,925Elapsed:00:26:12.52
dba_tablesprompt|----------------1 dba_tables--------------------------------------------+^Mcolumnownerformata10 heading'Owner'printentmapoffcolumntable_nameformata15 heading'Table_Name'printentmapoffcolumnNUM_ROWSformat999,999,999,999 heading'Num_Rows'printentmapoffcolumnblocksformat999,999,999 heading'Blocks'printentmapoffcolumnavg_row_lenformat999,999 heading'Avg_Row_len'printentmapoffcolumnLAST_ANALYZEDformata20 heading'Last_Analyzed'printentmapoffcolumnPARTITIONEDformata5 heading'Par'printentmapoffcolumnpar_keyformata10 heading'Par_Key'printentmapoffcolumnsubpar_keyformata10 heading'Subpar_Key'printentmapoffcolumn"ESTIMATE_PERCENT%"formata4 heading'ESTIMATE_PERCENT%'printentmapoffselectt.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,(selectnvl(m.column_name,'null')fromdba_part_key_columns mwherem.owner=t.OWNERandm.name=t.TABLE_NAME)"par_key",(selectnvl(sm.column_name,'null')fromdba_subpart_key_columns smwheresm.owner=t.OWNERandsm.name=t.TABLE_NAME)"subpar_key"fromdba_tables twheret.OWNER=upper('&TABLE_OWNER')andt.TABLE_NAME=upper('&TABLE_NAME')/|----------------1 dba_tables--------------------------------------------+^MOwnerprompt|----------------2 dba_tab_partitoins------------------------------------+^Mcolumnp_nameformata10 heading'p_NAME'printentmapoffselecttp.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_analyzedfromdba_tab_partitions tpwheretp.table_owner=upper('&TABLE_OWNER')andtp.TABLE_NAME=upper('&TABLE_NAME')/|----------------2 dba_tab_partitoins------------------------------------+^MOwnerprompt|----------------3 dba_tab_subpartitions---------------------------------+columnsp_nameformata20 heading'sp_NAME'printentmapoffselectsp.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_analyzedfromdba_tab_subpartitions spwheresp.table_owner=upper('&TABLE_OWNER')andsp.TABLE_NAME=upper('&TABLE_NAME')/|----------------3 dba_tab_subpartitions---------------------------------+dba_tab_columnsprompt|----------------4 dba_tab_columns---------------------------------+columnCOLUMN_NAMEformata20 heading'COLUMN_NAME'printentmapoffcolumnHISTOGRAMformata10 heading'HISTOGRAM'printentmapoffselectm.OWNER,m.TABLE_NAME,m.COLUMN_NAME,m.NUM_DISTINCT,m.HISTOGRAM,m.NUM_NULLS,m.LAST_ANALYZEDfromdba_tab_columns mwherem.OWNER=upper('&TABLE_OWNER')andm.TABLE_NAME=upper('&TABLE_NAME')ORDERBYNUM_DISTINCTDESC;|----------------4 dba_tab_columns---------------------------------+Ownerprompt|----------------5 dba_indexes---------------------------------+columnBLformat99 heading'BL'printentmapoffcolumncrformata4 heading'cr'printentmapoffcolumnIDX_KEYformata20 heading'IDX_KEY'printentmapoffcolumnuniqformata4 heading'uniq'printentmapoffcolumnINDEX_NAMEformata20 heading'INDEX_NAME'printentmapoffcolumnparformata3 heading'par'printentmapoffselectd.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,(selectm.COLUMN_NAMEfromdba_ind_columns mwherem.INDEX_OWNER=d.OWNERandm.INDEX_NAME=d.INDEX_NAMEandm.COLUMN_POSITION=1)||(select','||m.COLUMN_NAMEfromdba_ind_columns mwherem.INDEX_OWNER=d.OWNERandm.INDEX_NAME=d.INDEX_NAMEandm.COLUMN_POSITION=2)||(select','||m.COLUMN_NAMEfromdba_ind_columns mwherem.INDEX_OWNER=d.OWNERandm.INDEX_NAME=d.INDEX_NAMEandm.COLUMN_POSITION=3)||(select','||m.COLUMN_NAMEfromdba_ind_columns mwherem.INDEX_OWNER=d.OWNERandm.INDEX_NAME=d.INDEX_NAMEandm.COLUMN_POSITION=4)idx_key,d.partitioned parfromdba_indexes dwhered.table_owner=upper('&TABLE_OWNER')andd.TABLE_NAME=upper('&TABLE_NAME')orderby1,2desc/|----------------5 dba_indexes---------------------------------+Ownerprompt|----------------6 dba_tab_modifications----------------------------------+^Mselecttable_owner,table_name,partition_name p_name,subpartition_name sp_name,inserts,updates,deletesfromdba_tab_modificationswheretable_owner=upper('&TABLE_OWNER')andTABLE_NAME=upper('&TABLE_NAME')/|----------------6 dba_tab_modifications----------------------------------+^Mdba_tab_statisticsprompt|----------------7 dba_tab_statistics------------------------------------+^Mcolumnobject_typeformata15 heading'object_type'printentmapoffselectowner,table_name,object_type,stale_stats,num_rows,sample_size,trunc(sample_size/num_rows*100)estimate_percent,last_analyzedfromdba_tab_statisticswhereOWNER=upper('&TABLE_OWNER')andTABLE_NAME=upper('&TABLE_NAME')/|----------------7 dba_tab_statistics------------------------------------+^MOwner*******Oracle 10G parallel 8 HP-UX nopartitioned**********************table_nametable_size(M)index_size(M)total(M)time--------- ------------ ------------ ------- --------------------tab1488 0 488 Elapsed:00:00:27.99tab2 1,115 331 1,446 Elapsed:00:00:06.28tab3 2,019 243 2,262 Elapsed:00:00:44.30tab4 3,171 1,221 4,392 Elapsed:00:02:17.08tab5 4,756 0 4,756 Elapsed:00:05:42.85tab6 15,146 16,059 31,205Elapsed:00:29:59.14tab7 8,105 4,820 12,925Elapsed:00:26:12.52
声明：本站所有文章资源内容，如无特殊说明或标注，均为采集网络资源。如若本站内容侵犯了原著者的合法权益，可联系本站删除。
