Bug 814839

fyi: I reverted that one change made before: https://bugzilla.redhat.com/show_bug.cgi?id=797325#c8

(04:22:06 PM) mazz: because logging=false is our default, on EVERY table we create, we do so explicitly with NOLOGGING
(04:22:24 PM) mazz: CREATE TABLE foo NOLOGGING
(04:22:33 PM) mazz: that's what our create table SQL will look like
(04:22:38 PM) mazz: for EVERY table
(04:24:00 PM) mazz:     private boolean m_logging = false;
(04:24:00 PM) mazz: ...
(04:24:00 PM) mazz:         if (!this.m_logging) // Oracle does NOLOGGING   {
(04:24:00 PM) mazz:             strCmd += this.getLoggingSyntax();
(04:24:00 PM) mazz:         }
(04:24:00 PM) mazz: ...
(04:24:00 PM) mazz:     protected String getLoggingSyntax() {
(04:24:00 PM) mazz:         return " NOLOGGING";
(04:24:00 PM) mazz:     }
(04:24:07 PM) mazz: that's the revelent code

I think we should change our code such that Table.m_logging is true by
default, and therefore tables should be Logging = true (NOLOGGING should
not be set on all of our tables.  It means that we can always recover data
and customers won't worry that we have it turned off. From what I can see
it should make no difference to us because to the best of my knowledge
we don't perform table creates that load data in the create.  And we don't
subsequently load data with direct inserts (Append hints). Although we
should validate that for the metric tables, and maybe event tables.

See: 

http://www.databasejournal.com/features/oracle/article.php/3631361/Managing-Tables-Logging-versus-Nologging.htm

http://jakub.wartak.pl/blog/?page_id=107

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5280714813869

Even when used it is primarily temporary, and therefore not recommended
to create the table and leave it that way.

It seems we do use INSERT /*+ APPEND */  in a few places.  This is a
direct insert and therefore may have a performance benefit from NOLOGGING,
at the expense of not being able to recover data without a backup.

I see it used for the raw metric tables and also the calltime data value
table.  

Mazz notes that we explicitly set logging=false on the following tables:

(4:39:31 PM) mazz: RHQ_MEASUREMENT_DATA_NUM_[1H, 6H, 1D]
(4:40:00 PM) mazz: RHQ_ALERT, RHQ_ALERT_NOTIF_LOG, RHQ_ALERT_CONDITION_LOG

Which is not quite right from what I can see, because those are not the
tables with the INSERT /*+ APPEND */.  Perhaps they need a closer look at
how they are loaded.

So, if we do change the logging default to true then we should also
ensure that logging is false on the tables that make sense.  Today it
doesn't matter because logging is off on all tables.

these are the two places where I see /*+ APPEND */ being used:

1. ...measurement.CallTimeDataManagerBean.CALLTIME_VALUE_INSERT_STATEMENT

(that inserts into the table RHQ_CALLTIME_DATA_VALUE)

2. ...measurement.MeasurementDataManagerBean.addNumericData(Set<MeasurementDataNumeric>)

(that inserts into one of the 14 raw measurement tables)

from one of those links that jay posted, which is a quote from oracle docs:

"The NOLOGGING clause also specifies that subsequent direct loads using SQL*Loader and direct load INSERT operations are not logged. Subsequent DML statements (UPDATE, DELETE, and conventional path insert) are unaffected by the NOLOGGING attribute of the table and generate redo."

We do not use direct load INSERTs (which use the +APPEND hint in INSERT INTO...SELECT statements - for example: INSERT /*+ APPEND*/ INTO my_table SELECT * FROM my_other_table) except for that CALLTIME_VALUE_INSERT_STATEMENT insert (which I believe means we are OK with losing that data to gain the benefit of faster inserts).

I'm reading, however, that in production systems, you should not leave NOLOGGING on unless its what you really want.

i must really not understand what the person was trying to do when setting up logging="false" in our schema.xml files.

I just realized that only our aggregate meas data tables have logging="false" (the 1H, 6H and 1D tables). All of the 14 raw meas tables do NOT have this - but its those tables which we INSERT /*+APPEND*/

So, I must really not understand what NOLOGGING is trying to be used for - since the tables we use /*+APPEND*/ on are NOT set with NOLOGGING, and those that are set explicitly with logging="false" are never inserted with /*+APPEND*/

Created attachment 579660 [details]
patch to enable logging except for places where the code wants it off

see the attached patch for the proposed changes to turn on LOGGING on all tables except for those that we explicitly use +APPEND hint on. Those will have logging="false". The logging default is true in this patch.

Created attachment 579981 [details]
sql script to turn on oracle logging on relevant tables

attachment is a sql script that lets you turn on LOGGING or NOLOGGING on all tables appropriately. Use this if you already have RHQ installed and you want the logging option set on the relevant tables.

git commit to master: eca0a39

the dbsetup scripts have been adjusted. to test:

1) start with a clean Oracle DB (or when you install, tell the installer to not reuse any existing database tables - have it start clean).

2) After the installer finishes, go to a SQL tool (SQL*Plus or whatever), log in as the RHQ database user, and do a "select table_name, logging from user_tables" and verify that all tables have logging=YES except for the following tables, whose logging should be NO:

   * RHQ_CALLTIME_DATA_VALUE
   * the 15 raw metric tables named RHQ_MEAS_DATA_NUM_R00 through _R14

*** Bug 797325 has been marked as a duplicate of this bug. ***

Bulk closing of BZs that have no target version set, but which are ON_QA for more than a year and thus are in production for a long time.