carsten czarski oracle deutschland b.v. & co kgcarsten czarski oracle deutschland b.v. & co...

<Insert Picture Here>

APEX Hosting in the own company: apex.mycompany.com

Carsten Czarski

ORACLE Deutschland B.V. & Co KG

Carsten.Czarski@oracle.com

http://tinyurl.com/apexcommunity

http://sql-plsql-de.blogspot.com http://oracle-text-de.blogspot.com http://oracle-spatial.blogspot.com

http://plsqlexecoscomm.sourceforge.net http://plsqlmailclient.sourceforge.net

Twitter: @cczarski @oraclebudb

Everybody talks about Cloud Computing ...

Cloud Computing in Action since 2003 apex.oracle.com (data as of 2010/12)

Hardware:

– Dell PowerEdge 1950,

2 x DualCore 2,33GHz, 32G RAM

– ~ $4,500 USD (eBay Value: ~$1,000 USD)

– Database version 11.2.0.2

Performance statistics (for week of Dec 14, 2010)

– Total Page Events: 4,756,868 = 8 pro Sekunde

– Distinct Users: 3,958

– Number Workspaces: 11,002

– Number Applications: 37,171

APEX and Cloud Computing

APEX was designed for Hosting

– See "apex.oracle.com"

– See "cloud.oracle.com"

APEX Characteristics for Hosting

– Easy access to an APEX-Workspace

– Fast provisoning

– Developers can start immediately

– Central administration

– Chargeback possible – but required implementation

That IS "Cloud Computing" (PaaS)

APEX and Cloud Computing Workspaces as 'the foundation'

Independent and private – Independent development and administration

– Independent application management

– Independent user management

Central database – Central operations

– High availibility

– Backup & Recovery

WORKSPACE PROVISIONING APEX Hosting in the own company:

APEX Workspace-Provisioning

Request a workspace Self service

Administrators' View

Customized Provisioning An Example ...

APEX Application as the User Interface

Integration with SSO / Corporate Systems

Customized approval process

Customized Provisioning An Example ...

Custom Workspace Provisioning

– PL/SQL

– APEX_INSTANCE_ADMIN

– DBMS_SCHEDULER

– APEX_MAIL / UTL_SMTP / UTL_MAIL

Create an APEX Workspace

procedure create_workspace( p_workspace in varchar2, p_password in varchar2, p_schemaname in varchar2, p_admin_email in varchar2, p_admin_vname in varchar2, p_admin_nname in varchar2 ) is v_wsid number; pragma autonomous_transaction; begin wwv_flow_api.set_security_group_id(p_security_group_id=> 10); apex_instance_admin.add_workspace( P_WORKSPACE => p_workspace, P_PRIMARY_SCHEMA => p_schemaname, P_ADDITIONAL_SCHEMAS => p_schemaname ); commit; :

Create an APEX Workspace

select workspace_id into v_wsid from apex_workspaces where workspace = p_workspace; wwv_flow_api.set_security_group_id(p_security_group_id => v_wsid); apex_util.create_user( p_user_name => 'ADMIN', p_web_password => p_password, P_DEVELOPER_PRIVS => 'ADMIN:CREATE:DATA_LOADER:...', p_first_name => p_admin_vname, p_last_name => p_admin_nname, p_email_address => p_admin_email, p_default_schema => p_schemaname ); commit; apex_instance_admin.enable_workspace( p_workspace => p_workspace ); commit; end create_workspace;

RESOURCE MANAGEMENT

APEX Hosting in the own company:

Herausforderungen im APEX Betrieb

Many Workspaces in one APEX-Instance

– The Concept of Cloud Computing

How to make sure ...

... that nobody can bring down the system by

coding endless loops or similar ...?

... that important applications have higher

priority?

"apex.oracle.com" stand vor der gleichen Herausforderung ...

Database Resource Manager

Planning and Controlling Resource Usage

– Assign resources (CPU, etc) to database sessions

– Prioritizing particular sessions

Examples ...

– Cancel long running operations

– Limit CPU, I/O or parallel usage

Change the resource assignment of a database session

– Based on date and time

– Based on the APEX APP ID or PAGE ID

– Based on the APEX Workspace or User

Resource Manager requires Oracle Enterprise Edition

Resource Manager in APEX 4.2

Multi Level Plan Prioritizing Consumer Groups

Groups Level 1 Level 2 Level 3 Level 4

SYS_GROUP 75%

PRIO_HIGH 95%

PRIO_LOW 95%

OTHER_GROUPS 100%

