OTN Appreciation Day: Find performance issue for user session

In this post I would first like to thank OTN Community for helping me in connecting people and making every one get connected and learn. Also thanks to Tim Hall for setting all of us up for sharing some content with Oracle Community. So let me start my sharing. #ThanksOTN for all the tweets across OTN Appreciation Day.

In this post I am going to speak about the frequent issue which we have in a large database. We always used to see some long running PL/SQL programs or from a client session. Following are some of the ease of use dynamic views and queries to identify those.

1. In case of PL/SQL we first need to figure out which query or operation is taking long time.

select sesion.sid,
       sesion.username,
       optimizer_mode,
       hash_value,
       address,
       cpu_time,
       elapsed_time,
       sql_text
  from v$sqlarea sqlarea, v$session sesion
 where sesion.sql_hash_value = sqlarea.hash_value
   and sesion.sql_address    = sqlarea.address
   and sesion.username       = USER; --Gets the current user details (Or application userid)

You can also check the progress of any long running quries

SELECT s.sid,

       s.serial#,

       s.machine,

       sl.opname,

       sl.target,

       sl.elapsed_seconds,

       sl.time_remaining,

       sl.sofar/sl.totalwork*100, 2) progress_percent

FROM   v$session s,

       v$session_longops sl

WHERE  s.sid     = sl.sid

AND    s.serial# = sl.serial#

AND    s.username = USER;  --Gets the current user details (Or application userid)

These 2 queries have helped me many times for tracking which issue is running long.

Setup Eclipse in 3 simple steps

This tutorial explains how to setup Eclipse for Java and J2EE development, in 3 steps.

1)  Goto the link https://eclipse.org/downloads/ and download Eclipse IDE for Java EE Developers. (32 or 64 bit based on your computer configuration).

2)  Unzip the downloaded .zip file (eclipse-jee-mars-1-win32.zip) as per the latest version when writing this article.

3)  Open Eclipse.exe from that folder, it will ask for a workspace location. Enter the location and click Ok.

Done, Eclipse is up and running now. We can start our programming in Eclipse now.

Please provide your comments below the blog.

Start any program from Run Prompt

In this blog post we are going to see how to open any program or application from Windows Run Command Prompt.

By default you can open many programs for Windows command prompt eg. Notepad, WordPad, Microsoft Word, Outlook. But the problem with the default functionality is that you need to know the exact exe name to open the application i.e for opening PowerPoint you need to type POWERPNT but consider you need to open PowerPoint when you type PPT, it can’t be done by default.

Also there is another problem, for some programs you need to add the exe location to the PATH environment variable, if not added during the application installation. I by default prefer using application zip package instead of the installation file so every time I need to add the exe location to my path variable which increased its description too long.

 Now lets get to the point how can we access any programs, its simple follow the steps below.

1) Create a new folder in your windows locations. Here my folder is created at C:\users\kumar\Shortcuts make a note of this path.

2) Right Click My Computer (Right click) --> Properties --> Advanced system settings (Tab) --> Open the System Properties pop settings box.

3) Navigate to Advanced (tab) --> Click Environment Variables.

4) Select PATHEXT in System Variable section and click Edit and add ;.LNK in Variable Value section at the last as show in the image below.

5) Now select System Variable Path and click Edit and at the last add ;C:\users\kumar\Shortcuts (path where you created the new folder) at the last. Click OK and in Environment Variables tab and Click on Ok in System Properties. 

6) Now right click any .exe file and select Create Shortcut. Once the shortcut is created cut it from that location and paste in our new folder, and rename as your want it. This is the name that you will be calling from the Run prompt or CMD line prompt.

That's it now you will be able to open any application directly from the Run prompt with your custom names. You can even create shortcuts of folder and paste it here and open from Run prompt.

Please provide your comments below the blog.

Various Oracle Database Objects

In this tutorial we are going to discuss about a couple of important database objects available in Oracle.

DATABASE:

                Database is a collection many related data which are treated as single unit. Its main purpose it to store, retrieve and manipulate the stored data when required. Oracle is a OORDBMS - Object Oriented Relational Database Management System.

               

SCHEMA / USER:

                Schema and User both are the same in terms of Oracle. All the database objects must belong to any one of the schema. An object between the schemas can communicate if they have process privileges.

