Saturday, March 23, 2019

performance tune your database


1) Denormalize your tables where appropriate.
2) Proper use of index columns: An index based on numeric fields is more efficient than an index based on character columns.
3) Reduce the number of columns that make up a composite key.
4) Proper partitioning of tablespaces and create a special tablespace for special data types like CLOB, BLOB etc.
5) Data access performance can be tuned by using stored procedures to crunch data in the database server to reduce the network overhead and also caching data within your application to reduce the number of accesses.
Q. Can you give some database performance tuning tips based on your experience?
1. Materialized views are one of the important SQL tuning tools in Oracle. Instead of the entire company accessing a single database server, user load can be distributed across multiple database servers with the help of materialized views in Oracle. Through the use of multi tier materialized views, you can create materialized views based on other materialized views, which enables you to distribute user load to an even greater extent because clients can access materialized view sites instead of master sites. To decrease the amount of data that is replicated, a materialized view can be a subset of a master table or master materialized view.
a) Materialized views are schema objects that can be used to summarize, precompute, replicate, and distribute data.
b) It allows you to pre-join complex views and pre-compute summaries for super-fast response times. Unlike an ordinary view, which does not take up any storage space or contain any data, a materialized view provides indirect access to table data by storing the results of a query in a separate schema object.
c) You can define a materialized view on a base table, partitioned table or view and you can define indexes on a materialized view.
d) A materialized view can be stored in the same database as its base tables (improves query performance through query rewrite) or in a different database.
2. As a rule of thumb, every table should have a clustered index. Generally, but not always, the clustered index should be on a column that increases in one direction (i.e. monotonic) such as an identity column, or some other column where the value is increasing and is unique. In many cases, the primary key is the ideal column for a clustered index. Create an index on any column that is a foreign key. If you know it will be unique, set the flag to force the index to be unique.
3. Avoid temp tables as much as you can, but if you need a temp table, create it explicitly using Create Table #temp.
4. Only return the columns and the rows you need.
5. Avoid full table scan where possible. The full table scan can be caused by
a) No WHERE condition.
b) No index on any type of field in the WHERE clause.
c) NOT IN predicate that is easier to write (replace NOT IN with a left outer join).
d) WHERE clauses like column_name is not null, condition 1 or condition 2, column_name between … and …, not equality comparisons
e) Use of SQL “LIKE clause” operator to find a string within a large table column (e.g. VARCHAR(2000), CLOB, BLOB).
DISTINCT, ANY, and ALL.
It is also worth noting that this capability may not suit best for too frequent activities as in online transaction processing (i.e. OLTP) environments. In other databases equivalent functionalities can be achieved through triggers on base tables that would update/insert aggregate or dimension tables and queries can be executed against these aggregated or dimension tables as oppose to the base tables.

Tuesday, February 19, 2019

Spring REST Web Services Exception Handling

 Solution 1 – The Controller level @ExceptionHandler

The first solution works at the @Controller level – we will define a method to handle exceptions, and annotate that with @ExceptionHandler:
1
2
3
4
5
6
7
8
public class FooController{
     
    //...
    @ExceptionHandler({ CustomException1.class, CustomException2.class })
    public void handleException() {
        //
    }
}
This approach has a major drawback – the @ExceptionHandler annotated method is only active for that particular Controller, not globally for the entire application. Of course, adding this to every controller makes it not well suited for a general exception handling mechanism.
We can work around this limitation by having all Controllers extend a Base Controller class – however, this can be a problem for applications where, for whatever reason, this isn’t possible. For example, the Controllers may already extend from another base class which may be in another jar or not directly modifiable, or may themselves not be directly modifiable.
Next, we’ll look at another way to solve the exception handling problem – one that is global and doesn’t include any changes to existing artifacts such as Controllers.

3. Solution 2 – The HandlerExceptionResolver

The second solution is to define an HandlerExceptionResolver – this will resolve any exception thrown by the application. It will also allow us to implement a uniform exception handling mechanism in our REST API.
Before going for a custom resolver, let’s go over the existing implementations.

3.1. ExceptionHandlerExceptionResolver

