<Insert Picture Here>
Performance Tuning for PHP with Oracle Databases Christopher Jones, Product Development, Oracle October 2007
Overview • What OCI8 does • Connection Management • Connection types • Performance tips • Statement Tuning • Binding • Caching • Transactions • Bulk operations • SQL and PL/SQL Tips • Tuning and Development Tools
OCI8 Extension
What is OCI8? • Main Oracle Database extension for PHP <?p hp $c = o ci _co nn ect ('u n' , ' pw ', '/ /l oca lh ost /X E') ; $s = o ci _pa rs e($ c, 's ele ct * fr om em pl oye es '); oci _ex ec ute ($ s); whi le ($ row = oc i_f et ch_ ar ray ($ s) ) fore ac h ( $r ow as $i tem ) pr in t $ it em; ?>
Three Tier Web Model Apache PHP OCI8 Extension Oracle Client Libraries Web User
Mid Tier 9i, 10g, 11g Any platform
Oracle Database 8i, 9i, 10g, 11g Any platform
OCI8 or PDO_OCI? • OCI8 • Stable and fast • Has support for Oracle 11g DB Resident Connection Pooling* • PDO • Time is coming rapidly, but perhaps not just yet • Bugs for PDO and PDO_xxx (for various database drivers) are outstanding • PDO_OCI doesn't support all features of OCI8
* Beta OCI8 extension available now
OCI8 Features • Execute SQL and PL/SQL • Long Objects (LOB), including Temporary LOBs • Collections • REFCURSORS • Binds and Array Binds • Persistent Connections • Prefetching • Statement Caching • Meta Data • Password Changing
OCI8 Extension • Refactored by Oracle and Zend in 2005 • Stable and fast • Contributed back to PHP community • Same API as original OCI8 in PHP 3 and PHP 4 • New php.ini parameters for • Persistent connection management • Row prefetching • Client side statement cache
Get the Refactored OCI8 • php.net • Source code, Windows Binaries • PECL - PHP Extension Community Library • Useful for updating PHP4 with new OCI8 • oss.oracle.com/projects/php • RPMs for Linux • Zend Core for Oracle • Linux, Solaris, Windows, AIX
Top DB Performance Mistakes • Bad connection management • Bad use of cursors and Shared Pool • Bad SQL • Use of non-standard initialization parameters • Getting DB I/O wrong • Redo Log Setup Problems • Serialization of data blocks • Long full table scans
Same is true for PHP applications too
Connection Management
OCI8 Connections oc i_ con ne ct () oc i_ new _c on nec t() oc i_ pco nn ec t()
Standard Connections $c = oci _c on nec t($ use rn am e, $p ass wo rd, $ db nam e);
• Second oci_connect() in script returns same
DB connection • Connection exists for script life • Can explicitly close
Multiple Unique Connections $c = oci _new _co nn ect ($ un, $ pw, $d b) ;
• Each oci_new_connect() returns a new DB
connection
• Use for independent operations
• Connection exists for script life • Can explicitly close
Persistent Connections are Cached oci_pconnect()
user:db:charset:privilege hr:XE:ALU32UTF8:normal system:XE:ALU32UTF8:sysdba
Second request fast
Persistent Connections $c = oci _pco nne ct ($u n, $p w, $d b);
• Not automatically closed at script completion • Fast for subsequent connections • But holds resources when application idle • Configurable in php.ini oci 8.max _pers isten t oci 8.per siste nt_ti meout oci 8.pin g_int erval
Maximum Number of Persistent Connections • php.ini: • • • •
oc i8 .ma x_ pe rsi ste nt = 1 Number of connections cached by each Apache process Using -1 means no limit If limit is reached, connections will succeed but will be treated like oci_connect() calls. Becomes obsolete with new Beta OCI8 extension • Can use 11g DB connection pool settings instead
Timeout for Unused Persistent Connections • php.ini:
oc i8 .pe rs is ten t_t ime ou t = 1 • Seconds an idle connection will be retained • Using -1 means no timeout
Round Trips Between PHP and Oracle
Round Trip
Tuning goal: minimize round trips
Pinging for Closed Persistent Connections • php.ini: • • • •
oc i8 .pi ng _i nte rva l = 6 0 Seconds that pass before oci_pconnect() connection is checked before being used New connection created if old one unavailable Generates “round trip” to DB For scalability, disable by setting to -1 • Applications need to recover from errors anyway
• With new Beta OCI8 use FAN events instead
Closing Connections • An oci _c los e( ) now closes connections from
oc i_c on nec t( ) and oci _n ew _co nn ect () • Unset related resources first $st id = nu ll; o ci_ clo se ($ c); • Was no-op, so check performance after upgrading • Or revert with php.ini
oc i8. ol d_o ci _c lose _s em ant ic s = 1 • Closing is good in many circumstances, e.g. • Long running scripts with little DB interaction • For using connection pooling
Connection Performance
Use the Right Function $c = oc i_ pconn ec t($ un , $ pw, $ db) ;
• Use oci_pconnect() • Doesn't need to create new physical DB connection
Pass the Character Set $c = oci _p co nne ct( $un , $p w, $d b, 'j a16 eu c' );
• Removes need for environment lookup • Improves even persistent connection
performance
Optimize Environment Setting fun ct ion m y_ con nec t($ un , $pw , $db ) { $c = o ci _p con ne ct($ un , $pw , $db ); $s = o ci _p ars e( $c, "a lt er se ssi on se t nls _da te _fo rm at= 'YY YY MM D D H H2 4: MI: SS '") ; oci _ex ec ut e($ s) ; ret urn $ c; }
Could be improved
Environment Setting: Process Environment • Set NLS_DATE_FORMAT in environment • All users get same format • Application deployment now has dependency
on environment • In Shell:
nl s_d at e_f or mat ='Y YY YM M DD HH 24 :MI :S S'
• PHP code: fun cti on my _c onn ect ($ un, $ pw, $ db ) { $c = o ci_ pc onn ect ($ un, $ pw, $ db ); retu rn $c ; }
Environment Setting: Trigger • Use a PL/SQL LOGON trigger cre ate o r r ep lac e t ri gge r my_ se t_ dat e af te r l og on on da tab as e beg in if ( us er = 'HR ') th en ex ec ute i mme di ate 'alt er se ss ion se t nls _d at e_f or mat = ''YY YY MM D D H H24 :M I:S S' ' '; end if ; end my _s et_ da te;
Environment Setting: Trigger • PHP Code is fun cti on my _c onn ect ($ un, $ pw, $ db ) { $c = o ci_ pc onn ect ($ un, $ pw, $ db ); retu rn $c ; }
LOGON Trigger • Fires only first time oci_pconnect() is called
after Apache process starts • This is when the DB connection is created • Subsequent oci_pconnect() calls get established connection from PHP cache
• Multiple SQL statements can be executed in
the procedure
• Reduces number of calls between PHP and DB
Connections with Oracle RAC • Tune auditing sequence generator SQL> a lter seque nce sy s.aud ses$ cache 1000 0; • Reduces data transfers between nodes • Also useful for non-RAC connection peaks
(RAC is “Real Application Clusters”)
Statement Tuning
SQL Statement Execution • Parse -
oc i_par se() Prepares a statement for execution
• Bind oc i_bin d_by_ name( ) Optionally binds variables in WHERE clause or to PL/SQL arguments • Execute - oc i_exe cute( ) The Database executes the statement and buffers the results • Fetch oc i_fet ch_ar ray() Optionally retrieves results from the database
Row Prefetching Set oci8.default_prefetch
PHP OCI8 Extension Beijing
Database Oracle Client Libraries Beijing Bern Bombay ...
Reduces round trips
OCI8 Prefetch Rows • php.ini
oci 8. de fau lt _pr efe tc h = 1 0 • Maximum number of rows in each DB "round trip" • Memory limit also set: 1024 * oci8.default_prefetch
• Value can also be set inline
oc i_s et _p ref et ch( $s, 1 00) ; • Improves query performance by reducing “round trips” • Rows are cached internally by Oracle • no need to change application
OCI8 Prefetch Rows • PHP code still returns user one row at a time $s = oci _p ars e( $c, 's el ect * fr om e mpl oy ees ') ; oci _e xec ut e($ s) ; oci _s et_ pr efe tc h($ s, 10 0); whi le ($row = oc i_ fet ch_ ar ray ($ s) ) fore ac h ( $r ow as $i tem ) pr in t $ it em;
Statement Caching: No Bind Variables select col from tab where v = 1 select col from tab where v = 2
select col from tab where v = 1
select col from tab where v = 2
Poor use of cache
1: select col from tab where v = 1 2: select col from tab where v = 2 Database cache
Bind Variables • Like "%s" print format specifiers • Application can re-execute statements with
different values • Database feature, always available • Improves overall database throughput • Helps prevent SQL Injection attacks
Statement Caching: Binding select col from tab where v = :bv
select col from tab where v = :bv
select col from tab where v = :bv
select col from tab where v = :bv
Increases performance and security
1: select col from tab where v = :bv Database cache
OCI8 Bind Example $s = oc from $e id = oc i_bin oc i_exe oc i_fet ec ho "N $e id = oc i_exe oc i_fet ec ho "N
i_par emplo 101; d_by_ cute( ch_al ame: 102; cute( ch_al ame:
se($c ,"sel ect l ast_n ame yees where empl oyee_ id = :eidb v"); name( $s, " :eidb v", $ eid); $s); l($s, $r); ".$r[ 'LAST _NAME '][0] ."\n" ; $s); // No ne ed to rep arse l($s, $r); ".$r[ 'LAST _NAME '][0] ."\n" ;
Statement Caching: Client Set oci8.statement_cache_size select col from tab
select col from tab
â&#x20AC;&#x153;Use statement 1â&#x20AC;?
1: select col from tab
1: select col from tab
select col from tab Client cache
Less traffic and CPU
Database cache
OCI8 Statement Cache • php.ini • • • • • •
oc i8. st ate me nt_ cac he _si ze = 20 Unit is number of statements Client side cache of statements Moves cache management load from DB to PHP side Reduces net traffic and context switches Uses memory on PHP side for cached statement handles Uses memory on DB for per-session cursors
Oracle Database 11g Also Has . . . • Oracle Database 11g introduces: • Server Result Cache • OCI Consistent Client Cache • PL/SQL Function Result Cache • OCI8 extension can use them automatically
Limiting Rows • Don't fetch more data than necessary • Use SQL to process data e.g. inline views • Row limiting query is: SE LECT last_ name FROM (SELEC T las t_nam e, ROW_NU MBER( ) OVE R (O RDER BY la st_na me) A S MYR FROM e mploy ees) WHE RE MY R BET WEEN 11 an d 20
Transaction Management • By default oci _ex ec ute () auto-commits • Unnecessarily committing • Can cause extra “round trips” to the DB • Use oci_ ex ec ute ($ s, OCI _D EFA UL T) • OC I_DEF AULT is not the default in PHP!
Commits can be Piggybacked oci _e xec ut e( $s1 , O CI_ DE FA ULT ); ... oci _e xec ut e( $s2 , O CI_ DE FA ULT ); ... oci _e xec ut e( $s3 ); ... // no ex pl ic it oci _co mm it () ne ede d
• Saves a round-trip • But can make code correctness harder • oci_execute() for queries will commit • Any DDL will commit
Non Transactional
fo reach ($a as $v ) { $s = oc i_par se($c , "i nsert into myta b val ues ( '".$v ."')" ); $r = oc i_exe cute( $s); }
Parse and commit per insert is slow (and insecure)
Transactional
$s = oc i_par se($c , 'in sert into mytab valu es (: bv)') ; oc i_bin d_by_ name( $s,': bv',$ v,20, SQLT_ CHR); fo reach ($a as $v ) $r = oci_e xecut e($s, OCI_ DEFAU LT); oc i_com mit($ c);
This is better: one parse, one commit
Bulk Inserts (PHP Code)
$s = oc i_par se($c , 'be gin m ypkg. mypro c(:c1 ); en d;'); oc i_bin d_arr ay_by _name ($s, ":c1" , $a, cou nt($a ), 1 , SQL T_CHR ); oc i_exe cute( $s);
Could be fastest: just three calls
Bulk Inserts (PL/SQL Side) cre at e o r re pla ce pac ka ge my pk g a s typ e a rr ty pe is tab le o f v ar cha r2 (20 ) inde x by pl s_ inte ge r; pro ced ur e myp ro c(p1 i n arr ty pe) ; end m ypk g; cre at e o r re pla ce pac ka ge bo dy my pk g a s pro ced ur e myp ro c(p1 i n arr ty pe) i s beg in fora ll i in i ndi ce s o f p 1 in se rt in to my tab v alu es (p 1( i)) ; end my pr oc ; end m ypk g;
Demonstration of OCI8
SQL and PL/SQL Tips
SQL Optimizer • Make sure table statistics are up to date • Use the PL/SQL DB MS_ST ATS package • Can override optimizer with “hints” in
statements:
select /*+ FULL( j) */ from j ob_hi story j;
PL/SQL Tips #1 These are suggestions for evaluation. They are in no particular order. • Wrap multiple SQL statements in a PL/SQL block to minimize number of oci_parse() and oci_execute() calls • Use SQL constructs to fetch minimal amount of data required • Optimize SQL in PL/SQL and calls to PL/SQL from SQL • If you call a PL/SQL function in a query, use a function based index
PL/SQL Tips #2 • Use PL/SQL bulk operations FORALL and BULK
COLLECT instead of loops • Use built in functions for string operations and use REGEXP_* functions for pattern matching • Take advantage of short circuit evaluation in tests • Minimize type conversions • Use built in types like PLS_INTEGER, SIMPLE_INTEGER and BINARY_FLOAT • Declare VARCHAR2's > 4000 bytes to trigger delayed space allocation of actual data size
PL/SQL Tips #3 • Group related procedures in same package so
all are loaded • Pin packages in shared memory • Use 11g DBMS_HPROF profiler • Use Native compilation • Use global application context or package state to cache values • Use DBMS_SCHEDULER for delayed jobs • Use Change Notification for event handling • No prefetching with REF CURSORS
Old Skool Tuning
SQL Tuning – SQL*Plus AUTOTRACE
Application Tuning • SQL_TRACE • Log SQL statement execution
• TIMED_STATISTICS • Gather timing information
• TKPROF • Trace file formatter produces text report
Looking at TKPROF Output • Identify “hard” parsing • Statements should be executed more times than parsed • Statements executed only once may need to
use bind variables • Look for slow queries • High disk reads
• DB Shared cache might be too small
• Low CPU time but high user time • Waiting for some resource
Database Tuning • STATSPACK • SQL scripts taking snapshot of DB performance • Reports parsing, caching, IO, latches, . . .
New Skool Tuning
New Skool Tools • GUI Tuning Tools • Application Express (aka HTML DB) • SQL Developer • Oracle Enterprise Manager • Automatic Performance Tools ( >= 10g) • Automatic Workload Repository (AWR) • Automatic Database Diagnostic Monitor (ADDM) • SQL Tuning Advisor • SQL Access Advisor
Oracle Database XE • Small footprint starter database • Up to 4GB user data, 1 core/processor, 1GB RAM • 1 database instance per machine • Free to download, develop, deploy, distribute • 32-bit Linux and Windows • Free OTN online community forum for support • Uses native install on Windows and Linux • Application Express GUI
Application Express SQL Worksheet
APEX Database Tuning
APEX Top SQL Report
SQL Developer • Browse, create and
• • •
•
update schema objects Create, edit and debug PL/SQL SQL Worksheet (commands, scripts) Run predefined reports or create new reports Add functionality using the extensible platform
SQL Developer • Great user experience • Just unzip the
download to install • Supports Windows, Mac OS, and Linux • Supports Oracle DB 9.2.0.1 or later • Free and supported
SQL Developer
Database Tuning: AWR • Create pair of snapshots before and after load $ s qlplu s / a s sys dba SQL > exe c db ms_wo rkloa d_rep osito ry.cr eate_ snaps hot() ; [Run application and then take second snapshot] SQL > exe c db ms_wo rkloa d_rep osito ry.cr eate_ snaps hot() ;
• Create AWR Report SQL > @$O RACLE _HOME /rdbm s/adm in/aw rrpt. sql • Prompts for start and end snapshots • Creates HTML or text report
AWR
Database Tuning: ADDM ● ● ●
● ●
ADDM analyzes pairs of AWR snapshots Provides recommendations Has internal goal to reduce a “throughput” metric of cumulative DB request time View in Oracle Enterprise Manager View text report
SQ L> @$ ORACL E_HOM E/rdb ms/ad min/a ddmrp t.sql ● Prompts for start and end AWR snapshots ● Creates a text file report
ADDM … FINDING 1: 100% impact (494 seconds) Significant virtual memory paging was detected on the host operating system. RECOMMENDATION 1: Host Configuration, 100% benefit (494 seconds) ACTION: Host operating system was experiencing significant paging but no particular root cause could be detected. Investigate processes that do not belong to this instance running on the host that are consuming significant amount of virtual memory. Also consider adding more physical memory to the host. FINDING 2: 75% impact (369 seconds) SQL statements consuming significant database time were found. RECOMMENDATION 1: SQL Tuning, 21% benefit (101 seconds) ACTION: Investigate the SQL statement with SQL_ID "cfz686a6qp0kg" for possible performance improvements. RELEVANT OBJECT: SQL statement with SQL_ID cfz686a6qp0kg and PLAN_HASH 3679656590 select o.obj#, u.name, o.name, t.spare1,
Benchmark During Development • Benchmark from the start • Set metrics • Test as you create the application • Instrument code • Use efficient architecture and algorithms • Scale up and test • This will show weaknesses
Oracle Technology Network PHP Developer Center • Articles • Install guides • Underground
PHP and Oracle Manual • Online forum • PHP RPMs • Oracle JDeveloper 10g PHP extension oracle.com/technology/php
Oracle Resources • Oracle Techology Network (OTN) is free
PHP Developer Center www.oracle.com/technology/php • Underground PHP and Oracle Manual • Articles, FAQs, links to blogs, JDeveloper PHP Extension, PHP RPMs
• SQL and PL/SQL Questions asktom.oracle.com • Information christopher.jones@oracle.com blogs.oracle.com/opal • ISVs and hardware vendors oraclepartnernetwork.oracle.com
The preceding 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â&#x20AC;&#x2122;s products remain at the sole discretion of Oracle.