Luminis Oracle Database Tuning
Our Luminis database has crashed because of memory issues. Initially we had 4 gigs of ram on the database server with 2.8 gigs allocated to Oracle. We then increased the database server to 5.5 with the database at 3.2. It crashed at this point so we increased the database server to 8 gigs and the database to 5.5 gigs.
This term we have an increase in users accessing the course studio. With the targeted announcements increasing as well. Our session count has been averaging about 1200 with peaks at about 2500. The number of users on the system does not seem to cause the issue. Both times there were around 1400 sessions.
After our increase to 8 gigs, the Luminis database CPU now sits at 90% usage. We noticed one query taking memory and CPU:
SELECT m.GROUP_ID, g.bom_course_id, m.last_visit, g.group_name,
g.parent_group_id, m.member_type, m.admin_news, m.admin_photo,
m.admin_link, m.admin_fileshare, m.admin_leader, m.favorite_group
FROM gt_member m, gt_group g
WHERE g.GROUP_ID = m.GROUP_ID
AND m.user_id = :1
AND group_type IN ('P', 'R', 'H')
AND m.gt_status = 'A'
AND g.gt_status = 'A'
ORDER BY g.parent_group_id || g.GROUP_ID
Any help is appreciated on trouble shooting the issue.
db tuning
What luminis version are you running? There were a couple luminis patch ranges that had a bug that caused a huge number of queries to run over and over.
The query we saw running millions of times was "SELECT UP_FILTER_GROUP.GROUP_ID, UP_FILTER_GROUP.CREATO
R_ID, UP_FILTER_GROUP.ENTITY_TYPE_ID, UP_FILTER_GROUP.GROU....."
I have 3 scripts for checking oracle long running queries, suspicious queries, and expensive queries. I can email them to you unless you know how to query that info already.
We have similar concurrent sessions and peaks, and our oracle load is tiny.
NPROC USERNAME SWAP RSS MEMORY TIME CPU
85 oracle 4605M 4572M 28% 2:15:40 1.0%
This is cpver 4.2.0.67
Also, if you migrated from 3 to 4, there were some index issues for some folks. You might want to double check that key fields are still indexed, like group_id.
REM idx.sql
SET PAGES 999 LINES 132
COLUMN tabname FORMAT A30
COLUMN column_name FORMAT A35 HEADING "Column Name"
COLUMN data_type FORMAT A9 HEADING "Type"
COLUMN data_length FORMAT 9999 HEADIN "Size (bytes)"
COLUMN indname FORMAT A30 HEADING "Index"
BREAK ON tabname on indname SKIP 1 NODUPLICATE
spool all_idx
SELECT I.table_name tabname, I.index_name indname, I.column_name, T.data_type, T.data_length
FROM dba_ind_columns I, dba_tab_columns T
WHERE I.index_owner = 'LUMINIS'
AND I.table_name = T.table_name
AND I.index_owner = T.owner
AND I.column_name = T.column_name
ORDER BY I.table_name, I.index_name, I.column_position;
spool off
exit
Thanks!
We are running Luminis 4.2.2.34. Can you email those queries to djohnson@valenciacc.edu. We are using Spotlight to monitor the Database. The query I listed was the worst. Thanks again!
db tuning
Hmm. That version should be high enough to avoid the db problems. Unless the bug was reintroduced at some point. I emailed you the scripts.
scripts
Hi Jason,
We are experiencing similar problems with connection pools and our luminis database. I would like to use your scripts to check our database, my email address is alchalel@nova.edu. Any help will be appreciated.
Thanks,
Eliana