This may be a little off topic, but I was on a team to help resolve a performance issue with Moodle using Oracle for the backend database. The following is part of the solution that was found.
Changing the CLOB datatype to VARCHAR2(4000) in the m_config database table provided the most help. The following links were the resources I found to point this out.
https://tracker.moodle.org/browse/MDL-16955
http://moodle.org/mod/forum/discuss.php?d=87509#p389413
Using SQLDeveloper, I performed the following actions to the data after stopping the Apache Web server.
create table m_config_original as (select * from m_config); drop table m_config; CREATE TABLE M_CONFIG ( ID NUMBER(10,0) NOT NULL ENABLE, NAME VARCHAR2(255 BYTE) NOT NULL ENABLE, VALUE VARCHAR2(4000 BYTE) NOT NULL ENABLE ); insert into m_config (select * from m_config_original);
Restart the Apache web server
With this single change, the pages displayed in 2/3 less time based on
the original display time. This was a significant amount – 15 seconds
down to 5 seconds.
Additional Notes:
Before applying the changes to the m_config table, I needed to determine
if the oci8 driver was the main problem. I was able to verify that the
OCI8 driver was not the issue by running the following test page on the
moodle server. Here is the PHP code:
<?php
# Sample code for make connection with Oracle Database and send simple DDL,DML and query commands from oci8 extension.
# Database instance: fuju
# host name : fuju.exzilla.net
# lintener port number : 1521
# user : scott
# Password : tiger
# You can connect to database server from this program without setting tnsnames.ora.
# fuju at exizilla dot net, Jan10,2002
# Modified from original of " http://www.php.net/manual/en/function.ocilogon.php "
print "<HTML><PRE>";
# Sample connection string, use your own values before testing
# $db = "(DESCRIPTION=(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = fuju.exzilla.net)(PORT = 1521)))(CONNECT_DATA=(SID=fuju)))";
$c1 = ocilogon("username","password",$db);
$c2 = ocilogon("username","password",$db);
function create_table($conn)
{ $stmt = ociparse($conn,"create table hellotable (hellocol varchar2(64))");
ociexecute($stmt);
echo $conn." created hellotable\n\n";
}
function drop_table($conn)
{ $stmt = ociparse($conn,"drop table hellotable");
ociexecute($stmt);
echo $conn." dropped hellotable\n\n";
}
function insert_data($conn)
{ $stmt = ociparse($conn,"insert into hellotable
values('$conn' || ' ' || to_char(sysdate,'DD-MON-YY HH24:MI:SS'))");
ociexecute($stmt,OCI_DEFAULT);
echo $conn." inserted hellotable\n\n";
}
function delete_data($conn)
{ $stmt = ociparse($conn,"delete from hellotable");
ociexecute($stmt,OCI_DEFAULT);
echo $conn." deleted hellotable\n\n";
}
function commit($conn)
{ ocicommit($conn);
echo $conn." committed\n\n";
}
function rollback($conn)
{ ocirollback($conn);
echo $conn." rollback\n\n";
}
function select_data($conn)
{ $stmt = ociparse($conn,"select * from hellotable");
ociexecute($stmt,OCI_DEFAULT);
echo $conn."----selecting\n\n";
while (ocifetch($stmt))
echo $conn." <".ociresult($stmt,"TEST").">\n\n";
echo $conn."----done\n\n";
}
# start Main program
echo "<h3> Start :: Simple oci8 extension functions test </h3><p><hr><p>";
create_table($c1);
insert_data($c1); // Insert a row using c1
insert_data($c2); // Insert a row using c2
select_data($c1); // Results of both inserts are returned
select_data($c2);
rollback($c1); // Rollback using c1
select_data($c1); // Both inserts have been rolled back
select_data($c2);
insert_data($c2); // Insert a row using c2
commit($c2); // commit using c2
select_data($c1); // result of c2 insert is returned
delete_data($c1); // delete all rows in table using c1
select_data($c1); // no rows returned
select_data($c2); // no rows returned
commit($c1); // commit using c1
select_data($c1); // no rows returned
select_data($c2); // no rows returned
drop_table($c1);
print "</PRE></HTML>";
echo "<hr><h3> End :: Simple oci8 extension functions test </h3>";
?>
This test confirmed that the OCI8 driver was functioning correctly and
was not the problem.
With this change, performance improved by reducing the time to display pages by 67%!