12 Replies Latest reply: Sep 24, 2012 9:38 AM by 947991 RSS

    Few Questions on PT

    947991
      Hi ,

      I need some basic information on PT.

      We have difference phases of SQL Query :


      1)Syntax and object checks
      1) Parse --- Hard parse or soft parse
      2)Excecte
      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 ?

      3) what is value of DISK_READ in v$sess_io (in B , KB ?)

      Regards
      Sourabh Gupta
        • 1. Re: Few Questions on PT
          Girish Sharma
          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".

          http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1855700000346846274
          2) What does index cluster column signifies in dba_indexes ?
          As such there is no "cluster" column in dba_indexes (11.2.0.1). 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.
          http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_1069.htm#i1578369
          3) what is value of DISK_READ in v$sess_io (in B , KB ?)
          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
          No such column DISK_READ....

          Regards
          Girish Sharma
          • 2. Re: Few Questions on PT
            Billy~Verreynne
            Sourabh85 wrote:

            1)Syntax and object checks
            1) Parse --- Hard parse or soft parse
            2)Excecte
            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 ?
            Oracle® Database Concepts guide.
            2) What does index cluster column signifies in dba_indexes ?
            3) what is value of DISK_READ in v$sess_io (in B , KB ?)
            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. Re: Few Questions on PT
              947991
              Hi Girish,

              Thanks a lot. Actually in a query we use DISK_READ alias for PHYSICAL_READ Column, so I mentioned that. I am sorry for confusion.

              So The number provided by PHYSICAL REad column is in ( B, KB or some thing else ) ?

              Please Clarify.

              REgards
              Sourabh Gupta
              • 4. Re: Few Questions on PT
                Girish Sharma
                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 :
                http://gavinsoorma.com/tag/physical-reads/

                Regards
                Girish Sharma
                • 5. Re: Few Questions on PT
                  947991
                  Hi Girish,

                  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.

                  Regards
                  Sourabh Gupta

                  Edited by: Sourabh85 on Jul 22, 2012 11:24 PM

                  Edited by: Sourabh85 on Jul 22, 2012 11:27 PM
                  • 6. Re: Few Questions on PT
                    Girish Sharma
                    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
                    Query 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%';

                    Source:http://www.rampant-books.com/art_nanda_awr.htm

                    Regards
                    Girish Sharma

                    Edited by: Girish Sharma on Jul 23, 2012 3:28 PM
                    And One of the great collection of AWR by Sir Jonathan Lewis :
                    http://jonathanlewis.wordpress.com/2011/02/23/awr-reports/
                    • 7. Re: Few Questions on PT
                      Nikolay Savvinov
                      Hi,

                      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.

                      Best regards,
                      Nikolay
                      • 8. Re: Few Questions on PT
                        947991
                        Hi Girish,

                        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 ;

                        PHYSICAL_READ
                        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 ?

                        REgards
                        Sourabh Gupta
                        • 9. Re: Few Questions on PT
                          895633
                          what does Syntax and object checks
                          kindly give complete basics
                          • 10. Re: Few Questions on PT
                            rp0428
                            >
                            what does Syntax and object checks
                            kindly give complete basics
                            >
                            Please don't HIJACK another user's thread. If you have a question open a new thread.
                            • 11. Re: Few Questions on PT
                              947991
                              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.

                              Regards
                              Sourabh Gupta
                              • 12. Re: Few Questions on PT
                                947991
                                Thanks