Solved

Is SQL_ID in Oracle reused?

Posted on 2008-10-23
5
3,355 Views
Last Modified: 2013-12-18
In Oracle 10.2.0.3, how and when is the sql_id generated? e.g. will "select * from a;" have the same sql_id for all "select * from a;" queries all throughout or will it get a different id once it is removed from the cache/on DB restart?
How long is it's lifespan before the id can be reused (if it is reused)?

Thanks!
0
Comment
Question by:dkaisla
  • 2
  • 2
5 Comments
 
LVL 4

Expert Comment

by:hqassap
ID: 22802395
The SQL_ID of the V$SQL and V$SQL_TEXT and some other Dynamic Performance Views are only unique in the Instance Life cycle; it can be repeated only after the instance bounced.

0
 
LVL 4

Author Comment

by:dkaisla
ID: 22817999
ok, the reply is vague. sql_ids are definitely unique per sql statement per instance life cycle, i get that. you say it can be repeated, but you didn't say if the same sql statement will get the same sql_id. so reiterating my 2nd question, will "select * from a;" have the same sql_id for all "select * from a;" queries all throughout or will it get a different id on DB restart?

e.g.
Day 1
DB start
select * from a; (sql_id - abcdef1234567)
Day 2
select * from a; (sql_id - abcdef1234567)
Day 3
DB restart
select * from a; (sql_id - abcdef1234567 - ???? or will it have another sql_id??)

any reference will definitely help. thanks!
am i just not understanding the explanation above and i need someone to spell it out for me or am i asking the wrong questions? hehe.. thanks
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 250 total points
ID: 22819201
the same statement can have different sql_id's within the same same lifetime for different reasons.

One way is two different people parsing it.

The same id can be repeated within the same lifetime for the same sql too, that can be shown by
changing an optimizer parameter.

Fortunately, these are easy to show simply by doing it.  :)


Here I log in as SYS shutdown and startup to give a clean slate,
select from emp
check the sql_id
change optimizer
select from emp
see same id used twice
then I login as myself (SDS)
select from emp
new sql_id generated
reconnect as SYS
shutdown startup
select from emp
original sql_id is reused.


As far as I know, the exact hash mechanism for generating a sql_id is not published,  from my observations I would
assume it is very likely that the same statement for the same user would generate the same sql_id "most" of the time.

Since it's a hash code, it may be affected by things that affect hashing algorithms, like available memory (may create different hash tables based on memory structures)
different version and patch levels may affect the algorithm,  other sql statements in memory may change the id as well as you may get hash collisions forcing shuffling in the tree.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
 
Total System Global Area  535662592 bytes
Fixed Size                  1348508 bytes
Variable Size             352324708 bytes
Database Buffers          176160768 bytes
Redo Buffers                5828608 bytes
Database mounted.
Database opened.
SQL> select * from scott.emp;
 
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7369 SMITH      CLERK           7902 17-DEC-80        800
        20
 
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300
        30
 
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500
        30
 
 
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7566 JONES      MANAGER         7839 02-APR-81       2975
        20
 
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400
        30
 
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850
        30
 
 
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7782 CLARK      MANAGER         7839 09-JUN-81       2450
        10
 
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000
        20
 
      7839 KING       PRESIDENT            17-NOV-81       5000
        10
 
 
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0
        30
 
      7876 ADAMS      CLERK           7788 12-JAN-83       1100
        20
 
      7900 JAMES      CLERK           7698 03-DEC-81        950
        30
 
 
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7902 FORD       ANALYST         7566 03-DEC-81       3000
        20
 
      7934 MILLER     CLERK           7782 23-JAN-82       1300
        10
 
 
14 rows selected.
 
SQL> select sql_id from v$sql where sql_text like 'select * from scott.emp%';
 
SQL_ID
-------------
ggqns3c1jz86c
 
SQL> alter session set optimizer_index_cost_adj=50;
 
Session altered.
 