This resolver was introduced in Spring 3.1 and is enabled by default in the DispatcherServlet. This is actually the core component of how the @ExceptionHandler mechanism presented earlier works.

3.2. DefaultHandlerExceptionResolver

This resolver was introduced in Spring 3.0, and it’s enabled by default in the DispatcherServlet. It’s used to resolve standard Spring exceptions to their corresponding HTTP Status Codes, namely Client error – 4xx and Server error – 5xx status codes. Here’s the full list of the Spring Exceptions it handles, and how they map to status codes.
While it does set the Status Code of the Response properly, one limitation is that it doesn’t set anything to the body of the Response. And for a REST API – the Status Code is really not enough information to present to the Client – the response has to have a body as well, to allow the application to give additional information about the failure.
This can be solved by configuring view resolution and rendering error content through ModelAndView, but the solution is clearly not optimal. That’s why Spring 3.2 introduced a better option that we’ll discuss in a later section.

3.3. ResponseStatusExceptionResolver

This resolver was also introduced in Spring 3.0 and is enabled by default in the DispatcherServlet. Its main responsibility is to use the @ResponseStatus annotation available on custom exceptions and to map these exceptions to HTTP status codes.
Such a custom exception may look like:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
@ResponseStatus(value = HttpStatus.NOT_FOUND)
public class ResourceNotFoundException extends RuntimeException {
    public ResourceNotFoundException() {
        super();
    }
    public ResourceNotFoundException(String message, Throwable cause) {
        super(message, cause);
    }
    public ResourceNotFoundException(String message) {
        super(message);
    }
    public ResourceNotFoundException(Throwable cause) {
        super(cause);
    }
}
Same as the DefaultHandlerExceptionResolver, this resolver is limited in the way it deals with the body of the response – it does map the Status Code on the response, but the body is still null.

3.4. SimpleMappingExceptionResolver and AnnotationMethodHandlerExceptionResolver

The SimpleMappingExceptionResolver has been around for quite some time – it comes out of the older Spring MVC model and is not very relevant for a REST Service. We basically use it to map exception class names to view names.
The AnnotationMethodHandlerExceptionResolver was introduced in Spring 3.0 to handle exceptions through the @ExceptionHandler annotation but has been deprecated by ExceptionHandlerExceptionResolver as of Spring 3.2.

3.5. Custom HandlerExceptionResolver

The combination of DefaultHandlerExceptionResolver and ResponseStatusExceptionResolver goes a long way towards providing a good error handling mechanism for a Spring RESTful Service. The downside is – as mentioned before – no control over the body of the response.
Ideally, we’d like to be able to output either JSON or XML, depending on what format the client has asked for (via the Accept header).
This alone justifies creating a new, custom exception resolver:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
@Component
public class RestResponseStatusExceptionResolver extends AbstractHandlerExceptionResolver {
 
