Home » RDBMS Server » Performance Tuning » dbms_xplan for format model (10.1.0.4.0)
dbms_xplan for format model [message #579969] Mon, 18 March 2013 08:01 Go to next message
pointers
Messages: 345
Registered: May 2008
Senior Member
Hi,

I am just trying to get the execution plan of a query

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - 64bi
PL/SQL Release 10.1.0.4.0 - Production
CORE    10.1.0.4.0      Production
TNS for Solaris: Version 10.1.0.4.0 - Production
NLSRTL Version 10.1.0.4.0 - Production


SQL> explain plan for select * from dual;

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
Plan hash value: 3543395131

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

8 rows selected.


But, when I try to give any formats like 'basic + Cost' or any other explicit formats (I tried to give different formats), I do get
an error saying error: format as shown

SQL> select * from table(dbms_xplan.display('plan_table',null,'basic + cost'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------
Error: format 'BASIC + COST' not valid for DBMS_XPLAN.DISPLAY()


I tried to check over internet but no positive answer.

Thank you very much in advance.

Regards,
Pointers
Re: dbms_xplan for format model [message #579972 is a reply to message #579969] Mon, 18 March 2013 08:47 Go to previous messageGo to next message
Roachcoach
Messages: 1181
Registered: May 2010
Location: UK
Senior Member
Check here

http://docs.oracle.com/cd/B14117_01/appdev.101/b10802/d_xplan.htm
Re: dbms_xplan for format model [message #579973 is a reply to message #579972] Mon, 18 March 2013 09:04 Go to previous messageGo to next message
cookiemonster
Messages: 10859
Registered: September 2008
Location: Rainy Manchester
Senior Member
The formats you're trying to use are new in oracle 11. Oracle 10 doesn't have them.
Next time you get a problem like that check the documentation before checking the internet in general.
Re: dbms_xplan for format model [message #579976 is a reply to message #579973] Mon, 18 March 2013 09:12 Go to previous message
pointers
Messages: 345
Registered: May 2008
Senior Member
@Roachcoach, cookiemonster:
Thank you very much for your response.

Yes you are right, these options were not there in 10.1.0.4 but were available in 10.2
I just over looked in 10.2

Regards,
Pointers
Previous Topic: Execution Plan Changing with same SQL Profile
Next Topic: SubQuery - Joins performance compare
Goto Forum:
  


Current Time: Thu Jul 31 17:45:24 CDT 2014

Total time taken to generate the page: 0.14898 seconds