Frequently asked question
SQL*Plus example:
set timing on
exec dbms_stats.gather_table_stats('BEN','ROADS_1M')
Results :
PL/SQL procedure successfully completed.
43:36:39.79
SQL to identify the poorly performing query :
SELECT se.sid,se.username,sa.sql_text
FROM v$session se, v$sqlarea sa
WHERE se.sql_address=sa.address
AND se.sql_hash_value=sa.hash_value;
Poorly Performing Query :
SELECT /*+ no_parallel(b) no_parallel_index(b)
dbms_stats cursor_sharing_exact use_weak_name_resl
dynamic_sampling(0) no_monitoring
*/ dbms_rowid.rowid_block_number(b.rowid)
FROM
(SELECT s.sp_id, s.gx, s.gy, row_number()
OVER
( Partition BY s.sp_id ORDER BY s.gx, s.gy) rncol
FROM BEN.S57_IDX$ s) sp, BEN.ROADS_1M b
WHERE rncol = 1 AND b.rowid = sp.sp_id
ORDER BY sp.gx, sp.gy, sp.sp_id
Article ID: 000013348
Get help from ArcGIS experts
Start chatting now