Home » RDBMS Server » Performance Tuning » dbms_xplan.display_cursor (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit)
dbms_xplan.display_cursor [message #564027] Mon, 20 August 2012 10:27 Go to next message
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
Senior Member

Hi,

I ran a query with /*+ gather_plan_statics */ hint,

and ran below to get the additional information (basically A-rows and A-time)

select * from table(dbms_xplan.display_cursor('4ukp7un70qvhg', NULL, 'allstats last outline'));


But I found other columns as well (below), can you tell me the significance of those columns:

---------------------------
Id
Operation
Name
Starts
E-Rows
A-Rows
A-Time
Buffers
Reads
Writes
OMem
1Mem
Used-Mem
Used-Tmp
------------------

Want to know what is the meaning of all these columns, found no documentation about that.

Regards,
Manu
Re: dbms_xplan.display_cursor [message #564032 is a reply to message #564027] Mon, 20 August 2012 11:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The actual hint is gather_plan_statistics.
E-Rows = Estimated rows
A-Rows = Accessed rows

I don't know what you mean by other columns but Id, Operation, Name exists since the very first version of EXPLAIN PLAN (far before dbms_xplan). Most of other ones are obvious (what do you think Buffers, Reads, Writes, Used-Mem or Used-Tmp mean?).

Regards
Michel

[Updated on: Mon, 20 August 2012 11:14]

Report message to a moderator

Re: dbms_xplan.display_cursor [message #564039 is a reply to message #564032] Mon, 20 August 2012 13:35 Go to previous messageGo to next message
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
Senior Member

Yes, I know about ID, operation, Name, E-rows and A-rows already

But I want to know particular columns:

Starts - don't know
Buffers - Guess, Block read from buffer cache
Reads - Guess, From disk to buffer cache
Writes - Guess, Write on disk
OMem - don't know
1Mem - don't know
Used-Mem - Guess, used Primary Memeory
Used-Tmp - Guess, used Temporary tablespace

Is my guess correct, is there any documentation available explaining them.

Regards,
Manu
Re: dbms_xplan.display_cursor [message #564040 is a reply to message #564039] Mon, 20 August 2012 13:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no documentation neither in standard documentation nor at Metalink/MOS site.

"Starts" column is a multiplier for the E-rows to get the A-rows in case of nested loops operation.

Regards
Michel
Re: dbms_xplan.display_cursor [message #564041 is a reply to message #564039] Mon, 20 August 2012 13:50 Go to previous message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
"Starts" is the number of times that the step was executed. Very important, if you are not clear on what operations cause iteration of subsequent operations.
--
update: Omem and 1mem are (I think) to do with how much memory would be needed for the operation to run optimally or 1 pass.

[Updated on: Mon, 20 August 2012 13:52]

Report message to a moderator

Previous Topic: Same query with different Explain Plan in two Database
Next Topic: Performance Issue while truncating table partition. (3 Merged)
Goto Forum:
  


Current Time: Thu Mar 28 07:41:45 CDT 2024