| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> explain plan changes if using bind vars
I have a query that changes explain plan for the worse if I change
non-bind vars to bind vars. Discovered this yesterday, when was trying
to figure out extra dblink chatter (see reduce dblink (database link)
chatter thread):
http://groups-beta.google.com/group/comp.databases.oracle.server/browse_thread/thread/d0d99e62ede0f249/e65824577f7747e8?lnk=st&q=author:netcomrade&rnum=1&hl=en#e65824577f7747e8
When not using bind cars the query is fine, as it executes a remote query, and then joins in the results with local objects. If I take the same query, and replace variables with bind vars, explain plan changes for the worse (starts making many inefficient remote calls). There are no special statistics collected on any of the tables, just table and index stats (done via dbms_stats.gather_table_stats (ownname=>'',tabname=>'',estimate_percent=>20,cascade=>true);
Any idea on what we can do?
One thing that seems to work, but doesn't seem like an optimal
solution, is not using bind vars, but setting cursor_sharing=force
(see below)
non-bind var result:
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=7 Bytes=2114) 1 0 SORT (ORDER BY) (Cost=6 Card=7 Bytes=2114)
2 1 NESTED LOOPS (Cost=4 Card=7 Bytes=2114) 3 2 NESTED LOOPS (Cost=3 Card=7 Bytes=1827) 4 3 NESTED LOOPS (Cost=2 Card=7 Bytes=1645) 5 4 NESTED LOOPS (Cost=1 Card=7 Bytes=1260) 6 5 VIEW (Cost=5 Card=100 Bytes=15600) 7 6 REMOTE*
8 5 INDEX (UNIQUE SCAN) OF 'CLUBHOUSE_FRIENDS_PK' (U
NIQUE)
9 4 TABLE ACCESS (BY INDEX ROWID) OF 'MEMBERS' (Cost=1
Card=4760301 Bytes=261816555)
10 9 INDEX (UNIQUE SCAN) OF 'MEMBERS_PK' (UNIQUE)
11 3 TABLE ACCESS (BY INDEX ROWID) OF 'MEMBER_SCORE' (Cos
t=1 Card=5204708 Bytes=135322408)
12 11 INDEX (UNIQUE SCAN) OF 'MEMBER_SCORE_PK' (UNIQUE)
13 2 TABLE ACCESS (BY INDEX ROWID) OF 'MEMBER_SCORE_THEME'
(Cost=1 Card=29 Bytes=1189)
14 13 INDEX (UNIQUE SCAN) OF 'MEMBER_SCORE_THEME_PK' (UNIQ
UE)
7 SERIAL_FROM_REMOTE SELECT /*+ */
"A3"."POSTER_ID",RANK() OVER (
PARTITION BY "A3"."POSTER_ID"
ORDER BY "A3"
."POST_ID" DESC
),"A3"."POST_ID","A4"."TOPIC
_ID","A4"."TOPIC_TITLE","A2"."POST_SUBJECT"
FROM "PHPBB_TOPICS"
"A4","PHPBB_POSTS" "A3",
"PHPBB_POSTS_TEXT_CLOB"
"A2","TSN_CLUBHOUSE_
FRIENDS" "A1" WHERE
"A3"."TOPIC_ID"="A4"."TO
PIC_ID" AND
"A2"."POST_ID"="A3"."POST_ID" AN
D "A1"."FRIEND_ID"="A3"."POSTER_ID"
AND "A1"
."M_ID"=25
Query with bind var:
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=4 Bytes=1208
)
1 0 SORT (ORDER BY) (Cost=11 Card=4 Bytes=1208)
2 1 NESTED LOOPS (Cost=10 Card=4 Bytes=1208)
3 2 NESTED LOOPS (Cost=9 Card=4 Bytes=1044)
4 3 NESTED LOOPS (Cost=8 Card=4 Bytes=940)
5 4 NESTED LOOPS (Cost=7 Card=4 Bytes=720)
6 5 VIEW (Cost=6 Card=9 Bytes=1404)
7 6 WINDOW (SORT PUSHED RANK)
8 7 NESTED LOOPS (Cost=4 Card=9 Bytes=1503)
9 8 NESTED LOOPS (Cost=3 Card=9 Bytes=1224)
10 9 NESTED LOOPS (Cost=2 Card=9 Bytes=639)
11 10 REMOTE* (Cost=1 Card=5 Bytes=120)
PHPBB.WO
RLD
12 10 REMOTE* (Cost=1 Card=28 Bytes=1316)PHPBB.WO RLD
13 9 REMOTE*
14 8 REMOTE*
15 5 INDEX (UNIQUE SCAN) OF 'CLUBHOUSE_FRIENDS_PK' (U
NIQUE)
16 4 TABLE ACCESS (BY INDEX ROWID) OF 'MEMBERS' (Cost=1
Card=4760301 Bytes=261816555)
17 16 INDEX (UNIQUE SCAN) OF 'MEMBERS_PK' (UNIQUE)
18 3 TABLE ACCESS (BY INDEX ROWID) OF 'MEMBER_SCORE' (Cos
t=1 Card=5204708 Bytes=135322408)
19 18 INDEX (UNIQUE SCAN) OF 'MEMBER_SCORE_PK' (UNIQUE)
20 2 TABLE ACCESS (BY INDEX ROWID) OF 'MEMBER_SCORE_THEME'
(Cost=1 Card=29 Bytes=1189)
21 20 INDEX (UNIQUE SCAN) OF 'MEMBER_SCORE_THEME_PK' (UNIQ
UE)
11 SERIAL_FROM_REMOTE SELECT "M_ID","FRIEND_ID" FROM
"TSN_CLUBHOUS
E_FRIENDS" "A1" WHERE
"PHPBB_POSTS" "A3" WHERE
:1="POSTER_ID"
13 SERIAL_FROM_REMOTE SELECT "TOPIC_ID","TOPIC_TITLE"
FROM "PHPBB_
TOPICS" "A4" WHERE :1="TOPIC_ID"
14 SERIAL_FROM_REMOTE SELECT "POST_ID","POST_SUBJECT"
FROM "PHPBB_
POSTS_TEXT_CLOB" "A2" WHERE
SELECT '', m.name, m.id, NVL(NVL(posts.post_subject, posts.topic_title), '-'), posts.post_id, mst.rank
FROM members m, clubhouse_friends cf, member_score ms, member_score_theme mst,
(select p.poster_id poster_id, rank() over (partition by p.poster_id order by p.post_id desc) post_num,
p.post_id post_id,
t.topic_id topic_id,
t.topic_title topic_title,
ptc.post_subject post_subject
from phpbb_topics_at_phpbb.world t,
phpbb_posts_at_phpbb.world p,
phpbb_posts_text_clob_at_phpbb.world ptc,
tsn_clubhouse_friends_at_phpbb.world cf
where p.topic_id = t.topic_id
and ptc.post_id = p.post_id
and cf.friend_id = p.poster_id
and cf.m_id = :var1
) posts
WHERE m.id = cf.friend_id
AND posts.post_num = 1
AND cf.m_id = :var2
AND cf.friend_id = posts.poster_id(+)
AND mst.score = ms.total_score
AND mst.theme_id = 1
AND ms.member_id = cf.friend_id
ORDER BY LOWER(m.name)
.......
We use Oracle 8.1.7.4 and 9.2.0.5 on Solaris 2.7 boxes
remove NSPAM to email
Received on Tue Aug 02 2005 - 11:40:57 CDT
 |
 |