No question at this time
DBA Top 10
1 M. Cadot 12200
2 B. Vroman 6300
3 A. Kavsek 5600
4 J. Schnackenberg 3200
5 P. Wisse 2900
6 J. Péran 2200
7 T. Boles 600
8 Z. Hudec 400
8 G. Lambregts 400
8 N. Havard 400
8 F. Pachot 400
8 D. Dave 400
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48570
Total active users1387
Act. users last 24h1
Act. users last hour0
Registered user hits last week32
Registered user hits last month257
Go up

READ WRITE OPERATIONS
Next thread: A few little things after migrating to 19.9
Prev thread: RESTful web services

Message Score Author Date
Dear Experts, We do not have O.S Watcher, Is th...... Jill Salalila Dec 12, 2020, 09:05
Hello Jill, -A- "<i>reads, inserts, updates</i>...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Bruno Vroman Dec 12, 2020, 15:59
Hi Bruno Vroman Thanks for an update. We have...... Jill Salalila Dec 12, 2020, 22:12
Hello Jill, -A- the figures are at DB level, yo...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Bruno Vroman Dec 13, 2020, 12:57
Bruno , Thank you for given reply. <b> >> co...... Jill Salalila Dec 13, 2020, 18:36

Follow up by mail Click here


Subject: READ WRITE OPERATIONS
Author: Jill Salalila, Philippines
Date: Dec 12, 2020, 09:05, 35 days ago
Os info: RHEL 7..2
Oracle info: 19C
Message: Dear Experts,

We do not have O.S Watcher, Is there any possibility to track below details within DB ?

I could not get accurate data, Kindly suggest me below some best approach methods.

During sparing hour(10:30 AM to 11:30 AM UTC)

1) No. of transactions per second
2) No. of reads, inserts, updates

=> Daily average

1) No. of transactions per second
2) No. of reads, inserts, updates

Regards,
Jill …
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: READ WRITE OPERATIONS
Author: Bruno Vroman, Belgium
Date: Dec 12, 2020, 15:59, 34 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Hello Jill,

-A- "reads, inserts, updates"
Well, maybe you want this for some well know table(s)?
Then some work with DBA_TAB_MODIFICATIONS (and DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO) might help for inserts, updates (and deletes), by checking the evolution of values over time. If reads means that you want how many times SELECT has been issued, or how many rows have been selected (1 select can retrieve 0, 1 or several rows), I don't see how to do it and anyway this doesn't seem useful.

But if you ask this "for all tables without distinction" than I would say that the requirement is perhaps no so meaningful... (Continue to read: '-B-...')

-B- something else:
Do you have access to AWR (Automatic Workload Repository)? (this requires access to the DIAGNOSTIC pack)?

If yes, AWR reports covering the desired time intervals might contain more pertinent information...

If no, hmmm, first check if you don't use it anyway (and in that case you should review your license quickly)...
Have a look at MOS Doc Id 1490798.1 AWR Reporting - Licensing Requirements Clarification
If you don't use it and don't have the license, I would recommend to convince your manager that AWR is necessary for your job...

-C- and lastly
At system level, you can also work with V$SYSSTAT: at time t0, store the current values that interest you in a table TTT, and at time t1 query the current value and compute:
  ( current_value - stored_value ) / ( t1 - t0 ) * 86400 = delta_per_second.
(of course you can also store the values at t1 like done at t0 -table TTT has just to have a column "moment"-, and repeat at t2, t3, ..., then you can compute rates between any couple (ti tj)).

Best regards,

Bruno Vroman
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: READ WRITE OPERATIONS
Author: Jill Salalila, Philippines
Date: Dec 12, 2020, 22:12, 34 days ago
Message: Hi Bruno Vroman


Thanks for an update. We have license to access AWR.

SQL>show parameter control 

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_management_pack_access string DIAGNOSTIC+TUNING

-------------------------------------------------------------------------------------------------------------

1) Are these accurate details per second for entire DB ?

  System Statistics - Per Second

I# Logical Reads/s Physical Reads/s Physical Writes/s Redo Size (k)/s Block Changes/s User Calls/s Execs/s Parses/s Logons/s Txns/s

1 649,331.67 1,376.36 391.69 2,692.96 18,010.69 4,422.61 3,574.75 1,091.92 5.93 220.91
2 221,931.01 3,047.36 129.53 994.62 8,478.98 4,261.51 4,126.70 1,059.42 5.25 263.61
3 192,402.95 452.79 213.29 2,405.32 19,809.51 6,805.95 4,473.78 1,756.31 4.91 1,177.37


FYI : IS there any possibility to track total number of transactions per second against any table or whole DB ?


>> IO Profile details fetched from AWR :

IO Profile (Global)  

Statistic Read+Write/s Reads/s Writes/s
Total Requests 2,633.32 1,058.31 1,575.01
Database Requests 1,150.87 965.24 185.64
Optimized Requests 0.00 0.00 0.00
Redo Requests 621.83 621.83
Total (MB) 84.75 60.01 24.74
Database (MB) 43.84 38.10 5.74
Optimized Total (MB) 0.00 0.00 0.00
Redo (MB) 5.95 5.95
Database (blocks) 5,611.01 4,876.51 734.50
Via Buffer Cache (blocks) 3,961.78 3,305.55 656.23
Direct (blocks) 1,649.23 1,570.95 78.28

Thanks a lot
Regards,
Jil …
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: READ WRITE OPERATIONS
Author: Bruno Vroman, Belgium
Date: Dec 13, 2020, 12:57, 33 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Hello Jill,

-A- the figures are at DB level, you will not find something like "3txn/second involving SCOTT.EMP"...

-B- keep in mind that the fact that you use AWR and have the parameter set to "DIAGNOSTIC+TUNING" does not mean that this is in line with your contract. It is very easy to use options with Oracle, even paying ones that we are not supposed to use...

-C- could you have a look at DBA_TAB_MODIFICATIONS?

-D- are you interested is a few known tables (then maybe you can design some features to take care of them) or would you like to be able to ask the info "for any table a posteriori" (I mean: nothing special declared before, then the request "how many times has SELECT been run against SCOTT.EMP between t1 and t2?"
Maybe you can also have a look at the various AUDIT features...

-E- (and one more time: can we question the requirement itself? What is the ultimate goal?)

HTH,

Bruno
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: READ WRITE OPERATIONS
Author: Jill Salalila, Philippines
Date: Dec 13, 2020, 18:36, 33 days ago
Message: Bruno ,

Thank you for given reply.

>> could you have a look at DBA_TAB_MODIFICATIONS ?

Yes, but Application team request not for a single table.


>> can we question the requirement itself? What is the ultimate goal ?

All Queries are from Application Team request. I am pasting following below ..


1) Can you please provide us the count of DB Transactions on PRD_SBJ_OBJS EXECUTIONERRORS table in last 1 month and last 1 year?

--- Seriously no idea about how do we track transaction details against one specific table. I guess NOT Possible to track.

Heading to find number of transactions for whole DB in terms of seconds

=> During sparing hour(10:30 AM to 11:30 AM)

a) No. of transactions per second
b) No. of reads, inserts, updates

=> Daily average

c) No. of transactions per second
d) No. of reads, inserts, updates

We have updated to Appl Team that AWR report provide transaction count , so not possible to count DML per seconds.

=> Per Sec Transaction from all 3 instance Time-Frame

Peak hour XXX approx x:00 - x:00 AM UTC
Non Peak xxx approx x:00 - x:00 AM UTC

Kindly update your inputs, it would help us heading to right direction.

Regards,
Jill …



Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here