Skip to Main Content
  • Questions
  • How to differentiate between internal system queries and application queries in Oracle

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Rajatabha.

Asked: October 13, 2020 - 9:22 am UTC

Answered by: Chris Saxon - Last updated: October 13, 2020 - 4:03 pm UTC

Category: SQL - Version: 11g

Viewed 100+ times

You Asked

Hi Team,

Firstly, I want to thank you for all the immense help that we continue to receive from your end, making us a better, knowledgeable Oracle developer.
I've this weird question related to internal queries that Oracle fires and the queries that we, the user fires (either from application, or sql developer, sql prompt).

(Environment: Oracle 11g R2)

select sql_fulltext,sql_id,executions,service,parsing_schema_name,rows_processed,optimizer_cost from v$sql
where to_date(last_load_time,'yyyy-mm-dd/hh24:mi:ss')>=
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') and service='SYS$USERS' and parsing_schema_name <> 'SYS'
and rows_processed>0


Below is the sample output:

      select count(*) from all_objects where object_name = :NAME and owner = :OWNER gq4p31m39qpms 1 SYS$USERS HR 1 4
      select last_load_time,sql_fulltext,sql_id,loaded_versions,fetches,executions... 2hx8s4mqk6qfc 2 SYS$USERS HR 100 1
      begin  DBMS_UTILITY.NAME_RESOLVE (   name          => :NAME,    context........ ats0vqbmfytk8 1 SYS$USERS HR 1 0
      select * from v$sql                                                         gtzvs9utsg0qs 1 SYS$USERS HR 500 1
      SELECT dbms_transaction.local_transaction_id FROM dual                         gqj5gaygdbfs5 2 SYS$USERS SCOTT 2 2
      select * from employees                                                         f34thrbt8rjt5 3 SYS$USERS HR 150 3
      select owner,object_name from all_objects where object_type in ('TABLE',....... 3yqkryv9c3nhk 1 SYS$USERS HR 2 72


Out of these, only select * from employees/Select * from v$sql/(the above stated query) are executed by the user from sql developer/sql prompt/application. Rest of them are all fired by oracle internally.

My question is, how can I find these queries only, the ones that are fired from sql developer/application/sql prompt. Is there any other metadata views/parameters/columns by which we can segregate these queries as fired by the user/application and queries fired by Oracle internally.

Please help in this regard

and we said...

There may be no developers/DBAs who written statements like these:

select count(*) from all_objects where object_name = :NAME and owner = :OWNER

begin  DBMS_UTILITY.NAME_RESOLVE (   name          => :NAME,    context........


But they most likely are fired from SQL Developer/SQL*Plus/etc.!

SQL Developer in particular has loads of things you can do that will run SQL queries, such as:

- Viewing objects the schema browser
- Generating auto-complete table/column name suggestions
- Describing/infoing a table
- etc.

For example, if I run:

desc t


In SQL Dev, it runs the two statements above; they are coming from the application. Though you may not realise it!

This makes them effectively indistinguishable from user typed statements when looking in v$sql or similar.

So the question here is: what's your goal? Why do you care and what are you trying to find out?

and you rated our response

  (4 ratings)

Reviews

Extension to oracle internal query segregation

October 13, 2020 - 11:17 am UTC

Reviewer: Rajatabha Dutta from India

Hi Chris,

Thanks a lot for the quick reply.
My goal/target here is to collect all the queries that has been executed by the user/application/procedures/functions only, every 30-45 mins interval
Chris Saxon

Followup  

October 13, 2020 - 1:27 pm UTC

These are queries executed by the application! That application being SQL Dev/SQL*Plus/...

foreground connections.

October 13, 2020 - 12:17 pm UTC

Reviewer: Rajeshwaran, Jeyabal

.....
My goal/target here is to collect all the queries that has been executed by the user/application/procedures/functions only, every 30-45 mins interval
.....


that should be available in dba_hist_active_sess_history - where session_type ='FOREGROUND'.

not only it provides the sql_id of all sql's, but also nicely summarizes those execution details.
Chris Saxon

Followup  

October 13, 2020 - 1:28 pm UTC

Remember ASH uses sampling - relying on this will miss some queries.

Difficult

October 13, 2020 - 2:33 pm UTC

Reviewer: David D. from PARIS from FRANCE


Hello masters,

Rajatabha, you said you want only the user's orders so what are the differences between user's orders and internal (SYS I presume ) orders? SYS use only the real tables (tab$, user$...) in the FROM clause, not the views like DBA_TABLES, USER_SYNONYMS...

I think you can begin by filtering orders which are using ***$ in the table's name. This is a beginning but it will eliminate many orders.


For example, to be sure, I use trace 10046 for a CREATE TABLE to see internals orders.
          CONNECT HR/HR@orcl;
          
          SQL> ALTER SESSION SET timed_statistics = TRUE;
          SQL> ALTER SESSION SET max_dump_file_size = unlimited;
          SQL> ALTER SESSION SET TRACEFILE_IDENTIFIER = 'zztest_create_table01.trc';
          SQL> ALTER SESSION SET events '10046 trace name context forever, level 4';


The CREATE TABLE.
          SQL> CREATE TABLE ZZ01 (ID NUMBER primary key, NOM VARCHAR2(50), PRENOM VARCHAR2(50));

          SQL> ALTER SESSION SET events '10046 trace name context off';


The trace file is here; and I use TKPROF to make him more readable.
         SQL> SELECT value FROM   v$diag_info WHERE  name = 'Default Trace File';
          VALUE
 --------------------------------------------------------------------------------
          /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/trace/orcl12c_ora_4616_zztest_create_table01.trc
          
          Unix$ tkprof orcl12c_ora_4616_zztest_create_table01.trc orcl12c_ora_4616_zztest_create_table01.txt



Exemples of orders executed by SYS : like we see, they use ***$ in the table's name.
          SQL ID: 0sbbcuruzd66f Plan Hash: 2239883476
          select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#,
            sample_size, minimum, maximum, distcnt, lowval, hival, density, col#,
            spare1, spare2, avgcln, minimum_enc, maximum_enc
          from
           hist_head$ where obj#=:1 and intcol#=:2
          
          
          SQL ID: 2sxqgx5hx76qr Plan Hash: 3312420081
          select /*+ rule */ bucket, endpoint, col#, epvalue, epvalue_raw,
            ep_repeat_count, endpoint_enc
          from
           histgrm$ where obj#=:1 and intcol#=:2 and row#=:3 order by bucket
          

          SQL ID: 06gfrprr7w0r2 Plan Hash: 2709293936
          select name,password,datats#,tempts#,type#,defrole,resource$, ptime,
            decode(defschclass,NULL,'DEFAULT_CONSUMER_GROUP',defschclass), spare1,
            spare4,ext_username,spare2,nvl(spare3,16382),spare9,spare10
          from
           user$ where user#=:1



Chris Saxon

Followup  

October 13, 2020 - 4:02 pm UTC

True, but recursive SQL statements like this aren't the issue here.

The problem is SQL Developer and the like run queries against the data dictionary. These aren't "internal" or sys database statements. They come from the app, making them practically indistinguishable from queries I've written myself and sent by my actions in the application.

Follow up on Application specific queries

October 13, 2020 - 2:52 pm UTC

Reviewer: Rajatabha Dutta from India

Hi Chris,

I'm talking about the queries related to application only. Lets say the application is using several tables EMP, DEPT, REGION etc, under schema HR, SCOTT. From the application/ SQL dev/SQLPLUS, all the queries are fired joining those tables using HR, SCOTT schemas and other underlying tables only. I want to get the list of those queries specifically, that has been fired or are being fired within several intervals.
Chris Saxon

Followup  

October 13, 2020 - 4:03 pm UTC

If you want to see queries against tables in a given schema, auditing is what you want to look into.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.