    @Override
    protected ModelAndView doResolveException
      (HttpServletRequest request, HttpServletResponse response, Object handler, Exception ex) {
        try {
            if (ex instanceof IllegalArgumentException) {
                return handleIllegalArgument((IllegalArgumentException) ex, response, handler);
            }
            ...
        } catch (Exception handlerException) {
            logger.warn("Handling of [" + ex.getClass().getName() + "]
              resulted in Exception", handlerException);
        }
        return null;
    }
 
    private ModelAndView handleIllegalArgument
      (IllegalArgumentException ex, HttpServletResponse response) throws IOException {
        response.sendError(HttpServletResponse.SC_CONFLICT);
        String accept = request.getHeader(HttpHeaders.ACCEPT);
        ...
        return new ModelAndView();
    }
}
One detail to notice here is that we have access to the request itself, so we can consider the value of the Accept header sent by the client.
For example, if the client asks for application/json then, in the case of an error condition, we’d want to make sure we return a response body encoded with application/json.
The other important implementation detail is that we return a ModelAndView – this is the body of the response and it will allow us to set whatever is necessary on it.
This approach is a consistent and easily configurable mechanism for the error handling of a Spring REST Service. It does, however, have limitations: it’s interacting with the low-level HtttpServletResponseand it fits into the old MVC model which uses ModelAndView – so there’s still room for improvement.

4. Solution 3 – @ControllerAdvice

Spring 3.2 brings support for a global @ExceptionHandler with the @ControllerAdvice annotation. This enables a mechanism that breaks away from the older MVC model and makes use of ResponseEntity along with the type safety and flexibility of @ExceptionHandler:
1
2
3
4
5
6
7
8
9
10
11
12
13
@ControllerAdvice
public class RestResponseEntityExceptionHandler
  extends ResponseEntityExceptionHandler {
 
    @ExceptionHandler(value
      = { IllegalArgumentException.class, IllegalStateException.class })
    protected ResponseEntity<Object> handleConflict(
      RuntimeException ex, WebRequest request) {
        String bodyOfResponse = "This should be application specific";
        return handleExceptionInternal(ex, bodyOfResponse,
          new HttpHeaders(), HttpStatus.CONFLICT, request);
    }
}
The@ControllerAdvice annotation allows us to consolidate our multiple, scattered @ExceptionHandlers from before into a single, global error handling component.
The actual mechanism is extremely simple but also very flexible. It gives us:
  • Full control over the body of the response as well as the status code
  • Mapping of several exceptions to the same method, to be handled together, and
  • It makes good use of the newer RESTful ResposeEntity response
One thing to keep in mind here is to match the exceptions declared with @ExceptionHandler with the exception used as the argument of the method. If these don’t match, the compiler will not complain – no reason it should, and Spring will not complain either.
However, when the exception is actually thrown at runtime, the exception resolving mechanism will fail with:
1
2
java.lang.IllegalStateException: No suitable resolver for argument [0] [type=...]
HandlerMethod details: ...

5. Solution 4 – ResponseStatusException (Spring 5 and Above)

Spring 5 introduced the ResponseStatusException class. We can create an instance of it providing an HttpStatus and optionally a reason  and a cause:
1
2
3
4
5
6
7
8
9
@GetMapping("/actor/{id}")
public String getActorName(@PathVariable("id") int id) {
    try {
        return actorService.getActor(id);
    } catch (ActorNotFoundException ex) {
        throw new ResponseStatusException(
          HttpStatus.NOT_FOUND, "Actor Not Found", ex);
    }
}
What are the benefits of using ResponseStatusException?
  • Excellent for prototyping: We can implement a basic solution quite fast
  • One type, multiple status codes: One exception type can lead to multiple different responses. This reduces tight coupling compared to the @ExceptionHandler
  • We won’t have to create as many custom exception classes
  • More control over exception handling since the exceptions can be created programmatically
And what about the tradeoffs?
  • There’s no unified way of exception handling: It’s more difficult to enforce some application-wide conventions, as opposed to @ControllerAdvice which provides a global approach
  • Code duplication: We may find ourselves replicating code in multiple controllers
We should also note that it’s possible to combine different approaches within one application.
For example, we can implement a @ControllerAdvice globally, but also ResponseStatusExceptions locally. However, we need to be careful: If the same exception can be handled in multiple ways, we may notice some surprising behavior. A possible convention is to handle one specific kind of exception always in one way.
For more details and further examples, see our tutorial on ResponseStatusException.

6. Handle the Access Denied in Spring Security

The Access Denied occurs when an authenticated user tries to access resources that he doesn’t have enough authorities to access.

6.1. MVC – Custom Error Page

First, let’s look at the MVC style of the solution and see how to customize an error page for Access Denied:
The XML configuration:
1
2
3
4
5
<http>
    <intercept-url pattern="/admin/*" access="hasAnyRole('ROLE_ADMIN')"/>  
    ...
    <access-denied-handler error-page="/my-error-page" />
</http>
And the Java configuration:
1
2
3
4
5
6
7
8
@Override
protected void configure(HttpSecurity http) throws Exception {
    http.authorizeRequests()
        .antMatchers("/admin/*").hasAnyRole("ROLE_ADMIN")
        ...
        .and()
        .exceptionHandling().accessDeniedPage("/my-error-page");
}
When users try to access a resource without having enough authorities, they will be redirected to “/my-error-page“.

6.2. Custom AccessDeniedHandler

Next, let’s see how to write our custom AccessDeniedHandler:
1
2
3
4
5
6
7
8
9
10
@Component
public class CustomAccessDeniedHandler implements AccessDeniedHandler {
 
