Our DBA's have noticed that the below seems to be occurring in our installation:
The delete that runs for about 3 hours every day is as follows. DELETE FROM TICKETGRANTINGTICKET WHERE ID=? The select that was performing scans which has been the target of our tuning efforts and runs for 8 hours a day, 6 million reads and is executed about 1,800 times a day is as follows. Essentially, it's scanning the database all day long and from what we can tell, isn't returning anything. SELECT ticketgran0_.ID as ID2_, ticketgran0_.NUMBER_OF_TIMES_USED as NUMBER2_2_, ticketgran0_.CREATION_TIME as CREATION3_2_, ticketgran0_.EXPIRATION_POLICY as EXPIRATION4_2_, ticketgran0_.LAST_TIME_USED as LAST5_2_, ticketgran0_.PREVIOUS_LAST_TIME_USED as PREVIOUS6_2_, ticketgran0_.ticketGrantingTicket_ID as ticketG10_2_, ticketgran0_.AUTHENTICATION as AUTHENTI7_2_, ticketgran0_.EXPIRED as EXPIRED2_, ticketgran0_.SERVICES_GRANTED_ACCESS_TO as SERVICES9_2_ FROM TICKETGRANTINGTICKET ticketgran0_ WHERE ticketgran0_.ticketGrantingTicket_ID=? We've noticed 2 things. 1. The purge process that purges data from this table has not run since around Nov. 2013, and there are over 4 million rows in this TICKETGRANTINGTICKET table 2. The TICKETGRANTINGTICKET_ID column is null for every record. Is the TICKETGRANTINGTICKET_ID supposed to be null? I'm thinking it should not. Looking at the logs, when the delete tries to run, it is getting a SQLCODE -911 (which means the table is locked). Could this be because the select statement above is running all day pretty much? We are running 3.3.5 of CAS. Around the time this started happening, in Nov. 2013, we had deployed an update we made for CAS that implemented throttling. Any help with this would be greatly appreciated. Thanks!! -- You are currently subscribed to [email protected] as: [email protected] To unsubscribe, change settings or access archives, see http://www.ja-sig.org/wiki/display/JSG/cas-user
