Why select from the mam_assets table twice for a.id?
Code:
FROM MAM_ASSETS A
11 WHERE ((A.ACL_ID=65536) AND
12 (A.ASSET_GROUP_XID=120) AND
13 (A.IS_CURRENT_VERSION=1) AND
14 A.ID IN ( SELECT A.ID ASSET_ID FROM MAM_ASSETS A
15 WHERE (A.ID IN (SELECT SV65539.ID FROM MAM_ATTR_STRING_VALUES SV65539
Why select from the mam_assets table twice for a.id?
1. I don't have toad. Any other way, I can find out where the time is consumed.
2. There is a UI based query wizard at the application level that generates this query. Even I spotted the same thing but I don't have any control on it.
3. I checked the health of indexes involved using 'analyze index validate structure'. They are good.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
I have not used it ever. Would you please guide me?
Two pieces of code...
1- target sql statement. my_target_sql.sql in this example.
2- trace shell script. my_trace_script.sh in this example - see below.
Once you have both of them in place.
a) set your oracle_home
b) set your oracle_sid
c) nohup my_trace_script.sh > my_trace.out &
d) tkprof trace file on user_dump_destination with sys=no option
Build your my_trace_script.sh including code below...
sqlplus -s / <
set linesize 180
set pagesize 0
set timing on
spool my_trace_script.out
select instance_name, host_name from v\$instance;
drop table PLAN_TABLE;
@?/rdbms/admin/utlxplan
alter session set timed_statistics = true;
alter session set sql_trace = true;
set autotrace on explain
alter session set events '10046 trace name context forever, level 12';
-- execute sql to be traced like...
@my_target_sql.sql
--
alter session set sql_trace = false;
alter session set timed_statistics = false;
spool off
EOF
Last but not least, if my_target_sql.sql is expected to return a large number of rows you may want to wrap it up on a count shell like...
select count(*) from(
--put your sql statement here
);
...after all you are looking for trace/execution plan, not for the actual output.
Last edited by PAVB; 04-30-2010 at 09:05 AM.
Reason: typo
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.