Monitoring a go-live with google analytics and fixing table scans with Application Query Security

A recent large go-live has us monitoring things very closely to ensure that we are not getting problems.  Trying to be a little more proactive on problems

EM12C shows us all of the problem statements.  We are concentrating on the high impact long running statements.  We are able to then track these back to user applications.  This is a little harder than you might think.  Tracing users back trough their session to a web server and then using server manager to find the screens that they have open.  This information and cross reference can generally find the bad statements.

We then use all of the above and the new “Application Query” security to force users to enter at least 1 value into the keyed fields.

This is a a huge Segway from monitoring with google analytics to adding Application Query security for more efficient database queries:

Remember to save it first

Then open it again to configure fields

What I’m doing witht this SQL is finding the first column in all indexes for the F0911.  Then I’m going to make sure that at least one of these is selected for the Application Query Security, so I can be sure that an index is going to be used when people are looking at the F0911.  I don’t want table scans of 65000000 records.

This shows the columns, and then links to DD to help you find the “JDE name” that links to the DD items.

select distinct(column_name), dd1.frowdi,  dd2.frdscr
from all_ind_columns, dd910.f9210 dd1, dd910.f9202 dd2 
where table_name = 'F0911' 
and table_owner = 'PRODDTA' 
and column_position = '1' 
and trim(substr(column_name, 3, 10)) = trim(dd1.frdtai)
and dd1.frdtai = dd2.frdtai;

 

• GLBRE BatchRearEndPostCode                     Batch Rear End Posted Code             
  
• GLSBL Subledger                               Subledger                              
  
• GLCO Company                                 Company Number                         
  
• GLOBJ ObjectAccount                           Object Account                         
  
• GLDCT DocumentType                             Document Type                          
  
• GLOBJ ObjectAccount                           Cost Type                              
  
• GLAID AccountId                               Account ID                             
  
• GLCO Company                                 Company                                
  
• GLICUT BatchType                               Batch Type                             
  
• GLPOST GLPostedCode                             G/L Posted Code                        
  
• GLBC BillCode                                 Bill Code                              
  
• GLJELN JournalEntryLineNo                       Journal Entry Line Number              
  
• GLJELN JournalEntryLineNo                       J/E Line Number                        
  
• GLASID SerialTagNumber                         Serial Number                          
  
• GLRCND ReconciledROrBlank                       Reconciled       
• 
  

                     

The about will help you find the screen:

So, we can force users to only query of keyed columns – nice!

 

What link they are using

And where they are coming from.