• 75% CPU for the DBA (SYS_GROUP)

• Resources not used by the DBA go to ...

• 95% for Group PRIO_HIGH

• Resources not used by DBAs or PRIO_HIGH go to ...

• 95% for PRIO_LOW

• Resources not used by DBAs, PRIO_HIGH or PRIO_LOW go to ...

• all other sessions

Automatic group switching Limit resource usage ...

Groups CPU Switch after ... Switch to ...

APEX_HIGH 70% 10 sec. APEX_MEDIUM

APEX_MEDIUM 8% 120 sec. APEX_LOW

APEX_LOW 2% 1800 sec. CANCEL_SQL

OTHER_GROUPS 10%

• All sessions start in APEX_HIGH (70% CPU)

• After 10 seconds: Switch to APEX_MEDIUM (8% CPU)

• After another 2 minutes: Switch to APEX_LOW (2% CPU)

• After another 30 minutes: cancel the operation

This is the resource manager plan of "apex.oracle.com"!

CHARGEBACK: SOME THOUGHTS APEX Hosting in the own company:

Chargeback: Some thoughts

Charge to whom …?

– Developers (Workspaces)

– Users (Applications)

How to charge …?

– Used disk space

– Click count

– CPU Usage ...

– Others ...

Chargeback: Some thoughts

But to be honest ... not now ...

You want to have users, don't you ...?

HARDWARE AND SIZING APEX Hosting in the own company:

Load behaviour of an APEX Server

APEX is mostly "CPU-Bound"

– PL/SQL consumes CPU – and APEX is PL/SQL

– I/O based on application SQL – hard to predict

APEX is totally state-less

– APEX does implicit Commits

– Database session returns to the pool after page

rendering

– One database session serves mutliple APEX

sessions – no direct assignment.

Real World example apex.oracle.com as of 12/2010

Hardware:

– Dell PowerEdge 1950,

2 x DualCore 2,33GHz, 32G RAM

– ~ $4,500 USD (eBay Value: ~$1,000 USD)

– Database version: 11.2.0.2

Performance statistics (for week of Dec 14, 2010)

– Total Page Events: 4,756,868 = 8 pro Sekunde

– Distinct Applications; 3,791

– Distinct Users: 3,958

– Number Workspaces: 11,002

APEX CPU-Sizing

APEX is stateless: We can use a simple formula

Examples:

– (4 / 0.75) * 1 = 5,33 (= 460.000 per day)

– (4 / 0.75) * 2 = 10,66

Note:

– CPU-time is an average value

– Extreme values are to be expected

SELECT * FROM SH.SALES, SH.SALES, SH.SALES

Cores

Ø CPU-Time

per Request

x Max.

Response

time

= Amount of

"parallel" Users

APEX Hosting Example 1: Small system

Hardware

– 1 Node; up to 2 Sockets

– 4 – 8 Cores

– 16 – 32 GB RAM

Software

– Oracle Standard Edition

Parallel users based on Formula: 24 - 48*

Contents: APEX, APEX-Cloud, SQL, PLSQL, Oracle TEXT, XML DB,

Oracle Locator, Analytic Funktions, Flashback Query,

Features of OracleSE

Assumption: 0,25 Sec. CPU time per request; acceptable response time is 1.5 sec.

APEX Cloud Example 2: Enterprise System

Hardware

– 1 Node

– 12 – 32 Cores

– 48 – 128 GB RAM

Software

– Oracle Enterprise Edition

Parallel users based on Formula: 72 – 192*

Contents: Standard-System plus Resource management, Virtual Private

Database, Flashback Database technology

Assumption: 0,25 Sec. CPU time per request; acceptable response time is 1.5 sec.

More information

German APEX Community Page http://tinyurl.com/apexcommunity

– Eigene Verfahren für Workspace Provisioning http://apex.oracle.com/url/apxwsprov

– Ressourcenverwaltung für APEX Anwendungen http://apex.oracle.com/url/resman

– Arbeiten mit dem APEX Dictionary http://apex.oracle.com/url/apxrepo

APEX OTN http://otn.oracle.com/apex

Blogs, Twitter & Co http://www.apexblogs.info

Carsten.Czarski@oracle.com http://tinyurl.com/apexcommunity http://sql-plsql-de.blogspot.com http://oracle-text-de.blogspot.com http://oracle-spatial.blogspot.com http://plsqlexecoscomm.sourceforge.net http://plsqlmailclient.sourceforge.net Twitter: @cczarski @oraclebudb