Search
Ashish sinha
- Jun 10, 2020
- 6 min
Performance weary queries in oracle
I had been going pity busy in recent time with several interviews scheduled. Every interview i took, Otherwise different in their questions had a common Key question, which was asked over and over again. Before long i decided to jot it down and try to illustrate with some use cases my approach on slow running queries. - "What are the things you would check should a query that has a long history of good performance start experiencing extremely long run times. " Whenever we a
52 views0 comments
Ashish sinha
- May 19, 2020
- 2 min
PRAGMA SERIALLY_REUSABLE IN ORACLE
The SERIALLY_REUSABLE Pragma specifies that the package state is needed for only one call to the server (for example, an OCI call to the database or a stored procedure invocation through a database link). After this call, the storage for the package variables can be reused, reducing the memory overhead for long-running sessions. This Pragma is appropriate for packages that declare large temporary work areas that are used once in the same session. The state of a serially reusa
11 views0 comments
Ashish sinha
- May 9, 2020
- 2 min
Relational to Oracle JSON
This post is a continuation of my prior blogs in Oracle Jason, which gave us an insight on Oracle Jason - Storing Jason data and querying it. Please check Oracle Jason for comprehensive lookup. Lets now take it a bit forward to converting our relational stuff to JASON with SQL. Oracle introduced some functions to deal with it without the use of libraries JSON_OBJECT – single-row function, creates an object for each row. JSON_ARRAY – single-row function, creates an array for
11 views0 comments
Ashish sinha
- May 1, 2020
- 3 min
PL/SQL - Bulk Collect
Bulk collect is one of my favourite features in PL/SQL programming. It gains significance when you require better performing code using collections to collect very large number of rows. larger the number of rows to be collected better is the performance using bulk collect. During an Oracle bulk collect, the SQL engine retrieves all the rows and loads them into the collection before it switches back to the PL/SQL engine. When rows are retrieved using Oracle bulk collect, t
19 views0 comments
Ashish sinha
- Apr 8, 2020
- 2 min
Load External JASON data in Oracle
Uploading Data files in oracle is nothing new. Many of us have been doing it for years but playing with JASON files is something that intrigues. Umpteen number of blogs are scattered everywhere which talks about uploading .dmp files but very few provides the actual .dmp file that can be uploaded. This post walks through the process to upload the external Jason data in oracle and then query it. The use cases in the blog makes the step simpler. I have used my windows ma
43 views0 comments