Skip to Main Content
  • Questions

  • About v$sql_plan and how to decide the maxsize of datafile

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Yindan.

Asked: August 20, 2007 - 7:37 am UTC

Last updated: April 25, 2011 - 7:45 am UTC

Version: 10.2.0.1

Viewed 1000+ times

You Asked

Hi,Tom
I have two questions:
One is about v$sql_plan.That is,
Are the contents in the v$sql_plan view dynamic? What kind of explain plan stored in the v$sql_plan and what kind of operations can cause the explain plan get out of from the v$sql_plan view?

The other is about how to decide the maxsize of datafile in a tablespace
We are doing a database design and we decide set datafile not be autoextensible.But we do not know how to decide the size of datafiles.2G 4G or 8G ?

Thanks very much.


and Tom said...

v$sql_plan shows the ACTUAL TRUE HONEST plan that was used at runtime when the query was executed.

v$sql_plan shows reality.

v$sql_plan is just like "row source operation" is in TKPROF reports (as opposed to explain plans, which can "lie" )

http://asktom.oracle.com/Misc/when-explanation-doesn-sound-quite.html



As for the datafile sizes - that is entirely up to you and what you like. Do you like dealing with 8 or 16gb files? Is that comfortable for you? do your tools support it? Do you want to do that ? If so, go for it.

If not, 2gb is fine.

In short, you choose based on what makes your life easier.


Just remember that in most cases (there is block level recovery, but in most cases) the unit of recovery is the datafile, so in the event of a failure of some sort that damages a single file - your recovery time will be affected as the size of the file goes up.

Rating

  (2 ratings)

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

Comments

Yindan Wang, August 24, 2007 - 2:01 am UTC

Thanks.TOM
Sorry,maybe I didn't express myself clearly.I have a sql statement ,we schedule it to run every day.So I am pretty sure the true plan of this statement must be in the library cache and I can query the plan by its sqlid every day.But last week I can not find this statement's plan by sqlid from v$sql_plan and seemed I did not do anything.So I want to know what operation may cause the plan out of v$sql_plan?Thanks.
Tom Kyte
August 24, 2007 - 2:59 pm UTC

lots of things

tons of things

too many things to enumerate


you should just realize that plans come, they go, they are not persistent - they are transient.

Normal shared pool aging
Squeezed for space in the shared pool
Privilege changes
DDL operations
statistics manipulation

lots and lots of stuff.

v$sql_plan information

jatin, April 20, 2011 - 3:29 pm UTC

Sir

I want to undersntand how do I interpret the information in v$sql_plan.

I understand that v$sql_plan shows information on each child cursor loaded into library cache. Can you please help me understand what does that mean for a single sql executed say 1000 times - why is it showing 25 different rows for this 1 sql:



SQL_ID        PLAN_HASH_VALUE CHILD_NUMBER OBJECT_OWNER                   OBJECT_NAME                          COST   CPU_COST       TIME
------------- --------------- ------------ ------------------------------ ------------------------------ ---------- ---------- ----------
0j06hs4gysj7q        86531355            1                                                                      765
0j06hs4gysj7q        86531355            1 SVCWCMADMIN02                  PK_DOCMETA                              1       7055          1
0j06hs4gysj7q        86531355            1                                                                      765  135409972          6
0j06hs4gysj7q        86531355            1                                                                      763  135396731          6
0j06hs4gysj7q        86531355            1                                                                      761  135365766          6
0j06hs4gysj7q        86531355            1                                                                      759  135334800          6
0j06hs4gysj7q        86531355            1                                                                      755  135303784          6
0j06hs4gysj7q        86531355            1
0j06hs4gysj7q        86531355            1                                                                      754  135295423          6
0j06hs4gysj7q        86531355            1 SVCWCMADMIN02                  GSAINDEXLOG                            72   22921184          1
0j06hs4gysj7q        86531355            1 SVCWCMADMIN02                  REVISIONS                             681  104430664          5
0j06hs4gysj7q        86531355            1 SVCWCMADMIN02                  DOCTYPES                                1       8361          1
0j06hs4gysj7q        86531355            1 SVCWCMADMIN02                  PK_DOCTYPES                             0       1050
0j06hs4gysj7q        86531355            1 SVCWCMADMIN02                  DOCUMENTS                               4      31015          1
0j06hs4gysj7q        86531355            1 SVCWCMADMIN02                  DID_2                                   2      15693          1
0j06hs4gysj7q        86531355            1 SVCWCMADMIN02                  DOCUMENTS                               2      30966          1
0j06hs4gysj7q        86531355            1 SVCWCMADMIN02                  DID_2                                   2      15693          1
0j06hs4gysj7q        86531355            1 SVCWCMADMIN02                  DOCUMENTS                               2      30966          1
0j06hs4gysj7q        86531355            1 SVCWCMADMIN02                  DID_2                                   2      15693          1
0j06hs4gysj7q        86531355            1 SVCWCMADMIN02                  DOCMETA                                 2      13240          1
0j06hs4gysj7q        86531355            1


Do I interpret it as 1 child cursor per row - if so why.

Thanks
Tom Kyte
April 25, 2011 - 7:45 am UTC

It is a plan in there, you use dbms_xplan to display it.


select * from table(dbms_xplan.display_cursor( '0j06hs4gysj7q', 1 ) );

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.