Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Table performance Issue(Huge table)

User_AS6XDOct 14 2021 — edited Oct 14 2021

Table total records: 39435879

Problem Statement: How to improve performance while querying this huge table? Kindly share suggestions.
Table Script:
DROP TABLE ERDCRM.ACTIVITY CASCADE CONSTRAINTS;

CREATE TABLE ERDCRM.ACTIVITY
(
ID NVARCHAR2(64) NOT NULL,
NAME NVARCHAR2(80) NOT NULL,
DESCRIPTION1 NVARCHAR2(2000),
DESCRIPTION2 NVARCHAR2(2000),
LEGACY_ID NUMBER(10) NOT NULL,
VERSION NUMBER(10) NOT NULL,
ACTIVITY_DATE DATE NOT NULL,
CREATED_FOR NVARCHAR2(100) NOT NULL,
TYPE_CODE NVARCHAR2(40) NOT NULL,
IS_URGENT CHAR(1 BYTE) NOT NULL,
MAIN_CONTACT_ID NVARCHAR2(64),
CREATED_BY NVARCHAR2(255) NOT NULL,
REQUESTED_BY NVARCHAR2(255),
SOURCE_SYSTEM NVARCHAR2(5),
VISIBLE_IN_RADAR CHAR(1 BYTE) DEFAULT 'Y' NOT NULL,
CREATION_DATE TIMESTAMP(6) DEFAULT sys_extract_utc(systimestamp) NOT NULL,
OVERRIDE_PRIVACY CHAR(1 BYTE),
SUBJECT_AUTOGENERATED CHAR(1 BYTE),
LAST_UPDATE_DATE TIMESTAMP(6) DEFAULT SYS_EXTRACT_UTC(SYSTIMESTAMP),
DURATION_SECS NUMBER,
IS_DURATION_DEFAULT CHAR(1 BYTE),
IS_MKT_MATERIAL_ATTACHED CHAR(1 BYTE),
ACTIVITY_START_DATETIME TIMESTAMP(6),
ACTIVITY_END_DATETIME TIMESTAMP(6),
IS_BROKER_REVIEW_RELEVANT CHAR(1 BYTE),
IS_AT_CLIENT_OFFICE CHAR(1 BYTE),
MEETING_ID VARCHAR2(20 BYTE),
QUESTION_BANK VARCHAR2(4000 BYTE),
PRIMARY KEY
(ACTIVITY_DATE, ID)
ENABLE VALIDATE
)
ORGANIZATION INDEX
PCTTHRESHOLD 50
TABLESPACE ERDCRM
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
OVERFLOW TABLESPACE ERDCRM
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
);

CREATE INDEX ERDCRM.IDX_ACTIVITY_MEETING_ID ON ERDCRM.ACTIVITY
(MEETING_ID)
NOLOGGING
TABLESPACE ERDCRM
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
PARALLEL ( DEGREE 4 INSTANCES 1 );

CREATE INDEX ERDCRM.IDX_ACT_CREATED_FOR ON ERDCRM.ACTIVITY
(CREATED_FOR, MAIN_CONTACT_ID)
LOGGING
TABLESPACE ERDCRM
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
PARALLEL ( DEGREE 4 INSTANCES 1 );

CREATE INDEX ERDCRM.IDX_ACT_DAT ON ERDCRM.ACTIVITY
(ACTIVITY_DATE)
LOGGING
TABLESPACE ERDCRM
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
);

ALTER INDEX ERDCRM.IDX_ACT_DAT
MONITORING USAGE;

CREATE UNIQUE INDEX ERDCRM.IDX_ACT_DATE_CODE_ID ON ERDCRM.ACTIVITY
(ACTIVITY_DATE, TYPE_CODE, ID)
LOGGING
TABLESPACE ERDCRM
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
);

ALTER INDEX ERDCRM.IDX_ACT_DATE_CODE_ID
MONITORING USAGE;

CREATE UNIQUE INDEX ERDCRM.IDX_ACT_ID_DATE_CODE ON ERDCRM.ACTIVITY
(ID, ACTIVITY_DATE, TYPE_CODE, CREATED_FOR, MAIN_CONTACT_ID)
LOGGING
TABLESPACE ERDCRM
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
);

ALTER INDEX ERDCRM.IDX_ACT_ID_DATE_CODE
MONITORING USAGE;

CREATE INDEX ERDCRM.IDX_ACT_LUD ON ERDCRM.ACTIVITY
("LAST_UPDATE_DATE" DESC)
NOLOGGING
TABLESPACE ERDCRM
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
PARALLEL ( DEGREE 4 INSTANCES 1 );

CREATE INDEX ERDCRM.IDX_ACT_NAME ON ERDCRM.ACTIVITY
(NAME)
LOGGING
TABLESPACE ERDCRM
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
);

ALTER INDEX ERDCRM.IDX_ACT_NAME
MONITORING USAGE;

-- There is no statement for index ERDCRM.SYS_IOT_TOP_112642.
-- The object is created when the parent object is created.