TABLESPACE:

                Oracle database is divided into one or more logical storage units which are called as Tablespaces. Tablespaces are further divided into segments, segments are further divided into extends. (i.e X segments group to form a segments. X segments group to form a Tablespace. Where X is a number greater than 1)

               

TABLE:

                Table is the basic location where all the data in the application are being stored. Data are stored as rows and columns. Where each table should have a unique name in the schema. And each table should have unique column names. And each columns should have a valid data type.  The regular tables are also called as Heap Organized Tables.

Note: There is no guarantee that the records will be retrieved in the same order that it was inserted. Need to use ORDER BY clause for it.

CLUSTER:

                Cluster is a combination of more than one table which share the same data blocks, its created for scenario where more than 1 table is joined and queried. Also those tables will have a common column which is shared between them.

               

INDEX:

                Index is a method by which the data of the table can be retrieved fast. Its very similar to Index in a book. In Oracle Index is for a table or cluster. I can have one or more columns. It speeds up the data retrieval in a SQL statement. At the same time if more index are there in a table it will eventually slow the DML operations on the table as every time the index also needs to be rearranged.

               

INDEX ORGANIZED TABLES (IOT):

                IOT's are very similar to an Oracle table, but the main difference is that they all the records are saved in a sorted order. When the table is queried the records are retrieved in the sorted order.

               

VIEW:

                A view in simple terms can be said as a saved SQL query. Whenever a view is called the saved SQL query is ran and the results are fetched. If any WHERE clause is added to view it will be added to the result of the SQL query. A view can also be used as an alternative for complex queries. The view result is generally considered as a virtual table.

               

MATERIALIZED VIEW:

                Materialized view is very similar to the view, but the main difference is that it does not re-query the saved query every time instead once its queried those data are took and saved against the materialized view. The MV can be refreshed based on ADHOC or on timely basis. MV are mainly used to calculate and keep a pre-computed set of results to avoid query long running.

               

TRIGGER:

                Trigger is a stored PL/SQL object, which are used to perform an action when some other actions happen. i.e Make a log entry when a user logs into the database when users login. Make note of the changes of the values when certain table is updated or inserted or deleted.

               

DATABASE LINK:

                Database Links are used to provide a link between more two databases, using which the data from one database can be assessed in the other database.

               

DIRECTORY:

                Directory is a database pointer to file location in the server in which Oracle Database is running. Directories are mainly used to access any files in that specified location. The DBA needs to be make sure that Oracle user have complete access to that directory location.

PROCEDURE:

                Procedure is a stored PL/SQL object, which are used to perform a set of operations either i.e group of PL/SQL statements. The procedure needs to be called each time when it needs to be executed. It doesn't return any value after executing.

               

FUNCTION:

                Function is a stored PL/SQL object, which are used to perform a set of operations either i.e group of PL/SQL statements. The function needs to be called each time when it needs to be executed. It should return one value to the calling environment of any valid data types.

PACKAGE:

                Package is a collection of Procedures, functions. The main advantage of having Package is to group all the related procedures and function into one logical unit. Also when the first procedure, function in the package the whole package is loaded into the memory.

SEQUENCE:

                Sequence is a method provided by Oracle which is used to generate numbers.

               

SYNONYM:

                Synonym is an alternative name of an existing table or view. The main purpose of having a synonym is to hide the identity of the underlying database object.

               

ROLES:

                Role is a database object which has a group of privileges assigned to it, which can granted to another role or user.

Please provide your comments below the blog.

SQL in breif

In this tutorial we are going to see the need of SQL in a database and what it is capable of doing to the database.

Basically SQL stands for Structured Query Language. The main purpose of SQL is manage the data which is available in any Database. SQL are mainly classified as following

DDL – Data Definition Language

DML – Data Manipulation Language

DCL – Data Control Language

TCL – Transaction Control Language

DQL – Data Query Language

SQL is mainly used to perform CRUD (Create Read Update Delete) operations in the database objects. Now lets the see in brief each of the classifications

Note: We are going to discuss the important things, to see the detailed please refer Oracle documentation.

Data Definition Language:

          DDL statements allows you to mainly create, modify, and drop any database objects. Also allows to control the privileges of the objects between the schema/user.

CREATE – Used to create any database objects.

ALTER – Modify any existing database objects.

DROP – Remove objects from the database.

