Latest content
Explore and discover our latest tutorials
Technical Articles
Tech Articles from your friends at Oracle and the developer community.
PHP Web Auditing, Authorization and Monitoring with Oracle Database
Learn to use Oracle Database's "client identifier" feature in your PHP applications.
The OCI8 extension for the PHP language lets applications set a small string identifier token on each database connection. This "client identifier" can be used by Oracle Database to distinguish between individual web application users who all connect to the database using one common set of database credentials. For example, every page in a web site might physically connect to the database as the same database user PHPUSER. If two different people 'Chris' and 'Alison' are using the site, these two user names can be set as their respective client identifiers and be passed into the database.
By associating a unique client identifier with each web user Oracle Database can:
- Provide an audit trail on individual web users
- Automatically apply rules to individual web users to restrict data access
- Monitor and trace applications per web user
Setting Client Identifiers
Each PHP file in a typical Oracle PHP application calls oci_connect() with an identical database user name. Once the application's own authentication system decides a particular web user is OK, then a unique token is passed back and forth in HTTP responses and requests so that the web user doesn't have to re-authenticate each time a new web page is loaded.
Implementing application level authentication and passing PHP session information need careful design to ensure security. Setting client identifiers in the application also requires care to ensure consistency of use. The overall nature of stateless web applications that utilize shared database connections means application code integrity is a very important part of ensuring data security. This article gives advice on when to set client identifiers, and on how using them helps development and management of web sites. It does not cover PHP authentication or session handling best practices.
Client identifiers should be set with oci_set_client_identifier() after connecting but before executing any statements or OCI8 calls on behalf of the web user. At its most basic, the client identifier could be the web user's name stored in PHP's session data by a previous authentication request:
session_start();
$c = oci_pconnect('phpuser', 'welcome', 'localhost/orcl');
oci_set_client_identifier($c, $_SESSION['app_user_name']);
. . .
If the identity of the end user alters during the run time of the script (perhaps if PHP is executing a long running command-line process, or perhaps in an administrative web page that runs different components representing different end users) then oci_set_client_identifier() can be called at each point the end-user identity changes:
$c = oci_pconnect('phpuser', 'welcome', 'localhost/orcl');
$myuser = 'Chris';
oci_set_client_identifier($c, $myuser);
. . .
$myuser = 'Alison';
oci_set_client_identifier($c, $myuser);
. . .
In practice, consider using more secure values for identifiers.
Client identifiers can be set when using oci_connect(), oci_new_connect(), or oci_pconnect() connection calls. Identifiers can be used when the database is configured to use any of the three types of server processes: "Dedicated" servers, "Shared" servers, and when using Database Resident Connection Pooling (DRCP) servers.
The oci_set_client_identifier() function was added in PHP OCI8 1.4 (first included in PHP 5.3.1). With older versions of OCI8 you can use the PL/SQL DBMS_SESSION package instead:
session_start();
$c = oci_pconnect('phpuser', 'welcome', 'localhost/orcl');
$s = oci_parse($c, "begin dbms_session.set_identifier(:id); end;");
oci_bind_by_name($s, ":id", $_SESSION['app_user_name']);
oci_execute($s);
The oci_set_client_identifier() function is preferred because unlike DBMS_SESSION.SET_IDENTIFIER it doesn't force a database round-trip request-and-response. With oci_set_client_identifier the identifier is piggy-backed on any subsequent OCI8 call that actually does reach the database from PHP. Unnecessary round-trips slow down each PHP page and impact application scalability.
To set the identifier in PHP frameworks, you will currently need to call OCI8 directly. In Zend Framework this could conceptually be like:
$auth = Zend_Auth::getInstance();
if ($auth->hasIdentity()) {
$db = $this->getAdapter();
$conn = $db->getConnection();
if ($conn) {
oci_set_client_identifier($conn, $auth->getIdentity()->USERNAME);
}
}
In practice a separation of responsibilities would be preferred.
PHP OCI8 does not clear the client identifier at the end of an HTTP request since the overhead of a round-trip to clear the value would impact scalability of every application. This is not detrimental for standard OCI8 connections since the database connection is destroyed at the end of the HTTP request and the identifier value is cleared as a result. However identifiers may remain in effect across web requests that use persistent connections. To avoid an incorrect or no identifier being recorded by the database, all PHP files that connect to the database should set the identifier so it is correct for the duration of the request's execution. If this cannot be guaranteed, then every script that sets the client identifier should forcefully clear it at the script end with:
$s = oci_parse($c, "begin dbms_session.clear_identifier; end;");
oci_execute($s);
This causes a round-trip to the database.
PHP's oci_set_client_identifier() function corresponds to setting Oracle's C level OCI_ATTR_CLIENT_IDENTIFIER attribute. Oracle literature on this, and on PL/SQL's equivalent DBMS_SESSION.SET_IDENTIFIER, provide good references about client identifiers.
A Sample Application
A sample PHP "Parts" application illustrates how client identifiers can be used in the OCI8 extension. Overall, the application shows an inventory of electrical and plumbing parts. An application-level authentication system handles web user logins. For successful logins, an identifier unique for each web user is passed between HTTP requests in the PHP session data. It is used for the client identifier value. The application has just enough complexity so the Oracle technologies being discussed are not abstract, but it is no where near a production example. The sample application is simply intended to show the relationship between the web user and the database user, and to show how a client identifier can be used in the database. PHP session management requires careful design to minimize security issues. There are many external references discussing this problem which should be closely studied by every PHP developer. Michael McLaughlin's Database-Based Authentication for PHP Apps is a good place to begin reading more.
The core of the Parts application is a setup.sql file that creates the database objects. All the PHP scripts in the application will connect to the database using the PHPUSER schema, which owns the PARTS application table. The SQL script creates a second user PHP_SEC_ADMIN to hold security information about the application. This user is given some extra database privileges needed for the auditing example, shown later. The PHP_AUTHENTICATION table contains the application user names and passwords. Query access on this table is granted to the PHPUSER user so the PHP application only has to open one connection to the database, but that connection cannot modify the security information.
-- setup.sql
set echo on
-- Create PHP application user
connect system/welcome
-- Create the PHP application user
drop user phpuser cascade;
create user phpuser identified by welcome;
grant connect, resource to phpuser;
alter user phpuser default tablespace users
temporary tablespace temp account unlock;
-- Create user owner security information about the application
drop user php_sec_admin cascade;
create user php_sec_admin identified by welcome;
alter user php_sec_admin default tablespace system
temporary tablespace temp account unlock;
grant create procedure, create session, create table,
resource, select any dictionary to php_sec_admin;
connect phpuser/welcome
-- "Parts" table for the application demo
create table parts
(id number primary key,
category varchar2(20),
name varchar2(20));
insert into parts values (1, 'electrical', 'lamp');
insert into parts values (2, 'electrical', 'wire');
insert into parts values (3, 'electrical', 'switch');
insert into parts values (4, 'plumbing', 'pipe');
insert into parts values (5, 'plumbing', 'sink');
insert into parts values (6, 'plumbing', 'toilet');
commit;
connect php_sec_admin/welcome
-- Authentication table with the web user user names & passwords.
-- A real application would NEVER store plain-text passwords but this
-- article is about uses of client identifiers and not about
-- authentication.
create table php_authentication
(app_username varchar2(20) primary key,
app_password varchar2(20) not null);
insert into php_authentication values ('chris', 'tiger');
insert into php_authentication values ('alison', 'red');
commit;
grant select on php_authentication to phpuser;
Production applications would not use such simple passwords and would never store clear text passwords in tables. Applications could do end user authentication in a number of ways, including using LDAP.
Each script in the PHP application needs to know the Oracle DB credentials so they are stored in a common include file dbinfo.inc.php:
<?php
// dbinfo.inc.php
// All connections to the database use these credentials
define("ORA_CON_UN", "phpuser");
define("ORA_CON_PW", "welcome");
define("ORA_CON_DB", "localhost/orcl");
?>
In real life, consider using Oracle Wallet Manager and connecting with OCI_CRED_EXT instead of hard coding the database password. The operating system account for application administration, and the owner of the web server processes should be the only users with access to the wallet.
The application login page login.php is a typical simple PHP script that when first loaded displays a form:
The login.php file looks like:
<?php
// login.php
require_once('./dbinfo.inc.php');
session_start();
function login_form($message)
{
echo <<<EOD
<body style="font-family: Arial, sans-serif;">
<h2>Login Page</h2>
<p>$message</p>
<form action="login.php" method="POST">
<p>Username: <input type="text" name="username"></p>
<p>Password: <input type="text" name="password"</p>
<input type="submit" value="Login">
</form>
</body>
EOD;
}
if (!isset($_POST['username']) || !isset($_POST['password'])) {
login_form('Welcome');
} else {
// Check validity of the supplied username & password
$c = oci_pconnect(ORA_CON_UN, ORA_CON_PW, ORA_CON_DB);
// Use a "bootstrap" identifier for this administration page
oci_set_client_identifier($c, 'admin');
$s = oci_parse($c, 'select app_username
from php_sec_admin.php_authentication
where app_username = :un_bv
and app_password = :pw_bv');
oci_bind_by_name($s, ":un_bv", $_POST['username']);
oci_bind_by_name($s, ":pw_bv", $_POST['password']);
oci_execute($s);
$r = oci_fetch_array($s, OCI_ASSOC);
if ($r) {
// The password matches: the user can use the application
// Set the user name to be used as the client identifier in
// future HTTP requests:
$_SESSION['username'] = $_POST['username'];
echo <<<EOD
<body style="font-family: Arial, sans-serif;">
<h2>Login was successful</h2>
<p><a href="application.php">Run the Application</a><p>
</body>
EOD;
exit;
}
else {
// No rows matched so login failed
login_form('Login failed. Valid usernames/passwords ' .
'are "chris/tiger" and "alison/red"');
}
}
?>
If you copy this code, make sure the "heredoc" EOD tokens are at the very start of their lines.
For form submission, the script calls back to itself, which now validates the entered user name and password against the users in the PHP_AUTHENTICATION table. A client identifier of admin is set as a bootstrap value since at this initial point we don't know if we have a valid end user. Also the login script is an administrative component not doing any actual application work on behalf of an end user.
From the login page, authenticated users can click to the application inventory page:
The user name is passed to the application page in PHP session data as $_SESSION['username']. This value will be used as the client identifier for the web user. In a real application a less obvious identifier would be recommended. For example, as part of application authentication for a successful end-user login, an initial look-up query or PL/SQL function could return a pre-computed obscure value to be used as the user's client identifier. This value would then be stored in the PHP session information for use in subsequent "real" application work. An obscure value would make it harder for attackers to predict identifier values. Also an identifier could be quickly changed if there was ever a concern about the authenticity of HTTP requests using it.
The application page application.php checks that the user is authenticated - this application's definition of an authenticated user is simply that a user name is set. The code then sets the client identifier and shows the inventory list by querying the PARTS table:
<?php
// application.php
require_once('./dbinfo.inc.php');
session_start();
// Check the user is logged in according to our application authentication
if (!isset($_SESSION['username'])) {
echo <<<EOD
<h2>Unauthorized</h2>
<p>You are not authenticated.<br>
Valid usernames/passwords are "chris/tiger" and "alison/red"<p>
<p><a href="login.php">Login Page</a><p>
EOD;
exit;
}
// Generate the application page
$c = oci_pconnect(ORA_CON_UN, ORA_CON_PW, ORA_CON_DB);
// Set the client identifier after every connection call
// using a value unique for the web end user.
oci_set_client_identifier($c, $_SESSION['username']);
$username = htmlentities($_SESSION['username'], ENT_QUOTES);
echo <<<EOD
<body style="font-family: Arial, sans-serif;">
<h2>Parts Company</h2>
<table border='1'>
<caption><b>Inventory for $username </b></caption>
EOD;
$s = oci_parse($c, "select * from parts order by id");
oci_execute($s);
while (($row = oci_fetch_array($s, OCI_ASSOC+OCI_RETURN_NULLS))
!= false) {
echo "<tr>\n";
foreach ($row as $item) {
echo " <td>" .
($item!==null?htmlentities($item, ENT_QUOTES):" ") .
"</td>\n";
}
echo "</tr>\n";
}
echo <<<EOD
</table>
<p><a href="logout.php">Logout</a></p>
</body>
EOD;
?>
When logged in as Chris, the application shows:
A logout script logout.php clears PHP's session information:
<?php
// logout.php
session_start();
unset($_SESSION['username']);
echo <<<EOD
<body style="font-family: Arial, sans-serif;">
<h2>Goodbye</h2>
<p>You are logged out.<p>
<p><a href="login.php">Login Page</a><p>
</body>
EOD;
?>
The logout page does not call dbms_session.clear_identifier to clear the database connection's identifier: that would need to be done in the files that use a database connection if there was concern about whether oci_set_client_identifier() was consistently used throughout the application.
The same application code will be used in the next sections without any modifications.
To summarize, this simple application is designed to show the relationship between database users and end users so that client identifiers can be discussed. It does not constitute a suitable example for production use. The application sets a client identifier with oci_set_client_identifier() immediately after each oci_pconnect() connection call. This identifier uniquely identifies the end user who is sitting at his or her web browser. For existing real-life applications, adding a call to oci_set_client_identifier() with a unique identifier per web user is the only application change that needs to be made to take advantage of client identification.
Using a Client Identifier in PHP for Auditing
Auditing lets you:
- Identify inappropriate database changes
- Investigate suspicious activity
- Verify authorization or access control policies
- Satisfy business compliance regulations
- Gather data about database activities for use in capacity and resource allocation planning
Oracle auditing is powerful and multi-faceted. You can audit general activities such as the type of SQL statement executed. You can audit fine grained activities such as when specific values occur, or what IP address initiated a request. Auditing can occur on both successful and failed activities. The audit trail can be stored inside the database or outside it, suitable for analysis with various tools.
Setting a client identifier allows auditing to be associated with unique web users, and not just with the database table owner who authenticated the PHP OCI8 oci_pconnect() call to the database.
The auditon.sql script is a basic example of query auditing on the PARTS table:
-- auditon.sql
-- Turn on object auditing for the PARTS table
connect system/welcome
audit select on phpuser.parts by access;
Run the script above in SQL*Plus. Then run the application and login as 'chris' or 'alison' (their passwords are in setup.sql). You can even query the table as the SYSTEM user in SQL*Plus outside the application:
select * from phpuser.parts;
This returns the expected parts list.
To show the audit trail from all these table accesses, the SQL script auditreport.sql queries the DBA_AUDIT_TRAIL view, which contains the audit data when the database initialization parameter AUDIT_TRAIL is set to DB.
-- auditreport.sql
-- View the audit trail for the PARTS table
connect system/welcome
set pagesize 100
col app_username format a13
col username format a13
col extended_timestamp format a37
col action_name format a13
select auth.app_username,
dat.username,
extended_timestamp,
action_name
from dba_audit_trail dat
left outer join
php_sec_admin.php_authentication auth
on auth.app_username = client_id
where obj_name = 'PARTS'
order by extended_timestamp;
Running the reports shows the time each person accessed the PARTS table:
APP_USERNAME USERNAME EXTENDED_TIMESTAMP ACTION_NAME
------------- ------------- ------------------------------------- -------------
chris PHPUSER 16-AUG-10 12.25.42.846153 PM -07:00 SELECT
alison PHPUSER 16-AUG-10 12.25.50.870773 PM -07:00 SELECT
SYSTEM 16-AUG-10 12.25.58.660922 PM -07:00 SELECT
There is no APP_USERNAME shown for the SYSTEM user because there was no client identifier set in the SQL*Plus session. Sometimes identifying data accesses where the client identifier is not correctly set is the desired auditing goal. Oracle's Fine-Grained Auditing can be used to audit specific events like this, helping monitor suspicious activity. This can be useful when client identifiers are used by Virtual Private Databases to restrict data access but complete auditing is not required.
When you are finished exploring the example, you can turn auditing off using the NOAUDIT command in SQL*Plus:
-- auditoff.sql
-- Turn off object auditing for the PARTS table
connect system/welcome
noaudit all on phpuser.parts;
Using a Client Identifier in PHP with a VPD for Restricting Data Access
Limiting access to avoid misuse of sensitive data is an architectural goal of all applications. Oracle PHP applications can use the client identifier to restrict data access in a manually coded or an automatic way. The manual way is to modify every SQL and PL/SQL statement to use SYS_CONTEXT(), which returns the client identifier of the PHP connection. For example, queries could be written to returns rows from PARTS only when the identifier of the current connection is 'chris':
select * from parts
where sys_context('userenv', 'client_identifier') = 'chris';
When Alison (or any user with a different client identifier) is connected, then the WHERE clause evaluates to false and no rows will be returned. This kind of logic is cumbersome to code and error prone to consistently implement everywhere. Oracle Database Enterprise Edition's Virtual Private Database (VPD) technology comes to the rescue. It will automatically add a WHERE predicate to each statement the application executes.
To set up VPD, a PL/SQL function that returns the desired text of the restrictive WHERE clause needs to be created. To automatically restrict data returned from the query select * from parts the function would just need to return the string:
sys_context('userenv', 'client_identifier') = 'chris'
With VPD enabled to use such a function, the query would be executed by Oracle as if it had the restrictive WHERE clause, resulting in the same application behavior as discussed above for the manual implementation. Technically Oracle uses a transient view that enforces the WHERE clause and rewrites the application query to use the view instead of the base table, as described in the VPD documentation. Regardless of the implementation details, Oracle transparently handles the authorization, so security is consistent and programmers can be more productive on other tasks. Remember that from the database perspective, client identifiers are "insecure" because the database has to rely on externally provided information for policy enforcement. This is the outcome of using shared database connections and middle-tier authentication in a stateless web architecture and it places a reliance on having correct application code.
For the Parts application, the SQL script vpdon.sql sets up VPD. First it creates an application specific table of privileges. I've decided that Chris can only see electrical items, but Alison can see electrical and plumbing supplies. The VPD policy function F_POLICY_PARTS returns a subquery that checks the current client identifier has access to the part category of the row. Although F_POLICY_PARTS is passed the schema and table name that the policy is being applied to, in this example the policy is only used for one table so the function parameters are not referenced. With the policy function defined, the DBMS_RLS.ADD_POLICY procedure is used to enable it for the PARTS table.
-- vpdon.sql
set echo on
connect / as sysdba
grant execute on sys.dbms_rls to php_sec_admin;
connect php_sec_admin/welcome
-- Application policy table
drop table php_privs;
create table php_privs (username varchar2(64), category varchar2(20));
-- Chris should only see electrical items. Alison can see
-- electrical and plumbing items
insert into php_privs values ('chris', 'electrical');
insert into php_privs values ('alison', 'electrical');
insert into php_privs values ('alison', 'plumbing');
commit;
grant select on php_privs to phpuser;
-- Policy function F_POLICY_PARTS returns a where clause to restrict access
create or replace function f_policy_parts
(schema in varchar2, tab in varchar2) return varchar2
as
predicate varchar2(400);
begin
predicate :=
'category in
(select category
from php_sec_admin.php_privs
where username = sys_context(''userenv'', ''client_identifier''))';
return predicate;
end;
/
show errors
begin
dbms_rls.add_policy (
object_schema => 'PHPUSER',
object_name => 'PARTS',
policy_name => 'ACCESS_CONTROL_PARTS',
function_schema => 'PHP_SEC_ADMIN',
policy_function => 'F_POLICY_PARTS',
policy_type => DBMS_RLS.STATIC);
end;
/
The policy functions in an application can be as complex as needed. Your own web sites can implement policy rules in the most suitable way for them, which is likely to be completely different to that used in this example. Take care with the POLICY_TYPE argument. Here the policy function returns a simple string, making the function identical for all uses. This means the type can be specified as STATIC allowing the function to be cached. The client identifier is not considered part of the user defined application context so if the policy function logic evaluates the identifier value then you will need to set the type to DYNAMIC.
Login to the Parts application as Chris see how the inventory list now only shows electrical supplies:
When logged in as Alison you can continue to see everything:
Oracle Database has a number of views for VPD management. One is the V$VPD_POLICY view that can be used to find the policies that were applied to executed SQL statements. This can be useful for debugging the values returned by the policy function.
A fun thing to do with VPD is to login to SQL*Plus as the owner of the PARTS table and check its contents:
connect phpuser/welcome
select * from parts;
No rows will be returned because the policy function F_POLICY_PARTS is applied even for the table owner. Without having an identifier validly set, the condition can never be satisfied. To remove this restriction and make administration of objects easier, Oracle has an EXEMPT ACCESS POLICY privilege for exempting users from VPD policies.
VPD is useful for more than personnel access control. The model can be extended to allow "shared hosting". The infrastructure for one application can be shared between multiple different groups of people who are never authorized to see data from any other group.
When you are finished with the VPD example, you can remove the policy by dropping it:
-- vpdoff.sql
set echo on
connect php_sec_admin/welcome
begin
dbms_rls.drop_policy (
object_schema => 'PHPUSER',
object_name => 'PARTS',
policy_name => 'ACCESS_CONTROL_PARTS');
end;
/
Using a Client Identifier in PHP for Monitoring and Tracing
While many tuning projects start with the automatic performance diagnostics run by Oracle Database, or analyze overall system performance manually using AWR snapshots, this may not be possible in all environments. Sometimes on a shared system, monitoring and analyzing the behavior of one web user is more practical and simplifies the process of diagnosing performance problems in PHP. During development, the behavior of a proposed application patch can be isolated from what else is happening on the system. The client identifier allows focused monitoring via Oracle's End to End Application Tracing, a feature introduced for multi-tier applications.
To collect database statistics about a user's database resource usage, the database administrator can execute the DBMS_MONITOR.CLIENT_ID_STAT_ENABLE procedure in SQL*Plus:
connect system/welcome
execute dbms_monitor.client_id_stat_enable(client_id => 'chris');
The application can be run normally for any chosen actions and duration. Statistics can be accessed in various way, including from the V$CLIENT_STATS view. After Chris views the Parts application inventory once, the view might contain:
STAT_NAME VALUE
----------------------------------- ----------
user calls 1
DB time 943
DB CPU 2000
parse count (total) 1
parse time elapsed 91
execute count 1
sql execute elapsed time 494
opened cursors cumulative 1
session logical reads 7
physical reads 0
physical writes 0
. . .
This particular example shows a single SQL statement was parsed and executed. Standard Oracle manuals and literature describes interpreting all the values, and describe the other statistics views.
Statistics can be turned off and reset to zero with:
execute dbms_monitor.client_id_stat_disable(client_id => 'chris');
A database trace to show the SQL "Explain Plan" output for analyzing executed statements can also be turned on for each web user. It will show how statements actually got optimized - not just how you thought they would be run. The database administrator can enable tracing with the DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE procedure in SQL*Plus:
connect system/welcome
execute dbms_monitor.client_id_trace_enable(client_id => 'chris', waits => true, binds => true);
The application can then be run normally. After completion of the analysis period, tracing can be turned off with:
execute dbms_monitor.client_id_trace_disable(client_id => 'chris');
To examine the created trace files, find the trace directory using SHOW PARAMETER in SQL*Plus:
show parameter user_dump_dest
This gives output like::
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest string /home/oracle/app/diag/rdbms/or
cl/orcl/trace
The trace directory typically contains many trace files from normal operation. The trcsess utility can consolidate any of those created by Chris's use of the application. The consolidation (or an individual file) can then be formatted with TKPROF. For example, start a terminal window as the Oracle software owner and run:
cd /home/oracle/app/diag/rdbms/orcl/orcl/trace
trcsess output=/tmp/all.trc clientid=chris *.trc
tkprof /tmp/all.trc /tmp/tkprof.out explain=phpuser/welcome
This looks through all the trace files in the directory and aggregates those created by Chris. If you need to run trcsess on a subset of files, such as the files for a particular day, search for the client identifier near the top of the files and pass the relevant file names to trcsess. Individual trace files contain a section like:
*** 2010-08-16 15:29:12.481
*** SESSION ID:(143.943) 2010-08-16 15:29:12.481
*** CLIENT ID:(chris) 2010-08-16 15:29:12.481
*** SERVICE NAME:(orcl) 2010-08-16 15:29:12.481
*** MODULE NAME:(httpd@localhost (TNS V1-V3)) 2010-08-16 15:29:12.481
*** ACTION NAME:() 2010-08-16 15:29:12.481
Oracle Database can also name files with a given suffix, for example 'php', to make them easier to identify. Do this by executing the SQL command ALTER SESSION SET TRACEFILE_IDENTIFIER = 'php' in PHP after connecting. Database server trace file names would then look like orcl_ora_9414_php.trc. Logic would need to be added to each PHP file to decide the suffix to use, and what conditions to set it.
The output from TKPROF in tkprof.out contains analysis of the executed statements. Here is a section of the file analyzing results on my system:
SQL ID: af69s0fa3cjnp
Plan Hash: 3769467330
select *
from
parts order by id
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 5 0.00 0.16 0 8 0 0
Execute 33 0.00 0.00 0 0 0 0
Fetch 33 0.00 0.00 0 231 0 198
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 71 0.01 0.17 0 239 0 198
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 1602 (PHPUSER)
Rows Row Source Operation
------- ---------------------------------------------------
6 SORT ORDER BY (cr=7 pr=0 pw=0 time=5 us cost=4 size=222 card=6)
6 TABLE ACCESS FULL PARTS (cr=7 pr=0 pw=0 time=5 us cost=3 size=222 card=6)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
6 SORT (ORDER BY)
6 TABLE ACCESS (FULL) OF 'PARTS' (TABLE)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 33 0.00 0.00
SQL*Net message from client 32 2219.78 5720.55
cursor: mutex S 1 0.02 0.02
library cache lock 1 0.01 0.01
cursor: pin S wait on X 1 0.01 0.01
Disk file operations I/O 4 0.00 0.00
********************************************************************************
For lover's of GUIs, Enterprise Manager 11g (Database Control) has a number of ways to check the impact of the application on the database. For example, to see statistics for a particular client identifier, start the Enterprise Manager console http://localhost:5500/em/ in a browser and navigate to Performance > Top Consumers > Top Clients. Set the View drop-down to "Clients with Aggregation Enabled". Click Add Client and specify the client identifier 'chris'. You can then select the row 'chris' and click the Enable SQL Trace button (this is same as DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE). Aggregation allows each run to be totalled together:
Other areas of Enterprise Manager can also filter by client identifier, including the Top Activity report.
A single run of the small Parts application may not make Enterprise Manager's monitoring thresholds or be visible in the aggregation periods - I ran it several times in a row to capture the screen shot.
You might have noticed that trcsess and Enterprise Manager also let data be aggregated by Action and by Module. These values can be set in PHP OCI8 with the functions oci_set_action() and oci_set_module_name() respectively to identify which parts of a PHP application are being executed. Monitoring and tracing can show up the application-wide hot spots, and the SQL statements being executed can easily be identified.
Enterprise Manager is useful for tracing performance bottlenecks and tracking causes of database slowdowns during development. It allows live analysis in a large system without impacting other concurrent web users.
Conclusion
Client identifiers should be used by PHP web applications that allow multiple application users to connect to the database via a single database user name. The identifier is a developer chosen value that can be derived from session information about the end user already present in most web applications. Client identifiers are set by simply calling the oci_set_client_identifier() function in PHP scripts connecting to the database. Oracle Database uses identifiers to audit, automatically restrict access to sensitive data, and allow focused monitoring and tracing of resource usage. Oracle PHP applications should use client identifiers so these Oracle Database features can be utilized at any point in the lifetime of the application.
Christopher Jones is a Consulting Technical Staffer with Oracle. He is the author of the PHP and Oracle Blog.
