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())

저작자표시 비영리 동일조건 (새창열림)