RENAME – Rename an existing object.

TRUNCATE –Remove all the data of the table.

PURGE – Permanently remove an object from the database.

Data Control Language:

               DCL statements make sure which the data are accessed between the schemas

GRANT – Allows a user to perform a specific task.

REVOKE – Removes the access provided to a user.

Data Manipulation Language:

          DML statements mainly helps us in creating, modifying, deleting the contents of an existing table.

INSERT – Insert new data into the table.

UPDATE – Update any existing data in the table.

DELETE – Delete the existing data in the table.

MERGE – Merge the data between two tables.

Note: All the transactions need to be committed so that the changes are saved in the database. If any DDL statements occurs

Transaction Control Language:

          TCL statements are used to control the data changed by DML statements. The changes can be either saved or discarded.

SAVEPOINT – Make a point for rolling back a transaction

SET TRANSACTION – Change transaction option like isolation level and what segment to use.

ROLLBACK – Rollback all the changes made during this transaction

COMMIT – Save all the changes made to the disk

Data Query Language:

            DQL allows mainly used to query existing data in the table, and display it to the user it’s SELECT statement. The select statement has the capability to only to display the data which is already available it can also modify the data before displaying it to the user.

SELECT - Used to select the data from the database object.

Please provide your comments below the blog.

Layout's in Android

Layout in simple terms determines how the items are being displayed for the user in an activity in the app or widget. The layout can be fixed either at the design time or at the run time. Following are the few basic layouts in Android

1.       Linear Layout

Linear layout is used to align all its child elements in a specific direction either horizontally or vertically.

2.       Relative Layout

Relative layout is used to align all its child elements based on the position of other element within it. If no relative positioning is given all the elements will be in the same place.

3.       Table Layout

Table layout is used to group the elements as rows and columns. Where each rows has zero or one cells, where each cell can have a view object.

4.       Grid Layout

Grid layout is used to display a 2-dimensional scrollable grid, generally the items to the list are added using a List Adapter.

5.       Frame Layout

Frame layout is generally used to as a placeholder on the screen for displaying an item. It generally blocks out an area in the screen to display the single item.

6.       List View

List layout is used to display a list of items with a scrollable option, generally the items to the list are generally added using an Adapter. The list of items can be a text or custom designed by extending the Base Adapter.

These are the main layout which we will be working on.

Please provide your comments below the blog.

Android application key components

This tutorial explains some of the basic key components of an Android application, which will be helpful in creating an Android project.

Activities: An activity is a single screen in which a user interacts in an android application. An application can have more than one activity. When an application starts it calls the default activity mentioned in AndroidManifest.xml (Refer HelloWorld example in the previous blog) as highlighted below, this can be changed at any point of design time.

<activity

            android:name=".MainActivity"

            android:label="@string/app_name" >

An activity can call / start another activity when required.

Services: Service is the component that is used to perform action in the background for an application without any user interaction. It can used for logging or to perform work for remote process.  

Content Provider: It’s the standard interface to share data between processes and provide mechanism for defining data security. Used to store data in the file system or use SQL Lite. For example using the content provider the app can read and modify the contact details using the Android provided content provider.

Broadcast Services: It’s a way to listen to system wide even happening in your Android device which is accessible to all apps. Some of the Android provided broadcast services are Screen turned on, battery is low, call/SMS is received, phone is booted etc.

Intents: It’s a messaging object you can use to request an action from another component. An Intent is the key component to call one activity from another activity. For example from our app we can request camera app to take a picture.

Explicit Intent: Specify the component to start by name like call another activity in the same app.

Implicit Intent: Does not specify the component to start instead declare a general action to perform which allows a component from another app to handle that.

Fragments: Fragment is the section of the activity which represents the behavior or a portion of user interface in an activity. An activity can have more than one fragment. A fragment has its own life cycle which can be removed or added at any point when an activity is running.

Views: It’s the place where all the UI elements are being placed in the android application.

Layouts: Layout in Android describes how the UI should look in an activity. This can be declared in the UI XML and/or instantiate the layout at runtime. This can be customized as per required. There are various types of layouts like Linear, Relative, List, Grid etc.

Resources: Resource is a component of the Android application which is used to keep track of all the non-code assets of the application. Resources can be a XML file, images, audio files, etc. 

Please provide your comments below the blog.