[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Total SQL Calls Per Day

Posted on 2009-04-16
6
Medium Priority
?
710 Views
Last Modified: 2013-12-18
Hello,

In an Oracle 10g database how can I determine how many user (none SYS\SYSTEM\SYSMAN etc.) SQL calls are occurring per day?

Ideally I am looking for a script or SQL call to determine this.


Thanks,

Michael
0
Comment
Question by:michael4606
  • 4
  • 2
6 Comments
 
LVL 18

Expert Comment

by:sventhan
ID: 24159081
Something to try..

select s.status, s.username, s.osuser, s.server, s.machine,
s.module, s.client_info, s.terminal,
s.program, p.program, s.logon_time, s.process, p.spid, p.pid, s.audsid, a.sql_text
from v$session s, v$process p, v$sqlarea a
where s.paddr = p.addr(+)
and s.sql_address = a.address
and s.sql_hash_value = a.hash_value
AND a.parsing_schema_id  in (select user_id from all_users where username not in ('SYS','SYSTEM'))
and a.last_active_time >= sysdate - 1

0
 

Author Comment

by:michael4606
ID: 24159762
Looks like a promising script.  Though I have been working with it intensely I do not quite have it working.  Would you please post a tested\working version.  

Thanks very much!

Michael
0
 
LVL 18

Expert Comment

by:sventhan
ID: 24159852
Just make it simple..
select  a.sql_text
from v$session s,v$sqlarea a
where s.sql_address = a.address
and s.sql_hash_value = a.hash_value
--AND s.username not in ('SYS','SYSTEM')
--and a.last_active_time >= sysdate - 1
ORDER BY LAST_ACTIVE_TIME DESC;  
/
if this works remove the comments and expand the sql.
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 

Author Comment

by:michael4606
ID: 24166954
sventhan,

It is returning one line -a SQL call.

I think I have not described what I need clearly enough.  

I need an easy way to count of how many SQL calls are run against an Oracle 10g database over a period of time.  I am defining a SQL call as all SQL statements executed by a client (application, SQLPlus etc.).  SELECTs and DML.

I do not know if v$sqlarea tracks all or a just finite set of SQL commands.  I seem to have exhausted Google efforts.  I really want to avoid installing a product having to use Stats Pack to determine this if possible.  I would like to the the 10g AWR has this info but I do not know the steps to extract it.

What can you recommend?

Thanks,

Michael
0
 

Author Comment

by:michael4606
ID: 24167265
Hey All,

To my delight I have found that AWR reports can be generated using:
@%ORACLE_HOME%\rdbms\admin\awrrpt.sql

This prompts for the intervals for start and end times and produces a VERY detailed report that includes a rich set of  SQL call information  (woohoo!).  I know what I need is in here I just need to interpret it correctly.  Yes I am that thick headed.

It looks like "Number of Transactions" undo UNDO is the total of SQL calls for the date range given but I could be wrong.

Can anyone advise me on interpreting an AWR report to determine the total SQL commands executed for the given date range?

Thanks,

Michael
0
 

Accepted Solution

by:
michael4606 earned 0 total points
ID: 24167517
Under "Instance Activity Stats"  "User Calls" seems like a good candidate too.  I am researching via Oracle docs and Google...

If you guys can confirm if this is the stat to go with please let me know.

Thanks again,

Michael
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses
Course of the Month18 days, 20 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question