CREATE UNIQUE INDEX ERDCRM.UN_ACT_ID ON ERDCRM.ACTIVITY
(ID)
LOGGING
TABLESPACE ERDCRM
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
);

ALTER INDEX ERDCRM.UN_ACT_ID
MONITORING USAGE;

CREATE UNIQUE INDEX ERDCRM.UN_ACT_LEGACY_ID ON ERDCRM.ACTIVITY
(LEGACY_ID)
LOGGING
TABLESPACE ERDCRM
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
);

ALTER INDEX ERDCRM.UN_ACT_LEGACY_ID
MONITORING USAGE;

ALTER TABLE ERDCRM.ACTIVITY ADD (
CONSTRAINT UN_ACT_ID
UNIQUE (ID)
USING INDEX ERDCRM.UN_ACT_ID
ENABLE VALIDATE
, CONSTRAINT UN_ACT_LEGACY_ID
UNIQUE (LEGACY_ID)
USING INDEX ERDCRM.UN_ACT_LEGACY_ID
ENABLE VALIDATE);

ALTER TABLE ERDCRM.ACTIVITY ADD (
CONSTRAINT ACTIVITY_MAIN_CON_FK
FOREIGN KEY (MAIN_CONTACT_ID)
REFERENCES ERDCRM.CONTACT (CONTACT_ID)
ENABLE VALIDATE
, CONSTRAINT ACTIVITY_OWNER_FK
FOREIGN KEY (CREATED_FOR)
REFERENCES ERDCRM.UBS_EMPLOYEE (GPN)
ENABLE VALIDATE
, CONSTRAINT ACTIVITY_TYPE_CODE_FK
FOREIGN KEY (TYPE_CODE)
REFERENCES ERDCRM.ACTIVITY_TYPE (CODE)
ENABLE VALIDATE);

GRANT UPDATE ON ERDCRM.ACTIVITY TO CRM;

GRANT SELECT ON ERDCRM.ACTIVITY TO CRMADMIN;

GRANT SELECT ON ERDCRM.ACTIVITY TO CRM_READONLY_ROLE;

GRANT DELETE, INSERT, SELECT, UPDATE ON ERDCRM.ACTIVITY TO CRUD_ERDCRM_ROLE;

GRANT DELETE, INSERT, SELECT, UPDATE ON ERDCRM.ACTIVITY TO ERDCRM_APP;

GRANT SELECT ON ERDCRM.ACTIVITY TO READONLY_ERDCRM_ROLE;

Comments

Timo Hahn

Have you read https://www.jobinesh.com/2010/04/invoking-applicationmodule-from-servlet.html?m=1
This blog sites what to do to access an am from a servlet.

Timo

dvohra21

since the conversion no AM is available.
What happens when AM is accessed?

Filip Huysmans

@dvohra21 the binding is not found, so no AM.

dvohra21

Did Timo's reference to getting a binding context help?

BindingContext bindingContext=null;
try {
    bindingContext = BindingContext.getCurrent();
    DCBindingContainer dcBindingContainer =
    bindingContext.findBindingContainer("view_dummyPageDef");
    JUCtrlRangeBinding depts =
    (JUCtrlRangeBinding)dcBindingContainer.findCtrlBinding("DepartmentsView1");
    dcBindingContainer.refreshControl();
    // Business logic goes here...
 }finally {
   // Clean up goes here...
}
Filip Huysmans

Hi @dvohra21 and @timo-hahn1 ,
Thx for your info.
This works for 1 operation of my authentication servlet, getting the user info.
Another operation I have is setting the language, which I get from the SSO application, in the AM, so this can be used to fetch the correct data throughout all the views. This means I need the same instance of the AM to get this working and so creating an new instance of the AM with Configuration.createRootApplicationModule, will not help me.
A second problem is that my servlet does not have a seperate url, it is the same url as the pages, as it verifies for each request that the user is valid. If I want to implement the solution of adding manually a pagedef file, to which url should I then bind this pagedef file? The same challenge exist for setting this language in the FacesContext environment.
Thank you in advance.
Filip

Filip Huysmans

@dvohra21
BindingContext.getCurrent() returns null in my filter.

FYI

Timo Hahn

Have you read the blog and configured the ADFBindingFilter to ADFServletFilter for the servlet?
Only then can you get the binding context.

Timo

dvohra21

Only a code snippet was posted but other config is also needed.

Filip Huysmans

web.xml.txt (10.49 KB)I uploaded my web.xml file, I removed the init-params and context-params specific for my application.
I just also added the adfauthentication filter to the mappings of the adfbinding filter, without succes. Still no access to FacesContext.getCurrentInstance().
Am I still missing something?
How do you " configured the ADFBindingFilter to ADFServletFilter for the servlet"?
Small remark: My code is in a filter, not a servlet.

Thx

Filip Huysmans

Hi everyone,
Some things I tried:
Oracle Support Doc 2423831.1 : adfc variable is empty/null in my case

adfc = ADFContext.initADFContext(httpRequest.getServletContext(), httpRequest.getSession() , httpRequest, servletResponse);