SQL> select * from scott.emp;
 
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7369 SMITH      CLERK           7902 17-DEC-80        800
        20
 
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300
        30
 
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500
        30
 
 
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7566 JONES      MANAGER         7839 02-APR-81       2975
        20
 
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400
        30
 
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850
        30
 
 
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7782 CLARK      MANAGER         7839 09-JUN-81       2450
        10
 
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000
        20
 
      7839 KING       PRESIDENT            17-NOV-81       5000
        10
 
 
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0
        30
 
      7876 ADAMS      CLERK           7788 12-JAN-83       1100
        20
 
      7900 JAMES      CLERK           7698 03-DEC-81        950
        30
 
 
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7902 FORD       ANALYST         7566 03-DEC-81       3000
        20
 
      7934 MILLER     CLERK           7782 23-JAN-82       1300
        10
 
 
14 rows selected.
 
SQL> select sql_id from v$sql where sql_text like 'select * from scott.emp%';
 
SQL_ID
-------------
ggqns3c1jz86c
ggqns3c1jz86c
 
SQL> connect sds
Enter password:
Connected.
SQL> select * from scott.emp;
 
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7369 SMITH      CLERK           7902 17-DEC-80        800
        20
 
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300
        30
 
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500
        30
 
 
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7566 JONES      MANAGER         7839 02-APR-81       2975
        20
 
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400
        30
 
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850
        30
 
 
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7782 CLARK      MANAGER         7839 09-JUN-81       2450
        10
 
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000
        20
 
      7839 KING       PRESIDENT            17-NOV-81       5000
        10
 
 
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0
        30
 
      7876 ADAMS      CLERK           7788 12-JAN-83       1100
        20
 
      7900 JAMES      CLERK           7698 03-DEC-81        950
        30
 
 
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7902 FORD       ANALYST         7566 03-DEC-81       3000
        20
 
      7934 MILLER     CLERK           7782 23-JAN-82       1300
        10
 
 
14 rows selected.
 
SQL> select sql_id, parsing_schema_name from v$sql where sql_text like 'select * 
 from scott.emp%';
 
SQL_ID        PARSING_SCHEMA_NAME
------------- ------------------------------
ggqns3c1jz86c SYS
ggqns3c1jz86c SYS
9mhzzknczbj6p SDS
 
SQL> connect sys as sysdba
Enter password:
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
 
Total System Global Area  535662592 bytes
Fixed Size                  1348508 bytes
Variable Size             352324708 bytes
Database Buffers          176160768 bytes
Redo Buffers                5828608 bytes
Database mounted.
Database opened.
SQL> select * from scott.emp;
 
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7369 SMITH      CLERK           7902 17-DEC-80        800
        20
 
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300
        30
 
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500
        30
 
 
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7566 JONES      MANAGER         7839 02-APR-81       2975
        20
 
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400
        30
 
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850
        30
 
 
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7782 CLARK      MANAGER         7839 09-JUN-81       2450
        10
 
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000
        20
 
      7839 KING       PRESIDENT            17-NOV-81       5000
        10
 
 
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0
        30
 
      7876 ADAMS      CLERK           7788 12-JAN-83       1100
        20
 
      7900 JAMES      CLERK           7698 03-DEC-81        950
        30
 
 
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7902 FORD       ANALYST         7566 03-DEC-81       3000
        20
 
      7934 MILLER     CLERK           7782 23-JAN-82       1300
        10
 
 
14 rows selected.
 
SQL> select sql_id from v$sql where sql_text like 'select * from scott.emp%';
 
SQL_ID
-------------
ggqns3c1jz86c

Open in new window

0
 
LVL 4

Author Comment

by:dkaisla
ID: 22819476
Hi, thank you for the example. I tried it and I guess it's safe to conclude that even when bouncing the DB, we still get the same SQL_ID for a specific sql_text. That answers my question! thanks a lot!

a follow up question though, correct me if i'm wrong, v$sql info remains available until the cache is flushed right? i am trying to get the sql_text for a specific sql_id (sql is a day old) but i can't seem to locate it in v$sql. where can i see the "retention" period for this cache? is there any other way to know the sql_text?

thank you!
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 22821223
it's based entirely on available space.  If your shared pool is small you can't hold very many statements, if it's large, you can hold more.  If your applications don't use bind variables you'll fill the pool with lots of statements and lose old ones.

And, like I said above, "most" of the time you'll "probably" get the same sql_id.  I definitely wouldn't count on it like a guid or something like that.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

860 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