Loading ...
Sorry, an error occurred while loading the content.

Re: [itsoracleteam] Ques asked by PDM at IFLEX solutions Ltd.

Expand Messages
  • jay pathak
    Hi Gaurav, Here I tried to explain these questions, please go through a file attached with this email. Going through my answers, you may get some help.
    Message 1 of 3 , Mar 1 7:09 AM
    • 0 Attachment
      Hi Gaurav,

      Here I tried to explain these questions, please go
      through a file attached with this email.
      Going through my answers, you may get some help.

      Regards,
      Jay Pathak,
      PATH Infotech,
      Mumbai.


      --- Gaurav Pandya <gauravhpandya@...> wrote:

      >
      > WHAT WILL BE YOUR APPROACH TOWARDS INCREASING
      > PERFORMANCE
      > WHILE INSERTING ROWS IN A TABLE, IF IT TAKES MORE
      > TIME
      > WHILE INSERTING. WHAT STEPS WILL YOU TAKE?
      > WHAT WILL BE YOUR APPROACH TOWARDS INCREASING
      > PERFORMANCE
      > WHICLE SELECTING ROWS FROM A TABLE, IF IT TAKES MORE
      > TIME
      > WHILE SELECTING. WHAT STEPS WILL YOU TAKE?
      > HOW WILL YOU TUNE YOUR SQL?
      > WHY DO WE USE CURSORS?
      > HOW WE CAN COME TO KNOW WHETHER DATA EXISTS OR
      > NOT IN A TABLE
      > (I DONT WANT TO USE SELECT * OR SELECT
      > COUNT(*))......
      > HOW?
      > WHAT IS THE PURITY LEVEL?
      > my friends, the Great oracle champs , try to
      > answer this question. i need it very urgently.
      > waiting a reply from your side.
      >
      > Gaurav Pandya
      > CMC LTD,
      > BKC,
      > Mumbai.
      >
      >
      > ---------------------------------
      > Want to start your own business? Learn how on Yahoo!
      > Small Business.




      ____________________________________________________________________________________
      TV dinner still cooling?
      Check out "Tonight's Picks" on Yahoo! TV.
      http://tv.yahoo.com/
    • jay pathak
      WHAT WILL BE YOUR APPROACH TOWARDS INCREASING PERFORMANCE These are basic steps and procedures for performance issues. - First I will collect performance
      Message 2 of 3 , Mar 2 2:36 AM
      • 0 Attachment
        WHAT WILL BE YOUR APPROACH TOWARDS INCREASING
        PERFORMANCE

        These are basic steps and procedures for performance
        issues.

        - First I will collect performance statistics by
        taking snapshots of a database.
        Then by taking Statspack report , I will analyze
        whether system is getting problem because of library
        cache or database buffer cache?
        Also see that sorts on dist to memory ration is how
        much?
        Also analyze top 5 events. And also go through all
        SQL statements , which SQL statements are taking more
        then 6 seconds.

        Then I will also check that any object is in system
        tablespace ? Indexes are properly used or not?
        Queries using CBO or RBO? If RBO is used then
        collect statistics by executing ANALYZE... command.
        Any contention occurs ? if occurs then I will put
        files on different mount points.

        I will also collect Histograms for those tables
        having low cardinality.
        And analyze tables whether they are having a
        problem of row chaining and migration by collecting
        table level statistics.

        Also need to check Paging-Swapping using OS
        command.
        To come out from this problem we can put a
        parameter lock_sga=true, but be careful before using
        these types of
        parameters because they only used in particular
        circumstances and for particular OS like IBM AIX.

        And then analyze that whether big tables can be
        made as a partitioned table or not?
        Finally,
        I will go through SQL statements for SQL tuning
        which are consuming more resources and try to identify
        them using v$process and v$sql and v$session.

        There are lots of thing we can do to optimize a
        performance other then this things.






        WHILE INSERTING ROWS IN A TABLE, IF IT TAKES MORE TIME

        WHILE INSERTING. WHAT STEPS WILL YOU TAKE?

        - First I need to check mount points and tablespace
        space using views v$dba_free_space for oracle system
        and for Unix I also
        need to check using a command (df -g) for mount
        points.
        - Second I will see whether because of higher level
        log switches and archival problem is coming.
        - Third, I will check how many physical writes by
        using a view v$filestat etc. And if it is a problem
        then I will increase Database Buffer.
        - Fourth, I will also check because of Indexing this
        problem comes or not?


        WHICLE SELECTING ROWS FROM A TABLE, IF IT TAKES MORE
        TIME
        WHILE SELECTING. WHAT STEPS WILL YOU TAKE?

        We will see whether indexes should be made or not?
        Collect histograms, use CBO,partitioned table, etc.


        HOW WILL YOU TUNE YOUR SQL?

        First I will collect table level statistics. Then,
        Using utlxplan utility I will put SQL in EXPLAIN PLAN
        command and then analyze which method it is using? how
        much CPU cycles and IO cycles occur? Indexes are used
        or not? Sorting happens or not? Which join method
        used? Whats more beneficial Index or FTS?
        For joins , 3 methods are there, 1. Nested loop 2.
        hash join 3. sort merge. If only small number of
        records are fetched then I will go for 1 method.
        If I am having sorted indexes and large number of
        records I need then I will go for Sort Merge otherwise
        Hashing.
        Lots of things are there but these are basics.



        WHY DO WE USE CURSORS?
        - Don’t know.


        HOW WE CAN COME TO KNOW WHETHER DATA EXISTS OR NOT IN
        A TABLE
        (I DONT WANT TO USE SELECT * OR SELECT COUNT(*))...
        ...
        HOW?
        Going through simple DELETE table_name query I can
        get a feedback that how many rows deleted?
        I can also rollback after getting a feedback. Simple.


        WHAT IS THE PURITY LEVEL?

        The purity level of a function determines how and
        where Oracle is allowed to use the function. If you
        want to be able to call a function from SQL,
        SELECT some_function( column_name )
        FROM myTable
        for example, that function cannot modify a database
        table (it must have a WNDS purity level). In older
        versions of Oracle, developers had to explicitly
        assert this purity level. In more recent versions, the
        Oracle compiler is smart enough to determine the
        purity level when it compiles the code, so developers
        no longer need to assert a purity level for functional
        reasons.










        --- jay pathak <pathakjay@...> wrote:

        > Hi Gaurav,
        >
        > Here I tried to explain these questions, please go
        > through a file attached with this email.
        > Going through my answers, you may get some help.
        >
        > Regards,
        > Jay Pathak,
        > PATH Infotech,
        > Mumbai.
        >
        >
        > --- Gaurav Pandya <gauravhpandya@...> wrote:
        >
        > >
        > > WHAT WILL BE YOUR APPROACH TOWARDS INCREASING
        > > PERFORMANCE
        > > WHILE INSERTING ROWS IN A TABLE, IF IT TAKES MORE
        > > TIME
        > > WHILE INSERTING. WHAT STEPS WILL YOU TAKE?
        > > WHAT WILL BE YOUR APPROACH TOWARDS INCREASING
        > > PERFORMANCE
        > > WHICLE SELECTING ROWS FROM A TABLE, IF IT TAKES
        > MORE
        > > TIME
        > > WHILE SELECTING. WHAT STEPS WILL YOU TAKE?
        > > HOW WILL YOU TUNE YOUR SQL?
        > > WHY DO WE USE CURSORS?
        > > HOW WE CAN COME TO KNOW WHETHER DATA EXISTS OR
        > > NOT IN A TABLE
        > > (I DONT WANT TO USE SELECT * OR SELECT
        > > COUNT(*))......
        > > HOW?
        > > WHAT IS THE PURITY LEVEL?
        > > my friends, the Great oracle champs , try to
        > > answer this question. i need it very urgently.
        > > waiting a reply from your side.
        > >
        > > Gaurav Pandya
        > > CMC LTD,
        > > BKC,
        > > Mumbai.
        > >
        > >
        > > ---------------------------------
        > > Want to start your own business? Learn how on
        > Yahoo!
        > > Small Business.
        >
        >
        >
        >
        >
        ____________________________________________________________________________________
        > TV dinner still cooling?
        > Check out "Tonight's Picks" on Yahoo! TV.
        > http://tv.yahoo.com/> {\rtf1\ansi\ansicpg1252\uc1
        >
        \deff0\deflang1033\deflangfe1033{\fonttbl{\f0\froman\fcharset0\fprq2{\*\panose
        > 02020603050405020304}Times New
        > Roman;}{\f2\fmodern\fcharset0\fprq1{\*\panose
        > 02070309020205020404}Courier New;}
        > {\f3\froman\fcharset2\fprq2{\*\panose
        >
        05050102010706020507}Symbol;}{\f14\fnil\fcharset2\fprq2{\*\panose
        >
        05000000000000000000}Wingdings;}{\f28\froman\fcharset238\fprq2
        > Times New Roman CE;}{\f29\froman\fcharset204\fprq2
        > Times New Roman Cyr;}
        > {\f31\froman\fcharset161\fprq2 Times New Roman
        > Greek;}{\f32\froman\fcharset162\fprq2 Times New
        > Roman Tur;}{\f33\froman\fcharset177\fprq2 Times New
        > Roman (Hebrew);}{\f34\froman\fcharset178\fprq2 Times
        > New Roman (Arabic);}
        > {\f35\froman\fcharset186\fprq2 Times New Roman
        > Baltic;}{\f44\fmodern\fcharset238\fprq1 Courier New
        > CE;}{\f45\fmodern\fcharset204\fprq1 Courier New
        > Cyr;}{\f47\fmodern\fcharset161\fprq1 Courier New
        > Greek;}{\f48\fmodern\fcharset162\fprq1 Courier New
        > Tur;}
        > {\f49\fmodern\fcharset177\fprq1 Courier New
        > (Hebrew);}{\f50\fmodern\fcharset178\fprq1 Courier
        > New (Arabic);}{\f51\fmodern\fcharset186\fprq1
        > Courier New
        >
        Baltic;}}{\colortbl;\red0\green0\blue0;\red0\green0\blue255;\red0\green255\blue255;
        >
        \red0\green255\blue0;\red255\green0\blue255;\red255\green0\blue0;\red255\green255\blue0;\red255\green255\blue255;\red0\green0\blue128;\red0\green128\blue128;\red0\green128\blue0;\red128\green0\blue128;\red128\green0\blue0;\red128\green128\blue0;
        >
        \red128\green128\blue128;\red192\green192\blue192;}{\stylesheet{\ql
        >
        \li0\ri0\widctlpar\aspalpha\aspnum\faauto\adjustright\rin0\lin0\itap0
        >
        \fs24\lang1033\langfe1033\cgrid\langnp1033\langfenp1033
        > \snext0 Normal;}{
        > \s1\ql
        >
        \li0\ri0\keepn\nowidctlpar\faauto\outlinelevel0\rin0\lin0\itap0
        >
        \b\f2\fs20\lang1033\langfe1033\cgrid\langnp1033\langfenp1033
        > \sbasedon0 \snext0 heading 1;}{\*\cs10 \additive
        > Default Paragraph
        > Font;}}{\*\listtable{\list\listtemplateid1198131140
        >
        \listhybrid{\listlevel\levelnfc23\levelnfcn23\leveljc0\leveljcn0\levelfollow0\levelstartat0\levelspace0\levelindent0{\leveltext\leveltemplateid1368042530\'01-;}{\levelnumbers;}\loch\af0\hich\af0\dbch\af0\chbrdr\brdrnone\brdrcf1
        >
        > \chshdng0\chcfpat1\chcbpat1\fbias0
        > \fi-360\li1080\jclisttab\tx1080
        >
        }{\listlevel\levelnfc23\levelnfcn23\leveljc0\leveljcn0\levelfollow0\levelstartat1\levelspace0\levelindent0{\leveltext\leveltemplateid67698691\'01o;}{\levelnumbers;}\f2\chbrdr
        > \brdrnone\brdrcf1 \chshdng0\chcfpat1\chcbpat1\fbias0
        > \fi-360\li1800\jclisttab\tx1800
        >
        }{\listlevel\levelnfc23\levelnfcn23\leveljc0\leveljcn0\levelfollow0\levelstartat1\levelspace0\levelindent0{\leveltext\leveltemplateid67698693\'01\u-3929
        > ?;}{\levelnumbers
        > ;}\f14\chbrdr\brdrnone\brdrcf1
        > \chshdng0\chcfpat1\chcbpat1\fbias0
        > \fi-360\li2520\jclisttab\tx2520
        >
        }{\listlevel\levelnfc23\levelnfcn23\leveljc0\leveljcn0\levelfollow0\levelstartat1\levelspace0\levelindent0{\leveltext\leveltemplateid67698689
        > \'01\u-3913
        > ?;}{\levelnumbers;}\f3\chbrdr\brdrnone\brdrcf1
        > \chshdng0\chcfpat1\chcbpat1\fbias0
        > \fi-360\li3240\jclisttab\tx3240
        >
        }{\listlevel\levelnfc23\levelnfcn23\leveljc0\leveljcn0\levelfollow0\levelstartat1\levelspace0\levelindent0{\leveltext
        >
        \leveltemplateid67698691\'01o;}{\levelnumbers;}\f2\chbrdr\brdrnone\brdrcf1
        > \chshdng0\chcfpat1\chcbpat1\fbias0
        > \fi-360\li3960\jclisttab\tx3960
        >
        }{\listlevel\levelnfc23\levelnfcn23\leveljc0\leveljcn0\levelfollow0\levelstartat1\levelspace0\levelindent0
        > {\leveltext\leveltemplateid67698693\'01\u-3929
        > ?;}{\levelnumbers;}\f14\chbrdr\brdrnone\brdrcf1
        > \chshdng0\chcfpat1\chcbpat1\fbias0
        > \fi-360\li4680\jclisttab\tx4680
        >
        }{\listlevel\levelnfc23\levelnfcn23\leveljc0\leveljcn0\levelfollow0\levelstartat1\levelspace0
        >
        \levelindent0{\leveltext\leveltemplateid67698689\'01\u-3913
        > ?;}{\levelnumbers;}\f3\chbrdr\brdrnone\brdrcf1
        > \chshdng0\chcfpat1\chcbpat1\fbias0
        > \fi-360\li5400\jclisttab\tx5400
        >
        }{\listlevel\levelnfc23\levelnfcn23\leveljc0\leveljcn0\levelfollow0\levelstartat1
        >
        \levelspace0\levelindent0{\leveltext\leveltemplateid67698691\'01o;}{\levelnumbers;}\f2\chbrdr\brdrnone\brdrcf1
        > \chshdng0\chcfpat1\chcbpat1\fbias0
        > \fi-360\li6120\jclisttab\tx6120
        >
        }{\listlevel\levelnfc23\levelnfcn23\leveljc0\leveljcn0\levelfollow0
        >
        \levelstartat1\levelspace0\levelindent0{\leveltext\leveltemplateid67698693\'01\u-3929
        > ?;}{\levelnumbers;}\f14\chbrdr\brdrnone\brdrcf1
        > \chshdng0\chcfpat1\chcbpat1\fbias0
        > \fi-360\li6840\jclisttab\tx6840 }{\listname
        > ;}\listid1005011611}}{\*\listoverridetable
        >
        {\listoverride\listid1005011611\listoverridecount0\ls1}}{\info{\author
        > db.admin}{\operator
        >
        db.admin}{\creatim\yr2007\mo3\dy1\hr20\min28}{\revtim\yr2007\mo3\dy1\hr20\min31}{\version3}{\edmins3}{\nofpages2}{\nofwords585}{\nofchars3337}{\*\company
        > vi}
        >
        {\nofcharsws4098}{\vern8269}}\widowctrl\ftnbj\aenddoc\noxlattoyen\expshrtn\noultrlspc\dntblnsbdb\nospaceforul\hyphcaps0\horzdoc\dghspace120\dgvspace120\dghorigin1701\dgvorigin1984\dghshow0\dgvshow3\jcompress\viewkind4\viewscale100\nolnhtadjtbl
        > \fet0
        > \sectd \linex0\sectdefaultcl
        >
        {\*\pnseclvl1\pnucrm\pnstart1\pnindent720\pnhang{\pntxta
        >
        .}}{\*\pnseclvl2\pnucltr\pnstart1\pnindent720\pnhang{\pntxta
        >
        .}}{\*\pnseclvl3\pndec\pnstart1\pnindent720\pnhang{\pntxta
        > .}}{\*\pnseclvl4
        > \pnlcltr\pnstart1\pnindent720\pnhang{\pntxta
        >
        )}}{\*\pnseclvl5\pndec\pnstart1\pnindent720\pnhang{\pntxtb
        > (}{\pntxta
        >
        )}}{\*\pnseclvl6\pnlcltr\pnstart1\pnindent720\pnhang{\pntxtb
        > (}{\pntxta
        >
        )}}{\*\pnseclvl7\pnlcrm\pnstart1\pnindent720\pnhang{\pntxtb
        > (}
        > {\pntxta
        >
        )}}{\*\pnseclvl8\pnlcltr\pnstart1\pnindent720\pnhang{\pntxtb
        > (}{\pntxta
        >
        )}}{\*\pnseclvl9\pnlcrm\pnstart1\pnindent720\pnhang{\pntxtb
        > (}{\pntxta )}}\pard\plain \s1\ql
        >
        \li0\ri0\keepn\nowidctlpar\faauto\outlinelevel0\rin0\lin0\itap0
        >
        >
        \b\f2\fs20\lang1033\langfe1033\cgrid\langnp1033\langfenp1033
        > {WHAT WILL BE YOUR APPROACH TOWARDS INCREASING
        > PERFORMANCE
        > \par }\pard\plain \ql
        > \li0\ri0\nowidctlpar\faauto\rin0\lin0\itap0
        >
        \fs24\lang1033\langfe1033\cgrid\langnp1033\langfenp1033
        > {\f2\fs20
        > \par These are basic steps and procedures for
        > performance issues.
        > \par
        > \par - First I will collect performance statistics
        > by taking snapshots of a database.
        > \par Then by taking Statspack report , I will
        > analyze whether system is getting problem because of
        > library cache or database buffer cache?
        > \par Also see that sorts on dist to memory ration
        > is how much?
        > \par Also analyze top 5 events. And also go
        > through all SQL statements , which SQL statements
        > are taking more then 6 seconds.
        > \par
        > \par Then I will also check that any object is in
        > system tablespace ? Indexes are properly used or
        > not?
        > \par Queries using CBO or RBO? If RBO is used
        > then collect statistics by executing ANALYZE...
        > command.
        > \par Any contention occurs ? if occurs then I
        > will put files on different mount points.
        > \par
        > \par I will also collect Histograms for those
        > tables having low cardinality.
        > \par And analyze tables whether they are having a
        > problem of row chaining and migration by collecting
        > table level statistics.
        > \par
        > \par Also need to check Paging-Swapping using OS
        > command.
        > \par To come out from this problem we can put a
        > parameter lock_sga=true, but be careful before using
        > these types of
        > \par parameters because they only used in
        > particular circumstances and for particular OS like
        > IBM AIX.
        > \par
        > \par And then analyze that whether big tables can
        > be made as a partitioned table or not?
        > \par Finally,
        > \par \tab I will go through SQL statements for SQL
        > tuning which are consuming more resources and try to
        > identify
        > \par them using v$process and v$sql and
        > v$session.
        > \par
        > \par There are lots of thing we can do to optimize a
        > performance other then this things.
        > \par
        > \par
        > \par
        > \par \tab
        > \par
        > \par }{\b\f2\fs20
        > \par WHILE INSERTING ROWS IN A TABLE, IF IT TAKES
        > MORE TIME
        > \par WHILE INSERTING. WHAT STEPS WILL YOU TAKE?
        > }{\f2\fs20
        > \par
        > \par \tab - First I need to check mount points and
        > tablespace space using views v$dba_free_space for
        > oracle system and for Unix I also
        > \par \tab need to check using a command (df -g)
        > for mount points.
        > \par \tab - Second I will see whether because of
        > higher level log switches and archival problem is
        > coming.
        > \par \tab - Third, I will check how many physical
        > writes by using a view v$filestat etc. And if it is
        > a problem then I will increase Database Buffer.
        > \par \tab - Fourth, I will also check because of
        > Indexing this problem comes or not?
        > \par \tab
        > \par
        > \par WHICLE SELECTING ROWS FROM A TABLE, IF IT TAKES
        > MORE TIME
        > \par WHILE SELECTING. WHAT STEPS WILL YOU TAKE? \tab
        >
        > \par
        > \par \tab We will see whether indexes should be made
        > or not?
        > \par \tab Collect histograms, use CBO,partitioned
        > table, etc.
        > \par \tab \tab
        > \par
        > \par }\pard\plain \s1\ql
        >
        \li0\ri0\keepn\nowidctlpar\faauto\outlinelevel0\rin0\lin0\itap0
        >
        \b\f2\fs20\lang1033\langfe1033\cgrid\langnp1033\langfenp1033
        > {HOW WILL YOU TUNE YOUR SQL?
        > \par }\pard\plain \ql
        > \li0\ri0\nowidctlpar\faauto\rin0\lin0\itap0
        === message truncated ===




        ____________________________________________________________________________________
        Now that's room service! Choose from over 150,000 hotels
        in 45,000 destinations on Yahoo! Travel to find your fit.
        http://farechase.yahoo.com/promo-generic-14795097
      Your message has been successfully submitted and would be delivered to recipients shortly.