Optimizer hits query in oracle database

 optinmizor hits query chapter 7 performance book please check it any issue

===============================================================================

1. Connect the database to the SH schema:

CONNECT sh@TESTDB/sh

2. Set the auto-trace functionality in SQL*Plus to see only the execution plan without executing the queries:

SET AUTOT TRACE EXP

3. Select some records from the CUSTOMERS table (say, all customers born in 1949):

SELECT

C.CUST_FIRST_NAME, C.CUST_LAST_NAME

FROM sh.CUSTOMERS C

WHERE C.CUST_YEAR_OF_BIRTH = 1949;

4. Execute the same query suggesting that it's better to full-scan the CUSTOMERS table in order to answer the same query we just saw:

SELECT /*+ FULL(C) */

C.CUST_FIRST_NAME, C.CUST_LAST_NAME

FROM sh.CUSTOMERS C

WHERE C.CUST_YEAR_OF_BIRTH = 1949;

5. Inform the optimizer that we are only interested in the first 10 rows of the result:

SELECT /*+ FIRST_ROWS(10) */

C.CUST_FIRST_NAME, C.CUST_LAST_NAME

FROM sh.CUSTOMERS C

WHERE C.CUST_YEAR_OF_BIRTH = 1949;

6. Tell the optimizer to use indexes on the CUSTOMERS table:

SELECT /*+ INDEX(C) */

C.CUST_FIRST_NAME, C.CUST_LAST_NAME

FROM sh.CUSTOMERS C

WHERE C.CUST_YEAR_OF_BIRTH = 1949;

7. Disable the auto-trace functionality:

SET AUTOT OFF

List of users connect to PeopleSoft script

 1. To Find the List of Users Log in PIA

SELECT COUNT(nr) users_number FROM (SELECT COUNT(oprid) AS nr FROM psaccesslog WHERE To_date(SYSDATE) - To_date(logindttm) >= 0 AND To_date(logoutdttm) - To_date(SYSDATE) >= 0 GROUP  BY oprid);

################################################################################

2. The following query will list all the PIA users through PSACCESSLOG record:

 SELECT DISTINCT oprid,

                logipaddress,

                To_char(logindttm, 'YYYY-MM-DD:hh:mi:ss')   logintime,

                To_char(logoutdttm, 'YYYY-MM-DD:hh:mi:ss')  logoutime,

                To_char(( SYSDATE ), 'YYYY-MM-DD:hh:mi:ss') currtime

FROM   psaccesslog

WHERE  To_date(SYSDATE) - To_date(logindttm) >= 0

       AND To_date(logoutdttm) - To_date(SYSDATE) >= 0;  

   

#######################################################################################

   

3. The following query will list all the users in the current database(not just those logged in from PIA): SELECT sid,

       serial#,

       username,

       terminal,

       program

FROM   v$session

WHERE  username IS NOT NULL;  

Tablespace Utilization Script

 SELECT F.TABLESPACE_NAME,

       TO_CHAR ((T.TOTAL_SPACE - F.FREE_SPACE),'999,999') "USED (MB)",

       TO_CHAR (F.FREE_SPACE, '999,999') "FREE (MB)",

       TO_CHAR (T.TOTAL_SPACE, '999,999') "TOTAL (MB)",

       TO_CHAR ((ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)),'999')||' %' PER_FREE

FROM   (

       SELECT       TABLESPACE_NAME, 

                    ROUND (SUM (BLOCKS*(SELECT VALUE/1024

                                        FROM V$PARAMETER 

                                        WHERE NAME = 'db_block_size')/1024)

                           ) FREE_SPACE

       FROM DBA_FREE_SPACE

       GROUP BY TABLESPACE_NAME

       ) F,

       (

       SELECT TABLESPACE_NAME,

       ROUND (SUM (BYTES/1048576)) TOTAL_SPACE

       FROM DBA_DATA_FILES

       GROUP BY TABLESPACE_NAME

       ) T

WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME

Monitor the temporary tablespace utilization script

 Monitor temp usage

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

    

Globally

========

select a.tablespace_name, 

to_char(a.used/1024/1024, 'FM999,990.00')  MB_USED, 

to_char(a.total/1024/1024, 'FM999,990.00') MB_TOTAL,

to_char(used*100/total, 'FM990.00')||'%' perc_used from (

  select tablespace_name, block_size

  , (select SUM (v$sort_usage.blocks * block_size) from v$sort_usage 

     where v$sort_usage.TABLESPACE = dba_tablespaces.tablespace_name) USED

  , (select sum(bytes) from dba_temp_files where tablespace_name = dba_tablespaces.tablespace_name) TOTAL

  from dba_tablespaces

  where contents = 'TEMPORARY'

) a

************************************************************************************************************

For each session

==============

SELECT (SELECT   nvl(SUM (v$sort_usage.blocks * dba_tablespaces.block_size

                     ), 0)

               / 1024

               / 1024

          FROM v$sort_usage, dba_tablespaces

         WHERE dba_tablespaces.tablespace_name = v$sort_usage.TABLESPACE

           AND v$sort_usage.session_addr = s.saddr) sort_space_mb,

       s.*

  FROM v$session s

****************************************************************************************************************

Total Temp Usage

==============

