sqlt

哎呀我的天吶發表於2015-02-09
SQL

點選(此處)摺疊或開啟

  1. [oracle@test install]$ ll sqcrea*
  2. -rw-r--r-- 1 oracle oinstall 4771 Oct 31 02:00 sqcreate.sql
  3. [oracle@test install]$ pwd
  4. /home/oracle/sqlt/install

點選(此處)摺疊或開啟

  1. [oracle@test install]$ sqlplus / as sysdba

  3. SQL*Plus: Release 10.2.0.4.0 - Production on Thu Feb 5 03:54:30 2015

  5. Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


  8. Connected to:
  9. Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
  10. With the Partitioning, OLAP, Data Mining and Real Application Testing options

  12. 03:54:30 SQL> @sqcreate    

  14. zip error: Nothing to (SQLT_installation_logs_archive.zip)

  16. zip error: Nothing to (SQLT_installation_logs_archive.zip)

  18. PL/SQL procedure successfully completed.

  20. Elapsed: 00:00:00.00

  22. Specify optional Connect Identifier (as per Oracle Net)
  23. Include "@" symbol, ie. @PROD
  24. If not applicable, enter nothing and hit the "Enter" key.
  25. You *MUST* provide a connect identifier when installing
  26. SQLT in a Pluggable Database in 12c
  27. This connect identifier is only used while exporting SQLT
  28. repository everytime you execute one of the main methods.

  30. Optional Connect Identifier (ie: @PROD): @TestDB12


  33. PL/SQL procedure successfully completed.

  35. Elapsed: 00:00:00.00

  37. PL/SQL procedure successfully completed.

  39. Elapsed: 00:00:00.02

  41. Define SQLTXPLAIN password (hidden and case sensitive).



  45. Elapsed: 00:00:00.05

  47. Password for user SQLTXPLAIN:
  48. Re-enter password:


  51. PL/SQL procedure successfully completed.

  53. Elapsed: 00:00:00.01
  54. ... please wait

  56. TABLESPACE         FREE_SPACE_MB
  57. ------------------------------ -------------
  58. TEST                 30125
  59. EXAMPLE              32700
  60. USERS                 32734

  62. Elapsed: 00:00:00.49

  64. Specify PERMANENT tablespace to be used by SQLTXPLAIN.

  66. Tablespace name is case sensitive.

  68. Default tablespace [USERS]:

  70. PL/SQL procedure successfully completed.

  72. Elapsed: 00:00:00.04
  73. ... please wait

  75. TABLESPACE
  76. ------------------------------
  77. TEMP001

  79. Elapsed: 00:00:00.01

  81. Specify TEMPORARY tablespace to be used by SQLTXPLAIN.

  83. Tablespace name is case sensitive.

  85. Temporary tablespace [TEMP001]:

  87. PL/SQL procedure successfully completed.

  89. Elapsed: 00:00:00.01

  91. The main application user of SQLT is the schema
  92. owner that issued the SQL to be analyzed.
  93. For example, on an EBS application you would
  94. enter APPS.
  95. You will not be asked to enter its password.
  96. To add more SQLT users after this installation
  97. is completed simply grant them the SQLT_USER_ROLE
  98. role.

  100. Main application user of SQLT:

  102. PL/SQL procedure successfully completed.

  104. Elapsed: 00:00:00.00

  106. SQLT can make extensive use of licensed features
  107. provided by the Oracle Diagnostic and the Oracle
  108. Tuning Packs, including SQL Tuning Advisor (STA),
  109. SQL Monitoring and Automatic Workload Repository
  110. (AWR).
  111. To enable or disable access to these features
  112. from the SQLT tool enter one of the following
  113. values when asked:

  115. \"T\" if you have license for Diagnostic and Tuning
  116. \"D\" if you have license only for Oracle Diagnostic
  117. \"N\" if you do not have these two licenses

  119. Oracle Pack license [T]:

  121. PL/SQL procedure successfully completed.

  123. Elapsed: 00:00:00.00

  125. PL/SQL procedure successfully completed.

  127. Elapsed: 00:00:00.00

  129. PL/SQL procedure successfully completed.

  131. Elapsed: 00:00:00.00

  133. PL/SQL procedure successfully completed.

  135. Elapsed: 00:00:00.00

  137. PL/SQL procedure successfully completed.

  139. Elapsed: 00:00:00.01

  141. PL/SQL procedure successfully completed.

  143. Elapsed: 00:00:00.00

  145. PL/SQL procedure successfully completed.

  147. Elapsed: 00:00:00.01

  149. PL/SQL procedure successfully completed.
建立好了就能連了

點選(此處)摺疊或開啟

  1. 04:51:08 SQL> conn SQLTXPLAIN/oracle
  2. Connected.
  3. 04:51:18 SQL> select * from tab;

  5. TNAME             TABTYPE    CLUSTERID
  6. ------------------------------ ------- ----------


點選(此處)摺疊或開啟

  1. 04:16:24 SQL> select * from table(dbms_xplan.display_cursor(null,null));

  3. PLAN_TABLE_OUTPUT
  4. --------------------------------------------------------------------------------------------------------------------------
  5. SQL_ID    1j2cqw0sxv467, child number 0
  6. -------------------------------------
  7. select a.id , b.ename, b.hiredate from c_lob a , empt b where a.id =
  8. b.empno

  10. Plan hash value: 646364930

  12. ------------------------------------------------------------------------
  13. | Id | Operation       |  Name |  Rows | Bytes | Cost (%CPU)| Time     |
  14. ------------------------------------------------------------------------
  15. | 0 | SELECT STATEMENT |       |       |       | 73747 (100)|          |
  16. |* 1| HASH JOIN        |       |    14 |   476 | 73747 (1)  | 00:14:45 |
  17. | 2 | TABLE ACCESS FULL|  EMPT |    14 |   406 |     3 (0)  | 00:00:01 |
  18. | 3 | TABLE ACCESS FULL| C_LOB |  1008K|  4922K| 73732 (1)  | 00:14:45 |
  19. ------------------------------------------------------------------------

  21. Predicate Information (identified by operation id):
  22. ---------------------------------------------------

  24.    1 - access("A"."ID"="B"."EMPNO")

  26. Note
  27. -----
  28.    - dynamic sampling used for this statement

點選(此處)摺疊或開啟

  1. oracle@test11g:/home/oracle/sqlt/run> pwd
  2. /home/oracle/sqlt/run

點選(此處)摺疊或開啟

  1. -rw-r--r-- 1 oracle oinstall 4431 Oct 30 14:00 sqltxtract.sql + sql_id
這個不真正的跑只是將你的執行計劃，資訊拿出來。


點選(此處)摺疊或開啟

  1. [oracle@test run]$ sqlplus / as sysdba

  3. SQL*Plus: Release 10.2.0.4.0 - Production on Thu Feb 5 04:27:55 2015

  5. Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


  8. Connected to:
  9. Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
  10. With the Partitioning, OLAP, Data Mining and Real Application Testing options

  12. 04:27:56 SQL> @sqltxtract 1j2cqw0sxv467

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29990276/viewspace-1431181/，如需轉載，請註明出處，否則將追究法律責任。

相關文章