Important views and terminologies in performance Tuning

By -

IMPORTANT VIEWS:
v$sql         
v$sql show the statistics on the shared sql area. Long running sql queries will update every 5-seconds in this view. It is ease to find the impact of the long running program.
v$sql_plan
It contains the execution plan information for each child cursor loader in library cache.
v$session
v$session show the session information for each current session.
v$process
It will show the currently active process.
v$session_wait
It will display the current/Last wait for each session
v$system_event
It will show the total wait event
dba_hist_sqltext
It will show the text of the sql statements this view capture the information from v$sql.
dba_hist_sql_plan
It will show the execution plan information this view capture the information from v$sql_plan.
​
V$SESSION:
Hash_Value:
Every SQL statement is generating unique value in library cache.
Example same SQL query running again Oracle find the hash value in library cache and executing same execution plan.
It retrieves the data from fast.
Plan Hash value:
Numerical representation of the SQL plan.
Comparing one plan hash value to another plan easily identifies whether two plans are same or not.
SQL_ID:
SQL Identified of the statement that is currently being executed.
SID:
SID is session identified. It’s used to unique identifier of database.
SERIAL#
Session serial number used to unique identifier of session objects.
Username:
Oracle OS Username
Status:
It will show the Status of the session
Active – Currently executing sql session
INACTIVE – session inactive
Killed – Session to be killed
Logon_Time:
When session is logon
Last_call_ET
It will show how much time in the session
Client_Identifier
It will show client identifier ex: client name/SSO id
Blocking_session:
It will show any blocking of the session 

V$SQL
Sql_txt:
This column will show the first 1000 characters of the sql statement
 Sql_fulltext:
It will show the complete sql statement text.
Sql_profile:
It will show the name of the sql profile
First_load_time:
When hard parse happened in first time
Last_load_time:
When hard parse happened in last time
Program_id
ID of the pl/sql program

V$process
Username:
It will show the OS username EX ($TWO_TASK)
Background:
1For the background process NULL for the normal process.
PGA_Used_mem:
PGA memory currently used by the process.
PGA_ALLOC_MEM:
PGA memory currently allocated by the process.
PGA_MAX_MEM:
Maximum PGA memory ever allocated by the processer.

dba_hist_sqltext
DBID:
It is the database id
SQL_ID
SQL Identified of the statement that is currently being executed
Command_Type
Oracle command type definition.

Comments

Post a Comment

Popular posts from this blog

How to troubleshoot long running concurrent request in R12.2

By -
Normally we are getting concurrent program running longer and concurrent request not picking jobs, etc. those are very frequent issue we expected from development team. Collect below basis level of information to development team 1 Oracle seeded program/Custom program? 2 How much time it used earlier? 3 Is there any recent code change done in concurrent program? 4 Is this program fetching higher data compare to last run? 5 Does this job running any specific time/ It can be run any time? 6 Does this job fetching data using DB link? 7 Does the problem happen on both the test and production instance? Once asked above questions to development team in meanwhile we need to start basic sanity check in our environment. 1 Verify the status of the concurrent program select REQUEST_ID,phase_code,status_code,ORACLE_SESSION_ID from apps.fnd_concurrent_requests where request_id=’1234567’; 2 Find which concurrent manger ran a specific concurrent request select b.USER_CONCURR
Read more

How to run Gather Schema Statistics in R12.2

By -
Image
What is Gather Schema Statistics? Gather Schema Statistics program generates statistics that quantify the data distribution and storage characteristics of tables, columns, indexes, and partitions. The cost-based optimization (CBO)  uses these statistics to calculate the selectivity of prediction and to estimate the cost of each execution plan. Why Run the Gather Schema Statistics? When Data is update, insert, delete in table of user end Like (Technical User ,Finance User ,Ect) It become necessary to run the Gather Schema Statistics It’s recommended to Run GSS in Weekly Once or twice. How to run Gather Schema Statistics? Login application with sysadmin user Submit Request Window Navigate to: Concurrent > Requests Enter the parameters This can be run for specific schemas by specifying the schema name or enter ‘ALL’ to gather statistics for every schema in the database Submit the Gather Schema Statistics program Parameters: Schem
Read more

How to compile forms in R12.2

By -
STEP 1--> Login to app server STEP 2--> Go to  directory $AU_TOP/forms?US STEP 3--> Move to ".fmb" file STEP 4--> Execute the below command to generate ".fmx" frmcmp_batch userid=apps/<apps_pwd> module=<form_name>.fmb output_file=<form_name>.fmx module_type=form batch=no compile_all=special (EX):-   frmcmp_batch userid=apps/apps module=JAINTAXT.fmb output_file=$JA_TOP/forms/US/JAINTAXT.fmx module_type=form batch=no compile_all= special (O/p):   Created form file /oracle/CLONE/apps/fs2/EBSapps/appl/ja/12.0.0/forms/US/ JAINTAXT.fmx Refer: R12: How to Compile a Form in Release 12 (Doc ID 1085928.1)
Read more