How to view results from 'Analyze table...' command?

[thg]

marist89,

I just realized that the 'explain plan' uses the statistics from 'analyze table' & 'analyze indexes'. Does that mean if I don't ran the 'analyze...' commands frequently, my query will become very slow?


Here is one of the 'explain plan' results from my query, is my query optimized?

Below is a simplified version of my query, basically it scans through the table to find entries that are before certain date. I've defined a cluster index on (id, update_date), but it seems that it still perform full table scan on my 'security_ts_coupon' table

// sample query

select t1.id, t1.update_date from security_ts_coupon t1 where t1.id in (12,23,43...) and t1.update_date < ?;

// 'explain plan' result


ID OPERATION OPTIONS OBJECT_NAME OPTIMIZER COST CARDINALITY
--------- ------------------------------ -------------------- -------------------- -------------------- --------- -----------
0 SELECT STATEMENT CHOOSE 12 1
1 FILTER
2 NESTED LOOPS OUTER 12 1
3 NESTED LOOPS OUTER 9 1
4 NESTED LOOPS OUTER 7 1
5 NESTED LOOPS OUTER 6 1
6 NESTED LOOPS OUTER 5 1
7 NESTED LOOPS OUTER 4 1
8 NESTED LOOPS OUTER 3 1
9 TABLE ACCESS BY INDEX ROWID SECURITY ANALYZED 2 1
10 INDEX UNIQUE SCAN PK_SECURITY ANALYZED 1 1
11 TABLE ACCESS BY INDEX ROWID SECURITY_MORTGAGE ANALYZED 1 1264
12 INDEX UNIQUE SCAN PK_SECURITY_MORTGAGE ANALYZED 1264
13 TABLE ACCESS BY INDEX ROWID SECURITY_BOND_SWAP ANALYZED 1 1296
14 INDEX UNIQUE SCAN PK_SECURITY_BOND_SWA ANALYZED 1296
P

15 TABLE ACCESS BY INDEX ROWID SECURITY_ARM ANALYZED 1 111
16 INDEX UNIQUE SCAN PK_SECURITY_ARM ANALYZED 111
17 TABLE ACCESS BY INDEX ROWID SECURITY_OPTION ANALYZED 1 93
18 INDEX UNIQUE SCAN PK_SECURITY_OPTION ANALYZED 93
19 TABLE ACCESS FULL SECURITY_TS_RATING ANALYZED 1 1
20 TABLE ACCESS FULL SECURITY_TS_COUPON ANALYZED 2 1429
21 TABLE ACCESS BY INDEX ROWID SECURITY_TS_FACTOR ANALYZED 3 1474
22 INDEX RANGE SCAN PK_SECURITY_TS_FACTO ANALYZED 2 1474
R

23 SORT AGGREGATE 1
24 INDEX FAST FULL SCAN PK_SECURITY_TS_FACTO ANALYZED 2 2
R

25 SORT AGGREGATE 1
26 INDEX FAST FULL SCAN PK_SECURITY_TS_COUPO ANALYZED 2 2
N


27 rows selected.