select (total_blocks*8192)/1024/1024/1000 "Avail (Gb)",(used_blocks*8192)/1024/1024/1000 "Used (Gb)",

(free_blocks*8192)/1024/1024/1000 "Free (Gb)" from v$sort_segment

TEMP usage session level

====================

Temp usage - session level 

column username format a15

column tablespace format a10

col sid format 9999

col machine format a12

col last_call_et/3600 format 999.99

col osuser format a15

set pages 1000

set lines 170

select /*+ RULE */ v.sid,v.serial#,v.username,v.osuser,v.machine, v.status,v.last_call_et/3600,tablespace,blocks*8192/1024/1024/1000 "size (GB)" 

from v$session v,v$sort_usage s

where v.saddr=s.session_addr and (blocks*8192/1024/1024/1000) > 0.5 

order by 9 desc

To See Sort being carried out

=====================

select sid, s.username, tablespace, blocks*8192/1024/1024 from v$session s,v$sort_usage ss where s.saddr=ss.session_addr;

Joining "V$SORT_USAGE" to "V$SESSION" will provide the user who is 

performing a sort within the sort segment.

The CONTENTS column shows whether the segment is created in a temporary 

or permanent tablespace.

When there are no active sorts for the instance you will not see any 

rows in v$sort_usage.

According to bug 1111185 (closed as not a bug) the BLOCKS_FREE in 

V$TEMP_SPACE_HEADER can be misleading.

"The extents are free but cached (as shown by v$temp_extent_pool).

You can look at this situation as analagous to the blocks being 

formatted rather than unformatted.

Once extents are used, they remain and don't participate in the 

bytes_free, blocks_free calculations of v$temp_space_header, at least 

for locally managed tablespaces."

Instead, you should look at FREE_BLOCKS from v$sort_segment.

the problem is same with the OEM also, it reports incorrectly.

PeopleSoft Security Script

 

The menu associated with each of the PeopleTools applications:

==============================================================

PeopleTools Application Menu 

Application designer APPLICATION_DESIGNER 

Data mover DATA_MOVER 

Definition Security OBJECT_SECURITY 

Query access QUERY 

Performance Monitor PPMI PERFMONPPMI 

To identify all permission lists (CLASSID) that provide access to the APPLICATION_DESIGNER menu using the following query:

========================================================================================================================

select distinct CLASSID from PSAUTHITEM where MENUNAME = 'APPLICATION_DESIGNER';

select CLASSID from PSAUTHITEM where MENUNAME='DATA_MOVER';

select CLASSID from PSAUTHITEM where MENUNAME='MAINTAIN_SECURITY';

To find the User associated with a particular Permission list

==============================================================

SQL> select OPRID,OPRCLASS from psoprcls where OPRCLASS='PTPT1200';

To find the Role associated with a permission list

====================================================

SQL> select * from PSROLECLASS where CLASSID='PTPT1200';

To find the User associated with a particular ROLE

======================================================

SQL> select * from PSROLEUSER where ROLENAME='PeopleTools';

To find the roll assigned to all the users

===========================================

select a.oprid User_ID,a.oprdefndesc User_Name,b.rolename,c.descr

from psoprdefn a,psroleuser b,PSROLEDEFN c

where a.oprid= b.roleuser

and b.rolename= c.rolename

and a.oprid not LIKE 'SP%'

finding permission lists with access, and identifying which users have those permission lists. 

=============================================================================================

select

    OC.OPRID,

    OC.OPRCLASS

from 

    PSOPRCLS OC

where 

    OPRCLASS = 'PSADMIN' or OPRCLASS in (

        select  CLASSID

        from    PSAUTHITEM        

        where   MENUNAME = 'APPLICATION_DESIGNER'    

        and     AUTHORIZEDACTIONS >= 1

    )

order by OC.OPRID, OPRCLASS

Note that this query takes into account the PeopleSoft Administrator role (permission list PSADMIN) as users with this role will also have access to PeopleTools.

Find out which users (and from what permission lists) have access to a particular page:

==========================================================================

select distinct OPRID, OPRCLASS

from PSOPRCLS

where OPRCLASS in (

    select distinct CLASSID

    from sysadm.PSAUTHITEM

    where PNLITEMNAME = '<PAGENAME>'

);

Synonyms and Grant Script Creation Steps

Synonyms and Grant Script Creation Steps 

 -- Run Dynamic SQL to create synonym script

SET HEA OFF;

SET PAGESIZE 0;

SET LINES 150;

SPOOL SYNONYM.SQL

select 'create public synonym '||object_name||' for '||owner||'.'||object_name||';'

from sys.dba_objects where owner = 'SYSADM' and object_type in ('TABLE', 'VIEW');

SPOOL OFF;

-- Run Dynamic SQL to create grant scripts

SET HEA OFF;

SET PAGESIZE 0;

SET LINES 150

SPOOL GRANT.SQL

select 'grant select, update, insert, delete on '||object_name||' to VOLT_SUID;'

from sys.dba_objects where owner = 'SYSADM' and object_type in ('TABLE', 'VIEW');

SPOOL OFF;

prompt

prompt Manually run synonym script 

prompt Enable spool to generate log

prompt @SYNONYM.SQL

prompt

prompt Manually run grant scripts

prompt Enable spool to generate log

prompt @GRANT.SQL