SQL Command Editor

ST04 또는 ORA_SPACE 에서 Native Sql 을 날려 데이터를 확인 할 수 있다.

help.sap.com

SQL Command Editor

 

This sub-monitor in the SAP/Oracle Database Monitor lets you display the results of native Oracle select statements, which you enter in an editor.

If your statement selects only content of tables with owner SYS or PUBLIC, the monitor displays the result of the select statement. Otherwise the monitor executes statements and displays miscellaneous runtime information.

 

You choose  Performance Additional Functions SQL Command Editor in the DBA Cockpit.

You can use the editor in the following ways:

• Interactive

  The sub-monitor consists of an editor screen where you enter the SQL statement and a result screen that displays the result of the SQL statement.

• Batch

  The result is written to a spool list.

• Database table

  The result is written to a database table. This lets you store and read it later, or evaluate it with an SQL script. To do this, make an entry of the form &&&<RESULT_ID>&&& before the SQL query.

   Example

  &&&ATEST&&&SELECT * FROM V$INSTANCE

  The system stores the result with the ID ATEST in table ORA_SQLC_DATA. The table ORA_SQLC_HEAD stores the result structure.

  For more information, see SAP Note 1114566.

Structure

You can choose:

•  SQL Command Save or Load or Delete

  These functions ask you for an ID so that the statement can be saved to, loaded to, or deleted from the SAP database.

  The advantage of storing your statements is that you can share the same statement with other users.

•  SQL Command Parse

  This function starts a simple parser to check the syntax. This parser only makes sure that the monitor is able to generate a display structure and display the result of the statement. It also checks the owner of the tables and views that have to be read.

  It does not check the complete Oracle syntax. Therefore, it does not guarantee that the statement can be executed.

•  SQL Command Execute

  This function starts the parser, executes the statement, and displays the result of the statement.

•  SQL Command Execute Background

  This function creates a background job for the execution of the current SQL statement. It is best to execute in the background when you expect a long runtime for the statement.

  The system asks for some parameters as follows:

  1. Background job parameters

     • Statement ID

       This is the ID for saving the current SQL statement in the SAP database. The current statement is stored under this ID for later execution. If you use the default value $TMP_JOB, this overwrites an already existing statement with this ID. Use this default only if you are sure that there is no other job that uses this ID awaiting execution.

     • Print result of statement

       Select this box if you want to get the resulting data.

     • Print runtime data for statement

       Select this to receive the runtime information for the statement.

     • Start execution immediately

       Select this to start execution immediately.

     • Scheduled start at

       Select this to start execution at the date and time you enter.

     • No start after

       You can prevent the system from executing the statement after this time. For example, this is useful to prevent execution when you expect the system load to be high.

     • Print time

       Here you can select whether the results are sent to the SAP spooler only or if they are printed immediately.

  2. Background print parameters

     Here you can specify the printer and other print parameters.

•  Goto Job overview

  After starting a statement in background execution you can find your job here.

•  Goto Spool requests

  After starting a statement in background execution you can find your spool lists here.

• Save as local file or Load local file

  You can save your SQL statement to a local file or load an SQL statement from a local file into the editor.

Syntax

• A statement must have the following syntax:

  SELECT [ hint ] [ { DISTINCT | UNIQUE } | ALL ] select_list

  FROM table_reference [, table_reference]...

  [ WHERE condition ]

  [ hierarchical_query_clause ]

  [ group_by_clause ]

  [ HAVING condition ]

  [ { UNION | UNION ALL | INTERSECT | MINUS } ( subquery )]

  [ order_by_clause ]

  You can put comments between "/*+" and "*/"

• A select list must have the following syntax:

  { * |

  {[table_alias.]dbfieldname | expression} alias [,[table_alias.]dbfieldname | expression} alias] ... }

  An expression within this select list can use a calculation operator such as +, -, *, /, ||. Unary functions (LN, MIN, AVG ...), null, or numbers, are also allowed.

• A table reference must have the following syntax:

  {(select statement) [table_alias] | table [table_alias]}

• If you enter one of the following commands, you receive the same table information as if you had entered the command with the same name on Oracle with SQLPLUS:

  • DESC <table name>

  • DESCR <table name>

  • DESCRIBE <table name>

Otherwise the syntax follows the SQL standard.

Conventions and Restrictions

• Each column specified in the select list becomes a column in the output list.

• If a select list element is specified with a column alias, this alias is used as header text in the output list. Otherwise the program uses the field name of the select list element as header text. If a select list element is an expression (that is, without a database field), the alias is obligatory.

   Caution

  For every expression that is not a database field, use a column alias.

   Example

  The following statement cannot be processed because the only select list element is an expression.

  select to_char(startup_time,'YYYY-MM-DD-HH24.MI.SS') from v$instance …

  Since expressions are not always related to exactly one database field, the monitor requires an alias name:

  select to_char(startup_time,'YYYY-MM-DD-HH24.MI.SS') as startup from v$instance

• Every column alias that is specified in the select list of a sub-query can be used in the same way as a database field name in the select statement.

• If more than one table is specified in the from clause, the columns are matched to one table for reasons of uniqueness. If a column name occurs in more than one table, uniqueness cannot be guaranteed. In this case you have to specify a table alias before the column name (that is, database field name).

   Caution

  When more than one table is specified and column names that have to be outputted occur in more than one table, use a table alias.

   Example

  If both table_a and table_b have a field with identical name, field_1, you have to specify the table alias for uniqueness:

  select a.field_1 as tc_a, b.field_1 as tc_b from table_a a, table_b b

• The SAP List Viewer (ALV) does not display leading blanks that the Oracle LPAD editing function has inserted into the SQL statement. To display leading blanks, you need to switch to list output by choosing View in the ALV grid.

Example

Here are some examples to show features of this sub-monitor:

• This statement shows an interesting rate statistics for read, write, and hit using a sub-select:

  select LogicalIO, PhysicalReads, PhysicalReadsDirect,

  PhysicalReadsDirectLob, PhysicalWrites,

  round((1-(PhysicalReads-PhysicalReadsDirect-PhysicalReadsDirectLob)/

  (LogicalIO-PhysicalReadsDirect-PhysicalReadsDirectLob))*100,2)

  HitRatio

  from (select SUM(DECODE(Name,'session logical reads',Value,0)) LogicalIO,

  SUM(DECODE(Name,'physical reads',Value,0)) PhysicalReads,

  SUM(DECODE(Name,'physical writes',Value,0)) PhysicalWrites,

  SUM(DECODE(Name,'physical reads direct',Value,0)) PhysicalReadsDirect,

  SUM(DECODE(Name,'physical reads direct (lob)',Value,0))

  PhysicalReadsDirectLob

  from v$sysstat

  where name in ('session logical reads',

  'physical reads','physical reads direct',

  'physical reads direct (lob)','physical writes'))

• This statement shows runtime statistics for reading SAP table t100 and a rowid statistic:

  Select rowid, a.* from t100 a

• This statement shows how to use a table cast:

  select * from table(dbms_xplan.display())