Mauro Pagano's Blog

Truncated CTAS text and SQL Plan Baselines


This is probably not earth-shattering (not that I ever blog earth-shattering things anyway) for many but it does answer a question I got today about “Do you think a truncated SQL text for CTAS affects SPM ability to give me the desired plan?”.

SQL text for CTAS is truncated as result of bug 17982832 (sister bugs 18705302 and20308798 affect 10046 and AWR respectively) but does this affect SPM? Can SPM match on the truncated text? Or maybe can SPM see the whole text and match on the original SQL? Those are the questions I wanted to answer.

As usual a test is worth a thousand expert opinions so here it goes:

SQL> create table test_shane as select * from dba_objects;
SQL> select sql_id, exact_matching_signature, sql_text from v$sql where sql_id = '30ywyzwvy6cqy';
------------- ---------------------------- ------------------------
30ywyzwvy6cqy	       6988945084141899327 create table test_sh

SQL text is truncated from above output and the signature (exact since baselines don’t do force_matching) is 6988945084141899327. Let’s create a baseline now

SQL> var n1 number
SQL> exec :n1 := dbms_spm.load_plans_from_cursor_cache('30ywyzwvy6cqy');
SQL> print :n1
SQL> select signature, sql_text from dba_sql_plan_baselines;
------------------------- ---------------------------------------
     15291506816473784520 create table test_sh

The baseline has been created with signature 15291506816473784520 which is different than the one from V$SQL so it sounds like the baseline used the wrong text to create the signature (or V$SQL was wrong and SPM was smarter, which is unlikely). We can verify it using DBMS_SQLTUNE API to compute the signature of both SQL texts, original and truncated.

SQL> select dbms_sqltune.sqltext_to_signature('create table test_sh') signature from dual;
SQL> select dbms_sqltune.sqltext_to_signature('create table test_shane as select * from dba_objects') signature from dual;

So V$SQL was right while SPM kind of used (I’m saying “kind of” because tracing the call to DBMS_SPM it seems like a different way is used to pull up the signature) the truncated text to compute and store the incorrect signature and as a consequence the baseline won’t be used because the correct signature won’t find any match in the SMB.
From 10053 from a hard parse

SPM: statement not found in SMB

Testing it in 12.2 where the SQL text is intact the baseline is created using the proper signature

----------------------- ------------------------------------------------------
    6988945084141899327 create table test_shane as select * from dba_objects

   - SQL plan baseline SQL_PLAN_61zdshtmgv3jzd9546056 used for this statement

… and the baseline is correctly used 🙂

7 thoughts on “Truncated CTAS text and SQL Plan Baselines

  1. are you also posting with orapeeps?



  2. I guess the only question would be why you would be repeatedly creating the same table with the same CTAS to make SPM relevant?


    • I totally agree that’s the better question to ask 🙂 Unfortunately I don’t know what the final client is doing, if I’ve to guess I’d say they like CTAS more than truncate/insert? But really guessing here 😦


      • This code was written and implemented into production against the advice of many. The developers main argument for implementing the code in this manner was was that they did not have to maintain column definitions in unison with the base tables that the data is selected from because the table definition would be created based on the selection at the time. Pretty weak argument in my mind.

        Incidentally, this same code has been re-written using truncate / insert, but the issue remains between now and when that code can be implemented.


  3. Good information provided by you ,very nice presentation of article i learn few new things from your articles.
    Thank you


  4. Thanks for sharing nice information, Hope you will share many more articles.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s