Home » RDBMS Server » Performance Tuning » XML/XPath rewrites and plan baselines (11.2.0.4 RHEL)
XML/XPath rewrites and plan baselines [message #599848] Tue, 29 October 2013 07:54 Go to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Hi all,

The types of query I refer to in the title are of this pseudo-code ilk:

select t.column_value
from table1 o, xmltable('for $co in $data
where $co/path1=$bind1
and $co/path2=$bind2
passing o.field as "data", :b1 as "bind1", :b2 as "bind2") t
where o.field = :b3
/


They're querying a table with a (binary) xmltype with a path/domain index over this column.

As those who have had the (mis)fortune to run into these will know, the queries are extensively rewritten under the covers to access to xml via the paths supplied.

Has anyone had any success getting a baseline to work with queries like this? I was suspicious because whilst I can hint it to pick a certain access path first (leading()), the plan hashes remain the same.

I'm not sure, however, if I'm simply "doing it wrong" or it is just not possible with the level of recursive rewriting going on.

NB: I consider myself reasonably competent in applying baselines to "traditional" queries... Wink

I dont want to go into to too much more detail here as the how/whys of the inner mechanisms of these rewrites and the index structures are a bit off topic and I hazard people who will answer the question will have already had that horror Smile

Thanks in advance.

Report message to a moderator

Re: XML/XPath rewrites and plan baselines [message #599850 is a reply to message #599848] Tue, 29 October 2013 09:08 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Hi,

I think more information is needed. I am a bit confused. Did you apply the baseline from hinted query to this non-hinted one? If yes, did it not work? Can you please post the steps you followed.

Ideally it should work as documented. I learnt this technique from Jonathan Lewis' and Randolf's blogs. I remember when Jonathan stated that with cursor_sharing being set to force or similar might create a mess.

[Updated on: Tue, 29 October 2013 09:13]

Report message to a moderator

Re: XML/XPath rewrites and plan baselines [message #599860 is a reply to message #599850] Tue, 29 October 2013 10:07 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
The queries are horrendous, so I'll truncate for readability.

alter system flush shared_pool
/

--bad query
select ......
/

--good version
select /*+ leading (sys_alias...) */ .....
/


Pull the "good" and "bad" sql_ids and plans from v$sql - easy to find as the pools had been purged.

--From http://jonathanlewis.wordpress.com/2011/01/12/fake-baselines/

DECLARE
    m_clob  CLOB;
BEGIN
    SELECT st.sql_fulltext
    INTO   m_clob  
    FROM   v$sql st  
    WHERE  sql_id = '7jpk22cstfttk'    -- original (slow) query sql_id
    ;  

    dbms_output.put_line(m_clob);  

    dbms_output.put_line(  
        dbms_spm.load_plans_from_cursor_cache(  
            sql_id          => 'brz69t0254gf9',          -- hinted (fast) query sql_id
            plan_hash_value => 561479308,   -- plan hash of the hinted (fast) query
            sql_text        => m_clob,                 -- clob of the original (slow) query
            fixed           => 'YES',  
            enabled         => 'YES'  
        )
    );
END;
/


This code above returns 1 - the baseline is added and can be seen in dba_sql_plan_baselines. It is accepted, enabled...but doesnt show as working either in explain plan, auto trace or in v$sql.

Enter value for sql_stmt:  SELECT * FROM DBA_SQL_PLAN_BASELINES
SIGNATURE                     : 6735181605168109909
SQL_HANDLE                    : SQL_5d782bfc41ca5955
SQL_TEXT                      : select .......
PLAN_NAME                     : SQL_PLAN_5uy1bzj0wnqapc80ef354
CREATOR                       : SYS
ORIGIN                        : MANUAL-LOAD
PARSING_SCHEMA_NAME           : SYS
DESCRIPTION                   :
VERSION                       : 11.2.0.4.0
CREATED                       : 29-OCT-13 14.26.34.000000
LAST_MODIFIED                 : 29-OCT-13 14.26.34.000000
LAST_EXECUTED                 :
LAST_VERIFIED                 :
ENABLED                       : YES
ACCEPTED                      : YES
FIXED                         : YES
REPRODUCED                    : YES
AUTOPURGE                     : YES
OPTIMIZER_COST                : 17
MODULE                        : sqlplus@machine (TNS V1-V3)
ACTION                        :
EXECUTIONS                    : 1
ELAPSED_TIME                  : 125783
CPU_TIME                      : 124981
BUFFER_GETS                   : 1495
DISK_READS                    : 1
DIRECT_WRITES                 : 0
ROWS_PROCESSED                : 0
FETCHES                       : 1
END_OF_FETCH_COUNT            : 1



So....I appear to be able to create and load a baseline....which is "accepted", yet it is steadfastly not used by the code.

As I say, I strongly suspect I'm trying the impossible but I've struggled to find anything around this online anywhere - seems to be something just not done, which is usually a bad sign Smile

[Updated on: Tue, 29 October 2013 10:10]

Report message to a moderator

Re: XML/XPath rewrites and plan baselines [message #599865 is a reply to message #599860] Tue, 29 October 2013 10:32 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
So, this is what you did step-by-step :

1. Flushed shared pool.
2. Executed non-hinted query to generate the bad execution plan.
3. Executed hinted query to generate the good execution plan.
4. Captured sql_id and plan_hash_value for both the plans.
5. Created a baseline to associate the good plan with the bad query.
6. Checked the SQL Baseline and confirmed by looking into dba_sql_plan_baselines.
7. Executed the non-hinted query.

Observations :

1. In step 5, you did not consider the child number for the child cursor to associate with the sql id while creating the baseline.
2. After step 6, i.e. after creating the baseline, did you flush the shared pool? Else, the original child cursor could still be used.

You did not post the cursor_sharing parameter details. It is important with bind variables.

Regards,
Lalit

Report message to a moderator

Re: XML/XPath rewrites and plan baselines [message #599868 is a reply to message #599865] Tue, 29 October 2013 10:39 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      EXACT


I'd not have expected children as it's an idle instance after a pool flush and two distinct sqls.


Yes, I also (later) tried flushing the pool after creating the baselines to no avail.

Report message to a moderator

Re: XML/XPath rewrites and plan baselines [message #599877 is a reply to message #599868] Tue, 29 October 2013 14:08 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Sadly you have happened upon one of the few areas where my knowledge retreats. In fact I have never applied a basedline to any query in any system I use. Thus in this case I am a poor choice to offer advice. I am pleased you thought of me but I am useless for baselines.

I am however happy to be a part of the thread and commiserate with you if it helps in some way.

Kevin

Report message to a moderator

Re: XML/XPath rewrites and plan baselines [message #599922 is a reply to message #599848] Wed, 30 October 2013 04:12 Go to previous message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Thanks

I'm trying to work on a generic test case that can replicate the issue, unfortunately our system the data for each rows xml column is huge and absolutely packed with stuff, loads of paths indexed - it's not your 'usual' easy to create a generic mapping with fake data. Maybe if I was better at XML Smile

DDL is easy, it's inserts that are the real headache.

[Updated on: Wed, 30 October 2013 04:13]

Report message to a moderator

Previous Topic: standard SQL query to benchmark Oracle DB response time (performance)
Next Topic: Please help me on tune this query
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Feb 06 13:47:33 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.