Performance Tuning :: Difference Between System / User Sql In Tkprof Report?
Jun 11, 2010How can i differentiate between system issued sql's and user issued sql's in the tkprof report ?
View 5 Replies
ADVERTISEMENT
Performance Tuning :: TKPROF Output For Session
Jun 9, 2010understanding the TKPROF output for the session that was executing an insert statement(inserting 70 lakh data) on which a row level trigger get fired and select from following table.The trace was run for 1 hour.
Table name - > GS_MAP_RCC_CCIT
This table is having 37 rows as in on single block and having primary key index on RCC_NUM that is also contained on single block.We are getting maximum wait events on db_file_sequential_read.
As per my understanding this is due to the contention for the same block because for each row these queries get fired.
Performance Tuning :: Regarding Trace File And TKprof?
Apr 10, 2013i want to know about trace file and TKPROF. any example.
View 1 Replies View RelatedPerformance Tuning :: Memory Fault Error In Tkprof?
Aug 31, 2011I am trying to run a trace file with TKPROF. It throws an error :
MEMORY FAULT
The size of the trace file is 8MB. I tried with some other file trace files bigger in size (10MB) than the above file, it works fine. I tried seeing any permission right is required on that trace file is required for the tkprof to excute, but it has got the same permissions like the other trace files.
The problem is that it is not showing any error number or any other Error Description other than "MEMORY FAULT".
Performance Tuning :: Elapsed Time Not Accounted In Tkprof File
Jan 13, 2011find the attached tkprof'ed file of session
I started the trace after the query started (upon user's complaint)
However even after tracing the session for more than 30 minutes I am not geeting where the 30 minutes are accounted in this file
Performance Tuning :: Why There Is No Row Resource Operation Information In Tkprof Output
May 13, 2011why there is no "Row Source Operation" information when I trace a long and complicated SQL query (It takes at least 25 minutes ro run)?
However, I can get the "Row Source Operation" information for a simple SQL Query.
Here are the tkprof output for a simple query and complicated query:
tkprof Output for a simple query
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 13 0.00 0.00 0 16 0 173
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 15 0.00 0.00 0 16 0 173
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 3139
Rows Row Source Operation
------- ---------------------------------------------------
173 TABLE ACCESS FULL MY_TABLE (cr=16 pr=0 pw=0 time=0 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 13 0.00 0.00
SQL*Net more data to client 24 0.00 0.00
SQL*Net message from client 13 5.33 5.47
Tkprof output for a complex query
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.38 0.40 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.38 0.40 0 0 0 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 3139
No Row Source Operation information here Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
db file sequential read 73062 0.25 692.23
db file scattered read 16099 0.18 76.65
latch: shared pool 4 0.02 0.03
latch: cache buffers chains 3 0.04 0.04
latch free 16 0.00 0.00
Performance Tuning :: Difference Between Cost And Bytes
Aug 4, 2010I am working on Tuning a Materialized view. I was getting an error saying ORA-12008: error in materialized view refresh path
ORA-12801: error signaled in parallel query server P002, instance hrms-stg-db01:HRSTG1 (1)
ORA-01652: unable to extend temp segment by 16 in tablespace TEMP1.
so i increased the temp size. I am trying out various hints to get it rectified. But i am not sure if i should go for less cost or less bytes.
Performance Tuning :: How To Determine System Memory Usage By Oracle Processes
Jun 20, 2012we have 96GB Memory on the UNIX server and 85% of its usage shows oracle processes I want to determine which Oracle processes are taking most of the memory
SGA is around 36G
SGA_TARGET is 40G
PGA is around 4G
the total of around 40-45 GB of usage is understandable but what other oracle process are chewing up the remaining 30-40 GB on the server is not known
load averages: 7.35, 6.46, 6.15; up 248+11:33:21 12:25:03
2202 processes: 2196 sleeping, 1 zombie, 5 on cpu
CPU states: 83.8% idle, 10.5% user, 5.8% kernel, 0.0% iowait, 0.0% swap
Memory: 96G phys mem, 15G free mem, 128G total swap, 128G free swap
PID USERNAME LWP PRI NICE SIZE RES STATE TIME CPU COMMAND
21720 oracle 258 0 0 40G 40G cpu/48 215:28 2.04% oracle
10709 oracle 1 0 2 1816K 1448K cpu/9 0:02 0.90% res_conf_email_
[code]......
Performance Tuning :: Difference Between Move And Shrink Table?
Jul 22, 2011What is different between move table and shrink table?
View 2 Replies View RelatedPerformance Tuning :: Tune Data Coming From Legacy System Into Oracle Database
May 22, 2012Customer is sending data from legacy system (Source) with the web service which in turn calls a package lying on Oracle server (Target). Now this package is simply inserting data passed by legacy system into master staging table in Oracle database. When they started this process in Sept 2011 then 4 lack records were inserted into staging table. In Oct 11 it was 0 records Nov 11 it was 2 lack records, Dec 11 it was 1 lack records, in Jan 12 it was 1 lac records, Feb 12 73k records, Mar 12 0 records, Apr 12 52k records.
As we see that number of records inserted in the table got reduced with time.. what should be the starting point here since web service is calling that package on the fly, how can i enable trace for that package? I cannot replicate this is Dev as this process is only working in PROD.
Performance Tuning :: Difference Between Buffer_gets / Buffer_cache And Disk_reads / Physical_reads
Aug 13, 2012Is there any difference between:
1. buffer_gets/buffer_cache
2. disk_reads/physical_reads
My database is facing slow performance. When I looked into it, I found it has high buffer_gets (1388159791) and low_disk_reads (130132).
Queries are working fine, as I verified their execution plans while they are running.
Forms :: Difference Between System.current_item And System.cursor_item?
Apr 1, 2012We have system.current_item and system.cursor_item.....
both represent the same....
like this we have few other system variables also .....
what is the main difference between current_item and cursor_item?
Performance Tuning :: AWR Report Evaluation
May 20, 2010My DB is oracle10g.
I have AWR report comparison for two different days. I want to find the below things.
1. Which day has better performance?
2. What are the top two findings on the report.
I attached the report.
Here are my answer. Please correct me if i am wrong.
1. Which day has better performance? Second day has higher load. Since redosize is showing very high.
2. What are the top two findings on the report.
a) Compared to two days, first day, little bit more I/0 wait for single block read.
b) Compared to two days, 2nd day, it takes higher CPU.
However, which day is best compared to two days?
Performance Tuning :: No Snapshot Available For AWR Report
Feb 1, 2011I am trying to generate AWR report for database observation. But I am not getting any snapshot listed there. below is the output of my awrrpt.sql
SQL> @?/rdbms/admin/awrrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
1140984076 AFCCV 1 afccv
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text Defaults to 'html'
Enter value for report_type: html
Type Specified: html
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 1140984076 1 AFCCV afccv SERVICEDB1
Using 1140984076 for database Id
Using 1 for instance number
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent (n) days of snapshots being listed. Pressing <return> without specifying a number lists all completed snapshots.
Enter value for num_days: 3
Listing the last 3 days of Completed Snapshots
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap:
Performance Tuning :: Get AWR Report Data?
Aug 8, 2012I am having only select_catalog_role in database. Can I take complete AWR report data from awr views without using DBMS_WORKLOAD_REPOSITORY package?
View 6 Replies View RelatedPerformance Tuning :: Find The Last User Analyzed Particular Table?
Aug 9, 2012How can i find the last user analyzed particular table?
I can able to find last analyzed date, but can't able find the which user has done it.
Performance Tuning :: Time Calculation In AWR Report
Dec 12, 2011Till statspack we had
elapsed time = time spent on waits + time CPU was used
Total time during snaps = Elapsed time + (may be) time waited for CPU...In AWR is it possible to draw such equation? I can see that the AWR report has following elements
1) End Snap time - Begin Snap time
2) DB time - as mentioned at the top of AWR report
3) DB CPU - in "Top 5 Timed Foreground Events" (I assume this is 'CPU used by sesson timing' in statspack)
4) Total of time for all Statistics in "Time Model Statistics"
5) BUSY_TIME + IDLE_TIME - "Operating System Statistics"
Time between 2 snapshots? or what else? Also for which seconds to multiply to 'DB Time(s)' per second and 'DB CPU(s)' per second in Load Profile to get the db time and CPU time?
Performance Tuning :: Generate HTML AWR Report?
Jan 10, 2012I would like to generate HTML awr report and save it to my local machine.
After running $ORACLE_HOME/rdbms/admin/awrrpt.sql in sqlplus and specifying HTML in (Enter value for report_type: HTML) i hvae get numerous html code in my sqlplus prompt.. I want to save the html report in local machine and open it by double clicking on it.. it will be opened in a browser..
Performance Tuning :: AWR Report In 11g Standard Not Supported?
Jan 15, 2011I was trying to generate AWR report, but the report which got generated consist most of the sections without data. Later i came to know that AWR report is not fully supported in 11g? Is that true?
View 6 Replies View RelatedPerformance Tuning :: AWR Report For Specific Date
Jun 4, 2010I want to take AWR report at specific date ex(25-05-2010).
View 2 Replies View RelatedPerformance Tuning :: Database Time In AWR Report
Jul 9, 2012I understand that when data is read from the disk, I/O is done..And When computations are done then CPU is used..Then where the following equation fits?
DB Time = sum of database CPU time + waits
Is I/O considered as a part of CPU time?
Does this equation changes with SAN, OS caching?
Performance Tuning :: How To Read And Understand AWR Report
Sep 9, 2011One of my procedure recently taking very long time to execute. I'm attaching AWR report. But I don't know how to read and understand this.
View 4 Replies View RelatedPerformance Tuning :: AWR Report - Find Disk I/O
Mar 11, 2012In AWR report, in order to find the disk i/o, Should I see the avg read(ms) under Tablespace I/O and Filesystem I/O columns?
View 1 Replies View RelatedPerformance Tuning :: Capacity Planning For 250 User OLTP Shop
Mar 19, 2013 installing Oracle for a new Data-Center(still in planning).It will be an OLTP shop working 24/7, with 250 concurrent users.
1. Can it, in theory run with good performance on a quad processor(something like Intel Xeon Series 56XX) , with 32GB of memory?
2. What is the Oracle Database Edition required for this configuration?
3. What additional things should one take into account to plan the most cost-effective configuration?
Performance Tuning :: Oracle Documentation On Details Of AWR Report
Jun 4, 2012A coworker of mine asked if there was any documentation from Oracle that listed all of the parts of the AWR report and what each meant. I was taken back because I don't think there is. There are third party books that talk about AWR reports and their predecessor Statspack reports.
Oracle has some notes on their support site about reading an AWR or Statspack report. All I found in the official documentation was some basic information about how to run an AWR report and an overview of what it was. It would be nice to have some sort of documentation that lists out each section and explains the units and purpose.
Performance Tuning :: AWR Report Generation For Short Duration
Nov 5, 2012Is it possible to generate AWR report for the duration of 5 min? As we know that snapshots are generated for every 1 hour, which we specified in parameter.
By changing the parameter to 2 min, what could be the impact on database?
Performance Tuning :: Application (software / Utility) For Report?
Jul 28, 2010Now, I'm tired to capture one-by-part image, and realize.
I've not seen any application or utility to make this work easier to me.
Performance Tuning :: ASH Report - SQL Has Been Executed For 1567 Times?
Sep 12, 2012In ASH report
there is a section that goes like this
SQL ID Planhashed Sampled # of Executions % ActivityEvent% Event Top Row Source
fdy93qpr1227 1567 7.58direct path read 3.65TABLE ACCESS - FULL
does it suggest that this SQL has been executed for 1567 times is this correct .
Performance Tuning :: AWR Report Network Foreground Wait Events?
Jun 29, 2011I ran an AWR report. The database looks fine, but a data load that loaded 1 Million rows an hour is now doing 500K per hour.
Wait Class Waits %Time -outs Total Wait Time (s) Avg wait (ms) %DB time
DB CPU 224 80.70
Other 2,668 0 28 10 9.99
System I/O 4,753 0 9 2 3.23
Administrative 1 0 6 5543 2.00
Commit 357 0 4 11 1.46
[code]....
The network value for wait: 630,601. What does this mean? Anything I should look at? When it was 1million per hour, the value was 4,563,000.
Top 5 Timed Foreground Events
Event Waits Time(s) Avg wait (ms) % DB time Wait Class
DB CPU 224 80.70
unspecified wait event 2,666 28 10 9.99 Other
control file sequential read 4,753 9 2 3.23 System I/O
switch logfile command 1 6 5543 2.00 Administrative
log file sync 357 4 11 1.46 Commit
Performance Tuning :: Top Wait Events - Specific Document To Analyze AWR Report
Jun 24, 2013We are using 11.2.0.3.0 on solaris 10 facing slow performance, following are the Wait Events in AWR report, Also if any specific document to analyze AWR report and to pin point the performance bottleneck.
Foreground Wait Events
**********************
Avg
%Time Total Wait wait Waits % DB
Event Waits -outs Time (s) (ms) /txn time
-------------------------- ------------ ----- ---------- ------- -------- ------
direct path read 308,729 0 21,191 69 58.0 39.5
db file sequential read 208,754 0 3,742 18 39.2 7.0
cursor: pin S 19,541,899 0 2,561 0 3,668.5 4.8
[code]....