    @Override
    public void handle
      (HttpServletRequest request, HttpServletResponse response, AccessDeniedException ex)
      throws IOException, ServletException {
        response.sendRedirect("/my-error-page");
    }
}
And now let’s configure it using XML Configuration:
1
2
3
4
5
<http>
    <intercept-url pattern="/admin/*" access="hasAnyRole('ROLE_ADMIN')"/>
    ...
    <access-denied-handler ref="customAccessDeniedHandler" />
</http>
Or using Java Configuration:
1
2
3
4
5
6
7
8
9
10
11
@Autowired
private CustomAccessDeniedHandler accessDeniedHandler;
 
@Override
protected void configure(HttpSecurity http) throws Exception {
    http.authorizeRequests()
        .antMatchers("/admin/*").hasAnyRole("ROLE_ADMIN")
        ...
        .and()
        .exceptionHandling().accessDeniedHandler(accessDeniedHandler)
}
Note how – in our CustomAccessDeniedHandler, we can customize the response as we wish by redirecting or display a custom error message.

6.3. REST and Method Level Security

Finally, let’s see how to handle method level security @PreAuthorize@PostAuthorize, and @SecureAccess Denied.
We’ll, of course, use the global exception handling mechanism that we discussed earlier to handle the AccessDeniedException as well:
1
2
3
4
5
6
7
8
9
10
11
12
13
@ControllerAdvice
public class RestResponseEntityExceptionHandler
  extends ResponseEntityExceptionHandler {
 
    @ExceptionHandler({ AccessDeniedException.class })
    public ResponseEntity<Object> handleAccessDeniedException(
      Exception ex, WebRequest request) {
        return new ResponseEntity<Object>(
          "Access denied message here", new HttpHeaders(), HttpStatus.FORBIDDEN);
    }
     
    ...
}

7. Spring Boot Support

Spring Boot provides an ErrorController implementation to handle errors in a sensible way.
In a nutshell, it serves a fallback error page for browsers (aka the Whitelabel Error Page), and a JSON response for RESTful, non HTML requests:
1
2
3
4
5
6
7
{
    "timestamp": "2019-01-17T16:12:45.977+0000",
    "status": 500,
    "error": "Internal Server Error",
    "message": "Error processing the request!",
    "path": "/my-endpoint-with-exceptions"
}
As usual, Spring Boot allows configuring these features with properties:
  • server.error.whitelabel.enabled: can be used to disable the Whitelabel Error Page and rely on the servlet container to provide an HTML error message
  • server.error.include-stacktrace: with an always value, it includes the stacktrace in both the HTML and the JSON default response
Apart from these properties, we can provide our own view-resolver mapping for /error, overriding the Whitelabel Page.
We can also customize the attributes that we want to show in the response by including an ErrorAttributes bean in the context. We can extend the DefaultErrorAttributes class provided by Spring Boot to make things easier:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
@Component
public class MyCustomErrorAttributes extends DefaultErrorAttributes {
 
    @Override
    public Map<String, Object> getErrorAttributes(WebRequest webRequest, boolean includeStackTrace) {
        Map<String, Object> errorAttributes = super.getErrorAttributes(webRequest, includeStackTrace);
        errorAttributes.put("locale", webRequest.getLocale()
            .toString());
        errorAttributes.remove("error");
 
        //...
 
        return errorAttributes;
    }
}
If we want to go further and define (or override) how the application will handle errors for a particular content type, we can register an ErrorController bean.
Again, we can make use of the default BasicErrorController provided by Spring Boot to help us out.
For example, imagine we want to customize how our application handles errors triggered in XML endpoints. All we have to do is define a public method using the @RequestMapping and stating it produces application/xml media type:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
@Component
public class MyErrorController extends BasicErrorController {
 
    public MyErrorController(ErrorAttributes errorAttributes) {
        super(errorAttributes, new ErrorProperties());
    }
 
    @RequestMapping(produces = MediaType.APPLICATION_XML_VALUE)
    public ResponseEntity<Map<String, Object>> xmlError(HttpServletRequest request) {
         
    // ...
 
    }
}