This content has been marked as final. Show 12 replies
What does execute signifies ? I am not able to get this ?Forget about SQL and IT terms for a while and think what is execute in general life. Execute means do the work, take action right ? Same applies in the execution phase of SQL.
See below text from below link :
2) execute - we OPEN the statement. For an update, for a delete, for an insert - that would be it, when you OPEN the statement, we execute it. All of the work happens here.
for select it is more complex. Most selects will do ZERO work during the execute. All we are doing is opening the cursor - the cursor is a pointer to the space in the shared pool where the plan is, your bind variable values, the SCN that represents the "as of" time for your query - in short the cursor at this point is your context, your virtual machine state, think of the SQL plan as if it were bytecode (it is) executed as a program (it is) in a virtual machine (it is). The cursor is your instruction pointer (where are you in the execution of this statement), your state (like registers), etc. Normally, a select does nothing here - it just "gets ready to rock and roll, the program is ready to go, but not yet really started".
2) What does index cluster column signifies in dba_indexes ?As such there is no "cluster" column in dba_indexes (126.96.36.199). If you are referring clustering_factor then :
Indicates the amount of order of the rows in the table based on the values of the index.
If the value is near the number of blocks, then the table is very well ordered. In this case, the index entries in a single leaf block tend to point to rows in the same data blocks.
If the value is near the number of rows, then the table is very randomly ordered. In this case, it is unlikely that index entries in the same leaf block point to rows in the same data blocks.
For bitmap indexes, this column is not applicable and is not used.
3) what is value of DISK_READ in v$sess_io (in B , KB ?)
No such column DISK_READ....
SQL> desc v$sess_io; Name Null? Type ----------------------------------------- -------- --------------- SID NUMBER BLOCK_GETS NUMBER CONSISTENT_GETS NUMBER PHYSICAL_READS NUMBER BLOCK_CHANGES NUMBER CONSISTENT_CHANGES NUMBER OPTIMIZED_PHYSICAL_READS NUMBER
Sourabh85 wrote:Oracle® Database Concepts guide.
1)Syntax and object checks
1) Parse --- Hard parse or soft parse
3)Fetch ---- Fetching data from buffer cache or dbf files
4) Elapsed = Parse + execute + fetch and also wait time
What does execute signifies ? I am not able to get this ?
2) What does index cluster column signifies in dba_indexes ?There is no DISK_READ column in v$sess_io. Refer to the Oracle® Database Reference guide for data dictionary details on both views mentioned.
3) what is value of DISK_READ in v$sess_io (in B , KB ?)
Physical read in Oracle :
When a user performs a SQL query, Oracle tries to retrieve the data from the database buffer cache (memory) first, then goes to disk if it is not in memory already. Reading data blocks from disk is much more expensive than reading the data blocks from memory. There are two types of physical reads 1. Physical Reads (per transaction) 2. Physical Reads (per second)
Good scripts for physical reads by Gavin @ below link :
Thanks a lot. I agree with you . If you read the war report there are two types of Physical read mentioned per second and per transaction.
I am interested in the number they provided kile 53000 per second or 325 per transaction .What is the unit of these value in KB or B
Similary PHYSICAL_READ output unit is in KB or B , since I have seen for some query PHYSICAL_READ Values come > 100000. So what is unit of this value...
Similar for the Logical Reads , Block changes , Redo Size in the Load profile of AWR report
If I see the SQL BY READ in AWR value foe some sql is 11,428,802.
Edited by: Sourabh85 on Jul 22, 2012 11:24 PM
Edited by: Sourabh85 on Jul 22, 2012 11:27 PM
I am interested in the number they provided kile 53000 per second or 325 per transaction .What is the unit of these value in KB or BQuery the DBA_HIST_METRIC_NAME view. This view DBA_HIST_METRIC_NAME defines the important metrics the AWR collects, the groups to which they belong, and the unit in which they are collected.
select metric_name,metric_unit from DBA_HIST_METRIC_NAME where upper(metric_name) like '%PHYSICAL%';
Edited by: Girish Sharma on Jul 23, 2012 3:28 PM
And One of the great collection of AWR by Sir Jonathan Lewis :
actually, the line between EXECUTE and FETCH phases can be a bit fuzzy. Oracle tries to postpone all actual work to the last phase (FETCH) as much as possible, because this phase is controlled by the client (e.g. you can fetch first N rows and then decide that you don't want to fetch the rest -- you don't have this flexibility with EXECUTE). However, some preparation work has to be done first (some rowsource structures have to be built) -- that's what the EXECUTE phase does.
Thanks BuT I have checked the query provided by you :
METRIC_NAME METRIC UNIT
Physical Reads Per Sec Reads Per Second
Physical Reads Per Txn Reads Per Txn
Physical Reads (Session) Reads
What does it signifies since unit in neither in B, KB. IF
select * from V$sess_io order by physical_readS desc ;
20719 1372 440039 75580183 1350 302 0
So what does this number signifies. Please Help me in understand this. What can be optimized value for PHYSICAL_READ ?
Hi Syntax check is wether your sql is properly written or not .
1) select count(*) dba_objects ; This sql is not properly written missing from keyword . This is called as syntax check
2) select count(*) from dba_obj : Here recursive sql will check for the object (dba_obj ) whether exit under that schema or not.