Solved

Is SQL_ID in Oracle reused?

Posted on 2008-10-23
5
3,110 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 73

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 73

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to take different types of Oracle backups using RMAN.

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now