Skip to Main Content
  • Questions

  • Question about Explain plans and Explain plan table

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Ravi.

Asked: June 08, 2023 - 2:09 pm UTC

Last updated: June 12, 2023 - 4:36 am UTC

Version: 19c

Viewed 1000+ times

You Asked

Hi,

I have a question about retrieving Full SQL statement from Oracle dictionary views, table or object.

I DO NOT execute a SQL statement and just do an explain plan for the query, from which dictionary views or table or any other object, can I get complete text of this SQL statement.

I have tried lots of views like v$SQL, v$sqltext, etc., but none of them return the complete SQL text, some just return first 1000 characters, but my requirement is I need Full text of the SQL statement.

Thanks,

Ravi S

and Chris said...

If you're using the EXPLAIN PLAN command, then the full text of the statement is exactly the explain statement you ran:

explain plan for 
  select max ( salary ) from hr.employees;
   
select sql_id, sql_fulltext 
from   v$sql
where  sql_text like '%select max%employees'
and    sql_text not like '%not this%';

SQL_ID           SQL_FULLTEXT                                                   
bajzjm2x0tb7f    explain plan for 
  select max ( salary ) from hr.employees


If this is not what you're looking for, please clarify with an example showing what you're doing and what you expect to get.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

dbms_xplan too

Dana Day, June 09, 2023 - 5:29 pm UTC

I do this a LOT. I use select * from table(dbms_xplan.display_cursor([sql_id]));
Works fine 99% of the time.
Also using AWR sql_id report can get you the full text with many other relevant pieces of information.
Connor McDonald
June 12, 2023 - 4:30 am UTC

Agreed.

(As long as the plan is still in v$sql_plan)

Follow-up question

Ravi S, June 10, 2023 - 5:32 pm UTC

Thanks Chris and also Dana for the comments.
My apology for not asking the question in details in the first place.

Our company has operation in multiple countries and developers execute SQL statements on the DB. When they run into issues, we've to tune their queries. However, developers are not allowed to send Queries in email due to security reasons (I know it sounds weird). Max they send SQL_ID or PLAN_HASH_VALUE. That's the reason for my original question, and I was looking for objects where I can get these SQL's.

Thanks again,

Ravi S

Connor McDonald
June 12, 2023 - 4:35 am UTC

v$sql or v$sqlstats is your best bet in that case.

If they've been aged out, then its time to look into the AWR historical tables if you're licensed for that.

Finding full SQL text

mathguy, June 11, 2023 - 5:54 pm UTC

OK, so it seems that your question has nothing (or very little) to do with EXPLAIN PLAN. Your user sends you an SQL_ID and you want to find the full text of that statement, so that you can run EXPLAIN PLAN on it. What you want to do with the full SQL text seems irrelevant; once the user sends you the SQL_ID and you can find the full text of the query, you can run EXPLAIN PLAN on it but that doesn't seem to matter.

Chris already answered that, perhaps just a bit indirectly. Query V$SQL, use the SQL_ID in the WHERE clause, and select SQL_FULLTEXT. If you are only seeing the first 1000 characters, this means that you selected SQL_TEXT, not SQL_FULLTEXT. Both are columns in V$SQL; the full text is a CLOB, while the (reduced) text is just the first 1000 characters, in VARCHAR2(1000) data type.

The same view has another column, HASH_VALUE, which you can use if that's what you get from your user.
Connor McDonald
June 12, 2023 - 4:36 am UTC

agreed
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.