[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE delete_all_expstms(p_commit IN BOOLEAN DEFAULT FALSE) IS
BEGIN
-- delete all the explan plans which have been previously generated
-- by this utility
EXECUTE IMMEDIATE 'DELETE FROM '||g_explain_table||' pt WHERE pt.statement_id LIKE '''||
g_explain_sql_statement_text || '%''';
g_error_text := g_error_std_text||'delete_all_expstms: '||
TO_CHAR(SQLCODE)||': '||SQLERRM;
END delete_all_expstms;
EXECUTE IMMEDIATE 'DELETE FROM '||
g_stat_table_name||
' st WHERE st.statid = :statid' USING p_statid;
'SELECT di.table_owner table_owner, '||
'di.table_name table_name '||
'FROM '||g_explain_table||' pt, dba_indexes di '||
'WHERE pt.statement_id LIKE ''exp%'' '||
'AND pt.object_owner NOT IN (''SYS'',''SYSTEM'') '||
'AND pt.object_type IN (''UNIQUE'',''NON-UNIQUE'') '||
'AND di.index_name = pt.object_name '||
'AND di.owner = pt.object_owner '||
'UNION '||
'SELECT dt.owner table_owner, '||
'dt.table_name table_name '||
'FROM '||g_explain_table||' pt, dba_tables dt '||
'WHERE pt.statement_id LIKE ''exp%'' '||
'AND pt.object_type IS NULL '||
'AND dt.table_name = pt.object_name '||
'AND dt.owner = pt.object_owner '||
'AND pt.object_owner NOT IN (''SYS'',''SYSTEM'') '||
'ORDER BY 1,2';
EXECUTE IMMEDIATE 'SELECT 1 FROM SYS.DUAL WHERE EXISTS (SELECT 1 FROM '||
g_explain_table||')' INTO l_dummy;
OPEN l_cursor FOR 'SELECT LPAD('' '',2*(LEVEL-1))||operation||'||
'DECODE(options, NULL,'''','' ''||'||
'options)||'' ''||object_name||'' (cost=''||'||
'cost||'', card=''||cardinality||'', bytes=''||bytes'||
'||'')'' exp_line '||
'FROM '||g_explain_table||' START WITH id=0 AND '||
'statement_id = :c_statement_id '||
'CONNECT BY PRIOR id = parent_id '||
'AND PRIOR NVL(statement_id, '' '') = NVL(statement_id, '' '') '||
'AND PRIOR timestamp <= timestamp'
USING g_explain_sql_statement_text||g_explain_sql_counter;
IF INSTR(l_text, 'SELECT') = 1
OR INSTR(l_text, 'INSERT') = 1
OR INSTR(l_text, 'UPDATE') = 1
OR INSTR(l_text, 'DELETE') = 1 THEN
RETURN (TRUE);
OPEN l_cursor FOR 'SELECT banner FROM v$version';
'SELECT name pname,'||
'value pvalue'||
',decode(name,'||
'''_sort_elimination_cost_ratio'', decode(value,''5'',''OK'',''RECOMMEND => 5''),'||
'''_optimizer_mode_force'', decode(value,''TRUE'',''OK'', ''RECOMMEND => TRUE''),'||
'''_fast_full_scan_enabled'', decode(value,''FALSE'',''OK'', ''RECOMMEND => FALSE''),'||
'''_ordered_nested_loop'', decode(value,''TRUE'',''OK'', ''RECOMMEND => TRUE''),'||
'''_complex_view_merging'', decode(value,''TRUE'',''OK'', ''RECOMMEND => TRUE''),'||
'''_push_join_predicate'', decode(value,''TRUE'',''OK'', ''RECOMMEND => TRUE''),'||
'''_use_column_stats_for_function'', decode(value,''TRUE'',''OK'', ''RECOMMEND => TRUE''),'||
'''_push_join_union_view'', decode(value,''TRUE'',''OK'', ''RECOMMEND => TRUE''),'||
'''_like_with_bind_as_equality'', decode(value,''TRUE'',''OK'', ''RECOMMEND => TRUE''),'||
'''_or_expand_nvl_predicate'', decode(value,''TRUE'',''OK'', ''RECOMMEND => TRUE''),'||
'''_table_scan_cost_plus_one'', decode(value,''TRUE'',''OK'', ''RECOMMEND => TRUE''),'||
'''_optimizer_undo_changes'', decode(value,''FALSE'',''OK'', ''RECOMMEND => FALSE''),'||
'''db_file_multiblock_read_count'', decode(value,''8'',''OK'', ''RECOMMEND => 8''),'||
'''optimizer_max_permutations'', decode(value,''79000'',''OK'', ''RECOMMEND => 79000''),'||
'''optimizer_mode'', decode(value,''CHOOSE'',''OK'', ''RECOMMEND => CHOOSE''),'||
'''optimizer_percent_parallel'', decode(value,''0'',''OK'', ''RECOMMEND => 0''),'||
'''optimizer_features_enable'', decode(value,''8.1.6'',''OK'', ''RECOMMEND => 8.1.6''),'||
'''query_rewrite_enabled'', decode(value,''TRUE'',''OK'', ''RECOMMEND => TRUE''),'||
'''compatible'', decode(value,''8.1.6'',''OK'', ''RECOMMEND => 8.1.6''),'||
'''optimizer_index_caching'', decode(value,''0'',''OK'', ''RECOMMEND => 0''),'||
'''optimizer_index_cost_adj'', decode(value,''100'',''OK'', ''RECOMMEND => 100''),'||
'''hash_area_size'', DECODE((SELECT TO_CHAR(TO_NUMBER(v1.value) * 2) '||
'FROM v$parameter v1 '||
'WHERE v1.name = ''sort_area_size''),'||
'value,''OK'',''RECOMMEND => (2*sort area size)''),'||
'''sort_area_size'', DECODE((SELECT ''Y'' '||
'FROM SYS.DUAL '||
'WHERE TO_NUMBER(value) '||
'BETWEEN 256000 AND 2000000), '||
'''Y'',''OK'',''RECOMMEND => ( >= 256k <= 2M)''), '||
''' '') pdvalue '||
'FROM v$parameter '||
'WHERE name IN (''_sort_elimination_cost_ratio'','||
'''_optimizer_mode_force'','||
'''_fast_full_scan_enabled'','||
'''_ordered_nested_loop'','||
'''_complex_view_merging'','||
'''_push_join_predicate'','||
'''_use_column_stats_for_function'','||
'''_push_join_union_view'','||
'''_like_with_bind_as_equality'','||
'''_or_expand_nvl_predicate'','||
'''_table_scan_cost_plus_one'','||
'''_optimizer_undo_changes'','||
'''db_file_multiblock_read_count'','||
'''optimizer_max_permutations'','||
'''optimizer_mode'','||
'''optimizer_percent_parallel'','||
'''optimizer_features_enable'','||
'''query_rewrite_enabled'','||
'''compatible'','||
'''db_block_size'','||
'''optimizer_index_caching'','||
'''optimizer_index_cost_adj'','||
'''timed_statistics'','||
'''sort_area_size'','||
'''sort_multi_block_read_count'','||
'''hash_join_enabled'','||
'''hash_area_size'')'||
' ORDER BY 1';
SELECT db.table_name table_name,
TO_CHAR(db.num_rows,999999999999) num_rows,
TO_CHAR(db.blocks,999999) blocks,
TO_CHAR(db.empty_blocks,9999999999) empty_blocks,
TO_CHAR(db.avg_row_len,99999999999) arl,
TO_CHAR(db.chain_cnt,9999999) chcnt,
TO_CHAR(db.last_analyzed, 'DD-MON-YYYY HH24:MI:SS') la
FROM dba_tables db
WHERE db.table_name = c_table_name
AND db.owner = c_owner;
SELECT di.index_name index_name,
TO_CHAR(di.num_rows,9999999999) num_rows,
TO_CHAR(di.distinct_keys,9999999999) dk,
TO_CHAR((1/di.distinct_keys),9.99999999) s,
TO_CHAR((di.num_rows/di.distinct_keys),9999999999) ec,
TO_CHAR(di.leaf_blocks,999999) lb,
TO_CHAR(di.clustering_factor,9999999) cf,
TO_CHAR(di.blevel,999999) bl,
TO_CHAR(di.last_analyzed, 'DD/MM/YYYY HH24:MI:SS') la,
di.avg_leaf_blocks_per_key albpk,
di.avg_data_blocks_per_key adbpk
FROM dba_indexes di
WHERE di.table_name = c_table_name
AND di.table_owner = c_owner
AND di.owner = c_owner
AND di.num_rows > 0
ORDER BY DECODE(di.uniqueness,'UNIQUE',1,2), 1;
SELECT c.index_name index_name,
i.uniqueness uniqueness,
SUBSTR(c.column_name,1,30) column_name,
c.column_position column_position
FROM dba_ind_columns c,
dba_indexes i
WHERE i.table_name = c_table_name
AND i.table_owner = c_owner
AND i.owner = c_owner
AND c.index_name = i.index_name
AND c.index_owner = i.owner
ORDER BY c.table_name,
DECODE(i.uniqueness,'UNIQUE',1,2),
c.index_name,
c.column_position ASC;
'SELECT dc.column_name column_name,'||
' TO_CHAR(dc.num_distinct, 999999999999) nd,'||
' TO_CHAR(DECODE(NVL(dc.num_distinct,0), 0, 0, 1 / dc.num_distinct),'||
' ''9.999999999'') es,'||
' TO_CHAR(DECODE(NVL(dc.num_distinct,0), 0, 0, CEIL(dt.num_rows/dc.num_distinct)),'||
' ''99999999999'') ec,'||
' TO_CHAR(dc.num_nulls, 999999999999) nn,'||
' TO_CHAR(dc.density,''9.99999'') d,'||
' dc.avg_col_len acl,'||
' DECODE((SELECT 1'||
' FROM dba_histograms dh'||
' WHERE dh.owner = dt.owner'||
' AND dh.table_name = dt.table_name'||
' AND dh.column_name = dc.column_name'||
' AND dh.endpoint_number NOT IN (0,1)'||
' AND ROWNUM < 2),1,''Y'',''N'') h '||
'FROM user_synonyms syn, dba_tab_columns dc, dba_tables dt '||
'WHERE syn.synonym_name = :c_table_name' ||
'AND syn.table_owner = :c_owner ' ||
'AND dc.table_name = syn.table_name ' ||
'AND dc.owner = syn.table_owner ' ||
'AND dc.table_name = dt.table_name '||
'AND dc.num_distinct > 0 '||
'AND dt.table_name = :c_table_name '||
'AND dt.owner = :c_owner '||
'AND dc.owner = dt.owner '||
'ORDER BY dc.column_id' USING g_table_name_table(i),g_table_owner_table(i);
g_table_owner_table.DELETE;
g_table_owner_status_table.DELETE;
g_table_name_table.DELETE;
delete_all_expstms(p_commit => TRUE);