Performance and Tuning - SQL query

-

Performance and Tuning

Below are few steps to use for performance and tuning of long running queries in Oracle database.

  1. Identification --> Use any tool such as Profiler, Execution Plan, AWR report
  2. Analysis --> Analyze the data volume of tables being used in query
  3. Create an Index, Only If required**
  4. Update table's statistics
  5. Rebuilding table, indexes etc.

1] Query Execution Plan
  • It list out all details of how the RDBMS plan on query processing
  • How the indexes will be used
  • How joins will be performed
  • Estimate cost
  • it will tell optimizer to perform certain action
  • Index hint is special syntax that you can put in SQL query
  • Order of table name (Choose selective table first which having where condition)
2] Analyze Plan
  • Driving table
  • Join order
  • Join methods
  • Loops --> Nested, Hash join, Merge sort
  • Full table scan
  • Indexes are present but not used properly

Flow of query execution:
  • Query checked for semantics
  • Parsing
  • Parsing can be Hard or Soft parse
  • Oracle check, if the parsed statement exist in library cache
  • If exist, the statement is used, otherwise it will be hard parsed & placed in library cache
  • Optimizer then decides on best execution plan and execute the statement
  • The data to be retrieved is fetched from buffer cache if already exist, if not then data blocks are read data fetched and stored in buffer cache & finally present the values to session based on requesting data

Comments

Post a Comment

Popular posts from this blog

PUTTY - The server's host key is not cached in the registry cache

-
Error: "The server's host key is not cached in the registry. You have no guarantee that the server is the computer you think it is. The server's rsa2 key fingerprint is: [SSH KEY FINGERPRINT] If you trust this host, enter "y" to add the key to PuTTY's cache and carry on connecting. If you want to carry on connecting just once, without adding the key to the cache, enter "n". If you do not trust this host, press Return to abandon the connection. Store key in cache? (y/n) y" Answer: Simply run this command in your windows preferred terminal (cmd.exe etc) and agree to accept the host and store the cache: C:\Program Files (x86)\PuTTY\plink.exe -ssh [SSH USERNAME]@[SSH HOST] -P [SSH PORT] echo y | pscp -i /path/to/key/file user@remote:/tmp/file  . echo y | plink -i /path/to/key/file scripts.sh Usually you would hit “y” here, assuming the host key is correct, in order to store it in future connection.  The storage of this goes into the Registry under ...
Read more

OIM-12c Installation - FMW - SOA - IDM

-
Image
Oracle Identity Manager (OIM) 12c Installation Prerequisites: The Oracle Identity Manager software includes the following: 1] Oracle's Java Development Kit (JDK) 1.8 (jdk-8u311-linux-x64.tar.gz) 2]  Fusion Middleware 12.2.1.4.0 3]  SOA Suite 12.2.1.4.0 4] Oracle Identity and Access Management  12.2.1.4.0 What Do You Need? For completing Oracle IDM Setup you will need, Environment as below: Oracle Enterprise Linux 6 or similar, with access to the Internet At least 8GB RAM (16GB recommended) An Oracle Database Enterprise Edition 12 or superior installed and running. Note: Installing a Database for Identity and Access Management (IDM) Suite provides instructions on how to install an Oracle Database that is suitable for Oracle Identity and Access Management Suite. oracle and root user credentials SYS database user credentials Basic understanding of Linux An Oracle Technology Network (OTN) account OIM-11g PS3 Installation Steps: Just for reference https://www.oracle.com/webfol...
Read more

Apache Kafka - Zookeeper

-
Apache Kafka - Zookeeper Tutorial Topics covered in this article as below: Need of Messaging System What is Kafka? Kafka Features Kafka Components Kafka Architecture Installing Kafka Working with Single Node Broker Cluster 1. Need of Messaging System Data pipelines - Communication is required between different systems in the real-time scenario, which is done by using data pipelines Chat Server -----------Data Pipeline---------------> Database server Many services are communicating with various internal systems like front-end, back-end Api, database server, security system, real-time monitoring, chat server etc. Messaging systems helps managing complexity of above pipelines. so Kafka helps here to decouples data pipelines Key entity involved in Kafka Producer (Publisher) Consumer (Subscriber) 2. What is Kafka? (High throughput distributed messaging system) Apache Kafka is a distributed publish-subscribe messaging system It was originally developed at Linked-In and later became part ...
Read more