1 / 67

PHP: Build and Deploy Mission Critical Applications with Oracle Database 11g

Luxi Chidambaran, Architect, Oracle NoCOUG Spring Conference. PHP: Build and Deploy Mission Critical Applications with Oracle Database 11g.

alagan
Download Presentation

PHP: Build and Deploy Mission Critical Applications with Oracle Database 11g

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Luxi Chidambaran, Architect, Oracle NoCOUG Spring Conference PHP: Build and Deploy Mission Critical Applications with Oracle Database 11g

  2. The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions.The development, release, and timing of any features or functionality described for Oracle’s products remain at the sole discretion of Oracle.

  3. Overview What is PHP? Connecting PHP to Oracle: The OCI8 Extension Massive Connection Scalability OCI8 Best Practices Client Result Cache Tracing and Monitoring High Availability In the News

  4. What is PHP?

  5. What is PHP? • Dynamically typed, open-source, scripting language • Rich functionality • Large number of extensions/interfaces • Commonly generates HTML output • Dynamic web pages • Widely Used • PHP is in Tiobe's top 5 languages (Statistics: www.tiobe.com)

  6. Hello World! <!DOCTYPE html> <html> <head> <title>PHP Test</title> </head> <body> <?php echo 'Hello World'; ?> </body> </html>

  7. Connecting PHP to Oracle: The OCI8 Extension

  8. What is OCI8? • Main Oracle Database extension for PHP • Open source and part of PHP <?php $c = oci_connect('un', 'pw', '//localhost/XE'); $s = oci_parse($c, 'select * from employees'); oci_execute($s); while ($row = oci_fetch_array($s))‏ foreach ($row as $item)‏ print $item; ?>

  9. Three Tier Web Model Apache PHP OCI8 Extension Oracle Client Libraries Oracle Database 9i, 10g, 11g Any platform Mid Tier 9iR2, 10g, 11g Oracle Client Any platform Web User

  10. PHP OCI8 Features • Execute SQL and PL/SQL • LOBs, including Temporary LOBs • Collections • REF CURSORS • Binds and Array Binds • Persistent Connections • Prefetching • Statement Caching • Meta Data • Password Changing • TimesTen!

  11. Getting PHP OCI8 1.4.5 • php.net • PHP 5.3 Source code, Windows binaries • PECL - PHP Extension Community Library • For updating PHP 4.3.9+ with latest OCI8 • http://oss.oracle.com/projects/php • RPMs for Linux with OCI8 • Unbreakable Linux Network • Oracle's Linux Support program‏ • OCI8 RPM available for PHP • Zend Server • Linux, Windows, Mac • Support from Zend

  12. PHP OCI8 Extension for Oracle Database • Windows DLLs available with PHP • Linux install with $ configure … --with-oci8=instantclient,$HOME/instantclient_11_2 or $ pecl install oci8 . . . Please provide the path . . . : instantclient,/home/lchidamb/instantclient_11_2 • Included in Zend Server

  13. Standard OCI8 connections • $c = oci_connect($un, $pw, $db); • Connection exists for script life • Subsequent oci_connect() in a script returns same DB connection • High Overhead • Connection establishment on first call in script • Connection teardown at end of script • Huge scaling issue

  14. Persistent OCI8 Connections • $c = oci_pconnect($un, $pw, $db); • Not automatically closed at end of script • Fast for subsequent connections • Holds resources when application idle • Second oci_pconnect() in script returns same connection • Some control configurable in php.ini • oci8.max_persistent • Number of connections per PHP process • oci8.persistent_timeout • “Idle” expiry time • oci8.ping_interval • Ping after retrieving from PHP cache

  15. Massive Connection Scalability

  16. Massive Connection Scalability • Use Non Persistent Connections? • High connect times • Burns CPU • Not scalable • Use Persistent Connections? • Majority idle • Excessive swapping, eventually exhausts RAM • Neither strategy really works at O(a few thousand) database connections • Of course, you could throw more hardware at it • Poor utilization of system resources • Poor utilization of $$

  17. Database Resident Connection Pool Oracle Database 11g Feature Not just for PHP Pool of dedicated servers on database machine Pool shared across mid-tier processes and middle-tier nodes Scales to tens of thousands of persistent connections Speeds up non-persistent connections Co-exists in all database server configurations Single instance, RAC

  18. Basic Functionality • Pooling is optionally enabled by DBA on server • Min, Max, Timeout etc. for Pool • Client connect string: • hostname/service:POOLED • (SERVER=POOLED) • Client directed to Database Resident Pool • Pooled Server “locked” when connection requested by client • Pooled Server “released” back to pool when client disconnects

  19. Dedicated Servers vs DRCP No Connection Pooling 11g Database Resident Connection Pooling

  20. Dedicated Servers Shared Servers DRCP Servers Database Servers 5000 * 4 MB 100 * 4 MB 100 * 4 MB Session Memory 5000 * 400 KB 5000 * 400 KB 100 * 400 KB DRCP Connection Broker Overhead 5000 * 35 KB Total Memory 22 GB 2.4 GB 610 MB Sample Sizing for 5000 Clients

  21. When to Use DRCP DRCP can be useful when any of the following apply: Large number of connections need to be supported with minimum memory usage on database host Applications mostly use same database credentials for all connections Applications acquire a database connection, work on it for a relatively short duration, and then release it Multiple web server hosts Connections look identical in terms of session settings, for example date format settings and PL/SQL package state Generally true for majority of web applications

  22. Starting and Configuring DRCP Start the pool: SQL> execute dbms_connection_pool.start_pool(); Optionally Configure the Pool: SQL> execute dbms_connection_pool.configure_pool( pool_name => 'SYS_DEFAULT_CONNECTION_POOL', minsize => 4, maxsize => 40, incrsize => 2, session_cached_cursors => 20, inactivity_timeout => 300, max_think_time => 600, max_use_session => 500000, max_lifetime_session => 86400);

  23. DRCP: System Components • Connection Broker • New in Oracle Database 11g • Oracle instance background daemon • Handles initial authentication • Handles subsequent connect/disconnect requests • Pooled Servers • New in Oracle Database 11g • Oracle instance background slave processes • Oracle 11g OCI Client library • DRCP aware

  24. DRCP: Connecting

  25. DRCP: Doing Work

  26. DRCP: After Disconnecting

  27. Using DRCP with PHP • No application code change required • Unchanged PHP API • Deployment decision to use DRCP • Application can still talk to other Oracle versions • Configure and start DRCP on Oracle Database 11g dbms_connection_pool.configure_pool dbms_connection_pool.start_pool • Set php.ini parameters oci8.connection_class = MYAPP

  28. PHP OCI8 Best Practices

  29. Example $a = array(1, 2, 3, 4, 5); // data to insert foreach ($a as $v) { $s = oci_parse($c, "insert into tab values ('".$v."')"); $r = oci_execute($s); }

  30. Bind Variables Too Many Hard Parses

  31. Lack of Bind Variables • Hard Parse is expensive • Creates shared cursor in SGA • Causes library cache latch contention • Causes shared pool contention • Causes scalability issues • Use Bind Variables • Reduces hard parses on the server • Reduces risk of SQL Injection: potential security issue

  32. Use Bind Variables $a = array(1, 2, 3, 4, 5);   // data to insert$s = oci_parse($c, 'insert into tab values (:bv)'); oci_bind_by_name($s, ':bv', $v, 20); foreach ($a as $v) {    $r = oci_execute($s);}

  33. Statement Caching: Minimize Soft Parses • PHP scripts issue the same statements repeatedly • PHP OCI8 creates session specific OCI statement handle and cursor context • Results in soft parse: repeats metadata processing • Use Statement Caching • OCI8 extension has client side statement cache • oci8.statement_cache_size = 20 • OCI Keeps frequently used session cursors open • Reduces soft parses on the Server • Cuts repeated metadata processing • Consumes less network bandwidth • Cuts code path in client/database tier

  34. Auto Commits • Beware. PHP OCI8 auto-commits by default • Causes more transactions, log flushes • Increases response time • Breaks atomicity of the transactions $a = array(1, 2, 3, 4, 5);   // data to insert$s = oci_parse($c, 'insert into tab values (:bv)'); oci_bind_by_name($s, ':bv', $v, 20); foreach ($a as $v) {$r = oci_execute($s); // this is an auto-commit! } Auto-commits are inefficient

  35. Turn off Auto-Commits $a = array(1, 2, 3, 4, 5);   // data to insert$s = oci_parse($c,  'insert into tab values (:bv)'); oci_bind_by_name($s, ':bv', $v, 20); foreach ($a as $v) {$r = oci_execute($s, OCI_NO_AUTO_COMMIT); } oci_commit($c); This is better: Only one explicit commit

  36. Stored Procedures • Bundle multiple SQL statements in one call • Use anonymous blocks or stored procedures • Eliminates roundtrips to database • Eliminates moving data between database and client • Can improve performance dramatically • Monitor roundtrips and bytes transferred stats • High values may indicate optimization opportunities

  37. Calling PL/SQL with OCI8 /* create or replace procedure myproc(d_p in varchar2, i_p in number) as begin insert into mytab (mydata, myid) values (d_p, i_p); end; */ <?php $c = oci_connect('hr', 'hrpwd', '//localhost/XE'); $s = oci_parse($c, "call myproc('mydata', 123)"); oci_execute($s); ?>

  38. Bulk Inserts (PHP Code) $s = oci_parse($c, 'begin mypkg.myproc(:c1); end;'); oci_bind_array_by_name($s, ":c1", $a, count($a), -1, SQLT_CHR); oci_execute($s);

  39. Bulk Inserts (PL/SQL Side) create or replace package mypkg as type arrtype is table of varchar2(20) index by pls_integer; procedure myproc(p1 in arrtype); end mypkg; create or replace package body mypkg as procedure myproc(p1 in arrtype) is begin forall i in indices of p1 insert into mytab values (p1(i)); end myproc; end mypkg;

  40. Prefetching Reduces Roundtrips Temporary buffer cache for query duration $r = oci_fetch_array(...); var_dump($r); // array('1000', 'Roma') $r = oci_fetch_array(...); var_dump($r); // array('1100', 'Venice') No DB access for next fetch Reduces round trips

  41. Prefetching is Enabled by Default • Enabled by default oci8.default_prefetch = 100 rows • Was 10 rows in OCI8 1.2 • Number of extra rows DB returns per fetch round-trip • Value set doesn't alter oci_fetch_* behavior • Reduces round trips • Can tune per statement: $s = oci_parse($c, 'select city from locations'); oci_set_prefetch($s, 87); oci_execute($s); while (($row = oci_fetch_array($s, OCI_ASSOC)) != false)‏ foreach ($row as $item)‏ print $item;

  42. REF CURSOR Prefetching • Works with Oracle 11.2 client libraries • Even to older DBs • Enabled by default • Can set size per statement

  43. REF CURSOR Prefetching /* create or replace procedure myproc(p1 out sys_refcursor) as begin open p1 for select * from tab; end; */ $s = oci_parse($c, "begin myproc(:rc); end;"); $rc = oci_new_cursor($c); oci_bind_by_name($s, ':rc', $rc, -1, OCI_B_CURSOR); oci_execute($s); oci_set_prefetch($rc, 200); oci_execute($rc); oci_fetch_all($rc, $res);

  44. Client Result Cache

  45. Consistent Caching Application Server Database 11g OCI Client Query Result Cachelike cache fusion between server and client • Caches SQL query results on client • Targeted at repetitive queries against read-mostly, read-only data

  46. Benefits • Easy to Use Cache • With 11gR2 • Can be turned on without application/SQL changes • CREATE TABLE foo (a NUMBER, b VARCHAR2(20)) RESULT_CACHE (MODE FORCE); • ALTER TABLE bar RESULT_CACHE (MODE FORCE); • With 11gR1 • Add /*+ result_cache */ hint in SQL • Frees application developers from building custom caches • Extends server-side result caching to client side memory • Leverages cheaper client-side memory • Each application has its working set cached locally • Achieves better performance by eliminating server roundtrips • Improves server scalability by saving server resources • Transparently maintains cache consistency with server side changes

  47. Identifying Candidate Queries for Client Result Caching from AWR

  48. Identifying Candidate Queries for Client Result Caching from AWR

  49. Identifying Candidate Queries for Client Result Caching from AWR • Identify top SELECT statements • BY CPU • BY Elapsed Time • Pick queries • On tables that are not updated often • With result sets can fit in available client memory • Ideal candidates for client result caching • Annotate base tables (using DDL) or add hints to specific queries • init.ora parameter • CLIENT_RESULT_CACHE_SIZE

More Related