Could you assist me in finding the problematic areas in the attached query?
Printable View
Could you assist me in finding the problematic areas in the attached query?
Do you have Toad? Run it through a tuning lab.
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
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.
Any other comments, I would be grateful.
trace the session, see where time is being taken - stop guessing
validating the structure of an index is pointless
Hi
Whats the output of
Code:select ATTRIBUTE_XID,count(*)
from MAM_ATTR_STRING_VALUES
group by ATTRIBUTE_XID
select count(*)
from MAM_ATTR_STRING_VALUES
I executed this query in a a session and traced the session.
Attached is the formatted output after tkprof.
Mainly time is taken by CPU.
How can we reduce the execution of this query? It's teasing me a lot now.
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.