Let's first look at the performance of the table without analysis without statistical data.
SQL> ALTER session set cursor_sharing = similar; session altered.
SQL> SELECT NAME, VALUE from V $ sysstat where name Like '% parse%; name value ----------------------------- -------------------------------------------- Parse Time CPU 4948 Parse Time ELAPSED 4468 PARSE COUNT (TOTAL) 170148 PARSE COUNT (HARD) PARSE COUNT (FAILURES) 80 SQL> SELECT Count (*) from T Where Object_ID = 1000; Count (*) ------ ---- 0 SQL> SELECT NAME, VALUE FROM V $ sysstat where name like '% parse%'; name value ------------------------ ---------------------------------------------------------------------------------- PARSE TIME CPU 4948 PARSE Time Elapsed 4468 Parse Count (Total) 170172 Parse Count (HARD) 1620 Parse Count (Failures) 80 SQL>
/ Name Value ----------------------------------------------- -------------------------- Parse Time CPU 4948 Parse Time Elapsed 4468 Parse Count (Total) 170176 Parse Count (HARD) 1620 Parse Count Failures) 80 SQL> SELECT Count (*) from T where object_id = 1000; count (*) ---------- 0 SQL> SESSSTAT WHERE NAME LIKE '% PARSE%' Name Value ----------------------------------------------- ----------------- ---------- Parse Time CPU 4948 Parse Time Elapsed 4468 Parse Count (Total) 170178 Parse Count (HARD) 1620 Parse Count (Failures) 80 SQL> Select Count (*) from T Where Object_ID = 1001; Count (*) ---------- 0 SQL> SELECT Name, Value from V $ sysstat where name like '% parse%;
Name Value ------------------------------------------------------------------------------------------------ ---------------- ---------- Parse Time CPU 4948 Parse Time Elapsed 4468 Parse Count (Total) 170180 Parse Count (HARD) 1620 (even if Object_ID Variation still has no hard analysis) PARSE COUNT (Failures) 80 Let's look at the performance after analyzing tables and field information
SQL> Analyze Table T1 Compute Statistics For Table for Columns Object_ID; Table Analyzed. SQL> SELECT NAME, VALUE FROM V $ SYSSTAT WHERE NAME LIKE '% PARSE%'; Name Value ------------- -------------------------------------------------- - ---------- PARSE TIME CPU 4973 Parse Time Elapsed 4495 Parse Count (Total) 170982 Parse Count (HARD) 80 SQL> Select Count (*) from T1 Where Object_ID = 5000 Count (*) ---------- 0 SQL> SELECT NAME, VALUE FROM V $ sysstat where name Like '% PARSE%'; name value ------------- -------------------------------------------------- - ----- ---- Parse Time CPU 4973 Parse Time Elapsed 4495 Parse Count (Total) 170984 Parse Count (HARD) 1641 Parse Count (Failures) 80 SQL> Select Count (*) from T1 Where Object_ID = 5000; Count (*) - --------- 0 SQL> SELECT NAME, VALUE from V $ sysstat where name limited '% parse%;
Name Value ------------------------------------------------------------------------------------------------ -------------------------- Parse Time CPU 4973 Parse Time Elapsed 4495 Parse Count (Total) 171008 Parse Count (HARD) 1641 (Repeated execution Variation) Parse Count (Failures) 80 SQL> Select Count (*) from T1 Where Object_ID = 5001; Count (*) ---------- 0 SQL> SESST NAME, VALUE FROM V $ sysstat where name Like '% PARSE%'; Name Value ---------------------------- ------------------------ ---------- Parse Time CPU 4973 Parse Time Elapsed 4495 parse count (total) 171010 parse count (hard) 1642 (when object_id changes produce a hard analysis) parse count (failures) 80 SQL> SQL> select sql_text, child_number from v $ sql where sql_text like 'select count (*) From t1 where% '; sql_text ---------------------------------------------------------------------------------------------------- ------------------------------------------------ Select count (*) from t1 where object_id =: "sys_b_0" 0 Select count (*) from t1 where object_id =: "sys_b_0" 1
