Retail DB Ops Console

Customers are responsible for both tuning their customizations and monitoring their CPU and storage capacity utilization. The Retail DB Ops Console provides customers the tools necessary to fulfill this responsibility. Additional capabilities provided in various Oracle DB packages can provide more detailed metrics and statistics. Consult Oracle documentation for further information.

Home

The Retail DB Ops Console can be accessed from Retail Home’s Application Navigator. The home page can be accessed by a ‘Viewer’ having RDS_MANAGEMENT_VIEWER or RDS_MANAGEMENT_VIEWER_PREPROD roles.

The Home page gives a quick view of recent data for the following:

• Latest Generated AWR Reports.

• Current Top SQL

• DBMS Jobs

Figure 4-11 DB Ops Console

All features can be accessed from the left panel.

Figure 4-12 DB Ops Console Menu

Top SQL

Top SQL screen displays a list of active SQL instances and their details that the snap process collects from SQL statements. The instances also can be filtered based on the Session States – ON CPU and WAITING. This page can be accessed by a ‘Viewer’ having RDS_MANAGEMENT_VIEWER or RDS_MANAGEMENT_VIEWER_PREPROD roles.

Figure 4-13 Top SQL

Clicking on the SQL ID link opens up a window with more details on the SQL Instance.

Figure 4-14 SQL Instance Viewer

Clicking on the SQL Full Text link shows the complete SQL statement.

Figure 4-15 SQL Text Viewer

DBMS Jobs

The DBMS Jobs table lists the available DB jobs and their details. This page can be accessed by a ‘Viewer’ having RDS_MANAGEMENT_VIEWER or RDS_MANAGEMENT_VIEWER_PREPROD roles.

Figure 4-16 DBMS Jobs

Clicking on the Job Name opens a window with detailed job log and job run details. For example, the job log and job run details for ‘RDS_RDS_MANAGEMENT_GRANT_JOB’ is as shown below.

Figure 4-17 DBMS Scheduler Job Logs

Figure 4-18 DBMS Scheduler Job Run Details

Database Metrics

Database Metrics shows the following graphical metrics for this environment’s Oracle APEX and database performance. This page can be accessed by an ‘Administrator’ having RDS_MANAGEMENT_ADMINISTRATOR or RDS_MANAGEMENT_ADMINISTRATOR_PREPROD roles.

CPU Utilization

Figure 4-19 CPU Utilization

Storage Utilization

Figure 4-20 Storage Utilization

A more detailed view of storage consumption can be obtained using the following query in the SQL Commands page of the SQL Workshop in the APEX UI.

select owner, cast(sum(bytes) / power(1024,2) as integer) MB from dba_segments where owner like '%_RDS%' group by owner order by owner

Sessions

Figure 4-21 Sessions

Execute Count

Figure 4-22 Execute Count

Running Statements

Figure 4-23 Running Statements

Queued Statements

Figure 4-24 Queued Statements

APEX Page Load Time

Figure 4-25 APEX Page Load Time

APEX Page Events

Figure 4-26 APEX Page Events

Application Properties

The Application Properties page lists the available application properties including Oracle APEX SMTP settings. The Application Properties are stored as key-value pair, e.g., oracle.apex.setting.smtp_from: [e-mail]. This page can be accessed by an ‘Administrator’ having RDS_MANAGEMENT_ADMINISTRATOR or RDS_MANAGEMENT_ADMINISTRATOR_PREPROD roles.

Note:

Roles are synonymous with OCI groups. An assignment to an OCI group may take up to 1 hour to propagate.

Figure 4-27 Application Properties

The value for the key can be updated using the Edit window.

Figure 4-28 Edit Property

Sending Email from APEX

To enable sending email from APEX, you must have access to an SMTP server. Provide values for the following Application Properties:

• oracle.apex.setting.smtp_host_address

• oracle.apex.setting.smtp_username

• oracle.apex.setting.smtp_password

• oracle.apex.setting.smtp_host_port

• oracle.apex.setting.smtp_from

Your settings can be verified by attempting to send a test email by clicking on the ‘Send a Test Mail’ button. Once you have verified your settings, you can use the APEX_MAIL package to send emails from Oracle APEX applications.

The APEX_MAIL_QUEUE will contain any queued email messages. The APEX_MAIL_LOG will contain the disposition of sent mail. Specifically, it will indicate whether an email message was sent successfully or not. See APEX_MAIL package documentation for additional details.

Figure 4-29 Send Test Email