Amis Document : bcx variable in the ConfigurePageDef method, remains null

BindingContext bc = DCUtil.getBindingContext(httpRequest);

BindingContext method remains null

BindingContext bc2 = BindingContext.getCurrent();

All these calls are done in the doFilter method of my AuthenticationFilter class, as defined in the web.xml.

Thx for your help on this.

Timo Hahn

Filip,
you can look at
https://github.com/oracle/adf-samples where you find a sample 'Custom Servlet Filter to Register a Globally-Available Data Control.' This sample from way back implements your use case. I'm not sure it still runs as it uses struts as view technology, but the filter implementation should still work.
If this doesn't help, you can provide a complete test case that we can use to reproduce the problem. Just looking at some files and code snippets won't help.
Timo

Filip Huysmans

Hi [Timo Hahn](/ords/forums/user/Timo Hahn) & dvohra21 ,
sorry for the delay, but due to holidays I had to postpone my search.
Attached an example. No additional actions are required to run this example.
DemoAuthFilterWithContext.zip (349.21 KB)I got the bindingContext working, through the correct web.xml configuration:
image.pngI used to use URI Path, but the Servlet config is working better.
The only thing I'm still missing is access to the FacesContext.
I added the call in my CustomAuthenticationFilter.java class at line 55. Running the app will result in a NPE.
Is there a way to get this FacesContext instance? I use it to set the local on the ViewRoot:

FacesContext.getCurrentInstance().getViewRoot().setLocale(Locale.FRENCH);

All help is more than welcome.

Thx

dvohra21

The issue could be :
My code is in a filter, not a servlet.
Shouldn't the code be in a servlet?

Filip Huysmans

Hi @dvohra21 ,
indeed, good question. Because it was like that in 11g.
I created a new version of my demo app, with the same code in a servlet.
DemoAuthFilterWithContext.zip (455.8 KB)This results in the same problem. FacesContext is not accessible.
Thank you for your help on this.
Filip

dvohra21

How is the CustomServletFilter class a servlet? It doesn't extend the javax.servlet.http.HttpServlet.
Please refer to https://blogs.oracle.com/groundside/post/integrating-adf-and-servlets

Filip Huysmans

Hi @dvohra21 ,
thanks for the example. But unfortunately this is not my usecase.
The code in my AuthenticationFilter is to add security checks and settings before a page is being called.
We are using an SSO-solution in front of our ADF Application.
In the case of the servlet, it can not stop the call to a page, nor can it set security settings in the session context before the execution of a page or taskflow.
Therefore I believe the use of a servlet is not a solution for my situation. Do you agree?

Thx

dvohra21

Thanks for the update, even though the earlier message is "I created a new version of my demo app, with the same code in a servlet."

Filip Huysmans

@dvohra21 yes, in the hope this could also work, but it seems that for my use case I need a filter.
Is this assumption correct?
If so, what can I do to make it work?
Thx

dvohra21

A servlet is needed. "The FacesServlet servlet manages the request processing lifecycle in JSF applications. FacesServlet creates an object called FacesContext, which contains the information necessary for request processing"
https://docs.oracle.com/cd/E16340_01/web.1111/b31974/adf_lifecycle.htm

Filip Huysmans

Hi @dvohra21 ,
ok, but how can I couple my servlet to this FacesServlet and make sure mine is executed after the FacesServlet one?
We can not put 2 servlets on the same URI path.
Thx
Filip

Timo Hahn

@filip-huysmans I'm somewhat confused by the long discussion. Can you summarize the current state, please?
I understood that you had two problems.
Can you access the application module from your filter?
Can you set the language inside the filter?

Timo

Filip Huysmans

Hi @timo-hahn1 ,
I am able to access the AM from within the filter.
Now I still want to access the FacesContext from inside this filter, to set the language.
Accessing the FacesContext still fails.

Thx

Timo Hahn

Do you have access to the DCDataControl?
In this case, you can call setLocaleContext() on the DCDataControl the rest should be handled for you. This internally will call the setLocaleContext() on the ADF BC Session object and the application module.

Timo

Filip Huysmans

Hi [Timo Hahn](/ords/forums/user/Timo Hahn) ,
I am not accessing the MC, but the FacesContext:

FacesContext.getCurrentInstance().getViewRoot().setLocale(Locale.FRENCH);

I suppose this is not the same thing as the ADF BC Session object or am I wrong?

Timo Hahn

The access to the viewRoot will not work as it is not present inside the filter.
I would set the locale directly to the DcDataControl. This will set it to the application module, and this should allow you to get the correct data inside the application module.

Timo

Filip Huysmans

Hi @timo-hahn1 & @dvohra21 ,
I stop this thread, since we can not find a solution to my request:
Access the application module and FacesContext from a filter
This works without any issue with jspx, but once the facelets are enabled, this fails.
No idea why there is a difference between those 2 approaches at the level of the filters.

Thx a lot for you help.

Filip

dvohra21

Which two servlets? The downloadable sample zip does not include any servlet.

1 - 27

Post Details

Added on Oct 14 2021
4 comments
589 views