This content has been marked as final. Show 7 replies
pdp0617 wrote:do EXPLAIN PLAN show index being used?
I have enabled the STA to run twice a week, while we are going live with a new application in a new database. I want to insure we have good performance as the load goes up. I was not involved in the original design, which there not not much of, I've discovered. Since we are licensed for it, it seemed like a safe thing to turn on.
The STA ran last night and the report tells me it looked at 736 SQLs with findings, 143 without findings, and skipped 0.
What puzzles me is that, in the "Statistics Finding Summary", it's reporting stale stats on some tables which I know are up to date, as I ran stats on them yesterday and there were no updates to them.
Worse, in the "Index Finding Summary", it reports indexes needed that already do, in fact, exist.
Is there some reason for this? Is there some way to get the STA to be aware of these and avoid the erroneous findings so that I can concentrate on real ones?
Thanks in advance for any suggestions.
do as below so we can know complete Oracle version & OS name.
Post via COPY & PASTE complete results of
SELECT * from v$version;
pdp, I do not use the tool in question, but for the " in the "Index Finding Summary", it reports indexes needed that already do, in fact, exist." issue have you verified that the indexes in question are 1 - usable and 2 - visible?1 person found this helpful
The Oracle performance and tuning features always have to be taken with a grain of salt and sometimes the result is just flat-out wrong.
HTH -- Mark D Powell --
Thanks for your interest, yes, the index is being used . Here is the evidence that you were asking about.
It has existed for many weeks, now as the info below will show.
Here is the STA run info, the run was from 2011-07-28 at 11 PM:
Advisory Type Name Description User Status Start Time Duration (seconds) Expires In (days)
SQL Tuning Advisor SYS_AUTO_SQL_TUNING_TASK Automatic SQL Tuning Task SYS COMPLETED Jul 28, 2011 11:00:03 PM 865 UNLIMITED
Here is part of the Index Finding Summary
Index Finding Summary
Table Name Schema References Index Columns
EINSTANCE_ARCH EINTAKE 74 GLOBAL_PATIENT_ID
Drilling into that table, we see the following SQL_ID listed first, "g9uf2kmyvc66y" which is just one of many.
Here is 1 if the sqls, which use the "global_patient_id" as a predicate
(with the many columns that are actually selected not listed as they are not germain:
FROM (SELECT '0' AS locked,
(... a whole lot of columns selected)
FROM einstance_arch a
LEFT OUTER JOIN
ON a.global_patient_id = d.patient_id
LEFT OUTER JOIN
ON a.instance_id = e.einstance_id,
WHERE a.current_state = b.inst_state_id
AND a.current_state_id != 15
AND ( a.global_patient_id = 496 )
ORDER BY a.instance_id ASC)
WHERE ROWNUM <= :1
Here's an explain plan extracted from Toad, the index in question is identified by ==>:
SELECT STATEMENT ALL_ROWS Cost: 19 Bytes: 4,528 Cardinality: 4
1 INDEX UNIQUE SCAN INDEX (UNIQUE) EINTAKE.STATES_PK Cost: 0 Bytes: 14 Cardinality: 1
3 SORT AGGREGATE Bytes: 6 Cardinality: 1
2 INDEX RANGE SCAN INDEX EINTAKE.DOCUMENT_ATTR_LNK_INST_ID_IDX2 Cost: 1 Bytes: 6 Cardinality: 1
18 COUNT STOPKEY
17 VIEW EINTAKE. Cost: 19 Bytes: 4,528 Cardinality: 4
16 SORT ORDER BY STOPKEY Cost: 19 Bytes: 1,544 Cardinality: 4
15 NESTED LOOPS
13 NESTED LOOPS Cost: 18 Bytes: 1,544 Cardinality: 4
11 NESTED LOOPS OUTER Cost: 10 Bytes: 1,296 Cardinality: 4
8 HASH JOIN OUTER Cost: 9 Bytes: 1,228 Cardinality: 4
5 TABLE ACCESS BY INDEX ROWID TABLE EINTAKE.EINSTANCE_ARCH Cost: 6 Bytes: 1,152 Cardinality: 4
==> 4 INDEX RANGE SCAN INDEX EINTAKE.EINSTANCE_ARCH_GLBL_PT_ID_IDX3 Cost: 1 Cardinality: 5
7 TABLE ACCESS BY INDEX ROWID TABLE EINTAKE.PATIENT Cost: 2 Bytes: 19 Cardinality: 1
6 INDEX UNIQUE SCAN INDEX (UNIQUE) EINTAKE.PATIENT_PK Cost: 1 Cardinality: 1
10 TABLE ACCESS BY INDEX ROWID TABLE EINTAKE.REFERRAL Cost: 1 Bytes: 17 Cardinality: 1
9 INDEX RANGE SCAN INDEX EINTAKE.REFERRAL_EINSTANCE_ID Cost: 0 Cardinality: 1
12 INDEX UNIQUE SCAN INDEX (UNIQUE) EINTAKE.EINSTANCE_STATES_ARCH_PK Cost: 1 Cardinality: 1
14 TABLE ACCESS BY INDEX ROWID TABLE EINTAKE.EINSTANCE_STATES_ARCH Cost: 2 Bytes: 62 Cardinality: 1
Here is the DDL info on the index in question:
select object_name, created, last_ddl_time from user_objects where object_name = 'EINSTANCE_ARCH_GLBL_PT_ID_IDX3';
OBJECT_NAME CREATED LAST_DDL_TIME
------------------------------ --------------------- ---------------------
EINSTANCE_ARCH_GLBL_PT_ID_IDX3 2011 07 11 11:22:36 2011 07 11 11:22:36
1 row selected.
Is the suggested index match exactly the existing index?
I mean the order of the columns in the index, are they at the same positions in the existing index and in the suggested one?
Yes, it is absolutely the same column in the index as in the query.
Here is the predicate: AND ( a.global_patient_id = 496 )
And here is the index ddl without all the storage parm, etc.:
CREATE INDEX EINTAKE.EINSTANCE_ARCH_GLBL_PT_ID_IDX3 ON EINTAKE.EINSTANCE_ARCH (GLOBAL_PATIENT_ID)
As I tried to make clear, I can go in now and find the SQL its issuing recommendations on and see that the actual execution plan is using it. I use Oracle Ent. Mgr. Grid Control to do that.
Here is my theory, but I'm not sure how to prove it. The tuning advisor is looking into the AWR and finding past runs, still, from when the index was not present, and plans from those historic SQLs that used different access methods.
I'd like to think there is some way, somehow to tell it to not look at old plans, but only into the current SQL cached in memory.
But, I dont know how to prove that or how to make it run and give current recommendations.
OK, for anyone that respondeded to this post, I believe I've found the answer. I was looking at the run from last night and noticed a section that I had not looked at or used before.
When you click on the SQL tuning run of interest, you are taken to a page called "Automatic SQL Tuning Result Summary". There are several sections to this page. I had been going to the bottom when it opened, to look at the "Index Finding Summary".
But from the top, there is "Task Status", then "Summary Time Period", "Overall Task Statistics", and "Profile Effect Statistics".
It appears that it is the "Summary Time Period" that is the key I was looking for here. There are 6 setting you can choose. The default is "Last 31 Days".
So my suspicion that it was looking at a much longer time period then since the previous run was correct. If I pick "Last 24 Hours", for example, it only recommends indexes that currently do not exist.
Thanks for all who responded to this post.
I forgot to mark this as answered, which I believe it now is.