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.<\/p>\n
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.<\/p>\n
https:\/\/tracker.moodle.org\/browse\/MDL-16955<\/a> Using SQLDeveloper, I performed the following actions to the data after stopping the Apache Web server.<\/p>\n Restart the Apache web server<\/p>\n With this single change, the pages displayed in 2\/3 less time based on Additional Notes: This test confirmed that the OCI8 driver was functioning correctly and With this change, performance improved by reducing the time to display pages by 67%!<\/p>\n","protected":false},"excerpt":{"rendered":" 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 […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[],"class_list":["post-14","post","type-post","status-publish","format-standard","hentry","category-moodle"],"_links":{"self":[{"href":"https:\/\/www.klsoftware.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/14","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.klsoftware.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.klsoftware.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.klsoftware.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.klsoftware.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=14"}],"version-history":[{"count":9,"href":"https:\/\/www.klsoftware.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/14\/revisions"}],"predecessor-version":[{"id":359,"href":"https:\/\/www.klsoftware.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/14\/revisions\/359"}],"wp:attachment":[{"href":"https:\/\/www.klsoftware.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=14"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.klsoftware.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=14"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.klsoftware.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=14"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\nhttp:\/\/moodle.org\/mod\/forum\/discuss.php?d=87509#p389413<\/a><\/p>\ncreate table m_config_original as (select * from m_config);\r\ndrop table m_config;\r\nCREATE TABLE M_CONFIG\r\n(\r\nID NUMBER(10,0) NOT NULL ENABLE,\r\nNAME VARCHAR2(255 BYTE) NOT NULL ENABLE,\r\nVALUE VARCHAR2(4000 BYTE) NOT NULL ENABLE\r\n);\r\ninsert into m_config (select * from m_config_original);<\/pre>\n
\nthe original display time. This was a significant amount – 15 seconds
\ndown to 5 seconds.<\/p>\n
\nBefore applying the changes to the m_config table, I needed to determine
\nif the oci8 driver was the main problem. I was able to verify that the
\nOCI8 driver was not the issue by running the following test page on the
\nmoodle server. Here is the PHP code:<\/p>\n\r\n<?php\r\n\r\n# Sample code for make connection with Oracle Database and send simple DDL,DML and query commands from oci8 extension.\r\n# Database instance: fuju\r\n# host name : fuju.exzilla.net\r\n# lintener port number : 1521\r\n# user : scott\r\n# Password : tiger\r\n# You can connect to database server from this program without setting tnsnames.ora.\r\n# fuju at exizilla dot net, Jan10,2002\r\n# Modified from original of \" http:\/\/www.php.net\/manual\/en\/function.ocilogon.php \"\r\n\r\nprint \"<HTML><PRE>\";\r\n# Sample connection string, use your own values before testing\r\n# $db = \"(DESCRIPTION=(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = fuju.exzilla.net)(PORT = 1521)))(CONNECT_DATA=(SID=fuju)))\";\r\n$c1 = ocilogon(\"username\",\"password\",$db);\r\n$c2 = ocilogon(\"username\",\"password\",$db);\r\n\r\nfunction create_table($conn)\r\n{ $stmt = ociparse($conn,\"create table hellotable (hellocol varchar2(64))\");\r\nociexecute($stmt);\r\necho $conn.\" created hellotable\\n\\n\";\r\n}\r\n\r\nfunction drop_table($conn)\r\n{ $stmt = ociparse($conn,\"drop table hellotable\");\r\nociexecute($stmt);\r\necho $conn.\" dropped hellotable\\n\\n\";\r\n}\r\n\r\nfunction insert_data($conn)\r\n{ $stmt = ociparse($conn,\"insert into hellotable\r\nvalues('$conn' || ' ' || to_char(sysdate,'DD-MON-YY HH24:MI:SS'))\");\r\nociexecute($stmt,OCI_DEFAULT);\r\necho $conn.\" inserted hellotable\\n\\n\";\r\n}\r\n\r\nfunction delete_data($conn)\r\n{ $stmt = ociparse($conn,\"delete from hellotable\");\r\nociexecute($stmt,OCI_DEFAULT);\r\necho $conn.\" deleted hellotable\\n\\n\";\r\n}\r\n\r\nfunction commit($conn)\r\n{ ocicommit($conn);\r\necho $conn.\" committed\\n\\n\";\r\n}\r\n\r\nfunction rollback($conn)\r\n{ ocirollback($conn);\r\necho $conn.\" rollback\\n\\n\";\r\n}\r\n\r\nfunction select_data($conn)\r\n{ $stmt = ociparse($conn,\"select * from hellotable\");\r\nociexecute($stmt,OCI_DEFAULT);\r\necho $conn.\"----selecting\\n\\n\";\r\nwhile (ocifetch($stmt))\r\necho $conn.\" <\".ociresult($stmt,\"TEST\").\">\\n\\n\";\r\necho $conn.\"----done\\n\\n\";\r\n}\r\n\r\n# start Main program\r\necho \"<h3> Start :: Simple oci8 extension functions test <\/h3><p><hr><p>\";\r\n\r\ncreate_table($c1);\r\ninsert_data($c1); \/\/ Insert a row using c1\r\ninsert_data($c2); \/\/ Insert a row using c2\r\n\r\nselect_data($c1); \/\/ Results of both inserts are returned\r\nselect_data($c2);\r\n\r\nrollback($c1); \/\/ Rollback using c1\r\n\r\nselect_data($c1); \/\/ Both inserts have been rolled back\r\nselect_data($c2);\r\n\r\ninsert_data($c2); \/\/ Insert a row using c2\r\ncommit($c2); \/\/ commit using c2\r\n\r\nselect_data($c1); \/\/ result of c2 insert is returned\r\n\r\ndelete_data($c1); \/\/ delete all rows in table using c1\r\nselect_data($c1); \/\/ no rows returned\r\nselect_data($c2); \/\/ no rows returned\r\ncommit($c1); \/\/ commit using c1\r\n\r\nselect_data($c1); \/\/ no rows returned\r\nselect_data($c2); \/\/ no rows returned\r\n\r\ndrop_table($c1);\r\nprint \"<\/PRE><\/HTML>\";\r\n\r\necho \"<hr><h3> End :: Simple oci8 extension functions test <\/h3>\";\r\n\r\n?><\/pre>\n
\nwas not the problem.<\/p>\n