{"id":2712,"date":"2018-02-02T22:41:29","date_gmt":"2018-02-02T13:41:29","guid":{"rendered":"http:\/\/haisins.epac.to\/wordpress\/?p=2712"},"modified":"2018-02-02T23:16:57","modified_gmt":"2018-02-02T14:16:57","slug":"sql-trace-facility-tkprof-utility-%ec%82%ac%ec%9a%a9","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=2712","title":{"rendered":"SQL TRACE FACILITY (TKPROF UTILITY \uc0ac\uc6a9)"},"content":{"rendered":"

SQL TRACE FACILITY \ub294SQL \ubb38 \uc0ac\uc6a9\uc5d0\ub300\ud55c \uc131\ub2a5\uc744 \ubd84\uc11d\ud558\uae30\uc704\ud574\uc11c \uc0ac\uc6a9\ub41c\ub2e4.<\/p>\n

\uc774\ub7ec\ud55cSQL TRACE FACILITY \ub97c \uc774\uc6a9\ud558\uba74 \u00a0 \uac01SQL \ubb38\uc5d0 \ub300\ud574\uc11c \ub2e4\uc74c\uacfc \uac19\uc740 \uc815\ubcf4\ub97c \uc5bb\uc744\uc218 \uc788\ub2e4.<\/p>\n

. parse,execute,fetch count<\/p>\n

. CPU \uc640elapsed \uc2dc\uac04<\/p>\n

. physical reads \uc640 logical reads<\/p>\n

. \ucc98\ub9ac\ub41crow \uc758 \uc218<\/p>\n

 <\/p>\n

SQL TRACE FACILITY \ub294SESSION \ud639\uc740INSTANCE \ub2e8\uc704\ub85c \ud560 \uc218 \uc788\uace0TRACE \uacb0\uacfc \ud654\uc77c\uc740tkprof UTILITY \u00a0 \uc5d0\uc758\ud574 \uc0ac\uc6a9\uc790\uac00 \uc77d\uc744\uc218 \uc788\ub294 \ud615\ud0dc\ub85c \ubcc0\ud658\uc2dc\ud0a8\ub2e4.<\/p>\n

 <\/p>\n

SQL TRACE SET UP \ud558\uc5ec \uc0ac\uc6a9\ud558\ub294\ubc29\ubc95<\/p>\n

———————————-<\/p>\n

    \n
  1. SQL TRACE enable \ubc0fTRACE \ud654\uc77c \ub514\ub809\ud1a0\ub9ac \uc9c0\uc815<\/li>\n<\/ol>\n

    < INSTANCE \ub2e8\uc704 ><\/p>\n

    ?\/dbs\/initSID.ora \u00a0 \ud654\uc77c\uc5d0 \ub2e4\uc74c \ub450\uac1c\uc758PARAMETER \ub97c \ucd94\uac00\ud558\uace0DATABASE \ub97c \ub2e4\uc2dcSTARTUP \uc2dc\ud0a8\ub2e4.<\/p>\n

    sql_trace = true<\/p>\n

    timed_statistics = true<\/p>\n

    timed_statistics \uc740 \uc2dc\uc2a4\ud15c\uc5d0 \ub9ce\uc740LOAD \uac00 \uac78\ub9ac\ubbc0\ub85c \uc0ac\uc6a9\ud558\uc9c0 \uc54a\ub294 \uac83\uc774 \uc88b\ub2e4.<\/p>\n

    < SESSION \ub2e8\uc704 ><\/p>\n

    SQL*PLUS<\/p>\n

    $ sqlplus scott\/tiger<\/p>\n

    SQL > ALTER SESSION SET SQL_TRACE = TRUE;<\/p>\n

    SQLFORMS30, RUNFORM30 : -s OPTION \uc0ac\uc6a9<\/p>\n

    $ runform30 -s frmfile scott\/tiger -c vt220<\/p>\n

    PRO*C<\/p>\n

    EXEC SQL CONNECT :username;<\/p>\n

    EXEC SQL ALTER SESSION SET SQL_TRACE = TRUE;<\/p>\n

    TRACE FILE \ub514\ub809\ud1a0\ub9ac\ub294 initSID.ora \ud654\uc77c\uc5d0 \ub2e4\uc74c\uacfc \uac19\uc774 \uc9c0\uc815\ud558\uace0 \uc9c0\uc815\ud558\uc9c0 \uc54a\uc73c\uba74?\/rdbms\/log \ub514\ub809\ud1a0\ub9ac\uc5d0 \uc0dd\uae34\ub2e4.<\/p>\n

    user_dump_dest = \/user\/dump<\/p>\n

     <\/p>\n

      \n
    1. SQL \ubb38 \uc2e4\ud589<\/li>\n<\/ol>\n

      TRACE \ud654\uc77c\uc740SESSION \ub2e8\uc704\ub85c1 \uac1c\uc529 \uc0dd\uae40\uc73c\ub85c \ud55cSESSION \uc5d0\uc11c \uc0ac\uc6a9\ub41c \ubaa8\ub4e0SQL \ubb38\uc758TRACE \uacb0\uacfc\ub2941\uac1c\uc758 \ud654\uc77c\uc5d0 \uc874\uc7ac\ud558\uac8c\ub41c\ub2e4.<\/p>\n

       <\/p>\n

      \uc608. SQL> ALTER SESSION SET \u00a0 SQL_TRACE = TRUE;<\/p>\n

      SQL> SELECT COUNT(*) FROM EMP E,DEPT D<\/p>\n

      WHERE E.DEPTNO = D.DEPTNO;<\/p>\n

      SQL> EXIT;<\/p>\n

       <\/p>\n

        \n
      1. TRACE \ud654\uc77c \ubcc0\ud658<\/li>\n
      2. \ub2e8\uacc4\uc5d0\uc11cSQL \ubb38\uc744 \uc2e4\ud589\ud558\uba74 user_dump_dest \uc5d0 \uc9c0\uc815\ub41c \ub514\ub809\ud1a0\ub9ac\uc5d0TRACE \ud654\uc77c\uc774 \uc0dd\uae30\uace0tkprof \ub97c\uc774\uc6a9\ud558\uc5ec \ud654\uc77c\uc744 \ubcc0\ud658\uc2dc\ud0a8\ub2e4. TRACE \ud654\uc77c\uc740 \uc27d\uac8c \ucc3e\uc744 \uc218 \uc788\ub294 \ud615\ud0dc\uac00 \uc544\ub2c8\ubbc0\ub85cSQL\ubb38\uc744 \uc2e4\ud589\ud558\uae30 \uc804\uc5d0 \u00a0 dump \ub514\ub809\ud1a0\ub9ac\uc5d0 \uc788\ub294ora_xxxx.trc\ud654\uc77c\uc744 \ubaa8\ub450 \uc0ad\uc81c\ud558\uac70\ub098 \uac00\uc7a5\ucd5c\uadfc\uc5d0 \uc0dd\uae34 \ud654\uc77c\uc911\uc5d0\uc11c \ucc3e\uc544\uc57c \ud55c\ub2e4.<\/li>\n<\/ol>\n

         <\/p>\n

        \uc608. $ cd $ORACLE_HOME\/rdbms\/log<\/p>\n

        $ tkprof ora_1111 out sort=fchqry,fchcu explain=scott\/tiger print=20<\/p>\n

         <\/p>\n

        ora_1111 : TRACE \ud654\uc77c<\/p>\n

        out\u00a0\u00a0\u00a0\u00a0\u00a0: OUTPUT \ud654\uc77c. \ub514\ub809\ud1a0\ub9ac\uc5d0out.prf \ub85c \uc0dd\uae34\ub2e4.<\/p>\n

        sort\u00a0\u00a0\u00a0\u00a0\u00a0: \uc9c0\uc815\ub41cOPION(fchqry,fchcu) \uc5d0ASCENDING \uc21c\uc73c\ub85cSQL \ubb38\uc744<\/p>\n

        SORTING \ud55c\ub2e4.<\/p>\n

        explain\u00a0: SQL \ubb38\uc758EXECUTION PLAN \uc744 \ubc1c\uc0dd\uc2dc\ud0a8\ub2e4.<\/p>\n

        print\u00a0\u00a0\u00a0: \uc9c0\uc815\ub41c \uac2f\uc218\uc758SQL\ubb38\uc5d0\ub300\ud574\uc11c\ub9ccTRACE \uacb0\uacfc\ub97cPRINT \ud55c\ub2e4.<\/p>\n

         <\/p>\n

          \n
        1. SQL TRACE \uacb0\uacfc \ubd84\uc11d<\/li>\n<\/ol>\n

          **********************************************************************<\/p>\n

          count\u00a0\u00a0 = number of times OCI procedure was executed<\/p>\n

          cpu\u00a0\u00a0\u00a0\u00a0 = cpu time in seconds executing<\/p>\n

          elapsed = elapsed time in seconds executing<\/p>\n

          disk\u00a0\u00a0 = number of physical reads of buffers from disk<\/p>\n

          query\u00a0\u00a0 = number of buffers gotten for consistent read<\/p>\n

          current = number of buffers gotten in current mode (usually forupdate)<\/p>\n

          rows\u00a0\u00a0 = number of rows processed by the fetch or execute call<\/p>\n

          **********************************************************************<\/p>\n

          SELECT COUNT(*)<\/p>\n

          FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO<\/p>\n

           <\/p>\n

          call\u00a0\u00a0 count cpu\u00a0\u00a0 \u00a0 elapsed\u00a0\u00a0 disk\u00a0\u00a0\u00a0query\u00a0\u00a0 \u00a0 current\u00a0\u00a0 \u00a0 rows<\/p>\n

          —————————————————————–<\/p>\n

          Parse\u00a0\u00a0 2\u00a0\u00a0\u00a0\u00a0 0.00\u00a0\u00a0 0.00\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a00\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 0<\/p>\n

          Execute \u00a0 2\u00a0\u00a0\u00a0\u00a0 0.00\u00a0\u00a0 0.00\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a00\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 0<\/p>\n

          Fetch\u00a0\u00a0 2\u00a0\u00a0\u00a0\u00a0 0.00\u00a0\u00a0 0.00\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a033\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 1<\/p>\n

           <\/p>\n

          Misses in library cache during parse: 1<\/p>\n

          Parsing user id: 8 (SCOTT)<\/p>\n

          Rows\u00a0\u00a0\u00a0\u00a0 Execution Plan<\/p>\n

          ——- —————————————————<\/p>\n

          0 SELECT STATEMENT<\/p>\n

          0\u00a0\u00a0 SORT (AGGREGATE)<\/p>\n

          16\u00a0\u00a0\u00a0\u00a0\u00a0NESTED LOOPS<\/p>\n

          16\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 TABLE ACCESS (FULL) OF ‘EMP’<\/p>\n

          16\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INDEX (UNIQUE SCAN) OF ‘DEPT_PRIMARY_KEY’ (UNIQUE)<\/p>\n

           <\/p>\n

          *********************************************************************<\/p>\n

          \ubd84\uc11d \uc608)<\/p>\n

            \n
          1. cpu, elapsed\u00a0\u00a0 \uc815\ubcf4\uac00\uc5c6\ub294 \uacbd\uc6b0\ub294initSID.ora \uc5d0timed_stattistic = false \ub85c \ub418\uc5b4 \uc788\uae30<\/li>\n<\/ol>\n

            \ub54c\ubb38\uc774\ub2e4.<\/p>\n

              \n
            1. Execute count \uc640Fectch count \uac00 \ub3d9\uc77c\ud558\uac8c \ud06c\ub2e4\uace0\ud558\uba74ARRAY FETCH \uc0ac\uc6a9\uc744 \uace0\ub824\ud568.<\/li>\n
            2. fetch \ub41c rows \uc218: query + current = 1 : 4 \uc774\ud558\uc774\uba74SQL \ubb38\uc740 \uc801\uc808\ud788 \uc0ac\uc6a9\ub41c \uacbd\uc6b0\uc774\uace0,<\/li>\n<\/ol>\n

              row \uc218\uc5d0 \ube44\ud558\uc5ecquery + count \uac00 \uc0c1\ub2f9\ud788 \ud06c\uba74 \ubd80\uc801\ud558\uac8c \uc0ac\uc6a9\ub41cSQL \ubb38\uc774\ubbc0\ub85c<\/p>\n

              (count,sum,distinct \ub4f1Group function \uc744 \uc0ac\uc6a9\ud558\ub294\uacbd\uc6b0\ub294 \uc608\uc678) \ub2e4\uc74c \ub0b4\uc6a9\ub4e4\uc5d0\ub300\ud574\uc11c \uc7ac\uac80\ud1a0<\/p>\n

              \uac00 \ud544\uc694\ud558\ub2e4.<\/p>\n

              .INDEX \uc0ac\uc6a9,\uad6c\uc131\uc5ec\ubd80<\/p>\n

              .ROWID \uc0ac\uc6a9<\/p>\n

              .COST_BASED OPTIMIZER \uc0ac\uc6a9(ORACLE7)<\/p>\n

              .ARRAY FETCH \uc0ac\uc6a9<\/p>\n

              .SORTING \uc744 \ud53c\ud560\uc218 \uc788\ub294SQL \ubb38 \uad6c\uc0ac<\/p>\n

                \n
              1. Parse count,Execute count \uac00 \ube44\uc2b7\ud55c\uacbd\uc6b0 RELEASE_CURSOR,<\/li>\n<\/ol>\n

                HOLE_CURSOR OPTION \uc0ac\uc6a9\ud558\uc5ecParse count \ub97c \uc904\uc784.<\/p>\n","protected":false},"excerpt":{"rendered":"

                SQL TRACE FACILITY \ub294SQL \ubb38 \uc0ac\uc6a9\uc5d0\ub300\ud55c \uc131\ub2a5\uc744 \ubd84\uc11d\ud558\uae30\uc704\ud574\uc11c \uc0ac\uc6a9\ub41c\ub2e4. \uc774\ub7ec\ud55cSQL TRACE FACILITY \ub97c \uc774\uc6a9\ud558\uba74 \u00a0 \uac01SQL \ubb38\uc5d0 \ub300\ud574\uc11c \ub2e4\uc74c\uacfc \uac19\uc740 \uc815\ubcf4\ub97c \uc5bb\uc744\uc218 \uc788\ub2e4. . parse,execute,fetch count . CPU \uc640elapsed \uc2dc\uac04 . physical reads \uc640 logical reads . \ucc98\ub9ac\ub41crow \uc758 \uc218   SQL TRACE FACILITY \ub294SESSION \ud639\uc740INSTANCE \ub2e8\uc704\ub85c \ud560 \uc218 \uc788\uace0TRACE \uacb0\uacfc \ud654\uc77c\uc740tkprof UTILITY \u00a0 \uc5d0\uc758\ud574 […]<\/p>\n","protected":false},"author":1,"featured_media":2411,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"spay_email":""},"categories":[11],"tags":[901,900],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/haisins.synology.me\/wordpress\/wp-content\/uploads\/2018\/01\/34aed586ca86225f5bf558a2e882a752.jpg?fit=500%2C353","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2712"}],"collection":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2712"}],"version-history":[{"count":1,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2712\/revisions"}],"predecessor-version":[{"id":2713,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2712\/revisions\/2713"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/media\/2411"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2712"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2712"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2712"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}